What is the appropriate context to reference fields from different tables? I am trying to do a join and the runtime is throwing an error with the tablename.columnname naming convention that I would expect to work.
The debug log looks like this:
The following code (without the JOIN or TableName.ColumnName naming convention) does work:
B4X:
'Dim html As String = DBUtils.ExecuteHtml(Main.SQL1, "SELECT CIDEventLog.EventTime, CIDEventLog.EventCode, CIDEventLog.EventPartition, CIDEventLog.EventZoneorUser, LookupCIDEvents.EventDescription FROM CIDEventLog INNER JOIN CIDEventLog on LookupCIDEvents.CIDEventCode=CIDEventLog.EventCode", Null)The debug log looks like this:
B4X:
Query=SELECT CIDEventLog.EventTime, CIDEventLog.EventCode, CIDEventLog.EventPartition, CIDEventLog.EventZoneorUser, LookupCIDEvents.EventDescription FROM CIDEventLog INNER JOIN CIDEventLog on LookupCIDEvents.CIDEventCode=CIDEventLog.EventCode
Error occurred on line: 240 (DBUtils)
java.sql.SQLException: [SQLITE_ERROR] SQL error or missing database (ambiguous column name: CIDEventLog.EventTime)
at org.sqlite.DB.newSQLException(DB.java:383)
at org.sqlite.DB.newSQLException(DB.java:387)
at org.sqlite.DB.throwex(DB.java:374)
at org.sqlite.NestedDB.prepare(NestedDB.java:134)
at org.sqlite.DB.prepare(DB.java:123)
at org.sqlite.PrepStmt.<init>(PrepStmt.java:42)
at org.sqlite.Conn.prepareStatement(Conn.java:404)
at org.sqlite.Conn.prepareStatement(Conn.java:399)
at org.sqlite.Conn.prepareStatement(Conn.java:383)
at anywheresoftware.b4j.objects.SQL.ExecQuery2(SQL.java:283)
at anywheresoftware.b4j.objects.SQL.ExecQuery(SQL.java:271)
at b4j.example.dbutils._executehtml(dbutils.java:455)
at sun.reflect.GeneratedMethodAccessor11.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:612)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:229)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:159)
at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:93)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:90)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:84)
at anywheresoftware.b4j.object.JServlet$Handle.run(JServlet.java:124)
at anywheresoftware.b4a.keywords.SimpleMessageLoop.runMessageLoop(SimpleMessageLoop.java:30)
at anywheresoftware.b4a.StandardBA.startMessageLoop(StandardBA.java:26)
at anywheresoftware.b4a.ShellBA.startMessageLoop(ShellBA.java:111)
at anywheresoftware.b4a.keywords.Common.StartMessageLoop(Common.java:131)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:301)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:159)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:93)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:90)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:84)
at b4j.example.main.main(main.java:29)
Error occurred on line: 17
java.lang.NullPointerException
at org.eclipse.jetty.server.ResponseWriter.write(ResponseWriter.java:248)
at anywheresoftware.b4j.object.JServlet$ServletResponseWrapper.Write(JServlet.java:337)
at sun.reflect.GeneratedMethodAccessor13.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at anywheresoftware.b4a.shell.Shell.runVoidMethod(Shell.java:656)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:232)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:159)
at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:93)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:90)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:84)
at anywheresoftware.b4j.object.JServlet$Handle.ru
n(JServlet.java:124)
at anywheresoftware.b4a.keywords.SimpleMessageLoop.runMessageLoop(SimpleMessageLoop.java:30)
at anywheresoftware.b4a.StandardBA.startMessageLoop(StandardBA.java:26)
at anywheresoftware.b4a.ShellBA.startMessageLoop(ShellBA.java:111)
at anywheresoftware.b4a.keywords.Common.StartMessageLoop(Common.java:131)
at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:301)
at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:159)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at anywheresoftware.b4a.BA.raiseEvent2(BA.java:93)
at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:90)
at anywheresoftware.b4a.BA.raiseEvent(BA.java:84)
at b4j.example.main.main(main.java:29)The following code (without the JOIN or TableName.ColumnName naming convention) does work:
B4X:
Dim html As String = DBUtils.ExecuteHtml(Main.SQL1, "SELECT EventTime, EventCode, EventPartition, EventZoneorUser FROM CIDEventLog", Null)