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 1mWHCu-0005id-1c for pgsql-odbc@arkaria.postgresql.org; Fri, 01 Oct 2021 12:00:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mWHCs-00035M-Vy for pgsql-odbc@arkaria.postgresql.org; Fri, 01 Oct 2021 12:00:02 +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 1mWHCs-00035D-Ij for pgsql-odbc@lists.postgresql.org; Fri, 01 Oct 2021 12:00:02 +0000 Received: from mx08-00574e01.pphosted.com ([185.183.31.38]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mWHCn-0007MQ-Gn for pgsql-odbc@postgresql.org; Fri, 01 Oct 2021 12:00:01 +0000 Received: from pps.filterd (m0213998.ppops.net [127.0.0.1]) by mx08-00574e01.pphosted.com (8.16.1.2/8.16.1.2) with SMTP id 191AfBld023511 for ; Fri, 1 Oct 2021 12:59:53 +0100 Received: from mail-ed1-f72.google.com (mail-ed1-f72.google.com [209.85.208.72]) by mx08-00574e01.pphosted.com with ESMTP id 3be0sh025b-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128 verify=NOT) for ; Fri, 01 Oct 2021 12:59:53 +0100 Received: by mail-ed1-f72.google.com with SMTP id e7-20020a50d4c7000000b003d871ecccd8so10110254edj.18 for ; Fri, 01 Oct 2021 04:59:53 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=iress-com.20210112.gappssmtp.com; s=20210112; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=o2TFIKjPSXSQB2Uad//errCDi8gESBix8ULvwRXiVBw=; b=39HHJqaefkoIC3GdGl76XvS54gMrdmWOzye5BziYYwtFPUZWnSglmqTrn1nzE51blm l5Sz1VuPYtSUXzwHhKBP5bcbQpBtXVzin05K5adXBYBLKt0BLpsGfJ2YJtBAiJVaC4Ug sgQynNEo64z136jUQnjUFt+iM2pqAE4juUCw3Nk2uLiP5Sg4RqqJuSKcqnpdNhbuOtDL 6cSauc77d2PEnud5DKKYx569Zw7cPbHzoeQ6alV6NVFWiHq4pdtUuQC2YpIxk5krYC4Y FatUvv7U/beUQl1dZ+m3I8pXlUA/n43KJvU+UeU7BQV1zGM4RU9xqXofWmTccsjXkK3H 4pOA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=o2TFIKjPSXSQB2Uad//errCDi8gESBix8ULvwRXiVBw=; b=St0/sQ3x+vrQMeWucaLGw0Qq2siATRdzLQ5qkmgna0qixagY1e8MswgXizaXnf9GVV cAqzwuhEpCUe8vFCxE2VaRsKGjbBbhktcQJxJmMk0j8UmrsAYExk4v+/FVv8DEtVEL+S CG8fWisuNbx1JfMSnd93Fun51AZysIQJ+qnfJbXxq5ktYroB7nJ50VdUgGjuZrs1J66Z 6+FLOek9BoHBoMdtm+k5Kjr4yXzz8CzDg4x369l6MFs5qLISpc253fmBEbHBBhqLnxY0 O//rPTgP6lutxowsvbzOgcNh5hxFIZg/of7EnDREBGBFMJgvcxhMJvRYtVT5i8igaWBh oRyQ== X-Gm-Message-State: AOAM530wyaIBFVOhA7/7rHV6pROmAB+QBZ39txGVKrzXF8uEwJNUWzzD N2gj+aBeXpIwrdyPVXV514xwEZJvrbGNYl/w875K/HxjiSdO76uf+p5S1UJ8FDp0YqESVorYDrI fFVM2Q7nHaH5/mkwDBxPQE5K3L8NdekuThFce5QvcgZDLVg== X-Received: by 2002:aa7:db8a:: with SMTP id u10mr2951047edt.189.1633089592740; Fri, 01 Oct 2021 04:59:52 -0700 (PDT) X-Google-Smtp-Source: ABdhPJzwV8hP0iBTRLDTy8N8z6OihrtB7t4fsu2NjhzPDMw1kF+2avFKeu7t6nxnZjomwbqMATNl7U2mIbcU X-Received: by 2002:aa7:db8a:: with SMTP id u10mr2951026edt.189.1633089592465; Fri, 01 Oct 2021 04:59:52 -0700 (PDT) Received: from eu1.smtp.exclaimer.net (eu1.smtp.exclaimer.net. [104.40.229.156]) by smtp-relay.gmail.com with ESMTPS id hg11sm79972ejc.162.2021.10.01.04.59.51 for (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Fri, 01 Oct 2021 04:59:52 -0700 (PDT) X-Relaying-Domain: iress.com Received: from mail-wr1-f71.google.com (209.85.221.71) by eu1.smtp.exclaimer.net (104.40.229.156) with Exclaimer Signature Manager ESMTP Proxy eu1.smtp.exclaimer.net (tlsversion=TLS12, tlscipher=TLS_ECDHE_WITH_AES256_SHA1); Fri, 1 Oct 2021 11:59:52 +0000 X-ExclaimerHostedSignatures-MessageProcessed: true X-ExclaimerProxyLatency: 6071972 X-ExclaimerImprintLatency: 4068632 X-ExclaimerImprintAction: fed251d1d20a47a490f3f5b9958e9739 Received: by mail-wr1-f71.google.com with SMTP id f11-20020adfc98b000000b0015fedc2a8d4so2776468wrh.0 for ; Fri, 01 Oct 2021 04:59:51 -0700 (PDT) X-Received: by 2002:a05:600c:510a:: with SMTP id o10mr3959446wms.81.1633089591373; Fri, 01 Oct 2021 04:59:51 -0700 (PDT) X-Received: by 2002:a05:600c:510a:: with SMTP id o10mr3959424wms.81.1633089591060; Fri, 01 Oct 2021 04:59:51 -0700 (PDT) MIME-Version: 1.0 References: <7071c56da1d54b26a2b6f6c0788759e7@exmbx04.ofis.int> In-Reply-To: <7071c56da1d54b26a2b6f6c0788759e7@exmbx04.ofis.int> From: Adrian Grucza Date: Fri, 1 Oct 2021 21:59:38 +1000 Message-ID: Subject: Re: Procedure parameter list enumeration error To: Kamil ADEM Cc: "pgsql-odbc@postgresql.org" , Haluk DALKIRAN Content-Type: multipart/alternative; boundary="000000000000f6521005cd494cb6" X-Proofpoint-GUID: DilAmvS9b2SQapPrg85dy0gYikzPbPFP X-Proofpoint-ORIG-GUID: DilAmvS9b2SQapPrg85dy0gYikzPbPFP List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --000000000000f6521005cd494cb6 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable 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 adrian.grucza@iress.com www.iress.com Level 16 385 Bourke St Melbourne, Victoria, 3000 The contents of this email originated from Iress. For this purpose Iress in= cludes Iress Limited and/or any of its subsidiaries, holding companies and = trading entities. =E2=80=8BIf 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 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_smallin= t > 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->Parameters->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 > 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=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=E2=80=A6;Uid=3Dpostgres;= Pwd=3D=E2=80=A6" > > On the ODBC DSN configuration, the following changes have been made: > > =E2=80=9CBools as Char=E2=80=9D is unchecked > > =E2=80=9CMax VarChar=E2=80=9D =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 > problems? > > > > Thanks in advance. > > > > Kamil Adem > > Aqvila Software Yaz=C4=B1l=C4=B1m A.=C5=9E. > > > > > --000000000000f6521005cd494cb6 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
Hi Kamil,

Not sure about the domain types and the Boole= an, but for the sizes, PostgreSQL does not store string lengths on procedur= e parameters. If you provide them when creating the procedure they are disc= arded without error (see the Notes section of=C2=A0https://www.postgresql.= org/docs/current/sql-createfunction.html). The procedure will accept st= rings of any length.

3D"iress.com"
= =
Adrian Grucza​  
Technical Lead
= +61390185800
Tel: 
adrian.grucza@iress.com
www.iress.com
Level 16 385 Bourke St
 = ;Melbourne, Victoria, 3000
<= /tr>
The contents of this email originated from I= ress. For this purpose Iress includes Iress Limited and/or any of its subsi= diaries, holding companies and trading entities. ​If you have receive= d this email in error please notify the sender immediately and delete this = email. 
nosig
On Fri, 1= Oct 2021 at 21:45, Kamil ADEM <kamila@aqvilasoftware.com> wrote:


CAU= TION: This Email is from an EXTERNAL source. Ensure you trust th= is sender before clicking on any links or attachments.


Hello,

=C2=A0

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.

=C2=A0

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, =C2=A0IN= 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 :

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 _C= ommandPtr pCommand;

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 pCommand->CommandType= =3D adCmdStoredProc;

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 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<= /p>

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

=C2=A0

The problem is that the t= ypes and sizes of the user created domains are not reported correctly!

=C2=A0

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, = =C2=A0INOUT 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<= /p>

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<= /p>

=C2=A0

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

=C2=A0

We use the following connection= string:

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 "Driver= =3D{PostgreSQL ANSI};Server=3Dlocalhost;Port=3D5432;Database=3D=E2=80=A6;Ui= d=3Dpostgres;Pwd=3D=E2=80=A6"

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

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =E2=80=9CBools= as Char=E2=80=9D is unchecked

=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 =E2=80=9CMax V= arChar=E2=80=9D =3D 1036

=C2=A0

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.

=C2=A0

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

=C2=A0

Thanks in advance.

=C2=A0

Kamil Adem=

Aqvila Software Yaz=C4=B1l=C4= =B1m A.=C5=9E.

=C2=A0

=C2=A0

--000000000000f6521005cd494cb6--