ODBC Call Syntax for multiple return values
So first off, sorry for the length of the question...
So basically I'm having issues calling a stored proc from an Informix database where the stored proc has multiple return value, while using ODBC in .NET (aka an ODBCCommand), and I haven't seen anything on the internet where someone has done this before... so is it even possible?
So to begin with using an ODBCCommand (or ODBC in general) to call an SP your supposed to use the ODBC Call Syntax.
Rough Stored Procedure:
create procedure informix.proc_ins (
n_company char(10),
n_message_body lvarchar(4096),
n_time int,
n_time_dt datetime year to second,
n_processed smallint)
returning
int,
int;
begin
define row_count int;
define new_serial int;
INSERT into my_table ( company, message_body, time, time_dt, processed, create_user, create_dt, recmod_user, recmod_dt)
VALUES 开发者_运维百科( n_company, n_message_body, n_time, n_time_dt, n_processed, USER, CURRENT YEAR TO SECOND, USER, CURRENT YEAR TO SECOND);
let new_serial = DBINFO('sqlca.sqlerrd1');
let row_count = DBINFO('sqlca.sqlerrd2');
return
new_serial,
row_count;
end
end procedure;
So given this SP the ODBC Call Syntax would look something like:
{XXXX = call informix.proc_ins(?,?,?,?,?)}
where 'XXXX' somehow has 2 '?'s... ?
Any help with this would be really appreciated.
Also before someone says "use the IBM.Data.Informix drivers" (either of them), it's not possible in this case because the 'old' one doesn't work with VS2010, and the new one can't be installed on the same PCs as the old one at the same time, but that's a totally different rant for another day.
The way I'd do it in ESQL/C is to treat the EXECUTE PROCEDURE informix.proc_ins(...)
statement as if it was a SELECT statement - that is, declare a cursor (possibly after preparing the statement), and then using OPEN (chance to pass in parameters) and FETCH and CLOSE to get the data.
I'd expect to use the same technique in ODBC - probably bypassing the general ODBC Call Syntax.
If you need the official answer using the official ODBC notation/method, you'll have to get someone else to provide the answer.
I don't believe there is a "official" ODBC answer. Its down to the driver. There is nothing stopping you from using SQL_PARAM_RETURN as the type for more than one parameter, but as to how you would call that, ODBC doesn't say.
精彩评论