MySQLPreparedStatement

I’m trying to learn about this as I understand it’s a better way to work. What got me here is the need to sanitize data, particularly the ’ often found in names. The thing is I’ve got about 80 fields in this table. Will I have to specify the BindType and then do the Bind spec for all 80 fields? Is there some way to pass the variable data by position right into the prepared statement? For example, I’ve got a simple 4 column table the logs database error. The ID and timestamp fields are auto so I just pass 2 fields. Here’s the code where vCvent_ID and vDBErrorMsg are both string variables:

Dim ps As PreparedSQLStatement=db.Prepare("INSERT INTO `Cvent_DataError`(`Cvent_ID`, `Error_Message`) VALUES (?, ?);") ps.BindType(0, MySQLPreparedStatement.MYSQL_TYPE_STRING) ps.Bind(0, vCvent_ID) ps.BindType(1, MySQLPreparedStatement.MYSQL_TYPE_STRING) ps.Bind(1, vDBErrorMsg) ps.SQLExecute

Is there some way to do something like this:

Dim ps As PreparedSQLStatement=db.Prepare("INSERT INTO `Cvent_DataError`(`Cvent_ID`, `Error_Message`) VALUES (vCvent_ID, vDBErrorMsg);") ps.SQLExecute

I can’t get it to work but maybe if I concatenated the variables into the string it would help. It’s interesting that I don’t have to concatenate the string variables into quoted strings as the prepare does that. Nice. It probably does a lot of other neat junk too.

Instead of separate binds for each type and value, you could try:

ps.SQLExecute(vCvent_ID, vDBErrorMsg)

I’m not sure how well that works with MySQL, but it is worth a shot.

Throws an error: Bind types were not specified, see BindType

Not much invested there. Actually, for clarity, it may be easier to type 80 ?'s and then do the binds for each of them.

I have a helper app written in Xojo that generates mysql prepared statement Xojo source. What OS are you on? I’ll post a link here.

Wayne, I’d be interested in your helper app as well. Thx.

Wayne, that would be cool! I’m on Mac OS 10.7.5.

I got something going here that’s a little unique. I’m getting a record in XML format and parsing the fields into an array. Then I have to match up each item of the array with the correct MySQL field. Then I do an INSERT or UPDATE depending on whether or not the record already exists in MySQL. So my “binds” look something like this:

ps.Bind(2, aIndividualData(19))

Where “aIndividualData(19)” refers to item 19 in a 0 based array. Don’t know if that matters.

You’ll have to do the data binds yourself. This app allows you to select a table from a mysql database, choose which fields you need to insert/update and then generates the sql statement, bindtypes & binds but without the data. Let me know how it works for you Link .

Feel free to download it & try. If you need other than OSX let me know - I develop on Windows myself.

Very cool! I just looked at it quickly and won’t get to play with it until perhaps tomorrow but I think this does it all. I am getting some odd characters though. Perhaps a Windows/Mac thing? Below is a screen shot. Thanks.

Had a problem with encodings. You can get a new version here.

Schweeeeet! That looks like a complete UPDATE statement to me. Very nice! You even made a couple of enhancements. One request would be to be able to paste into the host, user, and password fields. I’ve got 88 fields and this makes it sooo much easier. Wow.

The window sizes correctly except for the “Fields in Where clause” label.

Fixed. You can download again using the link above.

I can do this now on Windows. Do I need to do something special for OSX?

Please check the bindtypes carefully. The translation I’ve used works for the types I’ve used, but I can guaranty that I haven’t used all mySQL data types.

I built this project for my own use because like you I got tired of typing the same sorts of things over & over and isn’t this what computers are good at? I should probably have a method to save preferences, but at the moment all the defaults work for me.

And if we could save em for multiple hosts, that would be awesome.
Anyway, thank you for this nice tool.

I notice that the Edit menu is not present. That’s where the Cut, Copy, and Paste functions generally get enabled.

ummm…while you’re in there…if you could make the “Available Fields” list box independently resizable that would would be good for fields with longer names. Especially if the prefix is the same and that’s all you see.

I like the idea behind this app and will make my own. Am i allowed to make it available on my website (for free) later?

This is pretty cool - thanks Wayne.

I’ve just uploaded a new version. The edit menu is back and the available fields listbox is now the one that grows not the code box. Also the available fields listbox has resizable columns.

Same download link.

DB Link isn’t working anymore