SQL unique IDs and Sessions (was: yesterday I posted this and you all aksed for more info so ........)

esolm has asked for the wisdom of the Perl Monks concerning the following question:

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

Comment on SQL unique IDs and Sessions (was: yesterday I posted this and you all aksed for more info so ........) Select or Download Code

Replies are listed 'Best First'.
Re: yesterday I posted this and you all aksed for more info so ........ by no_slogan (Deacon) on May 10, 2001 at 21:54 UTC
Just like I thought, session_id is a varchar. Varchars alphabetize like this: 0 1 10 11 2 3 4 5 6 7 8 9 So 9 is the max, which isn't what you want. Make session_id a number, or make sure you always zero-pad it, or initialize it to "aaaaaaaaaa", or something. Update: Don't know if it's possible two copies of this script could run at the same time, but you might have a problem with two different sessions creating the same new session id at once. Might be better for you to do something like: `insert into sessions (session_id, session_date) select max(session_id)+1, ? from sessions` [download] ... or anything else that does proper locking.	[reply] [d/l]
(crazyinsomniac: bad advice) Re:^2 yesterday I posted this and you all aksed for more info so ........ by crazyinsomniac (Prior) on May 11, 2001 at 13:26 UTC
Bad advice there man without slogan, i also gave it here Win32::ODBC insert question, but was corrected. One thing esolm is doing wrong, and princepawn hinted on(real close) is not using an AutoNumber field. It's Microsoft's answer to autoincrement. Anyway, why don't you just use MS Access to design your database, then you can get to business `CREATE TABLE sessions ( SESSION_ID bigint(20) NOT NULL auto_increment, SESSION_DATE datetime, FILE_ARCHIVE varchar (25), constraint pk_session PRIMARY KEY (session_id) ); or to be MS Access Specific CREATE TABLE sessions ( SESSION_ID bigint(20) NOT NULL AutoNumber, SESSION_DATE datetime, FILE_ARCHIVE varchar (25), constraint pk_session PRIMARY KEY (session_id) );` [download] btw, here are some MS Acces SQL related links i dug up when I was messing with it 2 months ago: http://officeupdate.microsoft.com/articlelist/o2kAccessArticles.htm http://msdn.microsoft.com/library/default.asp?URL=/library/techart/acacclinks.htm http://support.microsoft.com/support/kb/articles/Q140/6/36.asp http://support.microsoft.com/support/kb/articles/Q208/6/79.asp http://msdn.microsoft.com/training/courseware/1539.asp http://officeupdate.microsoft.com/2000/downloaddetails/sp2upd.htm http://support.microsoft.com/support/serviceware/access/acc2000/ and some article titles you should google, cause i just saved them to my hd, and don't have the links: `Advanced Microsoft Jet SQL for Access 2000.html Fundamental Microsoft Jet SQL for Access 2000.html Intermediate Microsoft Jet SQL for Access 2000.html Tips and Tricks for Access 2000.html Fundamentals of Relational Database Design.html A Simple Guide to Five Normal Forms in Relational Database Theory.html` [download] ___crazyinsomniac_______________________________________ `Disclaimer: Don't blame. It came from inside the void` `perl -e "$q=$_;map({chr unpack qq;H;,$_}split(q;;,qH*));print;$q/$q;"`	[reply] [d/l] [select]
Re: Re: yesterday I posted this and you all aksed for more info so ........ by esolm (Acolyte) on May 10, 2001 at 22:47 UTC
Thanks, changed them to decimal. Works like a charm	[reply]
Re: yesterday I posted this and you all aksed for more info so ........ by frankus (Priest) on May 11, 2001 at 14:38 UTC
Just for a moment let us consider the title of your post, so you can learn to get more out of this site. yesterday - Is that the same yesterday as tomorrow's yesterday? We can calculate it from the date of the node, but should we have to? I - Ah that means esolm doesn't it? The rest of the world has to refer themselves as $self or /me. ;-) posted this - Are you sure you don't mean "that" not "this"? :0\| and you all aksed for more info so ........ - Bit lengthy isn't it? Now please read this before you think I'm berating you. A title defines the nature of your query: the modules you're using and a concise description of the symptoms will get the attention of the people who know the most on that topic. We use Newest Nodes a lot. If your query is a continuance of a previous query, try to reuse the previous thread. That way we can redirect similar queries to the same one location. Some people have down voted this, perhaps because the title implies a lack of thought, that might be unfounded, but first impressions count. For brevity use links, rather than cutting and pasting the previous post write a link to it. I sincerely hope you don't take this advice the wrong way, it is meant to help you. I also hope you don't think this is a lecture in our ettiquette, I speak only for myself, often in deciding what to include in the post you can find the solution, yourself which is much more rewarding. -- Brother Frankus. ¤	[reply]
Re: yesterday I posted this and you all aksed for more info so ........ by princepawn (Parson) on May 10, 2001 at 22:30 UTC
you can use perlfunc:sprintf to convert strings to numbers on the fly: `my $session_id = sprintf "%d", $string;` [download] perhaps you could look at Apache::Session or use an autoincrement field (if under MySQL) to handle sessioning for you? Or use a high-end webapp framework like HTML::Embperl which handles sessioning for you in a completely transparent manner.	[reply] [d/l]
Re: yesterday I posted this and you all aksed for more info so ........ by SilverB1rd (Scribe) on May 11, 2001 at 00:00 UTC
when you have a long node with lots of code use the <readmore> tag to keep the top level node small. ------ The Price of Freedom is Eternal Vigilance	[reply]


We don't bite newbies here... much
	PerlMonks