| Subject: | Serious Bug: Cannot perform any queries with non-text fields (including find by INT primary key) using dbd::Sybase |
| Date: | Wed, 08 Aug 2007 16:29:44 -0400 |
| To: | bug-DBIx-Class [...] rt.cpan.org |
| From: | Alexander Richter <rrichter [...] jcvi.org> |
To replicate:
------------- SQL
USE tempdb;
CREATE TABLE test (
id INT NOT NULL, PRIMARY KEY,
data VARCHAR(32) NULL,
);
INSERT INTO test (id,data) VALUES (1,'Hello World');
------------- DB/Schema.pm
package DB::Schema;
use base 'DBIx::Class::Schema';
__PACKAGE__->load_classes;
__PACKAGE__->connection('dbi:Sybase:database=tempdb','access','access');
1;
-------------- DB/Schema/Test
package DB::Schema::Test;
use base 'DBIx::Class';
__PACKAGE__->load_components("Core");
__PACKAGE__->table("test");
__PACKAGE__->add_columns(
"id" => {data_type => "int", default_value => undef, is_nullable => 0,
size => 10},
"data" => {data_type => "varchar",default_value => undef,is_nullable
=> 1,size => 32,},
);
__PACKAGE__->set_primary_key('id');
1;
-------------- test.pl
#!/usr/bin/perl
use DB::Schema;
my $rec = DB::Schema->resultset('Test')->find(1);
print $rec->data,"\n";
------------------------- Results
DBIx::Class::ResultSet::find(): DBI Exception: DBD::Sybase::st execute
failed: Server message number=257 severity=16 state=1 line=1
server=SYBASE text=Implicit conversion from datatype 'VARCHAR' to 'INT'
is not allowed. Use the CONVERT function to run this query.
------------------------- Expected
Hello, World
-------------------------- Discussion
The problem appears to be that _all_ query parameters are being quoted
using DBH::quote() in
DBIx::Class::Storage::DBI::NoBindVars::_prep_for_execute instead of only
text-based ones. If I alter DBIx::Class::Storage::DBI::Sybase to "use
base 'DBIx::Class::Storage::DBI'" instead of
"DBIx::Class::Storage::DBI::NoBindVars", it works fine.
This implies that you should be able to test the problem by simply using
DBIx::Class::Storage::DBI::NoBindVars with any other database, like
MySQL, if you don't have access to Sybase.
However, this isn't a perfect long term solution because Sybase is
semi-stupid with regard to bound parameters. Specifically, TEXT and
IMAGE (the BLOB-type datatypes) cannot be used as bound parameters in
insert or update statements, and cannot be used in "WHERE" clauses at
all. This pretty much bites you every time you try to add a new record
with a BLOB-type field, but ought to be rare events, since using a BLOB
is rare, and the data types suck in Sybase in many ways beyond that (and
frankly shouldn't be used in Sybase if you can possibly avoid it).
Therefore, a reasonable interim solution would be to revert
DBIx::Class::Storage::DBI::Sybase to be a DBIx::Class::Storage::DBI so
that the module's actually usable with Sybase, if not perfect. It also
really hurts to lose query caching completely (which Sybase does really
well), because of the BLOBs.
I don't know the code well enough to know if it's possible, but if there
is a way to pass the DBI SQL_TYPE along with the bind variables, then
DBIx::Class::Storage::DBI::NoBindVars could quote only on the text
types, and DBIx::Class::Storage::DBI::Sybase could explicitly
interpolate bound parameters only for types SQL_LONGVARBINARY and
SQL_LONGVARCHAR.
Thanks,
R. Alexander Richter
Informatics Engineer
J. Craig Venter Institute
9704 Medical Center Drive
Rockville, MD 20850
Phone: (301) 795-7582
Fax: (301) 795-7050