#1
  1. Contributing User
    Devshed Demi-God (4500 - 4999 posts)

    Join Date
    Aug 2011
    Posts
    4,897
    Rep Power
    481

    Keep connection to remote database open


    Project: populate a database using data that becomes more complete over time, acquiring data as soon as it's available. A new set of events occurs daily.

    Method: 3 passes are deemed sufficient.
    Code:
    insert
      The initial pass collects yesterday's results,
      inserting into database.
    
    update
      for n in 2 and 30 days ago:
        gather the possibly more detailed results from n days ago,
        updating records inserted n days ago.
    A common select statement plus one detail will identify the rows for update. I'd store the common value in a variable because it seems that doing so would be substantially more efficient. However, the variable is lost if the connection drops. This method seems unsafe.

    The program I've got writes a sql statements to a script, then processes the script, so the statements come along as fast as the computer will process them. There's no animal intervention. On the other hand, I didn't write a code that opens a connection and maintains a cursor.

    Roughly 10 groups of 200 rows by 10 columns need updated. There are 5 tables in a hierarchy.
    Code:
           event
          /    \
    summary     a      
               / \
              b   c
    each table has an auto_increment id field that is copied as a column into the table below it.

    Question: what's a preferred approach? My mysql skills are sufficient to create and populate databases, and I can write nested select statements. I hope, but am not sure this translates to "not completely clueless".

    Thank you for considering this request, Dave.
    Last edited by b49P23TIvg; March 12th, 2013 at 09:48 PM.
    [code]Code tags[/code] are essential for python code and Makefiles!
  2. #2
  3. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    Hi Dave,

    I'm sorry but I've read your post thoroughly 3 times and I still don't really understand what you are asking.

    I understand the row -"Question: what's a preferred approach?", but I don't understand what specifics you are uncertain about.

    Sorry
    /Stefan
  4. #3
  5. Contributing User
    Devshed Demi-God (4500 - 4999 posts)

    Join Date
    Aug 2011
    Posts
    4,897
    Rep Power
    481
    I finally thought of a solution which is to retain the data I loaded separately from the database. I'll look to see if the updates change the information in my own backup. I need only keep data for a week.
    [code]Code tags[/code] are essential for python code and Makefiles!

IMN logo majestic logo threadwatch logo seochat tools logo