FreeTDS Development Group

["Joseph Geiser" <joe AT mail.paravisions.com>]

Hello,  this is my first posting here and we're in need of some help with 
executing Stored Procedures from PHP via unixODBC.

Background:

We're running a Linux (Fedora Core 2) server with PHP 4.3.6, FreeTDS 0.62.3 
(using TDS 7.0) and unixODBC 2.2.8.  Up to now, we've been running just fine 
using FreeTDS and the PHP "mssql" db calls with direct queries, but no stored 
procedures.  

We're rewriting this application to expand its capabilities and to provide 
better reporting to internal and external users.  To better control the IO 
for consistency and to keep development time down, we've decided that Stored 
Procedures for some of the more complex database operations such as updating 
OLAP data and such.

After installing a fresh server (the one described above), we tested FreeTDS 
and for direct queries, it works just great.  For Stored Procedures, if the 
call is written exactly as it would be written with TSQL/SQLQA, FreeTDS 
handles it.  Straight queries through isql (unixODBC) execute just fine as 
well, but isql is not handling a Stored Procedure call at all.  In an attempt 
to code with PHP, it's also failing.

There are two simple stored procedures we are using to test.  One takes an 
input parameter, returns three output parameters and a return code.  The 
second takes one input parameter and returns the three data values as a 
resultset and also returns a return code.  Sample procedure code follows - to 
save space, only the first is given:

Test call to a procedure using tsql:

1> use dbname
2> declare @cid int,
3> @pid int,
4> @tid varchar(20),
5> @returncode int
6> exec @returncode=mt_gethash @hashin='1h88f9', @clientid=@cid OUTPUT, 
@productid=@pid OUTPUT, @trackid=@tid OUTPUT
7> print 'Results from the called Stored Procedure'
8> print ' '
9> print 'Client ID Returned is ' + cast(@cid as char(10))
10> print 'Product ID Returned is ' + cast(@pid as char(10))
11> print 'Track ID Returned is ' + cast(@tid as varchar(20))
12> print 'Return Code from the SP is ' + cast(@returncode as char(10))
13> go
(return status = 0)
Results from the called Stored Procedure

Client ID Returned is 88888
Product ID Returned is 10001
Track ID Returned is test_track_v2
Return Code from the SP is 0
1>

SP for the above test:

CREATE PROCEDURE [mt_gethash] 
        @hashin varchar(20),
        @clientid int OUTPUT,
        @productid int OUTPUT,
        @trackid varchar(20) OUTPUT
AS

set transaction isolation level read uncommitted
set nocount on

declare @result int

set @result=(select count(*) from tablename where external_hash=@hashin)

if @result=0
begin
        set @clientid=999999
        set @productid=999999
        set @trackid=''
        return 999
end
else
begin
        set @clientid=(select client_id from tablename where 
external_hash=@hashin)
        set @productid=(select prod_id from tablename where 
external_hash=@hashin)
        set @trackid=(select track_id from tablename where 
external_hash=@hashin)
        return 0
end


(some data above changed of obvious reasons)

The question is, since unixODBC is using FreeTDS for its underlying transport 
(driver), how should we be executing stored procedures now?  We just can't 
seem to get them to work within PHP (either through straight mssql db 
statements with msbind, or through ODBC).  Is there something we are missing?

Thanks in advance for any assistance that can be provided -- we've pretty 
much exhausted all possibilities that we know of.

Best Regards,
Joseph Geiser