DBI handles sessions differently from running that script in the SQL query
analyzer, or running it as an ISQL or OSQL script. I'm not sure exactly how
you are running this using DBI, but I'll start from the top and comment on
it:
1. You need to break it apart. A statement handle can only have a single
statement prepared - not an entire script.
2.
set nocount on
That tidies up the script in Query analyzer, but is not necessary from a
script, and in fact is gone when the next statement handle is prepared
anyway.
3. #temp_table is a session specific temporary table. It is not visible to
any other connections, and is gone immediately when a connection is broken.
A funny thing about DBI using DBD::ODBC (And I imagine most other dblibrary
connection modules) is that it doesn't recognize a session like that. You
can get around this by using a global temporary table from your Perl script.
You would do that by naming this table ##temp_table (double pound signs are
global temporary tables).
4. \@i int, again, SQL variables are session specific. Actually, they are
more than that - they are batch specific. You won't be able to use these
while executing something using DBI.
5. DECLARE search_cursor Great method (but not necessary for what you're
doing - not even from an OSQL script or in query analyzer) but this is not
how to handle this in DBI. In DBI you could use a SELECT..INTO or an
INSERT...SELECT, or you could prepare one statement handle with the select,
prepare another for the insert (using placeholders) then execute the select
statement handle, use a while loop to navigate through the cursor that is
created by the select statment, and use the values fetched in the
placeholders to execute the insert for each row.
Bottom line, you can use the variables in perl, and write this script out to
a file, then call OSQL or ISQL to execute it, and everything will work
(although think about that cursor, that is totally unnecessary, and very,
very slow compared with an INSERT..SELECT combo, or a SELECT..INTO..FROM
statement).
To bring the results back into your script from an OSQL statement (although
they're much more work to deal with if you do this instead of using DBI):
my $results = `OSQL -Sservername -Uuser -Ppw -Iscript_file`;
Possible...but about the only time I'd do something like that is when
inserting into a table that has an identity column when I am using explicit
values for that column.
Anyway, hope this helps. You're right, the script you wrote would word from
query analyzer, but not with DBI. Hopefully this can help you with that.
Steve Howard.
-----Original Message-----
From: Jonathan C. Popp [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, August 01, 2001 3:50 PM
To: [EMAIL PROTECTED]
Subject: SQL Service Pack 3 Install Resulted in ODBC Errors
Hi,
Just installed SQL Service Pack 3 on some of our systems and now the cursor
behavior has changed. When I run the below SQL statement in SQL Query
Analyzer I get the correct output; however, DBI::ODBC returns the following
error:
Error Detail: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find
prepared statement with handle 0. (SQL-37000)(DBD: st_execute/SQLExecute
err=-1): : No such file or directory
Below is a copy of SQL statement. Any assistance would be greatly
appreciated.
Thanks,
Jon
################ SQL ###########################
set nocount on
DECLARE search_cursor CURSOR LOCAL SCROLL OPTIMISTIC FOR
select
ID as EntryID,
Name as FullName,
EntryCode
FROM Entry
$where
order by EntryCode $asc
create table #temp_table
(
ORD int NOT NULL ,
Results int NULL ,
EntryID int NULL ,
FullName varchar (200) NULL ,
EntryCode varchar (12) NULL
)
OPEN search_cursor
insert into #temp_table (ord,results) values (0,\@\@CURSOR_ROWS)
declare
\@i int,
\@id int,
\@fn varchar (200),
\@ec varchar (12)
select \@i = 0
FETCH ABSOLUTE $st FROM search_cursor INTO \@id,\@fn,\@ec
if ( \@\@FETCH_STATUS = 0 )
begin
select \@i = \@i + 1
insert into #temp_table values (\@i,null,\@id,\@fn,\@ec)
end
while ( (\@i < $ps) and (\@\@FETCH_STATUS = 0) )
begin
FETCH NEXT FROM search_cursor INTO \@id,\@fn,\@ec
if ( \@\@FETCH_STATUS = 0 )
begin
select \@i = \@i + 1
insert into #temp_table values (\@i,null,\@id,\@fn,\@ec)
end
end
select * from #temp_table order by ord
