Hmmm...
Ok, this works here (of course, you knew I'd say that...:)
I think the catch is that I'm using the newest DBD::ODBC that uses the
SQLExecDirect for the "do". That seems to make the difference. I can't
tell why, yet, but when the create table is called with prepare() then
execute (which is what version 0.28 effectively does), then the next
statement gets destroyed, but I can't tell why. If you use the latest
released DBD::ODBC and use the do(), then the create table works and the
next statement works.
Here's the trace, but I can't figure out why the statement is getting
destroyed. I think it's in the DBI somewhere, but where is the problem...
So, upgrade your DBD::ODBC and use the do() method when creating the table.
dbd_st_prepare'd sql f31727200
INSERT INTO #foo (id, val) VALUES (?, ?)
<- prepare= DBI::st=HASH(0x1d4cd08) at mytest\sqltmptabs.pl line 11
>> DESTROY DISPATCH (DBI::st=HASH(0x1d4ce34) rc1/1 @1 g0 a0) at
mytest\sqltmptabs.pl line 12
<> DESTROY ignored for outer handle DBI::st=HASH(0x1d4ce34) (inner
DBI::st=HASH(0x1ab5528))
>> DESTROY DISPATCH (DBI::st=HASH(0x1ab5528) rc1/1 @1 g0 a0) at
mytest\sqltmptabs.pl line 12
-> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x1ab5528)~INNER)
use DBI;
my $dbh = DBI->connect($ENV{DBI_DSN}, $ENV{DBI_USER}, $ENV{DBI_PASS}, {
RaiseError => 1});
my $sth;
my $sql = 'CREATE TABLE #foo (id INT PRIMARY KEY, val CHAR(4))';
# $dbh->do($sql);
$sth = $dbh->prepare($sql);
$sth->execute;
$sth->finish;
print "Now inserting!\n";
$sth = $dbh->prepare("INSERT INTO #foo (id, val) VALUES (?, ?)");
my $sth2 = $dbh->prepare("INSERT INTO #foo (id, val) VALUES (?, ?)");
$sth2->execute(1, 'foo');
$sth2->execute(2, 'bar');
$sth = $dbh->prepare("Select id, val from #foo");
$sth->execute;
my @row;
while (@row = $sth->fetchrow_array) {
print join(', ', @row), "\n";
}
$dbh->disconnect;
> -----Original Message-----
> From: Kennis Koldewyn [mailto:[EMAIL PROTECTED]]
> Sent: Monday, June 03, 2002 10:33 AM
> To: [EMAIL PROTECTED]
> Subject: RE: Temp Tables w/ DBI:ODBC on SQL Server 2000
>
>
> ----- Jeff Urlwin wrote -----
> DBD::ODBC shouldn't be creating a new connection. What version
> of DBD::ODBC are you using?
> ----- End -----
>
> Yeah, it shouldn't be creating a new connection, and I'm not
> doing it in my code (the code I included in my first message was
> the whole shebang). I'm using version 0.28 of DBD::ODBC.
>
> ----- Jeff mentioned -----
> Some of the more recent versions use SQLExecDirect when using
> do(), instead of the full prepare, etc. Is it possible that
> SQLExecDirect causes a problem (I doubt it).
> ----- End -----
>
> Well, if I replace the dos with prepare, etc. as follows:
>
> ----- Replace lines 17 through 21 with this -----
> my $sth = $dbh->prepare('CREATE TABLE #foo (id INT PRIMARY KEY,
> val CHAR(4))')
> or die $dbh->errstr;
> my $rv = $sth->execute;
> $sth = $dbh->prepare("INSERT INTO #foo (id, val) VALUES (1, 'foo')")
> or die $dbh->errstr;
> $rv = $sth->execute;
> ----- End -----
>
> I still have the same problem.
>
> ----- Jeff asked -----
> Is it possible that you have to quote the table name when using
> this syntax?
> ----- End -----
>
> Nope, that's the correct syntax.
>
> - Kennis
>
>
>
