Skip to Content.
Sympa Menu

freetds - [freetds] FreeTDS ODBC and Sybase ASE: binding NULL value to TEXT and IMAGE column

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Charles Hung <chung AT caci.com>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: [freetds] FreeTDS ODBC and Sybase ASE: binding NULL value to TEXT and IMAGE column
  • Date: Wed, 21 Dec 2005 16:29:35 -0500


Hello,

I am testing a Perl script using DBI 1.48 and DBD-ODBC 1.13 on HP-UX 11i
(11.11) PA-RISC using FreeTDS 0.63 and unixODBC 2.2.11. The target database
is Sybase ASE 12.5.3.

I am trying to insert and update a table that has VARCHAR, NUMERIC, TEXT, and
IMAGE columns. Here is a code sample:

$sth = $dbh->prepare('insert into chung3(c1,c2,type) values(?,?,?)');
$sth->bind_param(1,'test_null',SQL_VARCHAR);
$sth->bind_param(2,undef,SQL_LONGVARCHAR);
$sth->bind_param(3,'TEXT',SQL_VARCHAR);
$sth->execute();

When binding "undef" value (Perl's way of indicating NULL value) using
bind_param function, I notice:

1. if the column is TEXT datatype, no error returned during bind and execute.
However, the data stored in the database is actually a space character, not a
NULL value.

2. if the column is IMAGE datatype and the "undef" value is bound with
SQL_LONGVARBINARY, the following error is returned:

DBD::ODBC::st execute failed: [unixODBC][FreeTDS][SQL Server]Incorrect syntax
near ','.
(SQL-42000)
[unixODBC][FreeTDS][SQL Server]Must attach a value to the binary prefix '0x';
zero-length binary value is not allowed.
(SQL-)(DBD: st_execute/SQLExecute err=-1) at test_odbc.pl line 188.

Binding the column as SQL_LONGVARCHAR gets around the error, but the data
stored is hex 20, which represents a space character.



Binding "undef" value to a VARCHAR or NUMERIC column works fine. The data are
stored as NULL in the database.

Has anyone encountered this issue? Any solution to this?

Thanks

Charles Hung




  • [freetds] FreeTDS ODBC and Sybase ASE: binding NULL value to TEXT and IMAGE column, Charles Hung, 12/21/2005

Archive powered by MHonArc 2.6.24.

Top of Page