MySQL comparing data

Hello there, I am really new to this but I am trying to compare data with a label to see if the version matches with the database and if it does it will say there is an update ready if it matches it will say everything is updated, thanks for any help on this matter

[code]If db.Connect Then
MsgBox “Connect!”
rs = db.SQLSelect(“SELECT * FROM update WHERE version = 1.0.0”)

if db.Error then
  MsgBox("Error: " + db.ErrorMessage)
  Return
end if

If rs <> Nil Then
  While Not rs.EOF
    If rs.Field("version").StringValue = "update" Then
      MsgBox("Table: " + rs.Field("version").StringValue)
    End If
    rs.MoveNext
  Wend
  rs.Close
End If
db.Close

Else
If db.Error Then
MsgBox("Error: " + db.ErrorMessage)
Return
End If
End If[/code]

Not sure what your table holds but 1.0.0 is a string, not a number

So it probably needs quotes

"SELECT * FROM update WHERE version = '1.0.0'"

[quote=376300:@Jeff Tullin]Not sure what your table holds but 1.0.0 is a string, not a number

So it probably needs quotes

"SELECT * FROM update WHERE version = '1.0.0'"

In addition to that, in your loop you’re looking for version=“update”, which it never will since you selected only records where it equals 1.0.0. Maybe you meant to compare a different field in your loop?

I get shot at for offering a solution where none is asked for … but

If db.Connect Then
    MsgBox "Connect!"
    rs = db.SQLSelect("SELECT * FROM update WHERE version = '"+whateverlabel.text+"'" // a prepared statement would be better
    
    if db.Error then
      MsgBox("Error: " + db.ErrorMessage)
      Return
    end if
    if rs.recordCount>=1 then 
        msgbox "MATCH"
    else
       msgbox "NO MATCH"
   end if
end if

instead of wasting resources comparing each record to a “known value”… just look for that value…

In one project i store information regarding changed parts of the project in the Database and those changes are related to the Major, Minor, … Version Numbers i already use in Xojo. I just compare those Numbers with the Numbers in the Database and then the App “decides” if this is an update worth reporting to the user or not. It can also “trigger” a “forced” update and so on. :slight_smile:

Thanks guys I can do this a lot more clearly now by seeing it but for some reason I am getting a syntax error from MySQL from both that you guys posted, I was thinking it was because of the semicolon missing but it wasn’t. Xojo doesn’t see a error so that part is okay but MySQL does. I even tried using a prepared statement instead, I am going to check MySQL website to see

Okay I figured that out but I am just wondering why I am getting a NilObjectException when running this because the object is there and the data is so I am a little confuse why this would come back

[code] If db.Connect Then
MsgBox “Connect!”
rs = db.SQLSelect("SELECT * FROM update WHERE version = ’ " + gversion.text + " ’ ")
else
MsgBox db.ErrorMessage
end if

if gversion.text = rs.Field(“version”).StringValue then
MsgBox “There is no update”
else
MsgBox “There is an update”
end if[/code]

Maybe show us your Database Scheme and tell us where the NOE occurrs? :slight_smile:

Well it is happening on this line

if gversion.text = rs.Field("version").StringValue then

I even tried moving the code inside the other if statement to make sure the database was not closing on me.

CREATE TABLE IF NOT EXISTS update
dev_build char(20) COLLATE utf8_unicode_ci DEFAULT NULL,
version varchar(120) COLLATE utf8_unicode_ci DEFAULT NULL,
build int(10) DEFAULT NULL

I even tried doing it local on my computer to make sure there was no issues but it even says connect so I don’t think it is from a dropping connection.

In your select, you add extra spaces for some reason: (I have swapped them with ^ here to illustrate)

WHERE version = '^" + gversion.text + "^' ")

That means that if your text field holds ‘1.0.0’ you are actually searching for ’ 1.0.0 ’

We shouldnt need to be writing the code down to this level, but …

If db.Connect Then MsgBox "Connected!" rs = db.SQLSelect("SELECT * FROM update WHERE version = '" + gversion.text + "' ") if db.error then MsgBox db.ErrorMessage else // no error if rs.eof then // no records returned MsgBox "There is no update" else MsgBox "There is an update" end if end if //end of if error end if

You should/need to test before using the StringValue if there’s a value in it. :wink:

A If rs.Field("version").Value<>Nil Then... should do it. OTOH you could just make sure, that there’s never a NULL Value in the Database Column. :wink: