perl dbi
Hello, I using the perl dbi and mysql and I was wondering if their was a load command with this. The following statement works with php LOAD DATA LOCAL INFILE '$file_location' INTO TABLE $table FIELDS TERMINATED BY ',' However I can not get the same command to work in perl. Any answers would be appreciated. Chris Burger -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Perl DBI error
Hi everybody,
I am having a problem with binding a variable when executing on a DBI
object. The code goes:
#!/usr/bin/perl
use warnings;
use strict;
use DBI;
#DBI variables
my $database = 'databaseName';
my $hostname = 'localhost';
my $dsn = "DBI:mysql:database=$database;host=$hostname";
my $dbh = DBI->connect($dsn, 'user', 'notMyPassword', {RaiseError=>1}) ||
die "Database connection not made : $DBI::errstr";
my $select = $dbh->prepare_cached("select * from ?");
die "Couldn't prepare queries\n" unless defined $select;
$select->execute('tableName');
while (my $row = $select->fetchrow_arrayref()){
print "@$row\n";
}
$dbh->disconnect();
However I get the error:
DBD::mysql::st execute failed: You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right
syntax to use near ''tableName'' at line 1 at ./haha.pl line 16.
If I change the bind variable to tableName in the SQL and just do
$select->execute(), then it works.
Any ideas?
Many thanks,
Dave
--
Using Opera's revolutionary e-mail client: http://www.opera.com/mail/
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
perl dbi question
Hello Perl community,
here is a question I encountered dbi and till now I use brute force to
solve it, but I figured I may see better way to do it here.
in orable sqlplus If I do
select * from table, it will print out the results nicely, all
aligned.
in dbi what I do now
my $body = sprintf("market_symbol last info_volume best_bid
best_bid_size best_ask best_ask_size info_open info_low info_high
\n");
while ($hash_ref = $sth->fetchrow_hashref) {
$body = $body.sprintf("%13s", $hash_ref->{'MARKET_SYMBOL'});
$body = $body.sprintf("%7s", $hash_ref->{'LAST'});
$body = $body.sprintf("%12s", $hash_ref->{'INFO_VOLUME'});
$body = $body.sprintf("%9s", $hash_ref->{'BEST_BID'});
$body = $body.sprintf("%13s", $hash_ref->{'BEST_BID_SIZE'});
$body = $body.sprintf("%10s", $hash_ref->{'BEST_ASK'});
$body = $body.sprintf("%14s", $hash_ref->{'BEST_ASK_SIZE'});
$body = $body.sprintf("%10s", $hash_ref->{'INFO_OPEN'});
$body = $body.sprintf("%9s", $hash_ref->{'INFO_LOW'});
$body = $body.sprintf("%10s", $hash_ref->{'INFO_HIGH'});
$body = $body.sprintf("\n");
}
I have to figure out the column width by counting them one by one, is
there some way to use dbi and print out results nicely without doing
so?
Thank you and have a nice Thx givin!
Jim
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
perl DBI n00b
I'm pretty new to the perl DBI world, so can someone point me to a good tutorial/reference site or book or something? I'm very proficient with databases and pretty good with basic perl, but not used together. Any help would be appreciated. -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Perl DBI question
I'm trying to print out statistics on a web page, based on an evaluation
form, where the person filling it out answers some questions with values
1-4. These are then stored in a MySQL database. I need to print out
averages for each question, and throw out any zeros (which means they
didn't select an answer for that question). I know I can type out
multiple SELECT statements, but I'm sure it'd be easier to do it with
perl. I'm sure that using an array is the way to go, but after that I'm
lost. Here's what I have so far-
# Sub for averaging all questions for all classes
sub avg_all {
my $dbh = shift;
my $hashref = shift;
my %in = %{hashref};
my $output;
my $sth_avg_all = $dbh->prepare("select round(avg($$answer[0]), 2) from
evaluation where $$answer[0] != 0}"); #this is the statement I need to
repeat with different fields
# output
$output .= "Internet Class
Evaluationsexecute;
--
Kolene
perl dbi insertid
Heylo.
I am using this command to get the latest value for the auto_increment field in the
mysql database:
$field_insertid = $sth->{'insertid'};
but, it is always returning a value of zero, eventhough the latest value is not zero.
Any ideas?
Thanks as always,
--Donnie
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Sybase & Perl DBI
Hi,
I have written a script the handles calls to a Sybase Database. The only problem I
have is handling the "Return Code" from Stored Procedures.
I have gone through the PERL DBI book but the only reference I can find in the
Appendix(Page 326), it is to do with syb_result_type.
I have used the syb_more_results attribute to handle all the statements the Stored
Procedure puts out and this is great. I am having a problem trying to interrogate
syb_result_type. Should I reference this before syb_more_results?
The code I put in is just like in the book;
do{
while ($data = $sth->fetch() {
print "Message $data\n";
}
} while ($sth->{syb_more_results});
I need to find out if a return code has been passed back. It currently just prints it
out and I need to exit the script with it.
Does anyone have an example of handling the return code?
Thanks
Gus
AW: Perl DBI error
Dave Tang asked: > I am having a problem with binding a variable when executing on a DBI > object. [...] "You can only reliably bind values, not field or table names. Database that plan query execution won't accept this, because they need table and field names to make their query execution plans. Without knowing table and field names, how can the database construct a plan that calls for using indexes that might be available? (Answer: It can't.)" -- http://www.perlmonks.org/index.pl?node_id=197667 HTH, Thomas PS: 1st hit while googling "dbi table name as bind variable" ;-) -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Re: perl dbi question
At 12:31 PM -0800 11/24/10, Jim Green wrote: Hello Perl community, here is a question I encountered dbi and till now I use brute force to solve it, but I figured I may see better way to do it here. in orable sqlplus If I do select * from table, it will print out the results nicely, all aligned. in dbi what I do now [lots of sprintf's snipped] I have to figure out the column width by counting them one by one, is there some way to use dbi and print out results nicely without doing so? Check out the Text::Table module, available from CPAN. -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Re: perl DBI n00b
Le 11 janv. 2012 15:59, "Mark Haney" a écrit : > > I'm pretty new to the perl DBI world, so can someone point me to a good tutorial/reference site or book or something? I'm very proficient with databases and pretty good with basic perl, but not used together. Any help would be appreciate. You can look ok http://dbi.perl.org
Re: perl DBI n00b
The doucument for DBI on CPAN is excellent. Or you may look at this thread: http://www.perlmonks.org/?node_id=22050 -Ursprüngliche Nachricht- Von: "Mark Haney" Gesendet: 2012-1-11 15:58:30 An: "beginners@perl.org" Betreff: perl DBI n00b >I'm pretty new to the perl DBI world, so can someone point me to a good >tutorial/reference site or book or something? I'm very proficient with >databases and pretty good with basic perl, but not used together. Any >help would be appreciated. > > >-- >To unsubscribe, e-mail: beginners-unsubscr...@perl.org >For additional commands, e-mail: beginners-h...@perl.org >http://learn.perl.org/ > > ___ SMS schreiben mit WEB.DE FreeMail - einfach, schnell und kostenguenstig. Jetzt gleich testen! http://f.web.de/?mc=021192 -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Re: perl DBI n00b
On 01/11/2012 09:58 AM, Mark Haney wrote: I'm pretty new to the perl DBI world, so can someone point me to a good tutorial/reference site or book or something? I'm very proficient with databases and pretty good with basic perl, but not used together. Any help would be appreciated. I really do appreciate the input. I have used the perl site source a lot, I was just wondering what else may be out there. It's good to know I'm not missing something that might be useful. -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Working with Perl DBI
Hello guys,
Please help me with debugging of following script:
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my $dbh = DBI->connect('DBI:mysql:exim_mx1', 'root', 'PASSWORD') || die
"Could not connect to database: $DBI::errstr";
my $sth = $dbh->prepare('SELECT * FROM blackuser WHERE id=160') or die
"Can't prepare SQL statement: $DBI::errstr\n";
$sth->execute() or die "Can't execute SQL statement: $DBI::errstr\n";
my $result = $sth->fetchrow_hashref();
#print "Value returned: $result->{val}\n";
print $result;
#$dbh->commit or die $DBI::errstr;
$dbh->disconnect();
#$dbh->disconnect() or warn "Error disconnecting: $DBI::errstr\n";
exit;
Purpose of this script is connecting to localhost's mysqld server, USE
exim_mx1 database and make simple select.. but i don't know why it's not
working.. I have following error:
perl check2.pl
DBI::db=HASH(0x1e40ae0)->disconnect invalidates 1 active statement
handle (either destroy statement handles or call finish on them before
disconnecting) at check2.pl line 17.
HASH(0x1e40258)
Please, help
I use Debian 6.0.5 and perl 5.10.1
Best regards,
Dmitry Korzhevin
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
Perl DBI vs SQLPLUS
> I don't know if this is the way to trespond to your question and I
> apologize
> in advance if this is not appropriate.
>
> I've got a question regarding SQL*PLUS and DBI
> The following query in Oracle, using sql*plus, takes 2.3 seconds.
>
> Using perl DBI, it takes over 4 minutes.
>
> Any ideas what to do?
>
> $oracle_query = 'select c.object_name,c.column_name, c.column_desc
> from abbr_ref b, meta_column_master c, meta_node_ref o
> where (b.word like \'%'.$wanted_value.'%\'
> or b.abbr like \''.$wanted_value.'%\'
> or c.column_name like \''.$wanted_value.'\'
> or upper(c.bus_name) like \'%'.$wanted_value.'%\'
> or upper(c.column_desc) like \'%'.$wanted_value.'%\')
> and b.abbr = o.node
> and o.column_name like c.column_name
> group by c.object_name,c.column_name,c.column_desc
> order by 1,2,3';
>
>
> I am using the following to fetch the rows:
> while(($object_name,$column_name,$column_desc) = $sth->fetchrow_array) {
> etc ...
>
> Thanks,
> Hal Beck
>
> *
> [EMAIL PROTECTED] wrote:
>
> >>-Original Message-
> >>From: Joseph Bajin [mailto:[EMAIL PROTECTED]]
> >>Sent: Monday, June 03, 2002 2:34 PM
> >>To: [EMAIL PROTECTED]
> >>Subject: Perl DBI vs SQLPLUS
> >>
> >>
> >>Got a question for you guys out there.
> >>
> >>I currently have a list of records that I need to search for in a
> >>mult-db that we have. I was wondering if perl would be faster
> >>to process
> >>or stick with my .ksh script that uses sqlplus. Here's how
> >>the current
> >>setup is
> >>
> >>cat input file and read for record #
> >>
> >>sqlplus into db1 and do a select statement to find what db that the
> >>account is located in. (This db just contains basic info on where the
> >>account is actually stored)
> >>
> >>exit sql plus
> >>
> >>open another sqlplus session to db that was found in previous select
> >>statement.
> >>
> >>select out data needed and write to a file.
> >>
> >>exit sqlplus
> >>
> >>Do the loop again.
> >>
> >>
> >>I needed to process about 8000 records today. It would get about 30%
> >>through and the process would just stop. Don't know if it's a buffer
> >>issue (that was a suggestion) or if perl because it will directly
> >>connect to the DB would be better.
> >>
> >>Got any ideas.
> >>
> >
> >Can't say why it's hanging, but using DBI should be faster than
> >what you're doing, because you can keep a database connection
> >open (at least on db1). How many total databases are there?
> >
> Total of 8 databases. The first database is used only to reference where
> the rest of the data is located. ie (record is located on Db4, and so on.
> )
>
> Thanks Joe
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
javascript and perl dbi
Hi, I have a web page where I've used Java Script to set up some relational menus, within a php script to send form data to a perl DBI script for querying a database. The user selects an option from the first menu, and an option from the second menu, and these values need to be passed either to the php section of the script, or directly to the DBI script. Is this nuts, or is there a way to pass the JS values to php or DBI? Inorvermyhead, Robbie -- -*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* Robbie Staufer NCAR/SCD 1850 Table Mesa Dr. Rm. 42 Boulder, CO. 80305 (303) 497-1836 -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
perl DBI and mysql
Hi all,
i am new to perl and i want to use it with DBI:mysql module. I want to
connect to my sql server and do some data manipulation like , insert,
update and delete. I read the DBI documention but i cant get through to get
my script working. any help is needed. below is my script.
#!/usr/bin/perl
use DBI;
$database="sampledb";
$host="localhost";
$user="db";
$pw="mysql";
$dbh=DBI->connect("DBI:mysql:database=$database;host=$host",$user,$pw,
{RaiseError => 1});
my $sth = $dbh->prepare(q{INSERT INTO USERS
(USERNAME,PASSWORD,DESCRIPTION,ATTRIBUTES) VALUES (?, ?, ?, ?)
}) or die $dbh->errstr;
while (<>) {
chomp;
my ($USERNAME,$PASSWORD,$DESCRIPTION,$ATTRIBUTE) = split /,/;
$sth->execute($USERNAME,$PASSWORD,$DESCRIPTION,$ATTRIBUTE) or
die $dbh->errstr;
}
$dbh->commit or die $dbh->errstr;
$dbh->disconnect;
--
Thanks.
Jaws
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
FW: Perl DBI question
Sorry, I forgot to reply to all when I replied:
If I understand what you are needing to do, rather than running the select
statement multiple times, it would be much more efficient to use a group by
SQL statement? I can't tell for sure about that part from what you have
written. However, if each question is identifiable in the table, you could
get the whole thing in one pass through the table. I am guessing something
like this for a table structure (since 0 means no answer, I am guessing the
rest of the possible answers are weighted numerically:
CREATE TABLE Evaluation (
QuestNo int,
Value int)
with that simple table (yours is probably more complex), you can get the
averages of all question in one pass through the table with a query like:
SELECT
QuestNo,
round(avg(Value, 2)
FROM Evaluation
WHERE Value != 0
GROUP BY QuestNo
That will return your two columns, one being the number of the question, and
the other being the
However, to make use of what is returned efficiently, you need to re-order
what else you are doing. You are using prepare right, but go ahead and
execute your statement handle, then bind the rows into variables. using the
above query as an example, I'll show you (untested, but I use the method
quite often). I am also assuming that you have made the connection to your
db server by this point in your script.
# use the qq{} quotation notation
# to make it easy to not interfere with quotes in your
# SQL query.
my $sql_statement = qq{SELECT
QuestNo,
round(avg(Value, 2)
FROM Evaluation
WHERE Value != 0
GROUP BY QuestNo};
my ($row, $questno, $value);
my $sth_avg_all = $dbh->prepare($sql_statement) || die "Can't prepare
\n$sql_statement\n$dbh::errstr\n";
$sth_avg_all->execute() || die "Can't
execute\n$sql_statement\n$dbh::errstr\n";
$sth_avg_all->bind_columns(undef, \$questno, \$value);
while ($row = $sth_avg_all->fetchrow_arrayref) {
#do what you need with each returned row here
}
There are several ways to get the variables into your script, but that is
one of the easiest. perldoc dbi gives some other nice examples, and just
monitoring the dbi users group will give you some other slick ideas. That is
just one of the easier ways to do it.
Hope this helps
Steve Howard
-Original Message-
From: Kolene Isbell [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, June 20, 2001 4:36 PM
To: [EMAIL PROTECTED]
Subject: Perl DBI question
I'm trying to print out statistics on a web page, based on an evaluation
form, where the person filling it out answers some questions with values
1-4. These are then stored in a MySQL database. I need to print out
averages for each question, and throw out any zeros (which means they
didn't select an answer for that question). I know I can type out
multiple SELECT statements, but I'm sure it'd be easier to do it with
perl. I'm sure that using an array is the way to go, but after that I'm
lost. Here's what I have so far-
# Sub for averaging all questions for all classes
sub avg_all {
my $dbh = shift;
my $hashref = shift;
my %in = %{hashref};
my $output;
my $sth_avg_all = $dbh->prepare("select round(avg($$answer[0]), 2) from
evaluation where $$answer[0] != 0}"); #this is the statement I need to
repeat with different fields
# output
$output .= "Internet Class
Evaluationsexecute;
--
Kolene
Re: Perl DBI question
I should have stated more clearly -
This is a Perl Module I'm writing. The table already exists and I'm just trying
to pull the data out and run certain stats on them, one of which is the
averages. If I write it like "Select avg($$answer[0]), avg($$answer[1],
avg($$answer[2]) from
evaluation where $$answer[0] != 0 || $$answer[1] !=0 || $$answer[2] != 0" I'll
get false answers, because of the not equal to zero part. If I use and's
instead of or's, it's likely I'll get no data returned. So, I simply need to
have multiple select statements, which in this case about 20. So it'd be much
smother to write it once and put it a foreach loop or something.
I can see where you're going with what you've written below. I'll give it a
shot.
And yes, I've already connected to the db. This is just the code for the sub.
Kolene
Steve Howard wrote:
> If I understand what you are needing to do, rather than running the select
> statement multiple times, it would be much more efficient to use a group by
> SQL statement? I can't tell for sure about that part from what you have
> written. However, if each question is identifiable in the table, you could
> get the whole thing in one pass through the table. I am guessing something
> like this for a table structure (since 0 means no answer, I am guessing the
> rest of the possible answers are weighted numerically:
>
> CREATE TABLE Evaluation (
> QuestNo int,
> Value int)
>
> with that simple table (yours is probably more complex), you can get the
> averages of all question in one pass through the table with a query like:
>
> SELECT
> QuestNo,
> round(avg(Value, 2)
> FROM Evaluation
> WHERE Value != 0
> GROUP BY QuestNo
>
> That will return your two columns, one being the number of the question, and
> the other being the
>
> However, to make use of what is returned efficiently, you need to re-order
> what else you are doing. You are using prepare right, but go ahead and
> execute your statement handle, then bind the rows into variables. using the
> above query as an example, I'll show you (untested, but I use the method
> quite often). I am also assuming that you have made the connection to your
> db server by this point in your script.
>
> # use the qq{} quotation notation
> # to make it easy to not interfere with quotes in your
> # SQL query.
>
> my $sql_statement = qq{SELECT
> QuestNo,
> round(avg(Value, 2)
> FROM Evaluation
> WHERE Value != 0
> GROUP BY QuestNo};
>
> my ($row, $questno, $value);
>
> my $sth_avg_all = $dbh->prepare($sql_statement) || die "Can't prepare
> \n$sql_statement\n$dbh::errstr\n";
>
> $sth_avg_all->execute() || die "Can't
> execute\n$sql_statement\n$dbh::errstr\n";
>
> $sth_avg_all->bind_columns(undef, \$questno, \$value);
>
> while ($row = $sth_avg_all->fetchrow_arrayref) {
> #do what you need with each returned row here
> }
>
> There are several ways to get the variables into your script, but that is
> one of the easiest. perldoc dbi gives some other nice examples, and just
> monitoring the dbi users group will give you some other slick ideas. That is
> just one of the easier ways to do it.
>
> Hope this helps
>
> Steve Howard
>
> -Original Message-
> From: Kolene Isbell [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, June 20, 2001 4:36 PM
> To: [EMAIL PROTECTED]
> Subject: Perl DBI question
>
> I'm trying to print out statistics on a web page, based on an evaluation
> form, where the person filling it out answers some questions with values
> 1-4. These are then stored in a MySQL database. I need to print out
> averages for each question, and throw out any zeros (which means they
> didn't select an answer for that question). I know I can type out
> multiple SELECT statements, but I'm sure it'd be easier to do it with
> perl. I'm sure that using an array is the way to go, but after that I'm
> lost. Here's what I have so far-
>
> # Sub for averaging all questions for all classes
> sub avg_all {
>
> my $dbh = shift;
> my $hashref = shift;
> my %in = %{hashref};
> my $output;
>
> my $sth_avg_all = $dbh->prepare("select round(avg($$answer[0]), 2) from
> evaluation where $$answer[0] != 0}"); #this is the statement I need to
> repeat with different fields
>
> # output
>
> $output .= "Internet Class
> Evaluations
> $output .= "Did this class meet your expectations? $$answer[0]";
>
> }
> $sth_avg_all->execute;
>
> --
> Kolene
What's Perl DBI module?
what's Perl DBI module? How can I execute a web-database in Perl language? thanks in advance
compiling perl - DBI programs
I am trying to build a web application in perl, and am using the Mysql as the database . I have written a sample code f.pl that includes the DBI module when I try compiling with perlcc I get the following . bash# perlcc f.pl Compiling f.pl: Making C(f.pl.c) for f.pl! /usr/bin/perl -I/usr/libdata/perl/5.00503/mach -I/usr/libdata/perl/5.00503 -I/usr/local/lib/perl5/site_perl/5.005/i386-freebsd -I/usr/local/lib/perl5/site_perl/5.005 -I. -MO=CC,-of.pl.c f.pl f.pl syntax OK Compiling C(f) for f.pl! /usr/bin/perl -I/usr/libdata/perl/5.00503/mach -I/usr/libdata/perl/5.00503 -I/usr/local/lib/perl5/site_perl/5.005/i386-freebsd -I/usr/local/lib/perl5/site_perl/5.005 -I. /tmp/f.pl.tst cc -I/usr/libdata/perl/5.00503/mach/CORE /usr/libdata/perl/5.00503/mach/auto/Fcntl/Fcntl.so /usr/local/lib/perl5/site_perl/5.005/i386-freebsd/auto/DBI/DBI.so -o f f.pl.c -Wl,-E -lperl -lm -L/usr/libdata/perl/5.00503/mach/CORE -lperl -lm -lc -lcrypt bash# ./f Can't locate object method "unimport" via package "strict" at /Mysql.pm line 247. BEGIN failed--compilation aborted at /Mysql.pm line 247. bash# -- Please help me get out of these errors. Or can anyone suggest an alternative method? ---jacob -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
perl DBI disconnect question
Regarding perl DBI - I know it's proper to use $sth->finish and $dbh->disconnect and I am doing that at the end of my script... however, i am also doing alot of error checking at the beginning of the script (to make sure data has been entered, and formatted properly). If there's an error I would usually just send back an error message and exit the script to prevent it from having to run through the rest of it needlessly. do I need to explicitly disconnect before exiting in those instances or it it OK to just exit? thanks, rory -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Perl DBI vs SQLPLUS
Got a question for you guys out there. I currently have a list of records that I need to search for in a mult-db that we have. I was wondering if perl would be faster to process or stick with my .ksh script that uses sqlplus. Here's how the current setup is cat input file and read for record # sqlplus into db1 and do a select statement to find what db that the account is located in. (This db just contains basic info on where the account is actually stored) exit sql plus open another sqlplus session to db that was found in previous select statement. select out data needed and write to a file. exit sqlplus Do the loop again. I needed to process about 8000 records today. It would get about 30% through and the process would just stop. Don't know if it's a buffer issue (that was a suggestion) or if perl because it will directly connect to the DB would be better. Got any ideas. -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Perl DBI and Oracle
Ho do you
set linesize 1000;
using dbi for Oracle. I have tried inserting it before the "select"
statement and putting it in its own "prepare" statement but neither have
worked. Google has got very little on this when I searched for it. I would
appreciate a decent link to how to do this sort of thing.
Cherrs
Harry.
*
COLT Telecommunications
Registered in England No. 2452736
Registered Office: Bishopsgate Court, 4 Norton Folgate, London E1 6DQ
Tel. +44 20 7390 3900
This message is subject to and does not create or vary any contractual
relationship between COLT Telecommunications, its subsidiaries or
affiliates ("COLT") and you. Internet communications are not secure
and therefore COLT does not accept legal responsibility for the
contents of this message. Any view or opinions expressed are those of
the author. The message is intended for the addressee only and its
contents and any attached files are strictly confidential. If you have
received it in error, please telephone the number above. Thank you.
*
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: perl dbi insertid
on Mon, 01 Jul 2002 00:56:27 GMT, [EMAIL PROTECTED] (Donnie
Jones) wrote:
> I am using this command to get the latest value for the
> auto_increment field in the mysql database:
>
> $field_insertid = $sth->{'insertid'};
>
>
> but, it is always returning a value of zero, eventhough the latest
> value is not zero.
try
$sth->{'mysql_insertid'}
or
$dhb->{'mysql_insertid'}
after you have inserted a record with 'do' or 'prepare/execute'.
--
felix
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Perl DBI - using parameters
Hi,
This is my first effort at perl scripting so forgive my innocence. What I am
trying to do is extract schema and data info from a proprietary database
into flat files, format those files and import the data and schema into an
Oracle database.
The initial part of this is capturing the table names into an array and then
using that array to query the database and dump the results to flat files.
Where I'm having trouble is in using a bound parameter in the sql statement.
If I use a table name directly it works without a problem but when I
parameterise it I get a Parse Error.
foreach $i (@table_arr){
$content = $i;
# print ("table name is: $i \n");
open( CONTENT, ">$content" ) || die "Can't open file $content";
my $sth = $dbh->prepare("select * from ?");
$sth->bind_param(1, $i);
my $row;
$sth->execute or die "Can't execute SQL statement: ", $sth->errstr(),
"\n";
$row = $sth->dump_results(80, "\n", ':',\*CONTENT);
}
Any ideas where I could be going wrong?
Cheers
Shane
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Perl DBI for Sybase
Hello all:
I am pretty new in this matter.
My code looks like this:
$sth2=$dbh->prepare("Select job.joid,
job_name,command,owner,machine,std_out_file,std_err_file,box_joid,timezone
from job, job2 where job.joid = job2.joid");
$sth2->execute;
while (my $info = $sth2->fetchrow_hasref)
{
calls a different subroutine to validate each argument like:
check_job_name($instance,$box_name,$info->{job_name},$info->{machine});
}
My question is... in my while loop am I quring the database every time when I
say $sth2-> fetchrow_hasref ? Is there any way I can call the fetchrow one
time and store it in a hash. When I did the perl -d:DProff -F ...I can see
that I called the fetch 2765 times does that mean that I access the database
2765 times.
I will be really greatful to your answers.
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: Working with Perl DBI
On Friday, June 8, 2012 at 6:49 AM, Dmitry Korzhevin wrote: What makes you think it's not working? Yes, you are getting this warning: > DBI::db=HASH(0x1e40ae0)->disconnect invalidates 1 active statement > > handle (either destroy statement handles or call finish on them before > disconnecting) at check2.pl (http://check2.pl) line 17. which you can avoid by just removing the '->disconnect()' call, or calling '->finish()', like the message tells you. But your output -- the hashref from ->fetchrow_hashref -- is right here: > HASH(0x1e40258) chrs, john. -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Re: Working with Perl DBI
On 2012-06-08 12:49, Dmitry Korzhevin wrote:
perl check2.pl
DBI::db=HASH(0x1e40ae0)->disconnect invalidates 1 active statement
handle (either destroy statement handles or call finish on them before
disconnecting) at check2.pl line 17.
You didn't fetch all the rows.
#!/usr/bin/perl -l
use strict;
use warnings;
my $dbh = DBI->connect(
"DBI:mysql:exim_mx1",
"root", "PASSWORD",
{ PrintError => 0, RaiseError => 1 },
);
my $sth = $dbh->prepare("SELECT * FROM blackuser WHERE id=160");
$sth->execute();
print "rows:", $sth->rows;
undef $sth; # mimic end-of-scope
$dbh->disconnect;
__END__
--
Ruud
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
Re: Perl DBI vs SQLPLUS
- Original Message -
From: <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Thursday, September 26, 2002 2:15 AM
Subject: Perl DBI vs SQLPLUS
>
>
> > I don't know if this is the way to trespond to your question and I
> > apologize
> > in advance if this is not appropriate.
> >
> > I've got a question regarding SQL*PLUS and DBI
> > The following query in Oracle, using sql*plus, takes 2.3 seconds.
> >
> > Using perl DBI, it takes over 4 minutes.
Can't understand why DBI whould be any slower. Are you *positive* it is the
*exact* same query?
Try writing $oracle_query out do a file and then run that using sqlplus.
Also, run an EXPLAIN PLAN on it and see what the execution plan looks like.
> >
> > Any ideas what to do?
> >
> > $oracle_query = 'select c.object_name,c.column_name, c.column_desc
> > from abbr_ref b, meta_column_master c, meta_node_ref o
> > where (b.word like \'%'.$wanted_value.'%\'
> > or b.abbr like \''.$wanted_value.'%\'
> > or c.column_name like \''.$wanted_value.'\'
> > or upper(c.bus_name) like \'%'.$wanted_value.'%\'
> > or upper(c.column_desc) like \'%'.$wanted_value.'%\')
> > and b.abbr = o.node
> > and o.column_name like c.column_name
This join condition looks suspiscious. Shouldn't that be o.column_name =
c.column_name ? Oracle probably can't optimize that join.
> > group by c.object_name,c.column_name,c.column_desc
> > order by 1,2,3';
> >
> >
> > I am using the following to fetch the rows:
> > while(($object_name,$column_name,$column_desc) = $sth->fetchrow_array) {
That should be fine.
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
RE: Perl DBI vs SQLPLUS
Hello Bob:
I ran explains ... got different execution paths and then
discovered a "=" in one where clause and, in the other
query, there was a "like" ... huge difference in Oracle performance.
Thanks. That was a good process for isolating a problem. Those
2:00 A.M. efforts under the gun still cause anxiety.
Hal
> -Original Message-
> From: Bob Showalter [SMTP:[EMAIL PROTECTED]]
> Sent: Thursday, September 26, 2002 5:54 AM
> To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Subject: Re: Perl DBI vs SQLPLUS
>
> - Original Message -
> From: <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Cc: <[EMAIL PROTECTED]>
> Sent: Thursday, September 26, 2002 2:15 AM
> Subject: Perl DBI vs SQLPLUS
>
>
> >
> >
> > > I don't know if this is the way to trespond to your question and I
> > > apologize
> > > in advance if this is not appropriate.
> > >
> > > I've got a question regarding SQL*PLUS and DBI
> > > The following query in Oracle, using sql*plus, takes 2.3 seconds.
> > >
> > > Using perl DBI, it takes over 4 minutes.
>
> Can't understand why DBI whould be any slower. Are you *positive* it is
> the
> *exact* same query?
>
> Try writing $oracle_query out do a file and then run that using sqlplus.
> Also, run an EXPLAIN PLAN on it and see what the execution plan looks
> like.
>
> > >
> > > Any ideas what to do?
> > >
> > > $oracle_query = 'select c.object_name,c.column_name, c.column_desc
> > > from abbr_ref b, meta_column_master c, meta_node_ref o
> > > where (b.word like \'%'.$wanted_value.'%\'
> > > or b.abbr like \''.$wanted_value.'%\'
> > > or c.column_name like \''.$wanted_value.'\'
> > > or upper(c.bus_name) like \'%'.$wanted_value.'%\'
> > > or upper(c.column_desc) like \'%'.$wanted_value.'%\')
> > > and b.abbr = o.node
> > > and o.column_name like c.column_name
>
> This join condition looks suspiscious. Shouldn't that be o.column_name =
> c.column_name ? Oracle probably can't optimize that join.
>
> > > group by c.object_name,c.column_name,c.column_desc
> > > order by 1,2,3';
> > >
> > >
> > > I am using the following to fetch the rows:
> > > while(($object_name,$column_name,$column_desc) = $sth->fetchrow_array)
> {
>
> That should be fine.
>
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: javascript and perl dbi
Robbie Staufer wrote: > Hi, > > I have a web page where I've used Java Script to set up some relational > menus, within a php script to send form data to a perl DBI script for > querying a database. The user selects an option from the first menu, > and an option from the second menu, and these values need to be passed > either to the php section of the script, or directly to the DBI script. > Is this nuts, or is there a way to pass the JS values to php or DBI? > > Inorvermyhead, > Robbie Hi Robbie, I would advise against using Perl and PHP in conjunction. There should be no need to do so. Both Perl and PHP are full-scale server-side programming languages, and either language alone should have adequate facilities to handle DB processing without the complications of inter-process communications.. If you are using PHP, you probably should not need to use javascript either, since PHP has some well-developed embeds to handle data input. It also has DB coonnectivity utilities Javascript/Perl is another matter. Javascript* is a very useful, but equally limited, tool for client-side data validation. The best way to use it is sparingly. Use it to ensure that use input is sensible in the context of the data being sought--that dates are readable as dates, numerical input as numbers, phone numbers have the correct number of digits, etc. You don't really address perl directly though. Javascript interacts with the form objects on the web page, and you can use it to reset them: document.FormName.FormObject.value = CorrectedValue; then your CGI script will read the form values through the query line. Unless you have extenxsive experience with both languages, and know exactly why you need to use both in conjunction, I would recommend using either Perl or PHP, but not both, for your server-side processing. Joseph *AFAIK, there is no such thing as "Java Script". Java is a standardized, paltform-independent, strongly-typed, structured compiled and intepreted language. Javascipt is an utnyped, unstructured, interpeted language, the implementation of of which is dependent on the browser on which it is run. Java was developed at Sun Computer. Javascript seems to have been developed by Netscape, although the information I base that impression on is not at all conclusive. -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: javascript and perl dbi
> Hi, > > I have a web page where I've used Java Script to set up some > relational You mean wher if they choose something from menu 1 ,then menu 2 changes accordingly, right? However javascript script is client side so it doesn't matter to the scripts as long as the Data is submitted properly via the html form. > menus, within a php script to send form data to a perl DBI script for > querying a database. The user selects an option from the first menu, > and an option from the second menu, and these values need to > be passed > either to the php section of the script, or directly to the > DBI script. > Is this nuts, or is there a way to pass the JS values to php or DBI? Like was mentioned I would use one or the other. Perl saya there is more than one way to do it and PHP people seem to try to make you feel like only it will do certain things. ( I'm not trying to start a flame war I'm just saying that most of the php die hards I talk to seem to think that php is the only language that can do things like work with databases and send email. I've actually had a few who were trying to evengelize me stare in amazment when I showed them a DBI script that managed some mysql tables and sent errors out as emails in just four lines of code. I say use what you like and what does what you need. I happen to like perl because it does what I need And about a zillion other things. And , IMHO, it is way easier to expand via modules. We've had lots of problems with php on our servers when ever we tried to add new features and it just seems bloated and funky like sweaty underpants, again just MHO .) What I'd do is create one perl script to print out your html form, complete with java script and also process your form. Check the CGI module, it will do everythign you need to do. http://search.cpan.org > > Inorvermyhead, > Robbie -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: javascript and perl dbi
Please post to the list so everyone can play.
>Dear Dan,
>
>Like you, I think that perl can do everything php can do, and more. I'm in the
>learning stages, so don't know alot about >either, but I've used both a few times.
>So taking your advice, I have a perl script that will eventually do everything I
>need. But I don't know how to place the >java script inside the perl script. For
>example, in html, I just put the java script in the area, and again in the
>>body wherever I want the interactive menus to appear.
>But in perl, if I use print $query->start_html('BMDB Query Form');
If that finction won't do what you need then don't use it.
See search.cpan.org and look for CGI.pm there's lots of info there.
You could use a 'here' doc.
print <
javascript...
...
HTML
Just watch out for your quotes and backslash them if need be.
DMuey
>there's no tag to house the java script. So I don't know where to put it.
>I hope I explained my question clearly. Can you show me an example of how it should
>look?
>Many thanks,
>Robbie
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
RE: javascript and perl dbi
Hope I'm not missing the context of the question :-o
--
You can include Javascript as any HTML code
print "\n";
print " var SealoffSource = true\; /* Deshabilitar Click
Derecho. */\n";
print " function click() {if (event.button==2)
{alert(\'Hardware: Mouse - Error. Please reboot.\')}}\n";
print "document.onmousedown=click\;\n";
print "\n";
Just be careful to not miss a backslash.
Or, as Dan said, use Here Documents.
I've found some details here:
http://www.perldoc.com/perl5.8.0/pod/perlfaq4.html#Why-don't-my-%3c%3cHERE-d
ocuments-work-
-rm-
- Original Message -
From: Dan Muey <[EMAIL PROTECTED]>
To: Robbie Staufer <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Wednesday, March 26, 2003 1:03 PM
Subject: RE: javascript and perl dbi
Please post to the list so everyone can play.
>Dear Dan,
>
>Like you, I think that perl can do everything php can do, and more. I'm in
the learning stages, so don't know alot about >either, but I've used both a
few times.
>So taking your advice, I have a perl script that will eventually do
everything I need. But I don't know how to place the >java script inside
the perl script. For example, in html, I just put the java script in the
area, and again in the >body wherever I want the interactive menus to
appear.
>But in perl, if I use print $query->start_html('BMDB Query Form');
If that finction won't do what you need then don't use it.
See search.cpan.org and look for CGI.pm there's lots of info there.
You could use a 'here' doc.
print <
javascript...
...
HTML
Just watch out for your quotes and backslash them if need be.
DMuey
>there's no tag to house the java script. So I don't know where to
put it.
>I hope I explained my question clearly. Can you show me an example of how
it should look?
>Many thanks,
>Robbie
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: perl DBI and mysql
Hi Jaws (!?),
You don't say where your script is failing, or what errors it's reporting,
which is going to make it hard for anyone to help. You could add some better
error checking. Here's a couple of lines lifted from one of my CGI's.
my $dbh;
eval { $dbh = DBI->connect( $DATA_SOURCE, $DB_USER, $DB_PASSWORD, {
RaiseError => 1, AutoCommit => 1 } ) };
if ($@) {
print "Failed in connecting to database, see following error\n";
print $@ . "\n";
return;
}
Perhaps you could add something like this to your script to get a better
idea of what's not working. You can put this type of error checking around
your compare and execute statements as well.
Good Luck
Rob
"Jaws" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hi all,
>
> i am new to perl and i want to use it with DBI:mysql module. I want to
> connect to my sql server and do some data manipulation like , insert,
> update and delete. I read the DBI documention but i cant get through to
get
> my script working. any help is needed. below is my script.
>
> #!/usr/bin/perl
>
> use DBI;
>
> $database="sampledb";
> $host="localhost";
> $user="db";
> $pw="mysql";
>
> $dbh=DBI->connect("DBI:mysql:database=$database;host=$host",$user,$pw,
> {RaiseError => 1});
>
> my $sth = $dbh->prepare(q{INSERT INTO USERS
> (USERNAME,PASSWORD,DESCRIPTION,ATTRIBUTES) VALUES (?, ?, ?, ?)
> }) or die $dbh->errstr;
> while (<>) {
> chomp;
> my ($USERNAME,$PASSWORD,$DESCRIPTION,$ATTRIBUTE) = split
/,/;
> $sth->execute($USERNAME,$PASSWORD,$DESCRIPTION,$ATTRIBUTE)
or
> die $dbh->errstr;
> }
> $dbh->commit or die $dbh->errstr;
> $dbh->disconnect;
>
>
> --
>
> Thanks.
>
> Jaws
>
>
>
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: perl DBI and mysql
when i run the program like this:
[EMAIL PROTECTED] util]# ./addvpdnuser.pl
sampleusername, samplepassword,sampledescription,sampleattr
DBD::mysql::st execute failed: Duplicate entry 'sample' for key 1 at
./addvpdnuser.pl line 17, <> line 1.
DBD::mysql::st execute failed: Duplicate entry 'sample' for key 1 at
./addvpdnuser.pl line 17, <> line 1.
[EMAIL PROTECTED] util]#
That's the error message of the program.
At 08:54 AM 3/28/2003 +, Rob Anderson wrote:
Hi Jaws (!?),
You don't say where your script is failing, or what errors it's reporting,
which is going to make it hard for anyone to help. You could add some better
error checking. Here's a couple of lines lifted from one of my CGI's.
my $dbh;
eval { $dbh = DBI->connect( $DATA_SOURCE, $DB_USER, $DB_PASSWORD, {
RaiseError => 1, AutoCommit => 1 } ) };
if ($@) {
print "Failed in connecting to database, see following error\n";
print $@ . "\n";
return;
}
Perhaps you could add something like this to your script to get a better
idea of what's not working. You can put this type of error checking around
your compare and execute statements as well.
Good Luck
Rob
"Jaws" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hi all,
>
> i am new to perl and i want to use it with DBI:mysql module. I want to
> connect to my sql server and do some data manipulation like , insert,
> update and delete. I read the DBI documention but i cant get through to
get
> my script working. any help is needed. below is my script.
>
> #!/usr/bin/perl
>
> use DBI;
>
> $database="sampledb";
> $host="localhost";
> $user="db";
> $pw="mysql";
>
> $dbh=DBI->connect("DBI:mysql:database=$database;host=$host",$user,$pw,
> {RaiseError => 1});
>
> my $sth = $dbh->prepare(q{INSERT INTO USERS
> (USERNAME,PASSWORD,DESCRIPTION,ATTRIBUTES) VALUES (?, ?, ?, ?)
> }) or die $dbh->errstr;
> while (<>) {
> chomp;
> my ($USERNAME,$PASSWORD,$DESCRIPTION,$ATTRIBUTE) = split
/,/;
> $sth->execute($USERNAME,$PASSWORD,$DESCRIPTION,$ATTRIBUTE)
or
> die $dbh->errstr;
> }
> $dbh->commit or die $dbh->errstr;
> $dbh->disconnect;
>
>
> --
>
> Thanks.
>
> Jaws
>
>
>
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---
cheers,
jaws
If there's one thing you need to remember it's this...
ALL SYSTEMS ARE VULNERABLE!
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: perl DBI and mysql
Jaws, > DBD::mysql::st execute failed: Duplicate entry 'sample' for key 1 at > ./addvpdnuser.pl line 17, <> line 1. > DBD::mysql::st execute failed: Duplicate entry 'sample' for key 1 at > ./addvpdnuser.pl line 17, <> line 1. > [EMAIL PROTECTED] util]# This is probably happening because the table has a primary key or unique constraint set. -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: perl DBI and mysql
What will I do to get my script working properly? Thanks for your help. At 04:17 AM 3/28/2003 -0500, Dave K wrote: Jaws, > DBD::mysql::st execute failed: Duplicate entry 'sample' for key 1 at > ./addvpdnuser.pl line 17, <> line 1. > DBD::mysql::st execute failed: Duplicate entry 'sample' for key 1 at > ./addvpdnuser.pl line 17, <> line 1. > [EMAIL PROTECTED] util]# This is probably happening because the table has a primary key or unique constraint set. -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --- cheers, jaws If there's one thing you need to remember it's this... ALL SYSTEMS ARE VULNERABLE! -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: perl DBI and mysql
Jaws,
This looks like your script is working fine, but the insert your trying to
do is in error. I'd guess that your just trying to add a row with a
duplicate key, and that's what DBI is complaining about, because of a
contraint on the database. You could try different keys, or the INSERT
statement from somewhere else (SQLPLUS?) to see if you get the same error.
Hope this helps.
Rob
"Jaws" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> when i run the program like this:
>
> [EMAIL PROTECTED] util]# ./addvpdnuser.pl
> sampleusername, samplepassword,sampledescription,sampleattr
>
> DBD::mysql::st execute failed: Duplicate entry 'sample' for key 1 at
> ./addvpdnuser.pl line 17, <> line 1.
> DBD::mysql::st execute failed: Duplicate entry 'sample' for key 1 at
> ./addvpdnuser.pl line 17, <> line 1.
> [EMAIL PROTECTED] util]#
>
> That's the error message of the program.
>
>
> At 08:54 AM 3/28/2003 +, Rob Anderson wrote:
> >Hi Jaws (!?),
> >
> >You don't say where your script is failing, or what errors it's
reporting,
> >which is going to make it hard for anyone to help. You could add some
better
> >error checking. Here's a couple of lines lifted from one of my CGI's.
> >
> >my $dbh;
> >eval { $dbh = DBI->connect( $DATA_SOURCE, $DB_USER, $DB_PASSWORD, {
> >RaiseError => 1, AutoCommit => 1 } ) };
> >
> >if ($@) {
> > print "Failed in connecting to database, see following
error\n";
> > print $@ . "\n";
> > return;
> >}
> >
> >Perhaps you could add something like this to your script to get a better
> >idea of what's not working. You can put this type of error checking
around
> >your compare and execute statements as well.
> >
> >Good Luck
> >
> >Rob
> >
> >
> >
> >"Jaws" <[EMAIL PROTECTED]> wrote in message
> >news:[EMAIL PROTECTED]
> > > Hi all,
> > >
> > > i am new to perl and i want to use it with DBI:mysql module. I want to
> > > connect to my sql server and do some data manipulation like , insert,
> > > update and delete. I read the DBI documention but i cant get through
to
> >get
> > > my script working. any help is needed. below is my script.
> > >
> > > #!/usr/bin/perl
> > >
> > > use DBI;
> > >
> > > $database="sampledb";
> > > $host="localhost";
> > > $user="db";
> > > $pw="mysql";
> > >
> > > $dbh=DBI->connect("DBI:mysql:database=$database;host=$host",$user,$pw,
> > > {RaiseError => 1});
> > >
> > > my $sth = $dbh->prepare(q{INSERT INTO USERS
> > > (USERNAME,PASSWORD,DESCRIPTION,ATTRIBUTES) VALUES (?, ?, ?, ?)
> > > }) or die $dbh->errstr;
> > > while (<>) {
> > > chomp;
> > > my ($USERNAME,$PASSWORD,$DESCRIPTION,$ATTRIBUTE) = split
> >/,/;
> > >
$sth->execute($USERNAME,$PASSWORD,$DESCRIPTION,$ATTRIBUTE)
> >or
> > > die $dbh->errstr;
> > > }
> > > $dbh->commit or die $dbh->errstr;
> > > $dbh->disconnect;
> > >
> > >
> > > --
> > >
> > > Thanks.
> > >
> > > Jaws
> > >
> > >
> > >
> >
> >
> >
> >--
> >To unsubscribe, e-mail: [EMAIL PROTECTED]
> >For additional commands, e-mail: [EMAIL PROTECTED]
>
> ---
> cheers,
> jaws
>
> If there's one thing you need to remember it's this...
> ALL SYSTEMS ARE VULNERABLE!
>
>
>
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: perl DBI and mysql
Thanks Ill try that.
At 09:26 AM 3/28/2003 +, Rob Anderson wrote:
Jaws,
This looks like your script is working fine, but the insert your trying to
do is in error. I'd guess that your just trying to add a row with a
duplicate key, and that's what DBI is complaining about, because of a
contraint on the database. You could try different keys, or the INSERT
statement from somewhere else (SQLPLUS?) to see if you get the same error.
Hope this helps.
Rob
"Jaws" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> when i run the program like this:
>
> [EMAIL PROTECTED] util]# ./addvpdnuser.pl
> sampleusername, samplepassword,sampledescription,sampleattr
>
> DBD::mysql::st execute failed: Duplicate entry 'sample' for key 1 at
> ./addvpdnuser.pl line 17, <> line 1.
> DBD::mysql::st execute failed: Duplicate entry 'sample' for key 1 at
> ./addvpdnuser.pl line 17, <> line 1.
> [EMAIL PROTECTED] util]#
>
> That's the error message of the program.
>
>
> At 08:54 AM 3/28/2003 +, Rob Anderson wrote:
> >Hi Jaws (!?),
> >
> >You don't say where your script is failing, or what errors it's
reporting,
> >which is going to make it hard for anyone to help. You could add some
better
> >error checking. Here's a couple of lines lifted from one of my CGI's.
> >
> >my $dbh;
> >eval { $dbh = DBI->connect( $DATA_SOURCE, $DB_USER, $DB_PASSWORD, {
> >RaiseError => 1, AutoCommit => 1 } ) };
> >
> >if ($@) {
> > print "Failed in connecting to database, see following
error\n";
> > print $@ . "\n";
> > return;
> >}
> >
> >Perhaps you could add something like this to your script to get a better
> >idea of what's not working. You can put this type of error checking
around
> >your compare and execute statements as well.
> >
> >Good Luck
> >
> >Rob
> >
> >
> >
> >"Jaws" <[EMAIL PROTECTED]> wrote in message
> >news:[EMAIL PROTECTED]
> > > Hi all,
> > >
> > > i am new to perl and i want to use it with DBI:mysql module. I want to
> > > connect to my sql server and do some data manipulation like , insert,
> > > update and delete. I read the DBI documention but i cant get through
to
> >get
> > > my script working. any help is needed. below is my script.
> > >
> > > #!/usr/bin/perl
> > >
> > > use DBI;
> > >
> > > $database="sampledb";
> > > $host="localhost";
> > > $user="db";
> > > $pw="mysql";
> > >
> > > $dbh=DBI->connect("DBI:mysql:database=$database;host=$host",$user,$pw,
> > > {RaiseError => 1});
> > >
> > > my $sth = $dbh->prepare(q{INSERT INTO USERS
> > > (USERNAME,PASSWORD,DESCRIPTION,ATTRIBUTES) VALUES (?, ?, ?, ?)
> > > }) or die $dbh->errstr;
> > > while (<>) {
> > > chomp;
> > > my ($USERNAME,$PASSWORD,$DESCRIPTION,$ATTRIBUTE) = split
> >/,/;
> > >
$sth->execute($USERNAME,$PASSWORD,$DESCRIPTION,$ATTRIBUTE)
> >or
> > > die $dbh->errstr;
> > > }
> > > $dbh->commit or die $dbh->errstr;
> > > $dbh->disconnect;
> > >
> > >
> > > --
> > >
> > > Thanks.
> > >
> > > Jaws
> > >
> > >
> > >
> >
> >
> >
> >--
> >To unsubscribe, e-mail: [EMAIL PROTECTED]
> >For additional commands, e-mail: [EMAIL PROTECTED]
>
> ---
> cheers,
> jaws
>
> If there's one thing you need to remember it's this...
> ALL SYSTEMS ARE VULNERABLE!
>
>
>
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---
cheers,
jaws
If there's one thing you need to remember it's this...
ALL SYSTEMS ARE VULNERABLE!
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Perl DBI connect from Windows
Hi,
I want to connect to a "Oracle SQL Developer" with perl (running on
windows).
I tried something like,
use DBI;
$db = "database1";
$host = "10.0.0.1:1433";
$user = "username";
$password = "password";
my $dbh = DBI->connect ("DBI:Oracle:database=$db:host=$host",
$user,
$password)
or die "Can't connect to database:
$DBI::errstr\n";
install_driver(Oracle) failed: Can't load
'C:/Perl/lib/auto/DBD/Oracle/Oracle.dll' for module DBD::Oracle:
load_file:The specified procedure could not be found at
C:/Perl/lib/DynaLoader.pm line 202.
Can someone please let me know how to connect to the database. I tried
searching on google. And tried few permutations but none of it worked.
--
TIA
Gautam
Perl DBI for SQL Server
Hi,
Query 1
Is it possible to use Perl DBI to connect to SQL Server?
I think all DBD drivers (mysql,Oracle,etc) need different syntax for
connect.
Supposing I want to connect to database runnnig at 10.0.0.1 port 1433 and
schema name is "schema1" username and password to connect to database is
"user" and "pass".
my $dbh = DBI->connect(**what is the syntax to write the string here??**)
|| die $DBI::errstr;
Query 2
==
I wanted to connect to one Oracle database through Perl DBI.
Initially I was trying with Perl 5.10, then I installed Perl 5.6 and
installed DBI, DBD-Oracle throough ppm.
I can connect to the Oracle database through toad.
If I try ,
my $dbh =
DBI->connect("dbi:Oracle:HOST=10.10.10.1;PORT=1533;UID=userid;PWD=pswd"
)|| die $DBI::errstr;;
I get error:
DBI connect('HOST=10.10.10.1;PORT=1533;UID=userid;PWD=pswd','',...) failed:
Can't connect using this syntax without specifying a HOST and a SID at
D:\scripts\test1.pl line 4
Can someone please let me know what is the correct way to connect?
Thanks & Regards
Gautam
Re: What's Perl DBI module?
On Thu, 9 Aug 2001, Kehai Li wrote: > what's Perl DBI module? How can I execute a web-database in Perl language? Here's a good place to start: http://dbi.symbolstone.org/index.html -- Brett http://www.chapelperilous.net/btfwk/ No man can have a reasonable opinion of women until he has long lost interest in hair restorers. -- Austin O'Malley -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: perl DBI disconnect question
I'm not a DBI guru but I guess if you "try and catch" your DBI exception handling
with eval, you can exit properly at all.
I mean:
eval{
$dbh=DBI->connect($connect_string);
$sth=$dbh->prepare($sql);
$sth->execute();
#some fetch here
$sth->finish();
$dbh->disconnect();
}
if($@)
{
print "program failed because : $@\n";
exit;
}
José.
rory oconnor <[EMAIL PROTECTED]> a écrit :
Regarding perl DBI - I know it's proper to use $sth->finish and
$dbh->disconnect and I am doing that at the end of my script...
however, i am also doing alot of error checking at the beginning of the
script (to make sure data has been entered, and formatted properly). If
there's an error I would usually just send back an error message and
exit the script to prevent it from having to run through the rest of it
needlessly. do I need to explicitly disconnect before exiting in those
instances or it it OK to just exit?
thanks,
rory
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
Yahoo! Mail -- Une adresse @yahoo.fr gratuite et en français !
Re: perl DBI disconnect question
Rory Oconnor wrote: > > Regarding perl DBI - I know it's proper to use $sth->finish and > $dbh->disconnect and I am doing that at the end of my script... > > however, i am also doing alot of error checking at the beginning of the > script (to make sure data has been entered, and formatted properly). If > there's an error I would usually just send back an error message and > exit the script to prevent it from having to run through the rest of it > needlessly. do I need to explicitly disconnect before exiting in those > instances or it it OK to just exit? > The solution is to not create the handle untill you are ready to talk to the database. Todd W -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: perl DBI disconnect question
on Thu, 16 May 2002 18:03:12 GMT, Rory Oconnor wrote:
>
> Regarding perl DBI - I know it's proper to use $sth->finish and
> $dbh->disconnect and I am doing that at the end of my script...
>
> however, i am also doing alot of error checking at the beginning of the
> script (to make sure data has been entered, and formatted properly). If
> there's an error I would usually just send back an error message and
> exit the script to prevent it from having to run through the rest of it
> needlessly. do I need to explicitly disconnect before exiting in those
> instances or it it OK to just exit?
You can always put the following in your script:
END {
$dbh->disconnect() if $dbh;
}
which will be executed even if your program 'die's.
--
felix
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: perl DBI disconnect question
> > From: Felix Geerinckx[SMTP:[EMAIL PROTECTED]]
> > exit the script to prevent it from having to run through the rest
> of it needlessly. do I need to explicitly disconnect before exiting
>in those instances or it it OK to just exit? You can always put the
>following in your script:
>
> END {
> $dbh->disconnect() if $dbh;
> }
>
> which will be executed even if your program 'die's.
This is another piece of code that I will have to add to my template
along with
#!Where ever bin is
use warnings;
use strict;
sub error{
Error Stuff;
}
END {
End Stuff;
}
=cut
Documentation here.
Does anyone have any other things that would be good practice in most
scripts.
Harry
__
Do You Yahoo!?
LAUNCH - Your Yahoo! Music Experience
http://launch.yahoo.com
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
RE: Perl DBI vs SQLPLUS
> -Original Message- > From: Joseph Bajin [mailto:[EMAIL PROTECTED]] > Sent: Monday, June 03, 2002 2:34 PM > To: [EMAIL PROTECTED] > Subject: Perl DBI vs SQLPLUS > > > Got a question for you guys out there. > > I currently have a list of records that I need to search for in a > mult-db that we have. I was wondering if perl would be faster > to process > or stick with my .ksh script that uses sqlplus. Here's how > the current > setup is > > cat input file and read for record # > > sqlplus into db1 and do a select statement to find what db that the > account is located in. (This db just contains basic info on where the > account is actually stored) > > exit sql plus > > open another sqlplus session to db that was found in previous select > statement. > > select out data needed and write to a file. > > exit sqlplus > > Do the loop again. > > > I needed to process about 8000 records today. It would get about 30% > through and the process would just stop. Don't know if it's a buffer > issue (that was a suggestion) or if perl because it will directly > connect to the DB would be better. > > Got any ideas. Can't say why it's hanging, but using DBI should be faster than what you're doing, because you can keep a database connection open (at least on db1). How many total databases are there? -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Perl DBI vs SQLPLUS
[EMAIL PROTECTED] wrote: >>-Original Message- >>From: Joseph Bajin [mailto:[EMAIL PROTECTED]] >>Sent: Monday, June 03, 2002 2:34 PM >>To: [EMAIL PROTECTED] >>Subject: Perl DBI vs SQLPLUS >> >> >>Got a question for you guys out there. >> >>I currently have a list of records that I need to search for in a >>mult-db that we have. I was wondering if perl would be faster >>to process >>or stick with my .ksh script that uses sqlplus. Here's how >>the current >>setup is >> >>cat input file and read for record # >> >>sqlplus into db1 and do a select statement to find what db that the >>account is located in. (This db just contains basic info on where the >>account is actually stored) >> >>exit sql plus >> >>open another sqlplus session to db that was found in previous select >>statement. >> >>select out data needed and write to a file. >> >>exit sqlplus >> >>Do the loop again. >> >> >>I needed to process about 8000 records today. It would get about 30% >>through and the process would just stop. Don't know if it's a buffer >>issue (that was a suggestion) or if perl because it will directly >>connect to the DB would be better. >> >>Got any ideas. >> > >Can't say why it's hanging, but using DBI should be faster than >what you're doing, because you can keep a database connection >open (at least on db1). How many total databases are there? > Total of 8 databases. The first database is used only to reference where the rest of the data is located. ie (record is located on Db4, and so on. ) Thanks Joe -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Perl DBI and Oracle
> -Original Message- > From: Jackson, Harry [mailto:[EMAIL PROTECTED]] > Sent: Thursday, June 27, 2002 9:35 AM > To: '[EMAIL PROTECTED]' > Subject: Perl DBI and Oracle > > > Ho do you > > set linesize 1000; > > using dbi for Oracle. I have tried inserting it before the "select" > statement and putting it in its own "prepare" statement but > neither have > worked. Google has got very little on this when I searched > for it. I would > appreciate a decent link to how to do this sort of thing. "set linesize" is a SQL*Plus commmand. The database knows nothing about "set linesize". DBI talks directly to the database engine, so only Oracle SQL statements are understood. Why are you wanting to issue a "set linesize" command? It's the responsibility of your script to format the output appropriately; DBI can't help with that. -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Perl DBI and Oracle
"set linesize" is a SQL*Plus command to control the size of an output line dbi is used for manipulating data from a database, the output is controlled by you... What are you trying to accomplish? -Joe --- "Jackson, Harry" <[EMAIL PROTECTED]> wrote: > Ho do you > > set linesize 1000; > > using dbi for Oracle. I have tried inserting it before the "select" > statement and putting it in its own "prepare" statement but neither > have > worked. Google has got very little on this when I searched for it. > I would > appreciate a decent link to how to do this sort of thing. > > Cherrs > Harry. __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Perl DBI and Oracle
> -Original Message-
> From: Joe Raube [mailto:[EMAIL PROTECTED]]
>
> "set linesize"
>
> is a SQL*Plus command to control the size of an output line
>
> dbi is used for manipulating data from a database, the output is
> controlled by you...
>
> What are you trying to accomplish?
I retrieve a load of records but because the default linesize means that it
comes out on multiple lines when printing to a CSV. I suppose I could remove
the control characters from the end of the line but I was wondering if it
could be done by setting the linesize using dbi.
Harry
*
COLT Telecommunications
Registered in England No. 2452736
Registered Office: Bishopsgate Court, 4 Norton Folgate, London E1 6DQ
Tel. +44 20 7390 3900
This message is subject to and does not create or vary any contractual
relationship between COLT Telecommunications, its subsidiaries or
affiliates ("COLT") and you. Internet communications are not secure
and therefore COLT does not accept legal responsibility for the
contents of this message. Any view or opinions expressed are those of
the author. The message is intended for the addressee only and its
contents and any attached files are strictly confidential. If you have
received it in error, please telephone the number above. Thank you.
*
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
RE: Perl DBI and Oracle
I think that if you posted some code, we could give better assistance with this matter. --- "Jackson, Harry" <[EMAIL PROTECTED]> wrote: > > I retrieve a load of records but because the default linesize means > that it > comes out on multiple lines when printing to a CSV. I suppose I > could remove > the control characters from the end of the line but I was wondering > if it > could be done by setting the linesize using dbi. > > Harry __ Do You Yahoo!? Yahoo! - Official partner of 2002 FIFA World Cup http://fifaworldcup.yahoo.com -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Perl DBI and Oracle
Title: RE: Perl DBI and Oracle
> -Original Message-
> From: Joe Raube [mailto:[EMAIL PROTECTED]]
>
>
> I think that if you posted some code, we could give better assistance
> with this matter.
This is the code that I am using to connect but if I am unable to send server messages from the DBI then I could code it to strip the control characters before writing to the file. I do not need to know how to do this but I thought the code although no shining example may help others.
#!Perl
# This program was written to be run on a Windows box not Unix
#
use warnings;
use strict;
use Harry::Connect;
my @table_name;
my $region;
foreach $region ( "csr_uk", "csr_de", "csr_at", "csr_it", "csr_ei",
"csr_fr", "csr_es", "csr_ch", "csr_nl", "csr_be",
"csr_nx", "csr_uk", "csr_int") {
my $dbh = ConnectToOracle($region) or die "shit";
open (OUTPUT, ">c:\\Worldcom_$region.csv") or die ("Cannot open file: $!");
my $sth = $dbh->prepare("select distinct a.customer_id,
a.a_side_customer,
a.z_side_customer,
a.circ_path_hum_id,
a.status,
a.bandwidth,
a.type,
substr(b.site_hum_id, 1, instr(b.site_hum_id, '_', 1, 4) - 1),
substr(c.site_hum_id, 1, instr(c.site_hum_id, '_', 1, 4) - 1)
from $region.circ_path_inst a, $region.site_inst b, $region.site_inst c
where a.a_side_site_id = b.site_inst_id
and a.z_side_site_id = c.site_inst_id
and (a.customer_id like '%x%'
or a.customer_id like '%xx%'
or a.a_side_customer like '%xx%'
or a.a_side_customer like '%xx%'
or a.z_side_customer like '%xx%'
or a.z_side_customer like '%xx%')
");
$sth->execute() || die "Execution of the SQL FAILED $!";
while (@table_name = $sth->fetchrow_array()) {
print OUTPUT ("$table_name[0],$table_name[1],$table_name[2],$table_name[3],
$table_name[4],$table_name[5],$table_name[6]$table_name[7],
$table_name[8],$table_name[8]\n")
or die ("Cannot output to OUTPUT filehandle: $!");
}
# Finish with DBI
#
$sth->finish()
or die "Unable to finish: $!";
# Disconnect from Database
#
$dbh->disconnect()
or die "Unable to Disconnect: $!";
}
close (OUTPUT);
exit 0;
*
COLT Telecommunications
Registered in England No. 2452736
Registered Office: Bishopsgate Court, 4 Norton Folgate, London E1 6DQ
Tel. +44 20 7390 3900
This message is subject to and does not create or vary any contractual
relationship between COLT Telecommunications, its subsidiaries or
affiliates ("COLT") and you. Internet communications are not secure
and therefore COLT does not accept legal responsibility for the
contents of this message. Any view or opinions expressed are those of
the author. The message is intended for the addressee only and its
contents and any attached files are strictly confidential. If you have
received it in error, please telephone the number above. Thank you.
*
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Perl DBI Oracle: Multiple statements
Hi: Not sure if this is the right place to post this question.
My dba has asked to execute a alter statement before the select statement in a
perl script. So I need to run the following. Supposedly the alter statement,
helps run the select sql run faster.
alter session set db_file_multiblock_read_count = 128;
SELECT ED.utime, ED.info, ED.agent_id FROM EVENT_DATA ED WHERE ED.utime between
$period order by ED.utime;
where $period is a string such as "190 and 300".
I tried the following
my $sql = qq{
BEGIN
alter session set db_file_multiblock_read_count = 128;
SELECT ED.utime, ED.info, ED.agent_id FROM EVENT_DATA ED WHERE
ED.utime between $period order by ED.utime;
END; };
my $sth = $dbh->prepare($sql);
$sth->execute();
I get the following error
DBD::Oracle::st execute failed: ORA-06550: line 3, column 5:
PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:
begin case declare exit for goto if loop mod null pragma
raise return select update while with
<<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
pipe
The symbol "update was inserted before "ALTER" to continue. (DBD ERROR: error
possibly near <*> indicator at char 14 in '
BEGIN
<*>alter session set
db_file_multiblock_read_count = 128;
SELECT ED.utime, ED.info, ED.agent_id FROM
EVENT_DATA ED WHERE ED.utime between 1164650400 and 1164653940 order by
ED.utime;
END; ') [for Statement "
BEGIN
alter session set db_file_multiblock_read_count
= 128;
SELECT ED.utime, ED.info, ED.agent_id FROM
EVENT_DATA ED WHERE ED.utime between 1164650400 and 1164653940 order by
ED.utime;
END; "] at
/actuate/AcServer/reports_scripts/report_functions.pl line 48.
=
Could someone help.
Thanks
Ravi
Do you Yahoo!?
Everyone is raving about the all-new Yahoo! Mail beta.
http://new.mail.yahoo.com
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: Perl DBI - using parameters
On Fri, Feb 01, 2002 at 12:13:41PM -0500, McElwee, Shane wrote:
> foreach $i (@table_arr){
> $content = $i;
> # print ("table name is: $i \n");
> open( CONTENT, ">$content" ) || die "Can't open file $content";
> my $sth = $dbh->prepare("select * from ?");
> $sth->bind_param(1, $i);
>
> my $row;
>
> $sth->execute or die "Can't execute SQL statement: ", $sth->errstr(),
> "\n";
> $row = $sth->dump_results(80, "\n", ':',\*CONTENT);
> }
Placeholders are for data, not SQL syntax. A placeholder doesn't just
insert the text as is, it quotes it. In your case, the quoting is
preventing the database from being able to parse it. Instead of using a
placeholder just use Perl to interpolate:
my $sth = $dbh->prepare("select * from $i");
Also, if $i is input from a user make sure to check it; I'd suggest
not allowing anything except [A-Za-z0-9_].
Michael
--
Administrator www.shoebox.net
Programmer, System Administrator www.gallanttech.com
--
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
RE: Perl DBI - using parameters
I misunderstood the example from the book. Thanks for clearing that up.
@table_arr is reading from a file now but I can use your suggestion if I
have to make the process more interactive.
Thanks
Shane
-Original Message-
From: Michael Fowler [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 01, 2002 12:53 PM
To: McElwee, Shane
Cc: '[EMAIL PROTECTED]'
Subject: Re: Perl DBI - using parameters
On Fri, Feb 01, 2002 at 12:13:41PM -0500, McElwee, Shane wrote:
> foreach $i (@table_arr){
> $content = $i;
> # print ("table name is: $i \n");
> open( CONTENT, ">$content" ) || die "Can't open file $content";
> my $sth = $dbh->prepare("select * from ?");
> $sth->bind_param(1, $i);
>
> my $row;
>
> $sth->execute or die "Can't execute SQL statement: ",
$sth->errstr(),
> "\n";
> $row = $sth->dump_results(80, "\n", ':',\*CONTENT);
> }
Placeholders are for data, not SQL syntax. A placeholder doesn't just
insert the text as is, it quotes it. In your case, the quoting is
preventing the database from being able to parse it. Instead of using a
placeholder just use Perl to interpolate:
my $sth = $dbh->prepare("select * from $i");
Also, if $i is input from a user make sure to check it; I'd suggest
not allowing anything except [A-Za-z0-9_].
Michael
--
Administrator www.shoebox.net
Programmer, System Administrator www.gallanttech.com
--
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: Perl DBI for Sybase
From: [EMAIL PROTECTED]
> Hello all:
> I am pretty new in this matter.
> My code looks like this:
> $sth2=$dbh->prepare("Select job.joid,
> job_name,command,owner,machine,std_out_file,std_err_file,box_joid,time
> zone from job, job2 where job.joid = job2.joid"); $sth2->execute;
> while (my $info = $sth2->fetchrow_hasref) { calls a different
> subroutine to validate each argument like:
> check_job_name($instance,$box_name,$info->{job_name},$info->{machine})
> ;
>
> }
>
> My question is... in my while loop am I quring the database every time
> when I say $sth2-> fetchrow_hasref ?
Not really. Basicaly when you execute the query the server
prepares a resultset (most probably it'll return the first row before it
has found&prepared the last one), then each fetch just tells the
server to send the next row. There is some caching going on and
the driver might even fetch all the data from the server before giving
you the first row, but this is nothing YOU should care about.
(It definitely is not anything I would care about. )
> Is there any way I can call the
> fetchrow one time and store it in a hash.
You could use fetchall_arrayref(), but I would not recomend doing
that unless you are really sure the resultset is small enough. That
would slurp all the data to memory of YOUR process with quite
some overhead.
Stop worrying and fetch the data row after row :-)
Jenda
=== [EMAIL PROTECTED] == http://Jenda.Krynicky.cz ==
There is a reason for living. There must be. I've seen it somewhere.
It's just that in the mess on my table ... and in my brain.
I can't find it.
--- me
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
perl DBI problem while installing bugzilla
> Hi All, > > I am having a problem while isntalling Bugzilla , Checksetup.pl throws > errors and exits.Below is the detail. > > when I run checksetup.pl in bugzilla folder I get the following error, > Though I have installed latest DBD-mysql which is available in CPAN. > > ERROR --- > > Checking formod_perl (v1.999022) ok: found v2.03 > Reading ./localconfig... > Checking for DBD-mysql (v4.00) ok: found v4.016 > > *Undefined subroutine &DBD::mysql::db::_login called at > /usr/lib/perl5/site_perl/5.8.8/i386-linux-thread-multi/DBD/mysql.pm line > 142, line 522.* > > > ERROR --- > > Can any help me in solving this I am stuck up here. > > thanks, > Siva >
Perl & DBI/DBD & Access MDB files...
-- There are only 10 types of people in the world - Those who understand binary, and those who don't Does anyone know if it is possible to access a .mdb file (MS Access) from perl running on a linux box? I don't have access to a windows box to serve the files and the DBD:ODBC drivers seem to work only under the win32 port of Perl. FYI. I have >300 .mdb files that I want to extract all the data to CSV files (There are 4 tables (with the same tablenames) within each .mdb). Thanks in advance for any advice/help. Mark. -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Perl & DBI/DBD & Access MDB files...
-- There are only 10 types of people in the world - Those who understand binary, and those who don't Does anyone know if it is possible to access a .mdb file (MS Access) from perl running on a linux box? I don't have access to a windows box to serve the files and the DBD:ODBC drivers seem to work only under the win32 port of Perl. FYI. I have >300 .mdb files that I want to extract all the data to CSV files (There are 4 tables (with the same tablenames) within each .mdb). Thanks in advance for any advice/help. Mark. -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[Fwd: RE: javascript and perl dbi]
Original Message Subject: RE: javascript and perl dbi Date: Wed, 26 Mar 2003 13:03:50 -0600 From: "Dan Muey" <[EMAIL PROTECTED]> To: "Robbie Staufer" <[EMAIL PROTECTED]> CC: <[EMAIL PROTECTED]> javascript... ... HTML Just watch out for your quotes and backslash them if need be. DMuey >there's no tag to house the java script. So I don't know where to put it. >I hope I explained my question clearly. Can you show me an example of how it should look? >Many thanks, >Robbie -- -*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-* Robbie Staufer NCAR/SCD 1850 Table Mesa Dr. Rm. 42 Boulder, CO. 80305 (303) 497-1836
Perl dbi - Microsoft Sql Server DRIVER
I can't find anything on ppm for dbd microsoft sql server. Dbi can communicate with a MsSql server correct? If so where and how do I get the driver. -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Perl DBI connect from Windows
MGautam wrote:
Hi,
I want to connect to a "Oracle SQL Developer" with perl (running on
windows).
I tried something like,
use DBI;
$db = "database1";
$host = "10.0.0.1:1433";
$user = "username";
$password = "password";
my $dbh = DBI->connect ("DBI:Oracle:database=$db:host=$host",
$user,
$password)
or die "Can't connect to database:
$DBI::errstr\n";
install_driver(Oracle) failed: Can't load
'C:/Perl/lib/auto/DBD/Oracle/Oracle.dll' for module DBD::Oracle:
load_file:The specified procedure could not be found at
C:/Perl/lib/DynaLoader.pm line 202.
Do you have an Oracle client installed on the Windows box?
Robert
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
Re: Perl DBI connect from Windows
On Sat, Dec 20, 2008 at 6:17 AM, MGautam wrote:
> Hi,
> I want to connect to a "Oracle SQL Developer" with perl (running on
> windows).
>
> I tried something like,
>
> use DBI;
>
> $db = "database1";
> $host = "10.0.0.1:1433";
> $user = "username";
> $password = "password";
>
> my $dbh = DBI->connect ("DBI:Oracle:database=$db:host=$host",
> $user,
> $password)
> or die "Can't connect to database:
> $DBI::errstr\n";
>
>
> install_driver(Oracle) failed: Can't load
> 'C:/Perl/lib/auto/DBD/Oracle/Oracle.dll' for module DBD::Oracle:
> load_file:The specified procedure could not be found at
> C:/Perl/lib/DynaLoader.pm line 202.
>
>
> Can someone please let me know how to connect to the database. I tried
> searching on google. And tried few permutations but none of it worked.
>
You need install the Oracle Perl DBD driver in order to connect to Oracla DB
http://search.cpan.org/~pythian/DBD-Oracle-1.22/Oracle.pm
In windows use the Perl Package Manager for install it.
Regards.
>
> --
> TIA
> Gautam
>
--
Francisco Valladolid H.
-- http://bsdguy.net - Jesus Christ follower.
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
Re: Perl DBI connect from Windows
From: "ficovh" You need install the Oracle Perl DBD driver in order to connect to Oracla DB http://search.cpan.org/~pythian/DBD-Oracle-1.22/Oracle.pm In windows use the Perl Package Manager for install it. As far as I know, if using perl 5.10.0, there is no a ppm package that offers the Oracle instant client, and it must be downloaded from Oracle's web site and installed. (That client is just some more .dll files that should be in the path.) Octavian -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Re: Perl DBI connect from Windows
MGautam wrote:
Hi,
I want to connect to a "Oracle SQL Developer" with perl (running on
windows).
I tried something like,
use DBI;
$db = "database1";
$host = "10.0.0.1:1433";
$user = "username";
$password = "password";
my $dbh = DBI->connect ("DBI:Oracle:database=$db:host=$host",
$user,
$password)
or die "Can't connect to database:
$DBI::errstr\n";
install_driver(Oracle) failed: Can't load
'C:/Perl/lib/auto/DBD/Oracle/Oracle.dll' for module DBD::Oracle:
load_file:The specified procedure could not be found at
C:/Perl/lib/DynaLoader.pm line 202.
Can someone please let me know how to connect to the database. I tried
searching on google. And tried few permutations but none of it worked.
Per an offline email...
You need to install the Oracle client on Windows. SQLDeveloper connects
over JDBC so the client is not necessary. Perl/DBI/DBD::Oracle needs the
client libraries for it connections back to the Oracle database.
HTH
Robert
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
Re: Perl DBI for SQL Server
>
> Query 1
>
>
> Is it possible to use Perl DBI to connect to SQL Server?
> I think all DBD drivers (mysql,Oracle,etc) need different syntax for
> connect.
>
> Supposing I want to connect to database runnnig at 10.0.0.1 port 1433 and
> schema name is "schema1" username and password to connect to database is
> "user" and "pass".
>
> my $dbh = DBI->connect(**what is the syntax to write the string here??**)
> || die $DBI::errstr;
I've been using DBD::ODBC to connect to SQL Server 2005. The only problem
I've had is with the bind_param_inout_array method. That method does not
seem to be implemented in DBD::ODBC 1.17.
my $dsn = 'schema1';
my $dbuser = 'user';
my $dbpass = 'pass';
my $dbh = DBI->connect("dbi:ODBC:$dsn", $dbuser, $dbpass) or die
"$DBI::errstr\n";
You would need to create the ODBC connection to 'schema1' using the Data
Sources administration tool.
- Mike
Re: Perl DBI for SQL Server
From: "Mike Munhall"
> I've been using DBD::ODBC to connect to SQL Server 2005. The only problem
> I've had is with the bind_param_inout_array method. That method does not
> seem to be implemented in DBD::ODBC 1.17.
>
> my $dsn = 'schema1';
> my $dbuser = 'user';
> my $dbpass = 'pass';
>
> my $dbh = DBI->connect("dbi:ODBC:$dsn", $dbuser, $dbpass) or die
> "$DBI::errstr\n";
>
> You would need to create the ODBC connection to 'schema1' using the Data
> Sources administration tool.
No. You can specify the connection properties in the connection
string. There are examples in DBD::ODBC.
Jenda
= je...@krynicky.cz === http://Jenda.Krynicky.cz =
When it comes to wine, women and song, wizards are allowed
to get drunk and croon as much as they like.
-- Terry Pratchett in Sourcery
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
help on perl dbi and dbd:mysql
I am beginner in perl. i needed to connect the perl programs to mysql database. Therefore I downloaded dbi and dbd:mysql (actually it was mysql-msql modules). I am finding it difficult to compile and run both dbi and dbd:mysql module. By the way I am using Windows 98. When I tried out dbi --- i.e first compiling "makefile.pl" and then make, make test and install (as said in the faq documentation) the following error message appears C:\DBI-1.18>make MAKE Version 5.0 Copyright (c) 1987, 1997 Borland International Error makefile 150: Colon expected Error makefile 797: Redefinition of target 'DBI.c' Fatal makefile 893: No terminator specified for in-line file operator --error When I tried out dbd::mssql --- When the makefile.pl has been compiled there are a few questions to be answered. In one of the questions it asks for the path of 'include' directory. but there wasn't a include dir in this mysql-msql module. What does this include dir mean. The default gives "local/usr/hughs" -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Perl DBI for Sybase [OT]
Jenda Krynicky wrote: [snip] > > You could use fetchall_arrayref(), but I would not recomend doing > that unless you are really sure the resultset is small enough. That > would slurp all the data to memory of YOUR process with quite > some overhead. > Are there any benchmarks on this? Becouse i guess you make the db server work alot more, or at least for a longer time. Consider you have a www server and a db server, then i don't think it's fully obvious wether you'd want to fetch all the rows, so the db can close down the connection and be ready for the next user. On the other hand, as you say the process will eat memory...so does anyone has THE ANSWER(TM)? /Jon > Stop worrying and fetch the data row after row :-) > > Jenda > > === [EMAIL PROTECTED] == http://Jenda.Krynicky.cz == > There is a reason for living. There must be. I've seen it somewhere. > It's just that in the mess on my table ... and in my brain. > I can't find it. > --- me > > -- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Perl DBI for Sybase [OT]
From: Jon Molin <[EMAIL PROTECTED]> > Jenda Krynicky wrote: > [snip] > > > > You could use fetchall_arrayref(), but I would not recomend doing > > that unless you are really sure the resultset is small enough. That > > would slurp all the data to memory of YOUR process with quite some > > overhead. > > > > Are there any benchmarks on this? Becouse i guess you make the db > server work alot more, or at least for a longer time. > > Consider you have a www server and a db server, then i don't think > it's fully obvious wether you'd want to fetch all the rows, so the db > can close down the connection and be ready for the next user. On the > other hand, as you say the process will eat memory...so does anyone > has THE ANSWER(TM)? No I did not do any benchmarks. But if you look into DBI.pm you'll find out that fetchall_arrayref() is implemented on top of fetchrow_hashref(). So basicaly the only difference between having a loop that fetches one row, does some computations and prints a result versus fetching all rows and then looping through them is that in the second case your scripts uses more memory, but closes the resultset a litle bit later. Keep in mind that most usualy you don't do anything heavy with the rows, you don't wait for anything. So it doesn't take longer to make the things you need and print results than to add the data to a structure. And since you may end up useing a lot of memory you may actualy spend more time allocating memory and swaping than you would processing the data. I'd say ... if the processing doesn't take long ... don't play tricks, fetch and process rows one by one. If processing one row takes long (like if you need to fetch a page for each row) you micht consider reading all the data beforehand and processing them later. But even in this case if I cared for efficiency I would not use fetchall_arrayref(). Keep in mind that it uses fetchrow_hashref() which has to construct a hash for every row. It's much more efficient to fetch into an array or into bound variables and store the data in an array of arrays. In either case the number of fetches, the number of "calls" to the database will be exactly the same. Jenda === [EMAIL PROTECTED] == http://Jenda.Krynicky.cz == There is a reason for living. There must be. I've seen it somewhere. It's just that in the mess on my table ... and in my brain. I can't find it. --- me -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Perl DBI Oracle SYSDBA help/advise please:
Hi all,
To anyone who had been using Perl DBI and Oracle, can you please confirm if
connection by SYSDBA works or doesn't?
I currently have the following DBI / DBD installed on this problem:
DBI Version : 1.43
DBD Version : 1.15
Unfortunately, am not the SA so can't just install the latest module, i.e.
if the issue is version related. Got no test box to test this on either, so
am a bit stuff, hopefully someone from the group had tried this on.
If anyone have the latest DBI and DBD version and uses Oracle, can you
please try if connection by SYSDBA, without supplying username and password
works or not?
The example that I got from CPAN that am trying out which supposedly should
work is as below:
This one gives segmentation fault:
#!/usr/bin/perl
use DBI;
use DBD::Oracle qw(:ora_session_modes);
print $DBI::VERSION,"\n"; # this prints the DBI version
$dsn = "dbi:Oracle:"; # no dbname here
$ENV{ORACLE_SID} = "test"; # set ORACLE_SID as needed
delete $ENV{TWO_TASK}; # make sure TWO_TASK isn't set
$dbh = DBI->connect($dsn, "", "", { ora_session_mode => ORA_SYSDBA }); # no
need to supply username and password
my $sth = $dbh->prepare("alter session set nls_date_format = 'DD-MON-
HH24:MI:SS'");
$sth->execute();
my $sth = $dbh->prepare("select 'Today is ' || sysdate from dual");
$sth->execute();
while (my ($sysdate) = $sth->fetchrow_array()) {
print $sysdate, "\n";
}
$sth->finish();
exit 0;
FYI, connection by sqlplus "/as sysdba" works alright and am testing this
locally on the server where the Oracle database is installed. Oracle version
is 10g at the moment but I will be needing this script to work from Oracle9.
Using the following code below where I include a username and password, this
works alright, so that more or less confirm that the DBI module is alright.
I want to get the SYSDBA connection working so I do not have to specify the
username and password when the script is run.
#!/usr/bin/perl
use DBI;
$dbh = DBI->connect('dbi:Oracle:host=localhost;sid=test;port=1521',
'system', 'correct_pass');
my $sth = $dbh->prepare("alter session set nls_date_format = 'DD-MON-
HH24:MI:SS'");
$sth->execute();
my $sth = $dbh->prepare("select 'Today is ' || sysdate from dual");
$sth->execute();
while (my ($sysdate) = $sth->fetchrow_array()) {
print $sysdate, "\n";
}
$sth->finish();
exit 0;
Basically, if possible, I just want to confirm from any Oracle DBI experts
that it is possible to connect using SYSDBA without suppling a username and
password.
BTW, forgive my ignorance, when do I need to use and not to use DBD. If you
look at the example above, the second set of code where I supply the
username and password does not require use DBD. Does that mean I need to use
DBD only if it is database specific, on this instance, Oracle?
Any feedback will be very much appreciated
Re: Perl dbi - Microsoft Sql Server DRIVER
From: "Paul Kraus" <[EMAIL PROTECTED]> > I can't find anything on ppm for dbd microsoft sql server. > > Dbi can communicate with a MsSql server correct? > > If so where and how do I get the driver. Usualy you use DBD::ODBC. If you are not running the code under Windows you may want to try DBD::Sybase (several years ago Microsoft bought a licence to Sybase and used it as the basis of it's MS SQL Server, the Sybase driver should still be able to communicate with MS SQL.) Anyway if that's what you need I'm sure someone more knowledgeable will give you more details. I am using Windows. Jenda = [EMAIL PROTECTED] === http://Jenda.Krynicky.cz = When it comes to wine, women and song, wizards are allowed to get drunk and croon as much as they like. -- Terry Pratchett in Sourcery -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Perl dbi - Microsoft Sql Server DRIVER
Jenda Krynicky wrote: > From: "Paul Kraus" <[EMAIL PROTECTED]> > > I can't find anything on ppm for dbd microsoft sql server. > > > > Dbi can communicate with a MsSql server correct? > > > > If so where and how do I get the driver. > > Usualy you use DBD::ODBC. If you are not running the code under > Windows you may want to try DBD::Sybase (several years ago Microsoft > bought a licence to Sybase and used it as the basis of it's MS SQL > Server, the Sybase driver should still be able to communicate with MS > SQL.) > > Anyway if that's what you need I'm sure someone more knowledgeable > will give you more details. I am using Windows. > > Jenda I'll second that Microsoft DB products have moved strongly away from direct programatic interfaces. I haven't really used SQL server, but since Office 2000, Access can only be accessed through a DSN. Joseph -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Perl/DBI newbie: password storage / security question
Hello! This is probably something trivial, in which case I apologize, however I'm quite new in Perl and even more so in databases. Actually, I'm quite new in programing at all, so please don't laugh at me. I'm basically concerned about the security of writing a simple web frontend to SQL database, the most important question being: how should I store the database password? Every example I have found so far about using DBI or Class:DBI or anything includes the password as a cleartext in the source code. I realise that it's just for the sake of code examples simplicity and anyone had to be insane to introduce such a serious vulnerability in a production system, but frankly I'm not quite sure how to do it right. Should I make my CGI scripts not directly readable/executable to the httpd processes and write a suid wrapper which would then run the script itself including the password in the source code or reading it from a filesystem? What about the database itself access privilages? Should I use different accounts for different scripts, depanding on what access do they need to have? The most obvious solution seems to be something like this: For example, there's a news website. Everyone can read news, but only admin can add/modify them. So there need to be two system user accounts, e.g. newsadmin and newsguest, with the same accounts in the database having respectively read/write and read only (of course I'm simplifying, it whould be select privilage, insert, etc.) and two CGI scripts with an appropriate suid wrapper each. The problem is that having many indepandant websites on the same server can cause a need to have quite a lot of OS and DB accounts, so I'm asking if this is indeed the way to go, or maybe I'm missing something? So I'd like to ask all of you experienced Perl developers: How do you solve the problem of database security in production environments yourself, especially when the website is not the only one on the system and not the only one using a given database? I'm sorry if I'm asking a stupid question maybe missing an obvious answer, but I'm just beginning my first Perl/database project and everything I have are some terrible examples left by someone working here before, who was obviously completely security-wise ignorant and now I have to clean all of this mess he left here. There are tens of websites on the server and every database access is done with an account having full (sic) access to all of the tables and - if that wasn't enough - the password is in the clear in the sourcecode which is all world readable! No wonder why the previous admin was fired. I'd be grateful if anyone could point me to some documentation which I failed to find or just say few simple tips. Thanks a lot! (Should I post it to [EMAIL PROTECTED] It's indeed a problem with CGI scripts, but the problem itself is not stricte CGI-related, so I'm not quite sure.) Thanks! -Zedgar Z. -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: help on perl dbi and dbd:mysql
if you're on windows, your OS shipped without a c-compiler... for that reason, activestate (www.activestate.com) use 'ppm' - the perl package manager.. which means they already built binaries of the modules you're looking for. i suggest you check that out, it will make your life a LOT easier if you run windows =) hth Jos Boumans - Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, August 12, 2001 8:10 PM Subject: help on perl dbi and dbd:mysql > I am beginner in perl. i needed to connect the perl programs to mysql > database. Therefore I downloaded dbi and dbd:mysql (actually it was > mysql-msql modules). I am finding it difficult to compile and run both dbi > and dbd:mysql module. By the way I am using Windows 98. > > When I tried out dbi > --- > i.e first compiling "makefile.pl" and then make, make test and install (as > said in the faq documentation) the following error message appears > > C:\DBI-1.18>make > MAKE Version 5.0 Copyright (c) 1987, 1997 Borland International > Error makefile 150: Colon expected > Error makefile 797: Redefinition of target 'DBI.c' > Fatal makefile 893: No terminator specified for in-line file operator > > --error > When I tried out dbd::mssql > --- > > When the makefile.pl has been compiled there are a few questions to be > answered. > In one of the questions it asks for the path of 'include' directory. but > there wasn't a include dir in this mysql-msql module. What does this > include dir mean. The default gives "local/usr/hughs" > > > > > > > > > > -- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > > -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Perl DBI Oracle: bind an array list
Hi,
I am using bind variables for executing a select query which uses "in"
operator. Following is the prototype of my code:
$query = "select a from tab where b in (:1)";
@list = {"A","B","C","D"};
$list_values = join("','",@list);
$DBI-> connect (..);
$sth -> prepare($query);
$sth-> execute ($list_values);
$array_ref = $sth->fetchrow_arrayref
This does not return me any values.
I suspect the issue is with the usage of passing up the comma separated
values as the parameter to the execute function.
Thanks,
Nitin
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/
Accessing array from Perl & DBI using Template Toolkit
Hi, I'm trying to pickup CGI web application programming using Perl, DBI &
TT (Template Toolkit). This small application is about storing data (using
CGI::FormBuilder) list it using Template Toolkit. After retrieving records
from MySQL using DBI, I've problem understanding how to pass to Template
Toolkit, even though I've managed make it work by copy some code from Perl
Mongers & Template Toolkit's tutorial. Below are my codes :
>>> start of list.cgi <<<
#!/usr/bin/perl
use warnings;
use strict;
#use diagnostics;
use Template;
use DBI;
my %kar;
my $tt_file='list.tt';
my $tt = Template->new({
INCLUDE_PATH => './tt',
}) || die "$Template::ERROR\n";
my $vars = {
kars=> \%kar,
};
sub db_select {
my ($dsn,$dbuser,$dbpass,$dbh,$plh_reg_no,$sth,$reg_no,$brand,$cc);
$dsn = "dbi:mysql:database=mycars:hostname=127.0.0.1";
$dbuser = "driver";
$dbpass = "plaintext";
$dbh = DBI->connect($dsn,$dbuser,$dbpass)
or die "cannot connect to database : $DBI::errstr";
$plh_reg_no = "car%";
$sth = $dbh->prepare("SELECT reg_no,brand,CC FROM auto_details WHERE
reg_no like ?");
$sth->execute($plh_reg_no)
or die "Cannot execute statement : $DBI::errstr";
while (($reg_no, $brand, $cc) = $sth->fetchrow_array()) {
$kar{$reg_no} = {
reg_no => $reg_no,
brand => $brand,
cc => $cc
}
}
$dbh->disconnect();
}
db_select();
$tt->process($tt_file, $vars)
|| die "Template process failed: ", $tt->error(), "\n";
>>> end of list.cgi <<<
>>> start of list.tt <<<
Content-type: text/html
[% PROCESS header %]
Form List
[% FOREACH kar IN kars.values %]
[% kar.reg_no %], [% kar.brand %], [% kar.cc %]
[% END %]
[% PROCESS footer %]
>>> end of list.tt <<<
My question :
- I suppose the above code convert array into hash and pass it to TT, is
there any other way then this because I find that the line noise is a bit
too much.
- I've read about some where on some website that arrays passing to TT
should always use array reference ($sth->fetchrow_arrayref) instead of the
above, using arrays ($sth->fetchrow_array). If this were to use array
reference, can someone show some codes to me so that i may check it out and
try to understand.
Thanks in advance,
Edward.
Re: Perl/DBI newbie: password storage / security question
I'm new to databasing too, and decided to put this question off till I got
the basics down first.
My beginners solution was to put the database info in a txt file in my
cgi-bin and read the variables into the script. I also set the variables for the
info with this code, which I read hides the text on the server side:
$pkg::dwd = "";#FOR -w
$pkg::dnm = "";
$pkg::dun = "";
require "../lib/confvars.pl";
local $ENV{'DBI_DRIVER'} = "mysql";
local $ENV{'DBI_DSN'} = $pkg::dnm;
local $ENV{'DBI_USER'} = $pkg::dun;
local $ENV{'DBI_PASS'} = $pkg::dwd;
The files that only read the database and available to the public are in
unprotected directories without htaccess files.
Then use apache htaccess files to protect the directory that has scripts
which change the database.
I would like to learn about allowing only certain scripts access. If anyone
has a tutorial.
Re: Perl/DBI newbie: password storage / security question
[EMAIL PROTECTED] wrote: > Hello! > > This is probably something trivial, in which case I apologize, however I'm quite new > in Perl and even more so in databases. Actually, I'm quite new in programing at all, > so please don't laugh at me. > > I'm basically concerned about the security of writing a simple web frontend to SQL > database, the most important question being: how should I store the database > password? > > Every example I have found so far about using DBI or Class:DBI or anything includes > the password as a cleartext in the source code. I realise that it's just for the > sake of code examples simplicity and anyone had to be insane to introduce such a > serious vulnerability in a production system, but frankly I'm not quite sure how to > do it right. > > Should I make my CGI scripts not directly readable/executable to the httpd processes > and write a suid wrapper which would then run the script itself including the > password in the source code or reading it from a filesystem? > > What about the database itself access privilages? Should I use different accounts > for different scripts, depanding on what access do they need to have? > > The most obvious solution seems to be something like this: For example, there's a > news website. Everyone can read news, but only admin can add/modify them. So there > need to be two system user accounts, e.g. newsadmin and newsguest, with the same > accounts in the database having respectively read/write and read only (of course I'm > simplifying, it whould be select privilage, insert, etc.) and two CGI scripts with > an appropriate suid wrapper each. > > The problem is that having many indepandant websites on the same server can cause a > need to have quite a lot of OS and DB accounts, so I'm asking if this is indeed the > way to go, or maybe I'm missing something? > > So I'd like to ask all of you experienced Perl developers: How do you solve the > problem of database security in production environments yourself, especially when > the website is not the only one on the system and not the only one using a given > database? > > I'm sorry if I'm asking a stupid question maybe missing an obvious answer, but I'm > just beginning my first Perl/database project and everything I have are some > terrible examples left by someone working here before, who was obviously completely > security-wise ignorant and now I have to clean all of this mess he left here. > > There are tens of websites on the server and every database access is done with an > account having full (sic) access to all of the tables and - if that wasn't enough - > the password is in the clear in the sourcecode which is all world readable! No > wonder why the previous admin was fired. > > I'd be grateful if anyone could point me to some documentation which I failed to > find or just say few simple tips. Thanks a lot! > > (Should I post it to [EMAIL PROTECTED] It's indeed a problem with CGI scripts, but > the problem itself is not stricte CGI-related, so I'm not quite sure.) > > Thanks! > -Zedgar Z. Although only a slight improvement, it can help to shift the database out of the cgi directory. On the server I use, the web server runs as part of the group. There fore if a side directory has chmod 660, the server can access it, but the world can not. Unfortunately others considered to be part of the same group can also read it. It really depends then on the granularity of the pemissions system on the server. The strucure would be like Home db_adm public_html cgi-bin Filepaths from the script should then be based on ../../db_adm/. Keeping the paths relative helps keep the code portable. Joseph -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Perl/DBI newbie: password storage / security question
R. Joseph Newton wrote: > [EMAIL PROTECTED] wrote: > >>I'm basically concerned about the security of writing a simple web frontend to SQL >>database, the most important question being: how should I store the database >>password? >>[...] >>There are tens of websites on the server and every database access is done with an >>account having full (sic) access to all of the tables and - if that wasn't enough - >>the password is in the clear in the sourcecode which is all world readable! No >>wonder why the previous admin was fired. > > Although only a slight improvement, it can help to shift the database out of the cgi > directory. On the server I use, the web server runs as part of the group. There > fore if a side directory has chmod 660, the server can access it, but the world can > not. Unfortunately others considered to be part of the same group can also read it. Thank you Joseph and Motherofperls for your tips, however I need something more than security through obscurity, as this database is going to store our customers personal information (real name and contact information) which is absolutely unacceptable to be stored in such an insecure manner (we cannot risk being sued or loosing our customers and of course I most definitely cannot knowingly introduce such a serious vulnerability being responsible for that website). I cannot depand on attackers not finding the database password which is stored as cleartext in a world-readable file while its path is included in the script source (or even if it wasn't included anywhere, for that matter). Even if it is group-readable for a group which the httpd process belongs to, it is actually not any more secure and only adds one simple step for attacker to access the file with a CGI script by exploiting any script from any website on the server or using any user account which can modify any one of those webites, so I'd say it is basically world-readable on a server where I am not the only one who has a website. What I need is a secure way of doing it and I'd like to know how the experienced Perl developers solve this common problem. It'd be somehow hard to believe that people actually store sensitive data in production environments in such a way which allows full access using the most trivial web attacks and even without any need of attack at all for everyone with a website on this server, even without the shell access. I hope someone who has developed any real production system could answer my question or even just tell me to RTFM while kindly pointing me to the right FM, because to my great surprise I couldn't find anything in perlfaq and any other Perl documentation or books I've read. Thanks a lot. -Zedgar Z. -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Perl/DBI newbie: password storage / security question
[EMAIL PROTECTED] wrote: > Thank you Joseph and Motherofperls for your tips, however I need something more than > security through obscurity, as this database is going to store our customers > personal information (real name and contact information) which is absolutely > unacceptable to be stored in such an insecure manner (we cannot risk being sued or > loosing our customers and of course I most definitely cannot knowingly introduce > such a serious vulnerability being responsible for that website). > > I cannot depand on attackers not finding the database password which is stored as > cleartext in a world-readable file while its path is included in the script source > (or even if it wasn't included anywhere, for that matter). > > Even if it is group-readable for a group which the httpd process belongs to, it is > actually not any more secure and only adds one simple step for attacker to access > the file with a CGI script by exploiting any script from any website on the server > or using any user account which can modify any one of those webites, so I'd say it > is basically world-readable on a server where I am not the only one who has a > website. > > What I need is a secure way of doing it and I'd like to know how the experienced > Perl developers solve this common problem. It'd be somehow hard to believe that > people actually store sensitive data in production environments in such a way which > allows full access using the most trivial web attacks and even without any need of > attack at all for everyone with a website on this server, even without the shell > access. > > I hope someone who has developed any real production system could answer my question > or even just tell me to RTFM while kindly pointing me to the right FM, because to my > great surprise I couldn't find anything in perlfaq and any other Perl documentation > or books I've read. > > Thanks a lot. > -Zedgar Z. Okay. In short, you probably can't do it all in Perl. Somewhere you have to have a compiled object holding your hashing algorithm. Any plian-text algorithm can be cracked. Ultimately, anything can be cracked. Here is the logic for your algorithm: Pne way: Have some large and obscure prime number for a key. Bury this number, offset by a few bytes, deep in a file of garbage. For each character of text to be protected, pick a random number and multiply the key by this number. Do something tweaky, but reversible with the character [perhaps map it through a simple translation table] Add the characters tweaked value to the prime/random product. Store this, offset by a different number of bits than used for the prime key, in some other file of garbage, or in a diffeent offset in the same. To decrypt, get the stored value[ each character probaly stored as some fixed-length, but large integer data.] Take the moduls of each integer value and untweak it to get the original character. The problem of the above is that the password gets decrypted. Another way would not ever require decryption. First tweak all characters, say through a substitution map. Then apply some hashing pattern to the string as a whole. A very simple one would be to multiply a running sum by some number greater than the size of the set of allowable password characters then the tweaked ordinal of the next character. Store the resulting number as the hashed value. Never try to decrypt the stored value. Simply hash each string offered in the same manner, and check to see if it renders the same value. Those are two hand-hacked approaches. Either would make a crackers work difficult. Neither would make it impossible. Another possibility, since this sounds like it involves some money, is to simply buy a third-party RSA encryption application, and have your scripts call this to verify passwords. Joseph -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Perl/DBI newbie: password storage / security question
Dear Zedgar, I'm not completely sure I understand your goals, question and setup, but, if you are concerned about security, using a relational database is almost if not certainly your best bet. I am working on just such a system right now for myself, so I can appreciate your question. I'll give you some input as best I can. 1.) The usual argument against flat file db's goes something like as follows. Flat file db's are usually more hackable and difficult to handle if they get large. If you are running Apache, but your server isnt running suEXEC, then you would have to chmod your files to some world writeable file. You could "hide" this world writeable file to promote security through obscurity, but I would only trust that to a certain extent. If you server does have suEXEC, however, then you can chmod the access permissions of your writeable flat files to a user group under which your cgi scripts would only have permission. But I still wouldnt use flat files because they are difficult to manage if they get large and need to be changed structurally. 2.) Relational DB's will require a login routine to connect, and this will have to be embedded in your scripts. The downside here is that it opens another port of your server, but the port can be limited much like user groups on Apache. For example, I cant use an FTP-like interface to connect to my DB remotely, as my hosting service requires I go through localhost first. As you can see, the SERVER's configuration often helps secure the DB too. Additionally, relational DB's themselves have varying levels of security. 3.) If you wanted to be really secure about things, then you could https the connection. Setting up a secured server would help to counteract sniffers, though it might slow things down a bit. 4.) The skill of the programmer and the scripts themselves can play a big role. A lot of people feel using CGI is like laying out a welcome mat to trouble. But you can use different modules and drivers that have been proven to help security, as well as build in validation features to the script. For instance, I'm not a great programmer, but I know enough to use CGI.pm to parse my input params rather than doing them myself. If you want to run sessions, like you say, then look into CGI::Session for secured login routines. There are many DB drivers you could use to connect to the database, and they each have different features. I use DBI, for instance, and it has the ability to do multiple logins to different databases. If you use your imagination, then the ability to login to multiple DB's could be a big asset to security. 5.) Change your database username and password frequently. This may sound difficult if you have a lot of scripts, but it's not so hard if you build a module to handle the connection string. Then all you have to do is change the username and password in the module, and, when the other scripts call it, they will work just fine. The worst hackers are those you dont know about. This way if someone got in undetected, then they would have to relearn how to get in again. 6.) Backup all your files. Write a perl/SQL script under a cron job to do a data dump of the entire db every so often. 7.) Work with your hosting service on solutions, as well as your client on their budget. Maybe you could set up a second server that would run a cron job to execute a perl script that would pull data from the first database and set it into a second database. In this way the first data server would only act as a proxy to the second data server, and, if someone hacked the first dataserver, then it would hold an incomplete list of information. It would also backup your files into a "larger pool." The problem then becomes how to get the data back to the front end when the end user needs it. Since there are many different ways to that, I'll leave it to you to choose what is the best way to go for your setup... Like I said, a lot of security is server dependent, and there are ways you could setup your system from a network standpoint. Anyway, I'll leave go now... just thinking out loud... providing some food for thought... etc... Good luck! quint From: [EMAIL PROTECTED] To: [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: Perl/DBI newbie: password storage / security question Date: Sat, 13 Sep 2003 21:25:55 -0500 (EST) R. Joseph Newton wrote: > [EMAIL PROTECTED] wrote: > >>I'm basically concerned about the security of writing a simple web frontend to SQL database, the most important question being: how should I store the database password? >>[...] >>There are tens of websites on the server and every database access is done with an account having full (sic) access to all of the tables and - if that wasn't enough - the password is in the clear in t
Re: Perl/DBI newbie: password storage / security question
Zedgar, I have had the opportunity to do what you are attempting in several different ways. 1. Hack Perl, 2. Provide a password access module 1. is not so pretty. Basically, we allocated a global database handle in the perl main. Using SWIG, we then create the database handle in C and using the C module retrieve the password from a password server. (another database). When the handle is passed back to Perl, we store it in our global variable. This is the worst hack in the world but it works. Currently we are attempting to get away from this method as it locked us down to particular hacked version of perl. 2. This is the current method that we are migrating toward. Subclassed DBI and overrode the connect method. Now if my DBI is called without a password, the password retrieval mechanism is invoked and supplies the password. More of an obscuring mechanism, but we do not have web servers calling directly to dbs, so the issue of clear text password in the subclassed DBI module is not so much of a concern for us. Like most places there are 9 layers of security to get through before you can try to run perl code on a production host. HTH, Chuck Fox Principal DBA America Online, INC. [EMAIL PROTECTED] wrote: Hello! This is probably something trivial, in which case I apologize, however I'm quite new in Perl and even more so in databases. Actually, I'm quite new in programing at all, so please don't laugh at me. I'm basically concerned about the security of writing a simple web frontend to SQL database, the most important question being: how should I store the database password? Every example I have found so far about using DBI or Class:DBI or anything includes the password as a cleartext in the source code. I realise that it's just for the sake of code examples simplicity and anyone had to be insane to introduce such a serious vulnerability in a production system, but frankly I'm not quite sure how to do it right. Should I make my CGI scripts not directly readable/executable to the httpd processes and write a suid wrapper which would then run the script itself including the password in the source code or reading it from a filesystem? What about the database itself access privilages? Should I use different accounts for different scripts, depanding on what access do they need to have? The most obvious solution seems to be something like this: For example, there's a news website. Everyone can read news, but only admin can add/modify them. So there need to be two system user accounts, e.g. newsadmin and newsguest, with the same accounts in the database having respectively read/write and read only (of course I'm simplifying, it whould be select privilage, insert, etc.) and two CGI scripts with an appropriate suid wrapper each. The problem is that having many indepandant websites on the same server can cause a need to have quite a lot of OS and DB accounts, so I'm asking if this is indeed the way to go, or maybe I'm missing something? So I'd like to ask all of you experienced Perl developers: How do you solve the problem of database security in production environments yourself, especially when the website is not the only one on the system and not the only one using a given database? I'm sorry if I'm asking a stupid question maybe missing an obvious answer, but I'm just beginning my first Perl/database project and everything I have are some terrible examples left by someone working here before, who was obviously completely security-wise ignorant and now I have to clean all of this mess he left here. There are tens of websites on the server and every database access is done with an account having full (sic) access to all of the tables and - if that wasn't enough - the password is in the clear in the sourcecode which is all world readable! No wonder why the previous admin was fired. I'd be grateful if anyone could point me to some documentation which I failed to find or just say few simple tips. Thanks a lot! (Should I post it to [EMAIL PROTECTED] It's indeed a problem with CGI scripts, but the problem itself is not stricte CGI-related, so I'm not quite sure.) Thanks! -Zedgar Z. -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Problem in fetching CLOB data using perl DBI
Hi ,
I have perl script to fetch CLOB data from oracle table
Table structure is
ID number ( 5)
long_field clob
Perl code :
#!/usr/local/bin/perl5
use DBI qw(:sql_types);
$dbh= DBI->connect ( . );
$dbh->{LongReadLen}=512*1024;
$sql="select * from table";
$sth = $dbh->prepare($sql);
$sth->execute();
while ( ($id , $edi_data ) = $sth->fetchrow ) {
print "id=$id\n";
print "data=$edi_data\n";
}
When I try to fetch data I get error like
ORA-03115: unsupported network datatype or representation (DBD: odescr failed)
Can anyone suggest why I am geting this error and what is the resolution.
thanks
Paresh.
Re: Perl DBI Oracle: bind an array list
Hi Nitin,
2007/2/16, Nitin Aggarwal <[EMAIL PROTECTED]>:
Hi,
I am using bind variables for executing a select query which uses "in"
operator. Following is the prototype of my code:
$query = "select a from tab where b in (:1)";
@list = {"A","B","C","D"};
$list_values = join("','",@list);
$DBI-> connect (..);
$sth -> prepare($query);
$sth-> execute ($list_values);
$array_ref = $sth->fetchrow_arrayref
This does not return me any values.
I suspect the issue is with the usage of passing up the comma separated
values as the parameter to the execute function.
From DBI man page:
Also, placeholders can only represent single scalar values. For exam-
ple, the following statement won't work as expected for more than one
value:
"SELECT name, age FROM people WHERE name IN (?)"# wrong
"SELECT name, age FROM people WHERE name IN (?,?)" # two names
I suggest you to check how many items @list has, and construct your
query according that. For example:
@list = qw(A B C D);
$query =
"select a from tab where b in (" . join( ",", map { "?" } @list ) . ")";
print $query, "\n";
Then you can do this:
$sth->prepare($query);
$sth->execute (@list);
HTH!
--
Igor Sutton Lopes <[EMAIL PROTECTED]>
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/
Re: Accessing array from Perl & DBI using Template Toolkit
On 9 August 2010 13:25, me @ wrote:
> Hi,
Hi,
Not sure why you had to send this twice.
I'm trying to pickup CGI web application programming using Perl, DBI &
...
...
>>>> start of list.tt <<<
> Content-type: text/html
>
>
> [% PROCESS header %]
> Form List
>
>
> [% FOREACH kar IN kars.values %]
> [% kar.reg_no %], [% kar.brand %], [% kar.cc %]
> [% END %]
>
>
> [% PROCESS footer %]
>>>> end of list.tt <<<
>
> My question :
> - I suppose the above code convert array into hash and pass it to TT, is
> there any other way then this because I find that the line noise is a bit
> too much.
Line noise? What is it you want to cut out?
You are iterating over the values of your hash. You don't need to
specify values here, you could omit the values and, according to the
TT man page[1] do:
[% FOREACH kars %]
[% reg_no %]
[% END %]
> - I've read about some where on some website that arrays passing to TT
> should always use array reference ($sth->fetchrow_arrayref) instead of the
> above, using arrays ($sth->fetchrow_array). If this were to use array
> reference, can someone show some codes to me so that i may check it out and
> try to understand.
I think you might be getting a bit confused here. TT requires
references for it's parameters variable, so yes array, hash references
(or any reference that returns a list) but that is different from how
you retrieve data from the DBI. You turned that data into a hash of
hash references when you did this:
while (($reg_no, $brand, $cc) = $sth->fetchrow_array()) {
$kar{$reg_no} = {
reg_no => $reg_no,
brand => $brand,
cc => $cc
}
}
If you want to use the DBI directly within TT then you should look at
using the TT DBI plugin[2]. you may get more help on TT matter from
the TT list[3].
Good luck,
Dp.
[1] http://template-toolkit.org/docs/manual/Directives.html#section_FOREACH
[2] http://search.cpan.org/~rehsack/Template-DBI-2.65/lib/Template/DBI.pod
[3] http://mail.template-toolkit.org/mailman/listinfo/templates
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
Re: Accessing array from Perl & DBI using Template Toolkit
Hi Dermot, On 10/08/10 4:49, Dermot wrote: snip > Hi, > > Not sure why you had to send this twice. Oops, sorry about it, to everybody. The first email (my time shows 8pm ++) was sent but it didn't turn up in my email client (on this mailing list, beginners@perl.org) and my hosting's webmail interface. After waited for 2 hours, then I sent again the second email (10 pm ++) and both turn up. I'm not sure why is it so. snip > Line noise? What is it you want to cut out? > > You are iterating over the values of your hash. You don't need to > specify values here, you could omit the values and, according to the > TT man page[1] do: > > [% FOREACH kars %] > [% reg_no %] > [% END %] > I've tried this and it doesn't work. But I think I'm quite satisfy with the current way of writing it as it kind of explain the hash relationship. e.g. [% PROCESS header %] Form List [% FOREACH kar IN kars.values %] [% kar.reg_no %], [% kar.brand %], [% kar.cc %] [% END %] [% PROCESS footer %] snip > I think you might be getting a bit confused here. TT requires > references for it's parameters variable, so yes array, hash references > (or any reference that returns a list) but that is different from how > you retrieve data from the DBI. You turned that data into a hash of > hash references when you did this: > You're absolutely correct about I'm confused. I can't differentiate what's array & array references, hash & hash references. And I thought all array are list. At one point when I try to pass multiple row of array (query from DBI using $sth->fetchrow_array) from perl to TT, it some how does not work. Until I stumble on this working codes from Perl Mongers and turn it into hash, then only it works. I was hoping someone over here can show some codes on how to use $sth->fetchrow_arrayref to pass arrays (or array references?) to TT. snip > If you want to use the DBI directly within TT then you should look at > using the TT DBI plugin[2]. you may get more help on TT matter from > the TT list[3]. > Not going to use DBI within TT as my intention for TT is to solely deal with design issue and keep the codes within CGI. Hopefully this will isolate problems (if any) and ease my learning picking up web programming. :) > Good luck, > Dp. > > > [1] http://template-toolkit.org/docs/manual/Directives.html#section_FOREACH > [2] http://search.cpan.org/~rehsack/Template-DBI-2.65/lib/Template/DBI.pod > [3] http://mail.template-toolkit.org/mailman/listinfo/templates > I'll definitely check out the TT's mailing list. Thanks for the explanation & help, Edward. -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
Re: Accessing array from Perl & DBI using Template Toolkit
On 2010.08.11 00:15, MySelf rdtan.net wrote:
> On 10/08/10 4:49, Dermot wrote:
>> I think you might be getting a bit confused here. TT requires
>> references for it's parameters variable, so yes array, hash references
>> (or any reference that returns a list) but that is different from how
>> you retrieve data from the DBI. You turned that data into a hash of
>> hash references when you did this:
>>
> You're absolutely correct about I'm confused. I can't differentiate
> what's array & array references, hash & hash references.
Understanding references is imho one of the most important aspects of
being able to use Perl effectively, and minimize headache.
> At one point when I try to pass multiple row of array (query from DBI
> using $sth->fetchrow_array) from perl to TT, it some how does not
> work.
That's because iirc TT accepts a hashref as the data, and you must stuff
your arrayrefs within it.
Here is an example I've cobbled together quickly (using an email template):
my ( @notices, @renewals );
if ( $blah ) {
# push a new (anonymous) hash reference onto the array
push @notices, { username => $username, hours => $hours_balance, };
}
else {
push @renewals, { username => $username, hours => $hours_balance, };
}
# create a hash for the template data, and insert
# a reference to both of the arrays within it
my %tmpl_data;
$tmpl_data{ notices }= \...@notices;
$tmpl_data{ renewals } = \...@renewals;
my $msg = MIME::Lite::TT->new(
From=> $from,
To => $to,
Subject => $subject,
Template=> $tmpl,
TmplParams => \%tmpl_data, # create ref of hash
);
__END__
The data structure looks like this when printed with Dumper. It is a
hash reference that has two keys, who's values contain an array
reference. Each of the two array references point to an array that
contains a hash reference as each element. The data within these hrefs
are what are used in the foreach loops within the template itself.
Note that the below output contains two loops. I refer to this output
whenever I find myself having an issue generating output from within a
template loop:
acct-dev: ISP % sudo ./b.pl
$VAR1 = {
'renewals' => [
{
'hours' => '8.84 hours remaining',
'username' => 'xxx'
},
{
'hours' => '5.51 hours remaining',
'username' => 'xxx'
},
],
'notices' => [
{
'hours' => '13.17 hours remaining',
'username' => 'xxx'
},
{
'hours' => '10 hours remaining',
'username' => 'xxx'
},
]
};
__END__
The resulting template itself:
Clients sent Notices:
[% FOREACH item IN notices %]
[%item.username%]: [%item.hours%]
[% END %]
Clients sent Renewals:
[% FOREACH item IN renewals %]
[%item.username%]: [%item.hours%]
[% END %]
> Until I stumble on this working codes from Perl Mongers and turn
> it into hash, then only it works. I was hoping someone over here can
> show some codes on how to use $sth->fetchrow_arrayref to pass arrays
> (or array references?) to TT.
I believe what you want to do is fetch the data from the db as a hashref
instead, and push the rows into an array to generate your loop topology.
untested... I haven't used DBI in quite a long time:
my @loop_array;
push @loop_array, $sth->fetchrow_hashref();
my %top_level_tmpl_data;
$top_level_tmpl_data{ db_data } = \...@loop_array;
Then, you can either create an explicit reference to the top level hash
like this:
my $tmpl_data_ref = \%top_level_tmpl_data;
...or just skip that step and create the ref as you pass the data into TT.
I hope this is what you were after. Note that I prefer to use references
directly as opposed to creating array and hash first, but I thought that
it would be better in this case if I explicitly did things the long way.
Cheers,
Steve
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
Re: Accessing array from Perl & DBI using Template Toolkit
Hi Steve,
> Understanding references is imho one of the most important aspects of
> being able to use Perl effectively, and minimize headache.
>
(stuffing my head into the Llama book)
> That's because iirc TT accepts a hashref as the data, and you must stuff
> your arrayrefs within it.
>
> Here is an example I've cobbled together quickly (using an email template):
snip
> I believe what you want to do is fetch the data from the db as a hashref
> instead, and push the rows into an array to generate your loop topology.
> untested... I haven't used DBI in quite a long time:
>
> my @loop_array;
>
> push @loop_array, $sth->fetchrow_hashref();
>
> my %top_level_tmpl_data;
>
> $top_level_tmpl_data{ db_data } = \...@loop_array;
>
> Then, you can either create an explicit reference to the top level hash
> like this:
>
> my $tmpl_data_ref = \%top_level_tmpl_data;
>
> ...or just skip that step and create the ref as you pass the data into TT.
>
> I hope this is what you were after. Note that I prefer to use references
> directly as opposed to creating array and hash first, but I thought that
> it would be better in this case if I explicitly did things the long way.
>
Hmm, this code look very much like what I want to do with it. I'll
definitely spend some time on it.
Thanks for the code examples & suggestion, this definitely shed some
light onto my question. Thanks!
Regards,
Edward.
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
Perl DBI upload a csv file to Access database
I try to upload a csv file to access database using perl DBI. But keep
failing.
The* number* and *type* of the field in csv file are not fixed. Might be
integer or character.
( I was able to make it if they are fixed)
---start of code
#connect to access database
*$dbh = DBI->connect("dbi:ODBC:$DSN", {
PrintError => 0, RaiseError => 1*
*}) or die "Can't connect to the database: $DBI::errstr\\n";*
#Create a table
* $sql = qq{ CREATE TABLE $table_name (id INTEGER NOT NULL, name
VARCHAR(64))}; **<=* What if I don't know the *type* and* name* and *
number* before I upload?
* $sth = $dbh ->prepare ($sql );
$sth -> execute;*
#Go through csv file and insert each row to table
* open( CSV, "<", $csv_file ) or die $!;*
* *
* foreach my $line () {
chomp $line ;
my ( $field1, $field2) = split( /,/, $line );*
*$sql = qq{ INSERT INTO $table_name values("$
field1","$field2") };* <= What if I don't know the *type* and* name* and *
number* before I upload? Also, this code doesn't work because
$field1,$field2 are variable. If I don't use variable, it works.
*$sth = $dbh ->prepare ($sql );
$sth -> execute;*
* }*
#disconnect databse
* $dbh->disconnect();*
---end of code
**
Could anyone show me how to modify my code or show me a template that can
upload an unknown format csv file into Access database using Perl DBI?
Thank you!
-Tiffany
RE: Problem in fetching CLOB data using perl DBI
I use the oracle driver to retrieve data from my database. You should download and
install it along with DBI.
#!/usr/local/bin/perl
use strict;
use DBI;
my $statement = "SELECT * from table";
my $dbh = DBI->connect('dbi:Oracle:host=hostname;sid=mysid','username','password') or
die "could not co
nnect to database: ".DBI->errstr;
my $sth = $dbh->prepare($statement);
my $rv = $sth->execute;
while (my @row = $sth->fetchrow_array) {
print "@row\n";
}
my $rc = $dbh->disconnect;
-Original Message-
From: Paresh Kakrecha [mailto:[EMAIL PROTECTED]]
Sent: Friday, July 12, 2002 3:14 PM
To: [EMAIL PROTECTED]
Subject: Problem in fetching CLOB data using perl DBI
Hi ,
I have perl script to fetch CLOB data from oracle table
Table structure is
ID number ( 5)
long_field clob
Perl code :
#!/usr/local/bin/perl5
use DBI qw(:sql_types);
$dbh= DBI->connect ( . );
$dbh->{LongReadLen}=512*1024;
$sql="select * from table";
$sth = $dbh->prepare($sql);
$sth->execute();
while ( ($id , $edi_data ) = $sth->fetchrow ) {
print "id=$id\n";
print "data=$edi_data\n";
}
When I try to fetch data I get error like
ORA-03115: unsupported network datatype or representation (DBD: odescr failed)
Can anyone suggest why I am geting this error and what is the resolution.
thanks
Paresh.
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Installing Perl DBI and Oracle DBD in AIX 5.1
Hi there, I have an AIX 5.1 machine and I wish to use the perl DBI and oracle DBD in this machine. I have tried to install them by downloading DBI-1.45.tar and DBD-Oracle-2.15.tar files from http://www.cpan.org/ So, I uncompressed the DBI-1.45.tar file and followed the README file instructions. First, I runned the folowing command: perl Makefile.PL this created the Makefile So, I tried to run the make command, but I got the following error: "cc_r -c-D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=16384 -qnoansialias -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -q32 -D_LARGE_FILES -qlonglong -O-DVERSION=\"1.45\" -DXS_VERSION=\"1.45\" "-I/usr/opt/perl5/lib/5.8.0/aix-thread-multi/CORE" Perl.c /bin/sh: cc_r: not found. make: 1254-004 The error code from the last command is 127." So, I changed the compiler from cc_r to cc, runned make again and got this error message: "cc -c-D_ALL_SOURCE -D_ANSI_C_SOURCE -D_POSIX_SOURCE -qmaxmem=16384 -qnoansialias -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -q32 -D_LARGE_FILES -qlonglong -O-DVERSION=\"1.45\" -DXS_VERSION=\"1.45\" "-I/usr/opt/perl5/lib/5.8.0/aix-thread-multi/CORE" Perl.c "/usr/opt/perl5/lib/5.8.0/aix-thread-multi/CORE/reentr.h", line 610.16: 1506-007 (S) "struct drand48_data" is undefined. "/usr/opt/perl5/lib/5.8.0/aix-thread-multi/CORE/reentr.h", line 717.16: 1506-007 (S) "struct random_data" is undefined. make: 1254-004 The error code from the last command is 1. Stop." Can somebody help me to get the job done? thanks in advance, Pablo Salinas _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] <http://learn.perl.org/> <http://learn.perl.org/first-response>
Re: [OT] Warnings from code when using perl-dbi
Hi
Firstly, for an excelent general perl list, try [EMAIL PROTECTED] It's not
just for beginners.
Secondly, it looks like you're trying to access a field that isn't defined,
possibly by running off the end of the row.
I've re-written (but not tested) the routine slightly more perlified.
See what you think.
while (my @row = $sth->fetchrow_array )
{
foreach my $field (shift @row) {
if (! defined ($field) || (length($field) == 0))
{
# don't know why you do this cos
# you're printing nothing here
print $field;
} else {
print "\"$field\"";
}
if (@row) # fields left, stick in a comma
{
print ",";
}
}
print ("\n");
}
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/
Re: [OT] Warnings from code when using perl-dbi
On Thu, 2007-08-16 at 10:38 +0100, Gary Stainburn wrote:
> Hi
>
> Firstly, for an excelent general perl list, try [EMAIL PROTECTED] It's not
> just for beginners.
Ah.. Cool.. I'll subscribe there.
>
> Secondly, it looks like you're trying to access a field that isn't defined,
> possibly by running off the end of the row.
>
> I've re-written (but not tested) the routine slightly more perlified.
>
> See what you think.
>
> while (my @row = $sth->fetchrow_array )
> {
> foreach my $field (shift @row) {
> if (! defined ($field) || (length($field) == 0))
> {
># don't know why you do this cos
># you're printing nothing here
> print $field;
> } else {
> print "\"$field\"";
> }
> if (@row) # fields left, stick in a comma
> {
> print ",";
> }
> }
> print ("\n");
> }
Thanks for the solution, however it doesn't really run. Not sure why.
There's a supposed missing brace/curly brace somewhere but I can't find
it yet. Your solution is more elegent. I will definitely try to figure
out where the error is..
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/
Hello, how to tell if I have perl DBI?
Hello, I have mysql installed as well as perl on our raq 3, I am at the mysql site for perl looking at dbi/dbd for mysql downloads and I was wondering how I can tell if I already have the perl dbi (Which dbi for ex?) Also, is the dbi for connections in general? See, I see 2 choices Perl dbi and *then* another choice is perl DBD which is for mysql. Does this mean I need both or just one? As I am only interested in using mysql with perl Thanks, I am very new to the concept of db connections with perl Joel
Perl DBI->Connect: how to detect a a lost connection
Hi: I have a script which connects to a database when it starts up
$dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$host;port=$port;","$username",
"$password", {AutoCommit => 1});
followed by a while loop which runs a query for this connection at 60 second
intervals. If the database goes down for some reason, I want the script to try
reconnecting to the database. How do I figure out within the while look if the
database connection is still valid. If tried using the $dbh variable (if
($dbh)then connection is fin. else connection is bad). That doesn't seem to
work. How do I figure out if the $dbh connection has been lost within the while
loop?
Any suggestions.
TIA
Ravi
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
http://learn.perl.org/
Re: Installing Perl DBI and Oracle DBD in AIX 5.1
> Hi there, > I have an AIX 5.1 machine and I wish to use > the perl DBI and oracle DBD in this machine. I have > tried to install them by downloading DBI-1.45.tar and > DBD-Oracle-2.15.tar files from http://www.cpan.org/ > This is a rather difficult combination and I know there has been lots of discussion about installing DBI on AIX and with Oracle on the dbi-users list, you may want to check the archives for it for help. http://www.mail-archive.com/[EMAIL PROTECTED]/ > So, I uncompressed the DBI-1.45.tar file and followed > the README file instructions. > First, I runned the folowing command: > perl Makefile.PL > > this created the Makefile > So, I tried to run the make command, but I got the > following error: > > "cc_r -c-D_ALL_SOURCE -D_ANSI_C_SOURCE > -D_POSIX_SOURCE -qmaxmem=16384 -qnoansialias > -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -q32 > -D_LARGE_FILES -qlonglong -O-DVERSION=\"1.45\" > -DXS_VERSION=\"1.45\" > "-I/usr/opt/perl5/lib/5.8.0/aix-thread-multi/CORE" > Perl.c > /bin/sh: cc_r: not found. > make: 1254-004 The error code from the last command is > 127." > > So, I changed the compiler from cc_r to cc, runned > make again and got this error message: > I am assuming the installer pulled cc_r from the config for Perl, check it with perl -V. Modules generally have to be built with the same compiler used to build Perl which is why you would get this second set of errors. Is 'cc_r' not installed on the system? Was the Perl installed with a binary, or built on a different system? I suspect you will need to match up the compiler of Perl with the ones you have available. > "cc -c-D_ALL_SOURCE -D_ANSI_C_SOURCE > -D_POSIX_SOURCE -qmaxmem=16384 -qnoansialias > -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -q32 > -D_LARGE_FILES -qlonglong -O-DVERSION=\"1.45\" > -DXS_VERSION=\"1.45\" > "-I/usr/opt/perl5/lib/5.8.0/aix-thread-multi/CORE" > Perl.c > "/usr/opt/perl5/lib/5.8.0/aix-thread-multi/CORE/reentr.h", > line 610.16: 1506-007 (S) "struct drand48_data" is > undefined. > "/usr/opt/perl5/lib/5.8.0/aix-thread-multi/CORE/reentr.h", > line 717.16: 1506-007 (S) "struct random_data" is > undefined. > make: 1254-004 The error code from the last command is > 1. > > > Stop." > > > Can somebody help me to get the job done? > > thanks in advance, > Pablo Salinas > If you don't get a satisfactory answer from this list I would definitely try the dbi-users list they are very good about helping with installations. http://danconia.org -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] <http://learn.perl.org/> <http://learn.perl.org/first-response>
Re: Installing Perl DBI and Oracle DBD in AIX 5.1
Hi, Thanks for your help. I searched the cc_r compiler in my AIX system, then copied the path to the Makefile and everything runned smoothly. After that, I installed the Oracle DBI by following the README instructions. thanks again, Pablo Salinas --- Wiggins d Anconia <[EMAIL PROTECTED]> escribió: > > Hi there, > > I have an AIX 5.1 machine and I wish to > use > > the perl DBI and oracle DBD in this machine. I > have > > tried to install them by downloading DBI-1.45.tar > and > > DBD-Oracle-2.15.tar files from > http://www.cpan.org/ > > > > This is a rather difficult combination and I know > there has been lots of > discussion about installing DBI on AIX and with > Oracle on the dbi-users > list, you may want to check the archives for it for > help. > > http://www.mail-archive.com/[EMAIL PROTECTED]/ > > > So, I uncompressed the DBI-1.45.tar file and > followed > > the README file instructions. > > First, I runned the folowing command: > > perl Makefile.PL > > > > this created the Makefile > > So, I tried to run the make command, but I got the > > following error: > > > > "cc_r -c-D_ALL_SOURCE -D_ANSI_C_SOURCE > > -D_POSIX_SOURCE -qmaxmem=16384 -qnoansialias > > -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -q32 > > -D_LARGE_FILES -qlonglong -O-DVERSION=\"1.45\" > > > -DXS_VERSION=\"1.45\" > > "-I/usr/opt/perl5/lib/5.8.0/aix-thread-multi/CORE" > > > Perl.c > > /bin/sh: cc_r: not found. > > make: 1254-004 The error code from the last > command is > > 127." > > > > So, I changed the compiler from cc_r to cc, runned > > make again and got this error message: > > > > I am assuming the installer pulled cc_r from the > config for Perl, check > it with perl -V. Modules generally have to be built > with the same > compiler used to build Perl which is why you would > get this second set > of errors. Is 'cc_r' not installed on the system? > Was the Perl > installed with a binary, or built on a different > system? > > I suspect you will need to match up the compiler of > Perl with the ones > you have available. > > > "cc -c-D_ALL_SOURCE -D_ANSI_C_SOURCE > > -D_POSIX_SOURCE -qmaxmem=16384 -qnoansialias > > -DUSE_NATIVE_DLOPEN -DNEED_PTHREAD_INIT -q32 > > -D_LARGE_FILES -qlonglong -O-DVERSION=\"1.45\" > > > -DXS_VERSION=\"1.45\" > > "-I/usr/opt/perl5/lib/5.8.0/aix-thread-multi/CORE" > > > Perl.c > > > "/usr/opt/perl5/lib/5.8.0/aix-thread-multi/CORE/reentr.h", > > line 610.16: 1506-007 (S) "struct drand48_data" is > > undefined. > > > "/usr/opt/perl5/lib/5.8.0/aix-thread-multi/CORE/reentr.h", > > line 717.16: 1506-007 (S) "struct random_data" is > > undefined. > > make: 1254-004 The error code from the last > command is > > 1. > > > > > > Stop." > > > > > > Can somebody help me to get the job done? > > > > thanks in advance, > > Pablo Salinas > > > > If you don't get a satisfactory answer from this > list I would definitely > try the dbi-users list they are very good about > helping with installations. > > http://danconia.org > > > -- > To unsubscribe, e-mail: > [EMAIL PROTECTED] > For additional commands, e-mail: > [EMAIL PROTECTED] > <http://learn.perl.org/> > <http://learn.perl.org/first-response> > > > _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com -- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] <http://learn.perl.org/> <http://learn.perl.org/first-response>
