Home » SQL & PL/SQL » SQL & PL/SQL » returning a PL/SQL boolean via ODBC from C
returning a PL/SQL boolean via ODBC from C [message #212757] Mon, 08 January 2007 03:42 Go to next message
Last_Inquisitor
Messages: 5
Registered: January 2007
Junior Member
Hi,

I need to call a stored function which returns a boolean.
My problem is, how do I formulate a valid SQLBindParameter call to retrieve that boolean return value?

So basically it should look like

// C++
bool l_return_value;
::SQLBindParameter(handle,1,SQL_PARAM_OUTPUT,SQL_C_LONG,SQL_INTEGER,0,0,&l_return_value,0,SQL_NULL_HANDLE);


After executing I always get error-code PLS-00382 (wrong type).
This only happens with boolean output parameters.

Obviously SQL_C_LONG and/or SQL_INTEGER are the wrong choice.
But what is the correct one??

Thanks in advance!
Re: returning a PL/SQL boolean via ODBC from C [message #212775 is a reply to message #212757] Mon, 08 January 2007 05:17 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Have you tried SQL_BIT ?
Re: returning a PL/SQL boolean via ODBC from C [message #212776 is a reply to message #212757] Mon, 08 January 2007 05:24 Go to previous messageGo to next message
Last_Inquisitor
Messages: 5
Registered: January 2007
Junior Member
Yes, same error message.
Re: returning a PL/SQL boolean via ODBC from C [message #212777 is a reply to message #212776] Mon, 08 January 2007 05:30 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Not an answer to your question but, can't you just return 1 and 0 instead ?

Are you sure the function is working properly, have you tried calling it from sqlplus ? If that gives an error post the code of the function.

[Updated on: Mon, 08 January 2007 05:36]

Report message to a moderator

Re: returning a PL/SQL boolean via ODBC from C [message #212779 is a reply to message #212757] Mon, 08 January 2007 05:36 Go to previous messageGo to next message
Last_Inquisitor
Messages: 5
Registered: January 2007
Junior Member
No, unfortunately not.
I have to support boolean return values.
Those DB functions aren't written by me.
As a (temporary and not desired) workaround I'm currently wrapping those functions by others returning integers:

boolean RealFunc(...)
integer RealFunc_Wrapper(...)


But, as I said, this isn't really an option in my case, since there will be databases I must not modify.
Re: returning a PL/SQL boolean via ODBC from C [message #212928 is a reply to message #212779] Mon, 08 January 2007 19:34 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
BOOLEAN is a PL/SQL type. Interfaces using other languages can only use the SQL types.

You can create another procedure of an anonymous PL/SQL block that returns the booean into a PL/SQL variable, and then translates it to a VARCHAR2 or NUMBER type.

Ross Leishman
Re: returning a PL/SQL boolean via ODBC from C [message #213032 is a reply to message #212757] Tue, 09 January 2007 03:30 Go to previous messageGo to next message
Last_Inquisitor
Messages: 5
Registered: January 2007
Junior Member
A boolean to integer translator function?
Sounds like a good idea since there seems to be no other way.
Thank you!

But this basically means that there is no way to handle boolean output values via ODBC?
Your workaround could do for the return value, but for other boolean output parameter it won't.

Did nobody ever had this problem? Seems like a big one to me...

[Updated on: Tue, 09 January 2007 03:40]

Report message to a moderator

Re: returning a PL/SQL boolean via ODBC from C [message #213227 is a reply to message #213032] Tue, 09 January 2007 20:34 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Yes, people have encountered this before, and I told you the solution. Wrap it in an anonymous block and call that block from your host program.
DECLARE
 a boolean;
BEGIN
 :out_a := 0;
 proc(a);
 if a then
    :out_a := 1;
 end if;
END;


Ross Leishman
Re: returning a PL/SQL boolean via ODBC from C [message #213284 is a reply to message #212757] Wed, 10 January 2007 03:20 Go to previous message
Last_Inquisitor
Messages: 5
Registered: January 2007
Junior Member
Aaah, now I got it Razz
Thanks!
Previous Topic: pl/sql completed successfully with showing result
Next Topic: bitand(x,y)
Goto Forum:
  


Current Time: Tue Dec 06 13:55:30 CST 2016

Total time taken to generate the page: 0.06658 seconds