Re: temporary tables don't work with freetds?

2009/12/4 Michal Seliga <[email protected]>:
>
>
> Frediano Ziglio wrote:
>> 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...
>>
>
> i know about it, problem is that i am not using ODBC driver, but direct TDS
> (becaus qt+iodbc+freetds is complete fail right now, qt+freetds works at least
> partially). qt is using dbsqlexec(..) in its tds driver to launch query, there
> are no extra prepare/exec steps, but still its not working
>

I saw your code at http://bugreports.qt.nokia.com/browse/QTBUG-850.
I looked at dblib code and qt code. Our dblib does not use prepared
statements at all. The problem is that QTDS open a new connection for
every result so this cause the problem (beside performance ones!).
Looking at qsql_tds.cpp you can see that dbopen is called in
QTDSDriver::open but after some rows dbclose is called (also there is
no DBPROCESS in QTDSDriverPrivate but only in QTDSResultPrivate). A
workaround would be to use the same result for all queries...
I would suggest to use odbc instead. I uploaded some patches for QT,
you can find post 0.82 patches at http://freetds.sf.net site.
Only 4 sub-tests failed using patched version. I have to say that CVS
version fails only a single test and support better unicode.

freddy77