Perl DBI fetchrow_array issue

Question

I'm having the following issue. I have a stored procedure that I use to either update or insert data into the DB called say UpdateData. It looks roughly something like this (though simplified):

CREATE PROCEDURE [dbo].[UpdateData] 
   @dataId as int,
   @data as int,
AS
BEGIN 
SET NOCOUNT ON;
declare @count int
select @count = (select COUNT(*) from DataTable data where data.id = @dataId)
if @count = 1
begin
update DataTable set data = @data from DataTable where  data.id = @dataId
select 'Updated' [operation] ,  @@ROWCOUNT [count]
end
else
begin
insert into DataTable (id, data)  values(@dataId, @data)
select 'Inserted' [operation] , @@ROWCOUNT [count]
end
END

I call this stored procedure from perl using DBI through a prepared statement iterating over my data. I then use a call to fetchrow_array to get the information about which operation was performed:

my $dbh = getDBHandle($debug);
foreach (@Data) {
     $updateData->execute($->[0], $_->[1]);
     my @row = $updateData->fetchrow_array;
     my ($action, $count) = ($row[0], $row[1]);
     print $row[0] .",$action, $count\n";
 }

What happens is that once any update statement is run then subsequently all action inserted descriptions are truncated form 'inserted' to 'inserte '. I think this is happening because the string 'updated' has one less character then 'inserted' and once fetchrow_array is called with that string in the column it resets some kind of limit. If I make the difference between the two description strings more then one charter like say modify the store procedure to use 'Update' instead of 'Updated' (diffrence of two characters to 'Inserted')

select 'Update' [operation] ,  @@ROWCOUNT [count]

I get the error:

   DBD::ODBC::st fetchrow_array failed: [Microsoft][ODBC SQL Server Driver]String data, right truncation (SQL-01004)

So in summary, the output looks like

1,Inserted,10

2,Updated,15

3,Inserte ,20

4,Updated,5

Any ideas on why the executions are not independent and what is the best way to solve this problem. I know I can make the actions the same but I would like a better solution.

EDIT: A follow up question. If the UpdateData procedure needed to call another procedure which also returned data. Is it possible in Perl to get both of the result sets. One coming from the inner procedure and one from the outer. Right now, >fetchrow_array only gets the inner result set.

EDIT 2: In regards to the original issue of data truncation I am wondering why does calling $updateData->finish after every execute not cause the width to be reset on every execute. IE

  foreach (@Data) {
       $updateData->execute($->[0], $_->[1]);
       my @row = $updateData->fetchrow_array;
       my ($action, $count) = ($row[0], $row[1]);
       print $row[0] .",$action, $count\n";
       $updateData->finish;
   }

Answer 1

It is difficult to attempt to reproduce your problem when we don't know the data in the table. As always with issues like this it is best to try and write a small self contained script which demonstrates the problem. When I attempt to guess at your scenario and run:

use DBI;
use strict;
use warnings;

my $h = DBI->connect('dbi:ODBC:xx','xx','xx',
                     {RaiseError => 1, PrintError => 0});

eval {
    $h->do(q/drop table mje/);
    $h->do(q/drop procedure pmje/);
};

$h->do(<<'EOT');
create table mje (id int, data int)
EOT

#my @data = (1,1,2);
#my $s = $h->prepare(q/insert into mje (id, data) values(?,?)/);
#foreach (@data) {
#    $s->execute($_, $_);
#}

$h->do(<<'EOT');
CREATE PROCEDURE pmje (
    @dataId as int,
    @data as int)
AS
BEGIN
SET NOCOUNT ON;
declare @count int
select @count = (select COUNT(*) from mje where id = @dataId)
if @count = 1
begin
update mje set data = @data from mje where id = @dataId
select 'Updated' [operation] ,  @@ROWCOUNT [count]
end
else
begin
insert into mje (id, data)  values(@dataId, @data)
select 'Inserted' [operation] , @@ROWCOUNT [count]
end
END
EOT

my $s = $h->prepare(q/{call pmje(?,?)}/);
my @data = (1,2,1);
foreach (@data) {
    $s->execute($_, $_);
    my @row = $s->fetchrow_array;
    my ($action, $count) = ($row[0], $row[1]);
    print $row[0] .",$action, $count\n";
 }

I get:

Inserted,Inserted, 1
Updated,Updated, 1
Inserted,Inserted, 1

However, I probably was not using the same ODBC driver as you (I was using the Easysoft SQL Server driver on Linux). The truncation error you see suggests DBD::ODBC bound the column with too small a buffer. DBD::ODBC uses SQLDescribeCol to obtain the size of the buffer required so in your case I'd suggest SQLDescribeCol returned 7 instead of 8. That should be fairly stright forward to prove if you enable DBD::ODBC tracing however, how you do this depends on how recent your DBI and DBD::ODBC are. If you have recent DBI and DBD::ODBC simply:

set DBI_TRACE=DBD=x.log (windows) or export DBI_TRACE=DBD=x.log (unix)

If that does not produce much in x.log

set DBI_TRACE=15=x.log (windows) export DBI_TRACE=15=x.log (unix)

and run your script. When I do that I get a line like this:

   DescribeCol column = 1, name = operation, namelen = 9, type = VARCHAR(12), precision/column size = 8, scale = 0, nullable = 0

which tells me the buffer size returned for the column is 8. You probably got 7 before and 128 now. I don't think anything is broken as such as how would the ODBC driver or database know who big the buffer should be?

Answer 2

While I still don't know why the behavior is what it is, I did find the solution. IF in the stored procedure one declared a variable into which the output will be placed and the size is large enough, then everything works as it should. Namely, this fixes things:

CREATE PROCEDURE [dbo].[UpdateData] 
    @dataId as int,
    @data as int,
AS
BEGIN 
SET NOCOUNT ON;
declare @operation varchar(128)
declare @count int
select @count = (select COUNT(*) from DataTable data where data.id = @dataId)
if @count = 1
begin
update DataTable set data = @data from DataTable where  data.id = @dataId
set @operation = 'Updated'
select @operation [operation] ,  @@ROWCOUNT [count]
end
else
begin
insert into DataTable (id, data)  values(@dataId, @data)
set @operation = 'Inserted'
select @operation [operation] ,  @@ROWCOUNT [count]
end
END

EDIT: This is a reply to bohicas' post. The following script (which is a slight modification of what he posted reproduces the problem on my machine).

use DBI;
use strict;
use warnings;

my $debug = 0;
my $h = DBI->connect('dbi:ODBC:xx','xx','xx',
                 {RaiseError => 1, PrintError => 0});

eval {
    $h->do(q/drop table mje/);
    $h->do(q/drop procedure pmje/);
};

$h->do(<<'EOT');
create table mje (id int, data int)
EOT

$h->do(<<'EOT');
CREATE PROCEDURE pmje (
    @dataId as int,
    @data as int)
AS
BEGIN
SET NOCOUNT ON;
declare @count int
select @count = (select COUNT(*) from mje where id = @dataId)
if @count = 1
begin
update mje set data = @data from mje where id = @dataId
select 'Updated' [operation] ,  @@ROWCOUNT [count]
end
else
begin
insert into mje (id, data)  values(@dataId, @data)
select 'Inserted' [operation] , @@ROWCOUNT [count]
end
END
EOT

my $s = $h->prepare(q/{call pmje(?,?)}/);
my @data = (1);
foreach (@data) {
    $s->execute($_, $_);
    my @row = $s->fetchrow_array;
    my ($action, $count) = ($row[0], $row[1]);
    print $row[0] .",$action, $count\n";
    $s->finish();
 }
 $h->disconnect();

 $h = getDBHandle($debug);
 $s = $h->prepare(q/{call pmje(?,?)}/);

@data = (1,2);
foreach (@data) {
    $s->execute($_, $_);
    my @row = $s->fetchrow_array;
    my ($action, $count) = ($row[0], $row[1]);
    print $row[0] .",$action, $count\n";
    $s->finish();
 }