[freetds] trouble passing (var)char data to and from stored procedures

Allan Kim akim at freedom.com
Tue Mar 14 22:22:33 EST 2006


If I'm not mistaken, mssql_bind() is pretty much just a wrapper for 
dbrpcparam() in rpc.c. That function normally logs an entry like 
"dbrpcparam added parameter" -- if you don't see that in the dump logs, 
I'd suspect that you're failing input validation in dbrpcparam (most of 
which was added post-0.62).

One possibility ... are you trying to bind an input parameter? If so you 
may not need to specify MAXLEN. Here's something from the published 
specs from our friends in Redmond:

-----------------
maxlen
For variable-length return parameters (when type is SQLCHAR, SQLBINARY,
SQLTEXT, or SQLIMAGE), maxlen is the maximum desired byte length for the
value parameter returned from a stored procedure.
Set maxlen to -1 in any of these cases:

For fixed-length return parameters (such as when type is SQLINT4).
To pass a NULL fixed-length parameter value (such as when type is
SQLINT4) to the stored procedure.
*** For parameters that are not designated as return parameters. *** (my
emphasis)
Set maxlen to 0 to pass a NULL variable-length parameter value (when
type is SQLCHAR, SQLBINARY, SQLTEXT, or SQLIMAGE) to the stored
procedure.
-----------------

I'm blissfully ignorant of how Sybase and MSSQL use maxlen, but if 
possible I suggest tweaking dbrpcparam to degrade more gently when 
handed bogus input, say by forcing more appropriate values rather than 
returning FAIL. It's not strictly correct, but there is a lot of older 
code out there (ADODB, cough, cough) that passes bogus input, and 
goodness knows there are enough backwards compatibility problems in the 
PHP world already.

Of course I freely admit that I haven't tried any of the recent release 
candidates or nightly builds (back on Java projects) so I could be 
completely wrong.

See also PHP bug 33963.

Thanks everyone!


Alexander Urmazov wrote:
> I have the same problem in 0.64 RC1:
> When I call in PHP
> mssql_bind('dbo.MyProc', '@ParamName', 'xxxx', SQLVARCHAR, FALSE, FALSE, 4);
> I got the message
> mssql_bind(): Unable to set parameter
> 
> If I change SQLVARCHAR to SQLCHAR the problem remains
> 
> It seems like there were no this error in 0.62
> 
> --------------
> Alexander
> 2006-03-14
> 
> -------------------------------------------
> Hello.
> I have some trouble passing (var)char data to and from stored procedures.
> 
> I made a page that describes the problem and all i did in detail,
> including the files and logs, at
> http://kb.vankoperen.nl/freetds-problems.html
> 
> I am not entirely sure it is a problem of FreeTDS or perhaps of PHP, So
> if this is a mispost please do excuse my ignorance.
> 
> 
> Here is a short version of the text:
> 
> 
> Installed FreeTDS-0.63 on a RHEL4 box with Apache-2.0.55 and PHP-5.1.2,
> connecting to a server running win2003 server standard edition and
> sqlserver 8.00.818. It all runs fine, untill i want to pass Character
> data to or from a storedprocedure.
> 
> I am not sure this is a known problem (i think not, no info on it on the
> net at all) and if parhaps anyone else has it, but here it is.
> 
> FreeTDS.conf:
> [global]
>         tds version = 8.0
>         text size = 20000
> 
> [TDS]
>         host = 192.168.5.251
>         port = 2433
>         tds version = 8.0
>         dump file = /var/log/freetds.log
>         dump file append = yes
>         debug level = 99
> 
> 
> Using the standard mssql_init / mssql_bind / mssql_execute structure to
> pass the data (see the kb page for the exact scripts)
> 
> 
> 1:
> Inputting an INT4 is no problem:
> 
>     # ./sptest1.php
>     test1= 35
>     retval = 350
> 
> 2:
> Inputting a VARCHAR however always fails:
> 
>     # ./sptest2.php
> 
>     Warning: mssql_bind(): Unable to set parameter in
> /var/lib/asterisk/agi-bin/sptest2.php on line 19
> 
>     Warning: mssql_execute(): message: Procedure 'sp_Test2' expects
> parameter '@test1', which was not supplied. (severity 16) in
> /var/lib/asterisk/agi-bin/sptest2.php on line 24
> 
>     Warning: mssql_execute(): stored procedure execution failed in
> /var/lib/asterisk/agi-bin/sptest2.php on line 24
>     Last message from SQL : Procedure 'sp_Test2' expects parameter
> '@test1', which was not supplied.
> 
> 
> 3:
> Outputting an INT4 is no problem:
>     # ./sptest3.php
>     test1= 10
>     retval = 3
> 
> 
> 4:
> Outputting a VARCHAR is extremely strange,
> sometimes its okay:
> 
>     # ./sptest4.php
>     test1= xyz45678
>     retval = 4
> 
> but sometimes not:
> 
>     # ./sptest4.php
>     Warning: mssql_execute(): Error converting client characters into
> server's character set. Some character(s) could not be converted.
> (severity 16) in /var/lib/asterisk/agi-bin/sptest4.php on line 24
> 
>     Warning: mssql_execute(): stored procedure execution failed in
> /var/lib/asterisk/agi-bin/sptest4.php on line 24
>     Last message from SQL : Changed database context to 'IVR'.
> 
> And thats the same script executing, mind you. nothing changing, just
> randomly succeeding and failing.
> 
> Now, if i cut off the output to 13 characters or less:
>     mssql_bind($proc, "@test1", &$test1, SQLVARCHAR, TRUE, FALSE, 13);
> then it will not fail.
> It will only fail (sometimes) when the data cutoff is bigger (even if
> the returned data is small!)
> 
> I suspect some buffer problem here ?
> 
> 
> I have, simply put, no idea what is going on here.
> Any tips or assistance is very welcome.
> 
> 
> 
> Yours gratefully,
> E.B. van Koperen.
> 
> 


More information about the FreeTDS mailing list