Java Development

[Thecr0w]

hi there.

When I update my database, it always hanged by prepare()! After 30 seconds, close() called finally.

Exception will be throwed if I operate the database during the 30 second before it closed.How Can I overcome this?

code:

   public void update(String sku, String name, int percent, int fileSize, int totalSize) 
   {
	   String sql = "UPDATE _download_table SET _percent = ?, _file_size = ?, _total_size = ? WHERE _name = " + "'"  + name + "'";
	   Log.i(this, "update(): sql = " + sql + ", percent = " + percent);
	   Database db = null;
	   Statement s = null;
	   
	   try {
		   
		   db = open();
		   s = db.createStatement(sql);
		   s.prepare();
		   s.bind( 1, percent );
		   s.bind( 2, fileSize );
		   s.bind( 3, totalSize );
		   s.execute();
		   
	   } catch(Exception e) {
		   
		   Log.alert(this, "update(): Exception = " + e.getMessage());
		   
	   } finally {
		   
		   Log.i(this, "update(): finally");
		   close(s);
		   close(db);
		   
	   }
	   
   }

 log:

[0.0] (i) DBHelper	update(): sql = UPDATE _download_table SET _percent = ?, _file_size[0.0]  = ?, _total_size = ? WHERE _name = 'IQ-Increase', percent = 100
[0.0] (i) DBHelper	open()


//It takes about 30 secondsevery time.


[0.0] (i) DBHelper	update(): finally
[0.0] (i) DBHelper	close()

[Kiran_Snist]

The only reason that prepare statement is thrown a exception is when ur sql statement is incorrect, not just ur UPDATE  statement that u mentioned might be incorrect also chk the create table statement of that table, even that could be incorrect.... better u run the statements and chk using firefox sqlite extention....

[Thecr0w]

Maybe I have not explained myself well, the Exception is not throwed by prepare() method. Finally I avoid invoking update() in a listener's callback thread, and it works fine.I assume the problem is multithread safe in sqlite, because if I do it in UI thread,every thing will be fine.Anyone have any suggestions?Thanks!

[peter_strange]

You need to be careful here.

If you are doing this in a listener call back Thread, then you are not running in your own Application context and you may be running on the Event Thread.  As I understand it, all SQLite processing is blocking, so you should really run it off the Event Thread and you should really run it within your own Application.  So I would encourage you to review the way you have implemented this in order to try to achieve these two objectives:

a) Run in your own Application

b) Run off the Event Thread

One way you can achieve (a) is using a Global Event Listener. 

To achieve (b), just start a Thread to do the Update.

I suspect doing both of these will resolve your problem, unless you have different parts of your own Application that are locking the database.  Sorry this is not an area of expertise, I hope some-else can help regarding database locking.

Hope this helps. 

[Thecr0w]

That's it. Thanks for reply.

Think about this, I need to update download percent and record percent in database.

I will update download percent if user click to stop download, like this:

void stopDownload()

{

  ... update(_percent);

} 

furthermore,  if download complete I will update database as well, like this:

// implements CompleteListener 

void onComplete()

{

  ...update(_percent); ...

} 

update works fine in stopDownload beacause click() is in the UI thread,

but onComplete is invoked by download thread and thing became complecated.

[peter_strange · Accepted solution]

I am dubious abut this statement:

"update works fine in stopDownload because click() is in the UI thread,"

I do not see any reason why updating the database needs to run on the Event Thread.  So something else in your update process is causing a problem, presumably because it does need to run on the Event Thread.  And I'd guess that this requirement is what is stopping the onComplete. 

So you could try using

UiApplication.getUiApplication().invokeLater( .... );

approach to running your onComplete, then it will run on the Event Thread.  If this works, then you have some processing in there that requires the Event Thread.  The SQL processing does not.  I would move the SQL processing off the Event Thread. 

[Thecr0w]

I mean that stopDownload() is invoked by user click the stop button:

navigationClick()
{
...stopDownload()...
}

while onComplete() is invoked by downloader:

class downloader extends Thread
{
...if(fileSize == totalSize)
{ ... completeListener.onComplete();... }
}

I will try invokeLater() in onComplete(), and let you know what I got. Thank you.

[Thecr0w]

invokeLater() did the trick at present! and I will do more test on it.

I wonder why sqlite will be hanged by statement.prepare() without using invokeLater(), you know I 'm not familiar with sqlite in multithread.

[peter_strange]

As I mentioned earlier, I think there are things going on in your code that you don't know about.  They might come back to bit you, they might not.  I would learn about Threads and do the SQLite update using a Thread, but I guess if it working, on device, then you will probably get away with it. 

I suspect it was not the prepare that was causing the problem, but if it was, it was the way it was being used rather than the statement itself. 

[ankitagrawal]

Hi Thecr0w,

                        Can you please show the more code that how you implement the invokeLater for DB transactions.

I have used synchronized block around the database opeations.

But still I am getting 'sqlite Pending operation' at statement.prepare(), if calling DB operation from thread.