Do SQL::Statement's REGEX and TRIM work with DBD::CSV?

Question

Hello!

The functions "REGEX()" and "TRIM()" in this script don't work as I would expect. The REGEX-function returns always true and the TRIM-function returns the "trim_char", not the trimmed string. (When I write the TRIM-function with FROM instead the "," I get an error message.)

#!/usr/bin/perl
use warnings;
use strict;
use 5.010;
use DBI;

my $dbh = DBI->connect( "DBI:CSV:", undef, undef, { RaiseError => 1, AutoCommit => 1 } );
my $table = 'artikel';

my $array_ref = [   [ 'a_nr', 'a_name', 'a_preis' ],  
                [ 12, 'Oberhemd', 39.80, ],
                [ 22, 'Mantel', 360.00, ],
                [ 11, 'Oberhemd', 44.20, ],
                [ 13, 'Hose', 119.50, ],
        ];
$dbh->do( "CREATE TEMP TABLE $table AS IMPORT(?)", {}, $array_ref );
say "";

# purpose   : test if a string matches a perl regular expression
# arguments : a string and a regex to match the string against
# returns   : boolean value of the regex match
# example   : ... WHERE REGEX(col3,'/^fun/i') ... matches rows
#             in which col3 starts with "fun", ignoring case
my $sth = $dbh->prepare( "SELECT a_name FROM $table WHERE REGEX( a_name, '/^O/')" );
$sth->execute();
$sth->dump_results();
say "\n";


# TRIM ( [ [LEADING|TRAILING|BOTH] ['trim_char'] FROM ] string )
$sth = $dbh->prepare( "SELECT a_name, TRIM( TRAILING 'd', a_name ) AS new_name FROM $table" );
$sth->execute();
$sth->dump_results();
say "";


$dbh->disconnect();

Has somebody a piece of advice?

Edit:

DBD::SQLite : 1.25
DBD::ExampleP : 12.010007
DBD::Sponge : 12.010002
DBD::CSV : 0.26
DBD::Gofer : 0.011565
DBD::DBM : 0.03
DBD::Proxy : 0.2004
DBI : 1.609
DBD::File : 0.37

SQL::Statement : 1.23

Answer 1

You should boil your code down to the minimal example necessary to exhibit the problem, and then compare the results you get to what happens when you type those commands into the DB's command line interface. (e.g. try comparing a simple "SELECT TRIM(...)" command.

Also, what DB and version are you using?

Answer 2

Are you sure the underlying SQL engine (DBI::SQL::Nano I guess) has implemented those functions? It may be best to select the data and process it using Perl.

Answer 3

Answer: Neat issue. Short answers from my testing with SQL::Statement-1.23 and DBD::CSV under 5.10.0 with your script:

• REGEX() appears to work, but returns a number, not a boolean, which needs to be handled a bit specially:

  Fix: SELECT a_name FROM $table WHERE REGEX( a_name, '/^O/') = 1

• TRIM() does not take a comma (as in your example); however, it seems unusably broken to me.

  Any use of TRIM( FROM ), in my testing, greatly confused the parser about table names, and any other interesting use seemed to parse out, as you discovered, as a string literal.

  Workaround: SELECT a_name, REPLACE(a_name, 's/d\$//') AS new_name FROM $table N.B.: you'll need to backslash that dollar sign in the s///, as I have, to keep your interpolating quotes...

Appeal: Please file bugs with test cases for this module. SQL::Statement may not be ready for prime time as an SQL engine, but we can help get it there!