Insert unique record into database

Hello All,

I have a program that reads a txt file (is beeing writen every second) and prepare some data to do and insert into a postgres/mysql database. This program is running as a daemon and reads this file every 5 seconds. The reading and the insert part are working just fine !

My problem is:
How should I know which lines I have to insert (to do not insert duplicate rows) ?
If there is a way to control this using memory and the prog stops, how to make the program return to the point that it has stopped and just insert new rows ?

Cany anyone help ?

Thanks in advance,

You should be able to set integrity constraints on the tables on the mysql database side of things to disregard duplicate entries. I routinely work with an Oracle database at work and several of our tables have constraints placed on them so records in those tables can only be unique (with regards to a specific field). You should look up the format for the SQL commands in an MySQL database in regards to creating an integrity constraint. In an Oracle database with an already existing table, it would be done with an "ALTER TABLE ADD CONSTRAINT" command (I believe... its been awhile I could be mistaken). The point is you should be able to look up the format of the command and an example relevant to your setup in some documentation somewhere.

You can also perhaps do something on the file processing side of things but it would depend on how the file gets processed, i.e. if the contents are erased when the program reads the data, or if the file simply has new lines added to it continuously.

Well, I thought using unique fields in the database but I think I would genarate more load than needed. It is a possibility but I realy prefer to make it another way !

Yes, the file simply has new lines added to it continuously ! My prog only has access to read it. So, I my prog should be intelligent enough to "know" witch lines he just did the insert and witch ones he didn't.

Any ideias ?

Thanks again,

Well, your program could count the number of lines it processed from the previous time. Then when it comes time again to read the file, you can loop through a number of lines equal to this count and then start processing the new lines that come after that point. When done with the file you should have a new count of lines processed for next time the program must process the file.

In any case, adding a constraint on the database should be considered since even a new line from the file could potentially have a duplicate "record" that your program would attempt to insert (depends on what your data is and how you determine uniqueness).

hk_mp5kpdw, Thanks for the advice ! I'll start this way ! I've just done it and it's working quite well. If in the future I have a better way for doing this I drop a note here !

Thanks once more !