B4J Question Using a field from a form to query a remote sql Database [Solved]

Peter Lewis · Mar 31, 2020

Hi Guys

I have made a registration form to register on my website. In the SQL I have a AUTONUMBER field called userID

My code does register as per normal but the AUTONUMBER field is provided by the MySQL so the code does not know what this user
ID is. I want to immediately query the database to get this userID and store it locally with all the other user info.

I have tried a few options but it seems like I just do not have the format to do it. I managed to get all the userIDs of all the registered users (first box below)and I can filter that but it seems like a long way round.

Brings back all the userIDs of all the registered users:

    Dim Cursor As ResultSet
    Cursor = Sql2.ExecQuery("SELECT userID FROM User_db")
    Do While Cursor.NextRow
        Log(Cursor.GetString("userID"))
  
    Loop

The best way would for me to send a query to the database with the Loginname and request back the userID then save it in the local database.

trying to filter out only 1 result dependant on the LoginName:

Log(dtaUsername.Text)
    Dim Cursor As ResultSet
    Cursor = Sql2.ExecQuery("SELECT userID FROM User_db WHERE LoginName = "&(dtaUsername.Text))
    Do While Cursor.NextRow
        Log(Cursor.GetString("userID"))
  
    Loop

Comes up with this error. I user PeterLewis10 as dtaUsername

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'PeterLewis10' in 'where clause'

Thank you in advance

Peter

josejad · Mar 31, 2020

Try with

B4X:

SELECT LAST_INSERT_ID()

josejad · Mar 31, 2020

Unknown column 'PeterLewis10' in 'where clause

anyway, there’s something wrong in your clausule. Try to test it in phpmyadmin

have you tried with

B4X:

 Cursor = Sql2.ExecQuery("SELECT userID FROM User_db WHERE LoginName = ''"&(dtaUsername.Text) &"")

Peter Lewis · Mar 31, 2020

José J. Aguilar said:
Try with

B4X:

SELECT LAST_INSERT_ID()

I tried that but it did not work, maybe I am doing something wrong. The issue also might be that there are other people registering at the same time and the delay from inserting to the database and me re-reading the database, someone might insert another record

This is that I tried on your advise

B4X:

    Log(dtaUsername.Text)
    Dim Cursor As ResultSet
    Cursor = Sql2.ExecQuery("SELECT LAST_INSERT_ID()")
    Do While Cursor.NextRow
        Log(Cursor.GetString("userID"))
    
    Loop

Error

java.sql.SQLException: Column 'userID' not found.

thank you

Peter Lewis · Mar 31, 2020

José J. Aguilar said:
Unknown column 'PeterLewis10' in 'where clause

anyway, there’s something wrong in your clausule. Try to test it in phpmyadmin

have you tried with

B4X:

Cursor = Sql2.ExecQuery("SELECT userID FROM User_db WHERE LoginName = ''"&(dtaUsername.Text) &"")

This one gives an error of

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 'abcd' at line 1

abcd is the dtaUsername

This was the username for that registration Unknown column 'PeterLewis10' in 'where clause

josejad · Mar 31, 2020

Please, test the sql sentences in your phpmyadmin or your sql manager, when you see you get a proper response, then use it in B4J

For example, if you use "SELECT LAST_INSERT_ID()" you will see there's no column called 'userId"

Try with

B4X:

SELECT LAST_INSERT_ID() AS 'userID' FROM User_db;

I mean

B4X:

    Log(dtaUsername.Text)
    Dim Cursor As ResultSet
    Cursor = Sql2.ExecQuery("SELECT LAST_INSERT_ID() AS 'userID' FROM User_db")
    Do While Cursor.NextRow
        Log(Cursor.GetString("userID"))
   
    Loop

Sorry with my second sample, I put two ''. Try with (again, test in your database, and when you get your sql working, copy it to B4J

B4X:

Cursor = Sql2.ExecQuery("SELECT userID FROM User_db WHERE LoginName = '"&(dtaUsername.Text) &"")

Test it before to see the result in your DBMS:

Peter Lewis · Mar 31, 2020

José J. Aguilar said:
Please, test the sql sentences in your phpmyadmin or your sql manager, when you see you get a proper response, then use it in B4J

For example, if you use "SELECT LAST_INSERT_ID()" you will see there's no column called 'userId"

View attachment 90981
Try with

B4X:

SELECT LAST_INSERT_ID() AS 'userID' FROM User_db;

I mean

B4X:

Log(dtaUsername.Text) Dim Cursor As ResultSet Cursor = Sql2.ExecQuery("SELECT LAST_INSERT_ID() AS 'userID' FROM User_db") Do While Cursor.NextRow Log(Cursor.GetString("userID")) Loop

Sorry with my second sample, I put two ''. Try with (again, test in your database, and when you get your sql working, copy it to B4J

B4X:

Cursor = Sql2.ExecQuery("SELECT userID FROM User_db WHERE LoginName = '"&(dtaUsername.Text) &"")

Test it before to see the result in your DBMS:
View attachment 90982

This one works

When I tried in code irt game me the correct number but so many times

Peter Lewis · Mar 31, 2020

José J. Aguilar said:
B4X:

Cursor = Sql2.ExecQuery("SELECT userID FROM User_db WHERE LoginName = '"&(dtaUsername.Text) &"")

This one still gives me an error

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 ''djpeterlewis1' at line 1

djpeterlewis1 is the dtaUsername text

Peter Lewis · Mar 31, 2020

This query in phpMyadmin worked

B4X:

SELECT userID FROM User_db WHERE LoginName = 'djpeterlewis'

Peter Lewis · Apr 1, 2020

I worked it out another way and it works

Working Solution:

Cursor = Sql2.ExecQuery2("SELECT userID FROM User_db WHERE `LoginName` LIKE ?",Array As String(dtaUsername.Text))

Thank you for your help

afields · Apr 1, 2020

hello.
you've resolved your problem but if ever you'll need the first sql statement then see it please:


Cursor = Sql2.ExecQuery("SELECT userID FROM User_db WHERE LoginName = '"&(dtaUsername.Text) &"")

you do not have ( at least i do not see) a ' after the username.text.
then it should be:
Cursor = Sql2.ExecQuery("SELECT userID FROM User_db WHERE LoginName = '"&(dtaUsername.Text) &"'")
so that you have:


SELECT userID FROM User_db WHERE LoginName = 'djpeterlewis'

MicroDrie · Apr 19, 2020

afields said:
hello.
you've resolved your problem but if ever you'll need the first sql statement then see it please:
Cursor = Sql2.ExecQuery("SELECT userID FROM User_db WHERE LoginName = '"&(dtaUsername.Text) &"")
you do not have ( at least i do not see) a ' after the username.text.
then it should be:
Cursor = Sql2.ExecQuery("SELECT userID FROM User_db WHERE LoginName = '"&(dtaUsername.Text) &"'")
so that you have:
SELECT userID FROM User_db WHERE LoginName = 'djpeterlewis'

Just an other tip: It can also be very useful to use literal strings in a query. Then you can just use single and double quotes in the query string. With ${dtaUsername.Text} you can insert the value of a textbox or the result of an other calqulation. It increases readability and makes it easier to see the hard-to-see single and double quotes mistakes in complex queries. Your example with literal string:

B4X:

Cursor = Sql2.ExecQuery("SELECT userID FROM User_db WHERE LoginName = '"&(dtaUsername.Text) &"'")

B4X:

Cursor = Sql2.ExecQuery($"SELECT userID FROM User_db WHERE LoginName = '${dtaUsername.Text}';"$)

I add the ; character to visually separated the single and double quote for easy catch mistakes. The use of literal string is very powerful because you can reuse the query by replacing the ${content} as the parameter of the calling sub.

Erel · Apr 19, 2020

This is not the correct way to make queries. Correct:

B4X:

Dim rs As ResultSet =Sql2.ExecQuery2($"SELECT userID FROM User_db WHERE LoginName = ?"$, Array As String(dtaUsername.Text))

B4J Question Using a field from a form to query a remote sql Database [Solved]

Peter Lewis

Active Member

josejad

Expert

josejad

Expert

Peter Lewis

Active Member

Peter Lewis

Active Member

josejad

Expert

Peter Lewis

Active Member

Peter Lewis

Active Member

Peter Lewis

Active Member

Peter Lewis

Active Member

afields

Member

MicroDrie

Well-Known Member

Erel

B4X founder

B4J Question Using a field from a form to query a remote sql Database [Solved]

Active Member

Expert

Expert

Active Member

Active Member

Expert

Active Member

Active Member

Active Member

Active Member

Member

Well-Known Member

B4X founder

Privacy & Transparency

Privacy & Transparency