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

From my main script, I created a new CGI::Session object to start a new session and used MySQL for persistent data storage. It worked.

I keep on pushing data to the session for use later on. When the user logs-in, I store the user credentials to the session. However, I have a need to identify a session with the user so I added a column in the "sessions" table named user_id (INT(10) UNSIGNED NOT NULL).

After the user log-on credentials were verified and authenticated, it's time for me to update the sessions table to add the user_id and associate it with a session. Here's the simple code:

$sth = $dbh->do ("UPDATE sessions SET user_id='$user_id' WHERE id LIKE '$CGISESSID'");

The problem here is that when I viewed the session table, the user_id column has not been updated. I verified the variables: $user_id and $CGISESSID indeed have real values. I just can't understand why it never got updated... is there something wrong with the above SQL code? To experiment further, I tried this (instead of the above code):

$sth = $dbh->do ("DELETE FROM sessions");

To my amazement, the session that was created was still there, while the old ones were deleted. Isn't it supposed to be delete also?

I have an impression that this is a simple problem, but it got tricky that I proved myself wrong. Any ideas are welcomed.

Thanks, Jay Soon

Replies are listed 'Best First'.
Re: Updating a new column entry for CGI::Session 'sessions' table
by EvdB (Deacon) on Mar 25, 2004 at 14:00 UTC
    Not sure if this will help but... what value are you putting into user_id when you create the row as user_id is not null? Perhaps this is related to the problem (ie DB is blocking the update due to referential intergrity problems, same for delete)...

    --tidiness is the memory loss of environmental mnemonics

[reply]
[d/l]

      The $user_id is verified to be a real value and not NULL. I even tried hard-coding in my code:

      $sth = $dbh->do ("UPDATE sessions SET user_id='20' WHERE id='$CGISESSID'");

      Still I got the same results

[reply]
[d/l]
        I was refering to the value that is in user_id in the database when you try to update it, not the value you are trying to insert.

        --tidiness is the memory loss of environmental mnemonics

[reply]
[d/l]
Re: Updating a new column entry for CGI::Session 'sessions' table
by astroboy (Chaplain) on Mar 25, 2004 at 14:29 UTC

    I could be off base - I know nothing about CGI:SESSION, and I haven't seen enough of your code... What if you replaced the literals in your update statements with placeholders and then pass through your values later to a prepared statement? I've often seen problems with interpolation of scalars in SQL statements, which is why I tend to prefer placeholders.

[reply]
      I could try that suggestion. However, all my previous implementations were using interpolations and I had no problem. It just appeared that it's locking the session entry so that it can't be modified by an external process. I just have no idea if it's really such.Could MySQL lock a single row entry rather that the entire table?
[reply]
        Are you testing for errors after your execute, or is RaiseError set for your database handle? Sorry, I don't know much about MySQL. If it was a locking problem, I'd either expect your SQL to hang until the lock was released, or return immediately because your update couldn't get a lock...
[reply]

Back to Seekers of Perl Wisdom