Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

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

by karlgoethebier (Priest)
on Feb 18, 2013 at 09:15 UTC ( #1019304=perlquestion: print w/ replies, xml ) Need Help??
karlgoethebier has asked for the wisdom of the Perl Monks concerning the following question:

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

«The Crux of the Biscuit is the Apostrophe»

Comment on Why does my insert work with DBI but fails with DBIx::Simple?
Select or Download Code
Re: Why does my insert work with DBI but fails with DBIx::Simple?
by tobyink (Abbot) on Feb 18, 2013 at 10:14 UTC

    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.

    package Cow { use Moo; has name => (is => 'lazy', default => sub { 'Mooington' }) } say Cow->new->name
    f

      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

      «The Crux of the Biscuit is the Apostrophe»

Re: Why does my insert work with DBI but fails with DBIx::Simple?
by Tux (Monsignor) on Feb 18, 2013 at 13:19 UTC

    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?


    Enjoy, Have FUN! H.Merijn

      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

      «The Crux of the Biscuit is the Apostrophe»

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1019304]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (7)
As of 2014-12-28 14:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (181 votes), past polls