My comments are below designated by [hm]... Hardy Merrill
>>> "Reidy, Ron" <[EMAIL PROTECTED]> 12/16/04 05:43PM >>> See below ... ----------------- Ron Reidy Lead DBA Array BioPharma, Inc. -----Original Message----- From: Follett, Robert [mailto:[EMAIL PROTECTED] Sent: Thursday, December 16, 2004 3:30 PM To: [EMAIL PROTECTED] Subject: Newbie request I am a total perl newbie and was wondering if someone could take a look at my code to determine if I am doing this the most efficient way. It works as it should, but I am sure an expert out there may have some constructive comments to help me make it better. For a little background: I am pulling some ids from a MySQL table into an array, then connecting to Oracle and pulling lab information based on the id's in the array. The lab info will then be loaded back into MySQL. Thanks in advance for any time you have to look at this. [hm] I'm assuming your "connect" is *before* this code where you do "prepare" and "execute" :) $sql = "SELECT mbr_no, mrn, make8mrn FROM make8mrn"; my $sth = $dbh->prepare($sql); $sth->execute(); while (my @data = $sth->fetchrow_array()) { push @hold_mrns, [EMAIL PROTECTED]; } #Connect to Oracle db my $OraDBH = DBI->connect("DBI:Oracle:ourdb",$user,$pwd , {RaiseError => 1}); [ron reidy] You need to set 'AutoCommit => 0' (a good practice, even if not needed here). [hm] I'm not sure I agree with Ron here. Ron is sure the expert here (I am no database expert), but in my limited database experience I'm not sure I agree that AutoCommit => 0 is always good practice. AutoCommit => 0 is useful for transaction processing that usually involves one or more steps including INSERT's and/or UPDATE's, but this app is strictly SELECT'ing info. IMHO, AutoCommit should always be specified, but "good practice" is knowing *when* to use AutoCommit => 0 and when to use AutoCommit => 1. Robert, read up on AutoCommit in the DBI perldocs by doing perldoc DBI at a command prompt. If you're on *nix, search for "AutoCommit" using the forward slash(/AutoCommit). If you plan to do more database coding with Perl and DBI, I would also highly recommend the book "Programming the Perl DBI" by Tim Bunce and Alligator Descartes. #Build date parms (my $yyyy, my $mm, my $dd) = Today(); [hm] This can be done like: my ($yyyy, $mm, $dd) = Today(); my $sql_to_date = "to_date('" . sprintf("%02d", $mm ) . sprintf("%02d", $dd) . "$yyyy','MM/DD/YYYY')"; ($yyyy, $mm, $dd) = Add_Delta_YMD(Today(), 0, -6, 0); my $sql_from_date = "to_date('" . sprintf("%02d", $mm ) . sprintf("%02d", $dd) . "$yyyy','MM/DD/YYYY')"; [ ron reidy] Go read up about the SYSDATE pseudo column in the Oracle docs. The above section of code is not necessary. [hm] Ron is right, SYSDATE should take care of this - no need for any of this extra date code in Perl. I don't know if you can just do this: AND COLLECTION_DATE = SYSDATE since date columns include time, so you might have to play around with TO_CHAR so that you are only comparing the YYYYMMDD of each, something like this: That's about all I see. HTH. Hardy Merrill AND TO_CHAR(COLLECTION_DATE, 'YYYYMMDD') = TO_CHAR(SYSDATE, 'YYYYMMDD') $sql = "SELECT COLLECTION_DATE, TEST, RESULT FROM LABTABLE" . "WHERE (MRN = ? AND COLLECTION_DATE >= $sql_from_date AND COLLECTION_DATE <= $sql_to_date " . "AND (TEST = 'HgbA1C' OR TEST = 'Chol'))"; $sth = $OraDBH->prepare_cached($sql); #Get lab info from Oracle for my $aref ( @hold_mrns ) { get_labs($OraDBH, $sth, @$aref[2]); } } sub get_labs { my ($dbh, $sth, $mrn) = @_; my @hold_labs; $sth->execute($mrn); while (my @data = $sth->fetchrow_array()) { push @hold_labs, [EMAIL PROTECTED]; } for my $aref ( @hold_labs ) { #Load this into mySQL } }
