FreeTDS Development Group

["James K. Lowden" <jklowden AT freetds.org>]

For a long time I've been meaning to write a script processor using ODBC,
something along the lines of bsqldb.  Eventually we need to add regression
tests that demonstrate progressive releases continue to produce the same
results.  These would be more general and higher-level than the unit
tests, which are ideally designed to prove that individual functions work
correctly.  We would want input-output (query-result) pairs.  And we would
want a way to easily add a new query-result pair.  

Isolating problems in ODBC is more difficult than with the other libraries
because there's usually one and often two layers of mediation: the DM and
what I'll call the host language bindings.  The DM often "synthesizes"
functions specified by ODBC but not inplemented by the driver.  And the
person using ODBC most often is not coding in C or even C++ but rather
Perl or, quite often, PHP.  The first question I have when reading ODBC
questions posted to the list is, "Is the FreeTDS driver responding
correctly to the query?"  It can be difficult to know.  

unixODBC comes with a tool, isql, that unfortunately isn't well suited to
our purposes.  It uses old ODBC 1.0 functions, relies (obviously) on the
DM, and has known but undocumented limits to its output string lengths. 
When a query works in isql but not in PHP, we cannot say unequivocally
that the problem lies in PHP.  There are too many moving parts to be sure.
 isql also doesn't link to FreeTDS (it loads the library under program
control with dlopen(3)), making it harder to use with gdb.  

Now it's a little bit easier: bsqlodbc is a batch-oriented script
processor.  You feed it SQL, it prints results.  It doesn't have to be
linked to a driver manager, letting us see exactly what FreeTDS is
producing.  I've just started using it, and the results are already
interesting.  

I am using CVS HEAD without any DM, using unixODBC's header files.  

Test 1: "print 'hello' print 'goodbye'"
---------------------------------------

I reported print messages being reversed a few weeks ago:

> SQL> print 'hello' print 'goodbye'
> [][unixODBC][FreeTDS][SQL Server]goodbye
> [][unixODBC][FreeTDS][SQL Server]hello
> [ISQL]INFO: SQLExecute returned SQL_SUCCESS_WITH_INFO
> SQLRowCount returns -1

It still isn't right:

$ SQL=hg.sql; cat $SQL && isql $S $U $P -b -v < $SQL
print 'hello' print 'goodbye'
[][unixODBC][FreeTDS][SQL Server]goodbye
[][unixODBC][FreeTDS][SQL Server]hello
[ISQL]INFO: SQLExecute returned SQL_SUCCESS_WITH_INFO
SQLRowCount returns -1

but it's OK with bsqlodbc, which uses SQLGetDiagRec (ODBC 3.0) instead of
SQLError (ODBC 1.0):

$ make bsqlodbc && ./bsqlodbc -S $S -U $U -P $P -i hg.sql   
`bsqlodbc' is up to date.
"[FreeTDS][SQL Server]hello"
"[FreeTDS][SQL Server]goodbye"

I suspect something about SQLError.  

Test 2: "print 'hello' select 1 as one print 'goodbye'"
-------------------------------------------------------

We never do say goodbye, love:

$ SQL=hsg.sql; cat $SQL && isql $S $U $P  -b -v < $SQL
print 'hello' select 1 as one print 'goodbye'
[][unixODBC][FreeTDS][SQL Server]hello
[ISQL]INFO: SQLExecute returned SQL_SUCCESS_WITH_INFO
+------------+
| one        |
+------------+
| 1          |
+------------+
SQLRowCount returns 1
1 rows fetched

$ ./bsqlodbc -S $S -U $U -P $P  -i hsg.sql  
"[FreeTDS][SQL Server]hello"
       one
----------
         1

I suspect something about SQLMoreResults.  

Test 3: "raiserror('ouch', 16,1) select 1 as 'one'"
---------------------------------

$ isql $S $U $P  -b -v < raiserror.sql 
[37000][unixODBC][FreeTDS][SQL Server]ouch
[ISQL]ERROR: Could not SQLExecute

No row from isql; bsqlodbc produces slightly different, also wrong,
output:

$ ./bsqlodbc -S $S -U $U -P $P -i raiserror.sql   
bsqlodbc: error -1: SQLExecute: SQL_ERROR: failed
bsqlodbc: error 50000: 42000: [FreeTDS][SQL Server]ouch

Under what circumstances should raiserror cause SQLExecute to fail?  Why
SQL_ERROR and not SQL_SUCCESS_WITH_INFO?  The query:

        raiserror('ouch', 16,1) select 1 as 'one'

returns and error message and a row.  If SQLExecute returns SQL_ERROR,
should the application continue on and try SQLFetch??  

Unfortunately, it looks like fisql and bsqldb both goof on this, too.  

$ ./bsqldb -S $S -U $U -P $P  -i raiserror.sql
Msg 50000, Level 16, State 1
Server 'TITAN', Line 1
        ouch

$ fisql -S $S -U $U -P $P 
1>> :r raiserror.sql
2>> go
Msg 50000, Level 16, State 1:
Server 'TITAN', Line 1:
ouch
DB-LIBRARY error:
        General SQL Server error: Check messages from the SQL Server
Operating-system error:
        (null)

Only sqsh gets it right:

[21] varley.master.1> :r raiserror.sql
[21] varley.master.1> raiserror('ouch', 16,1) select 1 as 'one'
[21] varley.master.2> go
Msg 50000, Level 16, State 1
Server 'TITAN', Line 1
ouch
 one        
 -----------
           1

(1 row affected)


Other considerations
--------------------

1.  I'm not sure SQLDescribeCol fills in DecimalDigitsPtr (2nd last
parameter) correctly.  I get zero for the above query; I think I should
get 10.  

2.  SQLSTATE is not being populated for SQLDescribeCol for T-SQL print
messages.  That's apparent from isql.  I don't know what the state should
be.  

We've made some real progress since 0.64.  Frediano has kindly maintained
patches for it.  I think it's getting to be time to release 0.65, but we
must be careful because we made several changes to the resultset handling.
 In db-lib, we changed how often dbresults returns, not to mention using
errhandle to manage timeouts.  ODBC was also affected by the timeout logic
changes.  

Clearly, there's still work to do.  Volunteers welcome!  

--jkl