B4J Question Help with SQL ExecQuery

cyiwin

Active Member
Licensed User
Longtime User
Hello, I'm trying to read data from my SQL cells. My table from phpMyAdmin looks like this:

table.png


My code looks like this:
B4X:
Sub SQL_Read
  Try
    Dim RS As ResultSet
    RS = SQL1.ExecQuery("SELECT 'bot' FROM `bot_settings`")
    Do While RS.NextRow
      Log(RS.GetString("bot"))
    Loop
  Catch
    Log(LastException)
  End Try
End Sub

My Output is this:
Program started.
bot
bot
bot
bot
bot
bot


I see it is logging the column name every time. How do I make it log the data in each row instead of the column name?
 

eps

Expert
Licensed User
Longtime User
I haven't played with B4J, but if it performs similarly to B4A it should be...

SELECT bot FROM `bot_settings`

i.e. bot without the quotation marks around it, this is why you've got bot repeated instead of the value. HTH
 
Upvote 0

cyiwin

Active Member
Licensed User
Longtime User
Thanks! That did it! Learning SQL and how it works with B4J has been an up-hill battle for me.
 
Upvote 0

jmon

Well-Known Member
Licensed User
Longtime User
Don't forget to close your cursor after finishing your read:
B4X:
Sub SQL_Read
  Try
    Dim RS As ResultSet
    RS = SQL1.ExecQuery("SELECT 'bot' FROM `bot_settings`")
    Do While RS.NextRow
      Log(RS.GetString("bot"))
    Loop
    RS.Close '<--- Here
  Catch
    Log(LastException)
  End Try
End Sub

This _may_ help... although you may well have seen it already : http://www.sqlite.org/lang.html
Apparently he is using MySQL. The commands are different between SQLite and MySQL.

You can also try this website: http://www.w3schools.com/sql/default.asp and all the keywords are given in the left column. You should try them one by one and you'll learn fast.
 
Upvote 0

eps

Expert
Licensed User
Longtime User
Ah right, I just assumed... MySQL is a different kettle of fish these days though as it isn't as free as it was since Oracle obtained it.
 
Upvote 0

cyiwin

Active Member
Licensed User
Longtime User
Don't forget to close your cursor after finishing your read:

Apparently he is using MySQL. The commands are different between SQLite and MySQL.

You can also try this website: http://www.w3schools.com/sql/default.asp and all the keywords are given in the left column. You should try them one by one and you'll learn fast.


You're right, I ran into "read only" errors while trying to update. Close seemed to fix the problem. That was confusing to me since I write to the data base every 10 seconds I thought I should leave it open but oh well.

edit: From reading http://www.b4x.com/b4j/help/jsql.html#sql_close I thought "close" would disconnect me from the SQL server, now I see it only removes the cursor.

edit: edit: Never mind my previous edit, I see there are two different Closes, ugh...

Thanks for the link I've added it to my bookmarks.
 
Last edited:
Upvote 0

jmon

Well-Known Member
Licensed User
Longtime User
Upvote 0
Top