Binding values to table with autoincrement ID

[sowais]

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;

[McA]

Hi,

you have to specify what columns of the table you want to insert values into. So you have to do something like:

my $sql = "INSERT INTO table (col2, col3, col4, col5, col6, col7, col8
+) VALUES (?,?,?,?,?,?,?)";

where colX are the names of the columns to insert into. I assumed that the first column is the autoincrement column. You have to take the real column names.

Without specifying the column names I would guess that the driver is going from left to right in the column definition of the table trying to bind the first variable to the autoincrement column.

My advice: Do always specify the column names. It's more robust against some kind of table layout changes.

McA

[sowais]

Thanks McA! that did the trick!

[mje]

Also, you should realise last_insert_id will not work with DBD::ODBC - read last_insert_id