Android Question [DBCommand] CreateCommand

[CR95]

In his tutorial "B4X] jRDC2 - B4J implementation of RDC", EREL uses an "object" as parameter for executing an SQL command.

Unfoetunately, I don't arrive to pass a simple string parameter. I get the correct id if I execute

Dim cmd As DBCommand = CreateCommand("select_artistid", Null)

with this line in the "config.properties" file :

sql.select_artistid=SELECT rowid FROM artiste WHERE ArtisteName = "Sting";

For transferring the parameter as a variable, I tried

Dim params() As Object = Array("Sting")
Dim cmd As DBCommand = CreateCommand("select_artistid", params)

with

sql.select_artistid=SELECT rowid FROM artiste WHERE ArtisteName = ?;

but it returns a bad rowid.
The Log in B4J Server shows it received

Execute: SELECT rowid FROM artiste WHERE ArtisteName = ?;

My understanding is that it cannot work if the question mark in the command received by B4J Server is not replaced by "Sting"
So something is wrong in my parameters list in B4A but what ?
Thanks for the help

 

[josejad]

Hum:

I use this sub in my app to get a record, and it works fine, the only difference I think is that I define parameters() as a String and not as an object.

Public Sub GetRecord (Command As String, parameters() As String) As ResumableSub
    Dim Answer As Map
    Answer.Initialize
    Dim req As DBRequestManager = CreateRequest
    Dim cmd As DBCommand = CreateCommand(Command, parameters)
    Wait For (req.ExecuteQuery(cmd, 0, Null)) JobDone(j As HttpJob)
    Answer.Put("Success", j.Success)
    If j.Success Then
        req.HandleJobAsync(j, "req")
        Wait For (req) req_Result(Res As DBResult)
        'work with result
        req.PrintTable(Res)
        Answer.Put("Message","Data have been read successfully")
        Answer.Put("Data",Res)
    Else
        Log("ERROR: " & j.ErrorMessage)
        Answer.Put("Error", j.ErrorMessage)
    End If
    j.Release
    
    'req.PrintTable(Res)
    Return Answer
End Sub

Usage

    Dim Parameters() As String = Array As String(etUser.Text.Trim, etPass.Text.Trim)
    Wait For(jRDC.GetRecord("Login", Parameters)) Complete (Answer As Map)
    If Answer.Get("Success") Then
        Dim l As List
        Dim rs As DBResult
        rs = Answer.Get("Data")
        ...
        ...
        ...
        End If
    Else
        xui.MsgboxAsync("Problem connecting: " & Answer.Get("Error"), "Error")
    End If

******** EDITED
I forgot to post the "Login" sentence in config.properties

sql.Login = SELECT * FROM mydatabase.users WHERE `email` = ? AND `password` = md5(?)

 

[OliverA]

What database are you using?

 

[CR95]

Thanks OliverA
I am using SQLite3 (on Raspberry)

If I try something similar to your usage (Login)

Dim params() As String = Array As String("Sting")

it does not return the correct id (and the server still receives the command with the question mark)

 

[OliverA]

Just out of curiosity, remove the semicolon from your query string in config.properties. Which jRDC2 server are you using? Are you compiling on the Pi?

 

[CR95]

I made a test after removing the semicolon at the end of config.properties, and the problem is still there.
I am using jRDC 2.22
I am compiling on a Windows7 system and executing on Raspberry. Transfert is made by B4J bridge

 

[OliverA]

What database are you using?

 

[CR95]

I am using SQLite3 (on Raspberry)
I think the issue is not on the server side as the command received contains the question mark (should be replaced by the searched string)
Thanks for your help

 

[josejad]

Just to dismiss...

- Download HeidiSQL in your laptop and configure your connection
- Go to the query tab
- Mark "Bind parameters" on the right window
- Press + and add, for example: "test" variable, with value "Sting"
- Write your sentence like this: "SELECT rowid FROM artiste WHERE ArtisteName = ':test'"
- Test... what do you get?

 

[aeric]

it returns a bad rowid

What do you mean by bad rowid? Wrong value or null ?

 

[CR95]

Sorry guys for disturbing you with my stupid error
This is the question of 'aeric' who helped me to find the solution : what I said as a bad rowid was the good rowid !
My error was to wait for a rowid found in the tables of a previous version of the database
FYI, the response is correct with the two lines hereafter

Dim params() As Object = Array As String("Sting")
Dim params() As String = Array As String("Sting")

Thanks to all

 

[OliverA]

I am using SQLite3 (on Raspberry)

I am using jRDC 2.22

Unmodified jRDC 2.22 with SQLite? Interesting. Have you tried multiple access to see if you encounter any locking issues? There is a modified version of jRDC2 that is designed to work with SQLite. Just search the forum...

 

[CR95]

Unmodified jRDC 2.22 with SQLite? Interesting. Have you tried multiple access to see if you encounter any locking issues? There is a modified version of jRDC2 that is designed to work with SQLite. Just search the forum...

No, I am still testing and I have only 1 user connected on the server.
I will look for this special SQLite version

 

[CR95]

@OliverA
May be I made an error.
After starting, the log of B4J indicates "jRDC is running (version = 2.22)"
but the library loaded in additional libraries is "sqlite-jdbc-3.7.2"

 

[josejad]

After starting, the log of B4J indicates "jRDC is running (version = 2.22)"
but the library loaded in additional libraries is "sqlite-jdbc-3.7.2"

Don't worry, it's not related. 2.22 is the version of the jRDC2 server, not the library

You can see it in the B4J code

Public const VERSION As Float = 2.22

 

[CR95]

May I come back with a new question (still concerning the parameters sent to the server)
1°) With sqlite3, if I send an sql request, I get a correct response :

2°) If my B4A Client send a similar request with ONE question mark in the SQL command, it works

    Dim params() As String = Array As String("1984")
    Dim cmd As DBCommand = CreateCommand("select_double", params)

with this line in "config properties"

sql.select_double=SELECT TrackAdresse FROM track WHERE TrackTitre = ?

3°) BUT if I send a request with TWO question marks

    Dim params() As String = Array As String("TrackTitre","1984")
    Dim cmd As DBCommand = CreateCommand("select_double", params)

with this line in "config properties"

sql.select_double=SELECT TrackAdresse FROM track WHERE ? = ?

the server does not find the value and returns nothing
Is there a limitation for the number of question marks with SQLite ?
If you could help

 

[OliverA]

The question marks are limited to values in a query. You cannot use them as parts of a query that relate to table/column names. That is not a limitation of jRDC2, that’s how parameterized queries work as it relates to JDBC.

 

[CR95]

Thanks OliverA
I will say goodbye to a paramerized subroutine for invoking SQL commans

 

[aeric]

Thanks OliverA
I will say goodbye to a paramerized subroutine for invoking SQL commans

Why? Prepared statement/parameterized query is a recommended way compared to sending the query without using the ? mark. You can use any number of question marks in your query.
Example:

sql.select_trackaddress=SELECT TrackAddress FROM track WHERE ReleasedYear = ? AND Genre = ?

 

[MrKim]

Thanks OliverA
I will say goodbye to a paramerized subroutine for invoking SQL commans

Don't do that. It is the way to do it. Once you get the hang of it, it is not difficult.
Array as OBJECT sould work fine. Keep in mind you may have more that one parameter and they might be different data types in which case the array will need to be Object.
I even have queries with subqueries that require the same parameter. I found it easier to pass the same parameter twice rather than set up a variable on the server side.