Android Question sqlite attach query app crash

Kiran Raotole

Active Member
Licensed User
My app crashes at attach database query
B4A Code :
B4X:
Starter.year.ExecNonQuery("attach '" & File.Combine(Starter.mdbpath,Starter.current_firm_and) & "' as db2;")
qry = $"select GL.*,b.UNIQID_GUID as UNIQID_GUID from GL,db2.TRBLANK1_AND b where
        GL.FBOOK='G101' AND GL.FACCODE=b.FITEM"$
Error Log:

B4X:
android.database.sqlite.SQLiteDatabaseLockedException: database is locked (code 5)
    at android.database.sqlite.SQLiteConnection.nativeExecuteForChangedRowCount(Native Method)
    at android.database.sqlite.SQLiteConnection.executeForChangedRowCount(SQLiteConnection.java:734)
    at android.database.sqlite.SQLiteSession.executeForChangedRowCount(SQLiteSession.java:754)
    at android.database.sqlite.SQLiteStatement.executeUpdateDelete(SQLiteStatement.java:64)
    at android.database.sqlite.SQLiteDatabase.executeSql(SQLiteDatabase.java:1676)
    at android.database.sqlite.SQLiteDatabase.execSQL(SQLiteDatabase.java:1605)
    at anywheresoftware.b4a.sql.SQL.ExecNonQuery(SQL.java:74)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at anywheresoftware.b4a.shell.Shell.runVoidMethod(Shell.java:777)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:354)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:255)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:144)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:180)
    at anywheresoftware.b4a.debug.Debug.delegate(Debug.java:262)
    at com.ctron.balajiPOS.sale_order._cart_bill_item_query(sale_order.java:2733)
    at com.ctron.balajiPOS.sale_order._cart_bill_item(sale_order.java:2705)
    at com.ctron.balajiPOS.sale_order._toolbar_menuitemclick(sale_order.java:4239)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:732)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:351)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:255)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:144)
    at anywheresoftware.b4a.BA$1.run(BA.java:335)
    at android.os.Handler.handleCallback(Handler.java:739)
    at android.os.Handler.dispatchMessage(Handler.java:95)
    at android.os.Looper.loop(Looper.java:135)
    at android.app.ActivityThread.main(ActivityThread.java:5264)
    at java.lang.reflect.Method.invoke(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:372)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:908)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:703)
 

sorex

Expert
Licensed User
did you try with a regular (inner) join instead of this table1,table2 method?
 

aeric

Well-Known Member
Licensed User
Maybe you didn't close the connection before you try to open another connection?
 

aeric

Well-Known Member
Licensed User
Starter
B4X:
#Region  Service Attributes
    #StartAtBoot: False
    #ExcludeFromLibrary: True
#End Region

Sub Process_Globals
    Dim SQL As SQL
    Dim mdbpath As String = File.DirInternal
    Dim current_firm_and As String = "db2.db"
End Sub

Sub Service_Create
    Dim strSQL As String
    If File.Exists(mdbpath, "db1.db") Then
        SQL.Initialize(mdbpath, "db1.db", False)
    Else
        SQL.Initialize(mdbpath, "db1.db", True)
        strSQL = "CREATE TABLE GL (FBOOK TEXT, FACCODE TEXT)"
        SQL.ExecNonQuery(strSQL)
        strSQL = "INSERT INTO GL (FBOOK, FACCODE) SELECT 'G101', 'A202'"
        SQL.ExecNonQuery(strSQL)
    End If
    SQL.Close
    If File.Exists(mdbpath, current_firm_and) Then
        SQL.Initialize(mdbpath, current_firm_and, False)
    Else
        SQL.Initialize(mdbpath, current_firm_and, True)
        strSQL = "CREATE TABLE TRBLANK1_AND (FITEM TEXT, UNIQID_GUID TEXT)"
        SQL.ExecNonQuery(strSQL)
        strSQL = "INSERT INTO TRBLANK1_AND (FITEM, UNIQID_GUID) SELECT 'A202', 'CDE456'"
        SQL.ExecNonQuery(strSQL)
    End If
    SQL.Close
End Sub

Sub Service_Start (StartingIntent As Intent)
    Service.StopAutomaticForeground 'Starter service can start in the foreground state in some edge cases.
End Sub

Sub Service_TaskRemoved
    'This event will be raised when the user removes the app from the recent apps list.
End Sub

'Return true to allow the OS default exceptions handler to handle the uncaught exception.
Sub Application_Error (Error As Exception, StackTrace As String) As Boolean
    Return True
End Sub

Sub Service_Destroy

End Sub
Main
B4X:
#Region  Project Attributes
    #ApplicationLabel: SQL
    #VersionCode: 1
    #VersionName:
    'SupportedOrientations possible values: unspecified, landscape or portrait.
    #SupportedOrientations: unspecified
    #CanInstallToExternalStorage: False
#End Region

#Region  Activity Attributes
    #FullScreen: False
    #IncludeTitle: True
#End Region

Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.

End Sub

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    'These variables can only be accessed from this module.

End Sub

Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    'Activity.LoadLayout("Layout1")
    ConnectDatabases
End Sub

Sub Activity_Resume

End Sub

Sub Activity_Pause (UserClosed As Boolean)

End Sub

Sub ConnectDatabases     
    Starter.SQL.Initialize(Starter.mdbpath, "db1.db", False)
    Dim qry As String
    qry = $"attach '${File.Combine(Starter.mdbpath, Starter.current_firm_and)}' as db2;"$
    Starter.SQL.ExecNonQuery(qry)
    qry = "SELECT GL.*, b.UNIQID_GUID"
    qry = qry & " FROM GL, db2.TRBLANK1_AND b"
    qry = qry & " WHERE GL.FBOOK = 'G101' AND GL.FACCODE = b.FITEM"
    Dim cur As Cursor
    cur = Starter.SQL.ExecQuery(qry)
    For i = 0 To cur.RowCount - 1
        cur.Position = i
        Log(cur.GetColumnName(0) & ": " & cur.GetString2(0))
        Log(cur.GetColumnName(1) & ": " & cur.GetString2(1))
        Log("UNIQID_GUID: " & cur.GetString("UNIQID_GUID"))
    Next
    cur.Close
    Starter.SQL.Close
End Sub
 

Attachments

Last edited:

Albert Kallal

Member
Licensed User
Ok, what you have looks ok. However, you have a main activity - but no UI code. So this looks like some kind of threading issue.

If you JUST launch what you have (debug mode), then we see this:

B4X:
** Activity (main) Resume **
** Activity (main) Pause, UserClosed = false **
** Activity (main) Create, isFirst = false **
** Activity (main) Resume **
*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create, isFirst = true **
So, the startup code is running multiple times (that explains your "lock" error. The code not finished the first time - and it is being fired again!

I did not get a lock error, but I get a cursor error not initilized.

So when I run this I get:

B4X:
Error occurred on line: 55 (Main)
java.lang.RuntimeException: Object should first be initialized (Cursor).
    at anywheresoftware.b4a.AbsObjectWrapper.getObject(AbsObjectWrapper.java:50)
    at anywheresoftware.b4a.sql.SQL$CursorWrapper.getRowCount(SQL.java:335)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:732)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:348)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:255)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:144)
    at b4a.sql.main.afterFirstLayout(main.java:104)
    at b4a.sql.main.access$000(main.java:17)
    at b4a.sql.main$WaitForLayout.run(main.java:82)
    at android.os.Handler.handleCallback(Handler.java:751)
    at android.os.Handler.dispatchMessage(Handler.java:95)
    at android.os.Looper.loop(Looper.java:154)
    at android.app.ActivityThread.main(ActivityThread.java:6165)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:888)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:778)
(the 55 might be off a few lines - I had put in a few extra "log" to spit out the row count.

So, the cursor seems to be even going out of scope!!! NONE of how this was behaving was making any sense at all!


I would create a layout - place a single button on it to call your routine that does the connect code + sql you have.

Even if I JUST add a layout - with a button - but it does nothing?

the code runs fine.
so try:
B4X:
Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    Activity.LoadLayout("1")
    If FirstTime Then
       ConnectDatabases
    End If

End Sub
Also note the "first time" check above.

So, starting that main without any kind of "UI", it seems the Android OS tosses out this "stray cat" routine - it will get restarted multiple times.
(that multiple times will explain the locking issue).

Have main load some kind of UI - that alone seems to fix this.

Better yet, is have a button call + run your Connect databases sub.

Or if you want/have no UI for quick testing? Then move all your code to the starter service if you just testing.

I ALWAYS thought that the starter service would finish before the "main" activity service starts. However, it can/will be fired multiple times - and it seems even several times without any kind of UI. And then it goes out of scope (again, due to no UI - I am betting some Android OS optomzing does this - hey - no UI - we going to stop that activity!!!).

So, if you wish to leave your code in "main", then load a layout with a single/simple button that runs this code.

I found your code runs fine if you load a layout. (and even without a button to call the code). But, for this testing, call the connect data stuff from a simple button would be the best bet here.

A few more tips:
Surprising the sample is set to release???? - it should be debug.

So, put it back to debug- and and do a tools->clean RIGHT after you switch it to debug before you try it.

I would also re-boot the actual device, since with those multiple threads running, the files get messed up and are locked.

So, if you not going to "wait" for any UI and a user hitting a button, them simply move all of your code to the starter service for testing.

And the "firstime" check is also a good idea.

However, your sql, and the code you have does look ok, and does run fine for me as long as I load a layout.

I did not try, but moving all your sample code to the starter service module also likely would have worked.

Regards,
Albert D. Kallal
Edmonton, Alberta Canada
 

aeric

Well-Known Member
Licensed User
Ok, what you have looks ok. However, you have a main activity - but no UI code. So this looks like some kind of threading issue.

If you JUST launch what you have (debug mode), then we see this:

B4X:
** Activity (main) Resume **
** Activity (main) Pause, UserClosed = false **
** Activity (main) Create, isFirst = false **
** Activity (main) Resume **
*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create, isFirst = true **
So, the startup code is running multiple times (that explains your "lock" error. The code not finished the first time - and it is being fired again!

I did not get a lock error, but I get a cursor error not initilized.

So when I run this I get:

B4X:
Error occurred on line: 55 (Main)
java.lang.RuntimeException: Object should first be initialized (Cursor).
    at anywheresoftware.b4a.AbsObjectWrapper.getObject(AbsObjectWrapper.java:50)
    at anywheresoftware.b4a.sql.SQL$CursorWrapper.getRowCount(SQL.java:335)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:732)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:348)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:255)
    at java.lang.reflect.Method.invoke(Native Method)
    at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:144)
    at b4a.sql.main.afterFirstLayout(main.java:104)
    at b4a.sql.main.access$000(main.java:17)
    at b4a.sql.main$WaitForLayout.run(main.java:82)
    at android.os.Handler.handleCallback(Handler.java:751)
    at android.os.Handler.dispatchMessage(Handler.java:95)
    at android.os.Looper.loop(Looper.java:154)
    at android.app.ActivityThread.main(ActivityThread.java:6165)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:888)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:778)
(the 55 might be off a few lines - I had put in a few extra "log" to spit out the row count.

So, the cursor seems to be even going out of scope!!! NONE of how this was behaving was making any sense at all!


I would create a layout - place a single button on it to call your routine that does the connect code + sql you have.

Even if I JUST add a layout - with a button - but it does nothing?

the code runs fine.
so try:
B4X:
Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    Activity.LoadLayout("1")
    If FirstTime Then
       ConnectDatabases
    End If

End Sub
Also note the "first time" check above.

So, starting that main without any kind of "UI", it seems the Android OS tosses out this "stray cat" routine - it will get restarted multiple times.
(that multiple times will explain the locking issue).

Have main load some kind of UI - that alone seems to fix this.

Better yet, is have a button call + run your Connect databases sub.

Or if you want/have no UI for quick testing? Then move all your code to the starter service if you just testing.

I ALWAYS thought that the starter service would finish before the "main" activity service starts. However, it can/will be fired multiple times - and it seems even several times without any kind of UI. And then it goes out of scope (again, due to no UI - I am betting some Android OS optomzing does this - hey - no UI - we going to stop that activity!!!).

So, if you wish to leave your code in "main", then load a layout with a single/simple button that runs this code.

I found your code runs fine if you load a layout. (and even without a button to call the code). But, for this testing, call the connect data stuff from a simple button would be the best bet here.

A few more tips:
Surprising the sample is set to release???? - it should be debug.

So, put it back to debug- and and do a tools->clean RIGHT after you switch it to debug before you try it.

I would also re-boot the actual device, since with those multiple threads running, the files get messed up and are locked.

So, if you not going to "wait" for any UI and a user hitting a button, them simply move all of your code to the starter service for testing.

And the "firstime" check is also a good idea.

However, your sql, and the code you have does look ok, and does run fine for me as long as I load a layout.

I did not try, but moving all your sample code to the starter service module also likely would have worked.

Regards,
Albert D. Kallal
Edmonton, Alberta Canada
Hi Albert, are you sure you run my code into errors without modifying any line?
 

aeric

Well-Known Member
Licensed User
I just wanted to make the code short since we only have 2 lines of code to start. I tested the code in an emulator since my Android device is broken. The code run normally where Main run after Starter as seen in the log. I am not sure why Service_Create will run multiple times in your device but I think that is not a problem because it will check whether the db files are existed. Actually I just want to show that I can attach to another db to the first db. The rest of the code is not interesting.
 

Albert Kallal

Member
Licensed User
Oh, no issues or worry about this being quick + dirty testing code (not a problem!!!).
Hi Albert, are you sure you run my code into errors without modifying any line?
Yes - but I DID add a layout with a button (no code stubs/members generated, no nothing - just a new view/layout)
(zero change to code).

So, ONLY change to your code is this:

B4X:
    Activity.LoadLayout("1")     <---- only change in code
    ConnectDatabases
As noted, if I don't load a layout, then the problem was larger then "just" the code running multiple times - the error I posted above showed the cursor going out of scope - that simply does not make sense (should not occur). So, as noted, adding a layout fixes this, or moving your code all to the starter, then this will work.

I am running a slight older version - 9.01.2.

However with the addition of the simple above loadlayout - no other code, then what you have runs fine on both emulator and on my moto phone.

So, add a simple layout - added a button to the layout also - but did not gen code stubs.

The multiple times running is not the end of the world, but me seeing the cursor go out of scope? Then it seems without any UI, that thread is being hammered or zooted by the OS and then being re-started. My cursor "going out of scope" suggests that thread is being re-loaded and not finished previous. Variables being overwritten in the same memory space is killing this code.

The code you have looks rather fine, and my error was simply not sensible at all. The cursor was being hammered! So, clean ups, opening of that file - everything was stepping on its own toes.

I don't have enough experience to state that not having any layout for an activity is a problem, but from looking at this post, I would conclude as such. Cleary this code trips over itself big time.

So, adding a layout, or moving all the code to starter seems to fix this issue.

Regards,
Albert D. Kallal
Edmonton, Alberta Canada
 

aeric

Well-Known Member
Licensed User
Understood. Maybe the older version of B4A shows different behaviour. I think it is already been "fixed" in the latest version of B4A 9.80. Cheers.
 
Top