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

[Peter Lewis]

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.

    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.

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]

Try with

SELECT LAST_INSERT_ID()

 

[josejad]

Unknown column 'PeterLewis10' in 'where clause

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

have you tried with

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

 

[Peter Lewis]

Try with

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

    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]

Unknown column 'PeterLewis10' in 'where clause

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

have you tried with

 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]

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

SELECT LAST_INSERT_ID() AS 'userID' FROM User_db;

I mean

    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

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

Test it before to see the result in your DBMS:

 

[Peter Lewis]

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

SELECT LAST_INSERT_ID() AS 'userID' FROM User_db;

I mean

    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

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]

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]

This query in phpMyadmin worked

SELECT userID FROM User_db WHERE LoginName = 'djpeterlewis'

 

[Peter Lewis]

I worked it out another way and it works

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

Thank you for your help

 

[afields]

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]

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:

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

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]

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

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