recordset is nil, mySQL query works in workbench

  1. ‹ Older
  2. last week

    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).

  3. 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.

  4. Louis D

    Dec 3 Pre-Release Testers, Xojo Pro Montreal, QC, Canada

    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 @Julian S beat me to this answer. Please read his post carefully. The answer is there (and here), I am quite certain.

  5. Dave S

    Dec 3 San Diego, California USA

    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]

  6. Bob K

    Dec 3 Pre-Release Testers, Xojo Pro Kansas City

    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.

  7. 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
  8. Dave S

    Dec 3 San Diego, California USA
    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 :)

  9. 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.

  10. Tim H

    Dec 3 Pre-Release Testers Portland, OR USA

    @ChristopherOliphant but doesn't actually perform the updates to the database.

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

  11. 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).

    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
    
  12. 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.

  13. Now this is insane. With no changes to the server or anywhere else in my address book program, I just rearranged the code and suddenly the old method without ' ' works. When I removed the ' ' last time, I must have had some other syntax problem (but there were no errors). I have given up trying to figure out what I had done wrong 5 hours ago along with how I could get a rs = Nil without error. Anyway, the method now updates the records as I have been wanting to since yesterday, while the PS version of the method still is not updating the records.

    Rather than taking this victory with a ice cold Corona and a tube of Pringles, of course I am now fixated on why this works

    Dim rs As RecordSet = db.SQLSelect("SELECT * FROM contact_table WHERE idContact = " + RecordID.Text + ";")

    but this does not

    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

    nor does this work

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

    The rest of the method code being identical.

    I was thinking the TYPE_DOUBLE could be screwing something up so I tried STRING, which is what the top one is doing, but as I mentioned, it doesn't update either. The strange thing is all 3 snippets above give rs identical record contents, but the rs.Edit and rs.Update methods are only working on the top one. I can't get rs.Edit and rs.Update methods to work with the PS style recordset.

    I really like this concept of Prepared Statement so I am working hard trying to get the PS version to work so that I can use it with confidence in the future. That and I am a glutton for punishment.

  14. Tim H

    Dec 3 Pre-Release Testers Portland, OR USA

    PreparedSQLStatement is the base class. You need to use the database-specific variant, namely MySQLPreparedStatement .

  15. Alberto D

    Dec 3 Pre-Release Testers, Xojo Pro

    @Tim H PreparedSQLStatement is the base class. You need to use the database-specific variant, namely MySQLPreparedStatement .

    Do you think the examples on that page are wrong? For example this code:

    Dim ps As PreparedSQLStatement = _
      db.Prepare("SELECT * FROM Persons WHERE Name = ? AND Age >= ?")
  16. Tim,

    I followed that page's sample code without success.

    Note that the sample code for that Dim As PreparedSQLStatement but then binds at MySQLPreparedStatement, which I tried to do. I also took a shot at Dim ps As MySQLPreparedStatement = but it tossed and error for the 'Dim As' line "Can't find a type with this name MySQLPreparedStatement"

    This is my code that correctly retrieves record contents (rs), modeled after the guide's sample code.

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

    So really, PS does work BUT, I cannot then use the rs.Edit and rs.Update methods on the resulting rs. The other way,

    Dim rs As RecordSet = db.SQLSelect("SELECT * FROM contact_table WHERE idContact = " + RecordID.Text + ";")

    works with the rs.Edit and rs.Update methods. For now I am just using this code for a functioning editing method and will try getting ps working with rs.Edit and rs.Update methods tomorrow (if it is even possible).

  17. Julian S

    Dec 4 Pre-Release Testers, Xojo Pro UK

    Try using MySQLPreparedStatement.MYSQL_TYPE_LONG for idContact if its shown as an INT in workbench.

  18. Julian S

    Dec 4 Pre-Release Testers, Xojo Pro UK
    Edited last week

    I'm not a fan of how https://docs.xojo.com/MySQLPreparedStatement lists constants that don't directly relate to https://dev.mysql.com/doc/refman/8.0/en/integer-types.html

    It might be a legacy thing, I've not been around Xojo long enough to know but it seems a little confusing, maybe a doc tweak could be done to show the mappings or the sizes involved with the xojo constants as it seems a little vague.

  19. scott b

    Dec 4 Pre-Release Testers, Xojo Pro local coffee shop

    @Julian S maybe a doc tweak could be done to show the mappings

    send an email to docs@xojo or open a feedback case and @Paul L will take care of it. he is awesome about doing doc updates based on people’s feedback.

  20. Julian S

    Dec 4 Pre-Release Testers, Xojo Pro UK

    @scott b send an email to docs@xojo or open a feedback case and @Paul L will take care of it. he is awesome about doing doc updates based on people’s feedback.

    A feature request is already in and outstanding since Oct 2014 Feedback Case #36301

  21. Alberto D

    Dec 4 Pre-Release Testers, Xojo Pro

    Too bad Paul is only human ;)
    He always promote a direct contact with him (you can see the information on all webinars). I think that case is not on Paul's radar and is better to do a more direct contact.

or Sign Up to reply!