Android Question Check user city and country in MYSQL

Ayabonga · May 2, 2020

Hello guys,

I am new from MYSQL and I have created a localhost database in PHPMyAdmin. I want to check if a city and country exist in my database. I have built up from JRDC2 tutorial.

Can you please advise:

SQL Command in the Config File:

sql.check_user =SELECT IF EXIST *FROM cities WHERE country = ? AND city = ?

B4A Code:

B4X:

Sub btn_checkUser_Click
    
    If editCountry.Text = "" Or editCity.Text = "" Then
        Msgbox("country or city data is missing", "Missing data")
        Return
    End If
    
    Dim req As DBRequestManager = CreateRequest
    Dim cmd As DBCommand = CreateCommand("check_user", Array As String(editCountry.Text, editCity.Text))
    Wait For (req.ExecuteQuery(cmd, 0, Null)) JobDone(j As HttpJob)
    If j.Success Then
        req.HandleJobAsync(j,"req")
        Wait For (req) req_Result(res As DBResult)
        Dim row() As Object = res.Rows.Get(0)
        Dim longitude As Double = row(2)
        Dim latitude As Double = row(res.Columns.Get("latitude"))
        lblResult.Text = $"$1.3{latitude} / $1.3{longitude}"$
        Log("Country and City Exist!")
    
Else
        Log("County or City Not exist!")
        Log("Error: " & j.ErrorMessage)
    End If
    j.Release
End Sub

Erel · May 3, 2020

Code with Msgbox = broken code. First step is to fix it: https://www.b4x.com/android/forum/threads/116651/#content

What is the problem? What happens when you run the code?

Ayabonga · May 3, 2020

Erel said:
Code with Msgbox = broken code. First step is to fix it: https://www.b4x.com/android/forum/threads/116651/#content

What is the problem? What happens when you run the code?

I get the error below, I believe its SQL command that is not structured properly and I need assistance in that. I want to check if a country and city typed in the text box where exists or not in my database.

Below is Log when I press the button:

Logger connected to: Hisense Hisense U989
--------- beginning of main
*** Service (starter) Create ***
** Service (starter) Start **
** Activity (main) Create, isFirst = true **
** Activity (main) Resume **
*** Service (httputils2service) Create ***
** Service (httputils2service) Start **
ResponseError. Reason: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXIST *FROM cities WHERE country = 'South Africa ' AND city = 'PE'' at line 1, Response: <html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8"/>
<title>Error 500 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXIST *FROM cities WHERE country = 'South Africa ' AND city = 'PE'' at line 1</title>
</head>
<body><h2>HTTP ERROR 500</h2>
<p>Problem accessing /rdc. Reason:
<pre> com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXIST *FROM cities WHERE country = 'South Africa ' AND city = 'PE'' at line 1</pre></p><hr><a href="http://eclipse.org/jetty">Powered by Jetty:// 9.4.z-SNAPSHOT</a><hr/>
</body>
</html>
County or City Not exist!
Error: <html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8"/>
<title>Error 500 com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXIST *FROM cities WHERE country = 'South Africa ' AND city = 'PE'' at line 1</title>
</head>
<body><h2>HTTP ERROR 500</h2>
<p>Problem accessing /rdc. Reason:
<pre> com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXIST *FROM cities WHERE country = 'South Africa ' AND city = 'PE'' at line 1</pre></p><hr><a href="http://eclipse.org/jetty">Powered by Jetty:// 9.4.z-SNAPSHOT</a><hr/>
</body>
</html>

Erel · May 3, 2020

We cannot guess the errors. This is very important information.

The error is:
1. You have an error in your SQL syntax;
2. County or City Not exist!

Ayabonga · May 3, 2020

Erel said:
We cannot guess the errors. This is very important information.

The error is:
1. You have an error in your SQL syntax;
2. County or City Not exist!

Yes I know there is an error in my SQL syntax, I need assistance in the structuring of this command: sql.check_user =SELECT IF EXIST *FROM cities WHERE country = ? AND city = ?

I created that Log : "Country or city not exist". The country and city exist in my database please check the attached.

udg · May 3, 2020

Maybe this one could help (about the proper syntax).

Erel · May 3, 2020

I would have used:

B4X:

sql.check_user =SELECT Count(*) FROM cities WHERE country = ? AND city = ?

OliverA · May 3, 2020

Since you are using PHPMyAdmin (at least that what the screenshot looks like), why not use it to test your SQL queries first? Instead of the ?'s, just put some data in it. Once the query works there, transform it to the parameterized query you want to use with jRDC2. In your case, I think what you were trying to achieve was

B4X:

sql.check_user =SELECT * FROM cities WHERE EXISTS (SELECT * FROM cities WHERE country = ? AND city = ?)

Note: All SQL statement provided here are untested (test with PHPMyAdmin as stated above).

For your purposes you can just use (and skip the whole EXISTS stuff):

B4X:

sql.check_user =SELECT * FROM cities WHERE country = ? AND city = ?

As is, your code does not check for the fact a ResultSet may be emtpy. Just because j.Success returns True, does not mean that Records have be returned. It just means that the call to the jRDC2 server was executed and returned some data. To see if any records have been returned, you can first see if any Rows have been returned.

With this new/simpler query you can do something like

B4X:

    Dim req As DBRequestManager = CreateRequest
    Dim cmd As DBCommand = CreateCommand("check_user", Array As String(editCountry.Text, editCity.Text))
    Wait For (req.ExecuteQuery(cmd, 0, Null)) JobDone(j As HttpJob)
    If j.Success Then ' Call to jRDC2 server was successful
        req.HandleJobAsync(j,"req")
        Wait For (req) req_Result(res As DBResult)
        If res.Rows.Size > 0 Then ' One or more records have been returned
           Dim row() As Object = res.Rows.Get(0) ' Let's fetch the first record information
           Dim longitude As Double = row(2) ' Just make sure that at least 3 columns are returned and that the 3rd column really has the info you want
           Dim latitude As Double = row(res.Columns.Get("latitude"))
           lblResult.Text = $"$1.3{latitude} / $1.3{longitude}"$
           Log("Country and City Exist!")
        Else ' No records were returned
           Log("Country or City Not exist!")
        End If
    Else ' Something went wrong with the call to the jRDC2 server
        Log("Something went wrong!!!!!!")
        Log("Error: " & j.ErrorMessage)
    End If
    j.Release

Android Question Check user city and country in MYSQL

Ayabonga

Member

Erel

B4X founder

Ayabonga

Member

Erel

B4X founder

Ayabonga

Member

Attachments

udg

Expert

Erel

B4X founder

OliverA

Expert

Similar Threads