thor has asked for the wisdom of the Perl Monks concerning the following question:

Sorry about the long title.

Basically, I'm trying to use DBI to call a stored procedure with one of the parameters containing an ASCII NUL character. What I've tried so far: 

my $packed = pack("i", 15);
$packed .= '$';
# #1
$sth = $dbh->prepare("exec my_proc ?");
$sth->execute($packed);

# #2
$sth = $dbh->prepare("exec my_proc $packed");
$sth->execute();

[download]
What I think the problem is is that the pack('i',15)<code> makes <code>"^@^@^@^O", where ^@ is ASCII NUL. So, when this gets passed, perl somehow sees this as the end of string (just like in C), and stops processing the string. Is there some way to pass this in without having perl interpret the NUL's?

thanks,
thor

Replies are listed 'Best First'.
Re: Calling a Sybase Procedure with NUL in a parm
by mpeppler (Vicar) on Feb 05, 2003 at 00:30 UTC
    What is the datatype of the parameter to the proc?

    As an explanation - when you use the first form DBD::Sybase assumes that the parameter is of type VARCHAR, which may not handle the NUL bytes very well (although I think it should, really).

    If the parameter is supposed to be a binary parameter (VARBINARY, for example), then you should do something like this: 

    $sth = $dbh->prepare("exec my_proc ?");
$sth->bind_param(1, $packed, SQL_BINARY);
$sth->execute;

    [download]
    or convert the packed value to a hex string and pass it as a hex string litteral (method #2).

    Michael

[reply]
[d/l]
      to answer your question, the parameter is indeed a VARCHAR field. Hadn't thought about converting to hex...though something tells me that the application on the other side won't like it. Worth a shot though...++ for you!

      thor

[reply]
        May I ask why you need to store a packed value in a varchar column? Wouldn't binary/varbinary be more appropriate?

        Now having said that this might still be a bug with DBD::Sybase - I'll look into it.

        Michael

[reply]
Re: Calling a Sybase Procedure with NUL in a parm
by Abigail-II (Bishop) on Feb 04, 2003 at 23:29 UTC
    Perl won't do anything with the NUL's, but something written in C will. Like DBD::Sybase, and whatever Sybase libraries (dblib or ctlib) you are using.

    Are you sure your Sybase client libraries can handle this?

    Abigail

[reply]
      CTlib can handle embedded nul bytes. However, DBD::Sybase does indeed assume NULL terminated strings for CHAR/VARCHAR parameters (which could be construed as a bug, I suppose).

      Michael

[reply]
Re: Calling a Sybase Procedure with NUL in a parm
by derby (Abbot) on Feb 04, 2003 at 23:55 UTC
    Hmm ... wel you may still have issues with the underlying c code but pack("i",15) will no give you a null character but a 4 byte value containing the SI (shift in character - who uses that?). To create a one character null, you would need pack( c, 0 );

    -derby

[reply]
Re: Calling a Sybase Procedure with NUL in a parm
by data64 (Chaplain) on Feb 05, 2003 at 00:03 UTC

    According to the DBI documentation you can use undef to indicate null values. Search for "Null Values" on the linked page

    BTW, ++ for the descriptive title.

    Just a tongue-tied, twisted, earth-bound misfit. -- Pink Floyd

[reply]
      What you're talking about is NULL, not NUL. NUL is ASCII 0, whereas NULL is an SQL concept. Thanks though.

      thor

[reply]

Back to Seekers of Perl Wisdom