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_proceduresmain() Connection
to shadow_shc_data database SUCCESSFULmain() nFields(cols)=2
nTuples(rows)=1SELECT operation succeeded on Shadow DBmain() blk_size
returned is 7565871*
*DB:*
*[nix-shell:/ext/shb/tpfasm/shares-c]$ psql shadow_shc_datapsql (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-# \dfList of functions-[ RECORD 1
]-------+-----------------------------------------------------------------------------------------------------------------Schema
| publicName | sql_insert_data_procedureResult
data type |Argument data types | fa integer, ft integer, ord integer,
xaddr text, recid text, blk_size integer, indata bytea, INOUT outdata
byteaType | proc-[ RECORD 2
]-------+-----------------------------------------------------------------------------------------------------------------Schema
| publicName | sql_select_data_procedureResult
data type |Argument data types | fa integer, hold boolean, INOUT blksize
integer, INOUT fadata byteaType | proc-[ RECORD 3
]-------+-----------------------------------------------------------------------------------------------------------------Schema
| publicName | sql_update_data_procedureResult
data type |Argument data types | indata bytea, unhold boolean, fa
integerType | 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;
}
