Skip Menu |
 

Preferred bug tracker

Please visit the preferred bug tracker to report your issue.

This queue is for tickets about the DBD-Oracle CPAN distribution.

Report information
The Basics
Id: 64206
Status: open
Priority: 0/
Queue: DBD-Oracle

People
Owner: Nobody in particular
Requestors: ARODLAND [...] cpan.org
Cc: ribasushi [...] leporine.io
AdminCc:

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



Subject: Quoted table name breaks when rebinding LOBs
Download (untitled) / with headers
text/plain 925b
Running this code: my $sth = $dbh->prepare('INSERT INTO "bindtype_test" ("id", "blob") VALUES (?, ?)'); $sth->bind_param(1, 3); $sth->bind_param(2, 'abc', { ora_type => DBD::Oracle::ORA_BLOB(), ora_field => 'blob'} ); $sth->execute; gives this error: DBD::Oracle::st execute failed: ORA-01740: missing double quote in identifier ORA-01740: missing double quote in identifier (DBD SUCCESS: OCIDescribeAny(view)/LOB refetch) [for Statement "INSERT INTO "bindtype_test" ("id", "blob") VALUES (?, ?)" with ParamValues: :p1=3, :p2='abc'] at ... My interpretation on looking at the code is that init_lob_refetch calls find_ident_after to find the next thing after "INSERT INTO" to do introspection, and find_ident_after isn't handling quoted identifiers properly. Adding an additional debug print in init_lob_refetch to print tablename shows it as 'bindtype_test"' (with trailing doublequote).
Download (untitled) / with headers
text/plain 2.5k
On Fri Dec 24 21:29:32 2010, ARODLAND wrote: Show quoted text
> Running this code: > > my $sth = $dbh->prepare('INSERT INTO "bindtype_test" ("id", "blob") > VALUES (?, ?)'); > $sth->bind_param(1, 3); > $sth->bind_param(2, 'abc', { ora_type => DBD::Oracle::ORA_BLOB(), > ora_field => 'blob'} ); > $sth->execute; > > gives this error: > > DBD::Oracle::st execute failed: ORA-01740: missing double quote in > identifier > ORA-01740: missing double quote in identifier (DBD SUCCESS: > OCIDescribeAny(view)/LOB refetch) [for Statement "INSERT INTO > "bindtype_test" ("id", "blob") VALUES (?, ?)" with ParamValues: :p1=3, > :p2='abc'] at ... > > My interpretation on looking at the code is that init_lob_refetch calls > find_ident_after to find the next thing after "INSERT INTO" to do > introspection, and find_ident_after isn't handling quoted identifiers > properly. Adding an additional debug print in init_lob_refetch to print > tablename shows it as 'bindtype_test"' (with trailing doublequote).
I didn't write the code at issue here and there are no comments in the code to give us a clue if the code is supposed to maintain the quotes around the table name or not. The following code is causing this: char * find_ident_after(char *src, char *after, STRLEN *len, int copy) { int seen_key = 0; char *orig = src; char *p; while(*src){ if (*src == '\'') { char delim = *src; while(*src && *src != delim) ++src; } else if (*src == '-' && src[1] == '-') { while(*src && *src != '\n') ++src; } else if (*src == '/' && src[1] == '*') { while(*src && !(*src == '*' && src[1]=='/')) ++src; } else if (isALPHA(*src)) { if (seen_key) { /* MJE the comparison with '"' below includes your * trailing double quote */ char *start = src; while(*src && (isALNUM(*src) || *src=='.' || *src=='$' || *src=='"')) ++src; *len = src - start; if (copy) { p = (char*)alloc_via_sv(*len, 0, 1); strncpy(p, start, *len); p[*len] = '\0'; return p; } return start; } else if ( toLOWER(*src)==toLOWER(*after) && (src==orig ? 1 : !isALPHA(src[-1]))) { p = after; while(*p && *src && toLOWER(*p)==toLOWER(*src)) ++p, ++src; if (!*p) seen_key = 1; } ++src; } else ++src; } return NULL; } Try deleting the || *src=='"' after my comment above. It's really down to whether the code is supposed to deal with double quotes which I cannot see it can e.g.: insert into "fred"."dave" will do something similar and return fred"."dave". Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 2.7k
Test case from ribasushi: use strict; use warnings; use DBI; use Test::More; use Test::Exception; my $dbh = DBI->connect(@ENV{map { "DBICTEST_ORA_${_}" } qw/DSN USER PASS/}, { AutoCommit => 1, RaiseError => 1 }); $dbh->do('DROP TABLE "bindtype_test"'); $dbh->do('CREATE TABLE "bindtype_test" ("id" integer NOT NULL, "blob" blob NULL )'); lives_ok { $dbh->prepare('INSERT INTO "bindtype_test" ("id") VALUES (?)')->execute(1); }; lives_ok { $dbh->prepare('INSERT INTO "bindtype_test" ("id", "blob") VALUES (?, ?)')->execute(2, 'abc'); }; lives_ok { my $sth = $dbh->prepare('INSERT INTO "bindtype_test" ("id", "blob") VALUES (?, ?)'); $sth->bind_param(1, 3); $sth->bind_param(2, 'abc', { ora_type => DBD::Oracle::ORA_BLOB(), ora_field => 'blob'} ); $sth->execute; }; lives_ok { my $sth = $dbh->prepare('INSERT INTO "bindtype_test" ("id", "blob") VALUES (?, ?)'); $sth->bind_param(1, 4); $sth->bind_param(2, 'abc', { ora_type => DBD::Oracle::ORA_BLOB(), ora_field => '"blob"'} ); $sth->execute; }; done_testing; t/73oracle.t .. ok 1 ok 2 not ok 3 DBD::Oracle::st execute failed: ORA-01740: missing double quote in identifier ORA-01740: missing double quote in identifier (DBD SUCCESS: OCIDescribeAny(view)/LOB refetch) [for Statement "INSERT INTO "bindtype_test" ("id", "blob") VALUES (?, ?)" with ParamValues: :p1=3, :p2='abc'] at t/73oracle.t line 26. # Failed test at t/73oracle.t line 27. # died: DBD::Oracle::st execute failed: ORA-01740: missing double quote in identifier # ORA-01740: missing double quote in identifier (DBD SUCCESS: OCIDescribeAny(view)/LOB refetch) [for Statement "INSERT INTO "bindtype_test" ("id", "blob") VALUES (?, ?)" with ParamValues: :p1=3, :p2='abc'] at t/73oracle.t line 26. not ok 4 1..4 DBD::Oracle::st execute failed: ORA-01740: missing double quote in identifier ORA-01740: missing double quote in identifier (DBD SUCCESS: OCIDescribeAny(view)/LOB refetch) [for Statement "INSERT INTO "bindtype_test" ("id", "blob") VALUES (?, ?)" with ParamValues: :p1=4, :p2='abc'] at t/73oracle.t line 33. # Failed test at t/73oracle.t line 34. # died: DBD::Oracle::st execute failed: ORA-01740: missing double quote in identifier # ORA-01740: missing double quote in identifier (DBD SUCCESS: OCIDescribeAny(view)/LOB refetch) [for Statement "INSERT INTO "bindtype_test" ("id", "blob") VALUES (?, ?)" with ParamValues: :p1=4, :p2='abc'] at t/73oracle.t line 33. # Looks like you failed 2 tests of 4. Dubious, test returned 2 (wstat 512, 0x200) Failed 2/4 subtests Test Summary Report ------------------- t/73oracle.t (Wstat: 512 Tests: 4 Failed: 2) Failed tests: 3-4 Non-zero exit status: 2 Files=1, Tests=4, 1 wallclock secs ( 0.02 usr 0.01 sys + 0.32 cusr 0.05 csys = 0.40 CPU) Result: FAIL Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 2.2k
On Sat Dec 25 14:25:15 2010, MJEVANS wrote: Show quoted text
> Test case from ribasushi: >
<Snipped test case> Interestingly when I use instant client 11.2 on Linux to Oracle 11 with the test code above I get: DBD::Oracle::st execute failed: ORA-01741: illegal zero-length identifier ORA-01741: illegal zero-length identifier (DBD SUCCESS: OCIDescribeAny(view)/LOB refetch) [for Statement "INSERT INTO "bindtype_test" ("id", "blob") VALUES (?, ?)" with ParamValues: :p1=3, :p2='abc'] at rt64206.pl line 28. However, the table name still contains the trailing " Removing the trailing " as I first suggested does not work: DBD::Oracle::st execute failed: ORA-04043: object bindtype_test does not exist ( DBD SUCCESS: OCIDescribeAny(view)/LOB refetch) [for Statement "INSERT INTO "bind type_test" ("id", "blob") VALUES (?, ?)" with ParamValues: :p1=3, :p2='abc'] at rt64206.pl line 28. so it obviously needs to keep the trailing quote but needs to pick up the leading one too. Changing the find_ident_after to: } else if (isALPHA(*src) || (*src == '"')) { if (seen_key) { gets the leading and trailing quote ("bindtype_test") but for me leads to: DBD::Oracle::st execute failed: ORA-00904: "BLOB": invalid identifier (DBD ERROR : error possibly near <*> indicator at char 12 in 'INSERT INTO <*>"bindtype_test " ("id", "blob") VALUES (:p1, :p2)') [for Statement "INSERT INTO "bindtype_test" ("id", "blob") VALUES (?, ?)" with ParamValues: :p1=3, :p2='abc'] at rt64206.pl line 28. Rewriting the test code to avoid quoting on column names, renaming column blob to ablob and using the change above runs through successfully (so long as you ignore the ora_field => '"blob"' which I don't think would ever work, although I understand why it is in the test code). So, an improvement but now quoting on column names is coming into it. I suspect the column name issue is only with lobs and the column name usage in ora_field. Oracle knows table "bindtype_test" means the table with the exact name and case bindtype_test and with the change this works. However, DBD::Oracle does not have the idea of maintaining case in the ora_field value. Will need to look at how DBD::Oracle relates ora_field to oracle. Martin -- Martin J. Evans Wetherby, UK
Download (untitled) / with headers
text/plain 121b
Addition to this ticket - it is now apparent that even *without* specifying ora_field everything breaks anyway. Ideas? :)
Subject: [rt.cpan.org #64206] Re: Quoted table name breaks when rebinding LOBs
Date: Mon, 22 Jul 2013 16:08:44 +0200
To: "'bug-DBD-Oracle [...] rt.cpan.org'" <bug-DBD-Oracle [...] rt.cpan.org>
From: Jonathan Stowe <jonathan.stowe [...] db.com>
Download (untitled) / with headers
text/plain 870b
Classification: Public Hi, Has anyone got any further with this? It's manifesting itself with the default behaviour of DBIx::Class when writing to a LOB on Oracle, we are seeing the "ORA-01741: illegal zero-length identifier (DBD SUCCESS: OCIDescribeAny(view)/LOB refetch)" but that does seem to be consistent with how it is described as happening on the instance client. (with quote_names set to true anyway.) Thanks, /J\ --- This e-mail may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and delete this e-mail. Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden. Please refer to http://www.db.com/en/content/eu_disclosures.htm for additional EU corporate and regulatory disclosures.
Download (untitled) / with headers
text/html 23.6k

Message body is not shown because it is too large.

Download (untitled) / with headers
text/plain 1.5k
On Mon Jul 22 10:09:05 2013, jonathan.stowe@db.com wrote: Show quoted text
> Classification: Public > Hi, > Has anyone got any further with this?
As I've said before, I didn't write this code and there is nothing to indicate what it was supposed to do. Quoting on column names seems simple but in reality it is a horrible issue. Show quoted text
> It's manifesting itself with the default behaviour of DBIx::Class when > writing to a LOB on Oracle, we are seeing the > "ORA-01741: illegal zero-length identifier (DBD SUCCESS: > OCIDescribeAny(view)/LOB refetch)" but that does seem to be > consistent with how it is described as happening on the instance > client. (with quote_names set to true anyway.) > > Thanks, > > /J\
As far as I am aware this has only been reported wrt to DBIx::Class as no other code attempts to quote column names. DBIx::Class has its work cut out as DB engines tend to uppercase, lowercase or maintain case on unquoted column names depending on the database (even ODBC has an attribute to see what the DB engine does). I've never used DBIx::Class. I've spent quite a bit of time on this in the past and not got much further since it is not clear what the existing code was intended to do and why DBIx::Class quotes column names. You could try and persuade me this is worth looking at again but I'm short of tuits right now and I'd want some cooperation from Peter (or whoever is coding DBIx::Class now, having seen Peter's recent blog post). I'm not trying to hide but this is a rather specific case it seems DBD::Oracle was never written to cope with. Martin -- Martin J. Evans Wetherby, UK
CC: ARODLAND [...] cpan.org
Subject: Re: [rt.cpan.org #64206] Re: Quoted table name breaks when rebinding LOBs
Date: Thu, 25 Jul 2013 06:33:26 +0000
To: "jonathan.stowe [...] db.com via RT" <bug-DBD-Oracle [...] rt.cpan.org>
From: Peter Rabbitson <ribasushi [...] cpan.org>
Download (untitled) / with headers
text/plain 918b
On Mon, Jul 22, 2013 at 10:09:05AM -0400, jonathan.stowe@db.com via RT wrote: Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=64206 > > > Classification: Public > Hi, > Has anyone got any further with this? > > It's manifesting itself with the default behaviour of DBIx::Class when writing to a LOB on Oracle, we are seeing the
Since fixing DBD::Oracle will prove very cumbersome, there was work done [1] to use the native OCI calls ($dbh->ora_lob_write). Unfortunately the work was... not of exceedingly high quality and the author never cared to clean up his act, even with the main set of problems clearly identified [2]. So for the time being this code lays in a branch ready for a white knight to take it further on. Want to give it a stab (note - not a walk in the pakr)? Cheers! [1] https://github.com/dbsrgits/dbix-class/commit/fcaccd73 [2] https://github.com/dbsrgits/dbix-class/commit/2d1abdd3
CC: ARODLAND [...] cpan.org
Subject: Re: [rt.cpan.org #64206] Quoted table name breaks when rebinding LOBs
Date: Thu, 25 Jul 2013 06:37:30 +0000
To: Martin J Evans via RT <bug-DBD-Oracle [...] rt.cpan.org>
From: Peter Rabbitson <ribasushi [...] cpan.org>
Download (untitled) / with headers
text/plain 643b
On Wed, Jul 24, 2013 at 03:39:53PM -0400, Martin J Evans via RT wrote: Show quoted text
> > DBIx::Class has its work cut out as DB engines tend to uppercase, > lowercase or maintain case on unquoted column names depending on the > database
Actually not really - when the user requests quoting we just quote. It is up to the user to ensure the column names in DBIC will make sense when quoted for that particular DBD. And yes, we encourage quoting everything (it's the sane thing to do) Show quoted text
> and I'd want some cooperation from Peter (or whoever is coding > DBIx::Class now, having seen Peter's recent blog post).
I am still around for the time being ;)
Download (untitled) / with headers
text/plain 1.1k
On Thu Jul 25 02:33:42 2013, RIBASUSHI wrote: Show quoted text
> Since fixing DBD::Oracle will prove very cumbersome, there was work > done [1] > to use the native OCI calls ($dbh->ora_lob_write). Unfortunately the > work was... not of exceedingly high quality and the author never > cared > to clean up his act, even with the main set of problems clearly > identified [2]. So for the time being this code lays in a branch > ready > for a white knight to take it further on. > > Want to give it a stab (note - not a walk in the pakr)? > > Cheers! > > [1] https://github.com/dbsrgits/dbix-class/commit/fcaccd73 > [2] https://github.com/dbsrgits/dbix-class/commit/2d1abdd3
I left something on #dbi irc for you if you can catch up. I'm not "white knight" material right now and as I have never used DBIx::Class (other than running its tests) I've no wish to scratch that itch. I might be able to take another look at this rt but my fogged memory suggests quoted column names are going to be a PITA. DBD::Oracle was never written with that in mind and has next to no comments which help what code is written was meant to do. Martin -- Martin J. Evans Wetherby, UK


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.