RE: DBI and Oracle 8I
I'm running it successfully against Oracle 8.1.6, if that helps. Try starting with DBI-1.18 and DBD-Oracle-1.07 from CPAN. Follow instructions for building and installing those packages and pay *very* close attention to README.java in the DBD distribution. If you have any problems try rebuilding your Perl with -lthread; that did it for me. Regards, Jonathan -Original Message- From: Conrad, Bill (ThomasTech) [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 05, 2001 4:27 PM To: [EMAIL PROTECTED] Subject: DBI and Oracle 8I Hi All I don't know if this is the right place to ask this but here goes. Please direct me to other lists if appropriate. I would like to use Perl to access are Oracle 8I version 817 database on Solaris 2.6. 1) Do the current DBI packages support this? 2) If so, what packages do I need? Thanks Bill Conrad
Re: DBI and Oracle
Emma Wermström (EMW) ([EMAIL PROTECTED]) wrote: > Thank you! > > I think you understand my dilemma. > Installing DBD::Oracle requires that I install "some Oracle software such as Pro*C", >according to the DBD::Oracle README. Do you know exactly what I need? > I've already installed Apache::DBI and I'm running one of the latest versions of >perl and mod_perl. > Thanks again, Hmmm, isn't Pro*C the C-Compiler? I can only talk about the Solaris requirements to set things up, but here we go. On the source machine, you need a C-Compiler - gcc should do - a Oracle client installation *AND* the Oracle Sample Code - there you'll find the required headerfiles, but I never figured out what Oracle installation options you need to get them installed. After extracting the DBD-Oracle tar file, change into its directory and do a perl Makefile.PL depending on your Oracle version you'll get a security waring - tell your DBA - and a flashy screen telling you that you're missing the RPC and Proxy stuff - they're not required and come in additional packages. Continue with make to build the stuff and test it with make test Note, that make test requires the scott/tiger users and the test database, otherwise you need to set ORACLE_SID and ORACLE_USER or a combination of both - sorry can't remember that detail - to let the test scripts successfully log into your database. If everything goes well, finally do a make install and you're done. I hope I haven't forgotten too much stuff... Mike -- If we fail, we will lose the war. Michael Lamertz | [EMAIL PROTECTED] / [EMAIL PROTECTED] Nordstr. 49 | http://www.lamertz.net 50733 Cologne| Work: +49 221 3091-121 Germany | Priv: +49 221 445420 / +49 171 6900 310
RE: DBI and Oracle
You the Oracle-Client Installation on the machine where you want to use DBD::Oracle. This should be an option in the Oracle installer. If I remember correctly Pro*C is an option you have to select in the installer as well. cr On Wed, 25 Apr 2001 15:22:05 +0200, Emma Wermström (EMW)<[EMAIL PROTECTED]> said: > Thank you! > > I think you understand my dilemma. > Installing DBD::Oracle requires that I install "some Oracle software such as >Pro*C", according to the DBD::Oracle README. Do you know exactly what I need? > I've already installed Apache::DBI and I'm running one of the latest versions of >perl and mod_perl. > Thanks again, > > Emma > > -Original Message- > From: Michael Lamertz [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, April 25, 2001 1:11 PM > To: Emma Wermström (EMW) > Cc: '[EMAIL PROTECTED]' > Subject: Re: DBI and Oracle > > > Emma Wermström (EMW) ([EMAIL PROTECTED]) wrote: > > Hi! > > > > I'm a beginner (to databases aswell as perl) trying to use perl code embedded in >html to access an Oracle database. The trouble I'm having is that the database >requires two login procedures. The first one to access the actual database and a >second one to access SQL (sql+) within the database. How can I implement this using >the > > $dbh->connect() ? > > I'm not sure I understand what you mean. As I interprete your question, > the first login happens during some telnet/ssh connect on the machine, > and the second is the SQL+'s username and password prompt? > > Using DBI things are different. DBD::Oracle can talk directly to a > database listener on a foreign machine, without the need to open a shell > on that machine or start SQL+, so providing the $DBI->connect(...) with > the correct data - the one you type into SQL+ - should be sufficient. > > -- > If we fail, we will lose the war. > > Michael Lamertz | [EMAIL PROTECTED] / [EMAIL PROTECTED] > Nordstr. 49 | http://www.lamertz.net > 50733 Cologne| Work: +49 221 3091-121 > Germany | Priv: +49 221 445420 / +49 171 6900 310 > >
RE: DBI and Oracle
Thank you! I think you understand my dilemma. Installing DBD::Oracle requires that I install "some Oracle software such as Pro*C", according to the DBD::Oracle README. Do you know exactly what I need? I've already installed Apache::DBI and I'm running one of the latest versions of perl and mod_perl. Thanks again, Emma -Original Message- From: Michael Lamertz [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 25, 2001 1:11 PM To: Emma Wermström (EMW) Cc: '[EMAIL PROTECTED]' Subject: Re: DBI and Oracle Emma Wermström (EMW) ([EMAIL PROTECTED]) wrote: > Hi! > > I'm a beginner (to databases aswell as perl) trying to use perl code embedded in >html to access an Oracle database. The trouble I'm having is that the database >requires two login procedures. The first one to access the actual database and a >second one to access SQL (sql+) within the database. How can I implement this using >the > $dbh->connect() ? I'm not sure I understand what you mean. As I interprete your question, the first login happens during some telnet/ssh connect on the machine, and the second is the SQL+'s username and password prompt? Using DBI things are different. DBD::Oracle can talk directly to a database listener on a foreign machine, without the need to open a shell on that machine or start SQL+, so providing the $DBI->connect(...) with the correct data - the one you type into SQL+ - should be sufficient. -- If we fail, we will lose the war. Michael Lamertz | [EMAIL PROTECTED] / [EMAIL PROTECTED] Nordstr. 49 | http://www.lamertz.net 50733 Cologne| Work: +49 221 3091-121 Germany | Priv: +49 221 445420 / +49 171 6900 310
Re: DBI and Oracle
Emma Wermström (EMW) ([EMAIL PROTECTED]) wrote: > Hi! > > I'm a beginner (to databases aswell as perl) trying to use perl code embedded in >html to access an Oracle database. The trouble I'm having is that the database >requires two login procedures. The first one to access the actual database and a >second one to access SQL (sql+) within the database. How can I implement this using >the > $dbh->connect() ? I'm not sure I understand what you mean. As I interprete your question, the first login happens during some telnet/ssh connect on the machine, and the second is the SQL+'s username and password prompt? Using DBI things are different. DBD::Oracle can talk directly to a database listener on a foreign machine, without the need to open a shell on that machine or start SQL+, so providing the $DBI->connect(...) with the correct data - the one you type into SQL+ - should be sufficient. -- If we fail, we will lose the war. Michael Lamertz | [EMAIL PROTECTED] / [EMAIL PROTECTED] Nordstr. 49 | http://www.lamertz.net 50733 Cologne| Work: +49 221 3091-121 Germany | Priv: +49 221 445420 / +49 171 6900 310
RE: DBI and ORACLE
[Copying the list on a reply to a private email, with permission]
A placeholder consists of a single question mark ("?") in your query
statement. It replaces a single value in your SQL statement. For example:
Without placeholders:
INSERT INTO MYTABLE VALUES ('Mark', 'Nutter', 'foo', 'bar', 'baz')
With placeholders
INSERT INTO MYTABLE VALUES (?, ?, ?, ?, ?)
Your code looks like this:
> my $query1=<<"QUERY";
> INSERT INTO DIFFAMNTS VALUES ($1,$2,$3,$4,$5,$6,$7,$8) # ARE THESE
> CORRECT???
> QUERY
This is correct, but it does not use place holders. At run-time, Perl does
a string substitution, so if, say, $1 is equal to "Mark", $2 is equal to
"Nutter", and so on, then the query that gets passed to DBI is
INSERT INTO DIFFAMNTS VALUES (Mark,Nutter,foo,bar,baz,...)
Now, if your columns are non-numeric, that query is not correct, because you
need to wrap your non-numeric data values in single quotes, like this:
INSERT INTO DIFFAMNTS VALUES ('Mark','Nutter','foo','bar','baz',...)
What's cool about placeholders is that you can stop worrying about the
difference between quoted and unquoted values -- you don't need to use
quotes at all with placeholders. This comes in real handy if you ever need
to insert large, multi-line values into a single database column:
my $query = execute($url, $page) or die $DBI::errstr;
Now the $page variable can include newlines, quotes, etc, and you don't have
to worry about it. The alternative would blow up in your face:
my $query = "INSERT INTO PAGES VALUES($url, $page)"; # Boom!
Behind the scenes what's happening is that, with placeholders, key pieces of
your data are being processed by DBI/DBD rather than by the Perl language
itself. For more sophisticated databases engines like Oracle, placeholders
are more efficient than query strings with embedded values. Here's how:
# Ordinary query
for $bar (qw(Eeny Meeny Miny Mo))
{
$query = EOM;
INSERT INTO FOO VALUES ('$bar')
EOM
$sth = $dbh->prepare($query);
$sth->execute();
}
Each time through the loop, Perl interpolates the value of $bar into the
query string, and the database engine gets a new query string that is
different from all previous strings:
INSERT INTO FOO VALUES ('Eeny')
INSERT INTO FOO VALUES ('Meeny')
INSERT INTO FOO VALUES ('Miny')
INSERT INTO FOO VALUES ('Mo')
The database engine has to parse each SQL query, which takes a certain
amount of time. Once the query is parsed, then the database actually moves
the data from the input buffer into the actual tables. Contrast this with
the placeholder approach:
# Query with placeholders
$query = EOM;
INSERT INTO FOO VALUES (?)
EOM
$sth = $dbh->prepare($query);
for $bar (qw(Eeny Meeny Miny Mo))
{
$sth->execute($bar);
}
Thanks to placeholders, we were able to move the prepare() statement outside
the loop. The database engine only parses the query once (in the prepare()
statement). Then, in the execute() statement, each value is simply copied
to the database engine's input buffer, and moved straight to the table. For
products that cache queries (e.g. Oracle), this can amount to a significant
time savings. Not all database engines support placeholders, so your
mileage may vary. I believe some DBD packages fake it by internally doing a
plain old string interpolation, so you should be able to safely use
placeholders in any DBI application -- worst case it would be no better than
not using placeholders, so it's a good bet.
Limitations: You can't use placeholders for table or column names.
Cheers.
Mark Nutter
Manager, Internet Applications Development
Marconi
[EMAIL PROTECTED]
It's not necessarily an advantage to have better brakes than the guy behind
you.
RE: DBI and ORACLE
> My results are in a comma delimited file. I need to read
> through the file
> adding each line as a record in the table using the SQL:
>
> my $query1=<<"QUERY";
> INSERT INTO DIFFAMNTS VALUES
> ('PK',9197,171509,'THIS IS A TEST2
> ROW','AA',1,77120,101032)
> QUERY
>
> Obviously thats a test line and the values I have in the
> insert would need
> to be replaced with variables from the csv file. I'll
> probably create those
> with a simple split.
If it's the simple case (CSV file was computer-generated and contains no
extraneous quotes or commas so you can depend on all the data being in the
right place and format), you will find placeholders are your friend:
#... standard DBI connect stuff to get $dbh database handle
$sth = $dbh->prepare(<)
{
chomp;
$sth->execute(split /,/);
#cool eh? :)
}
# etc...
Commentary:
The "while()" bit reads your csv file in one line at a time, and
assigns the line to the $_ variable.
The "chomp" strips off the trailing newline.
The "split /,/" takes the $_ variable and splits it into an array of data
values, dividing up at the commas.
Passing a list of arguments to "$sth->execute()" causes DBI to substitute
one data value for each "?" placeholder in the original prepared query, so
your data is pumped in more or less intact.
Caveats:
What about data values with quotes around them? Placeholders don't use
quotes, so if your data value is enclosed in quotes, the quotes will be
inserted in the database as part of the data. Oops! Probably not what you
want. Let's try this:
while()
{
chomp;
my @data = split /,/;
my @good_data = grep s/^'?(.*?)'?$/$1/, @data;
$sth->execute(@good_data);
}
The grep runs through each item in @data and strips off any leading and
trailing single quotes. Notice the bit in the middle that goes (.*?). By
default, the (.*) operator is "greedy" and tries to match the biggest string
it can, so /^'?(.*)'?$/ will essentially ignore the optional '? match at the
end and will include the trailing quote as part of the match for (.*). By
using (.*?), the "non-greedy" match, we match only what's inside the quotes
(if there are quotes).
Of course, a real HCPH (Hard Core Perl Hacker) would probably do something
more like
while()
{
chomp;
$sth->execute(
grep s/^'?(.*?)'?$/$1/,
split /,/);
}
...which is the same thing without the temporary variables. :)
Mark Nutter
Manager, Internet Applications Development
Marconi
[EMAIL PROTECTED]
It's not necessarily an advantage to have better brakes than the guy behind
you.
