I am using MySQL.
I have a bunch of fields that I’m using in an update statement.
I am using a PreparedSQLStatement.
When I execute the resulting statement, I’m getting a MYSQL 1064 error, which says I have a syntax error.
Is there anyway I can add a messagebox or see the actual mysql statement that is being executed so I can see what my error is?
I am using web if that matters.
Did you try your SQL in a database access app using test values first?
It’s a good way to debug your statement before you turn it into a prepared statement.
For Mac and MySQL I use Sequel Pro
What does your prepared statement look like now?
I know the syntax I want to use is good, but for some reason when I get all done preparing the actual statement with real data, I’m getting an error.
The documentation for MySQLPreparedStatement http://documentation.xojo.com/index.php/MySQLPreparedStatement doesn’t show a means of accessing the raw SQL string that gets generated.
Post your code and maybe we can help. It could be many things, but we can’t help without the code you’re trying.
The code is part of ActiveRecord, so it won’t make much sense to you, since there is a lot of backend code that’s being run. I think I may have a field type setup incorrectly, but I’m not sure at this point.
I may see if Bob Keeney has any suggestions.
Thanks
Alan
I have mysql logging turned on, but it’s not logging the statement either, maybe because it’s in a transaction?? but I’m not sure.
This is an inherited database, wonder if it’s nulls in some of the fields. I will play some more and maybe try editing a record that has no null fields to see.
ActiveRecord uses transactions, so unless you’re doing your own thing ActiveRecord should be working.
That is most likely the case if you are using ActiveRecord and haven’t written any of your own SQL.
None of the code is mine, just ActiveRecord using the classes that ARGen created.
You may have a field set up incorrectly, I’ve used ActiveRecord and ARGen so I can assure you it does work 
I’m not sure how Bob handles support for ActiveRecord and ARGen, but it couldn’t hurt to ask.
Anything is possible. I don’t believe it’s an ActiveRecord thing, more likely just a field not setup correctly or nulls. I am working on more tests.
The nulls shouldn’t be an issue for ActiveRecord.
ok, I am now checking other things.
I am editing a record, so the existing record values are pretty much staying as is, I am only changing one field in my form and then trying to save. I thought maybe even though there is current data in the record, some fields were Not Null, so I changed them so only the primary key was Not Null and that didn’t help.
I will continue to play with it until I figure out what’s going on.
I have many other tables that are being updated fine in my forms, it’s just this one that is not working. It’s a fairly wide table 89 columns, so it’s probably something special about this specific table. Too bad we don’t have any easy way to see the sql statement that’s being executed because that would show me exactly what my problem is.
Check that all your fields match the database types.
My guess right now that would raise a syntax error within a prepared statement like that is you’re using a non-string AR field type on a MySQL string column. MySQL would be expecting the quotes to contain the value and it’s not getting them because the prepared statement is sending a number or something. Or vice-versa of this situation, where a space is getting sent to MySQL when it’s expecting a different type. The only thing about this is I would expect MySQL to raise something other than a Syntax Error.
That’s a guess, I’ve never run into an issue like that while using ActiveRecord though.
If I recall correctly, there is a way to see the string ActiveRecord is using, but that’s before it’s prepared by PreparedSQLStatement.
I can see if before but since it is essentially assigning all 89 values at one time to the statement, it doesn’t do me much good. I am sure it’s not ActiveRecord issue, probably a data issue, but I’ll keep trying to play with it. Maybe Bob knows of a way to find out what’s going on, but I will continue to play and test.
I figured out what the issue was. As I said this was an inherited database. There was a varchar field called “condition” and condition is a reserved word in MySQL. Once I changed the column to condition1 it worked fine. AR must not enclose column names in tick marks, which is fine with me, I’ll just know for the future.