Re: Problem on calling procedures with ADODB

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 <kamila(at)aqvilasoftware(dot)com> 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 <adrian(dot)grucza(at)iress(dot)com>
> *Sent:* Thursday, September 16, 2021 2:07 PM
> *To:* Kamil ADEM <kamila(at)aqvilasoftware(dot)com>
> *Cc:* pgsql-odbc(at)postgresql(dot)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] <https://www.iress.com/>
>
> *Adrian Grucza*
>
> Technical Lead
>
> Tel:
>
> +61390185800
>
> *adrian(dot)grucza(at)iress(dot)com* <adrian(dot)grucza(at)iress(dot)com>
>
> *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 <kamila(at)aqvilasoftware(dot)com>
> 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.Ş.
>
>
>
>
>
>
>
>