Hello Monks! Lately I've been expanding my DB related Perl skills, so please excuse all the DB related posts.

I am getting errors while inserting into a table with autoincrement ID using bind-param. I get 'Invalid character value for cast specification (SQL-22018)' with binding. I tried interpolating values for shits and giggles and that worked fine. After implementing the trace, I believe the issue is that the DB is taking 'param 1' as the ID value but since its an autoincrement value the DB would have given an error related to "...trying to populate an identity column while IDENTITY_INSERT is ON", so I am not sure where the issue resides. Can someone please help in figuring out how to insert into this table with autoincrement ID?

Different options I have tried unsuccessfully:
a. started with param value 2 while having 8 placeholders
b. used undef, '', "" while binding the ID
c. while setting the placeholders skipped the ID value (,?,?,?,?,?,?,?)

From Trace:
* This is why i think its trying to bind filename to ID as ID is integer and filename is VARCHAR: +rebind_param 1 'test_file' (size SvCUR=9/SvLEN=12/max=0) svtype:4, value type:1, sql type:0 +get_param_type(26b8254,1) bind 1 'test...' value_len=9 maxlen=10 null=0) bind 1 value_type:1 INTEGER cs=9 dd=0 bl=9
*Errors recorded in Trace:
!!dbd_error2(err_rc=-1, what=st_execute/SQLExecute, handles=(2627568,22a4a8,2190d0) !SQLError(2627568,22a4a8,2190d0) = (22018, 0, MicrosoftODBC SQL Server DriverInvalid character value for cast specification) -dbd_st_execute(26b8254)=-2 !! ERROR: 1 'MicrosoftODBC SQL Server DriverInvalid character value for cast specification (SQL-22018)' (err#1) <- execute= ( undef ) 1 items at C:\IMS_db.pl line 61 via at C:\IMS_db.pl line 43 <> DESTROY(DBI::st=HASH(0x27c3534)) ignored for outer handle (inner DBI::st=HASH(0x27c34f4) has ref cnt 1) -> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x27c34f4)~INNER) thr#1e2bac ERROR: 1 'MicrosoftODBC SQL Server DriverInvalid character value for cast specification (SQL-22018)' (err#1) <- DESTROY= ( undef ) 1 items at C:\IMS_db.pl line 43 via at C:\IMS_db.pl line 43 dbih_clearcom 0x27c34f4 (com 0x27afbe4, type 3) done. <> DESTROY(DBI::db=HASH(0x27c3304)) ignored for outer handle (inner DBI::db=HASH(0x27c3274) has ref cnt 1) -> DESTROY for DBD::ODBC::db (DBI::db=HASH(0x27c3274)~INNER) thr#1e2bac ERROR: 1 'MicrosoftODBC SQL Server DriverInvalid character value for cast specification (SQL-22018)' (err#0) <- DESTROY= ( undef ) 1 items at C:\IMS_db.pl line 72 via at C:\IMS_db.pl line 72 dbih_clearcom 0x27c3274 (com 0x27af39c, type 2) done. 

use strict;
use warnings;
use DBI;
use Time::HiRes qw/gettimeofday/;
use Win32::ODBC;

my @timedata = localtime();
my $sec = $timedata[0];
$sec = '0'.$sec if $sec < 10;
my $min =  $timedata[1];
$min = '0'.$min if $min < 10;  
my $hour =  $timedata[2];
$hour = '0'.$hour if $hour < 10;
my $day = $timedata[3];
$day = '0'.$day if $day < 10;
my $month = $timedata[4]+1;
$month = '0'.$month if $month < 10;
my $year = $timedata[5]+1900;

(my $seconds, my $ms) = gettimeofday();
$ms = ($ms-($ms%1000))/1000;

my $time = $hour.":".$min.":".$sec.".".$ms;
my $date = $year."-".$month."-".$day;
my $temp_date = $date.' '.$time;

my $server_name = 'SMOKE-SERVER';
my $database_name = 'TESTDB';
my $database_user = '';
my $database_pass = '';

my $filename = 'test_file';
my $file_date = '2011-01-01 00:00:00.000';
my $record_count = 20;
my $sheet_count = 400; 
my $doc_ID = 12;
my $client_ID = 2;

my $dbh;

my $DSN = "driver={SQL Server};server=$server_name;database=$database_
+name;uid=$database_user;pwd=$database_pass;";  

eval {
    $dbh = DBI->connect("dbi:ODBC:$DSN");
    DBI->trace(4,'C:\db_test_log3.txt');        
    
    #below line INSERTS fine
    #my $sql = "INSERT INTO table VALUES ('$filename','$file_date','$r
+ecord_count','$sheet_count','$doc_ID','$client_ID','$temp_date')";
    
    my $sql = "INSERT INTO table VALUES (?,?,?,?,?,?,?)";

    my $sth = $dbh->prepare($sql);
    $sth->bind_param(1, $filename);
    $sth->bind_param(2, $temp_date); #using temp_date instead of $file
+_date to exclude date format issues
    $sth->bind_param(3, $record_count);
    $sth->bind_param(4, $sheet_count);
    $sth->bind_param(5, $doc_ID);
    $sth->bind_param(6, $client_ID);
    $sth->bind_param(7, $temp_date);

    $sth->execute() or die "Execute Failed: $!";
    
    my $last_ID = $dbh->last_insert_id(undef,undef,'table',undef) or d
+ie "No Last ID: $!";
    
    print "Last ID inserted: $last_ID";
    $dbh->disconnect();
};
if($@) {        
    print "DB Failure: $@";
}

exit 0;

[download]

In reply to Binding values to table with autoincrement ID by sowais

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.