Re: Re: modularizing database script?

Here is my code:

my $dbh= DBI->connect("DBI:Oracle:", "scott", "tiger") or  die $DBI::e
+rrstr;
my $sth1= $dbh->prepare(q{truncate table directory_data}) or die $DBI:
+:errstr;
$sth1->execute or die $sth1->errstr;

my $sth = $dbh->prepare(q{
    INSERT INTO directory_data(dn, cn, surname, given_name, initials, 
+generational_qualifier, building,
    room_number, mailstop, telephone, fax, mail, tfs_id, uid) 
    VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)}) or die $dbh->errstr;

foreach $emp (keys %employee){ 
$sth->execute($employee{$emp},.,....)
[download]

Currently I have created the Oracle table using SQL Plus. I would like to,however,create the table using a different perl script that I call within this script. Also each time I call the original script I want it to drop the current table and plot the new information. I would like to perform these sql functions in a different script as well. I was told that creating these different scripts will clean up my code. I hope this clarifies my question and again thanks for any help.

Comment on Re: Re: modularizing database script? Download Code

Replies are listed 'Best First'.
(jeffa) 3Re: modularizing database script? by jeffa (Bishop) on Dec 26, 2002 at 17:37 UTC
Spreading your code out among several different files won't really clean up your code ... it will just spread it out among several different files. :) What you need to do is search the CPAN for some modules that will make your task easier ... modules like DBIx::Connect which will allow you to abstract the DB connect info out of your scripts (and into a config file). However, before i start ripping my one script into a modularized, maintainable package ... i'd clean up what you have. Here is how i would code your snippet: `my $dbh = DBI->connect( qw(DBI:Oracle: user pass), {RaiseError => 1}, ); # with RaiseError turned on, no need for die and errstr $dbh->do("truncate table directory_data"); my $sth = $dbh->prepare(q{ INSERT INTO directory_data( dn, cn, surname, given_name, initials, generational_qualifier, building, room_number, mailstop, telephone, fax, mail, tfs_id, uid ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?) }); $sth->execute($employee{$_},.,....) for keys %employee;` [download] Sometimes i like to use code to generate an INSERT statement: `my @field = qw( dn cn surname given_name initials generational_qualifier building room_number mailstop telephone fax mail tfs_id uid ); my $table = 'directory_data'; my $sql = "insert into $table (" . join(',', @field) . ') values (' . join(',', map '?', @field) . ')' ;` [download] You could turn that snippet into a subroutine and pass it the name of the table, an reference to an array of field (column) names, and a reference to another array that contains the values (just make sure the two arrays line up!). Hope this helps and gives you some ideas. :) jeffa L-LL-L--L-LL-L--L-LL-L-- -R--R-RR-R--R-RR-R--R-RR B--B--B--B--B--B--B--B-- H---H---H---H---H---H--- (the triplet paradiddle with high-hat)	[reply] [d/l] [select]
Re: Re: Re: modularizing database script? by CountZero (Bishop) on Dec 26, 2002 at 21:58 UTC
If you want to further 'abstract' your code DBIx::Recordset is worth having a look at. CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law	[reply]
Re: Re: Re: modularizing database script? by osama (Scribe) on Dec 27, 2002 at 18:05 UTC
Why would you want to create the table using a different perl script??? Your `truncate` will delete all the data... do you need to run the script somewhere where the table does not exist? I think that having one script will make your code cleaner... since this seems like a small script... no need to spread all the code in different files... How about splitting it in different subroutines? you would probably want to replace the `truncate` by a `drop` and a `create`, but I don't think it's a good idea to do so in Oracle... (Will the table be in a separate Tablespace? How will extents allocation go, How frequently will you do this... it depends on so many factors)	[reply] [d/l] [select]