Dear Monks,

I would like your wisdom and advice on designing on following. I am looking to extract tables from a database and start executing SQL statements in a certain order.

so far, I have four perl programs, primary.pl, first.pl, second.pl, third.pl. primary will be the main program in which i am calling the other three scripts.

primary.pl will call the scripts, first.pl and second.pl will create temp table schema and each will run a huge query that loads data into respective temp table schema. third.pl will join the two temp tables from first and second and load it into third.

my question is,

How can I declare the database connection parameters just once?
when I call the scripts, the first/second/third wont execute without the database connection parameters mentioned in each one of them

how can I serialize the execution? such that subsequent scripts execute if and only if the first one executes successfully.

Is it possible to write multiple statements like dbh->do("create temp schema", "insert table");

Any general wisdom here? am I doing something outlandish?

primary.pl

use dbi;
use warnings;
use strict;

***import database credentials here
usename
password
database
host
driver
dbh

$dbh->do("alter session set current_schema = the current schema”);

my $jobs = `perl C:/first.pl`;
print “first script executed”;

my $jobs = `perl C:/second.pl`;
print “second script executed”;

my $jobs = `perl C:/third.pl`;
print “third script executed”;

*** code to export the final temp table to csv.
[download]

first.pl

use dbi;
use warnings;
use strict;

***import database credentials here
usename
password
database
host
driver
dbh

dbh->do(“create temp table”)

dbh->do(“insert into ttemp table”);

*** exit the database
[download]

second.pl

use dbi;
use warnings;
use strict;

***import database credentials here
usename
password
database
host
driver
dbh

dbh->do(“create second temp table”)

dbh->do(“insert into second temp table”);

*** exit the database
[download]

third.pl

use dbi;
use warnings;
use strict;

***import database credentials here
usename
password
database
host
driver
dbh

dbh->do(“create third temp table”)

dbh->do(“insert into third temp table where you join first and second”
+);

*** exit the database
[download]

thank you for your time

---

---

• Are you posting in the right place? Check out Where do I post X? to know for sure.
• Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:

  <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>

• Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
• Want more info? How to link or How to display code and escape characters are good places to start.