Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Need help in Perl DBI (SQLite) execute behaviour

by sam_bakki (Monk)
on Jul 12, 2013 at 12:04 UTC ( #1043949=perlquestion: print w/ replies, xml ) Need Help??
sam_bakki has asked for the wisdom of the Perl Monks concerning the following question:

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??

Thanks & Regards,
Bakkiaraj M
My Perl Gtk2 technology demo project - http://code.google.com/p/saaral-soft-search-spider/ , contributions are welcome.

Comment on Need help in Perl DBI (SQLite) execute behaviour
Select or Download Code
Re: Need help in Perl DBI (SQLite) execute behaviour
by Corion (Pope) on Jul 12, 2013 at 12:27 UTC

    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.

      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?.



      Thanks & Regards,
      Bakkiaraj M
      My Perl Gtk2 technology demo project - http://code.google.com/p/saaral-soft-search-spider/ , contributions are welcome.

Re: Need help in Perl DBI (SQLite) execute behaviour
by poj (Priest) on Jul 12, 2013 at 15:51 UTC
    Try
    $stHandle = $dbHandle->prepare( qq{ DELETE FROM TB_SESSIONS WHERE (TB_SESSIONS.creationTime + ? ) < CAST(? AS INTEGER) } );
    poj

      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 :)



      Thanks & Regards,
      Bakkiaraj M
      My Perl Gtk2 technology demo project - http://code.google.com/p/saaral-soft-search-spider/ , contributions are welcome.

        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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1043949]
Approved by marto
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (4)
As of 2014-08-29 03:49 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (275 votes), past polls