Hello, first of all, I want to thank you for your responses.
I cannot use do() to create the temp tables, it means for me that I must scan each statement for temp tables, extract it (if present), execute it separately with do() and the rest with prepare() and execute(). Global temp tables are not solution too, for example: if two clients use the same procedure simultaneously through different connections, which procedure in turn uses global temp table, what happens: the values from first client mess with the values with the second in the global temp table. The solution, which work fine for me, is setting odbc_exec_direct to 1 (submitted from BRIAN). I read the documentation in DBD::ODBC about odbc_exec_direct, I didn't understand what really does, but it works :) . Thanks! Andon "Priest, Darryl" <[EMAIL PROTECTED]> wrote: We saw a similar problem creating temp tables with SQL Server. To solve the issue we created the temp tables using the do method which keeps the temp tables available to statement handles created against that database handle. HTH, -D -----Original Message----- From: CAMPBELL, BRIAN D (BRIAN) [mailto:[EMAIL PROTECTED] Sent: Friday, May 11, 2007 12:34 PM To: Paul Gallagher; [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; dbi-users@perl.org Subject: RE: temporary table "disapears" I believe I have a solution to the problem of supporting MS SQL local temp tables without batching in a single prepare... I've already established that global temps (##foo) persist after an execute(). But to get local temps to persist (#foo) you need this attribute: $dbh->{odbc_exec_direct} = 1; However, local temps don't seem to persist after an error. Consider: my $s1 = 'create table #foo (a int not null)'; my $s2 = 'insert into #foo values (1)'; my $sth; $sth = $dbh->prepare($s1); $sth->execute(); # works: table created $sth = $dbh->prepare($s2); $sth->execute(); # works: value inserted $sth = $dbh->prepare($s1); $sth->execute(); # doesn't work: table already exists $sth = $dbh->prepare($s2); $sth->execute(); # doesn't work: table gone because of above error Turning Autocommit off doesn't seem to alter this behavior. Also, FYI, MS temp tables and the difference between global and local temps is described here: http://msdn2.microsoft.com/en-us/library/ms174979.aspx -----Original Message----- From: Paul Gallagher [mailto:[EMAIL PROTECTED] Sent: Friday, May 11, 2007 7:27 AM To: [EMAIL PROTECTED] Cc: CAMPBELL, BRIAN D (BRIAN); [EMAIL PROTECTED]; dbi-users@perl.org Subject: Re: temporary table "disapears" An aside: Andon's report got me wondering if Oracle temp tables behave correctly via DBI. My answer is: yes! Oracle only has the global temp table model, but with data private to the session and may or maynot survive a commit depending on how you have defined the temp table. I blogged and posted my test case at http://tardate.blogspot.com/2007/05/do-oracle-temp-tables-behave-correct ly.html On 5/11/07, [EMAIL PROTECTED] wrote: > You should run this with DBI->trace() turned on to see what DBD::ODBC > actually does. The temp tables should only be dropped when the > connection is closed. > > Michael > > > > > Extranet > [EMAIL PROTECTED] - 11.05.2007 00:19 > > > To: martin.evans, dbi-users > cc: > Subject: RE: temporary table "disapears" > > Martin, Autocommit off doesn't help local temps persist after the > execute. > > Andon said that batching all the commands in the same execute is not > an option for him, so the only working alternative so far is to > consider global temps (##foo). They do persist after an execute and > throughout an entire session. > > Consider these examples: > > my $s1 = 'create table #foo (a int not null)'; my $s2 = 'insert into > #foo values (1)'; my $s3 = 'select * from #foo'; > $dbh->{AutoCommit} = 0; # trying to see if this help, but it > doesn't > my $sth; > $sth = $dbh->prepare($s1); > $sth->execute(); # works: table created > $sth = $dbh->prepare($s1); > $sth->execute(); # works: can recreate table because > original is gone > $sth = $dbh->prepare($s2); > $sth->execute(); # doesn't work: table is gone > $sth = $dbh->prepare($s3); > $sth->execute(); # doesn't work: table is gone > $sth = $dbh->prepare("$s1; $s2; $s3"); > $sth->execute(); # works: table exists across batched > commands > _____________________________________________________________________________________ The information contained in this email may be confidential and/or legally privileged. It has been sent for the sole use of the intended recipient(s). If the reader of this message is not an intended recipient, you are hereby notified that any unauthorized review, use, disclosure, dissemination, distribution, or copying of this communication, or any of its contents, is strictly prohibited. If you have received this communication in error, please contact the sender by reply email and destroy all copies of the original message. To contact our email administrator directly, send to [EMAIL PROTECTED] Thank you. _____________________________________________________________________________________ --------------------------------- Ready for the edge of your seat? Check out tonight's top picks on Yahoo! TV.
