Ok, this fails for me too. In this case what happens in ODBC terms is:
SQLPrepare(exec proc1)=SQL_SUCCESS
SQLExecute()=SQL_SUCCESS;
SQLNumResultCols()=SQL_SUCCESS, resultant columns = 0
Here, when you call $stmt->fetchrow_array(), DBD::ODBC says there was no select
because SQLNumResultCols returns a column count of 0.
Having spoken with someone who knows TDS fairly well the problem is guessable.
Apparently, when calling a procedure TDS returns DONE messages for each
execution in the procedure. The MS SQL Server driver is obviously using these
as indicating possible multiple result sets.
If you do this in ODBC (for your procedure) it works:
SQLPrepare(exec proc1)=SQL_SUCCESS
SQLExecute()=SQL_SUCCESS;
SQLNumResultCols()=SQL_SUCCESS, resultant columns = 0
SQLMoreResults()=SQL_SUCCESS;
SQLNumResultCols()=SQL_SUCCESS, resultant columns = 1
This appears to be daftness in the ODBC driver as the procedure runs to
completion whatever you do with SQLMoreResults.
i.e.
say procedure is:
insert into table values (100);
insert into table values (100);
select 1
SQLPrepare(exec proc1)=SQL_SUCCESS
SQLExecute()=SQL_SUCCESS;
SQLNumResultCols()=SQL_SUCCESS, resultant columns = 0
SQLRowCount()=SQL_SUCCESS, 1 row affected
SQLCancel()=SQL_SUCCESS (NOTE SQLServer won't let you call SQLCloseCursor here)
The SQLCancel did not stop the two inserts happening because the whole
procedure was run to completion. All the driver is doing is passing the results
of the procedure back to you in batches. I think the key to this is the
SQLGetInfo(SQL_BATCH_ROW_COUNT) = which returns SQL_BRC_EXPLICIT for SQLServer
and the SQL_BRC_ROLLED_UP bit is NOT set.
A cynic may even say it looks as if MS have invented SQL_BATCH_ROW_COUNT because
their driver does it that way.
Anyway, the solution appears to be to call SQLNumResultCols(), if it returns 0
columns, call SQLMoreResults(). Repeat until SQLMoreResults returns an error or
SQLNumResultCols returns some columns.
I am not sure where this would need to go into DBD::ODBC/dbdimp.c but I did the
following just to check I wasn't talking rubbish:
Inserted:
if (num_fields == 0) {
while(1)
{
rc = SQLMoreResults(imp_sth->hstmt);
if (!SQL_ok(rc)) break;
rc = SQLNumResultCols(imp_sth->hstmt, &num_fields);
if (!SQL_ok(rc)) {
dbd_error(h, rc, "dbd_describe/SQLNumResultCols");
return 0;
}
if (num_fields != 0) break;
}
}
after
rc = SQLNumResultCols(imp_sth->hstmt, &num_fields);
if (!SQL_ok(rc)) {
dbd_error(h, rc, "dbd_describe/SQLNumResultCols");
return 0;
}
in dbdimp.c/dbd_describe
This returned the correct results for Joe's test case and still passed the
DBD::ODBC test but I would not describe this as thorough testing.
Martin
On 24-Jul-2002 Joe Tebelskis wrote:
> 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
--
Martin J. Evans
Easysoft Ltd, UK
Development
