SQLDroid new connection fails if a previous connection's autocommit mode is set to false

757 views
Skip to first unread message

Damodar Periwal

unread,
May 16, 2012, 7:48:52 PM5/16/12
to Jim Redman, sqld...@googlegroups.com
Jim,

As requested, I am attaching an Android project in which the SQLDroid JDBC driver gives an error (error code = 5, msg = database is locked) if the application tries to get a new database connection when a previously opened connection has its autocommit mode set to false.  If the autcommit mode is set to true for a previously opened connection, the new database connection can be obtained without any error.  The call to DriverManager.getConnection() on line 43 succeeds but the call to DriverManager.getConnection() on line 48 fails.  I am providing the message log below.

Thanks,

-- Damodar Periwal
408-410-9088


05-16 23:30:00.528: INFO/System.out(448): database path = /data/data/com.damodar.android.jdbc2/databases/test.db, new database created = true
05-16 23:30:00.547: INFO/SQLDroid(448): new sqlite jdbc from url 'jdbc:sqldroid:/data/data/com.damodar.android.jdbc2/databases/test.db', '{}'
05-16 23:30:00.547: INFO/SQlDRoid(448): opening database /data/data/com.damodar.android.jdbc2/databases/test.db
05-16 23:30:00.567: INFO/System.out(448): After getting connection...1
05-16 23:30:00.567: INFO/SQLDroid(448): new sqlite jdbc from url 'jdbc:sqldroid:/data/data/com.damodar.android.jdbc2/databases/test.db', '{}'
05-16 23:30:00.567: INFO/SQlDRoid(448): opening database /data/data/com.damodar.android.jdbc2/databases/test.db
05-16 23:30:00.579: INFO/System.out(448): After getting connection...2
05-16 23:30:00.587: INFO/SQLDroid(448): new sqlite jdbc from url 'jdbc:sqldroid:/data/data/com.damodar.android.jdbc2/databases/test.db', '{}'
05-16 23:30:00.587: INFO/SQlDRoid(448): opening database /data/data/com.damodar.android.jdbc2/databases/test.db
05-16 23:30:02.452: INFO/Database(448): sqlite returned: error code = 5, msg = database is locked
05-16 23:30:02.452: ERROR/Database(448): CREATE TABLE android_metadata failed
05-16 23:30:02.458: ERROR/Database(448): Failed to setLocale() when constructing, closing the database
05-16 23:30:02.458: ERROR/Database(448): android.database.sqlite.SQLiteException: database is locked
05-16 23:30:02.458: ERROR/Database(448):     at android.database.sqlite.SQLiteDatabase.native_setLocale(Native Method)
05-16 23:30:02.458: ERROR/Database(448):     at android.database.sqlite.SQLiteDatabase.setLocale(SQLiteDatabase.java:1950)
05-16 23:30:02.458: ERROR/Database(448):     at android.database.sqlite.SQLiteDatabase.<init>(SQLiteDatabase.java:1818)
05-16 23:30:02.458: ERROR/Database(448):     at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:817)
05-16 23:30:02.458: ERROR/Database(448):     at org.sqldroid.SQLiteDatabase.<init>(SQLiteDatabase.java:80)
05-16 23:30:02.458: ERROR/Database(448):     at org.sqldroid.SQLDroidConnection.<init>(SQLDroidConnection.java:80)
05-16 23:30:02.458: ERROR/Database(448):     at org.sqldroid.SQLDroidDriver.connect(SQLDroidDriver.java:45)
05-16 23:30:02.458: ERROR/Database(448):     at java.sql.DriverManager.getConnection(DriverManager.java:191)
05-16 23:30:02.458: ERROR/Database(448):     at java.sql.DriverManager.getConnection(DriverManager.java:154)
05-16 23:30:02.458: ERROR/Database(448):     at com.damodar.android.jdbc2.HelloAndroidJDBC2Activity.tryJDBC(HelloAndroidJDBC2Activity.java:48)
05-16 23:30:02.458: ERROR/Database(448):     at com.damodar.android.jdbc2.HelloAndroidJDBC2Activity.onCreate(HelloAndroidJDBC2Activity.java:23)
05-16 23:30:02.458: ERROR/Database(448):     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1047)
05-16 23:30:02.458: ERROR/Database(448):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2627)
05-16 23:30:02.458: ERROR/Database(448):     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2679)
05-16 23:30:02.458: ERROR/Database(448):     at android.app.ActivityThread.access$2300(ActivityThread.java:125)
05-16 23:30:02.458: ERROR/Database(448):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2033)
05-16 23:30:02.458: ERROR/Database(448):     at android.os.Handler.dispatchMessage(Handler.java:99)
05-16 23:30:02.458: ERROR/Database(448):     at android.os.Looper.loop(Looper.java:123)
05-16 23:30:02.458: ERROR/Database(448):     at android.app.ActivityThread.main(ActivityThread.java:4627)
05-16 23:30:02.458: ERROR/Database(448):     at java.lang.reflect.Method.invokeNative(Native Method)
05-16 23:30:02.458: ERROR/Database(448):     at java.lang.reflect.Method.invoke(Method.java:521)
05-16 23:30:02.458: ERROR/Database(448):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868)
05-16 23:30:02.458: ERROR/Database(448):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626)
05-16 23:30:02.458: ERROR/Database(448):     at dalvik.system.NativeStart.main(Native Method)
05-16 23:30:02.468: INFO/System.out(448): JDBC Error Unable to Chain SQLException database is locked
05-16 23:30:02.488: ERROR/JDBC(448): Error
05-16 23:30:02.488: ERROR/JDBC(448): java.sql.SQLException: Unable to Chain SQLException database is locked
05-16 23:30:02.488: ERROR/JDBC(448):     at org.sqldroid.SQLDroidConnection.chainException(SQLDroidConnection.java:117)
05-16 23:30:02.488: ERROR/JDBC(448):     at org.sqldroid.SQLiteDatabase.<init>(SQLiteDatabase.java:94)
05-16 23:30:02.488: ERROR/JDBC(448):     at org.sqldroid.SQLDroidConnection.<init>(SQLDroidConnection.java:80)
05-16 23:30:02.488: ERROR/JDBC(448):     at org.sqldroid.SQLDroidDriver.connect(SQLDroidDriver.java:45)
05-16 23:30:02.488: ERROR/JDBC(448):     at java.sql.DriverManager.getConnection(DriverManager.java:191)
05-16 23:30:02.488: ERROR/JDBC(448):     at java.sql.DriverManager.getConnection(DriverManager.java:154)
05-16 23:30:02.488: ERROR/JDBC(448):     at com.damodar.android.jdbc2.HelloAndroidJDBC2Activity.tryJDBC(HelloAndroidJDBC2Activity.java:48)
05-16 23:30:02.488: ERROR/JDBC(448):     at com.damodar.android.jdbc2.HelloAndroidJDBC2Activity.onCreate(HelloAndroidJDBC2Activity.java:23)
05-16 23:30:02.488: ERROR/JDBC(448):     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1047)
05-16 23:30:02.488: ERROR/JDBC(448):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2627)
05-16 23:30:02.488: ERROR/JDBC(448):     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2679)
05-16 23:30:02.488: ERROR/JDBC(448):     at android.app.ActivityThread.access$2300(ActivityThread.java:125)
05-16 23:30:02.488: ERROR/JDBC(448):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2033)
05-16 23:30:02.488: ERROR/JDBC(448):     at android.os.Handler.dispatchMessage(Handler.java:99)
05-16 23:30:02.488: ERROR/JDBC(448):     at android.os.Looper.loop(Looper.java:123)
05-16 23:30:02.488: ERROR/JDBC(448):     at android.app.ActivityThread.main(ActivityThread.java:4627)
05-16 23:30:02.488: ERROR/JDBC(448):     at java.lang.reflect.Method.invokeNative(Native Method)
05-16 23:30:02.488: ERROR/JDBC(448):     at java.lang.reflect.Method.invoke(Method.java:521)
05-16 23:30:02.488: ERROR/JDBC(448):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868)
05-16 23:30:02.488: ERROR/JDBC(448):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626)
05-16 23:30:02.488: ERROR/JDBC(448):     at dalvik.system.NativeStart.main(Native Method)
05-16 23:30:02.758: INFO/ActivityManager(73): Displayed activity com.damodar.android.jdbc2/.HelloAndroidJDBC2Activity: 3420 ms (total 3420 ms)

HelloAndroidJDBC2.zip

Jim Redman

unread,
May 16, 2012, 8:01:09 PM5/16/12
to Damodar Periwal, sqld...@googlegroups.com
Damodar,

Does the problem exist if you remove references to the DatabaseHelper?
If so, could you please remove it and resend?

Is it correct that you are calling "setAutoCommit" on conn1 repeatedly?

Thanks,

Jim


On 05/16/2012 05:48 PM, Damodar Periwal wrote:
> Jim,
>
> As requested, I am attaching an Android project in which the SQLDroid
> JDBC driver gives an error (error code = 5, msg = database is locked) if
> the application tries to get a new database connection when a previously
> opened connection has its autocommit mode set to false. If the
> autcommit mode is set to true for a previously opened connection, the
> new database connection can be obtained without any error. The call to
> /DriverManager.getConnection()/ on line 43 succeeds but the call to
> /DriverManager.getConnection()/ on line 48 fails. I am providing the
--
Jim Redman
(505) 662 5156 x85
http://www.ergotech.com

Damodar Periwal

unread,
May 17, 2012, 12:51:27 AM5/17/12
to sqld...@googlegroups.com
Jim,

Since I don't know how to create a new database in Android without using DatabaseHelper, I ran the program once just to create the database using the DatabaseHelper class and then ran the program again after commenting out the database creation logic.  Same result for conn2 (error code = 5, msg = database is locked).

Regarding setAutoCommit, yes, I am calling it on conn1 repeatedly - first with parameter value of true and then with parameter value of false.  Interestingly, the following call to create conn2 succeeds:

            Connection conn1 = DriverManager.getConnection(jdbcURL);
            System.out.println("After getting connection...1");
           
            conn1.setAutoCommit(true); // This statement does not create any problem in subsequent creation of a new connection
            conn1.setAutoCommit(false); // Problem statement
            conn1.setAutoCommit(true);  // However, this statement sets things correct again
           
            Connection conn2 = DriverManager.getConnection(jdbcURL);   // succeeds
            System.out.println("After getting connection...2");

However, if a call to getConnection() is immediately preceded with conn1.setAutoCommit(false), the getConnection() fails with the same error (error code = 5, msg = database is locked):

            Connection conn1 = DriverManager.getConnection(jdbcURL);
            System.out.println("After getting connection...1");
           
            conn1.setAutoCommit(false);  // This statement somehow causes the problem in subsequent creation of a connection.
           
            Connection conn2 = DriverManager.getConnection(jdbcURL);  // fails
            System.out.println("After getting connection...2");

Thanks,

-- Damodar

Jim Redman

unread,
May 17, 2012, 10:09:06 AM5/17/12
to sqld...@googlegroups.com
Damodar,

SQLDroid should create the database for you when you call "getConnection".

The code in SQLiteDatabase is:

sqliteDatabase =
android.database.sqlite.SQLiteDatabase.openDatabase(dbQname, null,
android.database.sqlite.SQLiteDatabase.CREATE_IF_NECESSARY |
android.database.sqlite.SQLiteDatabase.OPEN_READWRITE);

So the "CREATE_IF_NECESSARY" should get the job done.

So I should see the problem if I comment out line 29 and so remove all
the DatabaseHelper code?

Thanks,

Jim


On 05/16/2012 10:51 PM, Damodar Periwal wrote:
> Jim,
>
> Since I don't know how to create a new database in Android without using
> DatabaseHelper, I ran the program once just to create the database using
> the DatabaseHelper class and then ran the program again after commenting
> out the database creation logic. Same result for conn2 (error code = 5,
> msg = database is locked).
>
> Regarding setAutoCommit, yes, I am calling it on conn1 repeatedly -
> first with parameter value of true and then with parameter value of
> false. Interestingly, the following call to create conn2 succeeds:
>
> Connection conn1 = DriverManager.getConnection(jdbcURL);
> System.out.println("After getting connection...1");
>
> conn1.setAutoCommit(true); // This statement does not
> create any problem in subsequent creation of a new connection
> conn1.setAutoCommit(false); // Problem statement
> conn1.setAutoCommit(true); // However, this statement sets
> things correct again
>
> Connection conn2 = DriverManager.getConnection(jdbcURL);
> // succeeds
> System.out.println("After getting connection...2");
>
> However, if a call to getConnection() is immediately preceded with
> conn1.setAutoCommit(*false*), the getConnection() fails with the same
> /DriverManager.getConnection()__/ on line 43 succeeds but the
> call to
> /DriverManager.getConnection()__/ on line 48 fails. I am
> providing the
>
> message log below.
>
> Thanks,
>
> -- Damodar Periwal
> 408-410-9088 <tel:408-410-9088>
>
>
> 05-16 23:30:00.528: INFO/System.out(448): database path =
> /data/data/com.damodar.__android.jdbc2/databases/test.__db, new
> database
> created = true
> 05-16 23:30:00.547: INFO/SQLDroid(448): new sqlite jdbc from url
> 'jdbc:sqldroid:/data/data/com.__damodar.android.jdbc2/__databases/test.db',
> '{}'
> 05-16 23:30:00.547: INFO/SQlDRoid(448): opening database
> /data/data/com.damodar.__android.jdbc2/databases/test.__db
> 05-16 23:30:00.567: INFO/System.out(448): After getting
> connection...1
> 05-16 23:30:00.567: INFO/SQLDroid(448): new sqlite jdbc from url
> 'jdbc:sqldroid:/data/data/com.__damodar.android.jdbc2/__databases/test.db',
> '{}'
> 05-16 23:30:00.567: INFO/SQlDRoid(448): opening database
> /data/data/com.damodar.__android.jdbc2/databases/test.__db
> 05-16 23:30:00.579: INFO/System.out(448): After getting
> connection...2
> 05-16 23:30:00.587: INFO/SQLDroid(448): new sqlite jdbc from url
> 'jdbc:sqldroid:/data/data/com.__damodar.android.jdbc2/__databases/test.db',
> '{}'
> 05-16 23:30:00.587: INFO/SQlDRoid(448): opening database
> /data/data/com.damodar.__android.jdbc2/databases/test.__db
> 05-16 23:30:02.452: INFO/Database(448): sqlite returned: error
> code = 5,
> msg = database is locked
> 05-16 23:30:02.452: ERROR/Database(448): CREATE TABLE
> android_metadata
> failed
> 05-16 23:30:02.458: ERROR/Database(448): Failed to setLocale() when
> constructing, closing the database
> 05-16 23:30:02.458: ERROR/Database(448):
> android.database.sqlite.__SQLiteException: database is locked
> 05-16 23:30:02.458: ERROR/Database(448): at
> android.database.sqlite.__SQLiteDatabase.native___setLocale(Native
> Method)
> 05-16 23:30:02.458: ERROR/Database(448): at
> android.database.sqlite.__SQLiteDatabase.setLocale(__SQLiteDatabase.java:1950)
> 05-16 23:30:02.458: ERROR/Database(448): at
> android.database.sqlite.__SQLiteDatabase.<init>(__SQLiteDatabase.java:1818)
> 05-16 23:30:02.458: ERROR/Database(448): at
> android.database.sqlite.__SQLiteDatabase.openDatabase(__SQLiteDatabase.java:817)
> 05-16 23:30:02.458: ERROR/Database(448): at
> org.sqldroid.SQLiteDatabase.<__init>(SQLiteDatabase.java:80)
> 05-16 23:30:02.458: ERROR/Database(448): at
> org.sqldroid.__SQLDroidConnection.<init>(__SQLDroidConnection.java:80)
> 05-16 23:30:02.458: ERROR/Database(448): at
> org.sqldroid.SQLDroidDriver.__connect(SQLDroidDriver.java:__45)
> 05-16 23:30:02.458: ERROR/Database(448): at
> java.sql.DriverManager.__getConnection(DriverManager.__java:191)
> 05-16 23:30:02.458: ERROR/Database(448): at
> java.sql.DriverManager.__getConnection(DriverManager.__java:154)
> 05-16 23:30:02.458: ERROR/Database(448): at
> com.damodar.android.jdbc2.__HelloAndroidJDBC2Activity.__tryJDBC(__HelloAndroidJDBC2Activity.__java:48)
> 05-16 23:30:02.458: ERROR/Database(448): at
> com.damodar.android.jdbc2.__HelloAndroidJDBC2Activity.__onCreate(__HelloAndroidJDBC2Activity.__java:23)
> 05-16 23:30:02.458: ERROR/Database(448): at
> android.app.Instrumentation.__callActivityOnCreate(__Instrumentation.java:1047)
> 05-16 23:30:02.458: ERROR/Database(448): at
> android.app.ActivityThread.__performLaunchActivity(__ActivityThread.java:2627)
> 05-16 23:30:02.458: ERROR/Database(448): at
> android.app.ActivityThread.__handleLaunchActivity(__ActivityThread.java:2679)
> 05-16 23:30:02.458: ERROR/Database(448): at
> android.app.ActivityThread.__access$2300(ActivityThread.__java:125)
> 05-16 23:30:02.458: ERROR/Database(448): at
> android.app.ActivityThread$H.__handleMessage(ActivityThread.__java:2033)
> 05-16 23:30:02.458: ERROR/Database(448): at
> android.os.Handler.__dispatchMessage(Handler.java:__99)
> 05-16 23:30:02.458: ERROR/Database(448): at
> android.os.Looper.loop(Looper.__java:123)
> 05-16 23:30:02.458: ERROR/Database(448): at
> android.app.ActivityThread.__main(ActivityThread.java:4627)
> 05-16 23:30:02.458: ERROR/Database(448): at
> java.lang.reflect.Method.__invokeNative(Native Method)
> 05-16 23:30:02.458: ERROR/Database(448): at
> java.lang.reflect.Method.__invoke(Method.java:521)
> 05-16 23:30:02.458: ERROR/Database(448): at
> com.android.internal.os.__ZygoteInit$__MethodAndArgsCaller.run(__ZygoteInit.java:868)
> 05-16 23:30:02.458: ERROR/Database(448): at
> com.android.internal.os.__ZygoteInit.main(ZygoteInit.__java:626)
> 05-16 23:30:02.458: ERROR/Database(448): at
> dalvik.system.NativeStart.__main(Native Method)
> 05-16 23:30:02.468: INFO/System.out(448): JDBC Error Unable to Chain
> SQLException database is locked
> 05-16 23:30:02.488: ERROR/JDBC(448): Error
> 05-16 23:30:02.488: ERROR/JDBC(448): java.sql.SQLException:
> Unable to
> Chain SQLException database is locked
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> org.sqldroid.__SQLDroidConnection.__chainException(__SQLDroidConnection.java:117)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> org.sqldroid.SQLiteDatabase.<__init>(SQLiteDatabase.java:94)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> org.sqldroid.__SQLDroidConnection.<init>(__SQLDroidConnection.java:80)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> org.sqldroid.SQLDroidDriver.__connect(SQLDroidDriver.java:__45)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> java.sql.DriverManager.__getConnection(DriverManager.__java:191)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> java.sql.DriverManager.__getConnection(DriverManager.__java:154)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> com.damodar.android.jdbc2.__HelloAndroidJDBC2Activity.__tryJDBC(__HelloAndroidJDBC2Activity.__java:48)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> com.damodar.android.jdbc2.__HelloAndroidJDBC2Activity.__onCreate(__HelloAndroidJDBC2Activity.__java:23)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> android.app.Instrumentation.__callActivityOnCreate(__Instrumentation.java:1047)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> android.app.ActivityThread.__performLaunchActivity(__ActivityThread.java:2627)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> android.app.ActivityThread.__handleLaunchActivity(__ActivityThread.java:2679)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> android.app.ActivityThread.__access$2300(ActivityThread.__java:125)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> android.app.ActivityThread$H.__handleMessage(ActivityThread.__java:2033)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> android.os.Handler.__dispatchMessage(Handler.java:__99)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> android.os.Looper.loop(Looper.__java:123)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> android.app.ActivityThread.__main(ActivityThread.java:4627)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> java.lang.reflect.Method.__invokeNative(Native Method)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> java.lang.reflect.Method.__invoke(Method.java:521)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> com.android.internal.os.__ZygoteInit$__MethodAndArgsCaller.run(__ZygoteInit.java:868)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> com.android.internal.os.__ZygoteInit.main(ZygoteInit.__java:626)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> dalvik.system.NativeStart.__main(Native Method)
> 05-16 23:30:02.758: INFO/ActivityManager(73): Displayed activity
> com.damodar.android.jdbc2/.__HelloAndroidJDBC2Activity: 3420 ms
> (total
> 3420 ms)
>
>
> --
> Jim Redman
> (505) 662 5156 x85 <tel:%28505%29%20662%205156%20x85>
> http://www.ergotech.com

Jim Redman

unread,
May 17, 2012, 11:42:50 AM5/17/12
to sqld...@googlegroups.com, Damodar Periwal
Damodar,

How accurate a representation of what you want to do is the test?

There are a couple of fairly easy solutions to the test.

The fundamental problem with the test is that calling "getConnection"
repeatedly cause multiple calls to

new SQLiteDatabase(dbQname, timeout);

and so to:

sqliteDatabase =
android.database.sqlite.SQLiteDatabase.openDatabase(dbQname, null,
android.database.sqlite.SQLiteDatabase.CREATE_IF_NECESSARY |
android.database.sqlite.SQLiteDatabase.OPEN_READWRITE);

I do not understand the locking of sqlite databases on Android but it
seems reasonable to me that multiple attempts to open a database r/w are
going to create locking problems. Why it should appear that conn1 is
locked rather than the new conn2 is beyond me.

Probably the easiest solution would be to throw if a "getConnection" is
called with a URL that is already used is called while the underlying
SQLiteDatabase is open.

An alternative would be to hash the SQLiteDatabase instances by jdbc and
simply return a connection with a reference to the same SQLiteDatabase
rather than a new (and therefore locked) instance. I'm not sure whether
there would be any unintended consequences to this, but I expect that it
would meet user expectations that they are working on the same database.
I have no idea what happens in a multi-threaded case here. There are
also some possible reference-counting issues concerning the "close()".

Unless you have a really good reason why you can't cache conn1 in your
application, my choice would be to throw. The biggest problem with
option two is I would expect some odd lock/thread/whatever errors that
become the fault of the Driver when in fact they are limitation of the
underlying Android SQLite layer. If we hide/encapsulate Android-level
limitation we'll end up making it more difficult for the user to figure
out why their application failed (and take the blame for the failure).

Oddly, the test case works on Linux with the compatibility layer, so
this appears to be an Android SQLite limitation rather than a universal
SQLite issue.

So, either one of those is a solution to the test case. Does the test
case represent the real case accurately enough that these solutions
would also solve the problems you have.

Jim

On 05/16/2012 10:51 PM, Damodar Periwal wrote:
> Jim,
>
> Since I don't know how to create a new database in Android without using
> DatabaseHelper, I ran the program once just to create the database using
> the DatabaseHelper class and then ran the program again after commenting
> out the database creation logic. Same result for conn2 (error code = 5,
> msg = database is locked).
>
> Regarding setAutoCommit, yes, I am calling it on conn1 repeatedly -
> first with parameter value of true and then with parameter value of
> false. Interestingly, the following call to create conn2 succeeds:
>
> Connection conn1 = DriverManager.getConnection(jdbcURL);
> System.out.println("After getting connection...1");
>
> conn1.setAutoCommit(true); // This statement does not
> create any problem in subsequent creation of a new connection
> conn1.setAutoCommit(false); // Problem statement
> conn1.setAutoCommit(true); // However, this statement sets
> things correct again
>
> Connection conn2 = DriverManager.getConnection(jdbcURL);
> // succeeds
> System.out.println("After getting connection...2");
>
> However, if a call to getConnection() is immediately preceded with
> conn1.setAutoCommit(*false*), the getConnection() fails with the same
> /DriverManager.getConnection()__/ on line 43 succeeds but the
> call to
> /DriverManager.getConnection()__/ on line 48 fails. I am
> providing the
>
> message log below.
>
> Thanks,
>
> -- Damodar Periwal
> 408-410-9088 <tel:408-410-9088>
>
>
> 05-16 23:30:00.528: INFO/System.out(448): database path =
> /data/data/com.damodar.__android.jdbc2/databases/test.__db, new
> database
> created = true
> 05-16 23:30:00.547: INFO/SQLDroid(448): new sqlite jdbc from url
> 'jdbc:sqldroid:/data/data/com.__damodar.android.jdbc2/__databases/test.db',
> '{}'
> 05-16 23:30:00.547: INFO/SQlDRoid(448): opening database
> /data/data/com.damodar.__android.jdbc2/databases/test.__db
> 05-16 23:30:00.567: INFO/System.out(448): After getting
> connection...1
> 05-16 23:30:00.567: INFO/SQLDroid(448): new sqlite jdbc from url
> 'jdbc:sqldroid:/data/data/com.__damodar.android.jdbc2/__databases/test.db',
> '{}'
> 05-16 23:30:00.567: INFO/SQlDRoid(448): opening database
> /data/data/com.damodar.__android.jdbc2/databases/test.__db
> 05-16 23:30:00.579: INFO/System.out(448): After getting
> connection...2
> 05-16 23:30:00.587: INFO/SQLDroid(448): new sqlite jdbc from url
> 'jdbc:sqldroid:/data/data/com.__damodar.android.jdbc2/__databases/test.db',
> '{}'
> 05-16 23:30:00.587: INFO/SQlDRoid(448): opening database
> /data/data/com.damodar.__android.jdbc2/databases/test.__db
> 05-16 23:30:02.452: INFO/Database(448): sqlite returned: error
> code = 5,
> msg = database is locked
> 05-16 23:30:02.452: ERROR/Database(448): CREATE TABLE
> android_metadata
> failed
> 05-16 23:30:02.458: ERROR/Database(448): Failed to setLocale() when
> constructing, closing the database
> 05-16 23:30:02.458: ERROR/Database(448):
> android.database.sqlite.__SQLiteException: database is locked
> 05-16 23:30:02.458: ERROR/Database(448): at
> android.database.sqlite.__SQLiteDatabase.native___setLocale(Native
> Method)
> 05-16 23:30:02.458: ERROR/Database(448): at
> android.database.sqlite.__SQLiteDatabase.setLocale(__SQLiteDatabase.java:1950)
> 05-16 23:30:02.458: ERROR/Database(448): at
> android.database.sqlite.__SQLiteDatabase.<init>(__SQLiteDatabase.java:1818)
> 05-16 23:30:02.458: ERROR/Database(448): at
> android.database.sqlite.__SQLiteDatabase.openDatabase(__SQLiteDatabase.java:817)
> 05-16 23:30:02.458: ERROR/Database(448): at
> org.sqldroid.SQLiteDatabase.<__init>(SQLiteDatabase.java:80)
> 05-16 23:30:02.458: ERROR/Database(448): at
> org.sqldroid.__SQLDroidConnection.<init>(__SQLDroidConnection.java:80)
> 05-16 23:30:02.458: ERROR/Database(448): at
> org.sqldroid.SQLDroidDriver.__connect(SQLDroidDriver.java:__45)
> 05-16 23:30:02.458: ERROR/Database(448): at
> java.sql.DriverManager.__getConnection(DriverManager.__java:191)
> 05-16 23:30:02.458: ERROR/Database(448): at
> java.sql.DriverManager.__getConnection(DriverManager.__java:154)
> 05-16 23:30:02.458: ERROR/Database(448): at
> com.damodar.android.jdbc2.__HelloAndroidJDBC2Activity.__tryJDBC(__HelloAndroidJDBC2Activity.__java:48)
> 05-16 23:30:02.458: ERROR/Database(448): at
> com.damodar.android.jdbc2.__HelloAndroidJDBC2Activity.__onCreate(__HelloAndroidJDBC2Activity.__java:23)
> 05-16 23:30:02.458: ERROR/Database(448): at
> android.app.Instrumentation.__callActivityOnCreate(__Instrumentation.java:1047)
> 05-16 23:30:02.458: ERROR/Database(448): at
> android.app.ActivityThread.__performLaunchActivity(__ActivityThread.java:2627)
> 05-16 23:30:02.458: ERROR/Database(448): at
> android.app.ActivityThread.__handleLaunchActivity(__ActivityThread.java:2679)
> 05-16 23:30:02.458: ERROR/Database(448): at
> android.app.ActivityThread.__access$2300(ActivityThread.__java:125)
> 05-16 23:30:02.458: ERROR/Database(448): at
> android.app.ActivityThread$H.__handleMessage(ActivityThread.__java:2033)
> 05-16 23:30:02.458: ERROR/Database(448): at
> android.os.Handler.__dispatchMessage(Handler.java:__99)
> 05-16 23:30:02.458: ERROR/Database(448): at
> android.os.Looper.loop(Looper.__java:123)
> 05-16 23:30:02.458: ERROR/Database(448): at
> android.app.ActivityThread.__main(ActivityThread.java:4627)
> 05-16 23:30:02.458: ERROR/Database(448): at
> java.lang.reflect.Method.__invokeNative(Native Method)
> 05-16 23:30:02.458: ERROR/Database(448): at
> java.lang.reflect.Method.__invoke(Method.java:521)
> 05-16 23:30:02.458: ERROR/Database(448): at
> com.android.internal.os.__ZygoteInit$__MethodAndArgsCaller.run(__ZygoteInit.java:868)
> 05-16 23:30:02.458: ERROR/Database(448): at
> com.android.internal.os.__ZygoteInit.main(ZygoteInit.__java:626)
> 05-16 23:30:02.458: ERROR/Database(448): at
> dalvik.system.NativeStart.__main(Native Method)
> 05-16 23:30:02.468: INFO/System.out(448): JDBC Error Unable to Chain
> SQLException database is locked
> 05-16 23:30:02.488: ERROR/JDBC(448): Error
> 05-16 23:30:02.488: ERROR/JDBC(448): java.sql.SQLException:
> Unable to
> Chain SQLException database is locked
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> org.sqldroid.__SQLDroidConnection.__chainException(__SQLDroidConnection.java:117)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> org.sqldroid.SQLiteDatabase.<__init>(SQLiteDatabase.java:94)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> org.sqldroid.__SQLDroidConnection.<init>(__SQLDroidConnection.java:80)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> org.sqldroid.SQLDroidDriver.__connect(SQLDroidDriver.java:__45)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> java.sql.DriverManager.__getConnection(DriverManager.__java:191)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> java.sql.DriverManager.__getConnection(DriverManager.__java:154)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> com.damodar.android.jdbc2.__HelloAndroidJDBC2Activity.__tryJDBC(__HelloAndroidJDBC2Activity.__java:48)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> com.damodar.android.jdbc2.__HelloAndroidJDBC2Activity.__onCreate(__HelloAndroidJDBC2Activity.__java:23)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> android.app.Instrumentation.__callActivityOnCreate(__Instrumentation.java:1047)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> android.app.ActivityThread.__performLaunchActivity(__ActivityThread.java:2627)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> android.app.ActivityThread.__handleLaunchActivity(__ActivityThread.java:2679)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> android.app.ActivityThread.__access$2300(ActivityThread.__java:125)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> android.app.ActivityThread$H.__handleMessage(ActivityThread.__java:2033)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> android.os.Handler.__dispatchMessage(Handler.java:__99)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> android.os.Looper.loop(Looper.__java:123)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> android.app.ActivityThread.__main(ActivityThread.java:4627)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> java.lang.reflect.Method.__invokeNative(Native Method)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> java.lang.reflect.Method.__invoke(Method.java:521)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> com.android.internal.os.__ZygoteInit$__MethodAndArgsCaller.run(__ZygoteInit.java:868)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> com.android.internal.os.__ZygoteInit.main(ZygoteInit.__java:626)
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> dalvik.system.NativeStart.__main(Native Method)
> 05-16 23:30:02.758: INFO/ActivityManager(73): Displayed activity
> com.damodar.android.jdbc2/.__HelloAndroidJDBC2Activity: 3420 ms
> (total
> 3420 ms)
>
>
> --
> Jim Redman
> (505) 662 5156 x85 <tel:%28505%29%20662%205156%20x85>
> http://www.ergotech.com

Damodar Periwal

unread,
May 17, 2012, 7:07:45 PM5/17/12
to sqld...@googlegroups.com
Jim,

Thanks for looking into it.  First a clarification: 

The problem is not calling getConnection() repeatedly, rather it is calling getConnection() after issuing a setAutoCommit(false) call on a previously opened connection.  Issuing a setAutoCommit(true) call does not cause any problem.  For example:

            Connection conn10 = DriverManager.getConnection(jdbcURL);
            Connection conn11 = DriverManager.getConnection(jdbcURL);  // ok
            Connection conn12 = DriverManager.getConnection(jdbcURL);  // ok
            conn10.setAutoCommit(true);
            Connection conn13 = DriverManager.getConnection(jdbcURL);  // ok
            conn13.setAutoCommit(true);
            Connection conn14 = DriverManager.getConnection(jdbcURL);  // ok
            conn11.setAutoCommit(true);
            Connection conn15 = DriverManager.getConnection(jdbcURL);  // ok
            conn12.setAutoCommit(false);
            Connection conn16 = DriverManager.getConnection(jdbcURL);  // NOT ok

I don't know if the following stack trace mentioning android-metadata or setLocale() can shed some extra light:

05-17 22:32:59.638: INFO/SQlDRoid(308): opening database /data/data/com.damodar.android.jdbc2/databases/test.db
05-17 22:33:01.167: INFO/Database(308): sqlite returned: error code = 5, msg = database is locked
05-17 22:33:01.167: ERROR/Database(308): CREATE TABLE android_metadata failed
05-17 22:33:01.409: ERROR/Database(308): Failed to setLocale() when constructing, closing the database
05-17 22:33:01.409: ERROR/Database(308): android.database.sqlite.SQLiteException: database is locked
05-17 22:33:01.409: ERROR/Database(308):     at android.database.sqlite.SQLiteDatabase.native_setLocale(Native Method)
05-17 22:33:01.409: ERROR/Database(308):     at android.database.sqlite.SQLiteDatabase.setLocale(SQLiteDatabase.java:1950)
05-17 22:33:01.409: ERROR/Database(308):     at android.database.sqlite.SQLiteDatabase.<init>(SQLiteDatabase.java:1818)
05-17 22:33:01.409: ERROR/Database(308):     at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:817)
05-17 22:33:01.409: ERROR/Database(308):     at org.sqldroid.SQLiteDatabase.<init>(SQLiteDatabase.java:80)
05-17 22:33:01.409: ERROR/Database(308):     at org.sqldroid.SQLDroidConnection.<init>(SQLDroidConnection.java:80)
05-17 22:33:01.409: ERROR/Database(308):     at org.sqldroid.SQLDroidDriver.connect(SQLDroidDriver.java:45)
05-17 22:33:01.409: ERROR/Database(308):     at java.sql.DriverManager.getConnection(DriverManager.java:191)
05-17 22:33:01.409: ERROR/Database(308):     at java.sql.DriverManager.getConnection(DriverManager.java:154)
05-17 22:33:01.409: ERROR/Database(308):     at com.damodar.android.jdbc2.HelloAndroidJDBC2Activity.tryJDBC(HelloAndroidJDBC2Activity.java:51)
05-17 22:33:01.409: ERROR/Database(308):     at com.damodar.android.jdbc2.HelloAndroidJDBC2Activity.onCreate(HelloAndroidJDBC2Activity.java:23)

Secondly, consider a reasonable application scenario which requires usage of multiple open connections where each connection may be used under its own transaction involving multiple database calls in that transaction.  These multiple connections may be opened by concurrent threads.  Moreover, even under single threaded execution, there may be a need to execute certain SQL statements under a new transaction while a previous transaction is in progress on a separate connection.  So the idea is that these multiple concurrent transactions would be happening on separate database connections where each connection would have setAutoCommit(false) because of the transactional requirements on those connections.  The test case simulates the desirable scenario in a single threaded environment.

I think that settinig autoCommit to false on one database connection should not preclude opening a new independent connection on the same database.  This is not a problem with other JDBC drivers on Oracle, MySQL, or SQL Server databases.  Since I am working on a general framework which sits on top of 'standard' JDBC, I would like to avoid limiting the functionality of the framework or doing any special coding for SQLite on Android, if possible.  Besides, the application scenario of concurrent transactions I described above would not be very unusual in general.  So supporting that would be nice.

Thanks,

-- Damodar

Jim Redman

unread,
May 17, 2012, 7:25:45 PM5/17/12
to sqld...@googlegroups.com
Damodar,

I agree with you and your use case and your goals, however, the
limitation is not in SQLDroid but, in the underlying Android database calls.

That is, as far as I know, it's not possible to do what you want using
the raw android.database packages, and that's what SQLDroid has to work
with.

I would actually expect that even with the setAutoCommit's the database
is going to fail (probably with the same exception) at some future point
- probably writing or writing while reading.

I'd be happy for someone to prove me wrong and demonstrate that you can
have multiple connections to the same database (on different threads?)
within Android but the SQLite implementation embedded in the OS seems to
have some very strict locking multi-use restrictions. Without some more
pointers in that direction, I really don't have any idea what SQLDroid
can do to help.

Jim

On 05/17/2012 05:07 PM, Damodar Periwal wrote:
> Jim,
>
> *05-17 22:33:01.167: INFO/Database(308): sqlite returned: error code =
> 5, msg = database is locked*
> *05-17 22:33:01.167: ERROR/Database(308): CREATE TABLE android_metadata
> failed
> 05-17 22:33:01.409: ERROR/Database(308): Failed to setLocale() when
> constructing, closing the database*
> android.database.sqlite.__SQLiteDatabase.openDatabase(__dbQname,
> null, android.database.sqlite.__SQLiteDatabase.CREATE_IF___NECESSARY
> | android.database.sqlite.__SQLiteDatabase.OPEN_READWRITE)__;
> DriverManager.getConnection(__jdbcURL);
> System.out.println("After getting connection...1");
>
> conn1.setAutoCommit(true); // This statement does not
> create any problem in subsequent creation of a new connection
> conn1.setAutoCommit(false); // Problem statement
> conn1.setAutoCommit(true); // However, this
> statement sets
> things correct again
>
> Connection conn2 =
> DriverManager.getConnection(__jdbcURL);
> // succeeds
> System.out.println("After getting connection...2");
>
> However, if a call to getConnection() is immediately preceded with
> conn1.setAutoCommit(*false*), the getConnection() fails with the
> same
>
> error (error code = 5, msg = database is locked):
>
> Connection conn1 =
> DriverManager.getConnection(__jdbcURL);
> System.out.println("After getting connection...1");
>
> conn1.setAutoCommit(false); // This statement somehow
> causes the problem in subsequent creation of a connection.
>
> Connection conn2 =
> DriverManager.getConnection(__jdbcURL);
> // fails
> System.out.println("After getting connection...2");
>
> Thanks,
>
> -- Damodar
>
> On Wed, May 16, 2012 at 5:01 PM, Jim Redman
> <jre...@ergotech.com <mailto:jre...@ergotech.com>
> /DriverManager.getConnection()____/ on line 43 succeeds
> but the
> call to
> /DriverManager.getConnection()____/ on line 48 fails. I am
>
> providing the
>
> message log below.
>
> Thanks,
>
> -- Damodar Periwal
> 408-410-9088 <tel:408-410-9088> <tel:408-410-9088
> <tel:408-410-9088>>
>
>
>
> 05-16 23:30:00.528: INFO/System.out(448): database path =
>
> /data/data/com.damodar.____android.jdbc2/databases/test.____db, new
>
> database
> created = true
> 05-16 23:30:00.547: INFO/SQLDroid(448): new sqlite jdbc
> from url
> 'jdbc:sqldroid:/data/data/com.____damodar.android.jdbc2/____databases/test.db',
>
> '{}'
> 05-16 23:30:00.547: INFO/SQlDRoid(448): opening database
>
> /data/data/com.damodar.____android.jdbc2/databases/test.____db
>
> 05-16 23:30:00.567: INFO/System.out(448): After getting
> connection...1
> 05-16 23:30:00.567: INFO/SQLDroid(448): new sqlite jdbc
> from url
> 'jdbc:sqldroid:/data/data/com.____damodar.android.jdbc2/____databases/test.db',
>
> '{}'
> 05-16 23:30:00.567: INFO/SQlDRoid(448): opening database
>
> /data/data/com.damodar.____android.jdbc2/databases/test.____db
>
> 05-16 23:30:00.579: INFO/System.out(448): After getting
> connection...2
> 05-16 23:30:00.587: INFO/SQLDroid(448): new sqlite jdbc
> from url
> 'jdbc:sqldroid:/data/data/com.____damodar.android.jdbc2/____databases/test.db',
>
> '{}'
> 05-16 23:30:00.587: INFO/SQlDRoid(448): opening database
>
> /data/data/com.damodar.____android.jdbc2/databases/test.____db
>
> 05-16 23:30:02.452: INFO/Database(448): sqlite returned:
> error
> code = 5,
> msg = database is locked
> 05-16 23:30:02.452: ERROR/Database(448): CREATE TABLE
> android_metadata
> failed
> 05-16 23:30:02.458: ERROR/Database(448): Failed to
> setLocale() when
> constructing, closing the database
> 05-16 23:30:02.458: ERROR/Database(448):
> android.database.sqlite.____SQLiteException: database is
> locked
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
> android.database.sqlite.____SQLiteDatabase.native_____setLocale(Native
>
> Method)
> 05-16 23:30:02.458: ERROR/Database(448): at
>
> android.database.sqlite.____SQLiteDatabase.setLocale(____SQLiteDatabase.java:1950)
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
> android.database.sqlite.____SQLiteDatabase.<init>(____SQLiteDatabase.java:1818)
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
> android.database.sqlite.____SQLiteDatabase.openDatabase(____SQLiteDatabase.java:817)
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
> org.sqldroid.SQLiteDatabase.<____init>(SQLiteDatabase.java:80)
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
> org.sqldroid.____SQLDroidConnection.<init>(____SQLDroidConnection.java:80)
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
> org.sqldroid.SQLDroidDriver.____connect(SQLDroidDriver.java:____45)
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
> java.sql.DriverManager.____getConnection(DriverManager.____java:191)
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
> java.sql.DriverManager.____getConnection(DriverManager.____java:154)
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
> com.damodar.android.jdbc2.____HelloAndroidJDBC2Activity.____tryJDBC(____HelloAndroidJDBC2Activity.____java:48)
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
> com.damodar.android.jdbc2.____HelloAndroidJDBC2Activity.____onCreate(____HelloAndroidJDBC2Activity.____java:23)
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
> android.app.Instrumentation.____callActivityOnCreate(____Instrumentation.java:1047)
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
> android.app.ActivityThread.____performLaunchActivity(____ActivityThread.java:2627)
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
> android.app.ActivityThread.____handleLaunchActivity(____ActivityThread.java:2679)
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
> android.app.ActivityThread.____access$2300(ActivityThread.____java:125)
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
> android.app.ActivityThread$H.____handleMessage(ActivityThread.____java:2033)
>
> 05-16 23:30:02.458: ERROR/Database(448): at
> android.os.Handler.____dispatchMessage(Handler.java:____99)
>
> 05-16 23:30:02.458: ERROR/Database(448): at
> android.os.Looper.loop(Looper.____java:123)
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
> android.app.ActivityThread.____main(ActivityThread.java:4627)
>
> 05-16 23:30:02.458: ERROR/Database(448): at
> java.lang.reflect.Method.____invokeNative(Native Method)
>
> 05-16 23:30:02.458: ERROR/Database(448): at
> java.lang.reflect.Method.____invoke(Method.java:521)
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
> com.android.internal.os.____ZygoteInit$____MethodAndArgsCaller.run(____ZygoteInit.java:868)
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
> com.android.internal.os.____ZygoteInit.main(ZygoteInit.____java:626)
>
> 05-16 23:30:02.458: ERROR/Database(448): at
> dalvik.system.NativeStart.____main(Native Method)
>
> 05-16 23:30:02.468: INFO/System.out(448): JDBC Error
> Unable to Chain
> SQLException database is locked
> 05-16 23:30:02.488: ERROR/JDBC(448): Error
> 05-16 23:30:02.488: ERROR/JDBC(448): java.sql.SQLException:
> Unable to
> Chain SQLException database is locked
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
> org.sqldroid.____SQLDroidConnection.____chainException(____SQLDroidConnection.java:117)
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
> org.sqldroid.SQLiteDatabase.<____init>(SQLiteDatabase.java:94)
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
> org.sqldroid.____SQLDroidConnection.<init>(____SQLDroidConnection.java:80)
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
> org.sqldroid.SQLDroidDriver.____connect(SQLDroidDriver.java:____45)
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
> java.sql.DriverManager.____getConnection(DriverManager.____java:191)
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
> java.sql.DriverManager.____getConnection(DriverManager.____java:154)
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
> com.damodar.android.jdbc2.____HelloAndroidJDBC2Activity.____tryJDBC(____HelloAndroidJDBC2Activity.____java:48)
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
> com.damodar.android.jdbc2.____HelloAndroidJDBC2Activity.____onCreate(____HelloAndroidJDBC2Activity.____java:23)
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
> android.app.Instrumentation.____callActivityOnCreate(____Instrumentation.java:1047)
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
> android.app.ActivityThread.____performLaunchActivity(____ActivityThread.java:2627)
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
> android.app.ActivityThread.____handleLaunchActivity(____ActivityThread.java:2679)
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
> android.app.ActivityThread.____access$2300(ActivityThread.____java:125)
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
> android.app.ActivityThread$H.____handleMessage(ActivityThread.____java:2033)
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> android.os.Handler.____dispatchMessage(Handler.java:____99)
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> android.os.Looper.loop(Looper.____java:123)
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
> android.app.ActivityThread.____main(ActivityThread.java:4627)
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> java.lang.reflect.Method.____invokeNative(Native Method)
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> java.lang.reflect.Method.____invoke(Method.java:521)
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
> com.android.internal.os.____ZygoteInit$____MethodAndArgsCaller.run(____ZygoteInit.java:868)
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
> com.android.internal.os.____ZygoteInit.main(ZygoteInit.____java:626)
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> dalvik.system.NativeStart.____main(Native Method)
>
> 05-16 23:30:02.758: INFO/ActivityManager(73): Displayed
> activity
>
> com.damodar.android.jdbc2/.____HelloAndroidJDBC2Activity: 3420 ms
>
> (total
> 3420 ms)
>
>
> --
> Jim Redman
> (505) 662 5156 x85 <tel:%28505%29%20662%205156%20x85>
> <tel:%28505%29%20662%205156%__20x85>

Damodar Periwal

unread,
May 17, 2012, 7:30:51 PM5/17/12
to sqld...@googlegroups.com
I tried to avoid using Android DatabaseHelper and instead use getConnection() directly to create the database but I ran into the following problem (error code = 14, msg = cannot open file at source line 25467):

05-17 23:21:06.208: INFO/ActivityManager(86): Start proc com.damodar.android.jdbc2 for activity com.damodar.android.jdbc2/.HelloAndroidJDBC2Activity: pid=462 uid=10036 gids={}
05-17 23:21:07.128: INFO/SQLDroid(462): new sqlite jdbc from url 'jdbc:sqldroid:/data/data/com.damodar.android.jdbc2/databases/test.db', '{}'
05-17 23:21:07.128: INFO/SQlDRoid(462): opening database /data/data/com.damodar.android.jdbc2/databases/test.db
05-17 23:21:07.128: INFO/Database(462): sqlite returned: error code = 14, msg = cannot open file at source line 25467
05-17 23:21:07.128: ERROR/Database(462): sqlite3_open_v2("/data/data/com.damodar.android.jdbc2/databases/test.db", &handle, 6, NULL) failed
05-17 23:21:07.138: INFO/System.out(462): JDBC Error Unable to Chain SQLException unable to open database file
05-17 23:21:07.168: ERROR/JDBC(462): Error
05-17 23:21:07.168: ERROR/JDBC(462): java.sql.SQLException: Unable to Chain SQLException unable to open database file
05-17 23:21:07.168: ERROR/JDBC(462):     at org.sqldroid.SQLDroidConnection.chainException(SQLDroidConnection.java:117)
05-17 23:21:07.168: ERROR/JDBC(462):     at org.sqldroid.SQLiteDatabase.<init>(SQLiteDatabase.java:94)
05-17 23:21:07.168: ERROR/JDBC(462):     at org.sqldroid.SQLDroidConnection.<init>(SQLDroidConnection.java:80)
05-17 23:21:07.168: ERROR/JDBC(462):     at org.sqldroid.SQLDroidDriver.connect(SQLDroidDriver.java:45)
05-17 23:21:07.168: ERROR/JDBC(462):     at java.sql.DriverManager.getConnection(DriverManager.java:191)
05-17 23:21:07.168: ERROR/JDBC(462):     at java.sql.DriverManager.getConnection(DriverManager.java:154)
05-17 23:21:07.168: ERROR/JDBC(462):     at com.damodar.android.jdbc2.HelloAndroidJDBC2Activity.tryJDBC(HelloAndroidJDBC2Activity.java:39)
05-17 23:21:07.168: ERROR/JDBC(462):     at com.damodar.android.jdbc2.HelloAndroidJDBC2Activity.onCreate(HelloAndroidJDBC2Activity.java:23)
05-17 23:21:07.168: ERROR/JDBC(462):     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1047)
05-17 23:21:07.168: ERROR/JDBC(462):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2627)
05-17 23:21:07.168: ERROR/JDBC(462):     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2679)
05-17 23:21:07.168: ERROR/JDBC(462):     at android.app.ActivityThread.access$2300(ActivityThread.java:125)
05-17 23:21:07.168: ERROR/JDBC(462):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2033)
05-17 23:21:07.168: ERROR/JDBC(462):     at android.os.Handler.dispatchMessage(Handler.java:99)
05-17 23:21:07.168: ERROR/JDBC(462):     at android.os.Looper.loop(Looper.java:123)
05-17 23:21:07.168: ERROR/JDBC(462):     at android.app.ActivityThread.main(ActivityThread.java:4627)
05-17 23:21:07.168: ERROR/JDBC(462):     at java.lang.reflect.Method.invokeNative(Native Method)
05-17 23:21:07.168: ERROR/JDBC(462):     at java.lang.reflect.Method.invoke(Method.java:521)
05-17 23:21:07.168: ERROR/JDBC(462):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:868)
05-17 23:21:07.168: ERROR/JDBC(462):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:626)
05-17 23:21:07.168: ERROR/JDBC(462):     at dalvik.system.NativeStart.main(Native Method)

Thanks,

-- Damodar

Damodar Periwal

unread,
May 17, 2012, 7:44:42 PM5/17/12
to sqld...@googlegroups.com
Jim,

Interestingly, I ran the same program with the 'inbuilt' but unsupported sqlite JDBC driver (SQLite.JDBCDriver) on Android 2.2.  

            String jdbcURL = "jdbc:sqlite:" + databasePath;          
            Class.forName("SQLite.JDBCDriver");

It ran fine without running into any issues associated with setAutoCommit(false).  This indicates that the problem may be at the JDBC driver level because both implementations are perhaps using the same underlying Android database layer.

Thanks,

-- Damodar

Jim Redman

unread,
May 17, 2012, 7:54:46 PM5/17/12
to sqld...@googlegroups.com, Damodar Periwal
Damodar,

Are the connections independent? Or do that do what I proposed and just
return the same connection to the database?

Jim

On 05/17/2012 05:44 PM, Damodar Periwal wrote:
> Jim,
>
> DriverManager.getConnection(__jdbcURL);
> Connection conn11 =
> DriverManager.getConnection(__jdbcURL);
> // ok
> Connection conn12 =
> DriverManager.getConnection(__jdbcURL);
> // ok
> conn10.setAutoCommit(true);
> Connection conn13 =
> DriverManager.getConnection(__jdbcURL);
> // ok
> conn13.setAutoCommit(true);
> Connection conn14 =
> DriverManager.getConnection(__jdbcURL);
> // ok
> conn11.setAutoCommit(true);
> Connection conn15 =
> DriverManager.getConnection(__jdbcURL);
> // ok
> conn12.setAutoCommit(false);
> Connection conn16 =
> DriverManager.getConnection(__jdbcURL);
> // NOT ok
>
> I don't know if the following stack trace mentioning
> android-metadata or
> setLocale() can shed some extra light:
>
> 05-17 22:32:59.638: INFO/SQlDRoid(308): opening database
> /data/data/com.damodar.__android.jdbc2/databases/test.__db
> *05-17 22:33:01.167: INFO/Database(308): sqlite returned: error
> code =
> 5, msg = database is locked*
> *05-17 22:33:01.167: ERROR/Database(308): CREATE TABLE
> android_metadata
>
> failed
> 05-17 22:33:01.409: ERROR/Database(308): Failed to setLocale() when
> constructing, closing the database*
>
> 05-17 22:33:01.409: ERROR/Database(308):
> android.database.sqlite.__SQLiteException: database is locked
> 05-17 22:33:01.409: ERROR/Database(308): at
> android.database.sqlite.__SQLiteDatabase.native___setLocale(Native
> Method)
> 05-17 22:33:01.409: ERROR/Database(308): at
> android.database.sqlite.__SQLiteDatabase.setLocale(__SQLiteDatabase.java:1950)
> 05-17 22:33:01.409: ERROR/Database(308): at
> android.database.sqlite.__SQLiteDatabase.<init>(__SQLiteDatabase.java:1818)
> 05-17 22:33:01.409: ERROR/Database(308): at
> android.database.sqlite.__SQLiteDatabase.openDatabase(__SQLiteDatabase.java:817)
> 05-17 22:33:01.409: ERROR/Database(308): at
> org.sqldroid.SQLiteDatabase.<__init>(SQLiteDatabase.java:80)
> 05-17 22:33:01.409: ERROR/Database(308): at
> org.sqldroid.__SQLDroidConnection.<init>(__SQLDroidConnection.java:80)
> 05-17 22:33:01.409: ERROR/Database(308): at
> org.sqldroid.SQLDroidDriver.__connect(SQLDroidDriver.java:__45)
> 05-17 22:33:01.409: ERROR/Database(308): at
> java.sql.DriverManager.__getConnection(DriverManager.__java:191)
> 05-17 22:33:01.409: ERROR/Database(308): at
> java.sql.DriverManager.__getConnection(DriverManager.__java:154)
> 05-17 22:33:01.409: ERROR/Database(308): at
> com.damodar.android.jdbc2.__HelloAndroidJDBC2Activity.__tryJDBC(__HelloAndroidJDBC2Activity.__java:51)
> 05-17 22:33:01.409: ERROR/Database(308): at
> com.damodar.android.jdbc2.__HelloAndroidJDBC2Activity.__onCreate(__HelloAndroidJDBC2Activity.__java:23)
> <mailto:jre...@ergotech.com <mailto:jre...@ergotech.com>>> wrote:
>
> Damodar,
>
> How accurate a representation of what you want to do is the
> test?
>
> There are a couple of fairly easy solutions to the test.
>
> The fundamental problem with the test is that calling
> "getConnection" repeatedly cause multiple calls to
>
> new SQLiteDatabase(dbQname, timeout);
>
> and so to:
>
>
> sqliteDatabase =
>
> android.database.sqlite.____SQLiteDatabase.openDatabase(____dbQname,
> null,
> android.database.sqlite.____SQLiteDatabase.CREATE_IF_____NECESSARY
> |
> android.database.sqlite.____SQLiteDatabase.OPEN_READWRITE)____;
> DriverManager.getConnection(____jdbcURL);
>
> System.out.println("After getting
> connection...1");
>
> conn1.setAutoCommit(true); // This
> statement does not
> create any problem in subsequent creation of a new
> connection
> conn1.setAutoCommit(false); // Problem
> statement
> conn1.setAutoCommit(true); // However, this
> statement sets
> things correct again
>
> Connection conn2 =
> DriverManager.getConnection(____jdbcURL);
>
> // succeeds
> System.out.println("After getting
> connection...2");
>
> However, if a call to getConnection() is immediately
> preceded with
> conn1.setAutoCommit(*false*), the getConnection() fails
> with the
> same
>
> error (error code = 5, msg = database is locked):
>
> Connection conn1 =
> DriverManager.getConnection(____jdbcURL);
>
> System.out.println("After getting
> connection...1");
>
> conn1.setAutoCommit(false); // This
> statement somehow
> causes the problem in subsequent creation of a connection.
>
> Connection conn2 =
> DriverManager.getConnection(____jdbcURL);
>
> // fails
> System.out.println("After getting
> connection...2");
>
> Thanks,
>
> -- Damodar
>
> On Wed, May 16, 2012 at 5:01 PM, Jim Redman
> <jre...@ergotech.com <mailto:jre...@ergotech.com>
> <mailto:jre...@ergotech.com <mailto:jre...@ergotech.com>>
> <mailto:jre...@ergotech.com <mailto:jre...@ergotech.com>
> <mailto:jre...@ergotech.com <mailto:jre...@ergotech.com>>>__>
> /DriverManager.getConnection()______/ on line 43
> succeeds
> but the
> call to
> /DriverManager.getConnection()______/ on line 48
> fails. I am
>
>
> providing the
>
> message log below.
>
> Thanks,
>
> -- Damodar Periwal
> 408-410-9088 <tel:408-410-9088> <tel:408-410-9088
> <tel:408-410-9088>> <tel:408-410-9088 <tel:408-410-9088>
>
> <tel:408-410-9088 <tel:408-410-9088>>>
>
>
>
> 05-16 23:30:00.528: INFO/System.out(448):
> database path =
>
>
> /data/data/com.damodar.______android.jdbc2/databases/test.______db, new
>
>
> database
> created = true
> 05-16 23:30:00.547: INFO/SQLDroid(448): new
> sqlite jdbc
> from url
> 'jdbc:sqldroid:/data/data/com.______damodar.android.jdbc2/______databases/test.db',
>
>
> '{}'
> 05-16 23:30:00.547: INFO/SQlDRoid(448): opening
> database
>
>
> /data/data/com.damodar.______android.jdbc2/databases/test.______db
>
>
> 05-16 23:30:00.567: INFO/System.out(448): After
> getting
> connection...1
> 05-16 23:30:00.567: INFO/SQLDroid(448): new
> sqlite jdbc
> from url
> 'jdbc:sqldroid:/data/data/com.______damodar.android.jdbc2/______databases/test.db',
>
>
> '{}'
> 05-16 23:30:00.567: INFO/SQlDRoid(448): opening
> database
>
>
> /data/data/com.damodar.______android.jdbc2/databases/test.______db
>
>
> 05-16 23:30:00.579: INFO/System.out(448): After
> getting
> connection...2
> 05-16 23:30:00.587: INFO/SQLDroid(448): new
> sqlite jdbc
> from url
> 'jdbc:sqldroid:/data/data/com.______damodar.android.jdbc2/______databases/test.db',
>
>
> '{}'
> 05-16 23:30:00.587: INFO/SQlDRoid(448): opening
> database
>
>
> /data/data/com.damodar.______android.jdbc2/databases/test.______db
>
>
> 05-16 23:30:02.452: INFO/Database(448): sqlite
> returned:
> error
> code = 5,
> msg = database is locked
> 05-16 23:30:02.452: ERROR/Database(448): CREATE
> TABLE
> android_metadata
> failed
> 05-16 23:30:02.458: ERROR/Database(448): Failed to
> setLocale() when
> constructing, closing the database
> 05-16 23:30:02.458: ERROR/Database(448):
> android.database.sqlite.______SQLiteException:
> database is
>
> locked
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
>
> android.database.sqlite.______SQLiteDatabase.native_______setLocale(Native
>
>
> Method)
> 05-16 23:30:02.458: ERROR/Database(448): at
>
>
> android.database.sqlite.______SQLiteDatabase.setLocale(______SQLiteDatabase.java:1950)
>
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
>
> android.database.sqlite.______SQLiteDatabase.<init>(______SQLiteDatabase.java:1818)
>
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
>
> android.database.sqlite.______SQLiteDatabase.openDatabase(______SQLiteDatabase.java:817)
>
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
>
> org.sqldroid.SQLiteDatabase.<______init>(SQLiteDatabase.java:__80)
>
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
>
> org.sqldroid.______SQLDroidConnection.<init>(______SQLDroidConnection.java:80)
>
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
>
> org.sqldroid.SQLDroidDriver.______connect(SQLDroidDriver.java:______45)
>
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
>
> java.sql.DriverManager.______getConnection(DriverManager.______java:191)
>
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
>
> java.sql.DriverManager.______getConnection(DriverManager.______java:154)
>
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
>
> com.damodar.android.jdbc2.______HelloAndroidJDBC2Activity.______tryJDBC(______HelloAndroidJDBC2Activity.______java:48)
>
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
>
> com.damodar.android.jdbc2.______HelloAndroidJDBC2Activity.______onCreate(______HelloAndroidJDBC2Activity.______java:23)
>
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
>
> android.app.Instrumentation.______callActivityOnCreate(______Instrumentation.java:1047)
>
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
>
> android.app.ActivityThread.______performLaunchActivity(______ActivityThread.java:2627)
>
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
>
> android.app.ActivityThread.______handleLaunchActivity(______ActivityThread.java:2679)
>
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
>
> android.app.ActivityThread.______access$2300(ActivityThread.______java:125)
>
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
>
> android.app.ActivityThread$H.______handleMessage(__ActivityThread.____java:2033)
>
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
> android.os.Handler.______dispatchMessage(Handler.java:______99)
>
>
> 05-16 23:30:02.458: ERROR/Database(448): at
> android.os.Looper.loop(Looper.______java:123)
>
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
>
> android.app.ActivityThread.______main(ActivityThread.java:__4627)
>
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
> java.lang.reflect.Method.______invokeNative(Native Method)
>
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
> java.lang.reflect.Method.______invoke(Method.java:521)
>
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
>
> com.android.internal.os.______ZygoteInit$______MethodAndArgsCaller.run(______ZygoteInit.java:868)
>
>
> 05-16 23:30:02.458: ERROR/Database(448): at
>
>
> com.android.internal.os.______ZygoteInit.main(ZygoteInit.______java:626)
>
>
> 05-16 23:30:02.458: ERROR/Database(448): at
> dalvik.system.NativeStart.______main(Native Method)
>
>
> 05-16 23:30:02.468: INFO/System.out(448): JDBC Error
> Unable to Chain
> SQLException database is locked
> 05-16 23:30:02.488: ERROR/JDBC(448): Error
> 05-16 23:30:02.488: ERROR/JDBC(448):
> java.sql.SQLException:
> Unable to
> Chain SQLException database is locked
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
> org.sqldroid.______SQLDroidConnection.______chainException(______SQLDroidConnection.java:117)
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
> org.sqldroid.SQLiteDatabase.<______init>(SQLiteDatabase.java:__94)
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
> org.sqldroid.______SQLDroidConnection.<init>(______SQLDroidConnection.java:80)
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
> org.sqldroid.SQLDroidDriver.______connect(SQLDroidDriver.java:______45)
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
> java.sql.DriverManager.______getConnection(DriverManager.______java:191)
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
> java.sql.DriverManager.______getConnection(DriverManager.______java:154)
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
> com.damodar.android.jdbc2.______HelloAndroidJDBC2Activity.______tryJDBC(______HelloAndroidJDBC2Activity.______java:48)
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
> com.damodar.android.jdbc2.______HelloAndroidJDBC2Activity.______onCreate(______HelloAndroidJDBC2Activity.______java:23)
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
> android.app.Instrumentation.______callActivityOnCreate(______Instrumentation.java:1047)
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
> android.app.ActivityThread.______performLaunchActivity(______ActivityThread.java:2627)
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
> android.app.ActivityThread.______handleLaunchActivity(______ActivityThread.java:2679)
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
> android.app.ActivityThread.______access$2300(ActivityThread.______java:125)
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
> android.app.ActivityThread$H.______handleMessage(__ActivityThread.____java:2033)
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
> android.os.Handler.______dispatchMessage(Handler.java:______99)
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> android.os.Looper.loop(Looper.______java:123)
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
> android.app.ActivityThread.______main(ActivityThread.java:__4627)
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
> java.lang.reflect.Method.______invokeNative(Native Method)
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
> java.lang.reflect.Method.______invoke(Method.java:521)
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
> com.android.internal.os.______ZygoteInit$______MethodAndArgsCaller.run(______ZygoteInit.java:868)
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
> com.android.internal.os.______ZygoteInit.main(ZygoteInit.______java:626)
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
> dalvik.system.NativeStart.______main(Native Method)
>
>
> 05-16 23:30:02.758: INFO/ActivityManager(73):
> Displayed
> activity
>
>
> com.damodar.android.jdbc2/.______HelloAndroidJDBC2Activity:
> 3420 ms
>
>
> (total
> 3420 ms)
>
>
> --
> Jim Redman
> (505) 662 5156 x85 <tel:%28505%29%20662%205156%20x85>
> <tel:%28505%29%20662%205156%__20x85>
> <tel:%28505%29%20662%205156%____20x85>

Damodar Periwal

unread,
May 17, 2012, 8:03:29 PM5/17/12
to sqld...@googlegroups.com
I did toString() on two separate connections, and based on the output, they seem to be different:

Code:

            String jdbcURL = "jdbc:sqlite:" + databasePath;          
            Class.forName("SQLite.JDBCDriver");

            Connection conn10 = DriverManager.getConnection(jdbcURL);           
            Connection conn11 = DriverManager.getConnection(jdbcURL);
            System.out.println("Conn10: " + conn10.toString());
            System.out.println("Conn11: " + conn11.toString());

Output:

        05-17 23:58:02.318: INFO/System.out(1437): Conn10: SQLite.JDBC2y.JDBCConnection@43e457e8
        05-17 23:58:02.318: INFO/System.out(1437): Conn11: SQLite.JDBC2y.JDBCConnection@43e45bd8

I don't know anything about the internal implementation of SQLite.JDBCDriver.

-- Damodar

Jim Redman

unread,
May 17, 2012, 11:12:32 PM5/17/12
to sqld...@googlegroups.com, Damodar Periwal
Damodar,

Interestingly, but not in any way useful, the JDBC Driver is not based
on the android.database code at all.

The Database code used in SQLite.JDBC2y.JDBCConnection (actually
DatabaseX, which is small sublcass of Database):

http://code.google.com/searchframe#r07P-bJdvBY/trunk/Android%20Java%20Framework/src/SQLite/Database.java

Uses a direct native call to open the database

private native void _open(String filename, int mode)
throws SQLite.Exception;

I didn't dig into the native code.

So it seems that google have two parallel and unrelated paths to database.

If I get a chance I'll look again, but I'm not optimistic that any of
these problems are solvable outside of the Android implementation.

Interestingly, the code you provided ran flawlessly against the
compatibility layer on Linux - a layer which is more like the Android
JDBC2y than the android.database code.

Finally, I noticed this in the JDBC2y result set:

public java.sql.Blob getBlob(String columnIndex) throws SQLException {
throw new SQLException("not supported");
}

so it seems SQLDroid is still the only JDBC driver that supports Blobs.

Jim

On 05/17/2012 06:03 PM, Damodar Periwal wrote:
> I did toString() on two separate connections, and based on the output,
> they seem to be different:
>
> *Code:*
>
> String jdbcURL = "jdbc:sqlite:" + databasePath;
> Class.forName("SQLite.JDBCDriver");
>
> Connection conn10 = DriverManager.getConnection(jdbcURL);
> Connection conn11 = DriverManager.getConnection(jdbcURL);
> System.out.println("Conn10: " + conn10.toString());
> System.out.println("Conn11: " + conn11.toString());
>
> *Output:*
>
> 05-17 23:58:02.318: INFO/System.out(1437): Conn10:
> SQLite.JDBC2y.JDBCConnection@43e457e8
> 05-17 23:58:02.318: INFO/System.out(1437): Conn11:
> SQLite.JDBC2y.JDBCConnection@43e45bd8
>
> I don't know anything about the internal implementation of
> SQLite.JDBCDriver.
>
> -- Damodar
>
> On Thu, May 17, 2012 at 4:54 PM, Jim Redman <jre...@ergotech.com
> <mailto:jre...@ergotech.com>> wrote:
>
> Damodar,
>
> Are the connections independent? Or do that do what I proposed and
> just return the same connection to the database?
>
> Jim
>
>
> On 05/17/2012 05:44 PM, Damodar Periwal wrote:
>
> Jim,
>
> Interestingly, I ran the same program with the 'inbuilt' but
> unsupported
> sqlite JDBC driver (SQLite.JDBCDriver) on Android 2.2.
>
> String jdbcURL = "jdbc:sqlite:" + databasePath;
> Class.forName("SQLite.__JDBCDriver");
>
> It ran fine without running into any issues associated with
> setAutoCommit(false). This indicates that the problem may be at the
> JDBC driver level because both implementations are perhaps using the
> same underlying Android database layer.
>
> Thanks,
>
> -- Damodar
>
> On Thu, May 17, 2012 at 4:25 PM, Jim Redman
> <jre...@ergotech.com <mailto:jre...@ergotech.com>
> <mailto:jre...@ergotech.com <mailto:jre...@ergotech.com>>> wrote:
>
> Damodar,
>
> DriverManager.getConnection(____jdbcURL);
> Connection conn11 =
> DriverManager.getConnection(____jdbcURL);
>
> // ok
> Connection conn12 =
> DriverManager.getConnection(____jdbcURL);
>
> // ok
> conn10.setAutoCommit(true);
> Connection conn13 =
> DriverManager.getConnection(____jdbcURL);
>
> // ok
> conn13.setAutoCommit(true);
> Connection conn14 =
> DriverManager.getConnection(____jdbcURL);
>
> // ok
> conn11.setAutoCommit(true);
> Connection conn15 =
> DriverManager.getConnection(____jdbcURL);
>
> // ok
> conn12.setAutoCommit(false);
> Connection conn16 =
> DriverManager.getConnection(____jdbcURL);
>
> // NOT ok
>
> I don't know if the following stack trace mentioning
> android-metadata or
> setLocale() can shed some extra light:
>
> 05-17 22:32:59.638: INFO/SQlDRoid(308): opening database
>
> /data/data/com.damodar.____android.jdbc2/databases/test.____db
>
> *05-17 22:33:01.167: INFO/Database(308): sqlite
> returned: error
> code =
> 5, msg = database is locked*
> *05-17 22:33:01.167: ERROR/Database(308): CREATE TABLE
> android_metadata
>
> failed
> 05-17 22:33:01.409: ERROR/Database(308): Failed to
> setLocale() when
> constructing, closing the database*
>
> 05-17 22:33:01.409: ERROR/Database(308):
> android.database.sqlite.____SQLiteException: database is
> locked
>
> 05-17 22:33:01.409: ERROR/Database(308): at
>
> android.database.sqlite.____SQLiteDatabase.native_____setLocale(Native
>
> Method)
> 05-17 22:33:01.409: ERROR/Database(308): at
>
> android.database.sqlite.____SQLiteDatabase.setLocale(____SQLiteDatabase.java:1950)
>
> 05-17 22:33:01.409: ERROR/Database(308): at
>
> android.database.sqlite.____SQLiteDatabase.<init>(____SQLiteDatabase.java:1818)
>
> 05-17 22:33:01.409: ERROR/Database(308): at
>
> android.database.sqlite.____SQLiteDatabase.openDatabase(____SQLiteDatabase.java:817)
>
> 05-17 22:33:01.409: ERROR/Database(308): at
>
> org.sqldroid.SQLiteDatabase.<____init>(SQLiteDatabase.java:80)
>
> 05-17 22:33:01.409: ERROR/Database(308): at
>
> org.sqldroid.____SQLDroidConnection.<init>(____SQLDroidConnection.java:80)
>
> 05-17 22:33:01.409: ERROR/Database(308): at
>
> org.sqldroid.SQLDroidDriver.____connect(SQLDroidDriver.java:____45)
>
> 05-17 22:33:01.409: ERROR/Database(308): at
>
> java.sql.DriverManager.____getConnection(DriverManager.____java:191)
>
> 05-17 22:33:01.409: ERROR/Database(308): at
>
> java.sql.DriverManager.____getConnection(DriverManager.____java:154)
>
> 05-17 22:33:01.409: ERROR/Database(308): at
>
> com.damodar.android.jdbc2.____HelloAndroidJDBC2Activity.____tryJDBC(____HelloAndroidJDBC2Activity.____java:51)
>
> 05-17 22:33:01.409: ERROR/Database(308): at
>
> com.damodar.android.jdbc2.____HelloAndroidJDBC2Activity.____onCreate(____HelloAndroidJDBC2Activity.____java:23)
> <mailto:jre...@ergotech.com <mailto:jre...@ergotech.com>
> <mailto:jre...@ergotech.com <mailto:jre...@ergotech.com>>>__>
> wrote:
>
> Damodar,
>
> How accurate a representation of what you want to do
> is the
> test?
>
> There are a couple of fairly easy solutions to the test.
>
> The fundamental problem with the test is that calling
> "getConnection" repeatedly cause multiple calls to
>
> new SQLiteDatabase(dbQname, timeout);
>
> and so to:
>
>
> sqliteDatabase =
>
>
> android.database.sqlite.______SQLiteDatabase.openDatabase(______dbQname,
> null,
>
> android.database.sqlite.______SQLiteDatabase.CREATE_IF_______NECESSARY
> |
>
> android.database.sqlite.______SQLiteDatabase.OPEN_READWRITE)______;
> DriverManager.getConnection(______jdbcURL);
>
>
> System.out.println("After getting
> connection...1");
>
> conn1.setAutoCommit(true); // This
> statement does not
> create any problem in subsequent creation of a new
> connection
> conn1.setAutoCommit(false); // Problem
> statement
> conn1.setAutoCommit(true); //
> However, this
> statement sets
> things correct again
>
> Connection conn2 =
> DriverManager.getConnection(______jdbcURL);
>
>
> // succeeds
> System.out.println("After getting
> connection...2");
>
> However, if a call to getConnection() is immediately
> preceded with
> conn1.setAutoCommit(*false*), the
> getConnection() fails
> with the
> same
>
> error (error code = 5, msg = database is locked):
>
> Connection conn1 =
> DriverManager.getConnection(______jdbcURL);
>
>
> System.out.println("After getting
> connection...1");
>
> conn1.setAutoCommit(false); // This
> statement somehow
> causes the problem in subsequent creation of a
> connection.
>
> Connection conn2 =
> DriverManager.getConnection(______jdbcURL);
>
>
> // fails
> System.out.println("After getting
> connection...2");
>
> Thanks,
>
> -- Damodar
>
> On Wed, May 16, 2012 at 5:01 PM, Jim Redman
> <jre...@ergotech.com <mailto:jre...@ergotech.com>
> <mailto:jre...@ergotech.com <mailto:jre...@ergotech.com>>
> <mailto:jre...@ergotech.com <mailto:jre...@ergotech.com>
> <mailto:jre...@ergotech.com <mailto:jre...@ergotech.com>>>
> <mailto:jre...@ergotech.com <mailto:jre...@ergotech.com>
> <mailto:jre...@ergotech.com <mailto:jre...@ergotech.com>>
> <mailto:jre...@ergotech.com <mailto:jre...@ergotech.com>
> <mailto:jre...@ergotech.com <mailto:jre...@ergotech.com>>>__>__>
> /DriverManager.getConnection()________/
> on line 43
>
> succeeds
> but the
> call to
> /DriverManager.getConnection()________/
> on line 48
>
> fails. I am
>
>
> providing the
>
> message log below.
>
> Thanks,
>
> -- Damodar Periwal
> 408-410-9088 <tel:408-410-9088> <tel:408-410-9088
> <tel:408-410-9088>> <tel:408-410-9088 <tel:408-410-9088>
> <tel:408-410-9088 <tel:408-410-9088>>> <tel:408-410-9088
> <tel:408-410-9088> <tel:408-410-9088 <tel:408-410-9088>>
>
> <tel:408-410-9088 <tel:408-410-9088> <tel:408-410-9088
> <tel:408-410-9088>>>>
>
>
>
>
> 05-16 23:30:00.528: INFO/System.out(448):
> database path =
>
>
>
> /data/data/com.damodar.________android.jdbc2/databases/test.________db, new
>
>
>
> database
> created = true
> 05-16 23:30:00.547: INFO/SQLDroid(448): new
> sqlite jdbc
> from url
> 'jdbc:sqldroid:/data/data/com.________damodar.android.jdbc2/________databases/test.db',
>
>
>
> '{}'
> 05-16 23:30:00.547: INFO/SQlDRoid(448):
> opening
> database
>
>
>
> /data/data/com.damodar.________android.jdbc2/databases/test.________db
>
>
>
> 05-16 23:30:00.567:
> INFO/System.out(448): After
> getting
> connection...1
> 05-16 23:30:00.567: INFO/SQLDroid(448): new
> sqlite jdbc
> from url
> 'jdbc:sqldroid:/data/data/com.________damodar.android.jdbc2/________databases/test.db',
>
>
>
> '{}'
> 05-16 23:30:00.567: INFO/SQlDRoid(448):
> opening
> database
>
>
>
> /data/data/com.damodar.________android.jdbc2/databases/test.________db
>
>
>
> 05-16 23:30:00.579:
> INFO/System.out(448): After
> getting
> connection...2
> 05-16 23:30:00.587: INFO/SQLDroid(448): new
> sqlite jdbc
> from url
> 'jdbc:sqldroid:/data/data/com.________damodar.android.jdbc2/________databases/test.db',
>
>
>
> '{}'
> 05-16 23:30:00.587: INFO/SQlDRoid(448):
> opening
> database
>
>
>
> /data/data/com.damodar.________android.jdbc2/databases/test.________db
>
>
>
> 05-16 23:30:02.452: INFO/Database(448):
> sqlite
> returned:
> error
> code = 5,
> msg = database is locked
> 05-16 23:30:02.452: ERROR/Database(448):
> CREATE
> TABLE
> android_metadata
> failed
> 05-16 23:30:02.458: ERROR/Database(448):
> Failed to
> setLocale() when
> constructing, closing the database
> 05-16 23:30:02.458: ERROR/Database(448):
>
> android.database.sqlite.________SQLiteException:
>
> database is
>
> locked
>
> 05-16 23:30:02.458: ERROR/Database(448):
> at
>
>
>
> android.database.sqlite.________SQLiteDatabase.native_________setLocale(Native
>
>
>
> Method)
> 05-16 23:30:02.458: ERROR/Database(448):
> at
>
>
>
> android.database.sqlite.________SQLiteDatabase.setLocale(________SQLiteDatabase.java:1950)
>
>
>
> 05-16 23:30:02.458: ERROR/Database(448):
> at
>
>
>
> android.database.sqlite.________SQLiteDatabase.<init>(________SQLiteDatabase.java:1818)
>
>
>
> 05-16 23:30:02.458: ERROR/Database(448):
> at
>
>
>
> android.database.sqlite.________SQLiteDatabase.openDatabase(________SQLiteDatabase.java:817)
>
>
>
> 05-16 23:30:02.458: ERROR/Database(448):
> at
>
>
>
> org.sqldroid.SQLiteDatabase.<________init>(SQLiteDatabase.__java:__80)
>
>
>
> 05-16 23:30:02.458: ERROR/Database(448):
> at
>
>
>
> org.sqldroid.________SQLDroidConnection.<init>(________SQLDroidConnection.java:80)
>
>
>
> 05-16 23:30:02.458: ERROR/Database(448):
> at
>
>
>
> org.sqldroid.SQLDroidDriver.________connect(SQLDroidDriver.__java:______45)
>
>
>
> 05-16 23:30:02.458: ERROR/Database(448):
> at
>
>
>
> java.sql.DriverManager.________getConnection(DriverManager.________java:191)
>
>
>
> 05-16 23:30:02.458: ERROR/Database(448):
> at
>
>
>
> java.sql.DriverManager.________getConnection(DriverManager.________java:154)
>
>
>
> 05-16 23:30:02.458: ERROR/Database(448):
> at
>
>
>
> com.damodar.android.jdbc2.________HelloAndroidJDBC2Activity.________tryJDBC(________HelloAndroidJDBC2Activity.________java:48)
>
>
>
> 05-16 23:30:02.458: ERROR/Database(448):
> at
>
>
>
> com.damodar.android.jdbc2.________HelloAndroidJDBC2Activity.________onCreate(________HelloAndroidJDBC2Activity.________java:23)
>
>
>
> 05-16 23:30:02.458: ERROR/Database(448):
> at
>
>
>
> android.app.Instrumentation.________callActivityOnCreate(________Instrumentation.java:1047)
>
>
>
> 05-16 23:30:02.458: ERROR/Database(448):
> at
>
>
>
> android.app.ActivityThread.________performLaunchActivity(________ActivityThread.java:2627)
>
>
>
> 05-16 23:30:02.458: ERROR/Database(448):
> at
>
>
>
> android.app.ActivityThread.________handleLaunchActivity(________ActivityThread.java:2679)
>
>
>
> 05-16 23:30:02.458: ERROR/Database(448):
> at
>
>
>
> android.app.ActivityThread.________access$2300(ActivityThread.________java:125)
>
>
>
> 05-16 23:30:02.458: ERROR/Database(448):
> at
>
>
>
> android.app.ActivityThread$H.________handleMessage(____ActivityThread.____java:2033)
>
>
>
> 05-16 23:30:02.458: ERROR/Database(448):
> at
>
>
> android.os.Handler.________dispatchMessage(Handler.java:________99)
>
>
>
> 05-16 23:30:02.458: ERROR/Database(448):
> at
>
> android.os.Looper.loop(Looper.________java:123)
>
>
>
> 05-16 23:30:02.458: ERROR/Database(448):
> at
>
>
>
> android.app.ActivityThread.________main(ActivityThread.java:____4627)
>
>
>
> 05-16 23:30:02.458: ERROR/Database(448):
> at
>
> java.lang.reflect.Method.________invokeNative(Native
> Method)
>
>
>
> 05-16 23:30:02.458: ERROR/Database(448):
> at
>
> java.lang.reflect.Method.________invoke(Method.java:521)
>
>
>
> 05-16 23:30:02.458: ERROR/Database(448):
> at
>
>
>
> com.android.internal.os.________ZygoteInit$________MethodAndArgsCaller.run(________ZygoteInit.java:868)
>
>
>
> 05-16 23:30:02.458: ERROR/Database(448):
> at
>
>
>
> com.android.internal.os.________ZygoteInit.main(ZygoteInit.________java:626)
>
>
>
> 05-16 23:30:02.458: ERROR/Database(448):
> at
>
> dalvik.system.NativeStart.________main(Native Method)
>
>
>
> 05-16 23:30:02.468:
> INFO/System.out(448): JDBC Error
> Unable to Chain
> SQLException database is locked
> 05-16 23:30:02.488: ERROR/JDBC(448): Error
> 05-16 23:30:02.488: ERROR/JDBC(448):
> java.sql.SQLException:
> Unable to
> Chain SQLException database is locked
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
>
> org.sqldroid.________SQLDroidConnection.________chainException(________SQLDroidConnection.java:117)
>
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
>
> org.sqldroid.SQLiteDatabase.<________init>(SQLiteDatabase.__java:__94)
>
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
>
> org.sqldroid.________SQLDroidConnection.<init>(________SQLDroidConnection.java:80)
>
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
>
> org.sqldroid.SQLDroidDriver.________connect(SQLDroidDriver.__java:______45)
>
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
>
> java.sql.DriverManager.________getConnection(DriverManager.________java:191)
>
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
>
> java.sql.DriverManager.________getConnection(DriverManager.________java:154)
>
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
>
> com.damodar.android.jdbc2.________HelloAndroidJDBC2Activity.________tryJDBC(________HelloAndroidJDBC2Activity.________java:48)
>
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
>
> com.damodar.android.jdbc2.________HelloAndroidJDBC2Activity.________onCreate(________HelloAndroidJDBC2Activity.________java:23)
>
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
>
> android.app.Instrumentation.________callActivityOnCreate(________Instrumentation.java:1047)
>
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
>
> android.app.ActivityThread.________performLaunchActivity(________ActivityThread.java:2627)
>
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
>
> android.app.ActivityThread.________handleLaunchActivity(________ActivityThread.java:2679)
>
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
>
> android.app.ActivityThread.________access$2300(ActivityThread.________java:125)
>
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
>
> android.app.ActivityThread$H.________handleMessage(____ActivityThread.____java:2033)
>
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
> android.os.Handler.________dispatchMessage(Handler.java:________99)
>
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
> android.os.Looper.loop(Looper.________java:123)
>
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
>
> android.app.ActivityThread.________main(ActivityThread.java:____4627)
>
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
> java.lang.reflect.Method.________invokeNative(Native
> Method)
>
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
> java.lang.reflect.Method.________invoke(Method.java:521)
>
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
>
> com.android.internal.os.________ZygoteInit$________MethodAndArgsCaller.run(________ZygoteInit.java:868)
>
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
>
>
> com.android.internal.os.________ZygoteInit.main(ZygoteInit.________java:626)
>
>
>
> 05-16 23:30:02.488: ERROR/JDBC(448): at
>
> dalvik.system.NativeStart.________main(Native Method)
>
>
>
> 05-16 23:30:02.758:
> INFO/ActivityManager(73):
> Displayed
> activity
>
>
>
> com.damodar.android.jdbc2/.________HelloAndroidJDBC2Activity:
>
> 3420 ms
>
>
> (total
> 3420 ms)
>
>
> --
> Jim Redman
> (505) 662 5156 x85 <tel:%28505%29%20662%205156%20x85>
> <tel:%28505%29%20662%205156%__20x85>
> <tel:%28505%29%20662%205156%____20x85>
> <tel:%28505%29%20662%205156%______20x85>

Uwe Kubosch

unread,
May 18, 2012, 3:48:35 AM5/18/12
to <sqldroid@googlegroups.com>, Damodar Periwal
Hi all!

I am pretty sure the locking limitation is on the lowest level of the native SQLite library. We have a retry/timeout feature around many calls to the same database, but if you start a transaction and hold it beyond the timeout, other connections will get an exception.

We could delay the actual connection to the database until an actual query/update transaction is started. That would allow multiple threads to connect concurrently. Multiple transactions will never be allowed, I think.


On 2012-05-18, at 06:12, Jim Redman wrote:

> Damodar,
>
> Interestingly, but not in any way useful, the JDBC Driver is not based on the android.database code at all.
>
> The Database code used in SQLite.JDBC2y.JDBCConnection (actually DatabaseX, which is small sublcass of Database):
>
> http://code.google.com/searchframe#r07P-bJdvBY/trunk/Android%20Java%20Framework/src/SQLite/Database.java
>
> Uses a direct native call to open the database
>
> private native void _open(String filename, int mode)
> throws SQLite.Exception;
>
> I didn't dig into the native code.
>
> So it seems that google have two parallel and unrelated paths to database.
>
> If I get a chance I'll look again, but I'm not optimistic that any of these problems are solvable outside of the Android implementation.
>
> Interestingly, the code you provided ran flawlessly against the compatibility layer on Linux - a layer which is more like the Android JDBC2y than the android.database code.
>
> Finally, I noticed this in the JDBC2y result set:
>
> public java.sql.Blob getBlob(String columnIndex) throws SQLException {
> throw new SQLException("not supported");
> }
>
> so it seems SQLDroid is still the only JDBC driver that supports Blobs.
>
> Jim

--
Uwe Kubosch
Systems Developer
Datek Wireless AS
u...@datek.no
http://datek.no/


Jim Redman

unread,
May 18, 2012, 12:42:16 PM5/18/12
to sqld...@googlegroups.com
Damodar/Uwe,

It's an Android bug. There's an easy fix to make Damodar's test work.
Change the open code to:

sqliteDatabase =
android.database.sqlite.SQLiteDatabase.openDatabase(dbQname, null,
android.database.sqlite.SQLiteDatabase.CREATE_IF_NECESSARY |
android.database.sqlite.SQLiteDatabase.OPEN_READWRITE |
android.database.sqlite.SQLiteDatabase.NO_LOCALIZED_COLLATORS);

And then call:

sqliteDatabase.setLocale(Locale.getDefault());

ignoring any thrown exceptions. Strangely, the "setLocale" doesn't fail
in the test case.

Threadedness.....

As far as I can tell, android.database.SQLiteDatabase is thread-safe.
The underlying sqlite is not (I assume that it's not compiled with
"SQLITE_THREADSAFE preprocessor macro set to 1" -
http://www.sqlite.org/faq.html#q6
however, the SQLiteDatabase "lock()"'s all the calls - hence the
irritating, but necessary "SQLliteLockedExceptions".

So the Java code and so SQLDroid is solid and can be used from multiple
thread although not simultaneously. Incidently, I suspect that the
JDBC2y code is not and so using that code on multiple thread risks
database corruption - something we might want to note on github.

Damodar's test upchucks in setLocale, however, it passed the "lock()"
and fails in native_setLocale

public void setLocale(Locale locale) {
lock();
try {
native_setLocale(locale.toString(), mFlags);
} finally {
unlock();
}
}

It seems that native_setLocale does some sort of locking of it's own,
because if you make it a no-op by passing:
android.database.sqlite.SQLiteDatabase.NO_LOCALIZED_COLLATORS
into SQLiteDatabase.openDatabase then the problem with Demodar's code
goes away:

I/ActivityManager( 59): Starting activity: Intent {
act=android.intent.action.MAIN cat=[android.intent.category.LAUNCHER]
flg=0x10200000 cmp=com.damodar.android.jdbc2/.HelloAndroidJDBC2Activity }
I/System.out( 549): database path =
/data/data/com.damodar.android.jdbc2/databases/test.db, new database
created = false
I/System.out( 549): After getting connection...1
I/System.out( 549): After getting connection...2
I/System.out( 549): After getting connection...3
I/System.out( 549): Prime=2
I/System.out( 549): Prime=3
I/System.out( 549): Prime=5
I/System.out( 549): Prime=7

Jim

Damodar Periwal

unread,
May 19, 2012, 2:32:56 AM5/19/12
to sqld...@googlegroups.com
Jim,

Thanks for investigating into the problem.  There are some interesting observations.  Friday and Saturday I am very busy in a conference so cannot provide much feedback.  But I do wonder how/why setLocale() and setAutoCommit(false) are conflicting with each other.

-- Damodar

Jim Redman

unread,
May 19, 2012, 9:01:30 AM5/19/12
to sqld...@googlegroups.com
Damodar,

On 05/19/2012 12:32 AM, Damodar Periwal wrote:
>But I do wonder how/why setLocale() and
> setAutoCommit(false) are conflicting with each other.

It's a good question. I assume that there must be another lock in play
somewhere related to transactions. I don't think SQLite supports the
idea of "autocommit" and so the "setAutoCommit" is implemented in terms
of starting/ending transactions.

It's also weird that "setLocale" succeeds if called after the database
is open. (Now I think about it, it may have no effect because of
NO_LOCALIZED_COLLATORS, which might be a problem).

As I say, it solve the test case. I think you will need to work with it
for a while to make sure that there are no lurking problems.

>
> Thanks for investigating into the problem. There are some interesting
> observations. Friday and Saturday I am very busy in a conference so
> cannot provide much feedback.

There are a few other changes, mainly tweaks to blobs. I'll see if I
can get get it checked in so you can work with it.

Thanks,

Jim


>
> -- Damodar
>
> On Fri, May 18, 2012 at 9:42 AM, Jim Redman <jre...@ergotech.com
> <mailto:jre...@ergotech.com>> wrote:
>
> Damodar/Uwe,
>
> It's an Android bug. There's an easy fix to make Damodar's test
> work. Change the open code to:
>
> sqliteDatabase =
> android.database.sqlite.__SQLiteDatabase.openDatabase(__dbQname,
> null, android.database.sqlite.__SQLiteDatabase.CREATE_IF___NECESSARY
> | android.database.sqlite.__SQLiteDatabase.OPEN_READWRITE |
> android.database.sqlite.__SQLiteDatabase.NO_LOCALIZED___COLLATORS);
>
> And then call:
>
> sqliteDatabase.setLocale(__Locale.getDefault());
>
> ignoring any thrown exceptions. Strangely, the "setLocale" doesn't
> fail in the test case.
>
> Threadedness.....
>
> As far as I can tell, android.database.__SQLiteDatabase is
> thread-safe. The underlying sqlite is not (I assume that it's not
> compiled with
> "SQLITE_THREADSAFE preprocessor macro set to 1" -
> http://www.sqlite.org/faq.__html#q6 <http://www.sqlite.org/faq.html#q6>
> however, the SQLiteDatabase "lock()"'s all the calls - hence the
> irritating, but necessary "SQLliteLockedExceptions".
>
> So the Java code and so SQLDroid is solid and can be used from
> multiple thread although not simultaneously. Incidently, I suspect
> that the JDBC2y code is not and so using that code on multiple
> thread risks database corruption - something we might want to note
> on github.
>
> Damodar's test upchucks in setLocale, however, it passed the
> "lock()" and fails in native_setLocale
>
> public void setLocale(Locale locale) {
> lock();
> try {
> native_setLocale(locale.__toString(), mFlags);
> } finally {
> unlock();
> }
> }
>
> It seems that native_setLocale does some sort of locking of it's
> own, because if you make it a no-op by passing:
> android.database.sqlite.__SQLiteDatabase.NO_LOCALIZED___COLLATORS
> into SQLiteDatabase.openDatabase then the problem with Demodar's
> code goes away:
>
> I/ActivityManager( 59): Starting activity: Intent {
> act=android.intent.action.MAIN
> cat=[android.intent.category.__LAUNCHER] flg=0x10200000
> cmp=com.damodar.android.jdbc2/__.HelloAndroidJDBC2Activity }
> I/System.out( 549): database path =
> /data/data/com.damodar.__android.jdbc2/databases/test.__db, new
> http://code.google.com/__searchframe#r07P-bJdvBY/trunk/__Android%20Java%20Framework/__src/SQLite/Database.java
> <http://code.google.com/searchframe#r07P-bJdvBY/trunk/Android%20Java%20Framework/src/SQLite/Database.java>
>
> Uses a direct native call to open the database
>
> private native void _open(String filename, int mode)
> throws SQLite.Exception;
>
> I didn't dig into the native code.
>
> So it seems that google have two parallel and unrelated
> paths to database.
>
> If I get a chance I'll look again, but I'm not optimistic
> that any of these problems are solvable outside of the
> Android implementation.
>
> Interestingly, the code you provided ran flawlessly against
> the compatibility layer on Linux - a layer which is more
> like the Android JDBC2y than the android.database code.
>
> Finally, I noticed this in the JDBC2y result set:
>
> public java.sql.Blob getBlob(String columnIndex) throws
> SQLException {
> throw new SQLException("not supported");
> }
>
> so it seems SQLDroid is still the only JDBC driver that
> supports Blobs.
>
> Jim
>
>
> --
> Uwe Kubosch
> Systems Developer
> Datek Wireless AS
> u...@datek.no <mailto:u...@datek.no>
> http://datek.no/
>
>
> --
> Jim Redman
> (505) 662 5156 x85 <tel:%28505%29%20662%205156%20x85>
> http://www.ergotech.com

Damodar Periwal

unread,
May 25, 2012, 12:45:08 AM5/25/12
to sqld...@googlegroups.com
Jim,

Your guess is right.  The native_setLocale() code does a "CREATE TABLE" and then possibly a "BEGIN TRANSACTION". 

http://source-android.frandroid.com/frameworks/base/core/jni/android_database_SQLiteDatabase.cpp

Perhaps android implementation is dealing with only one instance of  SQLiteDatabase class for a given database url, and if so, the native_setLocale() called through SQLiteDatabase.openDatabase() method fails when the same database is under a transaction lock due to an earlier setAutoCommit(false) call.

I don't know, even if using NO_LOCALIZED_COLLATORS flag can solve the immediate problem, what other repercussions of that would be for a SQLite database especially if it is used in non-English locales.

Thanks,

-- Damodar

Jim Redman

unread,
May 25, 2012, 4:54:35 PM5/25/12
to sqld...@googlegroups.com
Damodar,

Sorry, I got distracted. I've just committed the code, but don't want
to pull the Connection code into the main branch until we've figured out
the consequences.

It seems that localization is only related to sort ordering, but I
suspect eliminating localization should be an option, but phrased as
something to do with multiple connections to the same database.

Jim


On 05/24/2012 10:45 PM, Damodar Periwal wrote:
> Jim,
>
> Your guess is right. The native_setLocale() code does a "CREATE TABLE"
> and thenpossibly a "BEGIN TRANSACTION".
> <mailto:jre...@ergotech.com <mailto:jre...@ergotech.com>>> wrote:
>
> Damodar/Uwe,
>
> It's an Android bug. There's an easy fix to make Damodar's test
> work. Change the open code to:
>
> sqliteDatabase =
>
> android.database.sqlite.____SQLiteDatabase.openDatabase(____dbQname,
> null,
> android.database.sqlite.____SQLiteDatabase.CREATE_IF_____NECESSARY
> | android.database.sqlite.____SQLiteDatabase.OPEN_READWRITE |
>
> android.database.sqlite.____SQLiteDatabase.NO_LOCALIZED_____COLLATORS);
>
> And then call:
>
> sqliteDatabase.setLocale(____Locale.getDefault());
>
>
> ignoring any thrown exceptions. Strangely, the "setLocale"
> doesn't
> fail in the test case.
>
> Threadedness.....
>
> As far as I can tell, android.database.____SQLiteDatabase is
>
> thread-safe. The underlying sqlite is not (I assume that
> it's not
> compiled with
> "SQLITE_THREADSAFE preprocessor macro set to 1" -
> http://www.sqlite.org/faq.____html#q6
> <http://www.sqlite.org/faq.__html#q6>
> <http://www.sqlite.org/faq.__html#q6
> <http://www.sqlite.org/faq.html#q6>>
>
> however, the SQLiteDatabase "lock()"'s all the calls - hence the
> irritating, but necessary "SQLliteLockedExceptions".
>
> So the Java code and so SQLDroid is solid and can be used from
> multiple thread although not simultaneously. Incidently, I
> suspect
> that the JDBC2y code is not and so using that code on multiple
> thread risks database corruption - something we might want
> to note
> on github.
>
> Damodar's test upchucks in setLocale, however, it passed the
> "lock()" and fails in native_setLocale
>
> public void setLocale(Locale locale) {
> lock();
> try {
> native_setLocale(locale.____toString(), mFlags);
>
> } finally {
> unlock();
> }
> }
>
> It seems that native_setLocale does some sort of locking of it's
> own, because if you make it a no-op by passing:
>
> android.database.sqlite.____SQLiteDatabase.NO_LOCALIZED_____COLLATORS
>
> into SQLiteDatabase.openDatabase then the problem with Demodar's
> code goes away:
>
> I/ActivityManager( 59): Starting activity: Intent {
> act=android.intent.action.MAIN
> cat=[android.intent.category.____LAUNCHER] flg=0x10200000
> cmp=com.damodar.android.jdbc2/____.HelloAndroidJDBC2Activity }
>
> I/System.out( 549): database path =
>
> /data/data/com.damodar.____android.jdbc2/databases/test.____db, new
> http://code.google.com/____searchframe#r07P-bJdvBY/trunk/____Android%20Java%20Framework/____src/SQLite/Database.java
> <http://code.google.com/__searchframe#r07P-bJdvBY/trunk/__Android%20Java%20Framework/__src/SQLite/Database.java>
> u...@datek.no <mailto:u...@datek.no> <mailto:u...@datek.no
> <mailto:u...@datek.no>>
>
> http://datek.no/
>
>
> --
> Jim Redman
> (505) 662 5156 x85 <tel:%28505%29%20662%205156%20x85>
> <tel:%28505%29%20662%205156%__20x85>

Damodar Periwal

unread,
May 30, 2012, 5:37:42 PM5/30/12
to sqld...@googlegroups.com
Jim,

I made the local change for NO_LOCALIZED_COLLATORS for the openDatabase call in the constructor of org.sqldroid.SQLiteDatabase.  My original test ran fine with that change on Android 2.2, as you also reported.  However, when I ran the test on Android 3.0, it fails with the following exception:

05-30 21:32:01.326: INFO/SQLDroid(13415): new sqlite jdbc from url 'jdbc:sqldroid:/data/data/com.damodar.android.jdbc2/databases/test.db', '{}'
05-30 21:32:01.346: INFO/SQlDRoid(13415): opening database /data/data/com.damodar.android.jdbc2/databases/test.db
05-30 21:32:01.526: INFO/System.out(13415): After getting connection...1
05-30 21:32:01.576: INFO/SQLDroid(13415): new sqlite jdbc from url 'jdbc:sqldroid:/data/data/com.damodar.android.jdbc2/databases/test.db', '{}'
05-30 21:32:01.576: INFO/SQlDRoid(13415): opening database /data/data/com.damodar.android.jdbc2/databases/test.db
05-30 21:32:01.636: INFO/System.out(13415): After getting connection...2
05-30 21:32:01.646: INFO/SQLDroid(13415): new sqlite jdbc from url 'jdbc:sqldroid:/data/data/com.damodar.android.jdbc2/databases/test.db', '{}'
05-30 21:32:01.646: INFO/SQlDRoid(13415): opening database /data/data/com.damodar.android.jdbc2/databases/test.db
05-30 21:32:02.476: DEBUG/dalvikvm(147): GC_EXPLICIT freed 929K, 20% free 14436K/17991K, paused 4ms+4ms
05-30 21:32:02.786: INFO/SqliteDatabaseCpp(13415): sqlite returned: error code = 5, msg = database is locked, db=/data/data/com.damodar.android.jdbc2/databases/test.db
05-30 21:32:02.846: INFO/System.out(13415): JDBC Error android.database.sqlite.SQLiteDatabaseLockedException: database is locked: , while compiling: PRAGMA journal_mode=TRUNCATE
05-30 21:32:02.878: WARN/ActivityManager(66): Launch timeout has expired, giving up wake lock!
05-30 21:32:02.936: ERROR/JDBC(13415): Error
05-30 21:32:02.936: ERROR/JDBC(13415): java.sql.SQLException: android.database.sqlite.SQLiteDatabaseLockedException: database is locked: , while compiling: PRAGMA journal_mode=TRUNCATE
05-30 21:32:02.936: ERROR/JDBC(13415):     at java.lang.reflect.Constructor.constructNative(Native Method)
05-30 21:32:02.936: ERROR/JDBC(13415):     at java.lang.reflect.Constructor.newInstance(Constructor.java:417)
05-30 21:32:02.936: ERROR/JDBC(13415):     at org.sqldroid.SQLDroidConnection.chainException(SQLDroidConnection.java:103)
05-30 21:32:02.936: ERROR/JDBC(13415):     at org.sqldroid.SQLiteDatabase.<init>(SQLiteDatabase.java:93)
05-30 21:32:02.936: ERROR/JDBC(13415):     at org.sqldroid.SQLDroidConnection.<init>(SQLDroidConnection.java:80)
05-30 21:32:02.936: ERROR/JDBC(13415):     at org.sqldroid.SQLDroidDriver.connect(SQLDroidDriver.java:45)
05-30 21:32:02.936: ERROR/JDBC(13415):     at java.sql.DriverManager.getConnection(DriverManager.java:180)
05-30 21:32:02.936: ERROR/JDBC(13415):     at java.sql.DriverManager.getConnection(DriverManager.java:145)
05-30 21:32:02.936: ERROR/JDBC(13415):     at com.damodar.android.jdbc2.HelloAndroidJDBC2Activity.tryJDBC(HelloAndroidJDBC2Activity.java:51)
05-30 21:32:02.936: ERROR/JDBC(13415):     at com.damodar.android.jdbc2.HelloAndroidJDBC2Activity.onCreate(HelloAndroidJDBC2Activity.java:23)
05-30 21:32:02.936: ERROR/JDBC(13415):     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1048)
05-30 21:32:02.936: ERROR/JDBC(13415):     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:1700)
05-30 21:32:02.936: ERROR/JDBC(13415):     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:1752)
05-30 21:32:02.936: ERROR/JDBC(13415):     at android.app.ActivityThread.access$1500(ActivityThread.java:123)
05-30 21:32:02.936: ERROR/JDBC(13415):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:993)
05-30 21:32:02.936: ERROR/JDBC(13415):     at android.os.Handler.dispatchMessage(Handler.java:99)
05-30 21:32:02.936: ERROR/JDBC(13415):     at android.os.Looper.loop(Looper.java:126)
05-30 21:32:02.936: ERROR/JDBC(13415):     at android.app.ActivityThread.main(ActivityThread.java:3997)
05-30 21:32:02.936: ERROR/JDBC(13415):     at java.lang.reflect.Method.invokeNative(Native Method)
05-30 21:32:02.936: ERROR/JDBC(13415):     at java.lang.reflect.Method.invoke(Method.java:491)
05-30 21:32:02.936: ERROR/JDBC(13415):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:841)
05-30 21:32:02.936: ERROR/JDBC(13415):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:599)
05-30 21:32:02.936: ERROR/JDBC(13415):     at dalvik.system.NativeStart.main(Native Method)
05-30 21:32:02.936: ERROR/JDBC(13415): Caused by: android.database.sqlite.SQLiteDatabaseLockedException: database is locked: , while compiling: PRAGMA journal_mode=TRUNCATE
05-30 21:32:02.936: ERROR/JDBC(13415):     at android.database.sqlite.SQLiteCompiledSql.native_compile(Native Method)
05-30 21:32:02.936: ERROR/JDBC(13415):     at android.database.sqlite.SQLiteCompiledSql.<init>(SQLiteCompiledSql.java:64)
05-30 21:32:02.936: ERROR/JDBC(13415):     at android.database.sqlite.SQLiteProgram.compileSql(SQLiteProgram.java:138)
05-30 21:32:02.936: ERROR/JDBC(13415):     at android.database.sqlite.SQLiteProgram.compileAndbindAllArgs(SQLiteProgram.java:368)
05-30 21:32:02.936: ERROR/JDBC(13415):     at android.database.sqlite.SQLiteStatement.acquireAndLock(SQLiteStatement.java:253)
05-30 21:32:02.936: ERROR/JDBC(13415):     at android.database.sqlite.SQLiteStatement.simpleQueryForString(SQLiteStatement.java:153)
05-30 21:32:02.936: ERROR/JDBC(13415):     at android.database.DatabaseUtils.stringForQuery(DatabaseUtils.java:736)
05-30 21:32:02.936: ERROR/JDBC(13415):     at android.database.DatabaseUtils.stringForQuery(DatabaseUtils.java:724)
05-30 21:32:02.936: ERROR/JDBC(13415):     at android.database.sqlite.SQLiteDatabase.setJournalMode(SQLiteDatabase.java:1025)
05-30 21:32:02.936: ERROR/JDBC(13415):     at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:961)
05-30 21:32:02.936: ERROR/JDBC(13415):     at android.database.sqlite.SQLiteDatabase.openDatabase(SQLiteDatabase.java:927)
05-30 21:32:02.936: ERROR/JDBC(13415):     at org.sqldroid.SQLiteDatabase.<init>(SQLiteDatabase.java:81)
05-30 21:32:02.936: ERROR/JDBC(13415):     ... 19 more


Further, when I added a call to setAutoCommit(false) on conn2 after a similar call is done for conn1 (please see the code below), I get the following exception on Android 2.2:

05-30 19:39:38.172: INFO/ActivityManager(59): Start proc com.damodar.android.jdbc2 for activity com.damodar.android.jdbc2/.HelloAndroidJDBC2Activity: pid=1348 uid=10046 gids={}
05-30 19:39:38.782: INFO/SQLDroid(1348): new sqlite jdbc from url 'jdbc:sqldroid:/data/data/com.damodar.android.jdbc2/databases/test.db', '{}'
05-30 19:39:38.782: INFO/SQlDRoid(1348): opening database /data/data/com.damodar.android.jdbc2/databases/test.db
05-30 19:39:38.842: INFO/System.out(1348): After getting connection...1
05-30 19:39:38.882: INFO/SQLDroid(1348): new sqlite jdbc from url 'jdbc:sqldroid:/data/data/com.damodar.android.jdbc2/databases/test.db', '{}'
05-30 19:39:38.882: INFO/SQlDRoid(1348): opening database /data/data/com.damodar.android.jdbc2/databases/test.db
05-30 19:39:38.892: INFO/System.out(1348): After getting connection...2
05-30 19:39:40.262: ERROR/Database(1348): Failure 5 (database is locked) on 0x11e778 when executing 'BEGIN EXCLUSIVE;'
05-30 19:39:40.302: INFO/System.out(1348): JDBC Error Unable to Chain SQLException database is locked: BEGIN EXCLUSIVE;
05-30 19:39:40.422: ERROR/JDBC(1348): Error
05-30 19:39:40.422: ERROR/JDBC(1348): java.sql.SQLException: Unable to Chain SQLException database is locked: BEGIN EXCLUSIVE;
05-30 19:39:40.422: ERROR/JDBC(1348):     at org.sqldroid.SQLDroidConnection.chainException(SQLDroidConnection.java:117)
05-30 19:39:40.422: ERROR/JDBC(1348):     at org.sqldroid.SQLiteDatabase.execNoArgVoidMethod(SQLiteDatabase.java:199)
05-30 19:39:40.422: ERROR/JDBC(1348):     at org.sqldroid.SQLiteDatabase.beginTransaction(SQLiteDatabase.java:215)
05-30 19:39:40.422: ERROR/JDBC(1348):     at org.sqldroid.SQLDroidConnection.setAutoCommit(SQLDroidConnection.java:318)
05-30 19:39:40.422: ERROR/JDBC(1348):     at com.damodar.android.jdbc2.HelloAndroidJDBC2Activity.tryJDBC(HelloAndroidJDBC2Activity.java:65)
05-30 19:39:40.422: ERROR/JDBC(1348):     at com.damodar.android.jdbc2.HelloAndroidJDBC2Activity.onCreate(HelloAndroidJDBC2Activity.java:23)

Code fragment with the additional statement follows:

               Connection conn1 = DriverManager.getConnection(jdbcURL);

            System.out.println("After getting connection...1");
           
            conn1.setAutoCommit(true);
            conn1.setAutoCommit(false);
            conn1.setAutoCommit(true);
           
            Connection conn2 = DriverManager.getConnection(jdbcURL);

            System.out.println("After getting connection...2");
           
            conn1.setAutoCommit(false);
            conn2.setAutoCommit(false);   // New call
           
            Connection conn3 = DriverManager.getConnection(jdbcURL);
            System.out.println("After getting connection...3");

And I get the following exception with Android 3.0 - with same code as above.

05-30 20:10:00.828: INFO/SQLDroid(898): new sqlite jdbc from url 'jdbc:sqldroid:/data/data/com.damodar.android.jdbc2/databases/test.db', '{}'
05-30 20:10:00.828: INFO/SQlDRoid(898): opening database /data/data/com.damodar.android.jdbc2/databases/test.db
05-30 20:10:01.046: INFO/System.out(898): After getting connection...1
05-30 20:10:01.056: INFO/SQLDroid(898): new sqlite jdbc from url 'jdbc:sqldroid:/data/data/com.damodar.android.jdbc2/databases/test.db', '{}'
05-30 20:10:01.066: INFO/SQlDRoid(898): opening database /data/data/com.damodar.android.jdbc2/databases/test.db
05-30 20:10:01.136: INFO/System.out(898): After getting connection...2
05-30 20:10:02.257: INFO/System.out(898): JDBC Error Timeout Expired
05-30 20:10:04.477: ERROR/JDBC(898): Error
05-30 20:10:04.477: ERROR/JDBC(898): java.sql.SQLException: Timeout Expired
05-30 20:10:04.477: ERROR/JDBC(898):     at org.sqldroid.SQLiteDatabase.execNoArgVoidMethod(SQLiteDatabase.java:203)
05-30 20:10:04.477: ERROR/JDBC(898):     at org.sqldroid.SQLiteDatabase.beginTransaction(SQLiteDatabase.java:215)
05-30 20:10:04.477: ERROR/JDBC(898):     at org.sqldroid.SQLDroidConnection.setAutoCommit(SQLDroidConnection.java:318)
05-30 20:10:04.477: ERROR/JDBC(898):     at com.damodar.android.jdbc2.HelloAndroidJDBC2Activity.tryJDBC(HelloAndroidJDBC2Activity.java:65)
05-30 20:10:04.477: ERROR/JDBC(898):     at com.damodar.android.jdbc2.HelloAndroidJDBC2Activity.onCreate(HelloAndroidJDBC2Activity.java:23)


By the way, the DatabaseHelper code to create the database has been commented out - we are dealing with a per-existing database in both the tests above (in Android 2.2 as well as in Android 3.0).

It seems that if one connection has setAutoCommit(false), another connection cannot have setAutoCommit(false) on the same database.   So this fix of NO_LOCALIZED_COLLATORS does not fully solve the problem of having multiple transactions open on a SQLite database at the same time.  By the way, under both Android 2.2 and Android 3.0, these transactions are being opened in the same execution thread as shown in the code fragment above.

I did not want to confuse everyone with this long email, but I thought more information might help in better diagnosis and decisions.

Thanks,

-- Damodar

Jim Redman

unread,
Aug 22, 2012, 11:43:33 AM8/22/12
to sqld...@googlegroups.com
I've been using the code, including the non-Android compatibility code,
for quite a while and it seems stable.

I'd like to push the code to the Origin and rebuild the download jars.

I currently have the compatibility code in a directory "compat". I
assume that I can just check that in and it will appear at the same
level as src in the archive - anyone object to this?

The Rakefile seems easy enough. Should the compat jar be pushed
separately to gems or is there a way to group them.

The name of the compatibility jar is currently:

SqldroidAndroidCompat.jar

for compatibility with the existing downloadable jar I think we should
go with:

sqldroidandroidcompat-x.x.x.jar

for the first cut.

I'd like to bump the version (and keep it the same for both). It's
currently 0.3.0. Is there some logic to this? It's going to take a
REALLY long time to get to a 1.0 release if we only bump the second
decimal place. Anyone have any thoughts about a serious bump? (I'd be
happy to drop the leading zero, so the next release is sqldroid.4.0.jar).

I'd like the compat and the sqldroid to have the same version since I
think that they should be a matched pair.

Jim

Uwe Kubosch

unread,
Aug 23, 2012, 3:48:10 AM8/23/12
to <sqldroid@googlegroups.com>
On 2012-08-22, at 17:43, Jim Redman <jre...@ergotech.com> wrote:

> I've been using the code, including the non-Android compatibility code, for quite a while and it seems stable.
>
> I'd like to push the code to the Origin and rebuild the download jars.
>
> I currently have the compatibility code in a directory "compat". I assume that I can just check that in and it will appear at the same level as src in the archive - anyone object to this?

Sounds good.

> The Rakefile seems easy enough. Should the compat jar be pushed separately to gems or is there a way to group them.

Se can place both cars in the same gem. How big is the compat jar? Every byte counts when making software for phones, but it would be easier to distribute both jars together.

> The name of the compatibility jar is currently:
>
> SqldroidAndroidCompat.jar
>
> for compatibility with the existing downloadable jar I think we should go with:
>
> sqldroidandroidcompat-x.x.x.jar
>
> for the first cut.

Sounds OK. The "compat" jar only contains what is needed to use on non-android system, right? So you need both jars on non-android systems? Just to clarify.

> I'd like to bump the version (and keep it the same for both). It's currently 0.3.0. Is there some logic to this? It's going to take a REALLY long time to get to a 1.0 release if we only bump the second decimal place. Anyone have any thoughts about a serious bump? (I'd be happy to drop the leading zero, so the next release is sqldroid.4.0.jar).

The logic is that it is _not_ a decimal number, but a three part version string, with numeric parts.

The first number (major) is bumped if there are backwards breaking changes, typically a rewrite or major reorganisation. There is no requirement for version with different major version to be compatible.

The second number (minor) is bumped when there are additions to the api or significant features (like the compatibility classes). Versions with the same major version number should be able to replace any version with a lower minor number.

The third number (patch level) is bumped on all releases unless any of the first numbers have already been bumped. Versions where only the third number differs should be interchangeable.

Whenever a number is bumped, all numbers to the right of it are reset to zero.

I would also like to indicate, even if it breaks the logic, "production ready" by bumping to version "1.0.0".

Sounds good?

> I'd like the compat and the sqldroid to have the same version since I think that they should be a matched pair.

I agree completely.

--
Uwe Kubosch
Systems Developer
Datek Wireless AS
u...@datek.no
http://datek.no/


Damodar Periwal

unread,
Aug 24, 2012, 2:34:03 AM8/24/12
to sqld...@googlegroups.com
Bumping the version to "1.0.0" sounds good. 

One suggestion.  While you are making that change, can you please also remove or comment out four debug statements (System.out.println) from the method setObj(int n, Object obj) in the file SQLDroidPreparedStatement.java?  I think they have served their purpose by now.

Thanks,

-- Damodar

Jim Redman

unread,
Aug 24, 2012, 1:34:25 PM8/24/12
to sqld...@googlegroups.com

I have a couple of pieces of feedback from a new user along the lines of
Damodar's comments of excessive verbosity.

I propose we bump to 1.0.0RC1 and see if we've removed the irritants.
An RC may also encourage anyone else using SQLDroid. Hey! I've just
noticed that there are some changes by Bagges
(https://github.com/bagges). At first glance they look good. Unless I
see a problem with them, I'll pull them. How do you contact other
GitHub users, it would be nice to let him know.

There are a couple of real issues that I noticed yesterday.

First is this, in PreparedStatement execute():

Cursor c = db.rawQuery(sql, makeArgListQueryString());
rs = new SQLDroidResultSet(c);
if ( rs.getMetaData().getColumnCount() != 0 ) {
rs = new SQLDroidResultSet(c);
}

The result set is being created twice in the case that there are no
results. My interpretation of the behavior is that the result set
should not be created if the SQL returns no result. I base this on this
from the execute JavaDocs:

Returns:
true if the first result is a ResultSet object; false if it is an update
count or there are no results

It's not unambiguous that there should be no result set if there are no
results (an empty result set is a valid object - at least in SQLDroid),
but I think that behavior is implied. I propose to remove the first
creation.

Second is that PreparedStatement fulfills the contract:

There are no more results when:
* // stmt is a Statement object
* ((stmt.getMoreResults() == false) && (stmt.getUpdateCount() == -1))

but Statement does not (and it returns a result set on empty query).
This could cause code to hang, eg:

while ( (updateCount=statement.getUpdateCount()) != -1
|| (result= statement.getMoreResults()) ) {
[...]
}

I would propose to move the code from PreparedStatement to Statement
(basically, it will always return -1 on the second call.
getMoreResults() always returns false).

Jim

On 08/24/2012 12:34 AM, Damodar Periwal wrote:
> Bumping the version to "1.0.0" sounds good.
>
> One suggestion. While you are making that change, can you please also
> remove or comment out four debug statements (System.out.println) from
> the method setObj(int n, Object obj) in the file
> SQLDroidPreparedStatement.java? I think they have served their purpose
> by now.
>
> Thanks,
>
> -- Damodar
>
> On Thu, Aug 23, 2012 at 12:48 AM, Uwe Kubosch <u...@datek.no
> <mailto:u...@datek.no>> wrote:
>
> On 2012-08-22, at 17:43, Jim Redman <jre...@ergotech.com
> u...@datek.no <mailto:u...@datek.no>
> http://datek.no/
Reply all
Reply to author
Forward
0 new messages