Yes, that works for me too. But if the stored procedure executes an INSERT statement
before returning a value via SELECT, then the fetchrow_array fails. Try the following
script with/without the INSERT statement:
#!perl -w
use strict;
use DBI;
# Connect to the database, and create a table and 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 PROCEDURE proc1");};
my $proc1 =
"CREATE PROCEDURE proc1 AS ".
"BEGIN".
" INSERT INTO table1 VALUES (100);". # breaks fetchrow_array
" SELECT 1;".
"END";
eval {$dbh->do ($proc1);};
# Execute it:
my $sth = $dbh->prepare ("exec proc1");
$sth->execute ();
while (my $result = $sth->fetchrow_array()) {
print "result = $result\n";
}
$dbh->disconnect;
-- Joe
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, July 24, 2002 12:57 PM
To: Joe Tebelskis
Cc: [EMAIL PROTECTED]; Jeff Urlwin
Subject: RE: :ODBC 0.45_4
Having installed DBD::ODBC 0.45_4 and creating the procedure:
Create procedure joe as DECLARE @result int;SET @result = 1; select @result;
and executing:
#!/usr/bin/perl -w
use DBI;
my $dbh = DBI->connect('dbi:ODBC:mydsn', 'Martin_Evans', 'easysoft') ||
die "failed to connect";
print $DBD::ODBC::VERSION, "\n";
my $stmt = $dbh->prepare("exec joe");
$stmt->execute();
while(@row = $stmt->fetchrow_array())
{
print join(",", @row), "\n";
}
$dbh->disconnect();
I get:
0.45_4
1
so it appears to be working for me. Does this work for you? Admittedly, it is
to MS SQL Server over our ODBC_ODBC bridge from Linux and not directly on
Windows but it should not matter.
Martin
On 24-Jul-2002 Joe Tebelskis wrote:
> 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
>> > > > > > > > >
>> > > > > > > > >
>> > > > > > > >
>> > > > > > > >
>> > > > > > > >
>> > > > > > >
>> > > > > > >
>> > > > > > >
>> > > > > >
>> > > > > >
>> > > > >
>> > > > >
>> > > > >
>> > > >
>> > >
>> > >
>> > >
>> >
>> >
>> >
>>
>>
>>
--
Martin J. Evans
Easysoft Ltd, UK
Development
