B4J Question B4J Create List from MySQL SELECT

[Declan]

I am attempting to create a list from a SQL query:

Dim table As List = PushDB.ExecQuery2("SELECT push_messages.msg_id, text FROM push_messages, push_messages_to_deliver WHERE push_messages.msg_id = push_messages_to_deliver.msg_id AND user_id = ?", Array As String(id)) <<<<<-- (Error evaluating expression.)

    If table.Size > 0 Then 
        Dim msgs As List
        msgs.Initialize
        For Each row() As String In table
            msgs.Add(row(0))
            msgs.Add(row(1))
        Next
        CallSubDelayed2(pb, "SendMessages", msgs)
    End If

The SQLite query using DBUtils is:

Dim table As List = DBUtils.ExecuteMemoryTable(PushDB, "SELECT push_messages.msg_id, text FROM push_messages, push_messages_to_deliver WHERE push_messages.msg_id = push_messages_to_deliver.msg_id AND user_id = ?", Array As String(id), 0)

This works with a SQLite DB.

Using the SQL query above, I get the following error:

Waiting for debugger to connect...
Program started.
2017-02-28 11:06:58.502:INFO::main: Logging initialized @1138ms
2017-02-28 11:06:58.629:INFO:oejs.Server:main: jetty-9.3.z-SNAPSHOT
2017-02-28 11:06:58.701:INFO:oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@4cf777e8{/,file:///C:/B4J-Server-Push/ServerExampleNoMySQL/Objects/www/,AVAILABLE}
2017-02-28 11:06:58.703:INFO:oejs.AbstractNCSARequestLog:main: Opened C:\B4J-Server-Push\ServerExampleNoMySQL\Objects\logs\b4j-2017_02_28.request.log
2017-02-28 11:06:58.836:INFO:oejs.ServerConnector:main: Started ServerConnector@49fc609f{HTTP/1.1,[http/1.1]}{0.0.0.0:32009}
2017-02-28 11:06:58.837:INFO:oejs.Server:main: Started @1474ms
Emulated network latency: 200ms
Server started
NewConnection: 358726061334668
MyCount: 1
java.lang.NullPointerException
    at anywheresoftware.b4j.object.WebSocketModule$Adapter.onWebSocketText(WebSocketModule.java:121)
    at org.eclipse.jetty.websocket.common.events.JettyListenerEventDriver.onTextMessage(JettyListenerEventDriver.java:189)
    at org.eclipse.jetty.websocket.common.message.SimpleTextMessage.messageComplete(SimpleTextMessage.java:69)
    at org.eclipse.jetty.websocket.common.events.AbstractEventDriver.appendMessage(AbstractEventDriver.java:66)
    at org.eclipse.jetty.websocket.common.events.JettyListenerEventDriver.onTextFrame(JettyListenerEventDriver.java:158)
    at org.eclipse.jetty.websocket.common.events.AbstractEventDriver.incomingFrame(AbstractEventDriver.java:162)
    at org.eclipse.jetty.websocket.common.WebSocketSession.incomingFrame(WebSocketSession.java:367)
    at org.eclipse.jetty.websocket.common.extensions.ExtensionStack.incomingFrame(ExtensionStack.java:214)
    at org.eclipse.jetty.websocket.common.Parser.notifyFrame(Parser.java:220)
    at org.eclipse.jetty.websocket.common.Parser.parse(Parser.java:256)
    at org.eclipse.jetty.websocket.common.io.AbstractWebSocketConnection.readParse(AbstractWebSocketConnection.java:663)
    at org.eclipse.jetty.websocket.common.io.AbstractWebSocketConnection.onFillable(AbstractWebSocketConnection.java:493)
    at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:261)
    at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)
    at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:75)
    at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceAndRun(ExecuteProduceConsume.java:213)
    at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.execute(ExecuteProduceConsume.java:101)
    at org.eclipse.jetty.io.ManagedSelector.run(ManagedSelector.java:136)
    at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:654)
    at org.eclipse.jetty.util.thread.QueuedThreadPool$3.run(QueuedThreadPool.java:572)
    at java.lang.Thread.run(Thread.java:745)

 

[Erel]

SQL.ExecQuery2 returns a ResultSet.

See this tutorial: https://www.b4x.com/android/forum/threads/sql-tutorial.35185/#content

 

[Declan]

Sorry, couldn't find anything the the above link that pertained to problem.

 

[udg]

Hi @Declan ,
Erel pointed to that thread so that you could read the following snippet:

Dim RS As ResultSet = SQL1.ExecuteQuery(...)
Do While RS.NextRow
  Log(RS.GetString("col1"))
Loop
RS.Close

Here you can see that ExecuteQuery (and ExecuteQuery2 too) return an object of type ResultSet; you iterate on it and use methods like GetString(columName) to read the values returned for each record.
You are obviously free to initialize a list or other object and copy there those values if your program expects that kind of data structure.

udg

 

[Declan]

Thanks.
This is working:

Dim RS As ResultSet = PushDB.ExecQuery("SELECT push_messages.msg_id, text FROM push_messages, push_messages_to_deliver WHERE push_messages.msg_id = push_messages_to_deliver.msg_id AND user_id = '" & id & "'") 'Array As String(id))
Do While RS.NextRow
  Log(RS.GetString("msg_id"))
  Log(RS.GetString("text"))
        Dim msgs As List
        msgs.Initialize
            msgs.Add(RS.GetString("msg_id"))
            msgs.Add(RS.GetString("text"))
        CallSubDelayed2(pb, "SendMessages", msgs)
Loop
RS.Close

Now I will need to convert the following to insert into MySQL:

    DBUtils.InsertMaps(PushDB, "push_messages", Array As Object(CreateMap("text": Text, "msg_id": lastMsgId, "time": DateTime.Now)))

 

[Harris]

As I have often pondered, why do we have different methods that return a ResutSet as opposed to a List?
Each list row is then converted to a map. Each map key is the field name. Each map value is the field value.

I use both (that work) but only following examples due to lack of concise definition / guidance / understanding and WHY?

A ResultSet is new to my method of processing Select results.

 

[Erel]

Returning a List means that the whole available data should be read and stored in a list. In some cases it makes sense however there are cases where you don't really want to read all the results or that there are too many to store them all in a list.

 

[Harris]

Returning a List means that the whole available data should be read and stored in a list. In some cases it makes sense however there are cases where you don't really want to read all the results or that there are too many to store them all in a list.

That's where the "Where" clause filters. Granted - it could return a huge list.

The above example of ResultSet uses a "where" in the select. Seems it too could return a huge ResultSet.

What is (type of object) : (map, list, other?)
Dim RS As ResultSet


Thanks

 

[Erel]

Seems it too could return a huge ResultSet.

With ResultSet you can safely go over very large results as it doesn't read everything into memory.