FreeTDS Development Group

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

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
>