Android Question Please help: My SQL query does not work

A

AkaiYon1003

Member
Below is my query:

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 &"")

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


Lastly, below is the error I'm having:


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

Please let me know how I can fix this. Thank you very much.
 
Sort by date Sort by votes
Lucas Siqueira

Lucas Siqueira

Active Member
Licensed User
Longtime User
AkaiYon1003 said:
Below is my query:

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 &"")

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

Lastly, below is the error I'm having:


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

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

have you tested your select directly in your database?
 
Upvote 0
Lucas Siqueira

Lucas Siqueira

Active Member
Licensed User
Longtime User
AkaiYon1003 said:
Below is my query:

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 &"")

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

Lastly, below is the error I'm having:


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

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

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
 
Upvote 0
alwaysbusy

alwaysbusy

Expert
Licensed User
Longtime User
This part definitely looks weird in your query:

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

should be something like:
B4X: 
... INNER JOIN tblEvents ON tblEngagements.eventID WHERE tblEngagements.eventID = 1

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

Alwaysbusy
 
Upvote 0
M

Mahares

Expert
Licensed User
Longtime User
AkaiYon1003 said:
I actually found my clumsiness
Click to expand...

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:
B4X: 
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.
 
Upvote 0
A

AkaiYon1003

Member
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:
B4X: 
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.
Click to expand...
This really helps a whole lot. Thank you so much for this!
 
Upvote 0
You must log in or register to reply here.

Similar Threads

R
  • Question
Android Question Variable chkFilterSQL was not initialized. (warning #11)
Replies
8
Views
405
RB Smissaert
R
aeric
  • Question
Android Question (Solved) B4XRadioButton inside xCustomListView
Replies
9
Views
1K
aeric
aeric
udg
  • Question
Android Question [solved] NFC Tag (not NDEF) reading
Replies
1
Views
4K
udg
udg
advansis
  • Question
Android Question callsub and polymorphism
Replies
7
Views
2K
advansis
advansis
MarcoRome
  • Question
Android Question [RESOLVED] RegexBuilder Match
Replies
1
Views
1K
MarcoRome
MarcoRome
Top