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 1mR0gp-0008UA-Sr for pgsql-odbc@arkaria.postgresql.org; Thu, 16 Sep 2021 23:21:12 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1mR0go-0007on-J2 for pgsql-odbc@arkaria.postgresql.org; Thu, 16 Sep 2021 23:21:10 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mR0gn-0007of-Va for pgsql-odbc@lists.postgresql.org; Thu, 16 Sep 2021 23:21:10 +0000 Received: from mx08-00574e01.pphosted.com ([185.183.31.38]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1mR0gi-00005l-2u for pgsql-odbc@postgresql.org; Thu, 16 Sep 2021 23:21:08 +0000 Received: from pps.filterd (m0214000.ppops.net [127.0.0.1]) by mx08-00574e01.pphosted.com (8.16.1.2/8.16.1.2) with SMTP id 18GKg7p5011647 for ; Fri, 17 Sep 2021 00:21:01 +0100 Received: from mail-wm1-f70.google.com (mail-wm1-f70.google.com [209.85.128.70]) by mx08-00574e01.pphosted.com with ESMTP id 3b43e494j3-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128 verify=NOT) for ; Fri, 17 Sep 2021 00:21:00 +0100 Received: by mail-wm1-f70.google.com with SMTP id j21-20020a05600c1c1500b00300f1679e4dso3149797wms.4 for ; Thu, 16 Sep 2021 16:21:00 -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=wLx/cZpQVHC0PRFKHDUZnOlCZhMVSn0w7qzV0TOoLPM=; b=T3o76/y2uMhuZBHyZH+FZwt+OGE1gvYP/cIMswQKAcorP1D3yW+nv+83GWsZXtXw2I C0hfrxETCBmVA7PChEx0a7h+jMRwk/PVWDmm5D+Ph/t1ZfskKh4DfF2JVfw4zsmkqfg9 /VU+Op9ceNAcVcSDe8RGUgRubisCFUH0xJTkWzUQ9DJFu13hB/XJNoBWVjJRugPGirSP gbO7thE8xyQPTMPVK8MaCVDxGGvqHczFUqQaw2NcSs+HEMbQCBG7O1j8zxmO3x0pZ6Za j2M0lZPOiEXwtqsX/7G/gT6xcypDCZ+7CJzNvhAAIEXfKeA2vr5WQzqkl2HPrDdjb0ff Ga5Q== 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=wLx/cZpQVHC0PRFKHDUZnOlCZhMVSn0w7qzV0TOoLPM=; b=JPMAKmgODIOa96fqmPGEznLjEcF6EQ+pyFVpHG2or1sjlOBcRFcq5p9r73ZDZfWbS2 qFHX9zpVB8L7YaAdsZVOHxoySbsLgXtqzk1Y1cwBqcX9QleU8mdv1LHRD1PrcdzIK2OG 9BnCas1mTlgujKFcXi3kQCwnTidzxMe7BhwZU3s8fgBw3A7vaGJGSHmD8Kn5LjoEYRSy IvigYUTXWpPLfRtZSmnP890mBpbo11KklyZMXUdwixTW/IvI/6p1km1OHX+tLQJY689g /5YbD7MB+ZMBeVSrMsCFtNK3p4ctmbwJNEqWMr3Pin2ylWLuo1p+DJAmOFDZWMylnxrn RCQA== X-Gm-Message-State: AOAM530YW0okuvY+WoEZtKoTF7fbIbwxuq6sUrrbRQwZZFLXSeSl9uBy q8zxoLSasgDTVCizdNT6xA+tfgdI+LKngWVDyF8tlBsyxRWgqk49acuWIlW9YUbXtwUdGrCU/Cv ZPI4X/+V1/+v6JXi6lr0vJRqkzzkdYg27aiJw7iYtpyuoEQ== X-Received: by 2002:adf:fe0b:: with SMTP id n11mr8807202wrr.371.1631834460025; Thu, 16 Sep 2021 16:21:00 -0700 (PDT) X-Google-Smtp-Source: ABdhPJxEDd9wrCQ2IxzLkIQL6juqh/4DnynDtXINbg3sZnyUrmVFp9anMuwTt7LGnZeirN2oct4rX+6cbZYs X-Received: by 2002:adf:fe0b:: with SMTP id n11mr8807187wrr.371.1631834459711; Thu, 16 Sep 2021 16:20:59 -0700 (PDT) Received: from eu2.smtp.exclaimer.net (eu2.smtp.exclaimer.net. [52.169.0.179]) by smtp-relay.gmail.com with ESMTPS id v2sm179597wra.9.2021.09.16.16.20.59 for (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Thu, 16 Sep 2021 16:20:59 -0700 (PDT) X-Relaying-Domain: iress.com Received: from mail-wr1-f71.google.com (209.85.221.71) by eu2.smtp.exclaimer.net (52.169.0.179) with Exclaimer Signature Manager ESMTP Proxy eu2.smtp.exclaimer.net (tlsversion=TLS12, tlscipher=TLS_ECDHE_WITH_AES256_SHA1); Thu, 16 Sep 2021 23:20:59 +0000 X-ExclaimerHostedSignatures-MessageProcessed: true X-ExclaimerProxyLatency: 5397188 X-ExclaimerImprintLatency: 2489924 X-ExclaimerImprintAction: a8e605f1bd1443928008e1682732164c Received: by mail-wr1-f71.google.com with SMTP id r5-20020adfb1c5000000b0015cddb7216fso3027403wra.3 for ; Thu, 16 Sep 2021 16:20:59 -0700 (PDT) X-Received: by 2002:a5d:61c1:: with SMTP id q1mr8652131wrv.154.1631834458552; Thu, 16 Sep 2021 16:20:58 -0700 (PDT) X-Received: by 2002:a5d:61c1:: with SMTP id q1mr8652103wrv.154.1631834457997; Thu, 16 Sep 2021 16:20:57 -0700 (PDT) MIME-Version: 1.0 References: <994c12dd0d6348d389246fda802fff07@exmbx03.ofis.int> In-Reply-To: From: Adrian Grucza Date: Fri, 17 Sep 2021 09:20:46 +1000 Message-ID: Subject: Re: Problem on calling procedures with ADODB To: Kamil ADEM Cc: "pgsql-odbc@postgresql.org" Content-Type: multipart/related; boundary="00000000000033ebfd05cc251176" X-Proofpoint-GUID: 4Nw6D1wcK2YYxjCNVZXn-Bi3-0Ec_D46 X-Proofpoint-ORIG-GUID: 4Nw6D1wcK2YYxjCNVZXn-Bi3-0Ec_D46 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. --00000000000033ebfd05cc251176 Content-Type: multipart/alternative; boundary="00000000000033ebfc05cc251175" --00000000000033ebfc05cc251175 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hi Kamil, Yes I also found that output parameters were not processed when calling procedures. In May I included a fix for this in the below commit, but there has not been a release of psqlODBC since then. https://git.postgresql.org/gitweb/?p=3Dpsqlodbc.git;a=3Dcommit;h=3D241c70bf= 6516bf08770fabcb1b86934c8da116c8 Until this change is released, you would have to build your own version of the driver as per https://odbc.postgresql.org/docs/win32-compilation.html But I do hope there will be a new release soon, as I too am keen to have an official release that contains this fix. Adrian Grucza Technical Lead Tel: +61390185800 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=8B=E2=80=8BIf you have received this email in error please notify th= e sender immediately and delete this email. On Thu, 16 Sept 2021 at 23:23, Kamil ADEM wrote= : > > > *CAUTION: *This Email is from an EXTERNAL source. Ensure you trust this > sender before clicking on any links or attachments. > > > Hi Adrian, > > > > Thank you very much for your support. > > > > Yes, I tried setting CommandText as you propose and succeeded to call the > procedure. But I got another minor problem this time. I hope you have a > solution for this too. =F0=9F=98=8A > > To be more clear, here are the source codes: > > Postgres procedure: > > CREATE OR REPLACE PROCEDURE public.SP_TEST(INOUT VALUE_INOUT INTEGER, IN > USERNAME VARCHAR(50)) > > LANGUAGE plpgsql > > AS $$ > > BEGIN > > insert into tohal_kullanici (satis_faturasi_sira_no, ad) > values (VALUE_INOUT, USERNAME); > > VALUE_INOUT :=3D 20; > > RETURN; > > END; $$; > > MFC code: > > _CommandPtr pCommand; > > pCommand->CommandType =3D adCmdText; > > pCommand->CommandText =3D _bstr_t("CALL SP_TEST(?, ?)"); > > pCommand->Parameters->Append(pCommand->CreateParameter(_bstr_t("$1"), > adInteger, adParamInputOutput, 0)); > > pCommand->Parameters->Append(pCommand->CreateParameter(_bstr_t("$2"), > adVarChar, adParamInput, 255)); > > pCommand->Parameters->Item[_variant_t((long)1)]->Value =3D > _variant_t(CString("Test10")); > > pCommand->Parameters->Item[_variant_t((long)0)]->Value =3D > _variant_t((long)10); > > pCommand->Execute(NULL, NULL, adCmdText); > > > > The procedure is called and the parameter values are passed correctly to > the procedure. But the first parameter value is not returned to the C cod= e, > the value set before Execute() remains unchanged. > > Do you have any idea about the reason of this case? > > > > Thanks in advance. > > > > Kamil Adem > > > > > > *From:* Adrian Grucza > *Sent:* Thursday, September 16, 2021 2:07 PM > *To:* Kamil ADEM > *Cc:* pgsql-odbc@postgresql.org > *Subject:* Re: Problem on calling procedures with ADODB > > > > Hi Kamil, > > > > Have you tried changing pCommand->CommandText to _bstr_t("CALL sp_TEST(?, > ?, ?)"), with one question mark per parameter? > > > > [image: Image removed by sender. iress.com] > > *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 > 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 Thu, 16 Sept 2021 at 17:49, 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 porting a Windows MFC application from MSSQLServer to PostgreSQL and > trying to use psqlODBC driver with Microsoft ADODB. > > We are currently performing the migration steps of our C sources and got > stuck on an issue and thought to ask for your help. > > > > We use Microsoft ADODB on Windows to access the database and cannot chang= e > this interface in short time. To access Postgres we changed the connectio= n > string accordingly. (e.g. =E2=80=9CDriver=3D {PostgreSQL ANSI};=E2=80=9D) > > On calling Postgres procedures we have the following code sample: > > _CommandPtr pCommand; > > pCommand->CommandType =3D adCmdStoredProc; > > pCommand->CommandText =3D _bstr_t(=E2=80=9Csp_TEST=E2=80=9D); > > pCommand->Parameters->Refresh(); > > pCommand->Execute(NULL, NULL, adCmdStoredProc | adExecuteNoRecords); > > The Execute() method generates the command: =E2=80=9CSELECT * FROM sp_TES= T(=E2=80=A6)=E2=80=9D > instead of =E2=80=9CCALL sp_TEST(=E2=80=A6)=E2=80=9D. This is appropriate= for Postgres functions, > but there must be a way to call procedures also. > > Do you know such a reported issue? Do you know a way to change this > behaviour? Should we use a different driver? Should we get rid of > procedures and convert our MSSQL stored procedures to Postgres functions? > > > > We would be grateful if you can guide us to the right solution. > > Thanks in advance. > > > > Best regards, > > > > Kamil Adem > > Aqvila Software Yaz=C4=B1l=C4=B1m A.=C5=9E. > > > > > > > > --00000000000033ebfc05cc251175 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable
Hi=C2=A0Kamil,

Yes I also found that output = parameters were not processed when calling procedures. In May I included a = fix for this in the below commit, but there has not been a release of psqlO= DBC since then.


Un= til this change is released, you would have to build your own version of th= e driver as per=C2=A0https://odbc.postgresql.org/docs/win32-compilation.html

But I do hope there will be a new release soon,= as I too am keen to have an official release that contains this fix.
=



=
=
3D""
A= drian Grucza​
Technical Lead
Tel: +61390185800
=
= =
The contents of this email originated from Iress. Fo= r this purpose Iress includes Iress Limited and/or any of its subsidiaries,= holding companies and trading entities.
​​If you have rec= eived this email in error please notify the sender immediately and delete t= his email. 
On Thu, 16 Sept 2021 at 23:23, Kamil ADEM <kamila@aqvilasoftware.com> wro= te:


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


Hi Adrian,=

=C2=A0

Thank you very much for your su= pport.

=C2=A0

Yes, I tried setting CommandTex= t as you propose and succeeded to call the procedure. But I got another min= or problem this time. I hope you have a solution for this too. =F0=9F=98=8A<= /p>

To be more clear, here are the = source codes:

Postgres procedure:

CR= EATE OR REPLACE PROCEDURE public.SP_TEST(INOUT VALUE_INOUT INTEGER, IN USER= NAME VARCHAR(50))

LA= NGUAGE plpgsql

= =C2=A0=C2=A0 AS $$

BE= GIN

= =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 insert into tohal_kullanici (satis_faturasi_sira_no, ad)= values (VALUE_INOUT, USERNAME);

= =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 VALUE_INOUT :=3D 20;

= =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 RETURN;

EN= D; $$;

MFC code:<= /p>

_C= ommandPtr pCommand;

pC= ommand->CommandType =3D adCmdText;

pC= ommand->CommandText =3D _bstr_t("CALL SP_TEST(?, ?)");<= u>

pC= ommand->Parameters->Append(pCommand->CreateParameter(_bstr_t("= ;$1"), adInteger, adParamInputOutput, 0));

pC= ommand->Parameters->Append(pCommand->CreateParameter(_bstr_t("= ;$2"), adVarChar, adParamInput, 255));

pC= ommand->Parameters->Item[_variant_t((long)1)]->Value =3D _variant_= t(CString("Test10"));

pC= ommand->Parameters->Item[_variant_t((long)0)]->Value =3D _variant_= t((long)10);

pC= ommand->Execute(NULL, NULL, adCmdText);

=C2=A0

The procedure is called and the= parameter values are passed correctly to the procedure. But the first para= meter value is not returned to the C code, the value set before Execute() r= emains unchanged.

Do you have any idea about the = reason of this case?

=C2=A0

Thanks in advance.

=C2=A0

Kamil Adem=

=C2=A0

=C2=A0

From: Adrian Grucza <adrian.grucza@iress.com>
Sent: Thursday, September 16, 2021 2:07 PM
To: Kamil ADEM <kamila@aqvilasoftware.com>
Cc: p= gsql-odbc@postgresql.org
Subject: Re: Problem on calling procedures with ADODB<= /span>

=C2=A0

Hi Kamil,

=C2=A0

Have you tried changing pCommand->CommandText to = _bstr_t("CALL sp_TEST(?, ?, ?)"), with one question mark per para= meter?

=C2=A0

3D"Image

Adrian=C2=A0Grucza<= b>=C2=A0=C2=A0

Technical=C2=A0Lead

Tel:=C2=A0

+61390185800

adrian.grucza@ires= s.com

www.iress.com

Level=C2=A016=C2=A0385=C2=A0Bourke=C2=A0St=

=C2=A0Melbourne,=C2=A0

Victoria,=C2=A0

3000

T= he contents of this email originated from Iress. For this purpose Iress inc= ludes Iress Limited and/or any of its subsidiaries, holding companies and trading enti= ties. If you have received this email in error please notify the sender imm= ediately and delete this email.=C2=A0

nosig

On Thu, 16 Sept 2021 at 17:49, Kamil ADEM <kamila@aqvilasoftw= are.com> wrote:

=C2=A0

CAUTION: This Email is from an EXTERNAL source. Ensure you trust this sen= der before clicking on any links or attachments.

=C2=A0

Hello,

=C2=A0

We porting a Windows MFC applic= ation from MSSQLServer to PostgreSQL and trying to use psqlODBC driver with= Microsoft ADODB.

We are currently performing the= migration steps of our C sources and got stuck on an issue and thought to = ask for your help.

=C2=A0

We use Microsoft ADODB on Windo= ws to access the database and cannot change this interface in short time. T= o access Postgres we changed the connection string accordingly. (e.g. =E2=80=9CDriver=3D {PostgreSQL ANSI};=E2=80=9D)=

On calling Postgres procedures = we have the following code sample:

_CommandPtr pCommand;

pCommand->CommandType =3D adCmdStoredProc;

pCommand->CommandText =3D _bstr_t(=E2=80=9Csp_TEST= =E2=80=9D);

pCommand->Parameters->Refresh();

pCommand->Execute(NULL, NULL, adCmdStoredProc | adE= xecuteNoRecords);

The Execute() method generates = the command: =E2=80=9CSELECT * FROM sp_TEST(=E2=80=A6)=E2=80=9D instead of = =E2=80=9CCALL sp_TEST(=E2=80=A6)=E2=80=9D. This is appropriate for Postgres= functions, but there must be a way to call procedures also.

Do you know such a reported iss= ue? =C2=A0Do yo= u know a way to change this behaviour? Should we use a different driver? Sh= ould we get rid of procedures and convert our MSSQL stored procedures to Po= stgres functions?

=C2=A0

We would be grateful if you can guide us to the = right solution.

Thanks in advance.

=C2=A0

Best regards,<= /u>

=C2=A0

Kamil Adem=

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

=C2=A0

=C2=A0

=C2=A0

--00000000000033ebfc05cc251175-- --00000000000033ebfd05cc251176 Content-Type: image/jpeg; name="~WRD0002.jpg" Content-Disposition: inline; filename="~WRD0002.jpg" Content-Transfer-Encoding: base64 Content-ID: <17bf0e09d1cbeff1c801> X-Attachment-Id: 17bf0e09d1cbeff1c801 /9j/4AAQSkZJRgABAQEAYABgAAD/2wBDAAgGBgcGBQgHBwcJCQgKDBQNDAsLDBkSEw8UHRofHh0a HBwgJC4nICIsIxwcKDcpLDAxNDQ0Hyc5PTgyPC4zNDL/2wBDAQkJCQwLDBgNDRgyIRwhMjIyMjIy MjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjL/wAARCABkAGQDASIA AhEBAxEB/8QAHwAAAQUBAQEBAQEAAAAAAAAAAAECAwQFBgcICQoL/8QAtRAAAgEDAwIEAwUFBAQA AAF9AQIDAAQRBRIhMUEGE1FhByJxFDKBkaEII0KxwRVS0fAkM2JyggkKFhcYGRolJicoKSo0NTY3 ODk6Q0RFRkdISUpTVFVWV1hZWmNkZWZnaGlqc3R1dnd4eXqDhIWGh4iJipKTlJWWl5iZmqKjpKWm p6ipqrKztLW2t7i5usLDxMXGx8jJytLT1NXW19jZ2uHi4+Tl5ufo6erx8vP09fb3+Pn6/8QAHwEA AwEBAQEBAQEBAQAAAAAAAAECAwQFBgcICQoL/8QAtREAAgECBAQDBAcFBAQAAQJ3AAECAxEEBSEx BhJBUQdhcRMiMoEIFEKRobHBCSMzUvAVYnLRChYkNOEl8RcYGRomJygpKjU2Nzg5OkNERUZHSElK U1RVVldYWVpjZGVmZ2hpanN0dXZ3eHl6goOEhYaHiImKkpOUlZaXmJmaoqOkpaanqKmqsrO0tba3 uLm6wsPExcbHyMnK0tPU1dbX2Nna4uPk5ebn6Onq8vP09fb3+Pn6/9oADAMBAAIRAxEAPwD3+iii gAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKA CiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAK KKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiiigAoo ooAKKKKACiiigAooooAKKKKACiiigD//2Q== --00000000000033ebfd05cc251176--