Skip Menu |

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

Report information
The Basics
Id: 72418
Status: resolved
Priority: 0/
Queue: DBD-SQLite

People
Owner: Nobody in particular
Requestors: bohica [...] ntlworld.com
Cc:
AdminCc:

Bug Information
Severity: Normal
Broken in:
  • 1.29
  • 1.33
Fixed in: (no value)



Subject: unicode table names not decoded in table_info_all call
Hi, Attached a script I wrote for DBI and testing unicode in various DBDs. It shows that when you create a unicode table name and call table_info_all you cannot find the table by name comparison and yet it does exist. If the names returned by table_info_all are decoded as UTF-8 you can find the table. So that suggests DBD::SQLite is not decoding table names. The same probably applies to unicode column names but although there is a test to check you can use them and find them there is no additional code to test if they are decoded as UTF-8 you can find them. BTW, some tests DBD::SQLite fails no one really expects it to pass like unicode parameter name markers so please ignore that test - the script was written for discovery not to demonstrate any particular bugs. Output when run on Windows (hence the unicode data out is displayed wrong so ignore that) is: martin@bragi:~/svn/dbi/trunk/ex$ perl unicode_test.pl dbi:SQLite:db=test.db # Driver DBD::SQLite-1.29 # Using DBMS_NAME 'SQLite' # Using DBMS_VER '3.6.22' # Using DRIVER_NAME undef # Using DRIVER_VER undef # SQL_IDENTIFIER_CASE undef # LANG = en_GB.UTF-8 # create table "fred" ( "a" varchar(20)) ok 1 - table for unicode data ok 2 - insert unicode data into table ok 3 - unicode data out = unicode data in, no where ok 4 - length of output data the same ok 5 - db length of unicode data correct ok 6 - select unicode data via parameterised where ok 7 - select unicode data via inline where # create table "fred" ( "a" varchar(20),"b" blob) ok 8 - table for unicode data ok 9 - insert unicode data and blob into table ok 10 - unicode data out = unicode data in, no where with blob ok 11 - utf8 flag not set on blob data ok 12 - retrieved blob = inserted blob # create table "fred" ( "a" int) ok 13 - test table for unicode parameter markers DBD::SQLite::st bind_param failed: Unknown named parameter: fredÔé¼ at unicode_t est.pl line 429. not ok 14 - bind parameter with unicode parameter marker # Failed test 'bind parameter with unicode parameter marker' # at unicode_test.pl line 431. # died: DBD::SQLite::st bind_param failed: Unknown named parameter: fred├ó┬é┬¼ a t unicode_test.pl line 429. # create table "fred" ( "dave─Ç" int) ok 15 - unicode column name supported not ok 16 - unicode column found in unqualified column_info # Failed test 'unicode column found in unqualified column_info' # at unicode_test.pl line 306. not ok 17 - unicode column found by qualified column_info # Failed test 'unicode column found by qualified column_info' # at unicode_test.pl line 311. # create table "fred Ç" ( "a" int) ok 18 - unicode table name supported not ok 19 - unicode table found in unqualified table_info # Failed test 'unicode table found in unqualified table_info' # at unicode_test.pl line 269. not ok 20 - Table not found initially but when table name decoded it was found as fred Ç THAT TEST ABOVE^ # Failed test 'Table not found initially but when table name decoded it was found as fred Ç' # at unicode_test.pl line 275. # Did not find tables with utf8 on not ok 21 - unicode table found by qualified table_info # Failed test 'unicode table found by qualified table_info' # at unicode_test.pl line 287. ok 22 # skip table not found 1..22 # Looks like you failed 6 tests of 22. martin@bragi:~/svn/dbi/trunk/ex$ I tried it with 1.33 too - same result. Martin -- Martin J. Evans Wetherby, UK
The test code can be found in DBI subversion trunk in ex/unicode_test.pl but attached here in case it changes. Martin -- Martin J. Evans Wetherby, UK
Subject: unicode_test.pl

Message body is not shown because it is too large.

On Mon Nov 14 14:11:18 2011, MJEVANS wrote: Show quoted text
> The test code can be found in DBI subversion trunk in ex/unicode_test.pl > but attached here in case it changes. > > Martin
The test code has changed to fix a bug - best to get the latest version from DBI subversion tree in ex/unicode_test.pl. Martin -- Martin J. Evans Wetherby, UK
Hi. There was 2 resons why 6 tests fail. 1. sqltite_unicode option is only affects on connect method. DBI->connect($dsn, $user, $pass, { sqlite_unicode => 1}); # ok $dbh->{sqltite_unicode} = 1; # not ok 2. A test case is wrong 'bind parameter' test case seems to be wrong. There is no unicode bug. my $s = $h->prepare(qq/insert into $table (a) values (:$param_marker)/); - $s->bind_param($param_marker, 1); + $s->bind_param(":$param_marker", 1); Fixing two of them makes all unicode_test.pl tests passed. To fix No.1, I guess sqlite_db_login6 should some how detect $dbh->{sqlite_unicode} as well as sqlite_db_STORE_attrib. I'm just looking for how. In addition to No.1 issue there is still a column name bug due to lack of SvUTF8_on. The topic below describes the detail. https://rt.cpan.org/Public/Bug/Display.html?id=78833
On Fri Aug 10 11:41:54 2012, JAMADAM wrote: Show quoted text
> Hi. > > There was 2 resons why 6 tests fail. > > 1. sqltite_unicode option is only affects on connect method. > > DBI->connect($dsn, $user, $pass, { sqlite_unicode => 1}); # ok > $dbh->{sqltite_unicode} = 1; # not ok
Because of a typo? ;) (sql"t"ite_unicode) Show quoted text
> > 2. A test case is wrong > > 'bind parameter' test case seems to be wrong. There is no unicode bug. > > my $s = $h->prepare(qq/insert into $table (a) values (:
$param_marker)/); Show quoted text
> - $s->bind_param($param_marker, 1); > + $s->bind_param(":$param_marker", 1);
This is noted at the top of unicode_test.pl, but thanks for the heads- up. I'll remember to tweak this when I test. Show quoted text
> Fixing two of them makes all unicode_test.pl tests passed.
I'm not sure exactly what you did to pass the tests, but didn't you simply add sqlite_unicode => 1 in the "do_connect" function? Also, note that "*_info" methods reside in lib/DBD/SQLite.pm. Show quoted text
> > To fix No.1, I guess sqlite_db_login6 should some how detect > $dbh->{sqlite_unicode} as well as sqlite_db_STORE_attrib. > I'm just looking for how. > > In addition to No.1 issue there is still a column name bug due to lack > of SvUTF8_on. > The topic below describes the detail. > > https://rt.cpan.org/Public/Bug/Display.html?id=78833
On 2012-8月-09 木 23:37:32, ISHIGAKI wrote: Show quoted text
> On Fri Aug 10 11:41:54 2012, JAMADAM wrote:
> > Hi. > > > > There was 2 resons why 6 tests fail. > > > > 1. sqltite_unicode option is only affects on connect method. > > > > DBI->connect($dsn, $user, $pass, { sqlite_unicode => 1}); # ok > > $dbh->{sqltite_unicode} = 1; # not ok
> > Because of a typo? ;) (sql"t"ite_unicode) >
Sorry, the report is wrong. I tested without typo. Show quoted text
> > > > 2. A test case is wrong > > > > 'bind parameter' test case seems to be wrong. There is no unicode bug. > > > > my $s = $h->prepare(qq/insert into $table (a) values (:
> $param_marker)/);
> > - $s->bind_param($param_marker, 1); > > + $s->bind_param(":$param_marker", 1);
> > This is noted at the top of unicode_test.pl, but thanks for the heads- > up. I'll remember to tweak this when I test.
I overlooked that. Show quoted text
>
> > Fixing two of them makes all unicode_test.pl tests passed.
> > I'm not sure exactly what you did to pass the tests, but didn't you > simply add sqlite_unicode => 1 in the "do_connect" function?
Yes, I exactly did it to identify the issue is on sqlite_unicode detection. The driver still needs fix for passing original unicode_test.pl. Show quoted text
> > Also, note that "*_info" methods reside in lib/DBD/SQLite.pm. >
With the test modification, attached unicode_test.pl doesn't pass 'unicode table name' test but the newest one on svn passes.
On Fri Aug 10 13:29:24 2012, JAMADAM wrote: Show quoted text
> On 2012-8月-09 木 23:37:32, ISHIGAKI wrote:
> > On Fri Aug 10 11:41:54 2012, JAMADAM wrote:
> > > Hi. > > > > > > There was 2 resons why 6 tests fail. > > > > > > 1. sqltite_unicode option is only affects on connect method. > > > > > > DBI->connect($dsn, $user, $pass, { sqlite_unicode => 1}); # ok > > > $dbh->{sqltite_unicode} = 1; # not ok
> > > > Because of a typo? ;) (sql"t"ite_unicode) > >
> > Sorry, the report is wrong. I tested without typo. >
> > > > > > 2. A test case is wrong > > > > > > 'bind parameter' test case seems to be wrong. There is no unicode
bug. Show quoted text
> > > > > > my $s = $h->prepare(qq/insert into $table (a) values (:
> > $param_marker)/);
> > > - $s->bind_param($param_marker, 1); > > > + $s->bind_param(":$param_marker", 1);
> > > > This is noted at the top of unicode_test.pl, but thanks for the
heads- Show quoted text
> > up. I'll remember to tweak this when I test.
> > I overlooked that. >
> >
> > > Fixing two of them makes all unicode_test.pl tests passed.
> > > > I'm not sure exactly what you did to pass the tests, but didn't you > > simply add sqlite_unicode => 1 in the "do_connect" function?
> > Yes, I exactly did it to identify the issue is on sqlite_unicode
detection. Show quoted text
> The driver still needs fix for passing original unicode_test.pl.
Then try adding $h->{sqlite_unicode} = 1; after $h = do_connect(); in the middle of the test. The test will pass, but that's not the point of this test. Show quoted text
> > > > Also, note that "*_info" methods reside in lib/DBD/SQLite.pm. > >
> > With the test modification, attached unicode_test.pl doesn't pass > 'unicode table name' test but the newest one on svn passes. >
You're right. I'd misunderstood the test. There is no bug on sqlite_unicode detection. I will retry to investigate. Thanks. On 2012-8月-10 金 00:40:57, ISHIGAKI wrote: Show quoted text
> On Fri Aug 10 13:29:24 2012, JAMADAM wrote:
> > On 2012-8月-09 木 23:37:32, ISHIGAKI wrote:
> > > On Fri Aug 10 11:41:54 2012, JAMADAM wrote:
> > > > Hi. > > > > > > > > There was 2 resons why 6 tests fail. > > > > > > > > 1. sqltite_unicode option is only affects on connect method. > > > > > > > > DBI->connect($dsn, $user, $pass, { sqlite_unicode => 1}); # ok > > > > $dbh->{sqltite_unicode} = 1; # not ok
> > > > > > Because of a typo? ;) (sql"t"ite_unicode) > > >
> > > > Sorry, the report is wrong. I tested without typo. > >
> > > > > > > > 2. A test case is wrong > > > > > > > > 'bind parameter' test case seems to be wrong. There is no unicode
> bug.
> > > > > > > > my $s = $h->prepare(qq/insert into $table (a) values (:
> > > $param_marker)/);
> > > > - $s->bind_param($param_marker, 1); > > > > + $s->bind_param(":$param_marker", 1);
> > > > > > This is noted at the top of unicode_test.pl, but thanks for the
> heads-
> > > up. I'll remember to tweak this when I test.
> > > > I overlooked that. > >
> > >
> > > > Fixing two of them makes all unicode_test.pl tests passed.
> > > > > > I'm not sure exactly what you did to pass the tests, but didn't you > > > simply add sqlite_unicode => 1 in the "do_connect" function?
> > > > Yes, I exactly did it to identify the issue is on sqlite_unicode
> detection.
> > The driver still needs fix for passing original unicode_test.pl.
> > Then try adding $h->{sqlite_unicode} = 1; after $h = do_connect(); in > the middle of the test. The test will pass, but that's not the point of > this test. >
> > > > > > Also, note that "*_info" methods reside in lib/DBD/SQLite.pm. > > >
> > > > With the test modification, attached unicode_test.pl doesn't pass > > 'unicode table name' test but the newest one on svn passes. > >
> >
Hi. DBD::SQLite 1.38_01 should fix the issue, though the script in the DBI repo still needs some fix. Please refer to t/ rt_78833_utf8_flag_for_column_names.t as well. Closed this ticket as resolved for now. Thanks. On Tue Nov 15 04:07:22 2011, MJEVANS wrote: Show quoted text
> Hi, > > Attached a script I wrote for DBI and testing unicode in various DBDs. > It shows that when you create a unicode table name and call > table_info_all you cannot find the table by name comparison and yet it > does exist. If the names returned by table_info_all are decoded as
UTF-8 Show quoted text
> you can find the table. So that suggests DBD::SQLite is not decoding > table names. The same probably applies to unicode column names but > although there is a test to check you can use them and find them there > is no additional code to test if they are decoded as UTF-8 you can
find Show quoted text
> them. > > BTW, some tests DBD::SQLite fails no one really expects it to pass
like Show quoted text
> unicode parameter name markers so please ignore that test - the script > was written for discovery not to demonstrate any particular bugs. > > Output when run on Windows (hence the unicode data out is displayed > wrong so ignore that) is: > > martin@bragi:~/svn/dbi/trunk/ex$ perl unicode_test.pl
dbi:SQLite:db=test.db Show quoted text
> # Driver DBD::SQLite-1.29 > # Using DBMS_NAME 'SQLite' > # Using DBMS_VER '3.6.22' > # Using DRIVER_NAME undef > # Using DRIVER_VER undef > # SQL_IDENTIFIER_CASE undef > # LANG = en_GB.UTF-8 > # create table "fred" ( "a" varchar(20)) > ok 1 - table for unicode data > ok 2 - insert unicode data into table > ok 3 - unicode data out = unicode data in, no where > ok 4 - length of output data the same > ok 5 - db length of unicode data correct > ok 6 - select unicode data via parameterised where > ok 7 - select unicode data via inline where > # create table "fred" ( "a" varchar(20),"b" blob) > ok 8 - table for unicode data > ok 9 - insert unicode data and blob into table > ok 10 - unicode data out = unicode data in, no where with blob > ok 11 - utf8 flag not set on blob data > ok 12 - retrieved blob = inserted blob > # create table "fred" ( "a" int) > ok 13 - test table for unicode parameter markers > DBD::SQLite::st bind_param failed: Unknown named parameter: fredÔé¼ at > unicode_t > est.pl line 429. > not ok 14 - bind parameter with unicode parameter marker > # Failed test 'bind parameter with unicode parameter marker' > # at unicode_test.pl line 431. > # died: DBD::SQLite::st bind_param failed: Unknown named parameter: > fred├ó┬é┬¼ a > t unicode_test.pl line 429. > # create table "fred" ( "dave─Ç" int) > ok 15 - unicode column name supported > not ok 16 - unicode column found in unqualified column_info > # Failed test 'unicode column found in unqualified column_info' > # at unicode_test.pl line 306. > not ok 17 - unicode column found by qualified column_info > # Failed test 'unicode column found by qualified column_info' > # at unicode_test.pl line 311. > # create table "fred Ç" ( "a" int) > ok 18 - unicode table name supported > not ok 19 - unicode table found in unqualified table_info > # Failed test 'unicode table found in unqualified table_info' > # at unicode_test.pl line 269. > not ok 20 - Table not found initially but when table name decoded it
was Show quoted text
> found as fred Ç > > THAT TEST ABOVE^ > > # Failed test 'Table not found initially but when table name decoded > it was found as fred Ç' > # at unicode_test.pl line 275. > # Did not find tables with utf8 on > not ok 21 - unicode table found by qualified table_info > # Failed test 'unicode table found by qualified table_info' > # at unicode_test.pl line 287. > ok 22 # skip table not found > 1..22 > # Looks like you failed 6 tests of 22. > martin@bragi:~/svn/dbi/trunk/ex$ > > I tried it with 1.33 too - same result. > > Martin