DBI 1.625, DBD::Sybase 1.14, ASE 15.7: no placeholder support for UNSIGNED INT datatype in SET clause

[Anonymous Monk]

Do placeholders work in SET clause of UPDATE query for column datatype of UNSIGNED INT in ASE 15.7+?

After much time wasting I found -- with DBI 1.625, DBD::Sybase 1.14, & ASE 15.7 -- that ...

• VARCHAR datatype is being used for column of UNSIGNED INT datatype in SET clause of UPDATE query when placeholder is used. The error message is similar to ...

  DBD::Sybase::st execute failed: Server message number=257 severity=16 
  +state=1 line=0 server=DB_SERVER procedure=DBD2 text=Implicit conversi
  +on from datatype 'VARCHAR' to 'UNSIGNED INT' is not allowed.  Use the
  + CONVERT function to run this query.
   Statement=    UPDATE db..table
      SET col =
            :p1
            -- %d
      WHERE search_col = :p2
  

• neither does explicitly setting the datatype help: $sth->bind_param( $place , $var , SQL_INTEGER ) as it produces same error as above;
• neither does CONVERT function works per above error message (besides the point that I question the value of using a placeholder at all in this case); the following error message is noted in DBD::Sybase ...

  DBD::Sybase::db prepare failed: Server message number=12828 severity=1
  +6 state=1 line=1 server=DB_SERVER procedure=DBD2 text=The datatype of
  + a parameter marker used in the dynamic prepare statement could not b
  +e resolved.
   Statement=    UPDATE db..table
      SET
        col
        =
        --?
        CONVERT( UNSIGNED INT , :p1 )
        -- %d
      WHERE search_col = :p2
  

• there is no problem with datatype of numeric(9,0), or after embedding the value right in SQL string sans placeholder;

By way of crude, untested but representative code ...

# SQL.
create table pqr
(
  x  numeric(9,0)  not null
, y  unsigned int  not null
)
insert into pqr values( 0 , 0 )


# Perl
$n = 1;
#  Works.
$dbh->do( q/UPDATE pqr SET x = ?/ , undef , $n );

$n = 2;
#  Fails;
$dbh->do( q/UPDATE pqr SET y = ?/ , undef , $n );

$n = 3;
#  works (same as %s, this is all string in the end).
$dbh->do( sprintf q/UPDATE pqr SET y = %d/ , $n );

[mpeppler]

I'm only 9 years late.... But this has just been fixed in DBD::Sybase 1.23 after someone else bumped into the same issue.

[mpeppler]

Congratulation - you have found a bug in DBD::Sybase.

Placeholders do work, but not with UNSIGNED INT columns.

I'll try to get that fixed in the next release of DBD::Sybase.

Michael

[Mr. Muskrat]

Is there a ticket for this yet? I didn't see anything obvious in RT.

[Anonymous Monk]

Thanks, Michael. (It may take some time before I personally would come across UNSIGNED INT datatype usage again.)

[Anonymous Monk]

Could somebody please change "limited placeholder support" to "no placeholder support" in the OP title? Alternatively, could please remove the "for UNSIGNED INT datatype" part?