Thank you, Jeff. That has me back up and working again. I really appreciate your OUTSTANDING WORK!
Steve H. -----Original Message----- From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 30, 2002 12:12 AM To: Howard, Steven (US - Tulsa); 'Jeff Urlwin' Cc: [EMAIL PROTECTED] Subject: RE: Problem with stored procedures with inserts. Ok -- I have a quick fix for this which I'll release shortly. I still need to look into a few other bugs. This may not be a complete fix, but it seems to resolve the issues. I plan on making a SQLServer specific set of tests that add these issues and others that have come up, so they can be properly tested. Thanks, Jeff > > Ok, I have modified the script you sent me to make it re-produce this > condition. The script you sent did work as you sent it, and when > I modified > it to take an input parameter, it still worked. However, when I added a > return code, it gave the invalid cursor state. Always, it executes twice > without error, but on the third and later executions, it gives > the "invalid > cursor state" error. This third-execution is consistent with what I am > seeing in my production scripts as well. > > Here it is: > > > eval {$dbh->do("DROP TABLE table1");}; > eval {$dbh->do("CREATE TABLE table1 (i INTEGER)");}; > > eval {$dbh->do("DROP TABLE table2");}; > eval {$dbh->do("CREATE TABLE table2 (i INTEGER)");}; > > eval {$dbh->do("DROP PROCEDURE proc1");}; > eval {$dbh->do("CREATE PROCEDURE proc1 \@inputval int AS ". > "INSERT INTO table1 VALUES (\@inputval) > return 0");}; > > # Insert a row into table1, either directly or indirectly: > my $direct = 0; > my $sth1; > if ($direct) { > $sth1 = $dbh->prepare ("INSERT INTO table1 VALUES (1)"); > } else { > $sth1 = $dbh->prepare ("{? = call proc1(?) }"); > } > > # Insert a row into table2 (this fails after an indirect insertion): > $sth1->bind_param_inout(1, \$output, 50, DBI::SQL_INTEGER); > $sth1->bind_param(2, $_, DBI::SQL_INTEGER); > $sth1->execute(); > $sth1->execute(); > $sth1->execute(); > $sth1->execute(); > > > $dbh->disconnect; > > > > > > -----Original Message----- > From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] > Sent: Monday, July 29, 2002 10:48 AM > To: Sthoward; [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: RE: Problem with stored procedures with inserts. > > > > > I am having a problem with a script after upgrading the DBD::ODBC > > the script > > was running from version 0.28 to 0.45.8. I am getting invalid > cursor state > > errors on stored procedures that contain insert queries. This > is using MS > > SQL 7.0, Activestate Perl 5.6.1 bild 631. Is there something > > additional I am > > needing to do? I have searched the DBI archives and there seems > to be some > > indication that similar problems occurred with 0.45.4, but I > was thinking > > this was corrected in .8, and don't see any indication of > similar problems > > therein. > > It's supposed to be corrected in 0.45_8. Can you shrink it down to a > reasonably small sample with any create table, create procedure, > etc at the > top of the script? Joe Tebelskis supplied a nice one that now works on > SQLServer. Here's testproc2.pl which shows how this "now" works. Can you > test to see if it works on your site? It may be a driver issue, > too, but... > If testproc2.pl does work, but you are doing something differently, please > create a testproc3.pl to help resolve the issue, if you can. If > testproc2.pl does *not* work on your site, please: > a) ensure you are running .45_8 (sanity check) > b) check which version of SQL Server driver you have. I have the > latest > from MDAC 2.7. > > Regards, > > Jeff > > #!perl -w > use strict; > use DBI; > > # Connect to the database, and create two tables and a stored procedure: > my $dbh=DBI->connect() or die "Can't connect"; > > eval {$dbh->do("DROP TABLE table1");}; > eval {$dbh->do("CREATE TABLE table1 (i INTEGER)");}; > > eval {$dbh->do("DROP TABLE table2");}; > eval {$dbh->do("CREATE TABLE table2 (i INTEGER)");}; > > eval {$dbh->do("DROP PROCEDURE proc1");}; > eval {$dbh->do("CREATE PROCEDURE proc1 AS ". > "BEGIN INSERT INTO table1 VALUES (1); END;");}; > > # Insert a row into table1, either directly or indirectly: > my $direct = 0; > my $sth1; > if ($direct) { > $sth1 = $dbh->prepare ("INSERT INTO table1 VALUES (1)"); > } else { > $sth1 = $dbh->prepare ("{ call proc1 }"); > } > $sth1->execute(); > > # Insert a row into table2 (this fails after an indirect insertion): > my $sth2 = $dbh->prepare ("INSERT INTO table2 VALUES (2)"); > $sth2->execute(); > > my $sth = $dbh->prepare("select i from table1 union select i from > table2"); > my @row; > $sth->execute; > while (@row = $sth->fetchrow_array) { > print $row[0], "\n"; > } > $dbh->disconnect; > > > > > > > This worked without error as is on DBD::ODBC 0.28. > > > > > > Thanks, > > > > Steve Howard > > > > an example of the portion of the script that is having problems: > > > > $tgh = $tg->prepare($queries{login}) || do{$servers->{FAILED} = 8; > > $servers{FAILEDREASON} = $DBI::errstr; return;}; > > # prepare the execution of the stored procedure to process > > a line of logins > > results. > > $sql = qq/{CALL TPROCESSLOGIN (?, ?, ?, ?, ?, ?, ?, ?, ?, > > ?, ?, ?, ?)}/; > > my $sth = $dbh->prepare($sql) || do{$servers->{FAILED} = 9; > > $servers->{FAILEDREASON} = $DBI::errstr; return;}; > > # execute the statement in $tgh and process the results. > > $tgh->execute() || do{$servers->{FAILED} = 10; > > $servers->{FAILEDREASON} = > > $DBI::errstr; return;}; > > $tgh->bind_columns(undef, \@values[0..11]); > > # $sth->bind_param(2, $values[1], DBI::SQL_VARBINARY); # > > commented when DBD > > upgraded > > # $sth->bind_param(7, $values[6], DBI::SQL_VARBINARY); # > > commented when DBD > > upgraded > > while ($tgh->fetch()) > > { > > $sth->execute(@values[0..11], $servers->{SERVERID}) || > > do{$servers->{FAILED} = 11; $servers->{FAILEDREASON} > > = $DBI::errstr; > > return;}; > > # process for drop checking > > $logins{$values[0]}->{$values[1]} = 0; > > } > > > > > > > > The stored procedure being executed: > > > > > > CREATE PROCEDURE dbo.TPROCESSLOGIN > > @LOGINAME SYSNAME > > , @SID VARBINARY(85) > > , @SRVID SMALLINT > > , @XSTATUS SMALLINT > > , @XDATE1 VARCHAR(25) > > , @XDATE2 VARCHAR(25) > > , @PASSWORD VARBINARY(256) > > , @DBID SMALLINT > > , @LANGUAGE SYSNAME > > , @ISRPCINMAP SMALLINT > > , @ISHQOUTMAP SMALLINT > > , @SELFOUTMAP SMALLINT > > , @SERVERID INT > > > > AS > > -- CHECK FOR NULL NAME. WE DO NOT DEAL WITH NULL NAMES. > > IF @LOGINAME IS NULL > > RETURN -1 > > DECLARE @RETVAL INT, @LOGINID INT > > > > -- CHECK TO BE SURE THE LOGIN EXISTS, IF NOT, ADD IT. ALSO > > NEED TO GET > > @LOGINID IN THIS SECTION. > > > > IF NOT EXISTS (SELECT 1 FROM TLOGINS L JOIN TSERVERS S ON > > L.SERVERID = > > S.SERVERID > > LEFT JOIN TLOGINSDROPPED R ON L.LOGINID = R.LOGINID > > WHERE R.LOGINID IS > > NULL > > AND S.SERVERID = @SERVERID AND L.NAME = @LOGINAME) > > BEGIN > > EXEC @RETVAL = TADDLOGIN @SERVERID, @LOGINAME, @SID > > IF @RETVAL = -1 > > RETURN -1 > > SELECT @LOGINID = @@IDENTITY > > END > > ELSE > > BEGIN > > SELECT @LOGINID = (SELECT L.LOGINID FROM TLOGINS L > > JOIN TSERVERS S ON > > L.SERVERID = S.SERVERID > > LEFT JOIN TLOGINSDROPPED R ON L.LOGINID = > > R.LOGINID WHERE R.LOGINID IS > > NULL > > AND S.SERVERID = @SERVERID AND L.NAME = @LOGINAME ) > > END > > > > -- WE MUST HAVE A LOGINID TO PROCEED: > > IF @LOGINID IS NULL -- SHOULD NEVER HAPPEN, BUT JIC. > > BEGIN > > RAISERROR("NO LOGINID LOCATED FOR %s IN SERVERID > > %d", 16, 1, @LOGINAME, > > @SERVERID) > > RETURN -1 > > END > > > > -- NOW INSERT HISTORY IF THEY HAVE NO EXISTING RECORD > > IDENTICAL TO THIS: > > > > IF NOT EXISTS (SELECT 1 FROM TLOGINSHIST O WHERE > > LOGINID = @LOGINID > > AND ((SRVID = @SRVID) OR (SRVID > > IS NULL AND @SRVID IS NULL)) > > AND XSTATUS = @XSTATUS > > AND XDATE1 = @XDATE1 > > AND XDATE2 = @XDATE2 > > AND ((PASSWORD = @PASSWORD) OR > > (PASSWORD IS NULL AND @PASSWORD IS NULL)) > > AND DBID = @DBID > > AND LANGUAGE = @LANGUAGE > > AND ((ISRPCINMAP = @ISRPCINMAP) OR > > (ISRPCINMAP IS NULL AND @ISRPCINMAP IS > > NULL)) > > AND ((ISHQUOTMAP = @ISHQOUTMAP) OR > > (ISHQUOTMAP IS NULL AND @ISHQOUTMAP IS > > NULL)) > > AND ((SELFOUTMAP = @SELFOUTMAP) OR > > (SELFOUTMAP IS NULL AND @SELFOUTMAP IS > > NULL)) > > AND EFFDT = (SELECT > > MAX(EFFDT) FROM TLOGINSHIST I WHERE O.LOGINID = > > I.LOGINID) > > ) > > BEGIN > > INSERT INTO TLOGINSHIST (LOGINID, SRVID, XSTATUS, > > XDATE1, XDATE2, > > PASSWORD, DBID, LANGUAGE, > > ISRPCINMAP, ISHQUOTMAP, SELFOUTMAP) > > VALUES (@LOGINID, @SRVID, @XSTATUS, > > @XDATE1, @XDATE2, @PASSWORD, @DBID, > > @LANGUAGE, > > @ISRPCINMAP, @ISHQOUTMAP, @SELFOUTMAP) > > IF @@ERROR != 0 > > RETURN -1 > > RETURN 0 > > END > > > > GO > > > > > > And the stored procedure called within that one: > > > > > > CREATE PROCEDURE dbo.TADDLOGIN > > @SERVERID INT > > , @NAME SYSNAME > > , @SID VARBINARY(85) > > AS > > -- CHECK TO SEE THAT SERVERID IS VALID: > > IF NOT EXISTS (SELECT * FROM TSERVERS L LEFT JOIN > > TSERVERSDROPPED R ON > > L.SERVERID = R.SERVERID > > WHERE L.SERVERID = @SERVERID AND R.SERVERID IS NULL) > > BEGIN > > RAISERROR("SERVERID %d DOES NOT EXIST IN THE > > TSERVERS TABLE", 16, 1, > > @SERVERID) > > RETURN(-1) > > END > > -- CHECK TO SEE THAT THE NAME DOESN'T ALREADY EXIST IN THAT SERVER > > IF EXISTS (SELECT * FROM TLOGINS L LEFT JOIN TLOGINSDROPPED > > R ON L.LOGINID > > = R.LOGINID > > WHERE L.SERVERID = @SERVERID AND L.NAME = @NAME > > AND R.LOGINID IS NULL) > > BEGIN > > RAISERROR("NAME %s ALREADY EXISTS IN TABLE TLOGINS > > FOR SERVERID %d", 16, > > 1, @NAME, @SERVERID) > > RETURN (-1) > > END > > -- IF A SID IS GIVEN, WE NEED TO CHECK THAT IT DOESN'T > > ALREADY EXIST. > > -- AT THIS POINT, WE KNOW THAT THE NAME DOESN'T EXIST, SO > > IF THE SID DOES, > > THERE MAY BE AN ORPHANED USER. > > IF @SID IS NOT NULL > > BEGIN > > IF EXISTS (SELECT * FROM TLOGINS L LEFT JOIN > > TLOGINSDROPPED R ON L.LOGINID > > = R.LOGINID > > WHERE L.SERVERID = @SERVERID AND L.SID = > > @SID AND R.LOGINID IS NULL) > > BEGIN > > RAISERROR("SID ALREADY EXISTS ON SERVERID > > %d, BUT NOT FOR %s", 16, 1, > > @SERVERID, @NAME) > > RETURN (-1) > > END > > END > > -- IF WE GET HERE, IT IS SAFE TO MAKE THE INSERT. > > INSERT INTO TLOGINS (SERVERID, NAME, SID) VALUES > > (@SERVERID, @NAME, @SID) > > RETURN (0) > > > > GO > > > > > > > > Error I receive: > > > > DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Invalid > > cursor state (SQL-24000)(DBD: st_execute/SQLExecute err=-1) at > > GATHERFUNCS.pm line 95. > > DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Invalid > > cursor state (SQL-24000)(DBD: st_execute/SQLExecute err=-1) at > > GATHERFUNCS.pm line 95. > > DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Invalid > > cursor state (SQL-24000)(DBD: st_execute/SQLExecute err=-1) at > > GATHERFUNCS.pm line 95. > > DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Invalid > > cursor state (SQL-24000)(DBD: st_execute/SQLExecute err=-1) at > > GATHERFUNCS.pm line 95. > > DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Invalid > > cursor state (SQL-24000)(DBD: st_execute/SQLExecute err=-1) at > > GATHERFUNCS.pm line 95. > > DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Invalid > > cursor state (SQL-24000)(DBD: st_execute/SQLExecute err=-1) at > > GATHERFUNCS.pm line 95. > > DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Invalid > > cursor state (SQL-24000)(DBD: st_execute/SQLExecute err=-1) at > > GATHERFUNCS.pm line 95. > > DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Invalid > > cursor state (SQL-24000)(DBD: st_execute/SQLExecute err=-1) at > > GATHERFUNCS.pm line 95. > > DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Invalid > > cursor state (SQL-24000)(DBD: st_execute/SQLExecute err=-1) at > > GATHERFUNCS.pm line 95. > > > > > > - This message (including any attachments) contains confidential > information > intended for a specific individual and purpose, and is protected > by law. - > If you are not the intended recipient, you should delete this message and > are hereby notified that any disclosure, copying, or distribution of this > message, or the taking of any action based on it, is strictly prohibited. > - This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. - If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited.
