IBM Lotus Enterprise Integration (LEI) 7.01 is used to act as the data pump, it retrieves scan data (CSV file) on File Server into MySQL database at application server. Even the whole process is nothing to do with Lotus Notes database, the LEI is still my best choice(if performance meets) since no programming(Java, LotusScript or PHP) needed and easy to schedule the tasks.
LEI connection document for Text ODBC Connection :this is to connect the S/N text file uploaded by GoodSync
LEI connection document for MySQL ODBC Connection :
LEI activity document which is a "Direct Transfer" process :this copy the S/N data into MySQL SNOUT2 table
Command Activity document - UPDATE SNOUT WITH NEW SN FROM SNOUT2
It execute the following SQL command which is to update the SN database with new SN on SNOUT2 table
INSERT INTO champ.SNOUT SELECT * FROM champ.SNOUT2 WHERE CONCAT(INVNO, SN) NOT IN (SELECT CONCAT(INVNO, SN) FROM champ.SNOUT) GROUP BY INVNO, PN, SN;