Thread: [ODBC] Problem calling a function from ODBC application

On 02/01/2017 07:05 PM, Vilches, Alejandro wrote:
> Hi,
>
>
>
> I’m having an issue calling a stored function in my database from my
> application using ODBC.  The application creates a prepared statement
> with the following string/text using SQLPrepare(): “{call my_function(?,
> ?, ?, ?)}” (where the fourth parameter is an out parameter).  Then, it
> binds the parameters using SQLBindParameter() and specifying the correct
> data types.  This is the function’s signature:
>
> “my_function(in a integer, in b bigint, in c character varying, out d
> bigint)”.
>
>
>
> However, when I execute the application, I get the following error:
>
> ERROR: function my_function(integer, bigint, unknown) does not exist;
>
>
>
> Why does it recognize the first two parameters correctly and not the
> third one?  I’ve double checked everything including the binding of that
> parameter to make sure it’s the correct type (SQL_VARCHAR).

I think this is going to require  showing the actual entire code
sequence that sets up the parameters and binds them and then calls the
function.

>
>
>
> The details:
>
>
>
> Language: C/C++
>
> GCC: 4.8.5
>
> ODBC driver manager: unixODBC 2.3.1
>
> PostgreSQL: 9.4.5
>
> PostgreSQL ODBC driver: psqlodbc-09.05.0400
>
> OS: SLES 12-SP1
>
>
>
> Sorry if this is a repeated question.  I tried searching and couldn’t
> find a solution.
>
>
>
> Any help is greatly appreciated.
>
>
>
> Thanks!
>
> Alejandro
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Hi,

Hi,

 

I’m having an issue calling a stored function in my database from my application using ODBC.  The application creates a prepared statement with the following string/text using SQLPrepare(): “{call my_function(?, ?, ?, ?)}” (where the fourth parameter is an out parameter).  Then, it binds the parameters using SQLBindParameter() and specifying the correct data types.  This is the function’s signature:

“my_function(in a integer, in b bigint, in c character varying, out d bigint)”.

I tried the code after copy and pasting your mail and  the result was successful.

 

However, when I execute the application, I get the following error:

ERROR: function my_function(integer, bigint, unknown) does not exist;

I can see this exact error when I run without registering my_function.

 

Why does it recognize the first two parameters correctly and not the third one?

Because the third parameter is ambiguous for psqlodbc driver, varchar or text?
'unknown' means it is not specified and let the postgres server determine the type.

regards,
Hiroshi Inoue

  I’ve double checked everything including the binding of that parameter to make sure it’s the correct type (SQL_VARCHAR).

 

The details:

 

Language: C/C++

GCC: 4.8.5

ODBC driver manager: unixODBC 2.3.1

PostgreSQL: 9.4.5

PostgreSQL ODBC driver: psqlodbc-09.05.0400

OS: SLES 12-SP1

 

Sorry if this is a repeated question.  I tried searching and couldn’t find a solution.

 

Any help is greatly appreciated.

 

Thanks!

Alejandro

Hi Adrian,

Thanks for your reply.  To be honest, my application is a bit more complex and I simplified things to make the question
easierto ask.  I did go back and write up a small program as described in the question and now the error message I'm
gettingis 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
finefrom psql by simply executing "select * from my_function(...)".  But calling it from my application I get the
followingerror: "ERROR: syntax error at end of input;" 

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

-----Original Message-----
From: Adrian Klaver [mailto:adrian.klaver@aklaver.com]
Sent: Wednesday, February 1, 2017 7:16 PM
To: Vilches, Alejandro <alejandro.vilches@intel.com>; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Problem calling a function from ODBC application

On 02/01/2017 07:05 PM, Vilches, Alejandro wrote:
> Hi,
>
>
>
> I'm having an issue calling a stored function in my database from my
> application using ODBC.  The application creates a prepared statement
> with the following string/text using SQLPrepare(): "{call
> my_function(?, ?, ?, ?)}" (where the fourth parameter is an out
> parameter).  Then, it binds the parameters using SQLBindParameter()
> and specifying the correct data types.  This is the function's signature:
>
> "my_function(in a integer, in b bigint, in c character varying, out d
> bigint)".
>
>
>
> However, when I execute the application, I get the following error:
>
> ERROR: function my_function(integer, bigint, unknown) does not exist;
>
>
>
> Why does it recognize the first two parameters correctly and not the
> third one?  I've double checked everything including the binding of
> that parameter to make sure it's the correct type (SQL_VARCHAR).

I think this is going to require  showing the actual entire code sequence that sets up the parameters and binds them
andthen calls the function. 

>
>
>
> The details:
>
>
>
> Language: C/C++
>
> GCC: 4.8.5
>
> ODBC driver manager: unixODBC 2.3.1
>
> PostgreSQL: 9.4.5
>
> PostgreSQL ODBC driver: psqlodbc-09.05.0400
>
> OS: SLES 12-SP1
>
>
>
> Sorry if this is a repeated question.  I tried searching and couldn't
> find a solution.
>
>
>
> Any help is greatly appreciated.
>
>
>
> Thanks!
>
> Alejandro
>


--
Adrian Klaver
adrian.klaver@aklaver.com

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
questioneasier to ask.  I did go back and write up a small program as described in the question and now the error
messageI'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
finefrom psql by simply executing "select * from my_function(...)".  But calling it from my application I get the
followingerror: "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

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
inmy 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

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@dream.email.ne.jp]
Sent: Friday, February 3, 2017 4:15 PM
To: Vilches, Alejandro <alejandro.vilches@intel.com>; Adrian Klaver <adrian.klaver@aklaver.com>
Cc: pgsql-odbc@postgresql.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
questioneasier to ask.  I did go back and write up a small program as described in the question and now the error
messageI'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
finefrom psql by simply executing "select * from my_function(...)".  But calling it from my application I get the
followingerror: "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

Hi,

On 2017/02/04 9:31, 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
onein 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; 

Is the type of parameter 'timestamp with time zone' right?

> Error while preparing parameters
>
> I've attached the new sample program.  The stored function can be any dummy function.
>
> Thanks!
> Alejandro

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
onein 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)¶m*, 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@dream.email.ne.jp]
> Sent: Friday, February 3, 2017 4:15 PM
> To: Vilches, Alejandro <alejandro.vilches@intel.com>; Adrian Klaver <adrian.klaver@aklaver.com>
> Cc: pgsql-odbc@postgresql.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
questioneasier to ask.  I did go back and write up a small program as described in the question and now the error
messageI'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
finefrom psql by simply executing "select * from my_function(...)".  But calling it from my application I get the
followingerror: "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.klaver@aklaver.com

It's actually without time zone.  However, it doesn't seem like ODBC allows me to specify that.  When I bind the
parameter,I can only say: SQL_TYPE_TIMESTAMP. 

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

Hi,

On 2017/02/04 9:31, 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
onein 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; 

Is the type of parameter 'timestamp with time zone' right?

> Error while preparing parameters
>
> I've attached the new sample program.  The stored function can be any dummy function.
>
> Thanks!
> Alejandro

1) That's because PostgreSQL treats the out parameter (that 14th argument in this case), as the return value (so it
omitsit in the function signature in the error message).  But in the declaration of my stored function, the 14th
argumentis 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.klaver@aklaver.com]
Sent: Friday, February 3, 2017 4:49 PM
To: Vilches, Alejandro <alejandro.vilches@intel.com>; Inoue, Hiroshi <h-inoue@dream.email.ne.jp>
Cc: pgsql-odbc@postgresql.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
onein 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)¶m*, 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@dream.email.ne.jp]
> Sent: Friday, February 3, 2017 4:15 PM
> To: Vilches, Alejandro <alejandro.vilches@intel.com>; Adrian Klaver
> <adrian.klaver@aklaver.com>
> Cc: pgsql-odbc@postgresql.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
questioneasier to ask.  I did go back and write up a small program as described in the question and now the error
messageI'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
finefrom psql by simply executing "select * from my_function(...)".  But calling it from my application I get the
followingerror: "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.klaver@aklaver.com

On 2017/02/04 9:54, Vilches, Alejandro wrote:
> It's actually without time zone.  However, it doesn't seem like ODBC allows me to specify that.  When I bind the
parameter,I can only say: SQL_TYPE_TIMESTAMP. 

Hmm the current FE/BE protocol doesn't allow to specify preferable
parameter type(oid) unfortunately.
Probably I can make a patch to use unknown parameters instead of
specifying parameters by oid.
Could you test it?

regards,
Hiroshi Inoue

>
> -----Original Message-----
> From: Inoue, Hiroshi [mailto:h-inoue@dream.email.ne.jp]
> Sent: Friday, February 3, 2017 4:45 PM
> To: Vilches, Alejandro <alejandro.vilches@intel.com>; Adrian Klaver <adrian.klaver@aklaver.com>
> Cc: pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] Problem calling a function from ODBC application
>
> Hi,
>
> On 2017/02/04 9:31, 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
onein 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; 
> Is the type of parameter 'timestamp with time zone' right?
>
>> Error while preparing parameters
>>
>> I've attached the new sample program.  The stored function can be any dummy function.
>>
>> Thanks!
>> Alejandro

Hi,

On 2017/02/04 9:54, Vilches, Alejandro wrote:
> It's actually without time zone.  However, it doesn't seem like ODBC allows me to specify that.  When I bind the
parameter,I can only say: SQL_TYPE_TIMESTAMP. 

How about calling with cast i.e.

    my_function3(?, ?, ?, ?, ?::timestamp with time zone, ?, ?, ?, ?, ?, ?, ?, ?, ?)
?

regards,
Hiroshi Inoue


> -----Original Message-----
> From: Inoue, Hiroshi [mailto:h-inoue@dream.email.ne.jp]
> Sent: Friday, February 3, 2017 4:45 PM
> To: Vilches, Alejandro <alejandro.vilches@intel.com>; Adrian Klaver <adrian.klaver@aklaver.com>
> Cc: pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] Problem calling a function from ODBC application
>
> Hi,
>
> On 2017/02/04 9:31, 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
onein 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; 
> Is the type of parameter 'timestamp with time zone' right?
>
>> Error while preparing parameters
>>
>> I've attached the new sample program.  The stored function can be any dummy function.
>>
>> Thanks!
>> Alejandro

Hi Hiroshi,

Thanks for your suggestion!  That actually worked!  It works two ways: "?::timestamp" or "cast(? as timestamp)".

After running further tests, I'm seeing the issue occurs when the function has a parameter of type timestamp.
Furthermore,it throws off other parameters after that (e.g. the varchar parameters show up as unknown in the error
message). Anyway, I thought I would just mention that in case it helps anything. 

I wish there was a way to specify the timestamp type better when binding the parameter in ODBC (or that PostgreSQL
wouldn'tget confused between timestamp with or without time zone) so that I didn't need to cast it on top of that (as
thisseems a bit redundant). 

Anyway, thanks so much for your help, I really appreciate it!

Alejandro

-----Original Message-----
From: Inoue, Hiroshi [mailto:h-inoue@dream.email.ne.jp]
Sent: Monday, February 6, 2017 1:53 AM
To: Vilches, Alejandro <alejandro.vilches@intel.com>; Adrian Klaver <adrian.klaver@aklaver.com>
Cc: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Problem calling a function from ODBC application

Hi,

On 2017/02/04 9:54, Vilches, Alejandro wrote:
> It's actually without time zone.  However, it doesn't seem like ODBC allows me to specify that.  When I bind the
parameter,I can only say: SQL_TYPE_TIMESTAMP. 

How about calling with cast i.e.

    my_function3(?, ?, ?, ?, ?::timestamp with time zone, ?, ?, ?, ?, ?, ?, ?, ?, ?) ?

regards,
Hiroshi Inoue


> -----Original Message-----
> From: Inoue, Hiroshi [mailto:h-inoue@dream.email.ne.jp]
> Sent: Friday, February 3, 2017 4:45 PM
> To: Vilches, Alejandro <alejandro.vilches@intel.com>; Adrian Klaver
> <adrian.klaver@aklaver.com>
> Cc: pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] Problem calling a function from ODBC application
>
> Hi,
>
> On 2017/02/04 9:31, 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
onein 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;
> Is the type of parameter 'timestamp with time zone' right?
>
>> Error while preparing parameters
>>
>> I've attached the new sample program.  The stored function can be any dummy function.
>>
>> Thanks!
>> Alejandro

Hi Alejandro,

On 2017/02/07 9:24, Vilches, Alejandro wrote:
> Hi Hiroshi,
>
> Thanks for your suggestion!  That actually worked!  It works two ways: "?::timestamp" or "cast(? as timestamp)".
>
> After running further tests, I'm seeing the issue occurs when the function has a parameter of type timestamp.
Furthermore,it throws off other parameters after that (e.g. the varchar parameters show up as unknown in the error
message). Anyway, I thought I would just mention that in case it helps anything. 
>
> I wish there was a way to specify the timestamp type better when binding the parameter in ODBC (or that PostgreSQL
wouldn'tget confused between timestamp with or without time zone) so that I didn't need to cast it on top of that (as
thisseems a bit redundant). 

I'd like to improve the behavior in the next release.

regards,
Hiroshi Inoue
>
> Anyway, thanks so much for your help, I really appreciate it!
>
> Alejandro