FreeTDS Development Group

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

Min wrote:
> 
> As SYBASE will create a text pointer when using UPDATE to set TEXT
> field, even with a NULL. So there is a difference between initialized
> NULL value and a updated NULL value with a text pointer. It seems
> freetds doesn't handle the latter well and php will crash.

You seem to have found a bug!  Thanks!  

As you suspected, you found a problem with FreeTDS's processing of TEXT
columns on TDS 5.0.  It's not being done correctly.  In testing for it,
sadly, I found other bugs.  

BTW it has nothing to do with your SQL.  It has everything to do with the
client interpretation of the datastream. Demonstration follows. 

$ cat patches/text_null.sql 
create table #t (name_text text NULL, name varchar(10) NULL)
go 
insert #t select cast(name as text), name  from systypes where name =
'char'
go
select * from #t
go
update #t set name = NULL, name_text= NULL
go
select * from #t
go

db-lib
------

db-lib gets it right on Microsoft:

$ bsqldb -S microsoft-2005 -U $U -P $P -i patches/text_null.sql 
@@rowcount not available
1 rows affected
name_text  name      
---------  ----------
char       char      
1 rows affected
1 rows affected
name_text  name      
---------  ----------
NULL       NULL      
1 rows affected

And wrong on Sybase: 

$ bsqldb -S sybase -U $U -P $P -i patches/text_null.sql 
@@rowcount not available
1 rows affected
name_text  name      
---------  ----------
char       char      
1 rows affected
1 rows affected
name_text  name      
---------  ----------
           NULL      
^^^^ this should say "NULL"
1 rows affected

ODBC
----

ODBC can't handle temporary tables.   

$ bsqlodbc -S microsoft -U $U -P $P -i patches/text_null.sql 
bsqlodbc: error -1: SQLPrepare: SQL_ERROR: failed
bsqlodbc: error 208: 42S02: [FreeTDS][SQL Server]Invalid object name '#t'.
bsqlodbc: error 8180: 42000: [FreeTDS][SQL Server]Statement(s) could not
be prepared.

Lovely.[1]  

ct-lib
------

There is no bsqlct due to lack of interest and time.  But we can use sqsh.
 Sqsh duplicates your experience on Sybase:

$ sqsh  -S sybase -U $U -P $P -i patches/text_null.sql -mvert
name_text: char
name:      char
 
(1 row affected)
name_text: name:      NULL
          ^ Note sqsh's confusion here.  The 'name' column should be on
the left margin (as in first query), not here.  
(1 row affected)

And sqsh has real trouble on Microsoft (perhaps because of FreeTDS):

$ sqsh  -S microsoft -U $U -P $P -i patches/text_null.sql 
(1 row affected)
dsp_desc_bind: Memory allocation failure for column #1
(1 row affected)
dsp_desc_bind: Memory allocation failure for column #1

libtds
------

tsql reports the same behavior on TDS 5.0:

$ tsql -oq -S thistle -U sa -P '' < patches/text_null.sql

name_text       name
char    char
name_text       name
        NULL
^^^^ again not null, doesn't print "NULL".  

So, you found a bona-fide bug.  

Anyone got a patch?  I'm not going to be able to look into this for a few
weeks. 
--jkl

[1] This is more significant than it might seem. I bumped into it myself
with Microsoft's bcp.exe today.  bcp.exe has a "queryout" feature that
lets it execute a query and of course write the results to a file. 
Trouble is, you get an error if said query is a stored procedure that
selects from a temporary table.  Google showed many people with the same
plaint, and a few Microsoft MVP's explaining why it need be thus, because
ODBC executes the query with FORMAT_ONLY to determine the metadata.  Else,
they said, how could it know how to format the file?  Ye gods, what
insight!  I didn't have the heart or time or place to point out that
metadata are delivered prior to the first row of every resultset even
without preëmptive querying.  Or that bsqldb handles the situation quite
nicely, thank you.