MySQL - Inserting apostrophe question

Databases
1

Does xojo have a command or utility for inserting strings containing apostrophes into a MySQL table?

2

Yes, a PreparedSQLStatement or InsertRecord.

3

I had the same problem a few weeks ago - Prepared statements work nicely with saving apostrophes to the database.

4

I’m not too familiar with “Prepared Statements” … but are they on the server side?

What I do now is use a basic SQL command within the code. Either INSERT INTO or UPDATE .

5

I took a quick look at InsertRecord … can that be used against a MySQL table using xojo’s mysql connector?

6

You should get familiar with Prepared Statements or, as I said, Database.InsertRecord which uses a prepared statement in the background. RecordSet.Edit is safe too.

7

Sure.

8

Thanks, I’ll check into those options.

9

currently I use SQLExecute

10

For some reason I was under the assumption that appending to or updating a MySQL database using recordsets either didn’t work in xojo or wasn’t a good idea and it was better to use an SQL statement and SQLExecute.

11

I’m not aware of that, but converting to a PreparedSQLStatement is probably easier anyway.

If your code is currently something like:

db.SQLExecute "UPDATE mytable SET myfield = '" + Sanitize( theData ) + "'"

You can convert that pretty quickly to:

dim ps as PreparedSQLStatement = db.Prepare( "UPDATE mytable SET myfield = ?" )
ps.BindType( 0, MySQLPreparedStatement.MYSQL_TYPE_STRING )
ps.SQLExecute( theData )
12

Read this thread https://forum.xojo.com/4921-mysqlpreparedstatement

Wayne developed a helper and Sasha evolved the idea.
The tool is used to help you to design your statements.
It’s shared in Github.

13

I was advised to not use that.

What I do (actually, but I have to go the PreparedStatement way) is to *:

a. at import time: replace ’ with ’ (standard quote by curly quote),
b. at user TextArea or TextField entering time: replace ’ with ’ (standard quote by curly quote) too.

I have to take time asap to modify my code (one more time) to use PreparedStatements.

  • I think I do the same tricks with some other weird characters (characters Databases and Excel do not love as is), but I forgot what they are…
14

[quote=95480:@Rick Araujo]Read this thread https://forum.xojo.com/4921-mysqlpreparedstatement

Wayne developed a helper and Sasha evolved the idea.
The tool is used to help you to design your statements.
It’s shared in Github.[/quote]

I downloaded Sascha’s “tool” and was able to implement appropriate code which works perfectly for my needs.

Thanks all for the help.