RE: temporary table "disapears"


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:campbelb@xxxxxxxxxxxxxxxxxx]
Sent: Friday, May 11, 2007 12:34 PM
To: Paul Gallagher; michael.peppler@xxxxxxxxxxxxxx;
atschauschev@xxxxxxxxx
Cc: martin.evans@xxxxxxxxxxxx; dbi-users@xxxxxxxx
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: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


_____________________________________________________________________________________

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 postmaster@xxxxxxxxxxxx

Thank you.
_____________________________________________________________________________________
.

Relevant Pages

  • RE: temporary table "disapears"
    ... I cannot use doto create the temp tables, it means for me that I must scan each statement for temp tables, extract it, execute it separately with doand the rest with prepareand execute. ... local temps don't seem to persist after an error. ... consider global temps. ...
    (perl.dbi.users)
  • Re: T-SQL is not quite there yet
    ... no, the choice is between INSERT-EXEC ... results ought to be called functions, not "stored procedures". ... SQL Server is a server- ... gains only by putting keys on temp tables that had none. ...
    (microsoft.public.sqlserver.programming)
  • Row by Row Operations Help req
    ... Create temp table 1 ... Nigel,The cursor question is the SQL equivalent of the GOTOs / NO GOTOs ... question about whether the familiarity of procedural code is worth the ... leverages the strengths of SQL Server and is usually faster since it uses ...
    (microsoft.public.sqlserver.programming)
  • Re: SYS.SQL_DEPENDENCIES, Refresh Dependencies
    ... because you cannot have temp tables in user-defined functions. ... If you are using dependencies to see what you need to migrate from test ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: Help with trigger
    ... You could get the data into temp tables and from these insert into ... of temp tables in triggers. ... running INPUTBUFFER or querying sysprocesses from a trigger. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)