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