Re: Problem on calling procedures with ADODB

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Adrian Grucza <[email protected]>
To: Kamil ADEM <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Problem on calling procedures with ADODB
Date: Fri, 17 Sep 2021 09:20:46 +1000
Message-ID: <CADF4wWo38dSd3JiPyLFy3Z4SroKA8uM949nUOTRkVsJ_h8idRw@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<CADF4wWpFgFNyB=XxrVDKo=cG2n5N3p3pxo-XqwQTeToGSEUeZQ@mail.gmail.com>
	<[email protected]>

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=241c70bf6516bf08770fabcb1b86934c8da116c...

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 protected]> 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. 😊
>
> 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 protected]>
> *Sent:* Thursday, September 16, 2021 2:07 PM
> *To:* Kamil ADEM <[email protected]>
> *Cc:* [email protected]
> *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] <https://www.iress.com/;
>
> *Adrian Grucza*
>
> Technical Lead
>
> Tel:
>
> +61390185800
>
> *[email protected]* <[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 Thu, 16 Sept 2021 at 17:49, 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 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.Ş.
>
>
>
>
>
>
>
>


Attachments:

  [image/jpeg] ~WRD0002.jpg (823B, 3-~WRD0002.jpg)
  download | view image

view thread (9+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Problem on calling procedures with ADODB
  In-Reply-To: <CADF4wWo38dSd3JiPyLFy3Z4SroKA8uM949nUOTRkVsJ_h8idRw@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox