B4J Question sending Resultset over network connection

[Patrick Clark]

I have a server application that handles requests for information and return the result to a client application

I am using MQTT as my communication method but the same question applies to asyncstreams

One of the requests is to query a database and return the results to the client

I can't use lists or maps etc as the query format is unknow to the server and the results can only be interpreted by the client

so my question is: How can I send a reultset over a network?

 

[MicroDrie]

If you are already able to provide information in an any-to-any MQTT network, what prevents you from sending the result of a database query via a MQTT client? Is it the query itself, is it the database design or do you want to use a point-to-point connection, or is it something else?

 

[Patrick Clark]

If you are already able to provide information in an any-to-any MQTT network, what prevents you from sending the result of a database query via a MQTT client? Is it the query itself, is it the database design or do you want to use a point-to-point connection, or is it something else?

The data to send from the MQTT Client must be an Array of Btyes, not a Resultset

The Client publishes an SQL request to the MQTT Broker with a Topic of "GETDATA"
The Server is subscribing to "GETDATA"
The Server processed the SQL with a local SQLLite database and gets a ResultSet

What I then want to do is have the server publish the ResultSet with a topic of "ClientID/RESULT"

The client is subscribed to "ClientID/RESULT"

The client can then process the ResultSet

I have tried

    Dim sql As SQL
    sql.InitializeSQLite("D:\Users\Patrick\OneDrive\Documents\B4X", "MQTTServer.db", False)

    Dim rs As ResultSet = sql.ExecQuery("SELECT * from TesTable")
    Dim ser As B4XSerializator
    Dim ResultPackage() As Byte
    ResultPackage = ser.ConvertObjectToBytes(rs)

but i get
java.lang.RuntimeException: Cannot serialize object: org.sqlite.RS@f16dd6d

 

[DonManfred]

You can not serialize a Object.
You need to build your own List or Map or json (whatever you are most familar with) based on the content of the resultset and serialize this List/Map/json to transport.

 

[Patrick Clark]

You can not serialize a Object.
You need to build your own List or Map based on the content of the resultset and serialize this List/Map to transport.

Thanks, that is the conclusion i have just come to.

I am building a ResultSet2Map sub right now

unless you know of one that already exists

 

[Patrick Clark]

You can not serialize a Object.
You need to build your own List or Map or json (whatever you are most familar with) based on the content of the resultset and serialize this List/Map/json to transport.

Given that I don't know what the columns in the result set will be I have to workout what the type of each column is

eg:
rs.GetColumnName(0) will give me the name of the column to use as a key in the map

but the data value might be a string or int or blob etc

How can i determine the type of the column in the ResultSet?

This will give me a list of maps (each map have keys for each column) but this assumes that all columns are (or can be cast to) Strings

    Dim l As List
    l.Initialize
    Dim row As Map
    Do While rs.NextRow
        row.Initialize
        For c = 0 To rs.ColumnCount -1
            row.Put(rs.GetColumnName(c), rs.GetString2(c))
        Next
        l.Add(row)
    Loop

 

[OliverA]

Check out the source code of jRDC2. It converts the result set to a custom type. Look at the rdchandler code. Could reuse instead of re-invent

 

[Patrick Clark]

Thanks, I'll take a look

 

[Patrick Clark]

@OliverA exactly what I needed, thank you