AnsweredAssumed Answered

Use Execute SQL to check record count before importing?

Question asked by cmskatz on Aug 31, 2018
Latest reply on Aug 31, 2018 by fmpdude

Hi all

 

I use a server side import sql script to import from another table in an Oracle database. This works fine except when the Oracle database itself is not populated. The Oracle database syncs several times a day with an aging AS400 system and periodically fails usually leaving table(s) completely empty. I have no other access to the oracle tables other than via the ODBC drivers. I do however know the schedule and duration and therefore what times to avoid.

 

I know how many records where imported last time the script ran successfully so simply want to add steps to the import script to check the record count in the external table before proceeding with the import and abort if the record count is not the same or greater as there should never be less.

 

Could the execute SQL script or function be used to populate a variable with the record count from the relevant Oracle table then a simple 'if' step in the script can compare that with the previous import?

 

Belt and braces approach would be to have another table in my solution with a single field from the external table, import using the following-

SELECT ProductMD.PRODUCTCODE

FROM ProductMD ProductMD

then count those records.

 

Hopefully there is something more elegant without the need for an importing additional data as the connection speed is not great.

 

I am using Filemaker 16 Advanced on Windows with Filemaker 16 server.

 

Many thanks

Outcomes