I'm trying to update a MySQL table via FileMaker. I've attempted this in two different ways.

Method 1: Get(FoundCount)

I've got an Execute SQL script doing the following:

"select id from products where id = " & lasercut::id & ";"

Since id is unique for each record, this should either return 1 (if the record exists in the MySQL DB) or 0 (if the record doesn't exist there). Then, if it's 1, it'll update; if it's 0, it will insert. Fairly simple concept, right? Unfortunately, when I use Get(FoundCount) after my select query, it returns the total number of records in my FileMaker DB.

Method 2: Get(LastODBCError)

The other idea I had was to run through the whole database and try to just automatically update every record in the MySQL DB -- if a record didn't exist, it would give an error, and then I would insert that record. Unfortunately, Get(LastODBCError) returns "37000" every time, whether there's an error or not.

So, PLEASE, if anyone can explain why neither of these are working, OR if there's a better way to go about doing what I'm trying to do, help me! Thanks lots!

Okay, Well Why Get(LastODBCError) is returning 37000 - i think you've got a syntax error somewhere in your statement, but returning nothing because nothing is found doesn't constitute an error anyway...

So, what we do instead is run a dud import based on our query -- so make a new layout with one field, called "id".

Make a new script called "check existance". Add the import script step, choose odbc as the source, add your sql statement that will look something like:

SELECT Contact_ID

FROM Contacts

WHERE Contact_ID = 5

Now, if something imports you'll know because there were no records before, and now there are so we just use:

Get(FoundCount) > 0 to check if the record exists

FULL SCRIPT:

Go To Layout[sqlCheck]

Show All Records[]

Delete All Records[]

Import Records[DSN: sqlDSN, SQL TEXT: SELECT Contact_ID FROM Contacts WHERE Contact_ID = 5; Add; Windows ANSI]



If[ Get(FoundCount) > 0]

Set Variable[$error ; "Record Exists"]

Else

Set Variable[ $error ; "Record Doesn't Exist"]

End If

Show Custom Dialog[$error]

Note that this script isn't multi-user safe though -- if you wanted it to be, you'd need to lock it some how at any time it's being used.