MySQLPreparedStatement Update

Databases
1

I’m trying to use the MySQLPreparedStatement function for updating a record in the database. I use the exact same BindType and Bind Settings for both this update as well as the insert. The only thing changing is the query itself. The Insert works very well. The update however gives me a nilexception error. I created a straight SQL update statement with the same data as that being send to the prepared statement and ran it through the DB.SQLExecute successfully so I know that the update statement is good. Below are the 2 query strings:

qry = "UPDATE pm_FormData SET ProdCode = ?, Desc1 = ?, Desc2 = ?, ProdCat = ?, AlphaName = ?, " _

  • " SC1 = ?, SC2 = ?, SC3 = ?, SC4 = ?, SC5 = ?, SC6 = ?, SC7 = ?, SC8 = ?, SC9 = ?, SC10 = ?, " _
  • " SC11 = ?, SC12 = ?, SC13 = ?, " _
  • " SC14 = ?, SC15 = ?, SC16 = ?, ProcessCode = ?, CatchWeight = ?, CaseWeight = ?, PackSize = ?, " _
  • " Barcode = ?, BlockTi = ?, TierHi = ?, StockUM = ?, PricingUM = ?, SellByUM = ?, AlternateUM = ?, DivID = ?, " _
  • " Requestor = ?, EnterBy = ?, Complete = ?, RequestDate = ? WHERE ID = " + str(prodObj.ID)

dim dbgQry As String
dbgQry = “UPDATE pm_FormData SET ProdCode = '” + prodObj.ProdCode + “’, Desc1 = '” + prodObj.Desc1 _

  • “’, Desc2 = '” + prodObj.Desc2 + “’, ProdCat = '” + prodObj.ProdCat + “’, AlphaName = '” _
  • prodObj.AlphaName + “’, SC1 = '” + prodObj.SC1 + “’, SC2 = '” + prodObj.SC2 + “’, SC3 = '” _
  • prodObj.SC3 + “’, SC4 = '” + prodObj.SC4 + “’, SC5 = '” + prodObj.SC5 + “’, SC6 = '” + prodObj.SC6 _
  • “’, SC7 = '” + prodObj.SC7 + “’, SC8 = '” + prodObj.SC8 + “’, SC9 = '” + prodObj.SC9 + “’, SC10 = '” _
  • prodObj.SC10 + “’, SC11 = '” + prodObj.SC11 + “’, SC12 = '” + prodObj.SC12 + “’, SC13 = '” + prodObj.SC13 _
  • “’, SC14 = '” + prodObj.SC14 + “’, SC15 = '” + prodObj.SC15 + “’, SC16 = '” + prodObj.SC16 _
  • "’, ProcessCode = " + str(prodObj.ProcessCode) + “, CatchWeight = '” + prodObj.CatchWeight _
  • "’, CaseWeight = " + str(prodObj.CaseWeight) + “, PackSize = '” + prodObj.PackSize + “’, Barcode = '” + prodObj.Barcode _
  • "’, BlockTi = " + str(prodObj.BlockTi) + ", TierHi = " + str(prodObj.TierHi) + “, StockUM = '” + prodObj.StockUM _
  • “’, PricingUM = '” + prodObj.PricingUM + “’, SellByUM = '” + prodObj.SellByUM + “’, AlternateUM = '” _
  • prodObj.AlternateUM + "’, DivID = " + str(prodObj.DivID) + “, Requestor = '” + prodObj.Requestor _
  • “’, EnterBy = '” + prodObj.EnterBy + “’, Complete = '” + prodObj.Complete + “’, RequestDate = '” _
  • str(prodObj.RequestDate) + "’ WHERE ID = " + str(prodObj.ID)

I can not figure out while the NilObjectException is being thrown. Below are the Binding Statements. The binding statements are in a Method which is part of the prodObj class. When I use this Method for both the Insert and the the Update. The insert work perfectly.

dim ps As PreparedSQLStatement
dim p As cProduct = prodObj

ps = Session.AppDb.Prepare(query)
ps.Bindtype(0, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(0, p.ProdCode)
ps.Bindtype(1, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(1, p.Desc1)
ps.Bindtype(2, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(2, p.Desc2)
ps.Bindtype(3, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(3, p.ProdCat)
ps.Bindtype(4, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(4, p.AlphaName)
ps.Bindtype(5, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(5, p.SC1)
ps.Bindtype(6, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(6, p.SC2)
ps.Bindtype(7, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(7, p.SC3)
ps.Bindtype(8, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(8, p.SC4)
ps.Bindtype(9, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(9, p.SC5)
ps.Bindtype(10, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(10, p.SC6)
ps.Bindtype(11, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(11, p.SC7)
ps.Bindtype(12, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(12, p.SC8)
ps.Bindtype(13, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(13, p.SC9)
ps.Bindtype(14, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(14, p.SC10)
ps.Bindtype(15, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(15, p.SC11)
ps.Bindtype(16, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(16, p.SC12)
ps.Bindtype(17, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(17, p.SC13)
ps.Bindtype(18, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(18, p.SC14)
ps.Bindtype(19, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(19, p.SC15)
ps.Bindtype(20, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(20, p.SC16)
ps.Bindtype(21, MySQLPreparedStatement.MYSQL_TYPE_LONG)
ps.Bind(21, p.ProcessCode)
ps.Bindtype(22, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(22, p.CatchWeight)
ps.Bindtype(23, MySQLPreparedStatement.MYSQL_TYPE_FLOAT)
ps.Bind(23, p.CaseWeight)
ps.Bindtype(24, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(24, p.PackSize)
ps.Bindtype(25, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(25, p.Barcode)
ps.Bindtype(26, MySQLPreparedStatement.MYSQL_TYPE_SHORT)
ps.Bind(26, p.BlockTi)
ps.Bindtype(27, MySQLPreparedStatement.MYSQL_TYPE_SHORT)
ps.Bind(27, p.TierHi)
ps.Bindtype(28, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(28, p.StockUM)
ps.Bindtype(29, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(29, p.PricingUM)
ps.Bindtype(30, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(30, p.SellByUM)
ps.Bindtype(31, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(31, p.AlternateUM)
ps.Bindtype(32, MySQLPreparedStatement.MYSQL_TYPE_SHORT)
ps.Bind(32, p.DivID)
ps.Bindtype(33, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(33, p.Requestor)
ps.Bindtype(34, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(34, p.EnterBy)
ps.BindType(35, MySQLPreparedStatement.MYSQL_TYPE_STRING)
ps.Bind(35, p.Complete)
ps.BindType(36, MySQLPreparedStatement.MYSQL_TYPE_DATE)
ps.Bind(36, p.RequestDate)

ps.SQLExecute()
Session.AppDb.SQLExecute(“COMMIT”)

Is there any way to get deeper into the NilObjectException to see why the error is being thrown?

2

Always, always, always check for db error after every database operation.

if Session.AppDb.Error then msgbox Session.AppDb.ErrorMessage end

Did I mention always?

3

You’re using “qry” and “query”. Is that a typo in the forum post or is it in your code, too? If that’s your code, then you’re using a different variable in your Prepare call than you’re setting in your code.

4

Thanks Bob! You’re kind of a legend in this community and one that I’ve read about for years while working through the evolution of this product, so getting a response from you is kind of an honor.

I ALWAYS do a dbError check after a database function, I just didn’t include those lines in the source that I posted. It was kind of an Assumption that people would recognize that it’s best practices and that it’s being done. I have a 3 line statement similar to the one you described where I send the error back up the call stack, if it occurs.

The problem I’m having is that the NOE happens right at the ps.SQLExecute() and doesn’t make it to the dbError evaluation, so I can’t analyze the problem. What’s more interesting is if I modify the statement to be ps.SQLExecute(query) then it completes with out error (neither NOE or dbError) , but the update doesn’t actually occur in the database.

Tim, the “qry” and “query” are not typos. As I said, the preparedstatement is in a Class Method and I’m passing “qry” to the method which has the parameter name of “query”.

Thanks - Stew

5

Fair enough. Usually the db.error will tell you something.

I’m starting to suspect that it’s the Session part of it. Session is ambiguous so it might be there is no current session. That would explain why you’re getting a Nil Object Exception. Can you pass the instance of the session into this function?

6

I agree with your db.error statement, which why I’m so stumped. I’ve been out of RB/Xojo coding for quite a while so I’m having to knock off some rust. But this one doesn’t give me any clue in which direction to look.

I’ll try working with the session, it’s been the source of a couple other problems but I’m not sure it is here. My curiosity is that when I do a product Add it uses this same class with this exact same method and session. The add/insert works every time with out failure. Additionally to debug the problem, you’ll notice in the first part of my post that I have dbgQry, which is the traditional “SQL Injection” methodology and I can do a Session.AppDB.SQLExecute(dbgQry) successfully and it posts the updates to the database. So it seems to be centered around the prepared statement and only when doing updates.

I’m seriously not trying to make this a “Murder Mystery”, I’m just really stumped.

7

Solved:
I figured it out. The Code is Perfect! :wink: The problem was the the Date property in the prodObj class was getting set to 0 and when the prepared statement tried to bind the values to the query, it threw the NOE. It would really be nice if we could get a type mismatch error on something like this, then it wouldn’t be such a snipe hunt when these errors occur.

Thanks to all who responded.

8

Glad it worked out for you. The joys of debugging…

9

On this subject: I’m making my custom db-class injection-proof.

However the documentation of Xojo on MySQLPreparedStatement is not sufficient here. It is not clear to me, what MySQLPreparedStatement-type is equivalent to what MySQL data-type. Maybe you can fill in the gaps:

Constant
MYSQL_TYPE_BLOB = blob
MYSQL_TYPE_DATE = date
MYSQL_TYPE_DATETIME = datetime
MYSQL_TYPE_DOUBLE = double
MYSQL_TYPE_FLOAT = float
MYSQL_TYPE_LONG = integer???
MYSQL_TYPE_LONGLONG = bigint???
MYSQL_TYPE_NULL
MYSQL_TYPE_SHORT = ???
MYSQL_TYPE_STRING = varchar(255) OR Text???
MYSQL_TYPE_TINY = tiny what?

Where is the Text and LongText of Mysql?

10

Victor,

The version of MySQL I’m using has a TinyInt( _TYPE_TINY), MediumInt( _TYPE_LONG), and BigInt( _TYPE_LONGLONG). I use _TYPE_SHORT for the Bit fields since MySQL doesn’t have a boolean type. I record 0 or 1 in the Bit field then programmatically decipher for boolean control types. I rarely use TinyInt, and MediumInt is close enough to Int or Integer that I mostly define my integer fields in MySQL with the Int(10) and then use the _TYPE_LONG for writing to the field.

I didn’t find a specific _TYPE_TEXT either so I’ve been using _TYPE_STRING and it has filled both my Text fields and my Varchar fields with out any problems.

Hope this helps. I know it’s not very scientific but a little bit of hack testing is where the fun lies while using Xojo.

11

A little late to the party, but I was looking for the same info and came across the answer, so I’ll post it here for reference.

The MySQL C language binding documentation pages have more information about the mappings.
Looks like Xojo have exposed the ‘buffer_type’ value. The example link given below is for MySQL 5.6

link text