Why does my insert work with DBI but fails with DBIx::Simple?

[karlgoethebier]

Hi all,

Why do i get an SQL-22018 error when doing a simple insert using DBIx::Simple but the same thing works, when i use DBI? And i'm getting the same error when I'm using DBI with bind_params.

The datatypes

int, nvarchar(255), nvarchar(5),nvarchar(255),nvarchar(10), datetime, int, int, datetime,time(7),int

The data

1234567;11/111111111111111111;B;111111111111111111111111;A11;22.10.2012 00:00:00;7;4142;15.10.2012 00:00:00;13:22

This one fails

#!c:/perl/bin/perl

use strict;
use warnings;
use IO::All;
use DBIx::Simple;

my $file = q(data.csv);

my @lines = io($file)->slurp;
my $table = qq(table);

my $db = DBIx::Simple->connect(
        'DBI:ODBC:myDSN',     
        'user', 'password',                
        { RaiseError => 1 }            
);

foreach my $line (@lines) { 
    my @row = ();
    @row = split( ";", $line );
    chomp(@row);
    $row[3] =~ m/\d{12}(\d{12})/;
    my $some_ID = $1;
    $some_ID = int $some_ID;
    foreach my $item ( 1, 2, 3, 4, 5, 8, 9 ) {
        $row[$item] = qq('$row[$item]');
    }
    $db->insert($table, \@row);
}

The error

DBD::ODBC::st execute failed: [Microsoft][SQL Server Native Client 10.
+0]Ungültiger Zeichenwert für Konvertierungsangabe (SQL-22018)

N.B.: This means something like Invalid character value for cast specification

This one works

#!c:/perl/bin/perl

use strict;
use warnings;
use DBI;
use IO::All;

my $file = q(data.csv);
my @lines = io($file)->slurp;


my $dbh = DBI->connect( "dbi:ODBC:myDSN", "user", "password" ) || die 
+$!;
my $table = qq(table);
my $sth;
my $sql;

foreach my $line (@lines) {   
    my @row = ();
    @row = split( ";", $line );
    chomp(@row);
    $row[3] =~ m/\d{12}(\d{12})/;
    my $some_ID = $1;
    $some_ID = int $some_ID;
    foreach my $item ( 1, 2, 3, 4, 5, 8, 9 ) {
        $row[$item] = qq('$row[$item]');
    }
    $sql .= qq(INSERT INTO $table VALUES($row[0],$row[1],$row[2],$row[
+3],$row[4],$row[5],$row[6],$row[7],$row[8],$row[9],$some_ID ););
}

$sth = $dbh->prepare($sql);
$sth->execute();
$sth->finish();
$dbh->disconnect();

Thank you very much for help and best regards, Karl

[tobyink]

I guess that DBIx::Simple takes care of quoting issues, so you should not be adding single quotes to your values. The other main difference that I can see is that you're not populating the final int in the broken script.

f

[karlgoethebier]

Hi tobyink,

You are right, it should be push @row, $some_ID;.

I added the last column after i switched to DBI and forgot it in the broken example, sorry.

Unfortunatly fixing this and omitting the quotes brought no success. I'm getting still the same error.

Thanks and regards, Karl

[Tux]

While your example might look harmless, are all the records like that? As in, do ALL the lines validate on splitting on ;?

The example line has no quotation (or UTF-8 or embedded newlines), but once it does, reading your source like this is bound to lead to errors - somewhere.

Use Text::CSV_XS or Text::CSV to parse CSV data. Better yet, if you are doing DBI anyway, why not use DBD::CSV to start with?

[karlgoethebier]

Hi Tux,

"...are all the records like that?"

Yes, and i have no influence on it.

Update:

"...do ALL the lines validate on splitting on ;?"

Yes, i had luck ;-)

"...why not use DBD::CSV to start with?"

Good question. I didn't know it. It looks good, so i'll give it a try.

Thank you and best regards, Karl