recordset is nil, mySQL query works in workbench

  1. ‹ Older
  2. 4 months ago

    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.

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

  4. 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 >= ?")
  5. Edited 4 months ago

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

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

  7. Julian S

    Dec 4 Pre-Release Testers, Xojo Pro UK
    Edited 4 months ago

    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.

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

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

  10. 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!