B4J Question Help with SQL ExecQuery

[cyiwin]

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

My code looks like this:

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]

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

 

[cyiwin]

Thanks! That did it! Learning SQL and how it works with B4J has been an up-hill battle for me.

 

[eps]

This _may_ help... although you may well have seen it already : http://www.sqlite.org/lang.html

 

[cyiwin]

That is a nice site. If anyone else is starting out this one may be useful too.

http://www.ntu.edu.sg/home/ehchua/programming/sql/MySQL_Beginner.html#zz-2.5

 

[jmon]

Don't forget to close your cursor after finishing your read:

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.

 

[eps]

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.

 

[cyiwin]

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.

 

[jmon]

By the way, if you have many people connecting to the database, you may be interested in reading a problem I encountered a while ago here:
http://www.b4x.com/android/forum/th...ion-not-allowed-after-resultset-closed.42516/
The solution was to use the Pool library from jServer. But maybe that's not a something that will happen to you.