Author Topic: SQLiteClose() in every function using DB?  (Read 1974 times)

stuckerj

  • Guest
SQLiteClose() in every function using DB?
« on: August 15, 2012, 08:09:51 pm »
Does anyone know the overhead for opening and closing a SQLite database?  I'm thinking of opening and closing the DB in each function call that accesses it -- every few seconds probably -- and wonder the lag time.  Any other down sides?  Thanks.

nednones

  • Guest
Re: SQLiteClose() in every function using DB?
« Reply #1 on: August 16, 2012, 03:01:32 am »
I'm no expert on SQL but as a programmer looking at efficient coding, I would suggest you leave the connection open and build in some retry connection if the connection is lost and then only close the connection upon the app going into hibernation or exiting. That way, even if there is minimal lag time with the opening and closing you avoid adding additional processes on the cache. Hope that helps?

jjsanchezramirez

  • Guest
Re: SQLiteClose() in every function using DB?
« Reply #2 on: August 16, 2012, 06:39:58 pm »
Open the connection when the app starts. Close the connection when the app exits. You might want to throw in an exception each time you access the database. This way, you can catch any errors, assuming there would be any to begin with.

stuckerj

  • Guest
Re: SQLiteClose() in every function using DB?
« Reply #3 on: August 19, 2012, 01:16:08 pm »
Thanks, nednones, jj. 

I have boatloads of SQL experience, but not so much with SQLite.  Thinking about it, there's really no need to try to make this a stateless app, because it's not.  :-)  So I think I'll leave the connection open for the duration -- open it in AppMain() and close it in AppExit().   

JJ, to your point, if done right, there should be no exceptions worth trapping.  If it crashes it crashes.

Cheers,
Jeff

tim

  • Administrator
  • Hero Member
  • *****
  • Posts: 1595
  • Karma: +11/-1
    • View Profile
    • DragonFireSDK
Re: SQLiteClose() in every function using DB?
« Reply #4 on: August 20, 2012, 12:28:20 pm »
With respect to AppExit(), note that this function won't be called on devices that support multitasking.  So, you can call
Code: [Select]
SuspendInit(AppExit); to make sure that AppExit gets called when the app goes into the background...
Tim Sommers
DragonFireSDK Team

stuckerj

  • Guest
Re: SQLiteClose() in every function using DB?
« Reply #5 on: August 20, 2012, 08:31:22 pm »
What's the behavior when an app resumes after being put in the background?  I wouldn't want the DB connection to be gone....

tim

  • Administrator
  • Hero Member
  • *****
  • Posts: 1595
  • Karma: +11/-1
    • View Profile
    • DragonFireSDK
Re: SQLiteClose() in every function using DB?
« Reply #6 on: August 21, 2012, 09:27:08 am »
The database connection should still be alive as long as you didn't call SQLiteClose().

If you called SQLiteClose() it would be a good idea to keep track of this so you can know whether you need to open the database again.

For example, if you closed the database on suspend, you might want to set a boolean variable to keep track of the state of the database:

Code: [Select]
bool DBOpen=false;
...
if (!DBOpen)
{
    SQLiteOpen("mydatabase.db");
    DBOpen=true;
}
...
void OnSuspend()
{
    SQLiteClose();
    DBOpen=false;
}

Hope this helps.
Tim Sommers
DragonFireSDK Team