FreeTDS Development Group

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

On Thursday, July 01, 2004 3:07 PM -0500, Lowden, James K writes:

> To clarify, the PHP mssql extension uses db-lib, isql uses 
> ODBC, and tsql uses the TDS layer directly.   So, you really 
> can't test the mssql extension with isql; at least, you're 
> using different client libraries when you do.  

We've tried it several ways.  Basically, we would like to use unixODBC for
access (which looks as though it goes straight through, and not parsed by
db-lib.  Here's what we have tried:

VIA TSQL (FreeTDS Version AND SQLQA)
1. Straight queries (SELECT/UPDATE/INSERT) - these work
2. Stored Procedures with input and output parms - these work
3. Stored Procedures with input parm and output cursor - these work

VIA isql (unixODBC)
1. Straight queries - these work
2. Stored Procedures - all - do not work.  Receive the error message
[ISQL]ERROR: Could not SQLExecute

One thing we saw that was odd, was that when watching queries for this
database (a test db and using SQL Profiler filtered to this DB), everything
showed up EXCEPT the stored procedure attempt via isql under unixODBC.  We
saw all of the queries and SP attempts under every tool tested, except isql
which leads me to wonder whether it's making a connection at all.

(odbc.ini and odbcinst.ini are populated, as is freetds.conf)

> We now have a db-lib test facility: bsqldb, first included in 
> 0.62.3.  (If you want a man page, though, get a nightly 
> snapshot.)  The attached script works with bsqldb; I'd be 
> curious to know if isql can process it, too.  But: all it's 
> doing is T-SQL.  If you use the -v option, bsqldb will show 
> you that no parameters are returned as such to the client.  

Something to check out - thanks

> In the old days, Microsoft servers did return output 
> parameter data when a stored procedure was called with 
> "execute" in a T-SQL batch.  Lately they don't; the 
> application must use the rpc functions instead.  We have unit 
> test that demonstrates how.  

Basically, one has to declare local variables, and equate them to the output
parameters, at least in tsql (which you demonstrate in your attachment).
This seems to be the only way one can get the output parameters.  As for...

> That leaves PHP, which I don't use.  Unless the mssql 
> extension supports the db-lib rpc functions -- unless, that 
> is, you see some explicit support for stored procedures and 
> parameter binding -- you're not going to be able to fetch 
> output parameters.  The workaround is to call a wrapper 
> procedure that selects the output values, creating a normal 
> resultset.  

mssql extensions in PHP do permit binding (mssql_bind) and this is the
normal way to do it.  This is our fallback position at this point.  I was
advised however, to use SPs, to use unixODBC - now I wonder if it's worth
the hassle.  Time will tell (and not much of that will be allocated).

Thanks for your help Jim...  Film at 11 :)

Cheers,
Joe