Thread: Insert unique record into database

  1. #1
    Registered User
    Join Date
    Dec 2004
    Posts
    41

    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,

  2. #2
    Registered User hk_mp5kpdw's Avatar
    Join Date
    Jan 2002
    Location
    Northern Virginia/Washington DC Metropolitan Area
    Posts
    3,817
    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.
    "Owners of dogs will have noticed that, if you provide them with food and water and shelter and affection, they will think you are god. Whereas owners of cats are compelled to realize that, if you provide them with food and water and shelter and affection, they draw the conclusion that they are gods."
    -Christopher Hitchens

  3. #3
    Registered User
    Join Date
    Dec 2004
    Posts
    41
    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,

  4. #4
    Registered User hk_mp5kpdw's Avatar
    Join Date
    Jan 2002
    Location
    Northern Virginia/Washington DC Metropolitan Area
    Posts
    3,817
    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).
    "Owners of dogs will have noticed that, if you provide them with food and water and shelter and affection, they will think you are god. Whereas owners of cats are compelled to realize that, if you provide them with food and water and shelter and affection, they draw the conclusion that they are gods."
    -Christopher Hitchens

  5. #5
    Registered User
    Join Date
    Dec 2004
    Posts
    41
    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 !

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Database assignment is Killing me!
    By Boltrig in forum C Programming
    Replies: 2
    Last Post: 11-29-2007, 03:56 AM
  2. Inventory records
    By jsbeckton in forum C Programming
    Replies: 23
    Last Post: 06-28-2007, 04:14 AM
  3. Contest Results - May 27, 2002
    By ygfperson in forum A Brief History of Cprogramming.com
    Replies: 18
    Last Post: 06-18-2002, 01:27 PM
  4. (structure+array+pointer)to make a simple database
    By frankie in forum C Programming
    Replies: 5
    Last Post: 04-26-2002, 05:14 PM
  5. behind and confused
    By steviecrawf in forum C Programming
    Replies: 1
    Last Post: 11-09-2001, 12:51 PM