in reply to What is wrong with this mSQL insert?

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]

Replies are listed 'Best First'.
Re^2: What is wrong with this mSQL insert?
by hallikpapa (Scribe) on Feb 26, 2008 at 22:11 UTC
    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]

In Section Seekers of Perl Wisdom