Need some assistance on stored procedures execution using libpq in C

[Sasmit Utkarsh <utkarshsasmit@xxxxxxxxx>]

Hi PostgreSQL Team,

I'm trying to execute the stored procedure(details along with the program in the attachment) to fetch the records from the table for the inputs given in the code as well. I have already created the procedure in the db. But I see some strange output when I try to fetch the details from the table using PQexecPrepared, even though there are no records for the given input. I see that this message nTuples(rows)=1 along with procedure successful execution. How to better handle this?

when it returned probably an empty result set. Please advise

Output:

sasmit@sasmit-Virtual-Machine:~/test$ ./test_procedures
main() Connection to shadow_shc_data database SUCCESSFUL
main() nFields(cols)=2 nTuples(rows)=1
SELECT operation succeeded on Shadow DB
main() blk_size returned is 7565871

DB:

[nix-shell:/ext/shb/tpfasm/shares-c]$ psql shadow_shc_data
psql (14.9, server 12.16 (Ubuntu 12.16-0ubuntu0.20.04.1))
Type "help" for help.

shadow_shc_data=# select * from fs_data;
shadow_shc_data=# CALL SQL_select_data_procedure(-335509949,false , NULL, NULL);
 blksize | fadata
---------+--------
         |
(1 row)

shadow_shc_data-# \df
List of functions
-[ RECORD 1 ]-------+-----------------------------------------------------------------------------------------------------------------
Schema              | public
Name                | sql_insert_data_procedure
Result data type    |
Argument data types | fa integer, ft integer, ord integer, xaddr text, recid text, blk_size integer, indata bytea, INOUT outdata bytea
Type                | proc
-[ RECORD 2 ]-------+-----------------------------------------------------------------------------------------------------------------
Schema              | public
Name                | sql_select_data_procedure
Result data type    |
Argument data types | fa integer, hold boolean, INOUT blksize integer, INOUT fadata bytea
Type                | proc
-[ RECORD 3 ]-------+-----------------------------------------------------------------------------------------------------------------
Schema              | public
Name                | sql_update_data_procedure
Result data type    |
Argument data types | indata bytea, unhold boolean, fa integer
Type                | proc

Regards,

Sasmit Utkarsh

+91-7674022625

/*** stored Procedure:

CREATE OR REPLACE PROCEDURE sql_select_data_procedure(
    fa integer,
    hold bool,
    INOUT blksize integer,
    INOUT fadata bytea
) LANGUAGE plpgsql AS 
$$ 
BEGIN 
    IF (hold) THEN
        PERFORM pg_advisory_lock(fa);
    END IF; 
    SELECT blk_size, data INTO blksize, fadata 
    FROM fs_data 
    WHERE file_address = fa; 
END; 
$$;

***/
#include <stdio.h>
#include <stdlib.h>
#include <stdbool.h>
#include <libpq-fe.h>
#include <arpa/inet.h> // For htonl

int main() {

    int   nFields = 0;
    int   nTuples = 0;
    int   blk_size = 0;
    PGconn *shadow_db_conn;
    PGresult *res;
    const char *shadow_db_conn_info = "dbname=shadow_shc_data";
    int is_shadow_db = 1; // Assuming it's a shadow database
    char *blk_size_val;
    char *data_val;

    // Connect to the shadow database
    if (is_shadow_db) {
        shadow_db_conn = PQconnectdb(shadow_db_conn_info);
        if (PQstatus(shadow_db_conn) != CONNECTION_OK) {
            fprintf(stderr, "Connection to shadow database failed: %s\n", PQerrorMessage(shadow_db_conn));
            PQfinish(shadow_db_conn);
            exit(1);
        }

        // Set bytea_output to 'escape'
        res = PQexec(shadow_db_conn, "SET bytea_output = 'escape'");
        if (PQresultStatus(res) != PGRES_COMMAND_OK) {
            fprintf(stderr, "SET bytea_output command failed: %s\n", PQerrorMessage(shadow_db_conn));
            PQclear(res);
            PQfinish(shadow_db_conn);
            exit(1);
        }
        PQclear(res);
        printf("%s() Connection to shadow_shc_data database SUCCESSFUL\n", __func__);
    }

    // Construct the command to prepare
    char SelectDataName[11];
    char SelectDataCommand[150];
    int  SelectDataNParams;
    Oid  SelectDataParamTypes[2];

    sprintf(SelectDataName,"%s","SelectData");
    sprintf(SelectDataCommand, "CALL SQL_select_data_procedure($1, $2, NULL, NULL)");
    SelectDataNParams = 2;
    SelectDataParamTypes[0] = 23;  // int
    SelectDataParamTypes[1] = 16;  // bool

    // Prepare the statement
    if (is_shadow_db) {
        res = PQprepare(shadow_db_conn,
                        SelectDataName,
                        SelectDataCommand,
                        SelectDataNParams,
                        SelectDataParamTypes);
        if (PQresultStatus(res) != PGRES_COMMAND_OK) {
            fprintf(stderr, "Prepare failed: %s\n", PQerrorMessage(shadow_db_conn));
            PQclear(res);
            PQfinish(shadow_db_conn);
            exit(1);
        }
        PQclear(res);
    }

    // Sample input values
    int32_t fa = -335509949;
    bool hold = false;

    uint32_t fa2 = htonl(fa);
    char *fa_val = (char *)&fa2;
    bool hold2 = hold;
    char *hold_val = (char *)&hold2;


    const char *paramValues[2]      = {fa_val, hold_val};
    int   paramLengths[2]           = {sizeof(fa2), sizeof(hold2)};
    int   paramFormats[2]           = {1, 1};       // identify parameter as binary

    // Assuming paramValues, paramLengths, and paramFormats are properly populated
    // Execute the prepared statement
    if (is_shadow_db) {
        //const char *paramValues[2] = {"123", "true"};
        //int paramLengths[2] = {sizeof("123") - 1, sizeof("true") - 1}; // Minus 1 for excluding null terminator
        //int paramFormats[2] = {0, 0}; // Assuming all parameters are in text format
        int resultFormat = 1; // 1 for binary result format

        res = PQexecPrepared(shadow_db_conn,
                             SelectDataName,
                             SelectDataNParams,
                             paramValues,
                             paramLengths,
                             paramFormats,
                             resultFormat);

        if (PQresultStatus(res) == PGRES_TUPLES_OK && PQnfields(res) != 0 && PQntuples(res) > 0) {
            //printf("SELECT operation succeeded on Shadow DB\n");
            // Process the result if needed
            nFields = PQnfields(res);       // number of columns
            nTuples = PQntuples(res);       // number of rows
            printf("%s() nFields(cols)=%i nTuples(rows)=%i\n",__func__,nFields,nTuples);

            blk_size_val = PQgetvalue(res, 0, 0);
            data_val = PQgetvalue(res, 0, 1);
            if(blk_size_val != NULL && data_val != NULL)
            {
                  printf("SELECT operation succeeded on Shadow DB\n");
                  blk_size = ntohl(*(uint32_t *)PQgetvalue (res, 0, 0));
                  printf("%s() blk_size returned is %i\n",__func__,blk_size);
            }

        } else {
            fprintf(stderr, "SELECT failed on Shadow DB: %s\n", PQerrorMessage(shadow_db_conn));
        }
        PQclear(res);
    }

    PQfinish(shadow_db_conn); // Close the connection
    return 0;
}