Need some assistance on stored procedures execution using libpq in C

Started by Sasmit Utkarshalmost 2 years ago4 messagesgeneral

utkarshsasmit@gmail.com

almost 2 years ago

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

Pavel Stehule

pavel.stehule@gmail.com

almost 2 years ago

In reply to: Sasmit Utkarsh (#1)

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

č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

https://www.postgresql.org/docs/current/spi.html

Regards

Pavel

Show quoted text

*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

Sasmit Utkarsh

utkarshsasmit@gmail.com

almost 2 years ago

In reply to: Pavel Stehule (#2)

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

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:

Show quoted text

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

https://www.postgresql.org/docs/current/spi.html

Regards

Pavel

*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

Pavel Stehule

pavel.stehule@gmail.com

almost 2 years ago

In reply to: Sasmit Utkarsh (#3)

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

č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

https://github.com/postgres/postgres/blob/master/contrib/dblink/dblink.c

Regards

Pavel

Show quoted text

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

https://www.postgresql.org/docs/current/spi.html

Regards

Pavel

*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

Need some assistance on stored procedures execution using libpq in C

Attachments: