[freetds] unixODBC via FreeTDS, PHP and Stored Procedure Help
joe at mail.paravisions.com
Thu Jul 1 13:57:38 EDT 2004
Hello, this is my first posting here and we're in need of some help with executing Stored Procedures from PHP via unixODBC.
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))
(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
SP for the above test:
CREATE PROCEDURE [mt_gethash]
@clientid int OUTPUT,
@productid int OUTPUT,
@trackid varchar(20) OUTPUT
set transaction isolation level read uncommitted
set nocount on
declare @result int
set @result=(select count(*) from tablename where external_hash=@hashin)
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)
(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.
More information about the FreeTDS