FAQ
I'm calling some stored procedures using DBI 1.32, DBD::ODBC 1.07 and
Perl v5.8.0 (ActivePerl build 805) running on Win2kServer SP4 using
MS SQL 2000.

Am I supposed to initialize the variables I pass to
bind_param_inout()?

I'm using code like this to prepare and call the stored procedures
and fetch the OUTPUT parameters:

sub prepare_sps {
$sp{GetLocationInfo_p}
= $db->prepare('EXEC dbo.GetLocationInfo '. join(', ',('?') x 5));
# parameters 2-5 are OUTPUT!
{
my ($JobCity, $JobState, $JobCountry, $JobZIP)
= ( " "x 50, " "x 50, " ", " "x 5);
my $sp = $sp{GetLocationInfo_p};
$sp->bind_param_inout(2, \$JobCity, 50, DBI::SQL_VARCHAR);
$sp->bind_param_inout(3, \$JobState, 50, DBI::SQL_VARCHAR);
$sp->bind_param_inout(4, \$JobCountry, 2, DBI::SQL_CHAR);
$sp->bind_param_inout(5, \$JobZIP, 5, DBI::SQL_CHAR);

$sp{GetLocationInfo} = sub {
$sp->bind_param(1, $_[0]);
$sp->execute();
$_[1] = $JobCity;
$_[2] = $JobState;
$_[3] = $JobCountry;
$_[4] = $JobZIP;
s/^\s+//,s/\s+$// for ($_[1..4]);
}
};
...
}
...

$sp{GetLocationInfo}->($jobLocationId, $JobCity, $JobState,
$JobCountry, $JobZIP);

The stored proc looks like this:

CREATE PROCEDURE dbo.GetMonsterLocationInfo(
@LocationID Int,
@City varchar(50) OUTPUT,
@State varchar(50) OUTPUT,
@Country char(2) OUTPUT,
@Zip char(5) OUTPUT
) AS
BEGIN
SELECT @Zip = ZipCode, @State = State, @Country = Country, @City =
MappedCity
FROM dbo.LocationLookup WITH (NOLOCK)
WHERE ID = @LocationID
END

The problem is that if I do not initialize the $JobXxxx variables
with those spaces:

my ($JobCity, $JobState, $JobCountry, $JobZIP)
= ( " "x 50, " "x 50, " ", " "x 5);

I only get the first letters of the values. Once I do initialize the
variables I do get the right and complete values even in a loop, even
if the value in a later iteration is longer than the one in a
previous.
I do get the same incorrect results if the variables are undef or "".

Why is that? Is this something I missed in the docs? Or is it a bug
in DBD::ODBC? Or ...?

Thanks, Jenda

== Jenda@Krynicky.cz == http://Jenda.Krynicky.cz ==
I wonder why the whole chemistry industry ignores
computer bugs. We all would gladly pay big bucks
for a functional insecticide.

Search Discussions

  • Jeff Urlwin at Aug 13, 2004 at 6:45 pm
    Jenda,

    It could be a bug. Can you wrap up a quick test case and I'll trace it
    through?

    Regards,

    Jeff
    I'm calling some stored procedures using DBI 1.32, DBD::ODBC 1.07 and
    Perl v5.8.0 (ActivePerl build 805) running on Win2kServer SP4 using
    MS SQL 2000.

    Am I supposed to initialize the variables I pass to
    bind_param_inout()?

    I'm using code like this to prepare and call the stored procedures
    and fetch the OUTPUT parameters:

    sub prepare_sps {
    $sp{GetLocationInfo_p}
    = $db->prepare('EXEC dbo.GetLocationInfo '.
    join(', ',('?') x 5));
    # parameters 2-5 are OUTPUT!
    {
    my ($JobCity, $JobState, $JobCountry, $JobZIP)
    = ( " "x 50, " "x 50, " ", " "x 5);
    my $sp = $sp{GetLocationInfo_p};
    $sp->bind_param_inout(2, \$JobCity, 50,
    DBI::SQL_VARCHAR);
    $sp->bind_param_inout(3, \$JobState, 50,
    DBI::SQL_VARCHAR);
    $sp->bind_param_inout(4, \$JobCountry, 2,
    DBI::SQL_CHAR);
    $sp->bind_param_inout(5, \$JobZIP, 5, DBI::SQL_CHAR);

    $sp{GetLocationInfo} = sub {
    $sp->bind_param(1, $_[0]);
    $sp->execute();
    $_[1] = $JobCity;
    $_[2] = $JobState;
    $_[3] = $JobCountry;
    $_[4] = $JobZIP;
    s/^\s+//,s/\s+$// for ($_[1..4]);
    }
    };
    ...
    }
    ...

    $sp{GetLocationInfo}->($jobLocationId, $JobCity, $JobState,
    $JobCountry, $JobZIP);

    The stored proc looks like this:

    CREATE PROCEDURE dbo.GetMonsterLocationInfo(
    @LocationID Int,
    @City varchar(50) OUTPUT,
    @State varchar(50) OUTPUT,
    @Country char(2) OUTPUT,
    @Zip char(5) OUTPUT
    ) AS
    BEGIN
    SELECT @Zip = ZipCode, @State = State, @Country =
    Country, @City =
    MappedCity
    FROM dbo.LocationLookup WITH (NOLOCK)
    WHERE ID = @LocationID
    END

    The problem is that if I do not initialize the $JobXxxx variables
    with those spaces:

    my ($JobCity, $JobState, $JobCountry, $JobZIP)
    = ( " "x 50, " "x 50, " ", " "x 5);

    I only get the first letters of the values. Once I do initialize the
    variables I do get the right and complete values even in a loop, even
    if the value in a later iteration is longer than the one in a
    previous.
    I do get the same incorrect results if the variables are undef or "".

    Why is that? Is this something I missed in the docs? Or is it a bug
    in DBD::ODBC? Or ...?

    Thanks, Jenda

    == Jenda@Krynicky.cz == http://Jenda.Krynicky.cz ==
    I wonder why the whole chemistry industry ignores
    computer bugs. We all would gladly pay big bucks
    for a functional insecticide.
  • Jenda Krynicky at Aug 20, 2004 at 1:51 pm
    From: "Jeff Urlwin" <jurlwin@esoftmatic.com>
    It could be a bug. Can you wrap up a quick test case and I'll trace
    it through?

    Regards,

    Jeff
    Sorry for the delay.

    #!perl
    use DBI;

    my $PROC = <<'*END*';
    CREATE PROCEDURE dbo.GetL__ocationInfo(
    @LocationID Int,
    @City varchar(50) OUTPUT,
    @State varchar(50) OUTPUT,
    @Country char(2) OUTPUT,
    @Zip char(5) OUTPUT
    ) AS
    BEGIN
    SET @Zip = '12345';
    SET @State = 'Whatever';
    SET @Country = 'US'
    SET @City = 'Some City ' + Convert(varchar(10), @LocationID)
    END
    *END*

    $db = DBI->connect('dbi:ODBC:xxxx', 'xxx', 'xxx',
    {PrintError => 0,RaiseError => 1,LongReadLen => 65536,AutoCommit =>
    1});

    $db->do($PROC);

    eval{
    {
    my ($JobCity, $JobState, $JobCountry, $JobZIP);
    # this one returns just one char in each output variable in the
    first call
    # my ($JobCity, $JobState, $JobCountry, $JobZIP) = ('','','','');
    # this one returns just one char in each output variable in the
    first call
    # my ($JobCity, $JobState, $JobCountry, $JobZIP) = ( " "x 50, " "x
    50, " ", " "x 5);
    # my ($JobCity, $JobState, $JobCountry, $JobZIP) = ("\0" x 50, "\0"
    x 50, "\0" x 2, "\0" x 5, );
    # the two above work fine
    # my ($JobCity, $JobState, $JobCountry, $JobZIP) = ( " "x 3, " "x 3,
    " ", " "x 3);
    # my ($JobCity, $JobState, $JobCountry, $JobZIP) = ( " ", " ", " ",
    " ");
    # it's strange but these two work fine

    my $sp = $db->prepare('EXEC dbo.GetL__ocationInfo '. join(',
    ',('?') x 5));
    $sp->bind_param_inout(2, \$JobCity, 50, DBI::SQL_VARCHAR);
    $sp->bind_param_inout(3, \$JobState, 50, DBI::SQL_VARCHAR);
    $sp->bind_param_inout(4, \$JobCountry, 2, DBI::SQL_VARCHAR);
    $sp->bind_param_inout(5, \$JobZIP, 5, DBI::SQL_VARCHAR);

    sub GetLocation {
    $sp->bind_param(1, $_[0]);
    $sp->execute();
    $_[1] = $JobCity;
    $_[2] = $JobState;
    $_[3] = $JobCountry;
    $_[4] = $JobZIP;
    }
    }

    my ($JobCity, $JobState, $JobCountry, $JobZIP);

    GetLocation( 10, $JobCity, $JobState, $JobCountry, $JobZIP);
    print "'" . join( "', '", $JobCity, $JobState, $JobCountry,
    $JobZIP) . "'\n";

    GetLocation( 15456, $JobCity, $JobState, $JobCountry, $JobZIP);
    print "'" . join( "', '", $JobCity, $JobState, $JobCountry,
    $JobZIP) . "'\n";

    GetLocation( 15457716, $JobCity, $JobState, $JobCountry, $JobZIP);
    print "'" . join( "', '", $JobCity, $JobState, $JobCountry,
    $JobZIP) . "'\n";
    };

    $db->do('DROP PROCEDURE dbo.GetL__ocationInfo');
    __END__


    If I do not initialize the variables or initialize them to '' then
    the first call returns just the first characters of the return
    values, in all other cases it works correctly.
    The second and following calls work fine, even if the values are
    longer than the ones returned before.

    # $Id: DBI.pm,v 11.43 2004/02/01 11:16:16 timbo Exp $
    $DBI::VERSION = "1.43"; # ==> ALSO update the version in the pod text
    below!


    # $Id: ODBC.pm 124 2004-02-22 15:57:00Z jurl $
    $DBD::ODBC::VERSION = '1.07';

    This is perl, v5.8.0 built for MSWin32-x86-multi-thread
    ...
    Binary build 805 provided by ActiveState Corp.
    http://www.ActiveState.com
    Built 18:08:02 Feb 4 2003

    Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
    14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
    Edition on Windows NT 5.0 (Build 2195: Service Pack 4)


    Thanks, Jenda
    ===== Jenda@Krynicky.cz === http://Jenda.Krynicky.cz =====
    When it comes to wine, women and song, wizards are allowed
    to get drunk and croon as much as they like.
    -- Terry Pratchett in Sourcery
  • Jenda Krynicky at Sep 7, 2004 at 9:41 pm
    From: "Jenda Krynicky" <Jenda@Krynicky.cz>
    From: "Jeff Urlwin" <jurlwin@esoftmatic.com>
    It could be a bug. Can you wrap up a quick test case and I'll trace
    it through?

    Regards,

    Jeff
    Sorry for the delay.

    #!perl
    use DBI;

    my $PROC = <<'*END*';
    ...
    *END*

    $db = DBI->connect('dbi:ODBC:xxxx', 'xxx', 'xxx',
    {PrintError => 0,RaiseError => 1,LongReadLen => 65536,AutoCommit =>
    1});

    $db->do($PROC);

    eval{
    {
    my ($JobCity, $JobState, $JobCountry, $JobZIP);
    # this one returns just one char in each output variable in the
    first call # my ($JobCity, $JobState, $JobCountry, $JobZIP) =
    ('','','','');
    # this one returns just one char in each output variable in the
    first call # my ($JobCity, $JobState, $JobCountry, $JobZIP) = ( " "x
    50, " "x 50, " ", " "x 5); # my ($JobCity, $JobState, $JobCountry,
    $JobZIP) = ("\0" x 50, "\0" x 50, "\0" x 2, "\0" x 5, );
    # the two above work fine
    # my ($JobCity, $JobState, $JobCountry, $JobZIP) = ( " "x 3, " "x 3,
    " ", " "x 3);
    # my ($JobCity, $JobState, $JobCountry, $JobZIP) = ( " ", " ", " ",
    " ");
    # it's strange but these two work fine
    ...

    If I do not initialize the variables or initialize them to '' then the
    first call returns just the first characters of the return values, in
    all other cases it works correctly. The second and following calls
    work fine, even if the values are longer than the ones returned
    before.
    Any news?

    I just found out that it breaks even if the stored procedure returns
    some nulls. It returns nulls once and the following calls all return
    just the first character.

    Thanks, Jenda
    ===== Jenda@Krynicky.cz === http://Jenda.Krynicky.cz =====
    When it comes to wine, women and song, wizards are allowed
    to get drunk and croon as much as they like.
    -- Terry Pratchett in Sourcery
  • Jeff Urlwin at Sep 8, 2004 at 5:09 am
    Fixed in 1.10 released about 10 minutes ago ;)

    Sorry for the wait.

    Jeff
    -----Original Message-----
    From: Jenda Krynicky
    Sent: Friday, August 20, 2004 9:51 AM
    To: Jeff Urlwin
    Cc: dbi-users@perl.org
    Subject: RE: :ODBC, MSSQL, output params


    From: "Jeff Urlwin" <jurlwin@esoftmatic.com>
    It could be a bug. Can you wrap up a quick test case and
    I'll trace
    it through?

    Regards,

    Jeff
    Sorry for the delay.

    #!perl
    use DBI;

    my $PROC = <<'*END*';
    CREATE PROCEDURE dbo.GetL__ocationInfo(
    @LocationID Int,
    @City varchar(50) OUTPUT,
    @State varchar(50) OUTPUT,
    @Country char(2) OUTPUT,
    @Zip char(5) OUTPUT
    ) AS
    BEGIN
    SET @Zip = '12345';
    SET @State = 'Whatever';
    SET @Country = 'US'
    SET @City = 'Some City ' + Convert(varchar(10), @LocationID) END
    *END*

    $db = DBI->connect('dbi:ODBC:xxxx', 'xxx', 'xxx',
    {PrintError => 0,RaiseError => 1,LongReadLen =>
    65536,AutoCommit =>
    1});

    $db->do($PROC);

    eval{
    {
    my ($JobCity, $JobState, $JobCountry, $JobZIP);
    # this one returns just one char in each output
    variable in the
    first call
    # my ($JobCity, $JobState, $JobCountry, $JobZIP)
    = ('','','','');
    # this one returns just one char in each output
    variable in the
    first call
    # my ($JobCity, $JobState, $JobCountry, $JobZIP)
    = ( " "x 50, " "x
    50, " ", " "x 5);
    # my ($JobCity, $JobState, $JobCountry, $JobZIP)
    = ("\0" x 50, "\0"
    x 50, "\0" x 2, "\0" x 5, );
    # the two above work fine
    # my ($JobCity, $JobState, $JobCountry, $JobZIP)
    = ( " "x 3, " "x 3,
    " ", " "x 3);
    # my ($JobCity, $JobState, $JobCountry, $JobZIP)
    = ( " ", " ", " ",
    " ");
    # it's strange but these two work fine

    my $sp = $db->prepare('EXEC
    dbo.GetL__ocationInfo '. join(',
    ',('?') x 5));
    $sp->bind_param_inout(2, \$JobCity, 50,
    DBI::SQL_VARCHAR);
    $sp->bind_param_inout(3, \$JobState, 50,
    DBI::SQL_VARCHAR);
    $sp->bind_param_inout(4, \$JobCountry, 2,
    DBI::SQL_VARCHAR);
    $sp->bind_param_inout(5, \$JobZIP, 5, DBI::SQL_VARCHAR);

    sub GetLocation {
    $sp->bind_param(1, $_[0]);
    $sp->execute();
    $_[1] = $JobCity;
    $_[2] = $JobState;
    $_[3] = $JobCountry;
    $_[4] = $JobZIP;
    }
    }

    my ($JobCity, $JobState, $JobCountry, $JobZIP);

    GetLocation( 10, $JobCity, $JobState, $JobCountry, $JobZIP);
    print "'" . join( "', '", $JobCity, $JobState, $JobCountry,
    $JobZIP) . "'\n";

    GetLocation( 15456, $JobCity, $JobState, $JobCountry, $JobZIP);
    print "'" . join( "', '", $JobCity, $JobState, $JobCountry,
    $JobZIP) . "'\n";

    GetLocation( 15457716, $JobCity, $JobState,
    $JobCountry, $JobZIP);
    print "'" . join( "', '", $JobCity, $JobState, $JobCountry,
    $JobZIP) . "'\n";
    };

    $db->do('DROP PROCEDURE dbo.GetL__ocationInfo');
    __END__


    If I do not initialize the variables or initialize them to '' then
    the first call returns just the first characters of the return
    values, in all other cases it works correctly.
    The second and following calls work fine, even if the values are
    longer than the ones returned before.

    # $Id: DBI.pm,v 11.43 2004/02/01 11:16:16 timbo Exp $
    $DBI::VERSION = "1.43"; # ==> ALSO update the version in the pod text
    below!


    # $Id: ODBC.pm 124 2004-02-22 15:57:00Z jurl $
    $DBD::ODBC::VERSION = '1.07';

    This is perl, v5.8.0 built for MSWin32-x86-multi-thread
    ...
    Binary build 805 provided by ActiveState Corp.
    http://www.ActiveState.com
    Built 18:08:02 Feb 4 2003

    Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002
    14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise
    Edition on Windows NT 5.0 (Build 2195: Service Pack 4)


    Thanks, Jenda
    ===== Jenda@Krynicky.cz === http://Jenda.Krynicky.cz =====
    When it comes to wine, women and song, wizards are allowed
    to get drunk and croon as much as they like.
    -- Terry Pratchett in Sourcery

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedAug 13, '04 at 6:56a
activeSep 8, '04 at 5:09a
posts5
users2
websitedbi.perl.org

2 users in discussion

Jenda Krynicky: 3 posts Jeff Urlwin: 2 posts

People

Translate

site design / logo © 2022 Grokbase