Re: Some DBI question -
Hi Peter, Yes, that is correct ... am referring to these commands ... If I already have the .sql file that has all the formatting in them, is it okay to just run @x.sql from the Perl DBI script ... BTW, you mentioned some template toolkit, never heard of those ... can you elaborate on them or give a link reference that I can check On Tue, Jun 1, 2010 at 10:28 PM, Peter J. Holzer h...@wsr.ac.at wrote: On 2010-05-30 17:33:39 +1200, newbie01 perl wrote: 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? By column formatting stuff you mean commands like column foo format a20 ? These are sqlplus commands, not sql commands, i.e., they are features of the query tool, not the database. Consequently, they are not available in perl. To format data in Perl, use Perl features, like printf, formats, various templating toolkits, etc. hp -- _ | 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 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iD8DBQFMBODFMdFfQa64PCwRAjWnAJ9OWXWMfa786KB1XIEQeFQZKZe9AwCfSMOU RPGR6X8KB4QAKqUBZdx+FeI= =UW1N -END PGP SIGNATURE-
Re: Some DBI question -
On 06/02/2010 04:17 PM, newbie01 perl wrote: Hi Peter, Yes, that is correct ... am referring to these commands ... If I already have the .sql file that has all the formatting in them, is it okay to just run @x.sql from the Perl DBI script ... BTW, you mentioned some template toolkit, never heard of those ... can you elaborate on them or give a link reference that I can check On Tue, Jun 1, 2010 at 10:28 PM, Peter J. Holzer h...@wsr.ac.at wrote: On 2010-05-30 17:33:39 +1200, newbie01 perl wrote: 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? By column formatting stuff you mean commands like column foo format a20 ? These are sqlplus commands, not sql commands, i.e., they are features of the query tool, not the database. Consequently, they are not available in perl. To format data in Perl, use Perl features, like printf, formats, various templating toolkits, etc. hp -- _ | 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 -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iD8DBQFMBODFMdFfQa64PCwRAjWnAJ9OWXWMfa786KB1XIEQeFQZKZe9AwCfSMOU RPGR6X8KB4QAKqUBZdx+FeI= =UW1N -END PGP SIGNATURE- Hi, newbie01 perl, First, may I suggest you should be bottom posting, that is, put your words after the rest, as I'm doing. It makes it easier for others to scan through a post and figure out what it's all about ;-) As Peter said, those formatting commands are a feature of the query tool. That means they work in sqlplus but not necessarily anywhere else. And the @filename function is also, I believe, specific to the query tool. Though it may be common as the symbol of choice in several different tools, it is a command for the tool, not the DBI or DBD. In fact, if you tried to use @x.sql from a DBI script, you'd get some sort of error, since you've defined an array, @x with a concatenation operator, dot, and a bareword: sql. You will have to remove the formatting commands from your file, use the real SQL to get data, and then format for output using one of the tools Peter mentioned. As for templating toolkits, you may want to go to CPAN, search for that name (or just template) and study the results. -- Bob McGowan
Re: Some DBI question -
On 2010-05-30 17:33:39 +1200, newbie01 perl wrote: 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? By column formatting stuff you mean commands like column foo format a20 ? These are sqlplus commands, not sql commands, i.e., they are features of the query tool, not the database. Consequently, they are not available in perl. To format data in Perl, use Perl features, like printf, formats, various templating toolkits, etc. hp -- _ | 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: Some DBI question -
On Sun, May 30, 2010 at 01:33, newbie01 perl newbie01.p...@gmail.com 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 (DATA) {
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: Some DBI question -
On Sun, May 30, 2010 at 1:33 AM, newbie01 perl newbie01.p...@gmail.comwrote: 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? Not sure about what you mean by column formatting?? are you asking about the DDL commands like CREATE TABLE etc. The DDL commands can be done in perl but normally this would be done in a one off script. check out this link http://www.pythian.com/news/wp-content/uploads/introduction-dbd-oracle.html Also, is there anyway to sort of hide the password somehow when using Perl DBI? Yes you can use Oracle Wallet or server authentication cheers John Scoles Any advise or feedback will be very much appreciated. Thanks in advance. -- Catch Alex Sheeri at ODTUG/Kaleidoscope - June 27 - July 1. Hear Sheeri speak or email eve...@pythian.com to meet with Pythian.
Some DBI question -
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.
