Re: Add Unicode Support to the DBI

On Wed, 09 Nov 2011 19:41:33 +0000, "Martin J. Evans"
<[email protected]> wrote:

tl;dr;

> On 09/11/2011 15:49, H.Merijn Brand wrote:
> > On Tue, 08 Nov 2011 21:12:13 +0000, "Martin J. Evans"
> > <[email protected]>  wrote:
> >
> >> I've just checked in unicode_test.pl to DBI's subversion trunk in /ex dir.
> >>
> >> It won't run right now without changing the do_connect sub as you have
> >> to specify how to connect to the DB.
> >> Also, there is a DBD specific section at the start where you might have
> >> to add a DBD it does not know about (anything other than DBD::ODBC,
> >> DBD::Oracle, DBD::SQLite, DBD::CSV, DBD::mysql) if it needs something
> >> other than the defaults e.g., the name of the length function in SQL,
> >> the column type for unicode columns and binary columns, the setting to
> >> enable UTF8/Unicode support. It could be a bit of a pain if your DBD
> >> does not support type_info_all but I'm around on irc and in this list if
> >> anyone wants any help making it work.
> >>
> >> It needs rather a lot of tidying up so I'm not putting it forward as
> >> code-of-the-year but it is a start.
> >>
> >> BTW, you'll need Test::More::UTF8 and perhaps a couple of other non core
> >> modules to run it.
> >>
> >> Martin
> > I'll have some deeper look at both Unify and CSV ...
> > Attached is a revised version of the script (first argument is the
> > driver to test, some need more work)
> >
> > $ perl /tmp/unicode_test.pl Unify
> > # Driver DBD::Unify-0.78
> > # Using DBMS_NAME 'Unify DataServer'
> > # Using DBMS_VER undef
> > # Using DRIVER_NAME '/pro/asql/v83I/lib/perl/5.10.1/DBD/Unify.pm'
> > # Using DRIVER_VER '00.78.0000'
> > # SQL_IDENTIFIER_CASE 3
> > # LANGDIR = dutch
> > print() on closed filehandle $fh at /tmp/unicode_test.pl line 438.
> > Use of uninitialized value in concatenation (.) or string at /tmp/unicode_test.pl line 421.
> > Use of uninitialized value in concatenation (.) or string at /tmp/unicode_test.pl line 421.
> > # Found type  (HUGE AMOUNT) size=
> > Use of uninitialized value in string eq at /tmp/unicode_test.pl line 422.
> > Use of uninitialized value in concatenation (.) or string at /tmp/unicode_test.pl line 421.
> > Use of uninitialized value in concatenation (.) or string at /tmp/unicode_test.pl line 421.
> > # Found type  (AMOUNT) size=
> > Use of uninitialized value in string eq at /tmp/unicode_test.pl line 422.
> > Use of uninitialized value in concatenation (.) or string at /tmp/unicode_test.pl line 421.
> > Use of uninitialized value in concatenation (.) or string at /tmp/unicode_test.pl line 421.
> > # Found type  (BINARY) size=
> > Use of uninitialized value in string eq at /tmp/unicode_test.pl line 422.
> > Use of uninitialized value in concatenation (.) or string at /tmp/unicode_test.pl line 421.
> > :
> > :
> > # Found type  (TIME) size=
> > Use of uninitialized value in string eq at /tmp/unicode_test.pl line 422.
> > DBD::Unify::db prepare failed: Syntax error in SQL dynamic statement. [for Statement "create table fredĀ ( a int)"] at /tmp/unicode_test.pl line 214.
> > not ok 1 - unicode table name supported
> > #   Failed test 'unicode table name supported'
> > #   at /tmp/unicode_test.pl line 216.
> > # died: DBD::Unify::db prepare failed: Syntax error in SQL dynamic statement. [for Statement "create table fredÄ ( a int)"] at /tmp/unicode_test.pl line 214.
> > ok 2 # skip Failed to create unicode table name
> > ok 3 # skip Failed to create unicode table name
> > DBD::Unify::db prepare failed: Syntax error in SQL dynamic statement. [for Statement "create table fred ( daveĀ int)"] at /tmp/unicode_test.pl line 214.
> > not ok 4 - unicode column name supported
>
> Your going to have a lot of problems with this test code and DBD::Unify 
> as we previously discovered that DBD::Unify does not decode the data

I can change that as author and maintainer of this driver (if I want)

> coming back from the database itself but it can be decoded by any Perl 
> script using DBD::Unify into the correct data. Any chance you could 
> change the test code to print out the results of type_info_all for 
> DBD::Unify and send me them?

# Driver DBD::Unify-0.78
# Using DBMS_NAME 'Unify DataServer'
# Using DBMS_VER undef
# Using DRIVER_NAME '/pro/asql/v83I/lib/perl/5.10.1/DBD/Unify.pm'
# Using DRIVER_VER '00.78.0000'
# SQL_IDENTIFIER_CASE 3
# LANGDIR = dutch
[
    {   AUTO_UNIQUE_VALUE => 11,
        CASE_SENSITIVE   => 7,
        COLUMN_SIZE      => 2,
        CREATE_PARAMS    => 5,
        DATA_TYPE        => 1,
        FIXED_PREC_SCALE => 10,
        INTERVAL_PRECISION => 18,
        LITERAL_PREFIX   => 3,
        LITERAL_SUFFIX   => 4,
        LOCAL_TYPE_NAME  => 12,
        MAXIMUM_SCALE    => 14,
        MINIMUM_SCALE    => 13,
        NULLABLE         => 6,
        NUM_PREC_RADIX   => 17,
        SEARCHABLE       => 8,
        SQL_DATA_TYPE    => 15,
        SQL_DATETIME_SUB => 16,
        TYPE_NAME        => 0,
        UNSIGNED_ATTRIBUTE => 9
        },
    [   'HUGE AMOUNT',
        -207,
        undef,
        undef,
        undef,
        'PRECISION,SCALE',
        1,
        0,
        3,
        0,
        undef,
        undef,
        undef,
        2,
        2,
        undef,
        undef,
        undef,
        undef
        ],
    [   'AMOUNT',
        -206,
        undef,
        undef,
        undef,
        'PRECISION,SCALE',
        1,
        0,
        3,
        0,
        undef,
        undef,
        undef,
        2,
        2,
        undef,
        undef,
        undef,
        undef
        ],
    [   'BINARY',
        -3,
        undef,
        '\'',
        '\'',
        undef,
        1,
        0,
        3,
        undef,
        undef,
        undef,
        undef,
        undef,
        undef,
        undef,
        undef,
        undef,
        undef
        ],
    [   'BYTE',
        -2,
        undef,
        undef,
        undef,
        undef,
        1,
        0,
        3,
        0,
        undef,
        undef,
        undef,
        0,
        undef,
        undef,
        undef,
        undef,
        undef
        ],
    [   'CHAR',
        1,
        undef,
        undef,
        undef,
        'PRECISION',
        1,
        0,
        3,
        0,
        undef,
        undef,
        undef,
        0,
        undef,
        undef,
        undef,
        undef,
        undef
        ],
    [   'CURRENCY',
        -218,
        undef,
        undef,
        undef,
        'PRECISION,SCALE',
        1,
        0,
        3,
        0,
        2,
        undef,
        undef,
        0,
        8,
        undef,
        undef,
        undef,
        undef
        ],
    [   'HUGE DATE',
        11,
        undef,
        undef,
        undef,
        undef,
        1,
        0,
        3,
        undef,
        undef,
        undef,
        undef,
        undef,
        undef,
        undef,
        undef,
        undef,
        undef
        ],
    [   'DATE',
        9,
        undef,
        undef,
        undef,
        undef,
        1,
        0,
        3,
        undef,
        undef,
        undef,
        undef,
        undef,
        undef,
        undef,
        undef,
        undef,
        undef
        ],
    [   'DECIMAL',
        3,
        undef,
        undef,
        undef,
        'PRECISION',
        1,
        0,
        3,
        0,
        undef,
        undef,
        undef,
        0,
        undef,
        undef,
        undef,
        undef,
        undef
        ],
    [   'DOUBLE PRECISION',
        8,
        undef,
        undef,
        undef,
        'PRECISION',
        1,
        0,
        3,
        0,
        undef,
        undef,
        undef,
        0,
        undef,
        undef,
        undef,
        undef,
        undef
        ],
    [   'FLOAT',
        6,
        undef,
        undef,
        undef,
        'PRECISION',
        1,
        0,
        3,
        0,
        undef,
        undef,
        undef,
        0,
        undef,
        undef,
        undef,
        undef,
        undef
        ],
    [   'HUGE INTEGER',
        -5,
        undef,
        undef,
        undef,
        'PRECISION',
        1,
        0,
        3,
        0,
        undef,
        undef,
        undef,
        0,
        0,
        undef,
        undef,
        undef,
        undef
        ],
    [   'INTEGER',
        4,
        undef,
        undef,
        undef,
        'PRECISION',
        1,
        0,
        3,
        0,
        undef,
        undef,
        undef,
        0,
        undef,
        undef,
        undef,
        undef,
        undef
        ],
    [   'NUMERIC',
        2,
        undef,
        undef,
        undef,
        'PRECISION',
        1,
        0,
        3,
        0,
        undef,
        undef,
        undef,
        0,
        0,
        undef,
        undef,
        undef,
        undef
        ],
    [   'REAL',
        7,
        undef,
        undef,
        undef,
        'PRECISION',
        1,
        0,
        3,
        0,
        undef,
        undef,
        undef,
        0,
        undef,
        undef,
        undef,
        undef,
        undef
        ],
    [   'SMALLINT',
        5,
        undef,
        undef,
        undef,
        'PRECISION',
        1,
        0,
        3,
        0,
        undef,
        undef,
        undef,
        0,
        0,
        undef,
        undef,
        undef,
        undef
        ],
    [   'TEXT',
        -1,
        undef,
        '\'',
        '\'',
        undef,
        1,
        0,
        3,
        undef,
        undef,
        undef,
        undef,
        undef,
        undef,
        undef,
        undef,
        undef,
        undef
        ],
    [   'TIME',
        10,
        undef,
        undef,
        undef,
        undef,
        1,
        0,
        3,
        undef,
        undef,
        undef,
        undef,
        undef,
        undef,
        undef,
        undef,
        undef,
        undef
        ]
    ]

> I'd like to fix all those Use of unitialized value warnings so I'll look 
> into that. The test code has changed so the line numbers are a problem 
> so I'll have to compare them with your attachment. I'm guessing they are 
> all in the processing of the results from type_info_all. To be honest, 
> this is not a unicode/encoding issue but one of my biggest annoyances 
> when working with DBDs and that is finding the right column type for a 
> table is really hit and miss - many DBDs don't support type_info_all and 
> some that do only provide a subset of the columns DBI defines.

I thought I was as complete as possible in these two modules:
http://repo.or.cz/w/DBD-Unify.git/tree/HEAD:/lib/DBD/Unify/

> Somewhere (I've lost it right now but I'll find it later) I have a 
> document which details my experiences of trying to write DBD independent 
> code which worked with DBD::mysql, DBD::DB2 and DBD::Oracle. In the end 
> we gave up - it was just too difficult. It wasn't just the different SQL 
> syntax/support. We ended up putting all the logic into 
> procedures/functions and simply calling them from Perl which avoided SQL 
> syntax issues and loads of other differences. Perhaps I'll go back to 
> that if I find some time.
> 
> >
> > $ perl /tmp/unicode_test.pl
> > # Driver DBD::SQLite-1.33
> > # Using DBMS_NAME 'SQLite'
> > # Using DBMS_VER '3.7.6.3'
> > # Using DRIVER_NAME undef
> > # Using DRIVER_VER undef
> > # SQL_IDENTIFIER_CASE undef
> > # LANGDIR = dutch
> > print() on closed filehandle $fh at /tmp/unicode_test.pl line 438.
> > ok 1 - unicode table name supported
> > ok 2 - unicode table found in unqualified table_info
> > ok 3 - unicode table found by qualified table_info
> > ok 4 - unicode column name supported
> > ok 5 - unicode column found in unqualified column_info
> > ok 6 - unicode column found by qualified column_info
> > ok 7 - table for unicode data
> > ok 8 - insert unicode data into table
> > ok 9 - unicode data out = unicode data in, no where
> > ok 10 - length of output data the same
> > ok 11 - db length of unicode data correct
> > ok 12 - select unicode data via parameterised where
> > ok 13 - select unicode data via inline where
> > ok 14 - table for unicode data
> > ok 15 - insert unicode data and blob into table
> > ok 16 - unicode data out = unicode data in, no where with blob
> > ok 17 - utf8 flag not set on blob data
> > Use of uninitialized value in string eq at /tmp/unicode_test.pl line 373.
> > ok 18 - retrieved blob = inserted blob
> > ok 19 - test table for unicode parameter markers
> > DBD::SQLite::st bind_param failed: Unknown named parameter: fred⬠[for Statement "insert into fred (a) values (:fred€)"] at /tmp/unicode_test.pl line 390.
> > not ok 20 - bind parameter with unicode parameter marker
> > #   Failed test 'bind parameter with unicode parameter marker'
> > #   at /tmp/unicode_test.pl line 392.
> > # died: DBD::SQLite::st bind_param failed: Unknown named parameter: fred⬠[for Statement "insert into fred (a) values (:fredâ¬)"] at /tmp/unicode_test.pl line 390.
> > 1..20
> > # Looks like you failed 1 test of 20.
> >
> >
> 
> Martin
> 


-- 
H.Merijn Brand  http://tux.nl      Perl Monger  http://amsterdam.pm.org/
using 5.00307 through 5.14 and porting perl5.15.x on HP-UX 10.20, 11.00,
11.11, 11.23 and 11.31, OpenSuSE 10.1, 11.0 .. 11.4 and AIX 5.2 and 5.3.
http://mirrors.develooper.com/hpux/           http://www.test-smoke.org/
http://qa.perl.org      http://www.goldmark.org/jeff/stupid-disclaimers/