[freetds] SP parameters

Ellert van Koperen ellert at vankoperen.nl
Thu Jun 15 18:00:29 EDT 2006


Lowden, James K wrote:
>> Any one can tell me if there some bug on freetds 0.63 invoking a
>> stored procedure on MSSQL?. i' ve developed an application in C
>>
>>     
> It seems you're using a NULL parameter for a column with a weird
> datatype, and we don't determine the size correctly.  :-/
>
>   
>> what is the solution?.  
>>     
>
> Use a different datatype, I think.  
>
> --jkl
>
>   

So it has nothing to do with the problems posted half a year ago and did 
not get any reply on?

this one:

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.

ps. tds version used: freetds-0.63

-- 
+-----------------------------------------+
| Ellert van Koperen,  Concepts developer |
+-----------------------------------------+
| MAIL: ellert at vankoperen.nl              |
+-----------------------------------------+



More information about the FreeTDS mailing list