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-correctly.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

-----Original Message-----
From: Martin Evans [mailto:martin.evans@xxxxxxxxxxxx]
Sent: Thursday, May 10, 2007 7:39 AM
To: dbi-users@xxxxxxxx
Subject: Re: temporary table "disapears"

CAMPBELL, BRIAN D (BRIAN) wrote:
> You're right. It's the the other way around from what I said.
> However, when I tested this yesterday it seemed I was getting an error

> on the create command also. But I re-examined the results more
> carefully today and the create worked OK; it was just the insert that
> failed. However they were both run on the same connection (same $dbh
> handle). So it seems that local temps don't persist after an
> execute() call, as Andon supposed.
>

What if you turn autocommit off - do the temporary tables exist for
longer then?

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
> ________________________________
>
> From: michael.peppler@xxxxxxxxxxxxxx
> [mailto:michael.peppler@xxxxxxxxxxxxxx]
> Sent: Wednesday, May 09, 2007 10:49 PM
> To: CAMPBELL, BRIAN D (BRIAN)
> Cc: atschauschev@xxxxxxxxx; dbi-users@xxxxxxxx
> Subject: RE: temporary table "disapears"
>
>
>
> I'm pretty sure that #tmp is a local temporary table, and ##tmp
is a
> global temporary table...
>
> So the original problem is most likely that the create table
#tmp and
> the insert into #tmp statements aren't being run on the same physical
> connection. I don't know DBD::ODBC, but I can tell you that
> DBD::Sybase could possibly have opened a second connection under the
> covers if it thought the first statement hadn't been completely
> processed yet.
>
> Michael
>
>
>
>
>
> Extranet
> campbelb@xxxxxxxxxxxxxxxxxx - 09.05.2007 18:40
>
>
> To: atschauschev, dbi-users
>
> cc:
>
> Subject: RE: temporary table "disapears"
>
> Actually I tried this against SQL 2000, DBI 1.53 and DBD::ODBC
> 1.13...
>
> You should be getting 2 errors, the same error from both
prepares.
> In
> other words, #foo isn't being treated as a proper table name.
> Naturally, these statements work fine if you just use foo (which

> isn't
> temp).
>
> However, #foo should represent a "global temp" table, and this
is not
> being accepted as a valid name. Not sure why.
>
> But ##foo works fine, and the table does persist across executes

> while
> the $dbh connection is open. With 2 #'s, it's a "local temp"
> table
> which means it's not visible to other sessions. If that's OK,
> perhaps
> you can use that instead.
>
>
>
> -----Original Message-----
> From: Andon Tschauschev [mailto:atschauschev@xxxxxxxxx]
> Sent: Wednesday, May 09, 2007 8:31 AM
> To: dbi-users@xxxxxxxx
> Subject: temporary table "disapears"
>
> Hello,
>
> I am using DBI 1.51 and DBD::ODBC 1.13, connecting to MSSQL2005.
>
> Executing following statements:
> $sth = $dbh->prepare('create table #foo (a int not null)');
> $sth->execute(); $sth = $dbh->prepare('insert into #foo values
(1)');
> $sth->execute();
>
> generate an error:
> [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object
name
> '#foo'.
>
> So, the temporary table "disapears".... (I tested it on Sybase,
using
> DBD::Sybase, too, there is no an error). Since the two
statements are
> dynamically created (between come other statements), I cannot
execute
> in
> one batch $sth = $dbh->prepare('create table #foo (a int not
> null)
> insert into #foo values (1)); $sth->execute();
>
> at once...
>
> How can I avoid this problem?
>
> Regards!
>
> Andon
>
>
> ---------------------------------
> Sucker-punch spam with award-winning protection.
> Try the free Yahoo! Mail Beta.
>
>
> This message and any attachments (the "message") is
> intended solely for the addressees and is confidential.
> If you receive this message in error, please delete it and
> immediately notify the sender. Any use not in accord with
> its purpose, any dissemination or disclosure, either whole
> or partial, is prohibited except formal approval. The internet
> can not guarantee the integrity of this message.
> BNP PARIBAS (and its subsidiaries) shall (will) not
> therefore be liable for the message if modified.
>
> ---------------------------------------------
>
> Ce message et toutes les pieces jointes (ci-apres le
> "message") sont etablis a l'intention exclusive de ses
> destinataires et sont confidentiels. Si vous recevez ce
> message par erreur, merci de le detruire et d'en avertir
> immediatement l'expediteur. Toute utilisation de ce
> message non conforme a sa destination, toute diffusion
> ou toute publication, totale ou partielle, est interdite, sauf
> autorisation expresse. L'internet ne permettant pas
> d'assurer l'integrite de ce message, BNP PARIBAS (et ses
> filiales) decline(nt) toute responsabilite au titre de ce
> message, dans l'hypothese ou il aurait ete modifie.
>
>
>

.

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)
  • 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)
  • 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: IDS on a Mac?
    ... >> If you design systems rather than just throw them over the cubicle wall ... If you join against the temp table, and you have over 10,000 rows. ... (And before Daniel opens his mouth, there's about a million rows in the base table and my temp table has around 150,000 rows, based on the data I want to update. ... Cause I have to do some processing that is easier in a scripting language than in PL/SQL) The reason I can't use an existing GLOBAL TEMP table is that I need to create the index. ...
    (comp.databases.informix)