last_insert_id issue

[sowais]

Needd help figuring out why last_insert_id() is giving an error. I am using an INSERT statement via interpolating values (an issue asked in another post), but get "Inappropiate I/O control operation" on the last_insert_id line. I ran trace as well but didnt get much out of it.

Trace:
<- execute= ( 1 ) 1 items at C:\IMS_db.pl line 59 via at C:\IMS_db.pl line 43
-> last_insert_id for DBD::ODBC::db (DBI::db=HASH(0x2985304)~0x2985274 undef undef 'table' undef) thr#b52bac
<- last_insert_id= ( ) 0 items (not implemented) at C:\IMS_db.pl line 68 via at C:\IMS_db.pl line 43
<> DESTROY(DBI::st=HASH(0x2985534)) ignored for outer handle (inner DBI::st=HASH(0x29854f4) has ref cnt 1)
-> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x29854f4)~INNER) thr#b52bac
<- DESTROY= ( undef ) 1 items at C:\IMS_db.pl line 43 via at C:\IMS_db.pl line 43 dbih_clearcom 0x29854f4 (com 0x27ffbe4, type 3) 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;  #check I put in place for the single di
+gits
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_log88.txt');        
    
    my $sql = "INSERT INTO table VALUES ('$filename','$file_date','$re
+cord_count','$sheet_count','$doc_ID','$client_ID','$temp_date')";
    my $sth = $dbh->prepare($sql);

    $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;

[fishmonger]

The answer is in the documentation for the DBD::ODBC module.

last_insert_id

DBD::ODBC does not support DBI's last_insert_id. There is no ODBC defined way of obtaining this information. Generally the mechanism (and it differs vastly between databases and ODBC drivers) it to issue a select of some form (e.g., select @@identity or select sequence.currval from dual, etc).

There are literally dozens of databases and ODBC drivers supported by DBD::ODBC and I cannot have them all. If you know how to retrieve the information for last_insert_id and you mail me the ODBC Driver name/version and database name/version with a small working example I will collect examples and document them here.

[sowais]

Thanks, fishmonger! any suggestions of what else (funtion, etc) can be used to obtain the last ID? I am using the SELECT statement with the filename and temp_date scalars to obtain the ID but i am sure there must be a better way of getting that ID. Thanks!

[arkturuz]

I have never used SQL Server so far, but after a bit of googling I found the related query.

From stackoverflow.com:


SELECT SCOPE_IDENTITY();

From ASP docs:


SELECT CAST(scope_identity() AS int)

Hope this helps.

Update:


am using the SELECT statement with the filename and temp_date

I would suggest you use something more unique, like big random int or long string composed of random chars. temp_date might not be precise enough in a higher traffic environment.

[fishmonger]

OT

This comment is unrelated to your question but I'd like to point out that unless you really need to use those individual vars used in creating your $temp_date later in the script, you could simplify that by using the strftime function from the POSIX module.

my $ms = (gettimeofday())[1];
$ms = ($ms-($ms%1000))/1000;

my $temp_date = strftime("%Y-%m-%d %H:%M:%S.$ms", localtime);

[sowais]

Thanks a bunch! I am still learning Perl and not aware of many useful modules like POSIX. That shortens my code tremendously! Thanks again!

[ikegami]

Nothing in your code except print sets $! meaningfully, and then only when it returns false.

See the DBI documentation for information on how errors are reported.

[sowais]

Can you elaborate on what you mean by 'meaningfully'? I am using eval to report any DB errors and because execute and certain statement errors were not being caught by it I am using die and $!. I understand $! is basic, if you can give an example of how the error handling code could be written more meaningfully, that would help a beginner like myself.

[ikegami]

Can you elaborate on what you mean by 'meaningfully'?

In a way that has meaning, makes sense, conveys information, is useful.

I understand $! is basic

Actually, in this situation, it's value is meaningless, makes no sense, conveys no information, useless.

if you can give an example of how the error handling code could be written more meaningfully

Sorry, I thought the linked document had this.

I prefer to pass RaiseError => 1 to connect. Otherwise, if you establish there has been a DBI error, you can get it using DBI->errstr. You can also call errstr on database and statement handles if you want to make sure to get the right error when dealing with multiple handles.

my $id = $dbh->last_insert_id();
die "Foo: " . $dbh->errstr()
   if !defined($id);

[fishmonger]

Personally, I wouldn't wrap it in an eval block. I'd either enable RaiseError on the $dbh handle and let the module tell me when there was an error, or I'd manually check the return status of each db call and die if needed.

The advantage of letting the module handle the error checking/handling is that it's less code that I have to write while still retaining a reasonable level of error handling. The advantage of manually checking each call is that I have more control over how I want to deal with the error handling.

my $dbh = DBI->connect("dbi:ODBC:$DSN", {RaiseError => 1})
       or die "DB connection failure\n" . $DBI::errstr;