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]
<[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
>
