Can Perl replace SQR Functionality?

[Scott]

I work in HR software support and development. One of my responsibilites is
to support and develop custom built SQR's that interface with databases for
a myriad of differenet purposes. Today, a few of us in the office were
reminiscing on the beauty of Perl and the nearly limitless capabilites that
Perl seems to have. Then I started to think about what if Perl could replace
the functionality of what SQR's do. I somewhat familiar with Perl DBI and
such and I know that Oracle DBA's are known to use Perl in the
Administration tasks.

Therefore my following question. Does anyone know if Perl can provide the
same kind of functionality that the SQR language does for Relational
databases? If it can, what's the feasibility of doing it in Perl? What I
mean is if Perl can do the same things as the SQR language does, how much
more difficult or less difficult it is to do in Perl with everything being
equal (neaning that one's skill and knowledge is the same in both Perl and
SQR)?

Any thoughts would be appreciated.

Thanks,

Scott

 

[Uri Guttman]

S> I work in HR software support and development. One of my
S> responsibilites is to support and develop custom built SQR's that
S> interface with databases for a myriad of differenet
S> purposes. Today, a few of us in the office were reminiscing on the
S> beauty of Perl and the nearly limitless capabilites that Perl seems
S> to have. Then I started to think about what if Perl could replace
S> the functionality of what SQR's do. I somewhat familiar with Perl
S> DBI and such and I know that Oracle DBA's are known to use Perl in
S> the Administration tasks.

S> Therefore my following question. Does anyone know if Perl can
S> provide the same kind of functionality that the SQR language does
S> for Relational databases? If it can, what's the feasibility of
S> doing it in Perl? What I mean is if Perl can do the same things as
S> the SQR language does, how much more difficult or less difficult it
S> is to do in Perl with everything being equal (neaning that one's
S> skill and knowledge is the same in both Perl and SQR)?

it would help if you would explain what SQR is.

uri

 

[Scott]

That's fine. SQR is an acronym for "Structured Query Reporter". You use it
to interface with a database to do several different functions.

Among the uses that we employ SQR for:

1. Extracting data and writing it to some sort of output file for custom
reports.
2. Extracting data (HR benifit information like dental or 401k
information for employees) for outbound interface file to be sent to benefit
provider for new enrollees. e.g. format would be fixed width, .csv, etc.
3. To update, insert, or delete data from database tables.
4. Used to extract database data for migration from one vendor's HR
backend database and insert it into a new and different vendor database.

I think you get the idea...

S> I work in HR software support and development. One of my
S> responsibilites is to support and develop custom built SQR's that
S> interface with databases for a myriad of differenet
S> purposes. Today, a few of us in the office were reminiscing on the
S> beauty of Perl and the nearly limitless capabilites that Perl seems
S> to have. Then I started to think about what if Perl could replace
S> the functionality of what SQR's do. I somewhat familiar with Perl
S> DBI and such and I know that Oracle DBA's are known to use Perl in
S> the Administration tasks.

S> Therefore my following question. Does anyone know if Perl can
S> provide the same kind of functionality that the SQR language does
S> for Relational databases? If it can, what's the feasibility of
S> doing it in Perl? What I mean is if Perl can do the same things as
S> the SQR language does, how much more difficult or less difficult it
S> is to do in Perl with everything being equal (neaning that one's
S> skill and knowledge is the same in both Perl and SQR)?

it would help if you would explain what SQR is.

uri

http://jobs.perl.org

 

[Tad McClellan]

One of my responsibilites is
to support and develop custom built SQR's

What are SQR's?

Are we supposed to already know what they are?

Therefore my following question. Does anyone know if Perl can provide the
same kind of functionality that the SQR language does for Relational
databases?

What kind of functionality does the SQR language provide for
Relational Databases?

Any thoughts would be appreciated.

Tell us what you are talking about.

 

[Uri Guttman]

first, learn to not top post. see the posting guidelines for more.


S> 1. Extracting data and writing it to some sort of output file
S> for custom reports.

S> 2. Extracting data (HR benifit information like dental or 401k
S> information for employees) for outbound interface file to be sent
S> to benefit provider for new enrollees. e.g. format would be fixed
S> width, .csv, etc.

S> 3. To update, insert, or delete data from database tables.

S> 4. Used to extract database data for migration from one
S> vendor's HR backend database and insert it into a new and different
S> vendor database.

so it is more a report generator than anything else. check cpan for such
beasts. there are a large number of DBI related modules that can do some
of those things. the issue is how powerful is sqr (most report
generators aren't that powerful) and do you want the same or similar
syntax? with the cpan modules you would have to code up stuff to make
your reports and queries. if you made them generic enough you could
drive them from other code that parses and executes report commands.

then you have a basic SQR in pure perl.

or you could hire someone to do this development for you.

uri

 

[John M. Gamble]

Therefore my following question. Does anyone know if Perl can provide the
same kind of functionality that the SQR language does for Relational
databases? If it can, what's the feasibility of doing it in Perl? What I

Sort of. The part that will get you is grouping, and if your reports
use it heavily then you'll need to do some generic coding to handle
the different cases.

When i decided to avoid SQR whenever possible (it had bugs, or at
least the version that i used years ago did), it wasn't a terribly
difficult process. Run the SQL statements through DBI, print the
results. This handled almost all of the cases needed.

The one case where it didn't involved SQR's groups (not to be
confused with SQL "GROUP BY" [hmm, and it's been long enough that
i may be using the wrong term]), which i handled using perl's hash
data structure, delaying output until all of the consolidated data
was available.

mean is if Perl can do the same things as the SQR language does, how much
more difficult or less difficult it is to do in Perl with everything being
equal (neaning that one's skill and knowledge is the same in both Perl and
SQR)?

It's pretty much select-from-db-and-print. I have only used perl's
format statements once, they are otherwise unnecessary. DBI (the
module used to select data from your tables) is a breeze to use.
I don't forsee any problems, even if you have to do an SQR group
(am i using the term correctly? can't remember).

Any thoughts would be appreciated.

Hope that this helps.

 

[James Willmore]

I work in HR software support and development. One of my responsibilites
is to support and develop custom built SQR's that interface with
databases for a myriad of differenet purposes. Today, a few of us in the
office were reminiscing on the beauty of Perl and the nearly limitless
capabilites that Perl seems to have. Then I started to think about what
if Perl could replace the functionality of what SQR's do. I somewhat
familiar with Perl DBI and such and I know that Oracle DBA's are known
to use Perl in the Administration tasks.

Therefore my following question. Does anyone know if Perl can provide
the same kind of functionality that the SQR language does for Relational
databases? If it can, what's the feasibility of doing it in Perl? What I
mean is if Perl can do the same things as the SQR language does, how
much more difficult or less difficult it is to do in Perl with
everything being equal (neaning that one's skill and knowledge is the
same in both Perl and SQR)?

Any thoughts would be appreciated.

Try this question in the DBI user's mailing list (http://dbi.perl.org/ and
select the link for "Mailing Lists"). There are a few Oracle DBA's that
frequent the list and may know right off the bat how to integrate the two.

Since SQR is used for reporting, you may be able to ditch using SQR and
use a pure Perl solution for reporting (since Perl is the Personal
Extraction and Reporting Language).

One suggestion is to perform a base query using DBI, output the results to
XML, then use one of the XML modules to extract the required data and
perform calculations. This will be portable for most shops, so you may be
able to use solutions already in place. And, since the output is in XML,
you can use other applications to do such things as calculations (of
vacation time, sick time, etc. etc etc.), deliver content, etc. And it
all doesn't have to be from Perl (but, of course, I biased) since the
initial output is in XML. You could use Java to do whatever to the output
(this makes the managers happy, since the world seems to start and end
with Java in some shops - like where I work ... but I digress).

There's a whole range of solutions out there for you using Perl. You just
need to examine you needs and go from there.

HTH and makes sense (not enough coffee yet ;-) )

--
Jim

Copyright notice: all code written by the author in this post is
released under the GPL. http://www.gnu.org/licenses/gpl.txt
for more information.

a fortune quote ...
It's not so hard to lift yourself by your bootstraps once you're
off the ground. -- Daniel B. Luten