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