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