MIME-Version: 1.0
References: <CAM-5MT2-Sr8CquG96KWfz05b1smh4giP8xisyiT1qxL5vqpgiQ@mail.gmail.com>
In-Reply-To: <CAM-5MT2-Sr8CquG96KWfz05b1smh4giP8xisyiT1qxL5vqpgiQ@mail.gmail.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Thu, 25 Apr 2024 16:56:14 +0200
Message-ID: <CAFj8pRAJKfcgS7TmC=ou_WrrA+8VmpUi1FtO2u4SMSQhLC+rZA@mail.gmail.com>
Subject: Re: Need some assistance on stored procedures execution using libpq
 in C
To: Sasmit Utkarsh <utkarshsasmit@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000004e6e830616ecff4c"
Archived-At: <https://www.postgresql.org/message-id/CAFj8pRAJKfcgS7TmC%3Dou_WrrA%2B8VmpUi1FtO2u4SMSQhLC%2BrZA%40mail.gmail.com>
Precedence: bulk

--0000000000004e6e830616ecff4c
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi

=C4=8Dt 25. 4. 2024 v 12:57 odes=C3=ADlatel Sasmit Utkarsh <utkarshsasmit@g=
mail.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 giv=
en
> 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)=3D1 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)=3D2
> nTuples(rows)=3D1SELECT 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=3D# select * from fs_data;shadow_shc_data=3D# 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_procedureResul=
t
> 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_procedureResul=
t
> data type    |Argument data types | fa integer, hold boolean, INOUT blksi=
ze
> integer, INOUT fadata byteaType                | proc-[ RECORD 3
> ]-------+----------------------------------------------------------------=
-------------------------------------------------Schema
>              | publicName                | sql_update_data_procedureResul=
t
> data type    |Argument data types | indata bytea, unhold boolean, fa
> integerType                | proc*
>
> Regards,
> Sasmit Utkarsh
> +91-7674022625
>

--0000000000004e6e830616ecff4c
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi<br></div><br><div class=3D"gmail_quote"><div dir=
=3D"ltr" class=3D"gmail_attr">=C4=8Dt 25. 4. 2024 v=C2=A012:57 odes=C3=ADla=
tel Sasmit Utkarsh &lt;<a href=3D"mailto:utkarshsasmit@gmail.com">utkarshsa=
smit@gmail.com</a>&gt; napsal:<br></div><blockquote class=3D"gmail_quote" s=
tyle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);pad=
ding-left:1ex"><div dir=3D"ltr">Hi PostgreSQL Team,<div><br></div><div>I=
9;m trying to execute the stored procedure(details along with the program i=
n 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 usin=
g <b>PQexecPrepared</b>, even though there are no records for the given inp=
ut. I see that this message nTuples(rows)=3D1 along with procedure successf=
ul execution. How to better handle this?</div><div>when it returned probabl=
y an empty result set. Please advise</div></div></blockquote><div><br></div=
><div>PQ interface is designed for client server communication</div><div><b=
r></div><div>you should to use SPI API</div><div><br></div><div><a href=3D"=
https://www.postgresql.org/docs/current/spi.html">https://www.postgresql.or=
g/docs/current/spi.html</a></div><div><br></div><div>Regards</div><div><br>=
</div><div>Pavel</div><div>=C2=A0<br></div><blockquote class=3D"gmail_quote=
" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);=
padding-left:1ex"><div dir=3D"ltr"><div><br></div><div><b>Output:</b></div>=
<div><i>sasmit@sasmit-Virtual-Machine:~/test$ ./test_procedures<br>main() C=
onnection to shadow_shc_data database SUCCESSFUL<br><b style=3D"background-=
color:rgb(255,255,0)">main() nFields(cols)=3D2 nTuples(rows)=3D1<br>SELECT =
operation succeeded on Shadow DB<br>main() blk_size returned is 7565871</b>=
<br></i></div><div><br></div><div><b>DB:</b></div><div><i>[nix-shell:/ext/s=
hb/tpfasm/shares-c]$ psql shadow_shc_data<br>psql (14.9, server 12.16 (Ubun=
tu 12.16-0ubuntu0.20.04.1))<br>Type &quot;help&quot; for help.<br><br>shado=
w_shc_data=3D# select * from fs_data;<br><span style=3D"background-color:rg=
b(255,255,0)"><b><font color=3D"#000000">shadow_shc_data=3D# CALL SQL_selec=
t_data_procedure(-335509949,false , NULL, NULL);<br>=C2=A0blksize | fadata<=
br>---------+--------<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|</font></b><br>=
(1 row)</span><br></i></div><div><span style=3D"background-color:rgb(255,25=
5,0)"><i><br></i></span></div><div><i>shadow_shc_data-# \df<br>List of func=
tions<br>-[ RECORD 1 ]-------+---------------------------------------------=
--------------------------------------------------------------------<br>Sch=
ema =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| public<br>Name =C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| sql_insert_data_procedur=
e<br>Result data type =C2=A0 =C2=A0|<br>Argument data types | fa integer, f=
t integer, ord integer, xaddr text, recid text, blk_size integer, indata by=
tea, INOUT outdata bytea<br>Type =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0| proc<br>-[ RECORD 2 ]-------+-------------------------------=
---------------------------------------------------------------------------=
-------<br><b style=3D"background-color:rgb(255,255,0)">Schema =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| public<br>Name =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| sql_select_data_procedure<br>Result da=
ta type =C2=A0 =C2=A0|<br>Argument data types | fa integer, hold boolean, I=
NOUT blksize integer, INOUT fadata bytea<br>Type =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| proc</b><br>-[ RECORD 3 ]-------+---------=
---------------------------------------------------------------------------=
-----------------------------<br>Schema =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0| public<br>Name =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0| sql_update_data_procedure<br>Result data type =C2=A0 =C2=A0|<br=
>Argument data types | indata bytea, unhold boolean, fa integer<br>Type =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| proc</i><br></div><di=
v><br clear=3D"all"><div><div dir=3D"ltr" class=3D"gmail_signature"><div di=
r=3D"ltr"><div>Regards,<br></div><div>Sasmit Utkarsh</div><div>+91-76740226=
25</div></div></div></div></div></div>
</blockquote></div></div>

--0000000000004e6e830616ecff4c--