On Mon Dec 13 19:50:37 2010, Damon.Atkins@contracted.pmintl.com wrote:
<snipped some log output>
Show quoted text> Try1 Correct
> CreateMaint(DBI::db=HASH(0x3e72ac4),2010-01-01
> 00:00:00,datkins,2010-01-01 00:00:00,2010-01-01 00:10:00,A
> Comment1,maint
> enance,blackout,Script :,myselftest)
> RowId 25576
> Try2 NULL to fail
> CreateMaint(DBI::db=HASH(0x3e72ac4),2010-01-01
> 00:00:00,datkins,,2010-01-01 00:10:00,A
> Comment2,maintenance,blackout,Scr
> ipt :,myselftest)
> DBD::ODBC::st
> execute failed: [Microsoft][ODBC SQL Server Driver][SQL
> Server]Cannot insert the value NULL into column 'S
> cheduled_Start_Date_Time', table
> 'System_Tools_DEV.dbo.T_Alarm_Maintenances'; column does not allow
> nulls. INSERT fails.
> (SQL-23000)
> [Microsoft][ODBC SQL Server
> Driver][SQL Server]The statement has been terminated. (SQL-
> 01000)(DBD: st_execute/SQLExecute
> err=-1) at bug.pl line 57.
> SQLexec: INSERT INTO T_Alarm_Maintenances (Created_Date_Time,
> Created_By, Scheduled_Start_Date_Time, Scheduled_End_Date_
> Time,
> Comment, Source, Component, Media, Media_data)
> VALUES (?, ?, ?, ?,
> ?, ?, ?, ?, ?)
> ;SELECT SCOPE_IDENTITY()
I don't think DBI ever intended that you could prepare multiple
statements and cache them like this. If the first fails what should
happen with the second?
Just don't do this, set up a second prepare for the identity or create a
procedure which does the insert, obtains the identity and returns it.
Show quoted text> Data:'2010-01-01
> 00:00:00','datkins','','2010-01-01 00:10:00','A
> Comment2','maintenance','blackout','Script :','myselfte
> st'
> Error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert
> the value NULL into column 'Scheduled_Start_Date_Tim
> e', table
> 'System_Tools_DEV.dbo.T_Alarm_Maintenances'; column does not allow
> nulls. INSERT fails. (SQL-23000)
> [Microsoft][ODBC SQL Server
> Driver][SQL Server]The statement has been terminated. (SQL-
> 01000)(DBD: st_execute/SQLExecute
> err=-1) at bug.pl line 62.
> RowId
>
>
> Try3 Correct
> CreateMaint(DBI::db=HASH(0x3e72ac4),2010-
> 01-01 00:00:00,datkins,2010-01-01 00:00:00,2010-01-01 00:10:00,A
> Comment3,maint
> enance,blackout,Script :,myselftest)
> ---- It
> Stops, Control-C needed to break it ---
> Terminating on signal
> SIGINT(2)
>
> NOTICE: This e-mail may contain confidential
> information, which should not be copied or distributed without
> authorization. If you have received this e-mail message by
> mistake, please inform the sender and delete it from your system.
> Please note that, for the efficient preservation of Company records
> that may be required for litigation, e-mail messages sent to the
> author of this message will be copied and may be retained in a
> secure repository.
Admittedly, it is more than unfortunate that your script hangs but this
is because the insert statement failed, SQL Server moved on to the
select identity and returned the value down the socket but you never
called odbc_more_results. You'd need to do something like:
use DBI;
use strict;
sub doit
{
my $dbh=shift @_;
my $s = $dbh->prepare_cached(q/insert into mje (b) values(?);select
@@identity/);
$s->execute(@_);
print "sql errors $DBI::errstr\n";
$s->{odbc_more_results};
my $identity;
($identity) = $s->fetchrow_array;
print "identity = ", DBI::neat($identity), "\n";
($identity) = $s->fetchrow_array;
}
my $h = DBI->connect("dbi:ODBC:mydsn", "user", "password",
{PrintError => 0}) or die "connect";
eval {$h->do(q/drop table mje/);};
$h->do(q/create table mje (a int identity, b char(10) not null)/);
doit($h,undef);
#doit($h,1, undef);
#doit($h,1,'fred');
#doit($h,1, undef);
doit($h,'fred');
my $r = $h->prepare(q/select * from mje/);
$r->execute;
print DBI::dump_results($r);
But although that will probably stop the hang I doubt you'll get the
identity back with DBD::ODBC 1.13 which is over 6 years old now.
There is a small bug in the latest DBD::ODBC which I'll fix and then the
above example will work completely but I'd strongly suggest you don't
prepare 2 statements in one go as you will eventually find a MS SQL
Server ODBC Driver this won't work on (one which batches up the
statements for instance).
Martin
--
Martin J. Evans
Wetherby, UK
