hallikpapa has asked for the wisdom of the Perl Monks concerning the following question:

So here is my sql statement and connection to the DB(MS SQL), and this seems to work fine. I can do SELECTS and stuff from the DB with this connection 
$SQL = "INSERT INTO iCDRSbw (rec_num,src_file,src_host,init_status,cdr
+_day,last_modified,city,state,cname,vname,            
            npa,nxx,ocn,lata,lata_name,pdd,zone_id,cdrs_seq,file_seq,g
+c_id
) VALUES (?,?,?,?,?,?,?,?,?,?,
          ?,?,?,?,?,?,?,?,?,?)";


my $database_username = "USER";
my $database_password = "PASS";
my $msdsn = q/dbi:ODBC:DRIVER={SQL Server};Server=10.0.0.5;attr=value/
+;
my $dbh = DBI->connect( $msdsn, $database_username, $database_password
+ )
  or die "ERROR";
$dbh->do("use db_name") or log_die("$DBI::errstr");

[download]
And here is the execute 
unless (
        $sth->execute($rec_num, '$src_cdr_file', '$host','$init_status
+',
            '$f[20]',   '$f[04]', '$city',         '$state',
            '$cname',   '$vname',        $npa,          $nxx,
            '$ocn',     $lata,         $lata_name,    $pdd,
            $f[0],    $f[1],         $f[2],         '$f[3]'
            
        )
          )
    {
        $now = localtime(time);
        print "Error inserting: $DBI::errstr\n"
          if ($LOGERR);
    }

[download]
$f20 and $f04 are dates, everything else in single quotes are char fields. Otherwise they are int fields. All variables are populated, but just in case I set each field to allow NULL = true It gives me this error from the Server 
Error inserting: [Microsoft][ODBC SQL Server Driver]Invalid character 
+value for cast specification (SQL-22018)(DBD: st_execute/SQLExecute e
+rr=-1)

[download]
This statement works fine manually, so I am obviously not handling the variables correctly. 
INSERT INTO iCDRSbw (rec_num,src_file,src_host,init_status,cdr_day,las
+t_modified,city,state,cname,vname,            
            npa,nxx,ocn,lata,lata_name,pdd,zone_id,cdrs_seq,file_seq,g
+c_id
) VALUES (1,'icdr.5_5_5B.0.1.200712170000.052964.0','127.0.0.1','Unpro
+cessed','2007-12-17 00:00:47',
            '2007-12-17 00:00:47','NA','NA',' ',' ',
            999,999,' ',1,1,
            1,1,1,1,'AAAAC0dlvAgiIAABC7Przw.7591990');

[download]

Replies are listed 'Best First'.
Re: What is wrong with this mSQL insert?
by kyle (Abbot) on Feb 26, 2008 at 21:41 UTC

    You're very close.

    In Perl, the expression, '$f[20]', is the literal string "$f[20]". You seem to be trying to quote your parameters before giving them to the database, and that's important, but execute() actually takes care of that for you.

    This should work better: 

            $sth->execute($rec_num, $src_cdr_file, $host,$init_status,
            $f[20],   $f[04], $city,         $state,
            $cname,   $vname,        $npa,          $nxx,
            $ocn,     $lata,         $lata_name,    $pdd,
            $f[0],    $f[1],         $f[2],         $f[3]
            
        )

    [download]

    If there comes a day that you have to interpolate a value into a SQL statement directly, use $dbh->quote() to do the quoting. 

    my $value_quoted = $dbh->quote( $value );
my $SQL = "DELETE FROM t WHERE field = $value_quoted";
$dbh->do( $SQL );

    [download]
[reply]
[d/l]
[select]
      thanks for the reply. I tried your suggestion and I got the same error. Again, I checked and all variables are populated with the appropriate data type. I am going to break it down to just the primary keyy and inserted that. Then try one of each data type. If anyone has any other suggestions, please get on in here! <edit> It is the datetime field. characters, ints, work, but the datetime field isn't taking 2007-01-17 00:00:03 So I am supposed to put single quotes around the dates in a manual insert, but the execute() takes care of that? I will keep digging then
[reply]

        I would try it with literal values from the insert that you say works manually. 

        $SQL = "INSERT INTO iCDRSbw (rec_num,src_file,src_host,init_status,cdr
+_day,last_modified,city,state,cname,vname,            
            npa,nxx,ocn,lata,lata_name,pdd,zone_id,cdrs_seq,file_seq,g
+c_id
) VALUES (?,?,?,?,?,?,?,?,?,?,
          ?,?,?,?,?,?,?,?,?,?)";

my $database_username = "USER";
my $database_password = "PASS";
my $msdsn = q/dbi:ODBC:DRIVER={SQL Server};Server=10.0.0.5;attr=value/
+;
my $dbh = DBI->connect( $msdsn, $database_username, $database_password
+ )
  or die "ERROR";
$dbh->do("use db_name") or log_die("$DBI::errstr");

my $sth = $dbh->prepare( $SQL );
$sth->execute( 1, 'icdr.5_5_5B.0.1.200712170000.052964.0', '127.0.0.1'
+,
               'Unprocessed', '2007-12-17 00:00:47',
               '2007-12-17 00:00:47','NA','NA',' ',' ',
               999,999,' ',1,1,1,1,1,1,'AAAAC0dlvAgiIAABC7Przw.7591990
+' )
    or print "DBI error: " . $sth->errstr;

        [download]

        You might also try using DBI->trace() to see more of what's going on (see the DBI docs).

[reply]
[d/l]
[select]
        Well I always use dbh->quote() for dates, and indeed strings just to be on the safe side. A little overkill if the string should never include punctuation, but I like defensive programming here; 'should never' is a dangerous assumption in the real world (IME).
[reply]
Re: What is wrong with this mSQL insert?
by graff (Chancellor) on Feb 27, 2008 at 03:15 UTC
    If you haven't yet googled for "Invalid character value for cast", you might want to try that, in case there might be anything relevant that isn't even perl-related.

    Apart from that, whenever people say "I tried this and that", but they don't show the actual code that they tried, I tend to get a little skeptical. I'd like to suggest the following, which provides not only (I hope) a reliable test, but also some ideas for making the code easier to manage -- using arrays and hashes to keep things better organized:

    Read more... (3 kB)
    The idea here is that you have a test script that allows you to make sure that the values actually loaded to your variables contain exactly what you are expecting, by comparing them all to test values that are included in the script and are based directly on values that you know ought to work.

    Note that I've posted the test script in such a way that it would pass "perl -cw" on my own machine -- I eliminated the dependencies on things you didn't show us in the OP -- so as you add stuff to populate your "real" data variables, if you get syntax errors or warnings, you know it's because of stuff you've added.

[reply]
[d/l]

Back to Seekers of Perl Wisdom