Need help in Perl DBI (SQLite) execute behaviour

[sam_bakki]

Hi Monks

I am doing kind of session management for my client server based application. I use Perl DBI + SQLite to store the data into DB. I have following code,

...

  $self->_loggerObj()->logInfo("Cleanup Corpse sessions from DB: ".$se
+lf->_sessionsDBFName());
    # Prepare delete query to clean up corpse
    try
    {
        #Delete all the sessions where Sessions original creation time
+ + SESSION_MAX_LIFE_TIME < NOW
        $stHandle = $dbHandle->prepare( qq{
                                           DELETE FROM TB_SESSIONS WHE
+RE ( TB_SESSIONS.creationTime + ? ) < ? ;
                                      } );
        
    }catch
    {
        $self->_loggerObj()->logError("Can not PREPARE Corpse clean up
+ DELETE for DB: ".$self->_sessionsDBFName()." , ".$dbHandle->errstr()
+.", $_ , in removeSessionDetails");
        $dbHandle->disconnect();
        undef $stHandle;
        undef $dbHandle;
        #Here the main operation is success , Corpse clean up is extra
+, do not report error from Corpse clean up, Just return main operatio
+n
        # i.e removeSessionDetails's result.
        return SESSION_ID_REMOVED;
    };
    #Run query
    try
    {
        $self->_loggerObj()->logInfo("Execute :Corpse clean up DELETE 
+from DB [removeSessionDetails]".SESSION_MAX_LIFE_TIME." ".time());
        $dbHandle->do('BEGIN EXCLUSIVE TRANSACTION');
        my $now = time();
        print "\n NOW: $now";
        my $rv = $dbHandle->do('DELETE FROM TB_SESSIONS WHERE ( TB_SES
+SIONS.creationTime + '.SESSION_MAX_LIFE_TIME.' ) < '.$now.';');
        print "\n ---1.", Dumper($rv);
              
        $rv = $stHandle->execute(SESSION_MAX_LIFE_TIME,time());
        print "\n ---2.", Dumper($rv);
        
        $dbHandle->do('COMMIT TRANSACTION');
    }catch
    {
        $self->_loggerObj()->logError("Can not RUN Corpse clean up DEL
+ETE query for DB: ".$self->_sessionsDBFName()." , ".$dbHandle->errstr
+().", $_ , in removeSessionDetails");
        $dbHandle->disconnect();
        undef $stHandle;
        undef $dbHandle;
        #Here the main operation is success , Corpse clean up is extra
+, do not report error from Corpse clean up, Just return main operatio
+n
        # i.e removeSessionDetails's result.
        return SESSION_ID_REMOVED;
    };    

....

Essentially I am trying to remove dead sessionIDs. I expect same output for both prints but I got following output,

 NOW: 1373630196
 ---1.$VAR1 = '0E0';

 ---2.$VAR1 = 4;

According to my logic, I expect '0E0' , first query output is correct. This is want I want but when I pass the same arguments via prepare + execute, code is not doing what i want. Am I making some stupid mistake??

[Corion]

DBI documents the return value from ->do and ->execute.

->do returns the number of affected rows, as does ->execute, but only if the number is actially known to the database driver.

I wouldn't place too much importance on the return value except its truthyness.

[sam_bakki]

Hi Corion

I think I was not explained my problem clearly. I also do not worry about the return value but the DELETE query behaves differently in execute & do.

$dbHandle->do is not deleting the DB rows. This is what my expected behaviour because , Ex: SESSION_MAX_LIFE_TIME = 900 secs (15mins), and $now is perl's time() function value. I would like to delete session id which is more than 15 mins old. In this case, session ids are just created, it needs to live for 15 mins.

$stHandle->execute just deletes all the sessionsids (all db rows in database) , It should behave like $dbHandle->do right?.

[poj]

Try

$stHandle = $dbHandle->prepare( 
qq{ DELETE FROM TB_SESSIONS 
    WHERE (TB_SESSIONS.creationTime + ? ) < 
    CAST(? AS INTEGER) } );

poj

[sam_bakki]

Hi poj

Great. This works as expected. From DBI doc, I understand that DBI will do the type conversion for me from scalar automatically. Looks like in this case I need to do it manually. But why only for RHS? because LHS is doing addition, so it is converted to INTEGER automatically?

Thanks :)

[poj]

Is your database field creationTime type INTEGER ?.


You might find this statement works

DELETE FROM session WHERE creationTime < ?

if you make the parameter

(time() - SESSION_MAX_LIFE_TIME)

which I think is easier to understand

poj