Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mWGyi-0004u9-8u for pgsql-odbc@arkaria.postgresql.org; Fri, 01 Oct 2021 11:45:24 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mWGyg-0007JI-Va for pgsql-odbc@arkaria.postgresql.org; Fri, 01 Oct 2021 11:45:22 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mWGyg-0007J9-Ny for pgsql-odbc@lists.postgresql.org; Fri, 01 Oct 2021 11:45:22 +0000 Received: from exsmtp.ofis.net ([185.15.41.104]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1mWGyd-0007D8-LW for pgsql-odbc@postgresql.org; Fri, 01 Oct 2021 11:45:22 +0000 Received: from exmbx04.ofis.int (10.10.25.114) by exmbx01.ofis.int (10.10.25.111) with Microsoft SMTP Server (TLS) id 15.0.1497.23; Fri, 1 Oct 2021 14:46:07 +0300 Received: from exmbx04.ofis.int ([fe80::a12b:e369:553b:684b]) by exmbx04.ofis.int ([fe80::a12b:e369:553b:684b%18]) with mapi id 15.00.1497.023; Fri, 1 Oct 2021 14:46:07 +0300 From: Kamil ADEM To: "pgsql-odbc@postgresql.org" CC: Haluk DALKIRAN Subject: Procedure parameter list enumeration error Thread-Topic: Procedure parameter list enumeration error Thread-Index: Ade2uKkT3imBdUC7RLGMwlNrj8DWcg== Date: Fri, 1 Oct 2021 11:46:07 +0000 Message-ID: <7071c56da1d54b26a2b6f6c0788759e7@exmbx04.ofis.int> Accept-Language: en-GB, en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-ms-exchange-transport-fromentityheader: Hosted x-originating-ip: [213.153.245.158] Content-Type: multipart/alternative; boundary="_000_7071c56da1d54b26a2b6f6c0788759e7exmbx04ofisint_" MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_7071c56da1d54b26a2b6f6c0788759e7exmbx04ofisint_ Content-Type: text/plain; charset="iso-8859-9" Content-Transfer-Encoding: quoted-printable Hello, We use psqlODBC to access Postgres in our MFC C source on Windows. While en= umerating procedure parameters we observe some errors we would like to be s= olved. We have a procedure declared as: public.SP_TEST(INOUT return_value INTEGER, INOUT p1_varchar1024 ACIKLAMA102= 4, 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 =3D adCmdStoredProc; pCommand->CommandText =3D _bstr_t("SP_TEST"); Next, we enumerate the array pCommand->Parameters using pCommand->Parameter= s->Item[_variant_t(i)] and get the following output: Name=3Dreturn_value, Type=3D3, Direction=3D3, Size=3D0 Name=3Dp1_varchar1024, Type=3D200, Direction=3D3, Size=3D255 Name=3Dp2_char20, Type=3D200, Direction=3D3, Size=3D255 Name=3Dp3_int, Type=3D200, Direction=3D3, Size=3D255 Name=3Dp4_smallint, Type=3D200, Direction=3D3, Size=3D255 Name=3Dp5_bit, Type=3D200, Direction=3D3, Size=3D5 Name=3Dp6_float, Type=3D200, Direction=3D3, Size=3D255 Name=3Dp7_datetime, Type=3D200, Direction=3D3, Size=3D255 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 u= nderlying data types: public.SP_TEST(INOUT return_value INTEGER, INOUT p1_varchar1024 VARCHAR(102= 4), 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=3Dreturn_value, Type=3D3, Direction=3D3, Size=3D0 Name=3Dp1_varchar1024, Type=3D200, Direction=3D3, Size=3D255 Name=3Dp2_char20, Type=3D200, Direction=3D3, Size=3D255 Name=3Dp3_int, Type=3D3, Direction=3D3, Size=3D0 Name=3Dp4_smallint, Type=3D2, Direction=3D3, Size=3D0 Name=3Dp5_bit, Type=3D200, Direction=3D3, Size=3D5 Name=3Dp6_float, Type=3D5, Direction=3D3, Size=3D0 Name=3Dp7_datetime, Type=3D135, Direction=3D3, Size=3D0 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=3D{PostgreSQL ANSI};Server=3Dlocalhost;Port=3D5432;= Database=3D...;Uid=3Dpostgres;Pwd=3D..." On the ODBC DSN configuration, the following changes have been made: "Bools as Char" is unchecked "Max VarChar" =3D 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 prob= lems? Thanks in advance. Kamil Adem Aqvila Software Yaz=FDl=FDm A.=DE. --_000_7071c56da1d54b26a2b6f6c0788759e7exmbx04ofisint_ Content-Type: text/html; charset="iso-8859-9" Content-Transfer-Encoding: quoted-printable

Hello,

 

We use psqlODBC to access Postg= res 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= :

pu= blic.SP_TEST(INOUT return_value INTEGER, INOUT p1_varchar1024 ACIKLAMA1024,= INOUT p2_char20 KOD, INOUT p3_int TAMSAYI, INOUT p4_smallint YIL,  IN= OUT p5_bit BOOL, INOUT p6_float MEBLAG, INOUT p7_datetime TARIH)

The parameter types are user cr= eated domains.

We have the following C source = using Microsoft ADODB :

&= nbsp;           &nbs= p;   = _CommandPtr pCommand;

     = ;  pCommand->CommandType =3D adCmdStoredProc;

     = ;  pCommand->CommandText =3D _bstr_t(= "SP_TEST");

Next, we enumerate the array pC= ommand->Parameters using pCommand->Parameters->Item[_variant_t(i)]= and get the following output:

Na= me=3Dreturn_value, Type=3D3, Direction=3D3, Size=3D0

Na= me=3Dp1_varchar1024, Type=3D200, Direction=3D3, Size=3D255

Na= me=3Dp2_char20, Type=3D200, Direction=3D3, Size=3D255

Na= me=3Dp3_int, Type=3D200, Direction=3D3, Size=3D255

Na= me=3Dp4_smallint, Type=3D200, Direction=3D3, Size=3D255

Na= me=3Dp5_bit, Type=3D200, Direction=3D3, Size=3D5

Na= me=3Dp6_float, Type=3D200, Direction=3D3, Size=3D255

Na= me=3Dp7_datetime, Type=3D200, Direction=3D3, Size=3D255

 

The problem is that the t= ypes 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:

pu= blic.SP_TEST(INOUT return_value INTEGER, INOUT p1_varchar1024 VARCHAR(1024)= , INOUT p2_char20 CHAR(20), INOUT p3_int INT, INOUT p4_smallint SMALLINT, &= nbsp;INOUT p5_bit BOOLEAN, INOUT p6_float FLOAT, INOUT p7_datetime TIMESTAMP)

we get the following enumeratio= n result:

Na= me=3Dreturn_value, Type=3D3, Direction=3D3, Size=3D0

Na= me=3Dp1_varchar1024, Type=3D200, Direction=3D3, Size=3D255

Na= me=3Dp2_char20, Type=3D200, Direction=3D3, Size=3D255

Na= me=3Dp3_int, Type=3D3, Direction=3D3, Size=3D0

Na= me=3Dp4_smallint, Type=3D2, Direction=3D3, Size=3D0

Na= me=3Dp5_bit, Type=3D200, Direction=3D3, Size=3D5

Na= me=3Dp6_float, Type=3D5, Direction=3D3, Size=3D0

Na= me=3Dp7_datetime, Type=3D135, Direction=3D3, Size=3D0

 

This time the types are r= eported correctly (with the exception of boolean), but the size field for c= haracter types are still incorrect!

 

We use the following connection= string:

     &= nbsp;          "Driver=3D= {PostgreSQL ANSI};Server=3Dlocalhost;Port=3D5432;Database=3D…;Uid=3Dp= ostgres;Pwd=3D…"

On the ODBC DSN configuration, = the following changes have been made:

     &= nbsp;          “Bools as= Char” is unchecked

     &= nbsp;          “Max VarC= har” =3D 1036

 

We have a generic interface for= calling stored procedures in our code being migrated from MSSQL to Postgre= SQL. So we need to enumerate the parameters before calling the procedure to= generate the parameter list.

 

How can we get rid of these iss= ues? How can you help us to solve these problems?

 

Thanks in advance.

 

Kamil Adem

Aqvila Software Yaz=FDl=FDm A.= =DE.

 

 

--_000_7071c56da1d54b26a2b6f6c0788759e7exmbx04ofisint_--