CODEWITHSUNDEEP
databaseperlsql-server-2008transactions
Gopal SA
Gopal SA

Reputation: 959

perl-dbi #temp table created using dbh handle not accessible when accessed via ->do() with the same handle

I am facing this problem perl DBD::ODBC rollback ineffective with AutoCommit enabled at and while looking at the problem , I found that a very basic thing is failing with Perl::DBI using DBD::ODBC on sql server. But i am not sure if this wont happen with any other driver.

The problem is that when I create a #temp table using $dbh->do and when i try to access the same #temp table using another $dbh->do , i am getting the below error. Also this does not happen all the time , but only intermittently.

Invalid object name '#temp'

$dbh->do("SELECT  ... INTO #temp FROM ...");
$dbh->do("INSERT INTO ... SELECT ... FROM #temp");

The second do fails with 'Invalid object name '#temp''

Kindly help me with the problem.

Upvotes: 0

Views: 2952

Answers (2)

Jon
Jon

Reputation: 11

I was having this problem as well. I tried all of the above but it didnt matter. I stumbled upon this http://bytes.com/topic/sql-server/answers/80443-creating-temporary-table-select-into which solved my problem.

What's happening is that ADO is opening a second connection behind your back. This has really not anything to do with how you created the table.

The reason that ADO opens an extra connection, is because there are rows waiting to be fetched on the first connection, so ADO cannot submit a query on that connection.

I assume that Perl DBI is doing the same, so based on this assumption, here's what I did and it worked perfectly fine:

 my $sth = $dbh->prepare('Select name into #temp from NameTable');
 $sth->execute();
 $sth->fetchall_arrayref();
 $sth = $dbh->prepare('Select a.name, b.age from #temp a, AgeTable b where a.name = name');
 $sth->execute();
 my ($name,$age)
 $sth->bind_columns(\$name,\$age);
 while ( $sth->fetch())
 {
     # processing
 }

Upvotes: 1

bohica
bohica

Reputation: 5990

Not that it answers your question but it might help. The following works for me.

#
# To access temporary tables in MS SQL Server they need to be created via
# SQLExecDirect
#
use strict;
use warnings;
use DBI;

my $h = DBI->connect();

eval {
    $h->do(q{drop table martin});
    $h->do(q{drop table martin2});
};

$h->do(q{create table martin (a int)});
$h->do(q{create table martin2 (a int)});

$h->do('insert into martin values(1)');

my $s;
# this long winded way works:
#$s = $h->prepare('select * into #tmp from martin',
#                    { odbc_exec_direct => 1}
#);
#$s->execute;
# and this works too:
$h->do('select * into #tmp from martin');
# but a prepare without odbc_exec_direct would not work

print "NUM_OF_FIELDS: " . DBI::neat($s->{NUM_OF_FIELDS}), "\n";

$s = $h->selectall_arrayref(q{select * from #tmp});
use Data::Dumper;
print Dumper($s), "\n";

$h->do(q/insert into martin2 select * from #tmp/);
$s = $h->selectall_arrayref(q{select * from martin2});
print Dumper($s), "\n";

Upvotes: 1

Related Questions