Android Question Please help: My SQL query does not work

AkaiYon1003 · Jan 31, 2022

Below is my query:

[CODE lang="sql" title="Query"]Private res As ResultSet = modSQL.mysql.ExecQuery("SELECT tblActivities.activityID, tblActivities.Activity, tblActivities.Status, tblCommodities.commodity, tblMOT.mot, tblLedger.amount FROM (((tblLedger INNER JOIN tblMOT ON tblLedger.motID = tblMOT.motID) INNER JOIN tblCommodities ON tblLedger.commodityID_fk = tblCommodities.commodityID) INNER JOIN (tblEngagements INNER JOIN tblActivities ON tblEngagements.activityID_fk = tblActivities.activityID) ON tblLedger.engagementID_fk = tblEngagements.engagementID) INNER JOIN tblEvents ON tblEngagements.eventID = "& evntID &"")[/CODE]

For additional information, I attached a screenshot of my database design:

Lastly, below is the error I'm having:

[CODE title="Error"]Logger connected to: samsung SM-N976N
--------- beginning of main
Copying updated assets files (43)
*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create, isFirst = true **
Call B4XPages.GetManager.LogEvents = True to enable logging B4XPages events.
** Activity (main) Resume **
Error occurred on line: 41 (EventPage)
java.lang.reflect.InvocationTargetException
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.keywords.Common.CallSubDebug2(Common.java:1055)
at b4a.DailyTask.b4xpagesmanager._createpageifneeded(b4xpagesmanager.java:1064)
at b4a.DailyTask.b4xpagesmanager._showpage(b4xpagesmanager.java:421)
at b4a.DailyTask.b4xpages._showpage(b4xpages.java:46)
at b4a.DailyTask.b4xmainpage._clv_itemclick(b4xmainpage.java:216)
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:351)
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 anywheresoftware.b4a.keywords.Common.CallSub4(Common.java:1082)
at anywheresoftware.b4a.keywords.Common.CallSubNew3(Common.java:1045)
at b4a.example3.customlistview$ResumableSub_PanelClickHandler.resume(customlistview.java:805)
at b4a.example3.customlistview._panelclickhandler(customlistview.java:748)
at b4a.example3.customlistview._panel_click(customlistview.java:735)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:213)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:193)
at anywheresoftware.b4a.objects.ViewWrapper$1.onClick(ViewWrapper.java:80)
at android.view.View.performClick(View.java:5638)
at android.view.View$PerformClick.run(View.java:22430)
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:6176)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:891)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:781)
Caused by: java.lang.RuntimeException: java.lang.reflect.InvocationTargetException
at anywheresoftware.b4a.debug.Debug.CallSub4(Debug.java:336)
at anywheresoftware.b4a.debug.Debug.CallSubNew2(Debug.java:285)
... 30 more
Caused by: java.lang.reflect.InvocationTargetException
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.debug.Debug.CallSub4(Debug.java:318)
... 31 more
Caused by: android.database.sqlite.SQLiteException: no such column: tblActivities.Activity (code 1): , while compiling: SELECT tblActivities.activityID, tblActivities.Activity, tblActivities.Status, tblCommodities.commodity, tblMOT.mot, tblLedger.amount FROM (((tblLedger INNER JOIN tblMOT ON tblLedger.motID = tblMOT.motID) INNER JOIN tblCommodities ON tblLedger.commodityID_fk = tblCommodities.commodityID) INNER JOIN (tblEngagements INNER JOIN tblActivities ON tblEngagements.activityID_fk = tblActivities.activityID) ON tblLedger.engagementID_fk = tblEngagements.engagementID) INNER JOIN tblEvents ON tblEngagements.eventID = 1
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1318)
at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1257)
at anywheresoftware.b4a.sql.SQL.ExecQuery2(SQL.java:223)
at anywheresoftware.b4a.sql.SQL.ExecQuery(SQL.java:211)
at b4a.DailyTask.eventpage._showdb(eventpage.java:133)
at b4a.DailyTask.eventpage._b4xpage_created(eventpage.java:114)
... 33 more
[/CODE]

Please let me know how I can fix this. Thank you very much.

Lucas Siqueira · Jan 31, 2022

AkaiYon1003 said:
Below is my query:

[CODE lang="sql" title="Query"]Private res As ResultSet = modSQL.mysql.ExecQuery("SELECT tblActivities.activityID, tblActivities.Activity, tblActivities.Status, tblCommodities.commodity, tblMOT.mot, tblLedger.amount FROM (((tblLedger INNER JOIN tblMOT ON tblLedger.motID = tblMOT.motID) INNER JOIN tblCommodities ON tblLedger.commodityID_fk = tblCommodities.commodityID) INNER JOIN (tblEngagements INNER JOIN tblActivities ON tblEngagements.activityID_fk = tblActivities.activityID) ON tblLedger.engagementID_fk = tblEngagements.engagementID) INNER JOIN tblEvents ON tblEngagements.eventID = "& evntID &"")[/CODE]

For additional information, I attached a screenshot of my database design:
View attachment 124978

Lastly, below is the error I'm having:

[CODE title="Error"]Logger connected to: samsung SM-N976N
--------- beginning of main
Copying updated assets files (43)
*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create, isFirst = true **
Call B4XPages.GetManager.LogEvents = True to enable logging B4XPages events.
** Activity (main) Resume **
Error occurred on line: 41 (EventPage)
java.lang.reflect.InvocationTargetException
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.keywords.Common.CallSubDebug2(Common.java:1055)
at b4a.DailyTask.b4xpagesmanager._createpageifneeded(b4xpagesmanager.java:1064)
at b4a.DailyTask.b4xpagesmanager._showpage(b4xpagesmanager.java:421)
at b4a.DailyTask.b4xpages._showpage(b4xpages.java:46)
at b4a.DailyTask.b4xmainpage._clv_itemclick(b4xmainpage.java:216)
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:351)
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 anywheresoftware.b4a.keywords.Common.CallSub4(Common.java:1082)
at anywheresoftware.b4a.keywords.Common.CallSubNew3(Common.java:1045)
at b4a.example3.customlistview$ResumableSub_PanelClickHandler.resume(customlistview.java:805)
at b4a.example3.customlistview._panelclickhandler(customlistview.java:748)
at b4a.example3.customlistview._panel_click(customlistview.java:735)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:213)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:193)
at anywheresoftware.b4a.objects.ViewWrapper$1.onClick(ViewWrapper.java:80)
at android.view.View.performClick(View.java:5638)
at android.view.View$PerformClick.run(View.java:22430)
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:6176)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:891)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:781)
Caused by: java.lang.RuntimeException: java.lang.reflect.InvocationTargetException
at anywheresoftware.b4a.debug.Debug.CallSub4(Debug.java:336)
at anywheresoftware.b4a.debug.Debug.CallSubNew2(Debug.java:285)
... 30 more
Caused by: java.lang.reflect.InvocationTargetException
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.debug.Debug.CallSub4(Debug.java:318)
... 31 more
Caused by: android.database.sqlite.SQLiteException: no such column: tblActivities.Activity (code 1): , while compiling: SELECT tblActivities.activityID, tblActivities.Activity, tblActivities.Status, tblCommodities.commodity, tblMOT.mot, tblLedger.amount FROM (((tblLedger INNER JOIN tblMOT ON tblLedger.motID = tblMOT.motID) INNER JOIN tblCommodities ON tblLedger.commodityID_fk = tblCommodities.commodityID) INNER JOIN (tblEngagements INNER JOIN tblActivities ON tblEngagements.activityID_fk = tblActivities.activityID) ON tblLedger.engagementID_fk = tblEngagements.engagementID) INNER JOIN tblEvents ON tblEngagements.eventID = 1
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1318)
at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1257)
at anywheresoftware.b4a.sql.SQL.ExecQuery2(SQL.java:223)
at anywheresoftware.b4a.sql.SQL.ExecQuery(SQL.java:211)
at b4a.DailyTask.eventpage._showdb(eventpage.java:133)
at b4a.DailyTask.eventpage._b4xpage_created(eventpage.java:114)
... 33 more
[/CODE]

Please let me know how I can fix this. Thank you very much.

have you tested your select directly in your database?

Lucas Siqueira · Jan 31, 2022

AkaiYon1003 said:
Below is my query:

[CODE lang="sql" title="Query"]Private res As ResultSet = modSQL.mysql.ExecQuery("SELECT tblActivities.activityID, tblActivities.Activity, tblActivities.Status, tblCommodities.commodity, tblMOT.mot, tblLedger.amount FROM (((tblLedger INNER JOIN tblMOT ON tblLedger.motID = tblMOT.motID) INNER JOIN tblCommodities ON tblLedger.commodityID_fk = tblCommodities.commodityID) INNER JOIN (tblEngagements INNER JOIN tblActivities ON tblEngagements.activityID_fk = tblActivities.activityID) ON tblLedger.engagementID_fk = tblEngagements.engagementID) INNER JOIN tblEvents ON tblEngagements.eventID = "& evntID &"")[/CODE]

For additional information, I attached a screenshot of my database design:
View attachment 124978

Lastly, below is the error I'm having:

[CODE title="Error"]Logger connected to: samsung SM-N976N
--------- beginning of main
Copying updated assets files (43)
*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create, isFirst = true **
Call B4XPages.GetManager.LogEvents = True to enable logging B4XPages events.
** Activity (main) Resume **
Error occurred on line: 41 (EventPage)
java.lang.reflect.InvocationTargetException
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.keywords.Common.CallSubDebug2(Common.java:1055)
at b4a.DailyTask.b4xpagesmanager._createpageifneeded(b4xpagesmanager.java:1064)
at b4a.DailyTask.b4xpagesmanager._showpage(b4xpagesmanager.java:421)
at b4a.DailyTask.b4xpages._showpage(b4xpages.java:46)
at b4a.DailyTask.b4xmainpage._clv_itemclick(b4xmainpage.java:216)
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:351)
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 anywheresoftware.b4a.keywords.Common.CallSub4(Common.java:1082)
at anywheresoftware.b4a.keywords.Common.CallSubNew3(Common.java:1045)
at b4a.example3.customlistview$ResumableSub_PanelClickHandler.resume(customlistview.java:805)
at b4a.example3.customlistview._panelclickhandler(customlistview.java:748)
at b4a.example3.customlistview._panel_click(customlistview.java:735)
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:213)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:193)
at anywheresoftware.b4a.objects.ViewWrapper$1.onClick(ViewWrapper.java:80)
at android.view.View.performClick(View.java:5638)
at android.view.View$PerformClick.run(View.java:22430)
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:6176)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:891)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:781)
Caused by: java.lang.RuntimeException: java.lang.reflect.InvocationTargetException
at anywheresoftware.b4a.debug.Debug.CallSub4(Debug.java:336)
at anywheresoftware.b4a.debug.Debug.CallSubNew2(Debug.java:285)
... 30 more
Caused by: java.lang.reflect.InvocationTargetException
at java.lang.reflect.Method.invoke(Native Method)
at anywheresoftware.b4a.debug.Debug.CallSub4(Debug.java:318)
... 31 more
Caused by: android.database.sqlite.SQLiteException: no such column: tblActivities.Activity (code 1): , while compiling: SELECT tblActivities.activityID, tblActivities.Activity, tblActivities.Status, tblCommodities.commodity, tblMOT.mot, tblLedger.amount FROM (((tblLedger INNER JOIN tblMOT ON tblLedger.motID = tblMOT.motID) INNER JOIN tblCommodities ON tblLedger.commodityID_fk = tblCommodities.commodityID) INNER JOIN (tblEngagements INNER JOIN tblActivities ON tblEngagements.activityID_fk = tblActivities.activityID) ON tblLedger.engagementID_fk = tblEngagements.engagementID) INNER JOIN tblEvents ON tblEngagements.eventID = 1
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1318)
at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1257)
at anywheresoftware.b4a.sql.SQL.ExecQuery2(SQL.java:223)
at anywheresoftware.b4a.sql.SQL.ExecQuery(SQL.java:211)
at b4a.DailyTask.eventpage._showdb(eventpage.java:133)
at b4a.DailyTask.eventpage._b4xpage_created(eventpage.java:114)
... 33 more
[/CODE]

Please let me know how I can fix this. Thank you very much.

note: when using inner join, if the record does not exist in any table, it will not display the record in your select.
an alternative is to use left join.

SQL:

SELECT
   tblActivities.activityID
 , tblActivities.Activity
 , tblActivities.Status
 , tblCommodities.commodity
 , tblMOT.mot
 , tblLedger.amount
 
 FROM tblEngagements
 INNER JOIN tblEvents ON tblEvents.eventID = tblEngagements.eventID
 INNER JOIN tblActivities ON tblActivities.activityID = tblEngagements.activityID_fk
 INNER JOIN tblLedger ON tblLedger.engagementID_fk = tblEngagements.engagementID
 INNER JOIN tblMOT ON tblMOT.motID = tblLedger.motID
 INNER JOIN tblCommodities ON tblCommodities.commodityID = tblLedger.commodityID_fk
 
 WHERE tblEngagements.eventID = 1

alwaysbusy · Jan 31, 2022

This part definitely looks weird in your query:

Code:

... INNER JOIN tblEvents ON tblEngagements.eventID = 1

should be something like:

Code:

... INNER JOIN tblEvents ON tblEngagements.eventID WHERE tblEngagements.eventID = 1

EDIT: just noticed @Lucas Siqueira did use the correct WHERE construction.

Alwaysbusy

aeric · Jan 31, 2022

AkaiYon1003 said:
Caused by: android.database.sqlite.SQLiteException: no such column: tblActivities.Activity (code 1): , while compiling: SELECT tblActivities.activityID, tblActivities.Activity, ...

There is no column name Activity.

AkaiYon1003 · Feb 1, 2022

aeric said:
There is no column name Activity.

Yeah2. Thank you very much. Actually found it earlier today. It was very clumsy of me.

AkaiYon1003 · Feb 1, 2022

Thank you everyone for your quick replies. Really appreciate all of you. I actually found my clumsiness, earlier today. Thank you all again so much. And sorry for taking some of your time, on this clumsiness.

You all are awesome!
@Lucas Siqueira
@alwaysbusy
@aeric

Mahares · Feb 1, 2022

AkaiYon1003 said:
I actually found my clumsiness

For what it is worth, sometimes when you have a charged up query with many references, it will look tidier if you use aliases. Here is an example:

Code:

Dim strQuery As String = $"Select
A.activityID
, A.Activity
, A.Status
, C.commodity
, M.mot
, L.amount
FROM tblEngagements En
INNER JOIN tblEvents Ev ON Ev.eventID = En.eventID
INNER JOIN tblActivities A ON A.activityID = En.activityID_fk
INNER JOIN tblLedger L ON L.engagementID_fk = En.engagementID
INNER JOIN tblMOT M ON M.motID = L.motID
INNER JOIN tblCommodities C ON C.commodityID = L.commodityID_fk
WHERE En.eventID = ?"$
Dim rs As ResultSet
rs=SQL.ExecQuery2(strQuery, Array As Object(evntID))

Also, keep in mind string literals and parameterized queries.

AkaiYon1003 · Feb 1, 2022

Mahares said:
For what it is worth, sometimes when you have a charged up query with many references, it will look tidier if you use aliases. Here is an example:

Code:

Dim strQuery As String = $"Select A.activityID , A.Activity , A.Status , C.commodity , M.mot , L.amount FROM tblEngagements En INNER JOIN tblEvents Ev ON Ev.eventID = En.eventID INNER JOIN tblActivities A ON A.activityID = En.activityID_fk INNER JOIN tblLedger L ON L.engagementID_fk = En.engagementID INNER JOIN tblMOT M ON M.motID = L.motID INNER JOIN tblCommodities C ON C.commodityID = L.commodityID_fk WHERE En.eventID = ?"$ Dim rs As ResultSet rs=SQL.ExecQuery2(strQuery, Array As Object(evntID))

Also, keep in mind string literals and parameterized queries.

This really helps a whole lot. Thank you so much for this!

Android Question Please help: My SQL query does not work

AkaiYon1003

Member

Lucas Siqueira

Active Member

Lucas Siqueira

Active Member

alwaysbusy

Expert

aeric

Expert

AkaiYon1003

Member

AkaiYon1003

Member

Mahares

Expert

AkaiYon1003

Member

Similar Threads