Subject: Problem on calling procedures with ADODB

From: Kamil ADEM <[email-masked]>
Date: Thu, 16 Sep 2021 07:49:34 +0000

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 change this interface in short time. To access Postgres we changed the connection string accordingly. (e.g. "Driver= {PostgreSQL ANSI};")
On calling Postgres procedures we have the following code sample:
_CommandPtr pCommand;
pCommand->CommandType = adCmdStoredProc;
pCommand->CommandText = _bstr_t("sp_TEST");
pCommand->Parameters->Refresh();
pCommand->Execute(NULL, NULL, adCmdStoredProc | adExecuteNoRecords);
The Execute() method generates the command: "SELECT * FROM sp_TEST(...)" instead of "CALL sp_TEST(...)". 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ılım A.Ş.

---

From: Adrian Grucza <[email-masked]>
Date: Thu, 16 Sep 2021 21:06:51 +1000

Hi Kamil,

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

Adrian Grucza  
Technical Lead
Tel: +61390185800
[email-masked]
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 <[email-masked]> 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 change
> this interface in short time. To access Postgres we changed the connection
> string accordingly. (e.g. “Driver= {PostgreSQL ANSI};”)
>
> pCommand->CommandText = _bstr_t(“sp_TEST”);
>
> The Execute() method generates the command: “SELECT * FROM sp_TEST(…)”
> instead of “CALL sp_TEST(…)”. 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?
>
>
>

---

From: Kamil ADEM <[email-masked]>
Date: Thu, 16 Sep 2021 13:23:23 +0000

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. 😊
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 := 20;
                RETURN;
END; $$;
MFC code:
_CommandPtr pCommand;
pCommand->CommandType = adCmdText;
pCommand->CommandText = _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 = _variant_t(CString("Test10"));
pCommand->Parameters->Item[_variant_t((long)0)]->Value = _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 code, 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 <[email-masked]>
Sent: Thursday, September 16, 2021 2:07 PM
To: Kamil ADEM <[email-masked]>
Cc: [email-masked]
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 removed by sender. iress.com]<https://www.iress.com/>

Adrian Grucza​

Technical Lead

Tel:

+61390185800

[email-masked]<mailto:[email-masked]>

www.iress.com<https://www.iress.com/>

Level 16 385 Bourke St

 Melbourne,

Victoria,

3000

nosig

On Thu, 16 Sept 2021 at 17:49, Kamil ADEM <[email-masked]<mailto:[email-masked]>> wrote:

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

Hello,

We use Microsoft ADODB on Windows to access the database and cannot change this interface in short time. To access Postgres we changed the connection string accordingly. (e.g. “Driver= {PostgreSQL ANSI};”)
On calling Postgres procedures we have the following code sample:
_CommandPtr pCommand;
pCommand->CommandType = adCmdStoredProc;
pCommand->CommandText = _bstr_t(“sp_TEST”);
pCommand->Parameters->Refresh();
pCommand->Execute(NULL, NULL, adCmdStoredProc | adExecuteNoRecords);
The Execute() method generates the command: “SELECT * FROM sp_TEST(…)” instead of “CALL sp_TEST(…)”. 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?

Best regards,

Kamil Adem

---

From: Adrian Grucza <[email-masked]>
Date: Fri, 17 Sep 2021 09:20:46 +1000

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=psqlodbc.git;a=commit;h=241c70bf6516bf08770fabcb1b86934c8da116c8

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 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. 
On Thu, 16 Sept 2021 at 23:23, Kamil ADEM <[email-masked]> wrote:

> 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. 😊
>
> 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 code,
> the value set before Execute() remains unchanged.
>
> *From:* Adrian Grucza <[email-masked]>
> *Sent:* Thursday, September 16, 2021 2:07 PM
> *To:* Kamil ADEM <[email-masked]>
> *Cc:* [email-masked]
> *Subject:* Re: Problem on calling procedures with ADODB
>
>
>
> Hi Kamil,
>
>
>
>  <https://www.iress.com/>
> *Adrian Grucza*
> [email-masked]* <[email-masked]>
>
> *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
>

---

From: Kamil ADEM <[email-masked]>
Date: Fri, 17 Sep 2021 07:42:54 +0000

Hi Adrian,

Thank you for your comment.
I will work for our own version to include your fix. I hope this fix has no dependencies to other fixes not included in the source code I have. I think I can ask for help from you if I get a problem when building and testing.

We also are very keen to have a recent release that contains all the recent fixes. We are going to publish our old application with a new PostgreSQL interface and want to have a solid working environment.
How can we follow the release schedule? Is there an automatic mechanism or can you inform us when a new release comes out?

Kamil Adem

Sent: Friday, September 17, 2021 2:21 AM

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.

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.

[Image removed by sender.]

Adrian Grucza​

Technical Lead

Tel:

+61390185800

On Thu, 16 Sept 2021 at 23:23, Kamil ADEM <[email-masked]<mailto:[email-masked]>> wrote:

Hi Adrian,

Thanks in advance.

Kamil Adem

From: Adrian Grucza <[email-masked]<mailto:[email-masked]>>
Sent: Thursday, September 16, 2021 2:07 PM
To: Kamil ADEM <[email-masked]<mailto:[email-masked]>>
Cc: [email-masked]<mailto:[email-masked]>
Subject: Re: Problem on calling procedures with ADODB

Hi Kamil,

Adrian Grucza

Technical Lead

Tel:

+61390185800

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

Hello,

Best regards,

Kamil Adem

---

From: Adrian Grucza <[email-masked]>
Date: Fri, 17 Sep 2021 20:47:22 +1000

Hi Kamil,

That particular commit does actually depend on the previous commit. But to
fix your particular problem, you should only need the below change (which
doesn't have dependencies):

--- a/statement.c
<https://git.postgresql.org/gitweb/?p=psqlodbc.git;a=blob;f=statement.c;h=9f47b40fa3e0c38da719049d29943c9667642c9d>
+++ b/statement.c
<https://git.postgresql.org/gitweb/?p=psqlodbc.git;a=blob;f=statement.c;h=a42f7ca157489e41108f491d4581688a7094bc7b;hb=241c70bf6516bf08770fabcb1b86934c8da116c8>
@@ -56,6
<https://git.postgresql.org/gitweb/?p=psqlodbc.git;a=blob;f=statement.c;h=9f47b40fa3e0c38da719049d29943c9667642c9d#l56>
+56,9
<https://git.postgresql.org/gitweb/?p=psqlodbc.git;a=blob;f=statement.c;h=a42f7ca157489e41108f491d4581688a7094bc7b;hb=241c70bf6516bf08770fabcb1b86934c8da116c8#l56>
@@ static const struct
    ,{
        STMT_TYPE_DELETE, "DELETE"
    }
+   ,{
+       STMT_TYPE_PROCCALL, "CALL"
+   }
    ,{
        STMT_TYPE_PROCCALL, "{"
    }

Alternatively, just build with the latest code from the master branch.

I'm not part of the psqlODBC team so I'm not aware of the release schedule.
But whenever a new version is released, a message is posted on this mailing
list, so just stay subscribed.

Adrian Grucza
Technical Lead
Tel: +61390185800
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. 
On Fri, 17 Sept 2021 at 17:43, Kamil ADEM <[email-masked]> wrote:

> I will work for our own version to include your fix. I hope this fix has
> no dependencies to other fixes not included in the source code I have. I
> think I can ask for help from you if I get a problem when building and
> testing.
>
>
>
> We also are very keen to have a recent release that contains all the
> recent fixes. We are going to publish our old application with a new
> PostgreSQL interface and want to have a solid working environment.
>
> How can we follow the release schedule? Is there an automatic mechanism or
> can you inform us when a new release comes out?
>
>
>
> Kamil Adem
>
> *Sent:* Friday, September 17, 2021 2:21 AM
> <https://urldefense.com/v3/__https://git.postgresql.org/gitweb/?p=psqlodbc.git;a=commit;h=241c70bf6516bf08770fabcb1b86934c8da116c8__;!!OSkwEA!wqnOOfEzkbgIK-Th03S7-naM-yP_2luficMfLQW2b2dxa1cjwof1c7PKqw1bvMrnmQ$>
>
>
>
> <https://urldefense.com/v3/__https://odbc.postgresql.org/docs/win32-compilation.html__;!!OSkwEA!wqnOOfEzkbgIK-Th03S7-naM-yP_2luficMfLQW2b2dxa1cjwof1c7PKqw3PSwiPhQ$>
>
>
>
> 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
>
> and trading entities.
> If you have received this email in error please notify the sender
> immediately and delete this email.
>
> *Adrian Grucza  *

---

From: Kamil ADEM <[email-masked]>
Date: Fri, 17 Sep 2021 11:01:01 +0000

Hi Adrian,

Thank you very much for your precious support. I will follow the steps you suggested.

Have a nice day!

Kamil Adem

From: Adrian Grucza <[email-masked]>
Sent: Friday, September 17, 2021 1:47 PM
To: Kamil ADEM <[email-masked]>
Cc: [email-masked]; Haluk DALKIRAN <[email-masked]>
Subject: Re: Problem on calling procedures with ADODB

Hi Kamil,

That particular commit does actually depend on the previous commit. But to fix your particular problem, you should only need the below change (which doesn't have dependencies):

--- a/statement.c<https://git.postgresql.org/gitweb/?p=psqlodbc.git;a=blob;f=statement.c;h=9f47b40fa3e0c38da719049d29943c9667642c9d>
+++ b/statement.c<https://git.postgresql.org/gitweb/?p=psqlodbc.git;a=blob;f=statement.c;h=a42f7ca157489e41108f491d4581688a7094bc7b;hb=241c70bf6516bf08770fabcb1b86934c8da116c8>
@@ -56,6<https://git.postgresql.org/gitweb/?p=psqlodbc.git;a=blob;f=statement.c;h=9f47b40fa3e0c38da719049d29943c9667642c9d#l56> +56,9<https://git.postgresql.org/gitweb/?p=psqlodbc.git;a=blob;f=statement.c;h=a42f7ca157489e41108f491d4581688a7094bc7b;hb=241c70bf6516bf08770fabcb1b86934c8da116c8#l56> @@ static const struct
    ,{
        STMT_TYPE_DELETE, "DELETE"
    }
+   ,{
+       STMT_TYPE_PROCCALL, "CALL"
+   }
    ,{
        STMT_TYPE_PROCCALL, "{"
    }

I'm not part of the psqlODBC team so I'm not aware of the release schedule. But whenever a new version is released, a message is posted on this mailing list, so just stay subscribed.

Adrian Grucza​

Technical Lead

Tel:

+61390185800

On Fri, 17 Sept 2021 at 17:43, Kamil ADEM <[email-masked]<mailto:[email-masked]>> wrote:

Hi Adrian,

Kamil Adem

Hi Kamil,

https://git.postgresql.org/gitweb/?p=psqlodbc.git;a=commit;h=241c70bf6516bf08770fabcb1b86934c8da116c8<https://urldefense.com/v3/__https:/git.postgresql.org/gitweb/?p=psqlodbc.git;a=commit;h=241c70bf6516bf08770fabcb1b86934c8da116c8__;!!OSkwEA!wqnOOfEzkbgIK-Th03S7-naM-yP_2luficMfLQW2b2dxa1cjwof1c7PKqw1bvMrnmQ$>

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<https://urldefense.com/v3/__https:/odbc.postgresql.org/docs/win32-compilation.html__;!!OSkwEA!wqnOOfEzkbgIK-Th03S7-naM-yP_2luficMfLQW2b2dxa1cjwof1c7PKqw3PSwiPhQ$>

Adrian Grucza

Technical Lead

Tel:

+61390185800

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.

Hi Adrian,

Thanks in advance.

Kamil Adem

Hi Kamil,

Adrian Grucza

Technical Lead

Tel:

+61390185800

Level 16 385 Bourke St

 Melbourne,

Victoria,

3000

nosig

Hello,

Best regards,

Kamil Adem

---

From: "Inoue,Hiroshi" <[email-masked]>
Date: Fri, 17 Sep 2021 21:16:05 +0900

Hi,

We will make a new release next weekend.

regards,
Hiroshi Inoue

2021年9月17日(金) 20:00 Kamil ADEM <[email-masked]>:

> *Sent:* Friday, September 17, 2021 1:47 PM
> *Cc:* [email-masked]; Haluk DALKIRAN <[email-masked]
> >
> I'm not part of the psqlODBC team so I'm not aware of the release
> schedule. But whenever a new version is released, a message is posted on
> this mailing list, so just stay subscribed.
>
>
>
>
>
>
>
>
> *Adrian Grucza**​*
>
> Technical Lead
>
> Tel:
>
> +61390185800
>
> <https://urldefense.com/v3/__https:/git.postgresql.org/gitweb/?p=psqlodbc.git;a=commit;h=241c70bf6516bf08770fabcb1b86934c8da116c8__;!!OSkwEA!wqnOOfEzkbgIK-Th03S7-naM-yP_2luficMfLQW2b2dxa1cjwof1c7PKqw1bvMrnmQ$>
>
>
>
> <https://urldefense.com/v3/__https:/odbc.postgresql.org/docs/win32-compilation.html__;!!OSkwEA!wqnOOfEzkbgIK-Th03S7-naM-yP_2luficMfLQW2b2dxa1cjwof1c7PKqw3PSwiPhQ$>
>
>
>

---

From: Kamil ADEM <[email-masked]>
Date: Mon, 27 Sep 2021 16:58:42 +0000

Hi Adrian, Hi Hiroshi,

Thank you very much for the new release.
After a few adjustments in my code,  I managed to call a procedure with output parameters processed successfully.
I really appreciate the collaboration and dedication of the psqlODBC team.

Best regards,

Kamil Adem

From: Inoue,Hiroshi <[email-masked]>
Sent: Friday, September 17, 2021 3:16 PM
To: Kamil ADEM <[email-masked]>
Cc: Adrian Grucza <[email-masked]>; [email-masked]; Haluk DALKIRAN <[email-masked]>
Subject: Re: Problem on calling procedures with ADODB

Hi,

regards,
Hiroshi Inoue

2021年9月17日(金) 20:00 Kamil ADEM <[email-masked]<mailto:[email-masked]>>:
Hi Adrian,

Have a nice day!

Kamil Adem

Cc: [email-masked]<mailto:[email-masked]>; Haluk DALKIRAN <[email-masked]<mailto:[email-masked]>>

Hi Kamil,

Adrian Grucza​

Technical Lead

Tel:

+61390185800

Hi Adrian,

Kamil Adem

Hi Kamil,

Adrian Grucza

Technical Lead

Tel:

+61390185800

Hi Adrian,

Thanks in advance.

Kamil Adem

Hi Kamil,

Adrian Grucza

Technical Lead

Tel:

+61390185800

Level 16 385 Bourke St

 Melbourne,

Victoria,

3000

nosig

Hello,

Best regards,

Kamil Adem