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