Unfortunately your suggestion doesn't work, because in SQL Server functions cannot contain INSERT/UPDATE statements.
Returning a result from a stored procedure via SELECT worked in versions 0.28 and 0.43, but it stopped working in 0.45_4, apparently due to the ODBC 3.0 API. So I still need a way to get the result of the stored procedure. -- Joe -----Original Message----- From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 23, 2002 8:58 PM To: Joe Tebelskis; Jeff Urlwin Cc: [EMAIL PROTECTED] Subject: RE: :ODBC 0.45_4 Joe, I'm not a SQL Server expert, so take this with some grain of salt. What I see is a procedure which, according to ODBC, is not returning anything. What I see in the log is that no columns are in the result set (SQLNumResultCols returns 0) and adding a little debugging to my log produces SQLRowCount returning 0... What I am pretty sure will work is to: change the procedure to a function change the function to "returns int" change the call to prepare (" { ? = call yourfunction(?, ?, ?, ?, ?) } ") bind_param_inout(1, \$return, 50, SQL_INTEGER) bind_param(2, etc) rest of params. See mytest\testinout.pl for example/test. That doesn't cover everything you may need, since it no longer returns a result set. A more complex function may need a result set. Also, did this work under DBD::ODBC 0.28??? Thanks, Jeff > -----Original Message----- > From: Joe Tebelskis [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, July 23, 2002 11:58 AM > To: Jeff Urlwin > Subject: RE: :ODBC 0.45_4 > > > I've got a table: > > CREATE TABLE [dbo].[logLocks] ( > [logid] [varchar] (100) COLLATE > SQL_Latin1_General_CP1_CI_AS NOT NULL , > [status] [varchar] (100) COLLATE > SQL_Latin1_General_CP1_CI_AS NOT NULL , > [locker] [varchar] (100) COLLATE > SQL_Latin1_General_CP1_CI_AS NOT NULL , > [doneBytes] [int] NOT NULL , > [fTime1] [datetime] NOT NULL , > ) ON [PRIMARY] > > And a stored procedure: > CREATE PROCEDURE dbo.SetLock > (@logid varchar(100), > @status varchar(10), > @locker varchar(100), > @doneBytes int, > @fTime1 datetime) > AS > > DECLARE > @oldStatus varchar(10), -- old status of logid, if any > @oldLocker varchar(100), -- old locker of logid, if any > @result int; -- boolean result > > SET @result = 1; -- assume success > BEGIN TRANSACTION > > /* Check if this logid has an existing record */ > SET @oldStatus = (SELECT status FROM LogLocks WHERE logid=@logid); > > /* If the record doesn't exist yet, insert it: */ > IF (@oldStatus IS NULL) > BEGIN > INSERT INTO LogLocks (logid, status, locker, > doneBytes, fTime1) > VALUES (@logid, @status, @locker, > @doneBytes, @fTime1); > IF (@@error != 0) SET @result = 0; -- return failure? > END > > /* Else if we're resetting UNLOCKED <=> LOCKED, update the record: */ > ELSE IF (@oldStatus != @status) > BEGIN > UPDATE LogLocks SET status = @status, > locker = @locker, > doneBytes = @doneBytes, > fTime1 = @fTime1 > WHERE logid = @logid; > IF (@@error != 0) SET @result = 0; -- return failure? > END > > /* Else, someone is trying to redo something that's already > been done. */ > /* If @locker is changing, this is a race condition => return > failure; */ > /* If @locker is unchanged, this is crash recovery => return > success. */ > ELSE BEGIN > SET @oldLocker = (SELECT locker FROM LogLocks WHERE logid=@logid); > IF (@oldLocker != @locker) SET @result = 0; > END; > > COMMIT TRANSACTION > SELECT @result; > > I'm executing: > my $sth_setLock = $dbh->prepare ( > "EXEC setLock ?,?,?,?,?" > ); > $sth_setLock->bind_param (1, "JOET_log2_20020712170736", > SQL_VARCHAR); > $sth_setLock->bind_param (2, "LOCKED", SQL_VARCHAR); > $sth_setLock->bind_param (3, "JOET", SQL_VARCHAR); > $sth_setLock->bind_param (4, 0, SQL_INTEGER); > $sth_setLock->bind_param (5, "2002-07-12 17:07:36", > SQL_TYPE_TIMESTAMP); > $sth_setLock->execute > or die ($DBI::errstr) unless ($noexec); > while (my @data = $sth_setLock->fetchrow_array()) { > ($success) = @data; > } > > The fetchrow_array generates the error: > DBD::ODBC::st fetchrow_array failed: (DBD: no select statement > currently executing err=-1) > > I've attached the output of trace level 8. > > -- Joe > > -----Original Message----- > From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] > Sent: Tuesday, July 23, 2002 4:08 AM > To: Joe Tebelskis; Jeff Urlwin > Cc: [EMAIL PROTECTED] > Subject: RE: :ODBC 0.45_4 > > > Can you send me a trace and/or small sample? > > Thanks, > > Jeff > > > -----Original Message----- > > From: Joe Tebelskis [mailto:[EMAIL PROTECTED]] > > Sent: Tuesday, July 23, 2002 3:04 AM > > To: Jeff Urlwin > > Cc: [EMAIL PROTECTED] > > Subject: DBD::ODBC 0.45_4 > > > > > > Thanks, I've received and installed DBD::ODBC 0.45_4. But it > > causes at least one new problem: > > > > I'm making a call to a stored procedure that returns a result via > > a SELECT, and I'm fetching that result via > > $sth->fetchrow_result(). This worked fine in version 0.43, but > > 0.45_4 gives the error: > > DBD::ODBC::st fetchrow_array failed: (DBD: no select statement > > currently executing err=-1) > > > > How should I now get results from a stored procedure? > > > > -- Joe > > > > -----Original Message----- > > From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] > > Sent: Monday, July 22, 2002 7:11 PM > > To: Joe Tebelskis; Jeff Urlwin > > Cc: [EMAIL PROTECTED] > > Subject: RE: bugs in DBD::ODBC 0.43 > > > > > > Joe, > > > > You might have to wait, but I'll send it to you in another > > (offline) e-mail. > > > > Since you are on Windows, the biggest change is that: > > SQL_DATE is now SQL_TYPE_DATE and > > SQL_TIMESTAMP is now SQL_TYPE_TIMESTAMP. > > > > Your Windows should already be updated to 3.x, so you should be fine > > regarding the version. The largest problem will be those who have older > > driver managers (such as older iODBC versions such as 2.5x). The other > > worry is for those who do not link to a driver manager, rather they link > > directly to a driver itself. Typically the driver manager hides the > > shortcomings of drivers and maps newer calls to the older > calls. This is > > not typically done under Windows because the driver manager comes with > > Windows whereas on Unix it's an add-on and, with older versions > of driver > > managers, harder to install and configure than linking to the > > driver itself. > > With the latest version of unixODBC, it's pretty easy... > > > > It should be transparent for most operations, taking into > account the type > > changes listed above. > > > > I should also note that the varchar binding by the SQL Server > > driver bothers > > me. Neither Oracle nor DB2 exhibit the same behavior. I suspect > > it's a bug > > in what they are doing and I'm thankful we have a work around. > > > > Regards, > > > > Jeff > > > > > > > -----Original Message----- > > > From: Joe Tebelskis [mailto:[EMAIL PROTECTED]] > > > Sent: Monday, July 22, 2002 7:37 PM > > > To: Jeff Urlwin > > > Cc: [EMAIL PROTECTED] > > > Subject: RE: bugs in DBD::ODBC 0.43 > > > > > > > > > Thank you, Jeff. I already knew that SQL Server's datetime > > > rounds to the nearest 3 msec, so your fix will be perfect for me. > > > > > > I'm trying to retrieve > > > http://cpan.org/authors/id/J/JU/JURL/DBD-ODBC-0.45_4.tar.gz, but > > > it doesn't seem to be there. Do I just need to wait a while longer? > > > Also, I'm confused about the implications of the ODBC 3.0 API > > > upgrade. Will I need to modify my Perl script (which uses DBI), > > > or is the 3.0 API upgrade transparent to me? What specific > > > components might I need to upgrade to 3.0 in order to use your > > > DBD::ODBC 0.45_4, and how do I tell whether those upgrades are > > necessary? > > > > > > -- Joe > > > > > > -----Original Message----- > > > From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] > > > Sent: Monday, July 22, 2002 3:56 PM > > > To: Joe Tebelskis; Jeff Urlwin > > > Cc: [EMAIL PROTECTED] > > > Subject: RE: bugs in DBD::ODBC 0.43 > > > > > > > > > Joe -- > > > > > > Want the good news or the bad news first? > > > > > > Good news: I am now getting SQL Server to store the full time. > > I had been > > > working on this anyway as part of my upgrade to ODBC 3.0 api. > > > I'm going to > > > push out a developer version to CPAN tonight (now) which will not > > > be indexed > > > by CPAN for "general" accidental distribution. It's going up > as version > > > 0.45_4. Also, I modified your test to work on more than just SQL > > > Server for > > > sanity testing. DB2 works perfectly with or without the binding > > > (except the > > > string on the date returned is longer... DB2 returns 2001-01-01 > > > 01:01:01.111000 instead of 2001-01-01 01:01:01.111. > > > > > > Bad news: SQL Server rounds some of the times. Here's the output > > > with bind > > > parameter: > > > > > > Date type = datetime > > > Inserting: 0, 2001-01-01 01:01:01.111, string length 12 > > > Inserting: 1, 2002-02-02 02:02:02.222, string length 114 > > > Inserting: 2, 2003-03-03 03:03:03.333, string length 251 > > > Inserting: 3, 2004-04-04 04:04:04.444, string length 282 > > > Inserting: 4, 2005-05-05 05:05:05.555, string length 131 > > > > > > Retrieving: 0, 2001-01-01 01:01:01.110, string length 12 !time > > > Retrieving: 1, 2002-02-02 02:02:02.223, string length 114 !time > > > Retrieving: 2, 2003-03-03 03:03:03.333, string length 251 > > > Retrieving: 3, 2004-04-04 04:04:04.443, string length 282 !time > > > Retrieving: 4, 2005-05-05 05:05:05.557, string length 131 !time > > > > > > > > > As you can see, the timestamp is being rounded... > > > > > > However, I think that should get you past the problem... > > > > > > Regards, > > > > > > Jeff > > > > > > > -----Original Message----- > > > > From: Joe Tebelskis [mailto:[EMAIL PROTECTED]] > > > > Sent: Monday, July 22, 2002 6:12 PM > > > > To: Jeff Urlwin > > > > Cc: [EMAIL PROTECTED] > > > > Subject: RE: bugs in DBD::ODBC 0.43 > > > > > > > > > > > > Thank you, Jeff. Your suggestion of using "bind_param" > > > > explicitly seems to fix the problem for varchars... but > > > > unfortunately not for datetimes. Here is a test script that > > > > demonstrates the problem on a small sample of data: > > > > ------------------------------ > > > > #!perl -w > > > > use strict; > > > > use Getopt::Std; > > > > use DBI qw(:sql_types); > > > > > > > > my $usage = "perl dbtest.pl [-b]. -b binds parameters > explicitly.\n"; > > > > my @data = ( > > > > ["2001-01-01 01:01:01.111", "a" x 12], # "aaaaaaaaaaaa" > > > > ["2002-02-02 02:02:02.222", "b" x 114], > > > > ["2003-03-03 03:03:03.333", "c" x 251], > > > > ["2004-04-04 04:04:04.444", "d" x 282], > > > > ["2005-05-05 05:05:05.555", "e" x 131] > > > > ); > > > > > > > > # Get command line options: > > > > my %args; > > > > getopts ("b", \%args) or die $usage; > > > > my $bind = $args{"b"}; > > > > > > > > # Connect to the database and create the table: > > > > my $dbh=DBI->connect() or die "Can't connect"; > > > > $dbh->{RaiseError} = 1; > > > > $dbh->{LongReadLen} = 800; > > > > eval { > > > > $dbh->do("DROP TABLE foo"); > > > > }; > > > > $dbh->do("CREATE TABLE foo (i INTEGER, time DATETIME, str > > > > VARCHAR(4000))"); > > > > > > > > # Insert records into the database: > > > > my $sth1 = $dbh->prepare("INSERT INTO FOO (i,time,str) values > > (?,?,?)"); > > > > for (my $i=0; $i<@data; $i++) { > > > > my ($time,$str) = @{$data[$i]}; > > > > print "Inserting: $i, $time, string length ".length($str)."\n"; > > > > if ($bind) { > > > > $sth1->bind_param (1, $i, SQL_INTEGER); > > > > $sth1->bind_param (2, $time, SQL_TIMESTAMP); > > > > $sth1->bind_param (3, $str, SQL_LONGVARCHAR); > > > > $sth1->execute or die ($DBI::errstr); > > > > } else { > > > > $sth1->execute ($i, $time, $str) or die ($DBI::errstr); > > > > } > > > > } > > > > print "\n"; > > > > > > > > # Retrieve records from the database, and see if they match > > > original data: > > > > my $sth2 = $dbh->prepare("SELECT i,time,str FROM foo"); > > > > $sth2->execute or die ($DBI::errstr); > > > > while (my ($i,$time,$str) = $sth2->fetchrow_array()) { > > > > print "Retrieving: $i, $time, string length ".length($str)."\t"; > > > > print "!time " if ($time ne $data[$i][0]); > > > > print "!string" if ($str ne $data[$i][1]); > > > > print "\n"; > > > > } > > > > $dbh->disconnect; > > > > > > > > ------------------------ > > > > When passing parameters to execute(), both timestamps and strings > > > > get stored incorrectly: > > > > > > > > DOS> perl dbtest.pl > > > > Inserting: 0, 2001-01-01 01:01:01.111, string length 12 > > > > Inserting: 1, 2002-02-02 02:02:02.222, string length 114 > > > > Inserting: 2, 2003-03-03 03:03:03.333, string length 251 > > > > Inserting: 3, 2004-04-04 04:04:04.444, string length 282 > > > > Inserting: 4, 2005-05-05 05:05:05.555, string length 131 > > > > > > > > Retrieving: 0, 2001-01-01 01:01:00.000, string length 12 > !time > > > > Retrieving: 1, 2002-02-02 02:02:00.000, string length 80 > > > > !time !string > > > > Retrieving: 2, 2003-03-03 03:03:00.000, string length 251 > !time > > > > Retrieving: 3, 2004-04-04 04:04:00.000, string length 251 > > > > !time !string > > > > Retrieving: 4, 2005-05-05 05:05:00.000, string length 131 > !time > > > > ----------------------- > > > > When using bind_param, strings are stored correctly, but > > > > timestamps are still stored incorrectly: > > > > > > > > DOS> perl dbtest.pl -b > > > > Inserting: 0, 2001-01-01 01:01:01.111, string length 12 > > > > Inserting: 1, 2002-02-02 02:02:02.222, string length 114 > > > > Inserting: 2, 2003-03-03 03:03:03.333, string length 251 > > > > Inserting: 3, 2004-04-04 04:04:04.444, string length 282 > > > > Inserting: 4, 2005-05-05 05:05:05.555, string length 131 > > > > > > > > Retrieving: 0, 2001-01-01 01:01:00.000, string length 12 > !time > > > > Retrieving: 1, 2002-02-02 02:02:00.000, string length 114 > !time > > > > Retrieving: 2, 2003-03-03 03:03:00.000, string length 251 > !time > > > > Retrieving: 3, 2004-04-04 04:04:00.000, string length 282 > !time > > > > Retrieving: 4, 2005-05-05 05:05:00.000, string length 131 > !time > > > > --------------------- > > > > Again, I'm using ActivePerl 5.6.1 and DBI 1.201 under Windows > > > > 2000, with SQL Server. > > > > > > > > -- Joe > > > > > > > > -----Original Message----- > > > > From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] > > > > Sent: Sunday, July 21, 2002 5:00 AM > > > > To: Joe Tebelskis; Jeff Urlwin > > > > Cc: [EMAIL PROTECTED] > > > > Subject: RE: bugs in DBD::ODBC 0.43 > > > > > > > > > > > > Joe, > > > > > > > > I created a test table of an integer (primary key) and a text field > > > > varchar(4000). I have a loop that inserts varying size values. > > > > I can work around the problem/fix the problem by changing: > > > > $sth->execute($i, $tmp) > > > > to: > > > > > > > > $sth->bind_param(1, $i, SQL_INTEGER); > > > > $sth->bind_param(2, $tmp, SQL_LONGVARCHAR); > > > > $sth->execute; > > > > > > > > Attached is the script. The lengths come from your original > > > > message to me. > > > > > > > > Regards, > > > > > > > > Jeff > > > > > > > > > -----Original Message----- > > > > > From: Joe Tebelskis [mailto:[EMAIL PROTECTED]] > > > > > Sent: Friday, July 19, 2002 7:16 PM > > > > > To: Jeff Urlwin > > > > > Subject: RE: bugs in DBD::ODBC 0.43 > > > > > > > > > > > > > > > Now the "clipping" problem is occurring with datetimes as well. > > > > > That is, I'm inserting "2002-07-12 17:07:36.210" into a datetime > > > > > field, but the resulting datetime field contains "2002-07-12 > > > > > 17:07:00.000", apparently because the string is getting truncated > > > > > before it's converted to the datetime type. > > > > > > > > > > I've attached a level 8 trace of this behavior for you. Be aware > > > > > that the table is slightly different than before, it's now > > > > > defined like this: > > > > > > > > > > CREATE TABLE dbo.tfFragment ( > > > > > machine varchar(20) NOT NULL, > > > > > logX varchar(10) NOT NULL, > > > > > thread varchar(10) NOT NULL, > > > > > sTimem1 datetime NOT NULL, > > > > > sTimem2 datetime NOT NULL, > > > > > stage varchar(10) NULL, > > > > > qUrlX varchar(500) NULL, > > > > > qTime datetime NULL, > > > > > usrid varchar(100) NULL, > > > > > trigid varchar(10) NULL, > > > > > empty bit NULL, > > > > > narrative varchar(4000) NULL, > > > > > narrative1 varchar(3000) NULL, > > > > > gotHead bit NULL, > > > > > gotTail bit NULL, > > > > > CONSTRAINT [PK_tfFragment] PRIMARY KEY NONCLUSTERED > > > > > (machine, logX, thread, sTimem1) > > > > > ) > > > > > > > > > > Please let me know if you think this can be fixed sometime soon, > > > > > otherwise I won't be able to use this version of DBD::ODBC. > > > > > > > > > > -- Joe > > > > > > > > > > -----Original Message----- > > > > > From: Joe Tebelskis > > > > > Sent: Friday, July 19, 2002 2:16 PM > > > > > To: 'Jeff Urlwin' > > > > > Subject: RE: bugs in DBD::ODBC 0.43 > > > > > > > > > > > > > > > Here's the level 8 trace output. Thanks for looking at this. > > > > > > > > > > -- Joe > > > > > > > > > > -----Original Message----- > > > > > From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] > > > > > Sent: Friday, July 19, 2002 2:10 PM > > > > > To: Joe Tebelskis; Jeff Urlwin > > > > > Subject: RE: bugs in DBD::ODBC 0.43 > > > > > > > > > > > > > > > Sorry, can you bump it up to 8 and resend? (delete it first...) > > > > I want to > > > > > see the values of SQLBindParameter and level 8 contains that... > > > > > > > > > > Regards, > > > > > > > > > > Jeff > > > > > > > > > > > -----Original Message----- > > > > > > From: Joe Tebelskis [mailto:[EMAIL PROTECTED]] > > > > > > Sent: Friday, July 19, 2002 3:59 PM > > > > > > To: Jeff Urlwin > > > > > > Subject: RE: bugs in DBD::ODBC 0.43 > > > > > > > > > > > > > > > > > > Attached is a trace output (about 400 Kb). Search for the value > > > > > > "251" (it occurs just a few times). Of the 103 records > that were > > > > > > inserted into the database, there was a series of them that had > > > > > > strings of length 7, 114, 251, 282, 281, 276, 131, which got > > > > > > inserted into the "narrative" field with lengths 7, 114, 251, > > > > > > 251, 251, 251, 131. There was one more record, sometime later, > > > > > > that also had a string with length 251, but it did not cause > > > > > > subsequent strings to get clipped. > > > > > > > > > > > > I hope this helps. > > > > > > > > > > > > -- Joe > > > > > > > > > > > > -----Original Message----- > > > > > > From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] > > > > > > Sent: Friday, July 19, 2002 12:16 PM > > > > > > To: Joe Tebelskis; Jeff Urlwin > > > > > > Subject: RE: bugs in DBD::ODBC 0.43 > > > > > > > > > > > > > > > > > > Set DBI's trace level to 5 and run it...(it's an > > > environment variable) > > > > > > > > > > > > example: set DBI_TRACE=5=c:\foo.txt > > > > > > > > > > > > (set's the output to c:\foo.txt) > > > > > > > > > > > > Jeff > > > > > > > > > > > > > -----Original Message----- > > > > > > > From: Joe Tebelskis [mailto:[EMAIL PROTECTED]] > > > > > > > Sent: Friday, July 19, 2002 2:22 PM > > > > > > > To: Jeff Urlwin > > > > > > > Subject: RE: bugs in DBD::ODBC 0.43 > > > > > > > > > > > > > > > > > > > > > The column that gets clipped is "narrative" in the following > > > > > > > table, i.e., it's a varchar(4000). > > > > > > > > > > > > > > CREATE TABLE dbo.tfFragment ( > > > > > > > logid varchar(100) NOT NULL, > > > > > > > fTime1 datetime NOT NULL, > > > > > > > bTime2 datetime NULL, > > > > > > > thread varchar(10) NOT NULL, > > > > > > > stage varchar(10) NULL, > > > > > > > sTimem1 datetime NULL, > > > > > > > qUrlX varchar(500) NULL, > > > > > > > qTimem datetime NULL, > > > > > > > usrid varchar(100) NULL, > > > > > > > trigid varchar(10) NULL, > > > > > > > empty bit NULL, > > > > > > > narrative varchar(4000) NULL, > > > > > > > narrative1 varchar(3000) NULL, > > > > > > > gotHead bit NULL, > > > > > > > gotTail bit NULL > > > > > > > ) > > > > > > > > > > > > > > I doubt that I'll be able to reproduce the clipping > > problem using > > > > > > > a reduced script, as the cause seems pretty esoteric. > But I've > > > > > > > gone into the Perl debugger and confirmed that a > string about to > > > > > > > be inserted was 282 characters long, and then > confirmed that the > > > > > > > result of the INSERT was only 251 characters long. > The relevant > > > > > > > portions of my code look like this (abstracted): > > > > > > > > > > > > > > my $dbh = DBI->connect ($dsn, $user, $pwd, > > > > > > > { PrintError=>0, RaiseError=>1, > > > > > > AutoCommit=>1 }); > > > > > > > my ($sth1, $sth2, $sth3, ..., $sth14) = > > prepareStatements ($dbh); > > > > > > > sub prepareStatements { > > > > > > > my ($dbh) = @_; > > > > > > > my $sth1 = $dbh->prepare ( > > > > > > > "INSERT INTO tfFragment". > > > > > > > " (logid,fTime1,bTime2,thread, > > > > > > > > > stage,sTimem1,qUrlX,qTimem,usrid,trigid,empty,narrative,narrative1 > > > > > > > ,gotHead,gotTail) ". > > > > > > > " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)" > > > > > > > ); > > > > > > > my $sth2 = $dbh->prepare (....another SQL statement...); > > > > > > > my $sth3 = $dbh->prepare (....another SQL statement...); > > > > > > > return ($sth1, $sth2, $sth3, ..., $sth14); > > > > > > > } > > > > > > > main loop: { > > > > > > > gather data from files, building $state{$thread} = hash of > > > > > > > data to be databased.... > > > > > > > foreach my $thread (keys %state}) { > > > > > > > dbFragment (\%state, $thread, $logid, $fTime1S, > > $bTime2S); > > > > > > > } > > > > > > > } > > > > > > > sub dbFragment { > > > > > > > my ($stateR, $thread, $logid, $fTime1S, $bTime2S) = @_; > > > > > > > my > > > > > > > > > ($stage,$sTimem1S,$qUrlX,$qTimemS,$usrid,$trigid,$empty,$narrative > > > > > > > ,$narrative1,$gotHead,$gotTail) > > > > > > > = getState ($stateR, $thread); > > > > > > > $sth1->execute ($logid, $fTime1S, $bTime2S, $thread, > > > > > > > > > $stage,$sTimem1S,$qUrlX,$qTimemS,$usrid,$trigid,$empty,$narrative, > > > > > > > $narrative1,$gotHead,$gotTail) > > > > > > > or die ($dbh->errstr); > > > > > > > } > > > > > > > > > > > > > > What sort of trace would you be interested in seeing? > > > > > > > > > > > > > > -- Joe > > > > > > > > > > > > > > -----Original Message----- > > > > > > > From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] > > > > > > > Sent: Friday, July 19, 2002 7:59 AM > > > > > > > To: Joe Tebelskis; Jeff Urlwin > > > > > > > Subject: RE: bugs in DBD::ODBC 0.43 > > > > > > > > > > > > > > > > > > > > > Also, what type of column is the text column? > > > > > > > > > > > > > > Thanks, > > > > > > > > > > > > > > Jeff > > > > > > > > > > > > > > > -----Original Message----- > > > > > > > > From: Joe Tebelskis [mailto:[EMAIL PROTECTED]] > > > > > > > > Sent: Thursday, July 18, 2002 11:18 PM > > > > > > > > To: Jeff Urlwin; [EMAIL PROTECTED] > > > > > > > > Subject: bugs in DBD::ODBC 0.43 > > > > > > > > > > > > > > > > > > > > > > > > Thanks, Jeff. I've installed DBD::ODBC version 0.43, and it > > > > > > > > seems to have fixed the problem of memory access > > > > violation on exit. > > > > > > > > > > > > > > > > However: > > > > > > > > > > > > > > > > (1) I was hoping that the new version would also fix another > > > > > > > > possibly related bug that I noticed yesterday in > version 0.28, > > > > > > > > namely, that repeated executions of a prepared INSERT > > statement > > > > > > > > sometimes corrupt varchar columns, clipping them to > a certain > > > > > > > > observed length. For example, if I'm looping > through an array > > > > > > > > and inserting into a table strings of length 7, > 110, 251, 282, > > > > > > > > 281, 276, 130, 285, 140, sometimes the strings will > > actually get > > > > > > > > inserted with lengths 7, 110, 251, 251, 251, 251, > > 130, 285, 140. > > > > > > > > I can only avoid this corruption by re-preparing the INSERT > > > > > > > > statement just before executing it each time, which > > of course is > > > > > > > > inefficient. Unfortunately, version 0.43 has not fixed > > > > > this problem. > > > > > > > > > > > > > > > > (2) Version 0.28 accepted datetimes like "YYYYMMDD > > > HH:MM:SS", but > > > > > > > > version 0.43 only seems to accept "YYYY-MM-DD HH:MM:SS" (and > > > > > > > > milliseconds must now be preceded by a dot, not a > colon). Was > > > > > > > > this limitation intentional? > > > > > > > > > > > > > > > > I'm using ActivePerl 5.6.1 and DBI 1.201 under Windows > > > 2000, with > > > > > > > > SQL Server; my Perl script is single-threaded. > > > > > > > > > > > > > > > > -- Joe > > > > > > > > > > > > > > > > -----Original Message----- > > > > > > > > From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] > > > > > > > > Sent: Wednesday, July 17, 2002 9:13 PM > > > > > > > > To: Joe Tebelskis; [EMAIL PROTECTED] > > > > > > > > Subject: RE: DBI memory access violation on exit > > > > > > > > > > > > > > > > > > > > > > > > Actually, it's probably in DBD::ODBC. I hadn't seen it > > > > before using > > > > > > > > SQLServer, but it's certainly possible. You want to get > > > > > version 0.43 > > > > > > > > (releasing tonight, if I can get to the PAUSE > > server). I've got > > > > > > > > it patched > > > > > > > > and it definitely fixes Foxpro issues (at least the > > ones I know > > > > > > > > about :) and > > > > > > > > the symptoms sound the same as what you describe. If > > the memory > > > > > > > > allocation > > > > > > > > moves, the problem moves too. This fix should, at the very > > > > > > > least, make it > > > > > > > > better... > > > > > > > > > > > > > > > > Regards, > > > > > > > > > > > > > > > > Jeff > > > > > > > > > > > > > > > > > > > > > > > > > > I've written a big Perl script that reads from & > > > writes to a SQL > > > > > > > > > Server database. It seems to work just fine, > except that it > > > > > > > > > sometimes gives a mysterious memory access violation when > > > > > it exits: > > > > > > > > > "Perl.exe - Application Error: The instruction at > 0x411744a3 > > > > > > > > > referenced memory at 0x411744a3. The memory could > > > not be read". > > > > > > > > > > > > > > > > > > In trying to debug this, I've reduced my original > > script to a > > > > > > > > > tiny skeleton (abstracted below) that still evinces the > > > > > > same problem: > > > > > > > > > > > > > > > > > > use strict; > > > > > > > > > use Time::Local; > > > > > > > > > use Getopt::Std; > > > > > > > > > use DBI; > > > > > > > > > my $dsn = "DBI:ODBC:Name"; > > > > > > > > > my $dbh1 = DBI->connect ($dsn, "user", "password", > > > > > > > > > {PrintError=>0, RaiseError=>1, AutoCommit=>1}); > > > > > > > > > my $dbh2 = DBI->connect ($dsn, "user", "password", > > > > > > > > > {PrintError=>0, RaiseError=>1, AutoCommit=>1}); > > > > > > > > > my $sth1 = $dbh1->prepare ("SELECT * FROM tablename"); > > > > > > > > > $dbh1->disconnect(); > > > > > > > > > $dbh2->disconnect(); > > > > > > > > > exit 0; > > > > > > > > > > > > > > > > > > However, the access violation only occurs when this > > > skeleton is > > > > > > > > > followed by 40K of other code (leftover from the original > > > > > > > > > program) that's never executed. > > > > > > > > > This sensitivity to memory alignment suggests a subtle > > > > bug in DBI > > > > > > > > > that's corrupting a data structure that tracks > > resources to be > > > > > > > > > freed at DB disconnect time. > > > > > > > > > > > > > > > > > > Is this a known bug? Does it affect only the exit (as I > > > > > > > > > suspect), or might the body of my program be > > affected as well? > > > > > > > > > Is there a fix? > > > > > > > > > > > > > > > > > > I'm running this under ActivePerl 5.6.1 on Windows 2000. > > > > > > > > > DOS> perl -v > > > > > > > > > This is perl, v5.6.1 built for MSWin32-x86-multi-thread > > > > > > > > > > > > > > > > > > Thanks for any help. > > > > > > > > > > > > > > > > > > Joe Tebelskis | Sr. Software Engineer > > > > > > > > > > > > > > > > > > InfoSpace INC 601 108th Ave NE | Suite 1200 | > > > > > > Bellevue, WA 98004 > > > > > > > > > Tel 425.201.8765 | Fax 425.201.6159 | Mobile > > > 425.417.5801 > > > > > > > > > [EMAIL PROTECTED] | www.infospaceinc.com > > > > > > > > > > > > > > > > > > Discover what you can do.TM > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > >
