FreeTDS Development Group

[Зверев Виталий Борисович <VZverev AT genesis.spb.ru>]

Here it is, just after coffein was in
...It would be nice to consider any T-SQL statement in the batch as 
context-free (from point of view on placeholder datatype of course) because 
it give simplified AST processing.
Initially I doubted about serious limitations in datatype prediction which 
related to that consideration.
Using of temporary tables is such example (not unique, obviously).
It's a pity, but not deadly ;)

Sincerely yours. 
-------------------------------------------------------
Vitaly Zverev, OCP DBA 8&8i
Oracle E-Business Suite &
Infiniband Linux Cluster Administrator
Genesis Ltd., St.Petersburg, Russia

 

> -----Original Message-----
> From: freetds-bounces AT lists.ibiblio.org 
> [mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of 
> Зверев Виталий Борисович
> Sent: Saturday, February 25, 2006 11:53 PM
> To: FreeTDS Development Group
> Subject: Re: [freetds] parsing T-SQL with flex & yacc 
> (was:segfaultinPerl'sEmail::Store when using freetds)
> 
> I'm sorry James,
> it was my ugly description of potential deadlocks in datatype 
> prediction.I'll try to formulate it more abstractive:
> Imaginate some T-SQL batch in which somewhere we have got 
> statement with temporary indirect type declaration (like 
> stored procedure with  "select count(1) cnt into #tmp from 
> sysobjects group by type" ), next several statements 
> "bla-bla-bla;" and then "select * from #tmp where cnt > ?"
> I mean "indirect" in contrast with "declare, create type or 
> create table".
> Now, we could ask our placeholder prophet: give me 
> placeholder datatype, please.
> In this context we have problem with type prediction, because 
> we... ghm are constrained by prophet scope.
> Our "virtual prophet" should be able to see the text of 
> potential CONSTRUCTOR for #tmp in the virtual past (seems it 
> was a first virtual "feature in the past" in my english too). 
> Indeed, it is very optimistic point of view but impossible 
> for many samples like triggers and stored procedures.
> Precise prediction requires to interpret (in our case just 
> execute it) statement, but it will not prediction exactly!
> (I imaginate TV show where new prophet says - "tomorrow 
> proclamation about todays rain" ) I think It is deadlock aka 
> "double bind". I need more coffein...
> 
> Sincerely yours. 
> -------------------------------------------------------
> Vitaly Zverev, OCP DBA 8&8i
> Oracle E-Business Suite &
> Infiniband Linux Cluster Administrator
> Genesis Ltd., St.Petersburg, Russia
> 
>  
> 
> > -----Original Message-----
> > From: freetds-bounces AT lists.ibiblio.org 
> > [mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of James K. 
> > Lowden
> > Sent: Saturday, February 25, 2006 8:57 PM
> > To: FreeTDS Development Group
> > Subject: Re: [freetds] parsing T-SQL with flex & yacc (was: 
> > segfaultinPerl'sEmail::Store when using freetds)
> > 
> > Vitaly Zverev wrote:
> > > Thanks James,
> > > alas it was not mine (and not handmade by anybody) statement.
> > > "select cnt from #temp1 where 1=2" was captured by profiler
> > as reply
> > > on odbc call sequence: SQLPrepare(hstmt, ( unsigned char 
> * )"select
> > > count(1) as cnt ,type into #temp1 from sysobjects group by type ; 
> > > select cnt,type from #temp1 where cnt > ? ", SQL_NTS); 
> > > SQLDescribeParam(hstmt,1, &DataType, &ParamSize, &DecimalDigits, 
> > > &Nullable); So, the primary question is: How to get placeholder's 
> > > datatype if correlated object has temporary nature and does
> > not exist
> > > before now?
> > 
> > I'm not sure I understand the problem, Vitaly.  You asked about 
> > "select cnt from sysobjects", which isn't valid SQL; of course you 
> > need "select cnt from #temp1".
> > 
> > I think Microsoft's driver is telling us the answer: to 
> know the type 
> > of the placeholder in "select cnt,type from #temp1 where 
> cnt > ?", you 
> > need the (approximate) type of 'cnt'.
> > One way to find out -- maybe the easiest way and certainly 
> a reliable 
> > one -- is to create the table and gather its metadata with a SET 
> > FMTONLY query.  Then drop that table and proceed.
> > 
> > Am I missing something?  
> > 
> > --jkl
> > 
> > 
> > 
> > > > -----Original Message-----
> > > > From: freetds-bounces AT lists.ibiblio.org 
> > > > [mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of James K.
> > > > Lowden
> > > > Sent: Thursday, February 23, 2006 4:54 AM
> > ...
> > > > > select count(1) as cnt ,type into #temp1 from sysobjects
> > > > group by type
> > > > > ;
> > > > > 
> > > > > select cnt,type from #temp1 where cnt > ?
> > > > > 
> > > > > How to get datatype of placeholder (#temp1.cnt)? 
> > > > ...
> > > > > The native MS odbc driver give me an answer ;) :
> > > > > SET FMTONLY ON select  cnt from sysobjects where 1=2 SET
> > > > FMTONLY OFF
> > > > > Error :[Microsoft][ODBC SQL Server Driver][SQL
> > > > Server]Invalid column
> > > > > name 'cnt'.
> > > > 
> > > > 'cnt' is not a column in sysobjects.  Perhaps you want:
> > > > 
> > > >         SET FMTONLY ON 
> > > >         select cnt from #temp1 where 1=2 
> > > >         SET FMTONLY OFF
> > > > 
> > > > Is that any help?  
> > > > 
> > > > Regards,
> > > > 
> > > > --jkl
> > > > _______________________________________________
> > > > FreeTDS mailing list
> > > > FreeTDS AT lists.ibiblio.org
> > > > http://lists.ibiblio.org/mailman/listinfo/freetds
> > > > 
> > > _______________________________________________
> > > FreeTDS mailing list
> > > FreeTDS AT lists.ibiblio.org
> > > http://lists.ibiblio.org/mailman/listinfo/freetds
> > _______________________________________________
> > FreeTDS mailing list
> > FreeTDS AT lists.ibiblio.org
> > http://lists.ibiblio.org/mailman/listinfo/freetds
> > 
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
>