• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

SQL and Filemaker- Getting information about Deleted Records from SQL Database

I'm using an SQL database to record information about conference attendees. My Filemaker database utilizes the ODBC driver/plug-in from Actual Technologies to communicate with the SQL database. My Filemaker database checks for records that are modified or added (by utlizing the maximum timestamp) and then downloads/updates those records. The only problem I have is, I don't know how to check for records that have been deleted from the SQL database. My Filemaker reporting needs to be accurate, which means it would need to exclude records that have been deleted from the SQL database, but how can my Filemaker database know which records those are?
0
wesbrowning
Asked:
wesbrowning
  • 3
  • 2
1 Solution
 
lesouefCommented:
you can check if every fm record exists in the sql base (with a simple select return error), but thats gonna ages if the base is big.
or
you could disable deletion on sql side, ask users to mark records to be deleted with a specific field a check box or something, instead of deleting, and then have filemaker to query these, delete its local copy and the sql record at the same time.
I know nothing smarter so far.
0
 
wesbrowningAuthor Commented:
how would i do the "select return error"? The option of marking the records on the SQL side is not an option for us right now, as the programmer feels it will take too much time on their end to do it that way.
0
 
lesouefCommented:
return error: that depends on your plug-in which I do not know.
 using the regular fm SQL commands, you can use the get (lastODBCerror ) to find out what's wrong, and then decide the record is not found.

sql side, too much work to implement one field and remove the delete button from forms??

otherwise, you could delete all records everytime you use it and re-import, but I guess yr base is too big to do that....
the alternative is to have the SQL server to delete obsolete records in the fm base using the fm odbc driver, but there again, yr dev people are not gonna like it.
0
 
wesbrowningAuthor Commented:
I'm using the ODBC driver from Actual Technologies, which I would assume uses standard SQL commands. How could I tell it to find the records that are in my database? I assume you're telling me that if it does a find (or matching of some kind), it will return an error message that contains information about all the records that it couldn't find (which I would extract info on which records to delete on my end). What command would I send it to make this happen?
0
 
lesouefCommented:
Sorry, I got confused (starts like that you know!), I thought you were using a SQL plug-in, its just another ODBC driver.
So you can issue a select command in a "run SQL" using the record key field, and see what it returns, you would ignore a good answer, and delete local records for which you got a "nothing found". The odbc error number mirror the standard SQL errors, I don't have the list, but you can easily work it out by experience with a know missing record. See fm doc at " lastODBC error".
I may have it in a partial file @ home I did a long time ago, but I am not there at the moment.
You could also try to import the record in a temp table, and if there is none, deduce it is missing on SQL base side.
The big drawback of this method is that you need to run a loop record per record, and that's gonna be lengthy...
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now