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: "James K. Lowden" <jklowden AT schemamania.org>
  • 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 03:16:29 -0500

On Tue, 23 Mar 2004, "Polyanovskyi, Sasha" <sasha.polyanovskyi AT oa.com.au>
wrote:
> 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.

Completely normal. TDS will not pass back any output for "SELECT
@total = COUNT(*) FROM a_table". You can confirm this: If you set
TDSDUMP, execute the query, and grep the log for "marker", you'll see
there's no packet returned by the server that could carry the variables's
data.

> In this case dbnumcols()
> returns 0 and dbnumrets() returns 0. I actually expected dbnumrets() to
> return something... Is this correct?

Yes, dbnumrets() should return 1. HOWEVER, as of SQL Server 7.0 SP3,
Microsoft quietly dropped support for fetching return status values using
dbsqlexec(), and I wouldn't be surprised if they dropped return paramters,
too. You have to use the rpc functions.

> Now, RPC. It doesn't work with TDS v4.2: RPC packets don't even go
> out...

Right. I'll add it to our TODO, but it's not a priority right now.

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

Ugh. That's bad. I can see our unit test is, um, a little light in this
area. Thanks for pointing it out. I'll look into it.

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

Yes. Cf. dbretstatus().

I appreciate your work, Sasha. Let me see how hard it is to pass a string
or two.

Regards,

--jkl




Archive powered by MHonArc 2.6.24.

Top of Page