Skip to Content.
Sympa Menu

freetds - RE: [freetds] dbrpcsend fails (works now, BUT THERE IS MORE)

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Polyanovskyi, Sasha" <sasha.polyanovskyi AT oa.com.au>
  • To: 'FreeTDS Development Group' <freetds AT lists.ibiblio.org>
  • Subject: RE: [freetds] dbrpcsend fails (works now, BUT THERE IS MORE)
  • Date: Tue, 23 Mar 2004 16:51:03 +1100

My findings about stored procedures: THERE SEEM TO BE PROBLEMS :-)!!!!
Please reply to these issues one by one (if anyone is bothered :-)).

The database client I am using is Microsoft SQL server 2000. Please note
that all the stored procedures I use for testing work within MS SQL :-)


"EXECUTE stored_proc_name input_var_value" works. In fact, if stored
procedure does "SELECT COUNT(*) FROM a_table", I can process dbresults
normally by dbnextrow(). However, something like this doesn't work: "SELECT
@total = COUNT(*) FROM a_table", even if the variable @total is declared as
an "OUTPUT" variable within the stored procedure. In this case dbnumcols()
returns 0 and dbnumrets() returns 0. I actually expected dbnumrets() to
return something... Is this correct?


Now, RPC. It doesn't work with TDS v4.2: RPC packets don't even go out...
With TDS v8.0 some things don't work (unless I am doing something wrong).

My procedure is:
CREATE PROCEDURE myProc
(
@total int OUTPUT
)
AS
BEGIN
SELECT @total = COUNT(*) from a_table
END

In dbrpcparams I do
dbrpcparam(dbproc, "@total", DBRPCRETURN, SYBINT4, sizeof(long), -1,
(BYTE *)&num1);

This works! dbnumrets() returns 1, and printf("%s", *dbretdata(dbproc, 1))
returns the proper value.
Having more than parameter works as well...

Now the strange behavior.
First of all, the values returned for an integer type are in the range
[0,256]. Even if I specify everything this way:
dbrpcparam(dbproc, "@total", DBRPCRETURN, SYBINT4, sizeof(long), -1,
(BYTE *)&num1);

Secondly, strings don't seem to be working. I specify string parameter as:
dbrpcparam(dbproc, "@tname", DBRPCRETURN, SYBCHAR, -1, 20, (BYTE
*)str);
And when I look into the TDSDUMP file, string parameter doesn't even get
attached to the outgoing RPC packet!!! Moreover, this happens when at least
one of the attached parameters is a string... Here is the TDS dump:

int (works)
===
Sending packet @ 15:02:30.946523
0000 03 01 00 2b 00 00 01 00-06 00 6d 00 79 00 50 00 |...+.... ..m.y.P.|
0010 72 00 6f 00 63 00 00 00-06 40 00 74 00 6f 00 74 |r.o.c... .@.t.o.t|
0020 00 61 00 6c 00 01 38 00-00 00 00 |.a.l..8. ...|

string (outgoing packet has no params attached, even when you have other
parameters, not just strings)
======
Sending packet @ 16:04:46.353536
0000 03 01 00 18 00 00 01 00-06 00 6d 00 79 00 50 00 |........ ..m.y.P.|
0010 72 00 6f 00 63 00 00 00- |r.o.c...|


Finally a question. If my stored proc has a statement like "return 5" or
"return some_local_var_value", is there a way to catch it with dblib?




Archive powered by MHonArc 2.6.24.

Top of Page