recordset is nil, mySQL query works in workbench

Getting Started
1

Hello again, day 3 with Xojo and I am having fun. I have been working on an address book as my first project and so far I have it adding records and displaying records including using autocomplete to display records (all from a remote MySQLCommunity Server database).

Problem:
Now I am working on a method called ‘EditRecord’ to edit the displayed record however I am doing something wrong that results in my recordset being nil even if the query showing the debug window works in Workbench to fetch the single record row I am looking for.

I have added some remarks to the code for this post. After a few hours messing trying different things, I am stumped. I have modeled my code after the Changing Existing data section of the Database operations guide and am not seeing what I may be doing wrong.

At the time of this method call, you have a series of populated text fields fetched from the database in a windows form called ContactsWindow. An edit button on ContactsWindow that is made visible after field population calls this ‘EditRecord’ method as its click event .

With this method I am trying to update a bunch recordset fields with what is in the populated textfields whether they are changed or not (later I will make it only update changed fields, for now I am just trying to get it updating at all) but the recordset is nil so the method elses its way to db.Close without error.

' EditRecord method is public

' Connectdb is global method in a module "DatabaseModule" with global properties 
' db As MySQLCommunityServer
' and
' IsConnected As Boolean
' Connectdb method includes line db = New MySQLCommunityServer

//
// Begin method
//

' Connect to database, report error(s) if fail

Connectdb
If Not db.Connect Then
  MsgBox("Database Connect failed: " + db.ErrorMessage)
Else
  IsConnected = True
  IsConnectedLabel.Text = "Connected to: " + db.databasename + "@" + db.Host 
End If

' Database shows connected in workbench and IsConnected = True

' RecordID.Text is record PK set by user selection, assigning to a variable to show in debug window
Dim RecordIDString As String = RecordID.Text

Dim sql As String
sql = ("SELECT * FROM contact_table WHERE idContact = '" + str(RecordIDString) + "';")

Dim rs As RecordSet
rs = db.SQLSelect(sql)

' Break at If rs <> Nil... to check rs for Nil and value of sql, sql query statement looks good
' e.g.     SELECT * FROM contact_table WHERE idContact = '2';

If rs <> Nil Then
  rs.Edit
  If Not db.Error Then
    rs.Field("ShipName").StringValue = cNameShipField.Text
    rs.Field("ShipAddress").StringValue = shipAddressField.Text
    rs.Field("ShipAttn").StringValue = attnShipField.Text
    rs.Field("ShipCity").StringValue = shipCityField.Text
    rs.Field("ShipState").StringValue = shipStatePopup.Text
    rs.Field("ShipZip").StringValue = shipZipField.Text
    rs.Field("ShipPrefecture").StringValue = shipPrefectureField.Text
    rs.Field("ShipCountry").StringValue = shipCountryField.Text
    rs.Field("ShipEmail").StringValue = shipEmailField.Text
    rs.Field("ShipAltEmail").StringValue = shipAltEmailField.Text
    rs.Field("BillName").StringValue = cNameBillField.Text
    rs.Field("BillAddress").StringValue = billAddressField.Text
    rs.Field("BillAttn").StringValue = attnBillField.Text
    rs.Field("BillCity").StringValue = billCityField.Text
    rs.Field("BillState").StringValue = billStatePopup.Text
    rs.Field("BillZip").StringValue = billZipField.Text
    rs.Field("BillPrefecture").StringValue = billPrefectureField.Text
    rs.Field("BillCountry").StringValue = billCountryField.Text
    rs.Field("BillEmail").StringValue = billEmailField.Text
    rs.Field("BillAltEmail").StringValue = billAltEmailField.Text
    rs.Field("TermsIndex").StringValue = TermsPopup.Text
    rs.Field("CarrierIndex").StringValue = ShipPopup.Text
    rs.Field("ShipAcctNo").StringValue = custShipNoField.Text
    rs.Field("LegacyCustNo").StringValue = LegacyCustNoField.Text
    rs.Field("ShipPhoneNo").StringValue = shipPhoneField.Text
    rs.Field("ShipFaxNo").StringValue = shipFaxField.Text
    rs.Field("BillPhoneNo").StringValue = billPhoneField.Text
    rs.Field("BillFaxNo").StringValue = billFaxField.Text
    rs.Update
    MsgBox("Record Updated")
  Else
    MsgBox(db.ErrorMessage)
  End If
  rs.close
Else
  If db.Error Then
    MsgBox(db.ErrorMessage)
  End If
End If

db.Close
2

You can try including the primary key is the update clause. i.e.

rs.Field("idContact").IntegerValue = val(RecordID.Text) //assuming the PK is an int
3

if rs is nil it’s because the query is wrong.

[code]rs = db.SQLSelect(sql)

if db.error then
dim s as string = db.errormessage
break //read the message!
return
end[/code]

I suspect that idContact is really an integer field. So the query should really be:

"SELECT * FROM contact_table WHERE idContact = " + str(RecordIDString) + ";"  //not the removed single quotes
4

That is the thing, if I C&P the query from the debug window directly into Workbench, I get the desired record. Eg the debug window will show:

SELECT * FROM contact_table WHERE idContact = '2';

C&P that into Workbench and I get the entire record for PK=2 as it should.

So the concatenation works. From my understanding, Xojo just concatenates a string to send to the server, whether you create it with strings or integers, the actual query statement sent to mySQL is just a string that mySQL executes just as if you had pasted it into Workbench.

Connectdb method is the only code connecting to the server and it works when called by an addrecord method and retrieving records for textfield population method but not for EditRecord method.

This is from my PopulateWithSelection method and it works, returning a valid record set.

[code]Dim sql As String
sql = (“SELECT * FROM contact_table WHERE ShipName = '” + AddressSelectComboBox.Text + “’;”)

Dim rs As RecordSet
rs = db.SQLSelect(sql)[/code]

This is from the EditRecord method and returns nil while using the same dbConnect method to connecting the to db.

[code]Dim sql As String
sql = (“SELECT * FROM contact_table WHERE idContact = '” + str(RecordIDString) + “’;”)

Dim rs As RecordSet
rs = db.SQLSelect(sql)[/code]

Not knowing how xojo or mySQL really work, any method that calls for connecting to the database ends by closing the database
as I am not sure if connections are otherwise perpetual or whatever. I just proactively close the connection when finished just in case). I also close the record sets as I don’t know if reusing rs for a record set is a problem.

When the method is run, I see in Workbench that a connection is established. I don’t know enough mySQL to check mySQL logs to see if there is any record of received querys and such. Time to RTF-mySQL-M.

5

In my experience the DB.ErrorMessage will tell you what the problem is.

6

There is no error message. With the recordset being nil, the method elses its way to the end having not done anything (since the If rs <> nil is not satisfied).

7

What type is idContact? If its a numeric type then I’d suggest trying it with the ’ removed. Whether or not it works in workbench is mute because it might be doing something that Xojo isn’t which is causing the issues you’re seeing.

I’ve just tested the following code to my local mysql server with wordpress on it, ID is a bigint(20) unsigned:

rs = db.SQLSelect("SELECT * FROM wordpress.wp_users WHERE ID = '1';")

This sql works in wordbench but it returns no rs in xojo, if you change the code to:

rs = db.SQLSelect("SELECT * FROM wordpress.wp_users WHERE ID = 1;")

It works.

On a side note, please please read up on https://documentation.xojo.com/index.php/MySQLPreparedStatement as things like this are less prone to happen if you strictly define things.

PS. Welcome to Xojo :slight_smile:

8

Yup.
Do you open it again before doing the second query?
What happens if this is the FIRST query?

[code]Dim sql As String
sql = (“SELECT * FROM contact_table WHERE idContact = ‘2’;”)

Dim rs As RecordSet
rs = db.SQLSelect(sql)[/code]

9

As Bob stated… if the RS is NIL then the Query was incorrect or malformed… otherwise it would return an Error message, or in the case of a SELECT would return RecordCount=0

10

idContact is the integer PK for the address book table. There is only the one schema (other than sys) on the server and contact_table is the only table in the schema. There are no other mySQL servers running on the network or on my workstations (I even net stopped the local instance of mySQL80).

Same result removing the ’ ’ I even tried just putting an integer in the line regardless of record and still rs is nil which doesn’t make sense. For this I used str(INT) and just sticking the number of course gave me an expected text literally error.

I didn’t bother with a prepared statement on this since it is only the 1, supposedly simple element. It should just work. Actually it should just work with or without the ’ surrounding the PK integer value.

Jeff, yeah, if I need to do a read/write to the db, I connect, do the read write and then close. There is only 1 query per method at the moment. I made Connectdb, add, edit and populate all different methods (Connectdb is global) while I am learning xojo and mySQL. Add, edit and populate all call Connectdb and end with db.Close. Add and populate get rs no problem, edit is giving me fits.

Dave,
I just tried with and without ’ around the PK value and still rs is nil. This is baffling, it should just work. I even broke out the SQL statement to a variable so I can check the statement value in the debug window and it sure looks like it is formed correctly.

This really isn’t rocket science. Of all the xojo stuff, this is darn straight forward and it just isn’t working in my edit method. I will keep plugging away though. Usually I just keep trying different stuff until I stumble across a fix. It has to be something really dumb and I am just to newb to see it.

I will go with a prepared statement and see if using str(INT) or str(RecordIDString) in the sql string variable is just making it choke for some unknown reason (since this has been working fine in my other methods).

11

I looked through the database documentation, but xojo doesn’t go into detail about how the SQLSelect() function actually work, just how to use it. Seems it should just pass the string-line as a string to mySQL which then executes the string as a query. Perhaps I am 100% wrong about that.

12

If idContact is an integer, I suspect that the SQL string is the culprit; The line where you define SQL should probably be:

sql = "SELECT * FROM contact_table WHERE idContact = " + str(RecordIDString) + ";"

Do not enclose integer values in single quotes in your SQL string, or else your query will return nothing (at least, that’s the way it works with PostgreSQL). Also, do not enclose the string in parentheses (as shown above). The above snippet should work.

oh. I see that @ beat me to this answer. Please read his post carefully. The answer is there (and here), I am quite certain.

13

again if RS=NIL there can only be TWO things happening…

  • the SQL is invalid or malformed
  • the SQL is never actually even executed

ANY other circumstance will result in a NON-NIL recordset

So you are saying it goes DIRECTLY to the LAST statement (DB.CLOSE) with no errors, and no database actions?

What Julian and Louis said about quotes… “MIGHT” come under “malformed” [note :SQLite doesn’t care, mySQL might]

14

I’ve use MySQL in hundreds of different Xojo projects. RS will only ever be nil if there is an error in the SQL statement.

I’d put a breakpoint just after the SQLSelect method and manually check, in the debugger, the state of DB, and get the actual SQL string you’re sending to the database. Please share the results. Screenshare might be helpful too.

I’ve seen crazy things before where people will have multiple DB properties declared and they think they’ve opened a connection but it’s really an old one that’s either been closed or out of scope. I’ve seen multiple windows opening and closing the connection and competing with one another. Also a possibility that the user doesn’t have Select permissions in the application. There WILL be a simple explanation for it.

15

Yes Dave. With rs = Nil, it hits the else, but with NO ERROR, it just runs through the method to close. To double check I added a message box at the end fo the method, MsgBox(“The method was run”) and I get the message box. It makes no sense. Either I should get a record, or an error, the cause of why I am getting Nil, but I got neither with the way the code was as originally posted. It is nil and the block was just happy with it and runs to the end.

But you, Louis and others were right. For whatever reason, with ’ ’ or without, xojo didn’t like the string as concatenated. I RTFM on Prepared statements and switched to this

Dim ps As PreparedSQLStatement = db.Prepare("SELECT * FROM contact_table WHERE idContact = ?;") ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_DOUBLE) ps.Bind(0, val(RecordID.Text)) Dim rs As RecordSet = ps.SQLSelect

Now, I know that idContact is a PK integer, so I just guessed with trying val(RecordID.Text) to get the double and with no MYSQL_TYPE_INT choice, went with MYSQL_TYPE_DOUBLE.

Now rs has the record. …yay…

rs is no longer Nil, the If rs<> Nil block runs through to the Msgbox(“Record Updated”) line to close, but doesn’t actually perform the updates to the database. Still more to learn.

This is the updated code. I will RTFM more about prepared statements and see if rs.Edit…rs.Update syntax needs to be changed. With rs containing the record, I wouldn’t think so, but then I thought my old code should have worked too.

Connectdb
If Not db.Connect Then
  MsgBox("Database Connect failed: " + db.ErrorMessage)
End If

// Rem this block to try prepared statement
'Dim RecordIDString As String = RecordID.Text
'Dim sql As String
'sql = ("SELECT * FROM contact_table WHERE idContact = " + str(RecordIDString) + ";")
'Dim rs As RecordSet
'rs = db.SQLSelect(sql)
//

Dim ps As PreparedSQLStatement = db.Prepare("SELECT * FROM contact_table WHERE idContact =  ?;")
ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_DOUBLE)
ps.Bind(0, val(RecordID.Text))
Dim rs As RecordSet = ps.SQLSelect

If rs <> Nil Then
  rs.Edit
  If Not db.Error Then
    rs.Field("ShipName").StringValue = cNameShipField.Text
    rs.Field("ShipAddress").StringValue = shipAddressField.Text
    rs.Field("ShipAttn").StringValue = attnShipField.Text
    rs.Field("ShipCity").StringValue = shipCityField.Text
    rs.Field("ShipState").StringValue = shipStatePopup.Text
    rs.Field("ShipZip").StringValue = shipZipField.Text
    rs.Field("ShipPrefecture").StringValue = shipPrefectureField.Text
    rs.Field("ShipCountry").StringValue = shipCountryField.Text
    rs.Field("ShipEmail").StringValue = shipEmailField.Text
    rs.Field("ShipAltEmail").StringValue = shipAltEmailField.Text
    rs.Field("BillName").StringValue = cNameBillField.Text
    rs.Field("BillAddress").StringValue = billAddressField.Text
    rs.Field("BillAttn").StringValue = attnBillField.Text
    rs.Field("BillCity").StringValue = billCityField.Text
    rs.Field("BillState").StringValue = billStatePopup.Text
    rs.Field("BillZip").StringValue = billZipField.Text
    rs.Field("BillPrefecture").StringValue = billPrefectureField.Text
    rs.Field("BillCountry").StringValue = billCountryField.Text
    rs.Field("BillEmail").StringValue = billEmailField.Text
    rs.Field("BillAltEmail").StringValue = billAltEmailField.Text
    rs.Field("TermsIndex").StringValue = TermsPopup.Text
    rs.Field("CarrierIndex").StringValue = ShipPopup.Text
    rs.Field("ShipAcctNo").StringValue = custShipNoField.Text
    rs.Field("LegacyCustNo").StringValue = LegacyCustNoField.Text
    rs.Field("ShipPhoneNo").StringValue = shipPhoneField.Text
    rs.Field("ShipFaxNo").StringValue = shipFaxField.Text
    rs.Field("BillPhoneNo").StringValue = billPhoneField.Text
    rs.Field("BillFaxNo").StringValue = billFaxField.Text
    rs.Update
    MsgBox("Record Updated")
  Else
    MsgBox(db.ErrorMessage)
  End If
  rs.close
Else
  If db.Error Then
    MsgBox(db.ErrorMessage)
  End If
End If

db.Close
ContactsWindow.Close
16 
sql = "SELECT * FROM contact_table WHERE idContact = '" + RecordIDString + "';"

since RecordIDString IS a String, using STR() is not required (that changes an Numeric datatype to a string) and outer () are also not required

Note, when you wrote the PS code… you removed BOTH of those issues :slight_smile:

17

Yep. Too ignorant to remove str() when I just tried str(some known PK integer) in a pathetic attempt to get it working. Switched it back to RecordIDString through C&P, forgot to remove str(). Derp. I’ll get with the, don’t need a string$ program eventually.

18

You need a db.Commit to actually save the changes to the database.

19

Tim,

I didn’t see db.Commit in the database operations guide, but using the code reference just C&Ped the snippet right after my rs.Edit…rs.Update block

This is that block but it is not committing the changes. Does db.commit require any parameters or should it be somewhere else? This is for mySQL so maybe it requires something different? First time seeing db.commit so I have a rollback but no begin transaction or anything…just now figuring out how it works (trial and error…or really If trial Then Error).

[code]If rs <> Nil Then
rs.Edit
If Not db.Error Then
rs.Field(“ShipName”).StringValue = cNameShipField.Text
rs.Field(“ShipAddress”).StringValue = shipAddressField.Text
rs.Field(“ShipAttn”).StringValue = attnShipField.Text
rs.Field(“ShipCity”).StringValue = shipCityField.Text
rs.Field(“ShipState”).StringValue = shipStatePopup.Text
rs.Field(“ShipZip”).StringValue = shipZipField.Text
rs.Field(“ShipPrefecture”).StringValue = shipPrefectureField.Text
rs.Field(“ShipCountry”).StringValue = shipCountryField.Text
rs.Field(“ShipEmail”).StringValue = shipEmailField.Text
rs.Field(“ShipAltEmail”).StringValue = shipAltEmailField.Text
rs.Field(“BillName”).StringValue = cNameBillField.Text
rs.Field(“BillAddress”).StringValue = billAddressField.Text
rs.Field(“BillAttn”).StringValue = attnBillField.Text
rs.Field(“BillCity”).StringValue = billCityField.Text
rs.Field(“BillState”).StringValue = billStatePopup.Text
rs.Field(“BillZip”).StringValue = billZipField.Text
rs.Field(“BillPrefecture”).StringValue = billPrefectureField.Text
rs.Field(“BillCountry”).StringValue = billCountryField.Text
rs.Field(“BillEmail”).StringValue = billEmailField.Text
rs.Field(“BillAltEmail”).StringValue = billAltEmailField.Text
rs.Field(“TermsIndex”).StringValue = TermsPopup.Text
rs.Field(“CarrierIndex”).StringValue = ShipPopup.Text
rs.Field(“ShipAcctNo”).StringValue = custShipNoField.Text
rs.Field(“LegacyCustNo”).StringValue = LegacyCustNoField.Text
rs.Field(“ShipPhoneNo”).StringValue = shipPhoneField.Text
rs.Field(“ShipFaxNo”).StringValue = shipFaxField.Text
rs.Field(“BillPhoneNo”).StringValue = billPhoneField.Text
rs.Field(“BillFaxNo”).StringValue = billFaxField.Text
rs.Update

If Not db.Error Then
  db.Commit
  MsgBox("Record Updated")
Else
  MsgBox("Error: " + db.ErrorMessage)
  db.Rollback
End If

Else
MsgBox(db.ErrorMessage)
End If

rs.close

Else
If db.Error Then
MsgBox(db.ErrorMessage)
End If
End If

[/code]

20

Tim,

Me again, in looking at the RecordSet.Update method when used with mySQL, it doesn’t look like I need a separate db.Commit, with the caveat that it should not be used to loop updates. The Update method performs the commit.