Re: Some DBI question -
On Sun, May 30, 2010 at 01:33, newbie01 perl wrote:
> Hi,
>
> Am trying out DBI for Oracle and just want to know if for example, I need to
> include column formatting stuff etc., is it best to just put them into a
> .sql file and then execute the .sql file instead of placing them in the Perl
> DBI script?
> Also, is there anyway to sort of "hide" the password somehow when using Perl
> DBI?
>
> Any advise or feedback will be very much appreciated.
>
> Thanks in advance.
>
In general, I like to write code using the DBI like this (you will
need DBD::SQLite to run this example):
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my $db = "whatever";
my $user = "username";
my $pass = do {
#this file should by read-only for the user only
open my $fh, "<", "$ENV{HOME}/.dbipass"
or die "could not open password file\n";
local $/;
<$fh>
};
my $dbh = DBI->connect(
"dbi:SQLite:dbname=$db",
$user,
$pass,
{
AutoCommit => 1,
ChopBlanks => 1,
RaiseError => 1,
PrintError => 0,
FetchHashKeyName => "NAME_lc",
}
) or die "could not connect to $db: ", DBI->errstr;
# create the database
$dbh->do("
CREATE TABLE tab (
tab_id INTEGER,
tab_name VARCHAR(35)
)
");
$dbh->do("
CREATE TABLE col (
tab_id INTEGER,
col_position INTEGER,
col_name VARCHAR(35)
)
");
$dbh->do("
CREATE TABLE idx (
tab_id INTEGER,
idx_name VARCHAR(35)
)
");
# load the database
my %inserts = (
tab => $dbh->prepare("insert into tab values (?, ?)"),
col => $dbh->prepare("insert into col values (?, ?, ?)"),
idx => $dbh->prepare("insert into idx values (?, ?)"),
);
while () {
chomp;
my ($tab, @row) = split /,/;
$inserts{$tab}->execute(@row);
}
# query the database
my $sth = $dbh->prepare("
SELECT col.col_position, col.col_name
FROM tab, col
WHERE tab.tab_id = col.tab_id
AND tab.tab_name = ?
ORDER BY col.col_position
");
for my $tab_name (qw/tab col idx/) {
$sth->execute($tab_name);
print "table $tab_name:\n";
while (my $row = $sth->fetchrow_hashref) {
print "\t$row->{col_name}\n";
}
print "\n";
}
# drop the tables so this script can run a second time
$dbh->do("drop table tab");
$dbh->do("drop table col");
$dbh->do("drop table idx");
# data to be inserted into the db
__DATA__
tab,1,tab
tab,2,col
tab,3,idx
col,1,1,tab_id
col,1,2,tab_name
col,2,1,tab_id
col,2,2,col_positon
col,2,3,col_name
col,3,1,tab_id
col,3,2,idx_name
--
Chas. Owens
wonkden.net
The most important skill a programmer can have is the ability to read.
Re: How to set LD_LIBRARY_PATH
On Sun, May 30, 2010 at 10:47 AM, Marilyn Sander < marilyn-san...@earthlink.net> wrote: > > [...] My reasoning was that the thing being > loaded is a shared object (.so file). The system loader (ld) has to be > invoked for loading > a shared object. That seems to me to require a separate process, with an > environment > stack inherited from the Perl process that invokes it. There's a problem here. What you describe is not what happens. The system loader, ld, is used to create executables and shared objects. It indeed is a separate program that is most often invoked automatically by a compiler - GCC for example. There is a wholly separate module, often with a name such as ld.so.1, which is the dynamic library loader. It is actually a part of the program you are running - Perl in the current context. It is responsible for loading other shared libraries into the current process. Dynamically loading a shared library adds the code to the current process; it does not invoke a separate program/process. -- Jonathan Leffler #include Guardian of DBD::Informix - v2008.0513 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused."
Re: How to set LD_LIBRARY_PATH
On 2010-05-30 10:47:50 -0700, Marilyn Sander wrote: > > On May 29, 2010, at 6:24 AM, Paul Johnson wrote: > >> On Fri, May 28, 2010 at 11:23:32AM -0700, Marilyn Sander wrote: >> >>> On May 28, 2010, at 9:33 AM, Paul Johnson wrote: >>> On Fri, May 28, 2010 at 06:14:38AM -0400, John Scoles wrote: > You will have to set those values before your modules load. > > So you should stick them in the BEGIN and that should work ... except where it doesn't, such as on Solaris for example. Here, LD_LIBRARY_PATH (at least) really does need to be set before the process starts. You can do this by writing a shell wrapper, or re-execing your perl script if the value is not already set. >> >>> Have you considered doing a require instead of a use. With require, >>> the loading is done at run time, and would be governed by the >>> setting of LD_LIBRARY_PATH, at the time the require statement is >>> executed. Just set LD_LIBRARY_PATH before doing the require. >> >> I'm afraid that you may have misunderstood what I wrote. There are >> times when you really do need to set the variable before the process >> starts. >> > I did not misunderstand what you wrote. My reasoning was that the > thing being loaded is a shared object (.so file). The system loader > (ld) has to be invoked for loading a shared object. The .so file must be loaded into the (already running) perl process. While that may be possible from an external process, it is simpler to do it from the same process - so on the systems I know (Linux, Solaris) the shared library loader is just a library function, not an external tool. On Linux (at least i386[1]) the loader uses the current value of LD_LIBRARY_PATH when it is asked to load an .so file. So you can change LD_LIBRARY_PATH just before you want to load the library. On Solaris the loader uses the value of LD_LIBRARY_PATH at program startup. Changing LD_LIBRARY_PATH during runtime has no effect on the process itself, just its children. I don't know why this is the case, but I guess that LD_LIBRARY_PATH is split into components when the first library (probably libc) and after that the components aren't used any more. hp [1] I seem to remember that I ran into problems either on x86_64 or on power, but I don't remember the details. If it's x86_64, I should be able to test it (don't have access to power-based systems any more). -- _ | Peter J. Holzer| Auf jedem Computer sollte der Satz Ludwigs II |_|_) | Sysadmin WSR | eingeprägt stehen: "Ein ewig Rätsel will ich | | | h...@wsr.ac.at | bleiben, mir und andern." __/ | http://www.hjp.at/ |-- Wolfram Heinrich in desd signature.asc Description: Digital signature
Re: How to set LD_LIBRARY_PATH
On May 29, 2010, at 6:24 AM, Paul Johnson wrote: On Fri, May 28, 2010 at 11:23:32AM -0700, Marilyn Sander wrote: On May 28, 2010, at 9:33 AM, Paul Johnson wrote: On Fri, May 28, 2010 at 06:14:38AM -0400, John Scoles wrote: You will have to set those values before your modules load. So you should stick them in the BEGIN and that should work ... except where it doesn't, such as on Solaris for example. Here, LD_LIBRARY_PATH (at least) really does need to be set before the process starts. You can do this by writing a shell wrapper, or re-execing your perl script if the value is not already set. Have you considered doing a require instead of a use. With require, the loading is done at run time, and would be governed by the setting of LD_LIBRARY_PATH, at the time the require statement is executed. Just set LD_LIBRARY_PATH before doing the require. I'm afraid that you may have misunderstood what I wrote. There are times when you really do need to set the variable before the process starts. I did not misunderstand what you wrote. My reasoning was that the thing being loaded is a shared object (.so file). The system loader (ld) has to be invoked for loading a shared object. That seems to me to require a separate process, with an environment stack inherited from the Perl process that invokes it. I was also assuming that setting an environment variable at run time would set the environment for the Perl process that is executing the Perl program. However, I did not test it. I will test it and see what happens. --Marilyn
Re: How to set LD_LIBRARY_PATH
On Sat, May 29, 2010 at 12:19, Bobak, Mark wrote: > I'd argue you want to use a shell wrapper anyhow, because rather than setting > those variables explicitly, you ought to do something like: > export ORACLE_SID=your_sid > export ORAENV_ASK=NO > . oraenv > > and Oracle will set it all for you, and if/when something changes or is > upgraded, your code will continue to do the right thing. snip Yeah, using a wrapper shell script to setup the environment is always a good idea. I have seen too many Perl scripts (and C programs) fail miserably when run by cron because they assumed some environment would be present. This would also be a good place to set PERL5LIB and any other variables you need to exist. -- Chas. Owens wonkden.net The most important skill a programmer can have is the ability to read.
