public inbox for [email protected]
help / color / mirror / Atom feedProcedure parameter list enumeration error
3+ messages / 2 participants
[nested] [flat]
* Procedure parameter list enumeration error
@ 2021-10-01 11:46 Kamil ADEM <[email protected]>
2021-10-01 11:59 ` Re: Procedure parameter list enumeration error Adrian Grucza <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Kamil ADEM @ 2021-10-01 11:46 UTC (permalink / raw)
To: pgsql-odbc; +Cc: Haluk DALKIRAN <[email protected]>
Hello,
We use psqlODBC to access Postgres in our MFC C source on Windows. While enumerating procedure parameters we observe some errors we would like to be solved.
We have a procedure declared as:
public.SP_TEST(INOUT return_value INTEGER, INOUT p1_varchar1024 ACIKLAMA1024, INOUT p2_char20 KOD, INOUT p3_int TAMSAYI, INOUT p4_smallint YIL, INOUT p5_bit BOOL, INOUT p6_float MEBLAG, INOUT p7_datetime TARIH)
The parameter types are user created domains.
We have the following C source using Microsoft ADODB :
_CommandPtr pCommand;
pCommand->CommandType = adCmdStoredProc;
pCommand->CommandText = _bstr_t("SP_TEST");
Next, we enumerate the array pCommand->Parameters using pCommand->Parameters->Item[_variant_t(i)] and get the following output:
Name=return_value, Type=3, Direction=3, Size=0
Name=p1_varchar1024, Type=200, Direction=3, Size=255
Name=p2_char20, Type=200, Direction=3, Size=255
Name=p3_int, Type=200, Direction=3, Size=255
Name=p4_smallint, Type=200, Direction=3, Size=255
Name=p5_bit, Type=200, Direction=3, Size=5
Name=p6_float, Type=200, Direction=3, Size=255
Name=p7_datetime, Type=200, Direction=3, Size=255
The problem is that the types and sizes of the user created domains are not reported correctly!
When we change the procedure to the following, replacing the domains with underlying data types:
public.SP_TEST(INOUT return_value INTEGER, INOUT p1_varchar1024 VARCHAR(1024), INOUT p2_char20 CHAR(20), INOUT p3_int INT, INOUT p4_smallint SMALLINT, INOUT p5_bit BOOLEAN, INOUT p6_float FLOAT, INOUT p7_datetime TIMESTAMP)
we get the following enumeration result:
Name=return_value, Type=3, Direction=3, Size=0
Name=p1_varchar1024, Type=200, Direction=3, Size=255
Name=p2_char20, Type=200, Direction=3, Size=255
Name=p3_int, Type=3, Direction=3, Size=0
Name=p4_smallint, Type=2, Direction=3, Size=0
Name=p5_bit, Type=200, Direction=3, Size=5
Name=p6_float, Type=5, Direction=3, Size=0
Name=p7_datetime, Type=135, Direction=3, Size=0
This time the types are reported correctly (with the exception of boolean), but the size field for character types are still incorrect!
We use the following connection string:
"Driver={PostgreSQL ANSI};Server=localhost;Port=5432;Database=...;Uid=postgres;Pwd=..."
On the ODBC DSN configuration, the following changes have been made:
"Bools as Char" is unchecked
"Max VarChar" = 1036
We have a generic interface for calling stored procedures in our code being migrated from MSSQL to PostgreSQL. So we need to enumerate the parameters before calling the procedure to generate the parameter list.
How can we get rid of these issues? How can you help us to solve these problems?
Thanks in advance.
Kamil Adem
Aqvila Software Yazılım A.Ş.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Procedure parameter list enumeration error
2021-10-01 11:46 Procedure parameter list enumeration error Kamil ADEM <[email protected]>
@ 2021-10-01 11:59 ` Adrian Grucza <[email protected]>
2021-10-01 12:12 ` RE: Procedure parameter list enumeration error Kamil ADEM <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Adrian Grucza @ 2021-10-01 11:59 UTC (permalink / raw)
To: Kamil ADEM <[email protected]>; +Cc: pgsql-odbc; Haluk DALKIRAN <[email protected]>
Hi Kamil,
Not sure about the domain types and the Boolean, but for the sizes,
PostgreSQL does not store string lengths on procedure parameters. If you
provide them when creating the procedure they are discarded without error
(see the Notes section of
https://www.postgresql.org/docs/current/sql-createfunction.html). The
procedure will accept strings of any length.
Adrian Grucza
Technical Lead
Tel: +61390185800
[email protected]
www.iress.com
Level 16 385 Bourke St
Melbourne, Victoria, 3000
The contents of this email originated from Iress. For this purpose Iress includes Iress Limited and/or any of its subsidiaries, holding companies and trading entities. If you have received this email in error please notify the sender immediately and delete this email.
nosig
On Fri, 1 Oct 2021 at 21:45, Kamil ADEM <[email protected]> wrote:
>
>
> *CAUTION: *This Email is from an EXTERNAL source. Ensure you trust this
> sender before clicking on any links or attachments.
>
>
> Hello,
>
>
>
> We use psqlODBC to access Postgres in our MFC C source on Windows. While
> enumerating procedure parameters we observe some errors we would like to be
> solved.
>
>
>
> We have a procedure declared as:
>
> public.SP_TEST(INOUT return_value INTEGER, INOUT p1_varchar1024
> ACIKLAMA1024, INOUT p2_char20 KOD, INOUT p3_int TAMSAYI, INOUT p4_smallint
> YIL, INOUT p5_bit BOOL, INOUT p6_float MEBLAG, INOUT p7_datetime TARIH)
>
> The parameter types are user created domains.
>
> We have the following C source using Microsoft ADODB :
>
> _CommandPtr pCommand;
>
> pCommand->CommandType = adCmdStoredProc;
>
> pCommand->CommandText = _bstr_t("SP_TEST");
>
> Next, we enumerate the array pCommand->Parameters using
> pCommand->Parameters->Item[_variant_t(i)] and get the following output:
>
> Name=return_value, Type=3, Direction=3, Size=0
>
> Name=p1_varchar1024, Type=200, Direction=3, Size=255
>
> Name=p2_char20, Type=200, Direction=3, Size=255
>
> Name=p3_int, Type=200, Direction=3, Size=255
>
> Name=p4_smallint, Type=200, Direction=3, Size=255
>
> Name=p5_bit, Type=200, Direction=3, Size=5
>
> Name=p6_float, Type=200, Direction=3, Size=255
>
> Name=p7_datetime, Type=200, Direction=3, Size=255
>
>
>
> *The problem is that the types and sizes of the user created domains are
> not reported correctly!*
>
>
>
> When we change the procedure to the following, replacing the domains with
> underlying data types:
>
> public.SP_TEST(INOUT return_value INTEGER, INOUT p1_varchar1024
> VARCHAR(1024), INOUT p2_char20 CHAR(20), INOUT p3_int INT, INOUT
> p4_smallint SMALLINT, INOUT p5_bit BOOLEAN, INOUT p6_float FLOAT, INOUT
> p7_datetime TIMESTAMP)
>
> we get the following enumeration result:
>
> Name=return_value, Type=3, Direction=3, Size=0
>
> Name=p1_varchar1024, Type=200, Direction=3, Size=255
>
> Name=p2_char20, Type=200, Direction=3, Size=255
>
> Name=p3_int, Type=3, Direction=3, Size=0
>
> Name=p4_smallint, Type=2, Direction=3, Size=0
>
> Name=p5_bit, Type=200, Direction=3, Size=5
>
> Name=p6_float, Type=5, Direction=3, Size=0
>
> Name=p7_datetime, Type=135, Direction=3, Size=0
>
>
>
> *This time the types are reported correctly (with the exception of
> boolean), but the size field for character types are still incorrect!*
>
>
>
> We use the following connection string:
>
> "Driver={PostgreSQL
> ANSI};Server=localhost;Port=5432;Database=…;Uid=postgres;Pwd=…"
>
> On the ODBC DSN configuration, the following changes have been made:
>
> “Bools as Char” is unchecked
>
> “Max VarChar” = 1036
>
>
>
> We have a generic interface for calling stored procedures in our code
> being migrated from MSSQL to PostgreSQL. So we need to enumerate the
> parameters before calling the procedure to generate the parameter list.
>
>
>
> How can we get rid of these issues? How can you help us to solve these
> problems?
>
>
>
> Thanks in advance.
>
>
>
> Kamil Adem
>
> Aqvila Software Yazılım A.Ş.
>
>
>
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* RE: Procedure parameter list enumeration error
2021-10-01 11:46 Procedure parameter list enumeration error Kamil ADEM <[email protected]>
2021-10-01 11:59 ` Re: Procedure parameter list enumeration error Adrian Grucza <[email protected]>
@ 2021-10-01 12:12 ` Kamil ADEM <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Kamil ADEM @ 2021-10-01 12:12 UTC (permalink / raw)
To: Adrian Grucza <[email protected]>; +Cc: pgsql-odbc; Haluk DALKIRAN <[email protected]>
Hi Adrian,
Boolean type and the sizes are minor problems for us actually. We can handle them more easily than the domain type problem.
But we need the enumeration of underlying types when the procedure is declared using domains.
Thanks,
Kamil Adem
From: Adrian Grucza <[email protected]>
Sent: Friday, October 1, 2021 3:00 PM
To: Kamil ADEM <[email protected]>
Cc: [email protected]; Haluk DALKIRAN <[email protected]>
Subject: Re: Procedure parameter list enumeration error
Hi Kamil,
Not sure about the domain types and the Boolean, but for the sizes, PostgreSQL does not store string lengths on procedure parameters. If you provide them when creating the procedure they are discarded without error (see the Notes section of https://www.postgresql.org/docs/current/sql-createfunction.html). The procedure will accept strings of any length.
[Image removed by sender. iress.com]<https://www.iress.com/;
Adrian Grucza
Technical Lead
Tel:
+61390185800
[email protected]<mailto:[email protected]>
www.iress.com<https://www.iress.com/;
Level 16 385 Bourke St
Melbourne,
Victoria,
3000
The contents of this email originated from Iress. For this purpose Iress includes Iress Limited and/or any of its subsidiaries, holding companies and trading entities. If you have received this email in error please notify the sender immediately and delete this email.
nosig
On Fri, 1 Oct 2021 at 21:45, Kamil ADEM <[email protected]<mailto:[email protected]>> wrote:
CAUTION: This Email is from an EXTERNAL source. Ensure you trust this sender before clicking on any links or attachments.
Hello,
We use psqlODBC to access Postgres in our MFC C source on Windows. While enumerating procedure parameters we observe some errors we would like to be solved.
We have a procedure declared as:
public.SP_TEST(INOUT return_value INTEGER, INOUT p1_varchar1024 ACIKLAMA1024, INOUT p2_char20 KOD, INOUT p3_int TAMSAYI, INOUT p4_smallint YIL, INOUT p5_bit BOOL, INOUT p6_float MEBLAG, INOUT p7_datetime TARIH)
The parameter types are user created domains.
We have the following C source using Microsoft ADODB :
_CommandPtr pCommand;
pCommand->CommandType = adCmdStoredProc;
pCommand->CommandText = _bstr_t("SP_TEST");
Next, we enumerate the array pCommand->Parameters using pCommand->Parameters->Item[_variant_t(i)] and get the following output:
Name=return_value, Type=3, Direction=3, Size=0
Name=p1_varchar1024, Type=200, Direction=3, Size=255
Name=p2_char20, Type=200, Direction=3, Size=255
Name=p3_int, Type=200, Direction=3, Size=255
Name=p4_smallint, Type=200, Direction=3, Size=255
Name=p5_bit, Type=200, Direction=3, Size=5
Name=p6_float, Type=200, Direction=3, Size=255
Name=p7_datetime, Type=200, Direction=3, Size=255
The problem is that the types and sizes of the user created domains are not reported correctly!
When we change the procedure to the following, replacing the domains with underlying data types:
public.SP_TEST(INOUT return_value INTEGER, INOUT p1_varchar1024 VARCHAR(1024), INOUT p2_char20 CHAR(20), INOUT p3_int INT, INOUT p4_smallint SMALLINT, INOUT p5_bit BOOLEAN, INOUT p6_float FLOAT, INOUT p7_datetime TIMESTAMP)
we get the following enumeration result:
Name=return_value, Type=3, Direction=3, Size=0
Name=p1_varchar1024, Type=200, Direction=3, Size=255
Name=p2_char20, Type=200, Direction=3, Size=255
Name=p3_int, Type=3, Direction=3, Size=0
Name=p4_smallint, Type=2, Direction=3, Size=0
Name=p5_bit, Type=200, Direction=3, Size=5
Name=p6_float, Type=5, Direction=3, Size=0
Name=p7_datetime, Type=135, Direction=3, Size=0
This time the types are reported correctly (with the exception of boolean), but the size field for character types are still incorrect!
We use the following connection string:
"Driver={PostgreSQL ANSI};Server=localhost;Port=5432;Database=…;Uid=postgres;Pwd=…"
On the ODBC DSN configuration, the following changes have been made:
“Bools as Char” is unchecked
“Max VarChar” = 1036
We have a generic interface for calling stored procedures in our code being migrated from MSSQL to PostgreSQL. So we need to enumerate the parameters before calling the procedure to generate the parameter list.
How can we get rid of these issues? How can you help us to solve these problems?
Thanks in advance.
Kamil Adem
Aqvila Software Yazılım A.Ş.
Attachments:
[image/jpeg] ~WRD0001.jpg (823B, 3-~WRD0001.jpg)
download | view image
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-10-01 12:12 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-10-01 11:46 Procedure parameter list enumeration error Kamil ADEM <[email protected]>
2021-10-01 11:59 ` Adrian Grucza <[email protected]>
2021-10-01 12:12 ` Kamil ADEM <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox