Android Question Problem with syntax connecting Android to MySQL Database

[ciginfo]

Hello,
If I refer at the tuto https://www.b4x.com/android/forum/threads/connect-android-to-mysql-database-tutorial.8339/ it seems it does not need "sql" library. Isn't it ?

I create on the server, as shown, a php script file, essai.php

Look at my code below, the Buttons-click : I do not find the exact syntax for:
ExecuteRemoteQuery("SELECT * FROM MyTable WHERE champs1 ='" & Val1 & "' ,ESSAI_3)
ExecuteRemoteQuery("SELECT COUNT MyTable ................,ESSAI_4)
Can you help me and indicate the good syntax? I find many samples but they do not run.

Thank you

Sub ExecuteRemoteQuery(Query As String, JobName As String)
    Dim job As HttpJob
    job.Initialize(JobName, Me)
    job.PostString("https://myTable/applis/essai.php", Query)
End Sub

Sub JobDone(Job As HttpJob)
    ProgressDialogHide
    If Job.Success Then
        Dim res As String
        res = Job.GetString
        Log("Response from server: " & res)
        Dim parser As JSONParser
        parser.Initialize(res)
        Select Job.JobName
            Case ESSAI_1
                Dim ESSAI1 As List
                ESSAI1 = parser.NextArray 'returns a list with maps
                For i = 0 To ESSAI1.Size - 1
                    Dim m As Map
                    m = ESSAI1.Get(i)
                    Dim valeur1, valeur2, valeur3 As string
                    valeur1 = m.Get("champs1")
                    valeur2 = m.Get("champs2")
                    valeur3 = m.Get("champs3")
                    Log(valeur1 & " " & valeur2 & " " & valeur3)
                Next
            Case ESSAI_2
                MsgboxAsync("It runs fine", "Result")

            Case ESSAI_3
                Dim ESSAI3 As List
                ESSAI3 = parser.NextArray 'returns a list with maps
                For i = 0 To ESSAI3.Size - 1
                    Dim m As Map
                    m = ESSAI3.Get(i)
                    Dim valeur1, valeur2, valeur3 As string
                    valeur1 = m.Get("champs1")
                    valeur2 = m.Get("champs2")
                    valeur3 = m.Get("champs3")
                    Log(valeur1 & " " & valeur2 & " " & valeur3)
                Next

            Case ESSAI_4
                MsgboxAsync("It runs fine", "Result")

        End Select
    Else
        Log(Job.ErrorMessage)
        ToastMessageShow("Error: " & Job.ErrorMessage, True)
    End If
    Job.Release
End Sub

Sub Button1_Click
    'It Runs fine
    ExecuteRemoteQuery("SELECT * FROM MyTable WHERE champs1 = 1",ESSAI_1 )
End Sub

Sub Button2_Click
    Dim val1, val2, val3 as String
    Val1 = "Hello"
    val2 = "The"
    val3 = "World"

    'It Runs fine
    ExecuteRemoteQuery("INSERT INTO MyTable (champs1, champs1, champs1) VALUES ('" & Val1 & "', '" & Val2 & "','" & val3')",ESSAI_2)
End Sub

Sub Button3_Click
    Dim val1, val2, val3 as String
    Val1 = "Hello"
    val2 = "The"
    val3 = "World"

    'Synta error
    ExecuteRemoteQuery("SELECT * FROM MyTable WHERE champs1 ='" & Val1 & "' ,ESSAI_3)

    'Synta error
    ExecuteRemoteQuery("SELECT * FROM MyTable WHERE champs1 ='" & Val1 & "' ,ESSAI_3)
    
    ' Here, Erreur, l'objet a été transformé en variable string (Idem if I remplace Array as string by Array as Object)
    ExecuteRemoteQuery("SELECT * FROM MyTable where champs1 =  ? and champs2 = ?", Array As String (val1, val3), CHECK)
End Sub

Sub Button4_Click
    'IWhat is the syntax ?
    ExecuteRemoteQuery("SELECT COUNT MyTable ................,ESSAI_4)
End Sub

 

[DonManfred]

job.PostString("https://myTable/applis/essai.php", Query)

This can NEVER work!

Put a real URL to the php in there

Away from that the Tutorial is 10 Years old.

Search the forum; there are some newer tutorials to work with PHP.

Start with understanding what a full URL does contain.

 

[ciginfo]

This can NEVER work!

Put a real URL to the php in there

Away from that the Tutorial is 10 Years old.

Search the forum; there are some newer tutorials to work with PHP.

Start with understanding what a full URL does contain.

No i put a real URL and it works with. The url above is a factice example and the syntaxes for button1_click and button2_click in the joined code run fine.
But i don't find syntax for button3. and button4. I have searched on the forum, I found with sql library but it does not run.
Look for example the syntax at button3_click, Why it does not work ?
Thank you

 

[Mahares]

ExecuteRemoteQuery("SELECT * FROM MyTable WHERE champs1 ='" & Val1 & "' ,ESSAI_3)

Shouldn't be like this although parameterized queries are always safer:

ExecuteRemoteQuery("SELECT * FROM MyTable WHERE champs1 ='" & Val1 & "'" ,ESSAI_3)

 

[ciginfo]

Shouldn't be like this although parameterized queries are always safer:

ExecuteRemoteQuery("SELECT * FROM MyTable WHERE champs1 ='" & Val1 & "'" ,ESSAI_3)

OK thank you

 

[ciginfo]

And with 2 conditions like this

ExecuteRemoteQuery("SELECT * FROM MyTable WHERE champs1 ='" & Val1 & "' And champs2 = '"& Val2 &"' ,ESSAI_3)

Syntax error in this code, what is the Right syntax ?

 

[josejad]

You're missing the last " to close the quotes in &"'. It should be &"'"

ExecuteRemoteQuery("SELECT * FROM MyTable WHERE champs1 ='" & Val1 & "' And champs2 = '"& Val2 &"'" ,ESSAI_3)

 

[josejad]

It's always easier with Smart String Literals

 

[ciginfo]

You're missing the last " to close the quotes in &"'. It should be &"'"

ExecuteRemoteQuery("SELECT * FROM MyTable WHERE champs1 ='" & Val1 & "' And champs2 = '"& Val2 &"'" ,ESSAI_3)

Oh yes, sorry for the mistake, and thanks for the inconvenience.

 

[ciginfo]

I have another question, but maybe I have to start a new thread. I recently started using SQL, I don't know this part well, I'm Newbee.!!!
How can I get the number of rows in the following request.

ExecuteRemoteQuery("SELECT * FROM MyTable WHERE champs1 ='" & Val1 & "' And champs2 = '"& Val2 &"'" ,ESSAI_3)

I know how to retrieve the content of fields with the request "SELECT * FROM ........" but I cannot retrieve the number of rows in "SELECT COUNT ........"
Thank you

 

[Mahares]

ExecuteRemoteQuery("SELECT * FROM MyTable WHERE champs1 ='" & Val1 & "' And champs2 = '"& Val2 &"'" ,ESSAI_3)

Instead of "SELECT * use "SELECT COUNT (*)
The rest of your query is the same as you have above

 

[josejad]

BTW, as DonManfred told you, the tutorial is 10 years old.
At least you should use OKHttpUtils with Wait For and not use JobDone.

 

[ciginfo]

Instead of "SELECT * use "SELECT COUNT (*)
The rest of your query is the same as you have above

Yes, I understand but what is the code to retrieve the result (number of rows) in the Sub jobdone case ESSAI_3
Thank you

 

[josejad]

Sorry, I don't know what returns the php script. What's the value of ESSAI3.Size? Isn't the number of fields you get?

Remember, don't use JobDone, use Wait For

 

[ciginfo]

Yes, but here is exactly my problem.
In the code below I want to retrieve the value of certain fields from my table

ExecuteRemoteQuery("SELECT * FROM MyTable WHERE champs1 = 1",ESSAI_1 )

And here is the code used in in case ESSAI_1 of Jobdone to retrieve them

Sub JobDone(Job As HttpJob)
    ProgressDialogHide
    If Job.Success Then
        Dim res As String
        res = Job.GetString
        Log("Response from server: " & res)
        Dim parser As JSONParser
        parser.Initialize(res)
        Select Job.JobName
            Case ESSAI_1
                Dim ESSAI1 As List
                ESSAI1 = parser.NextArray 'returns a list with maps
                For i = 0 To ESSAI1.Size - 1
                    Dim m As Map
                    m = ESSAI1.Get(i)
                    Dim valeur1, valeur2, valeur3 As string
                    valeur1 = m.Get("champs1")
                    valeur2 = m.Get("champs2")
                    valeur3 = m.Get("champs3")
                    Log(valeur1 & " " & valeur2 & " " & valeur3)
                Next

Now if I use the code below to get the count of records, what is the code I have to write in case ESSAI_3 of Jobdone to get the count of records

ExecuteRemoteQuery(" SELECT COUNT (*)  FROM MyTable WHERE champs1 ='" & Val1 & "' And champs2 = '"& Val2 &"'" ,ESSAI_3)

What is the code in Jobdone to get the count of records ???

Sub JobDone(Job As HttpJob)
        Select Job.JobName
            ???????......
            Case ESSAI_3
               ?????....
        End xelect

 

[Mahares]

What is the code in Jobdone to get the count of records

Did you try something like this:

Case ESSAI_3
    Dim Rt As List = parser.NextArray
    For Each MyList As List In Rt
        Dim c As Long = MyList.Get(0)
        Log(c)  'displays the record count
    Next

 

[ciginfo]

Did you try something like this:

Case ESSAI_3
    Dim Rt As List = parser.NextArray
    For Each MyList As List In Rt
        Dim c As Long = MyList.Get(0)
        Log(c)  'displays the record count
    Next

Alas no. It crashes with this message : "java.lang.ClassCastException: anywheresoftware.b4a.objects.collections.Map$MyMap cannot be cast to java.util.List"

 

[Mahares]

Alas no. It crashes with this message

Sorry, I am not sure what the problem is. I hope someone will be able to spot something in your code and help.

 

[DonManfred]

Alas no. It crashes with this message : "java.lang.ClassCastException: anywheresoftware.b4a.objects.collections.Map$MyMap cannot be cast to java.util.List"

you are trying to use a value as a list but there is only a map which returns.

How does "the COMPLETE json you get as result" look like?

 

[ciginfo]

The problem is simple. I cannot display the number of records contained in a remote PfpMyAdmin database.
If I go to the Erel tutorial at this address: https://www.b4x.com/android/forum/threads/connect-android-to-mysql-database-tutorial.8339/#content he explains how to connect to a table and collect the value of the different fields in the table, but not how to get the number of records in the table.
I modified its Mysql.zip attachment to show you what I want to do.
Look at the zip attachement and run the program . Click on Button1 should display the number of records in the table, but code to add in the "Select COUNT_ROWS" of the Sub Jobdone is missing. I'm just looking for this exact code. Who knows it?
Thank you