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:gallagher.paul@xxxxxxxxx]
Sent: Friday, May 11, 2007 7:27 AM
To: michael.peppler@xxxxxxxxxxxxxx
Cc: CAMPBELL, BRIAN D (BRIAN); martin.evans@xxxxxxxxxxxx;
dbi-users@xxxxxxxx
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, michael.peppler@xxxxxxxxxxxxxx
<michael.peppler@xxxxxxxxxxxxxx> 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
campbelb@xxxxxxxxxxxxxxxxxx - 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

.

Relevant Pages

  • RE: temporary table "disapears"
    ... The temp tables should only be dropped when the connection ... Andon said that batching all the commands in the same execute is not an ... However they were both run on the same connection (same $dbh ... Toute utilisation de ce ...
    (perl.dbi.users)
  • Re: Returning recordsets from stored procedures
    ... We have a web application using stored procedures to generate the report data into temp tables from where we currently select the data; is it possible in IDS to circumvent the need for the temp tables and return the data direct from the stored procedure? ... Just add the WITH RESUME clause to the RETURN statement inside the FOREACH loop and each time the procedure is called from a cursor in the calling program it will return the next data row. ... Then you can call the procedure from an execute statement in pure SQL, or you can cast the procedure call to a MULTISET, or in 11.10+ you can EXECUTE it in the FROM clause of a SELECT statement. ...
    (comp.databases.informix)
  • Open a query and pausing for changes
    ... I have created a series of queries that alone executed in order work as ... What I am trying to do is execute the set of queries via a command button. ... from entering the changes needed to the temp tables. ...
    (microsoft.public.access.formscoding)
  • Re: Cell Shading Colour Property
    ... You can set it back to its red color by executing this line... ... copy/paste this code line into the Immediate window and execute it, the code will fill the first 56 rows with the 56 available color indexes... ... Dim Temp As Variant ... Temp = "Light blue" ...
    (microsoft.public.excel.programming)
  • Re: temporary table "disapears"
    ... Andon's report got me wondering if Oracle temp tables behave ... Oracle only has the global temp ... Andon said that batching all the commands in the same execute is not an ... Toute utilisation de ce ...
    (perl.dbi.users)