Обсуждение: Need some assistance on stored procedures execution using libpq in C
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
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)
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
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
č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 7565871DB:[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 | procRegards,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?
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 advisePQ interface is designed for client server communicationyou should to use SPI APIRegardsPavelOutput: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 7565871DB:[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 | procRegards,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-7674022625On 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 advisePQ interface is designed for client server communicationyou should to use SPI APIRegardsPavelOutput: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 7565871DB:[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 | procRegards,Sasmit Utkarsh+91-7674022625