Re: Problem calling a function from ODBC application

1) That's because PostgreSQL treats the out parameter (that 14th argument in this case), as the return value (so it omits it in the function signature in the error message). But in the declaration of my stored function, the 14th argument is there (declared as "out param14 bigint")

2) Yep, this is fine. The ones omitting the "&" is because they're already pointer types.

-----Original Message-----
From: Adrian Klaver [mailto:adrian(dot)klaver(at)aklaver(dot)com]
Sent: Friday, February 3, 2017 4:49 PM
To: Vilches, Alejandro <alejandro(dot)vilches(at)intel(dot)com>; Inoue, Hiroshi <h-inoue(at)dream(dot)email(dot)ne(dot)jp>
Cc: pgsql-odbc(at)postgresql(dot)org
Subject: Re: [ODBC] Problem calling a function from ODBC application

On 02/03/2017 04:31 PM, Vilches, Alejandro wrote:
> Oh goodness, that's embarrassing :).
>
> Okay, so I've tried the following:
>
> - I fixed the small sample program and now that one executes
> successfully
>
> - I created another sample program that calls a stored function with the same number of parameters and types as the one in my real application and now that fails with the following error message (the same error message I originally reported):
> ERROR: function my_function3(bigint, bigint, integer, integer,
> timestamp with time zone, integer, integer, integer, integer, integer,
> integer, unknown, unknown) does not exist; Error while preparing
> parameters

First I am not C programmer so approach with caution. Two things I noticed:

1) The above shows a function with 13 arguments, but your attached code shows it having 14 arguments.
2) In 12 of your SQLBindParameter statements you have (SQLPOINTER)&param*, in two you have (SQLPOINTER)param*
>
> I've attached the new sample program. The stored function can be any dummy function.
>
> Thanks!
> Alejandro
>
> -----Original Message-----
> From: Inoue, Hiroshi [mailto:h-inoue(at)dream(dot)email(dot)ne(dot)jp]
> Sent: Friday, February 3, 2017 4:15 PM
> To: Vilches, Alejandro <alejandro(dot)vilches(at)intel(dot)com>; Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com>
> Cc: pgsql-odbc(at)postgresql(dot)org
> Subject: Re: [ODBC] Problem calling a function from ODBC application
>
> Hi,
>
> On 2017/02/04 4:41, Vilches, Alejandro wrote:
>> Hi Adrian,
>>
>> Thanks for your reply. To be honest, my application is a bit more complex and I simplified things to make the question easier to ask. I did go back and write up a small program as described in the question and now the error message I'm getting is different, but I'm still having issues calling my function from ODBC.
>>
>> In essence, the problem I'm having is calling a function from ODBC that has an output parameter. I can call it just fine from psql by simply executing "select * from my_function(...)". But calling it from my application I get the following error: "ERROR: syntax error at end of input;"
>
> I see the following in your code.
>
> ret = SQLPrepare(stmt, "{call my_function(?, ?, ?, ?}", SQL_NTS);
>
> Should the last part ... ?, ?} be ... ?, ?)} ?
>
> regards,
> Hiroshi Inoue
>
>> Please find the source code attached. The stored function is very simple and silly:
>>
>> CREATE OR REPLACE FUNCTION public.my_function(a integer, b bigint, c character varying, OUT d bigint)
>> RETURNS bigint
>> LANGUAGE plpgsql
>> AS $function$
>> BEGIN
>> d := a + b + char_length(c);
>> END
>> $function$
>>
>> Thanks!
>> Alejandro

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com