Can't seem to get around this SQL parse error 1200 in FileMaker Pro...???

Andrew Angell
Andrew Angell used Ask the Experts™
on
I'm trying to do some very basic SQL updates on this FileMaker DB I'm working with and for some reason I can't get around this parse error 1200.

The table I'm working with in FileMaker is called Gourock Invoice.  I'm getting parse errors every time I try and update via SQL and I can't figure out where my problem is.

Here's one example...

UPDATE Gourock Invoice
                         SET g_PayPal_Error_Code = 10002,
                                g_PayPal_Short_Message = 'Security error',
                               g_PayPal_Long_Message = 'Security header is not valid'
                         WHERE id_invoice = 133987

g_PayPal_Error_Code is a number field, as is id_invoice (although I have tried adding quotes around those values as well).  

Here's another...

UPDATE Gourock Invoice
                     SET Credit card Authorization = '8Y781148UU567701C',
                     Card Number = '************5916'
                     WHERE id_invoice = '133987'

Card Number is indeed a text field.  I'm getting parse errors on both of these.   Apparently I need another pair of eyes to help me figure out what I'm doing wrong here.  I work with SQL all the time and I simply can't find my problem.  

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
leakim971Multitechnician
Top Expert 2014

Commented:
Hello angellEYE,

Try (table name in quote) :



UPDATE 'Gourock Invoice' SET g_PayPal_Error_Code = 10002, g_PayPal_Short_Message = 'Security error', g_PayPal_Long_Message = 'Security header is not valid' WHERE id_invoice = 133987

Open in new window

leakim971Multitechnician
Top Expert 2014

Commented:
or :


UPDATE "Gourock Invoice" SET g_PayPal_Error_Code = 10002, g_PayPal_Short_Message = 'Security error', g_PayPal_Long_Message = 'Security header is not valid' WHERE id_invoice = 133987
OR AGAIN :)
UPDATE [Gourock Invoice] SET g_PayPal_Error_Code = 10002, g_PayPal_Short_Message = 'Security error', g_PayPal_Long_Message = 'Security header is not valid' WHERE id_invoice = 133987

Open in new window

Andrew AngellCo-Owner / Developer

Author

Commented:
Nope, that's not helping.  I've actually done this in FileMaker quite a few times and I've never had to put quotes around the table names before.  I've always just followed my regular, basic, sql syntax and have never had this problem before.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

leakim971Multitechnician
Top Expert 2014

Commented:
And quotes :




UPDATE 'Gourock Invoice' SET g_PayPal_Error_Code = '10002', g_PayPal_Short_Message = "Security error", g_PayPal_Long_Message = "Security header is not valid" WHERE id_invoice = '133987'

Open in new window

leakim971Multitechnician
Top Expert 2014

Commented:
Could you restart you query editor ? Perhaps a hidden field somewhere...
Andrew AngellCo-Owner / Developer

Author

Commented:
Query editor...???
leakim971Multitechnician
Top Expert 2014

Commented:
Where are you running your query ? Close it before retrying.
Commented:
not sure fm works without the table name as prefix of the field names
try 'Gourock Invoice'.'fieldname' for the field names
I am also a bit suspicious about spaces in table names, many if not SQL dbs do not tolerate this normally.
Andrew AngellCo-Owner / Developer

Author

Commented:
Yeah I was thinking about that space, too.  I've done this before with FM and I never needed to use tablename.fieldname so I think it's gotta be the space.  I'm gonna play with that and I'll update in a bit.

Commented:
as a matter of fact, fm doc does not mention any quote for table and fields, and no table name before field names.
so they probably assume there won't be any space in table names? quite wyse indeed but not in the filemaker workd where there are lots of mac users, and this is a population which like unrestricted names!
Andrew AngellCo-Owner / Developer

Author

Commented:
Yup, it was the spaces.  

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial