MDB2 query error: VALUES (?, ?, ?, ?, ?, ?)

[lizciz]

I'm fairly new to PEAR but I'm starting to get the hang of it. However, I can't figure out what is wrong this time.

I use the package DB_Table and while doing an insertion with the insert()-function I get an PEAR_Error, which holds the message "syntax error". That didn't help much so I made a var dump on the array returned from the function getDebugInfo() which gave me this:

string(483) "_doQuery: [Error message: Could not execute statement]
[Last executed query: PREPARE MDB2_STATEMENT_mysql_f37a1d25f71ad05dc08388e2f208b0f1 FROM 'INSERT INTO Shows (ShowDate, Result, Judge, City, Union, CatID, ShowID) VALUES (?, ?, ?, ?, ?, ?, ?)']
[Native code: 1064]
[Native message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Union, CatID, ShowID) VALUES (?, ?, ?, ?, ?, ?, ?)' at line 1]
"

That is, the query being executed is
INSERT INTO Shows (ShowDate, Result, Judge, City, Union, CatID, ShowID) VALUES (?, ?, ?, ?, ?, ?, ?)

I've checked the data values before the insertion and they are correct, 100%. I've tried everything I can think of but nothing has helped so far. I've also done insertions on other pages which works fine.

Any ideas?
Thanks

[lizciz]

Now I have made my own backtrace following all the files and functions do the point where I found a function which does all the database queries. In there, I edited a bit so that the query is printed.

It seems the MDB2 first inserts values like (?, ?, ?, ...), then it runs queries like
SET @0 = value1
SET @1 = value2
...

And finally changes the ? to @0, @1 etc.

From another page which works, I cought this query

PREPARE MDB2_STATEMENT_mysql_aeb2d47ee9c2302e26428c4aed0ebf70 FROM 'INSERT INTO Cats (Title, Name, BirthDay, Ems, Sex, Father, Mother, CatID) VALUES (?, ?, ?, ?, ?, ?, ?, ?)'

And as above, here is the one failing

PREPARE MDB2_STATEMENT_mysql_0fb59ce13a389c84b7e37f022e369aaa FROM 'INSERT INTO Shows (ShowDate, Result, Judge, City, Union, CatID, ShowID) VALUES (?, ?, ?, ?, ?, ?, ?)'

See any difference between them? I don't.

[lizciz]

It just hit me! I'm using the column name 'Union', which is also a reserved key word in SQL, no wonder the query could not be executed! I simply changed the column name to 'Club' and everything started working!

To bad there isn't a built in function telling you if your using a reserved key words as column name, since it would have saved me 4 hours of frustration

[mark]

[lizciz]

I use DB_Table together with HTML_QuickForm. When the form was correctly filled in the data were to be added to the database.

Like this

[mark]

Okay, getDebugInfo() wasn't new for you, then. Just as an explanation why DB_Table behaves this way: The reason is that MDB2 (and also DB) report these short error messages back, and DB_Table can only pass them back to the user. The alternative would be to parse the debug information from MDB2 but that's something that would need days of work, and that would be never really ready and up-to-date. If you have any ideas on how this could be improved, please share them.