Wise ones,

Below you will find
1. Yesterday's post
2. The full code
3. copy the screen as the program runs till 10 then fails
4. Info on the database table

Yesterdays Post

$sessionid = $logquery->fetchrow_array;
$sessionid = '0' if not defined $sessionid;
$sessionid++;
[download]

This works great until $sessionid = 10, then $sessionid++; does not increment to 11. Later in the code I insert $sessionid into a database and after 10 I get a PK violation. How do I make $sessionid increment larger than 10.

The Full Code

#!/usr/bin/perl
# timeline.pl
use warnings;
use strict;
use DBI;

# #######################
# STEP 1 SET THE SESSION
# #######################
# here we are going to get the max session_id 
# from [logs].sessions and then add 1
# this will be the session of the entire process

# connect to the database
my ($logs, $sessionid, $timeid);
$logs=DBI->connect('DBI:ODBC:claudonntLOG', 'xxxxx') ||
    die "error opening database: $DBI::errstr\n";

# prepare the query
my ($logquery);
$logquery=$logs->prepare("
    SELECT max (session_id) 
        FROM sessions;")  
    || die "Prepare failed: $DBI::errstr\n";

# prepare the timequery 
    my ($timequery);
    $timequery=$logs->prepare("
        SELECT GETDATE();")  
        || die "Prepare failed: $DBI::errstr\n";

# execute the query
$logquery->execute() || die "couldn't execute query: $DBI::errstr\n";

# assign sessionid
$sessionid = $logquery->fetchrow_array;
$sessionid = '0' if not defined $sessionid;
$sessionid++;

# assign timeid
$timequery->execute() || die "couldn't execute query: $DBI::errstr\n";
$timeid = $timequery->fetchrow_array;

# prepare the session insert query
print "$sessionid $timeid\n";
my ($insert);
$insert=$logs->prepare("
    INSERT into sessions (session_id, session_date)
        VALUES ('$sessionid', '$timeid');")  
    || die "Prepare failed: $DBI::errstr\n";
$insert->execute() || die "couldn't execute query: $DBI::errstr\n";

# clean up all the varaibles
$logquery ->finish();
$timequery->finish();
$insert->finish;

undef $logquery;
undef $timequery;
undef $insert;

undef $sessionid;
undef $timeid;
undef $logquery;
[download]

Copy Of The Screen As The Program Runs

D:\timeline>timeline.pl
8 2001-05-10 10:32:55.280

D:\timeline>timeline.pl
9 2001-05-10 10:32:56.970

D:\timeline>timeline.pl
10 2001-05-10 10:32:58.843

D:\timeline>timeline.pl
10 2001-05-10 10:33:00.867
[download]

Note that the sessionid went up each time the program ran and then hung on 10. Now i'll show you the error as the insert fails b/c of a PK violation

 
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL 
+Server]Vio
lation of PRIMARY KEY constraint 'pk_session'. Cannot insert duplicate
+ key in ob
ject 'sessions'. (SQL-23000)
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been 
+terminated
. (SQL-01000)(DBD: st_execute/SQLExecute err=-1) at D:\timeline\timeli
+ne.pl line
 57.
couldn't execute query: [Microsoft][ODBC SQL Server Driver][SQL Server
+]Violation
 of PRIMARY KEY constraint 'pk_session'. Cannot insert duplicate key i
+n object '
sessions'. (SQL-23000)
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been 
+terminated
. (SQL-01000)(DBD: st_execute/SQLExecute err=-1)
[download]

The Table

create table sessions (
session_id    varchar (10),
session_date    datetime,
file_archive    varchar (25),
constraint pk_session primary key (session_id))
[download]

As always the help of the monks is apreciated.

2001-05-14 Edit by Corion : Changed title to be more descriptive