Re: temporary tables don't work with freetds?

2009/12/1 Michal Seliga <[email protected]>:
> hello list
>
> I am using FreeTDS 0.82. i found problem with temporary tables, it shows when
> using qt (using QTDS driver) and also when using iodbctest. everything works
> properly when using tsql. I am using connection to microsoft sql database
>
> problem is that created temporary tables disappear and can't be used later
>
> tsql (working):
>
> test@visibuntu:~$ tsql -S mssql -U dba -P sql
> locale is "en_US.UTF-8"
> locale charset is "UTF-8"
> 1> use HamiltonSFA_280
> 2> go
> 1> create table #temptab(cus_id integer)
> 2> go
> 1> select * from #temptab
> 2> go
> cus_id
>
>
> iodbc test (not working):
>
> test@visibuntu:~$ iodbctest "DSN=mssql;UID=dba;PWD=sql;DATABASE=HamiltonSFA_280"
> iODBC Demonstration program
> This program shows an interactive SQL processor
> Driver Manager: 03.52.0607.1008
> Driver: 0.82 (libtdsodbc.so)
>
> SQL>create table #temptab(cus_id integer)
> Statement executed. 0 rows affected.
>
> SQL>select * from #temptab
> 1: SQLPrepare = [FreeTDS][SQL Server]Invalid object name '#temptab'. (208)
> SQLSTATE=42S02
> 2: SQLPrepare = [FreeTDS][SQL Server]Statement(s) could not be prepared. (8180)
> SQLSTATE=42000
>
> in qt (using QTDS driver) it behaves the same as with iodbc test
>
>
> from my past experience i guess that problem can be related to the fact that
> microsoft sql can't use prepare/execute step when creating temporary tables,
> only direct execution works. see note at
> http://msdn.microsoft.com/en-us/library/aa905910%28SQL.80%29.aspx
>
>
> so is it known problem or something new?
> and is there any possible workaround to make it work?
>
> thanks

I was not aware of this problem however is a protocol server problem,
not a client one :(
So FreeTDS is correct (I treied with MS ODBC and got the same issue).
The solution is not to use SQLPrepare/SQLExecute but use
SQLExecDirect... a workaround would be not use prepared statement if
not parameters but this create incompatibility... if client wants
prepared statements it got prepared statements...

freddy77