B4J Question Ambiguous Table error in SQLite query

[Steve Piehl]

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.

    '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:

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:

    Dim html As String = DBUtils.ExecuteHtml(Main.SQL1, "SELECT EventTime, EventCode, EventPartition, EventZoneorUser FROM CIDEventLog", Null)

 

[imbault]

there is a mistake in your query

'Dim html As String = DBUtils.ExecuteHtml(Main.SQL1, "SELECT CIDEventLog.EventTime, CIDEventLog.EventCode, CIDEventLog.EventPartition, CIDEventLog.EventZoneorUser, LookupCIDEvents.EventDescription FROM CIDEventLog INNER JOIN LookupCIDEvents on LookupCIDEvents.CIDEventCode=CIDEventLog.EventCode", Null)

 

[Squiffy]

You reference CIDEventLog & LookupCIDEvents tables in the SELECT, but only have CIDEventLog in the FROM clause. Not sure what effect that would have on SQLite. I always thought you needed to include all referenced tables in the FROM clause.

Definitely bombs out on MySQL.

 

[rboeck]

For complex queries i try to create the query with sqlite expert on the pc; so i reduce the programming time...