Skip to Content.
Sympa Menu

freetds - Re: [freetds] temporary tables don't work with freetds?

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Frediano Ziglio <freddy77 AT gmail.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] temporary tables don't work with freetds?
  • Date: Fri, 4 Dec 2009 11:16:39 +0100

2009/12/1 Michal Seliga <michal.seliga AT visicom.sk>:
> 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




Archive powered by MHonArc 2.6.24.

Top of Page