Skip to Content.
Sympa Menu

freetds - [freetds] unixODBC via FreeTDS, PHP and Stored Procedure Help

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Joseph Geiser" <joe AT mail.paravisions.com>
  • To: <freetds AT lists.ibiblio.org>
  • Subject: [freetds] unixODBC via FreeTDS, PHP and Stored Procedure Help
  • Date: Thu, 1 Jul 2004 13:57:38 -0400

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





Archive powered by MHonArc 2.6.24.

Top of Page