Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Sep 2005
    Posts
    220

    Unanswered: Do I use a Hash or an Array

    This is a Perl newbie question.

    I have an Oracle table that has 4 columns. I need to query and fetch all rows for a given plannercode.

    Here's the question...Do I fetch the rows into an array or into a hash?

  2. #2
    Join Date
    Feb 2006
    Posts
    56
    probably an array.

  3. #3
    Join Date
    Sep 2005
    Posts
    220
    Can I ask why you said array? Will I be able to reference each of the table fields and their values?

  4. #4
    Join Date
    Feb 2006
    Posts
    56
    Using an array is very common, but you could use a hash too. Or an array of hashes or an array of arrays or .......... you get the picture.

    I have no idea what you are doing so I can't really add much more than that. Plus I really am not so hot with the database stuff anyway, but I see lots of code and stuffing database results into array is a very common practice.

  5. #5
    Join Date
    Sep 2005
    Posts
    220
    Perhaps I should tell you what I want to do before anyone can give me some advice. As I mentioned earlier, I have an Oracle table that has 4 columns. I need to query this table and load all records into either an array or a hash for additional processing. Here's a sample of the table:

    Code:
    planner      param    value       processflag
    M01           abc      A             N
    M01           abc      B             N
    M01           loc      A123          N
    I need to be able to loop through these records and check the various values and do stuff accordingly. For example, if PARAM = 'abc' and value = 'A' do ..., if PARAM = 'abc' and value = 'B' do ..., if PARAM = 'loc' and value = 'A123' do ... I think you get the picture.

    With the above in mind, do I use an array or a hash to access each record's values?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I wouldn't use either an array or a hash based on the problems as you've prsented it. I'd write the script so that it operated on the database itself to avoid (or at least limit) the problems with concurrency, etc.

    If you load the table into the array, what happens to the changes that other users make while your script is running? Do you ignore their changes, and over-write them? What happens if your script can only update part of the rows in the table? Do you roll back the update, or just ignore the problem?

    This just smells like a problem waiting to consume hundreds of hours to me!

    -PatP

  7. #7
    Join Date
    Sep 2005
    Posts
    220
    I appreciate the feedback. Planners are putting data into this table by plannercode using Access, then they run my script to process just their records. I am only extracting the rows for a given planner (the one running my script). Does this change things? Once I am done processing all their records, I will update the processflag for that planner.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I still see it as being bad, but not the potential disaster I was originally thinking it could be.

    It is possible to restructure your script so that it becomes a bunch of UPDATE statements that use their WHERE clauses to limit what rows they affect? This gives you most of the benefits of scripting, without the concurrency problems that come from pulling large parts of a table to a client for manipulation.

    -PatP

  9. #9
    Join Date
    Sep 2005
    Posts
    220
    OK, let me give you some more info on what I'm trying to accomplish. The planners are using the Access app to basically tell me what criteria to use when I pull and then update data from other Oracle tables.

    For example, if the planner enters a parameter of LOC and a value of "A123", then I run off and query a series of tables where LOC = "A123". If the parameter = 'abc' and the value = 'A', I run off and query/update these tables where ABCCODE = 'A'. If they enter 'abc' and value of 'A' and 'B', then I run and select/update rows where ABCCODE in ('A','B').

  10. #10
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by ssmith001
    For example, if PARAM = 'abc' and value = 'A' do ...
    If those will be the only type of questions, *and* the combined PARAM+VALUE columns form a primary key (or at least a column pair without duplicates) it could be worthwile to store the table as a hash (with these two columns as the hash key). This will speed up retrieving the row with a certain (PARAM,VALUE) content.

    If this assumption does not hold, I see no advantage of using a hash over an array, so I would suggest storing as an array (of quadruple references).

    Or probably better, as Pat Phelan suggests, don't store the result table in Perl but process the rows immediately one by one as they are fetched.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  11. #11
    Join Date
    Sep 2005
    Posts
    220
    It's the "two columns as the hash key" part that I don't know how to do.

  12. #12
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by ssmith001
    It's the "two columns as the hash key" part that I don't know how to do.
    That's indeed tricky since a hash key has to be a scalar.
    One solution would be to use a reference to a list of length 2.
    But that's not a good idea for a hash key since it doesn't allow efficient search on the contents of those two values.

    So you will have to somehow construct a scalar from those two fields.
    Most simply by concatenating them, say with a "!" as separator:
    To store the entry: $my_hash{$param . '!' . $value} = [$planner, $process_flag];
    (I.e., you store a reference as "value" part, but a concat as "key" part.)
    To retrieve the entry: ($planner,$process_flag) = @{$my_hash{$param . '!' . $value}};
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  13. #13
    Join Date
    Sep 2005
    Posts
    220
    Thanks Peter. How do I store the data into the hash using the fetchrow_hashref ?

  14. #14
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Your columns are (planner, param, value, processflag, so to get the whole table into a hash struct, you need something like
    Code:
    while ( $ref = $stmt_handler->fetchrow_hashref() ) 
    {
        $my_hash{"$$ref{'planner'}\t$$ref{'param'}"}
          = [ $$ref{'value'}, $$ref{'processflag'} ];
    }
    where %my_hash is the hash list where the table is to be stored, and a tab character is used as separator in the hash key (instead of a "!" sign)
    Last edited by Peter.Vanroose; 09-06-06 at 13:29.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  15. #15
    Join Date
    Feb 2006
    Posts
    56
    'planner' and 'param' are not unique identifiers for the hash keys, so the first instance of planner/param:

    Code:
    M01           abc      A             N
    will be over written by the second instance:

    Code:
    M01           abc      B             N
    and so on for all planner/param rows. I think this structure might work better:

    Code:
    while ( $ref = $stmt_handler->fetchrow_hashref() ) 
    {
       push @{ $my_hash{$$ref{'planner'} },( $$ref{'param'}, $$ref{'value'}, $$ref{'processflag'} );
    }
    or:

    Code:
    while ( $ref = $stmt_handler->fetchrow_hashref() ) 
    {
       push @{$my_hash{$$ref{'planner'}},$ref;
    }

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •