Thread: Need some assistance on stored procedures execution using libpq in C

Need some assistance on stored procedures execution using libpq in C

From
Sasmit Utkarsh
Date:
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
Attachment
Hi

čt 25. 4. 2024 v 12:57 odesílatel Sasmit Utkarsh <utkarshsasmit@gmail.com> napsal:
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

PQ interface is designed for client server communication

you should to use SPI API


Regards

Pavel
 

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
Hi Pavel,

Thanks for the info. But is it not possible to have some kind of handling of an empty result set using libpq for the given procedure? 


Regards,
Sasmit Utkarsh
+91-7674022625


On Thu, Apr 25, 2024 at 8:26 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hi

čt 25. 4. 2024 v 12:57 odesílatel Sasmit Utkarsh <utkarshsasmit@gmail.com> napsal:
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

PQ interface is designed for client server communication

you should to use SPI API


Regards

Pavel
 

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


čt 25. 4. 2024 v 18:46 odesílatel Sasmit Utkarsh <utkarshsasmit@gmail.com> napsal:
Hi Pavel,

Thanks for the info. But is it not possible to have some kind of handling of an empty result set using libpq for the given procedure? 

extension dblink uses libpq (to connect to other databases or servers). You can check code there


Regards

Pavel


Regards,
Sasmit Utkarsh
+91-7674022625


On Thu, Apr 25, 2024 at 8:26 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hi

čt 25. 4. 2024 v 12:57 odesílatel Sasmit Utkarsh <utkarshsasmit@gmail.com> napsal:
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

PQ interface is designed for client server communication

you should to use SPI API


Regards

Pavel
 

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