Skip Menu |
 

This queue is for tickets about the DBIx-Class CPAN distribution.

Report information
The Basics
Id: 28741
Status: resolved
Priority: 0/
Queue: DBIx-Class

People
Owner: ribasushi [...] leporine.io
Requestors: rrichter [...] jcvi.org
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: 0.08113



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>
Download (untitled) / with headers
text/plain 3.1k
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
Download (untitled) / with headers
text/plain 1.2k
On Wed Aug 08 16:31:32 2007, rrichter@jcvi.org wrote: Show quoted text
> 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.
However, that would break FreeTDS, which we have a number of active users of to talk to MSSQL DBs. Can you figure out a way to detect which one is present? I don't have access to the sybase client libs so there's not a lot I can do. (alternatively if you could arrange for the DBIC team to have temporary access to a shell we could have a look ourselves) Show quoted text
> 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.
The quoting is definitely feasible. Will the BLOBs work with an explicit data type bind? They do for Pg, have a look in Storage::DBI::Pg for details.
Download (untitled) / with headers
text/plain 1.4k
On Thu Aug 09 08:52:59 2007, MSTROUT wrote: Show quoted text
> On Wed Aug 08 16:31:32 2007, rrichter@jcvi.org wrote:
> > 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.
> > However, that would break FreeTDS, which we have a number of active > users of to talk to MSSQL DBs. Can you figure out a way to detect which > one is present? I don't have access to the sybase client libs so there's > not a lot I can do. > > (alternatively if you could arrange for the DBIC team to have temporary > access to a shell we could have a look ourselves) >
> > I don't know the code well enough to know if it's possible, but if
there Show quoted text
> > 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.
> > The quoting is definitely feasible. > > Will the BLOBs work with an explicit data type bind? They do for Pg, > have a look in Storage::DBI::Pg for details.
I have solid access to a mssql server, and have the DBICTEST setup for both Sybase and ODBC. I can audit/test any patches that someone is brave to supply.
No reply from original submitter, changing status to stalled.
Download (untitled) / with headers
text/plain 3.5k
On Wed Aug 08 16:31:32 2007, rrichter@jcvi.org wrote: Show quoted text
> 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. >
Not sure if you are still interested in a solution to this problem, but there is a branch based on the latest release cycle (0.081xx) which addresses these problems. The search quoting should be already taken care of. LONGCHAR/BINARY should be coming soon. Please test and report issues: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/0.08/branches/sybase/
Download (untitled) / with headers
text/plain 136b
Please try the new sybase support that went into beta testing: http://lists.scsys.co.uk/pipermail/dbix-class/2009-September/008474.html
Comprehensive Sybase support merged to trunk


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.