Skip to Content.
Sympa Menu

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

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Allan Kim <akim AT freedom.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] trouble passing (var)char data to and from stored procedures
  • Date: Tue, 14 Mar 2006 19:22:33 -0800

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.






Archive powered by MHonArc 2.6.24.

Top of Page