Android Question DBUtils ExecuteMemoryTable

[tdocs2]

Greetings.

I am stuck and cannot jump over a probably obvious stumbling block.

I am able to ExecuteMemoryTable successfully, but how do I read the list it returns? Code for ExecuteMemoryTable below. Of course, what I get in the log is : [Ljava.lang.String;@4568f288...

I know I am going to say I should have known that when I get an answer, but as of now, I cannot figure out how to do it. The great thing about this forum is that there is no such thing as an obvious question, and you do not get answers like - didn't you see it?

Thank you for any help on this matter.

Sub ExecuteMemoryTable(SQL As SQL, Query As String, StringArgs() As String, Limit As Int) As List
    Dim cur As Cursor
    If StringArgs <> Null Then
        cur = SQL.ExecQuery2(Query, StringArgs)
    Else
        cur = SQL.ExecQuery(Query)
    End If
    Log("ExecuteMemoryTable: " & Query)
    Dim Table As List
    Table.Initialize
    If Limit > 0 Then Limit = Min(Limit, cur.RowCount) Else Limit = cur.RowCount
    For row = 0 To Limit - 1
        cur.Position = row
        Dim values(cur.ColumnCount) As String
        For col = 0 To cur.ColumnCount - 1
            values(col) = cur.GetString2(col)
            Log("cur.GetString2(col) " & cur.GetString2(col))
        Next
        Table.Add(values)
    Next
    cur.Close
    Return Table
End Sub

 

[tdocs2]

Greetings.

I am stuck and cannot jump over a probably obvious stumbling block.

I am able to ExecuteMemoryTable successfully, but how do I read the list it returns? Code for ExecuteMemoryTable below. Of course, what I get in the log is : [Ljava.lang.String;@4568f288...

I know I am going to say I should have known that when I get an answer, but as of now, I cannot figure out how to do it. The great thing about this forum is that there is no such thing as an obvious question, and you do not get answers like - didn't you see it?

Thank you for any help on this matter.

Sub ExecuteMemoryTable(SQL As SQL, Query As String, StringArgs() As String, Limit As Int) As List
    Dim cur As Cursor
    If StringArgs <> Null Then
        cur = SQL.ExecQuery2(Query, StringArgs)
    Else
        cur = SQL.ExecQuery(Query)
    End If
    Log("ExecuteMemoryTable: " & Query)
    Dim Table As List
    Table.Initialize
    If Limit > 0 Then Limit = Min(Limit, cur.RowCount) Else Limit = cur.RowCount
    For row = 0 To Limit - 1
        cur.Position = row
        Dim values(cur.ColumnCount) As String
        For col = 0 To cur.ColumnCount - 1
            values(col) = cur.GetString2(col)
            Log("cur.GetString2(col) " & cur.GetString2(col))
        Next
        Table.Add(values)
    Next
    cur.Close
    Return Table
End Sub

I do not know if I am allowed to answer my question (or for Erel to have anticipated my question 2years ago)... This is the answer:

http://www.b4x.com/android/forum/threads/executememorytable-help.16418/#post-93478

AND I should have known THAT!

Best regards to all.

 

[ThRuST]

Do you mind sharing with us how you did since I am looking for solutions regarding DBUtils ExecuteMemoryTable. Even if this post is old the solution still works. Cheers

 

[LucaMs]

Do you mind sharing with us how you did since I am looking for solutions regarding DBUtils ExecuteMemoryTable. Even if this post is old the solution still works. Cheers

The "in memory table" consist of a list of arrays; so, each element of the list (an array) represents a record of the table.

Dim lstTable As List = ExecuteMemoryTable(...)
Dim Record() As String
Dim RecordIndex As Int = 3
Record = lstTable.Get(RecordIndex) ' <--- 4h record
Log(Record(0)) ' <---- first field of 4h record
Log(Record(1)) ' <---- second field of 4h record



[@tdocs2, where are you? ]

 

[ThRuST]

What about ExecuteMemoryTable(....) what would you put in the parameters? DBUtils requires an SQLite object and a SQL query must be used from what I've seen if so how do you display let's say 4 lists in the tableview (since arrays should be converted to lists to be shown in the tableview)? A little working source code example is always great otherwise questions for a working solution tends to be second best.

 

[ThRuST]

Do you mind putting together a Youtube tutorial on DBUtils? that would be awesome. Code snippet hints is useful but youtube is totally awesome this way, with JSON and MAPS that would be super. Thanks

 

[LucaMs]

What about ExecuteMemoryTable(....) what would you put in the parameters?

You can pass a "simple" query like:
Query = "SELECT * FROM TableName WHERE ID = 5"
and Null for StringArgs
ExecuteMemoryTable(SQL, Query, Null, 0)

or use a parameterized query:
Query = "SELECT * FROM TableName WHERE ID = ? AND Prize > ?"
ExecuteMemoryTable(SQL, Query, Array As String(5, 2000), 0)
5 and 2000 will automatically replace the question marks.

Limit = the number of records you want the query returns (0 means All)

 

[ThRuST]

Thanks, about the query, do I have to create a SQLite database object that resides in memory only or is a database file needed to be created for this to work? I don't see the association between the database object, and the query call with the lists involved. I'm used to ExecuteTableView which loads the contents from a db file on the drive, but I need to transfer contents from a mySQL database on a server someway and show the array (which I convert to lists) to show in the tableview. The changes are then made through jOkHttpUtils2 via PHP on the server. I think ExecuteMemoryTable could be a solution to not have to save the database to the drive and just have those lists displayed in the tableview from memory..

 

[ThRuST]

I can do it this way: Send 8 jobs with jOkHttpUtils2 to get the arrays. Convert them to lists and save them with a for each loop to an empty, temporary SQLite database file. As the mySQL database will include a column with their user ID, I can filter those out except for the current users Users ID, so everybodys hash password strings will be empty except for the current user. This way everybody will only be able to change their own data. I was looking for a solution to have those eight POST requests p1-p8 as arrays converted to lists shown in the tableview. That would make the query to filter hash strings from the SQLite database out, but at least that's a working option.

 

[LucaMs]

do I have to create a SQLite database object that resides in memory only or is a database file needed to be created for this to work?

The first parameter is only a reference to "some" SQLite db opened somewhere... locally, on device. Usually you should declare and initialize a SQLite DB in the Starter service (declare it as Public in the Process_Globals routine of the Starter service and initialized it the Service_Create routine).


[EDIT: above is the usal "method"; but you're using DBUtils, so you should create your SQLite DB with external tools, add it to the Files folder and use the DBUtils' CopyDBFromAssets method]

 

[LucaMs]

You could create a SQLite DB (using SQLite Expert Personal 5.x - free), create the same db structure of your MySql in it, copy it to the files folder, using it directly as I wrote above or using DBUtils' CopyDBFromAssets, then use OKHttpUtils and PHP to save-get what you need.

 

[LucaMs]

I'm assuming you can not install software on the machine where you have MySql; otherwise, you could use jRDC2.

 

[ThRuST]

yes since the jOkHttpUtils2 Jobs is really useful and once that tube is open anything is possible. DBUtils seems to to support various ways and until I level up my knowledge with MAPS and JSON I can make it work by saving to a local db file. I am using the SQL manager plugin for Firefox. However it is no longer supported in nerwer versions so installed an older version and disabled automatic updates so I can still use the plugin which is really good. I will look at SQL Expert Personal. Thanks LucaMs

 

[ThRuST]

I have signed up at one.com (in Denmark) to get my own domain with one mySQL database and it supports PHP 7.0 (also 7.1 beta). To use jOkHttpUtils2 to send POST requests to PHP scripts is the only option. Setting up my own server is an interesting solution but because I must guarantee uptime I will start with my registered domain.
To use one of my laptops as a server could be an option and run jRDC2. That would come with some advantages, but since I own a domain I can start with that.