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

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.