Re: Error While Trying to Fetch a row

[Hardy Merrill]

2 things might be wrong here, and the error message is
correct.  Notice this:
 
  Can't locate object method "fecth"...
 
You want to use "fetch"...not "fecth" - notice the spelling.
 
The other thing that might be wrong is this:
 
  $dbh = DBI -> connect
  ("dbi:Sybase:server=DB1;database=fights;timeout=240", "opns",
"flights")
  or die ("Cannot connect: DBI::errstr");
Looks like you are trying to connect to database "fights" - 
shouldn't that be "flights"?
 
HTH.
 
Hardy Merrill

>>> On 11/4/2005 at 3:03 PM, <[EMAIL PROTECTED]> wrote:
I am using SYBASE ASE 12_2, Perl5.8.5, DBI-1.48 and I just loaded
DBD::Sybase 1.04 on a UNIX/Solaris System.

I received the following error, while trying to fetch a row.

DBD::Sybase - can't change context to database fights
Can't locate object method "fecth" via package "DBI::st" at
/usr/local/apache/cgi-bin/fetch2.cgi line 35.



A copy of the perl program is:



#!/usr/local/bin/perl -w
use warnings;
use strict;
use DBI;
use CGI qw(:standard);
BEGIN {
  $ENV{SYBASE} = '/usr/local/sybase';
  }

my ($dbh, $sth, $sql);

print header;
print start_html ("Data View Program");
my $line = "*" x 40, "";
print <

   Flights Database Data

   View For tblflights Table$line
inputform


$dbh = DBI -> connect
  ("dbi:Sybase:server=DB1;database=fights;timeout=240", "opns",
"flights")
  or die ("Cannot connect: DBI::errstr");

$sql = qq(SELECT * FROM flights.opns.tblflights); 
   
$sth =  $dbh->prepare($sql);

$sth->execute() || die "Could not execute query $DBI::errstr";
my $counter = 1;

do {
  while (my $record = $sth->fecth) { 
print "$counter: ";
for my $field (@$record){
  print "$field";
}
$counter++;
print "";
  }
}while ($sth->{syb_more_results});
  
$sth->finish();

$dbh->disconnect || die "Failed to disconnect\n";

print "Goodbye";
print end_html;



The email domain address for NYS DHCR has changed.  As of June 16th, 2008 our 
new email address is [EMAIL PROTECTED]
We ask that you begin using [EMAIL PROTECTED] immediately and to correct your 
address book to reflect this new address.

Re: Perl lib version not match executable version

[Hardy Merrill]

You may have more than one problem, but this one I
recognize - see this near the top of the errors

Premature end of script headers:

This means that your script is writing out an incomplete
html document, and it most likely means that the headers
are incomplete.  IIRC, to begin your html document you
need to write out the Content-type line and then a blank
line and then you can start your html tag, like this

Content-type: text/html



This is the Title of My Document



   your content goes here




HTH.

Hardy Merrill

>>> On 12/8/2006 at 1:33 AM, "Chong, Wei-Ling" <[EMAIL PROTECTED]>
wrote:
> Hi Tim,
> 
> Need your advise.
> 
> I have Solaris 5.8 (x86) server and running Oracle Application
Server, I
> install perl-5.8.7-sol10-x86-local.gz.
> 
> When I run my perl script, I am getting error below. How to resolve
the
> problem? 
> 
> Thanks a lot.
> 
> [Fri Dec  8 14:18:45 2006] [error] [client 165.204.172.185] [ecid:
> 1165558725:165.204.178.145:8267
> :0:37,0] Premature end of script headers:
> /oracle/app/oracle/eq/web/cgi/ppcd/ppcd_approval_ora.pl
> Perl lib version (v5.6.1) doesn't match executable version (v5.8.7)
at
> /oracle/app/oracle/product/
> oas10.1.2.0.2/perl/lib/5.6.1/i86pc-solaris/Config.pm line 21.
> Compilation failed in require at
> /oracle/app/oracle/product/oas10.1.2.0.2/perl/lib/5.6.1/i86pc-sol
> aris/DynaLoader.pm line 25.
> BEGIN failed--compilation aborted at
> /oracle/app/oracle/product/oas10.1.2.0.2/perl/lib/5.6.1/i86pc
> -solaris/DynaLoader.pm line 25.
> Compilation failed in require at
> /oracle/app/oracle/product/oas10.1.2.0.2/perl/lib/site_perl/5.6.1
> /i86pc-solaris/DBI.pm line 152.
> BEGIN failed--compilation aborted at
> /oracle/app/oracle/product/oas10.1.2.0.2/perl/lib/site_perl/5
> .6.1/i86pc-solaris/DBI.pm line 152.
> Compilation failed in require at
> /oracle/app/oracle/product/oas10.1.2.0.2/perl/lib/site_perl/5.6.1
> /i86pc-solaris/Oraperl.pm line 25.
> BEGIN failed--compilation aborted at
> /oracle/app/oracle/product/oas10.1.2.0.2/perl/lib/site_perl/5
> .6.1/i86pc-solaris/Oraperl.pm line 25.
> Compilation failed in require at
> /oracle/app/oracle/eq/web/cgi/ppcd/ppcd_approval_ora.pl line 33.
> BEGIN failed--compilation aborted at
> /oracle/app/oracle/eq/web/cgi/ppcd/ppcd_approval_ora.pl line
> 33.

Re: Oracle and bindcolumns

[Hardy Merrill]

Yes that looks correct.
 
If you're still having trouble with it, reply back and post
the small section of code that you're having trouble with,
and the error you are getting.
 
Hardy Merrill

>>> Robert Hicks <[EMAIL PROTECTED]> 10/18/2006 10:11 AM >>>

Hardy Merrill wrote:
> Sorry for the top-post - Groupwise :-(
>  
> Notice how Philip suggested using "to_char" - *not*
> "to_date".
>  
> You probably already know this, but on the chance you don't,
> you use "to_date" if you have a string that contains a date and
> you want to put that date into a "DATE" column in the database.
> You use "to_char" if you want to pull a "DATE" column out of
> the database into a string (scalar) variable.
>  
> HTH.
>  
> Hardy Merrill

I think I get it yes. So here is what I am doing. Access has a date 
field that I am pulling out and when I print the "$start_date" variable

it looks like this:

2006-09-15 00:00:00

That is a string now to Perl...correct? Now I am inserted that string 
into the Oracle database as a DATE. So I am doing, using the variable 
from the bindcolumn parameter:

TO_DATE($start_date, 'MM/DD/')

to insert that string into Oracle as a DATE and passing in the date 
format along with it.

Do I have that right?

Robert

Re: Oracle and bindcolumns

[Hardy Merrill]

Sorry for the top-post - Groupwise :-(
 
Notice how Philip suggested using "to_char" - *not*
"to_date".
 
You probably already know this, but on the chance you don't,
you use "to_date" if you have a string that contains a date and
you want to put that date into a "DATE" column in the database.
You use "to_char" if you want to pull a "DATE" column out of
the database into a string (scalar) variable.
 
HTH.
 
Hardy Merrill

>>> Robert Hicks <[EMAIL PROTECTED]> 10/17/2006 8:32 PM >>>

Garrett, Philip (MAN-Corporate) wrote:
> Robert Hicks wrote:
>> Any gotchas there? I am opening an Access db via ODBC and binding
>> those columns (including a date field) and passing that to the
Oracle
>> handle to do inserts (i.e. Access -> Oracle migration).
> 
> Only gotcha is with formatting -- you'll need to either:
> 
> 1) "alter session set nls_date_format = '...'" to the date format
you're
> supplying Oracle, or:
> 
> 2) use to_char(?,'...') on the date fields
> 
> Philip

Oracle won't accept it if I do TO_DATE($start_date, 'DD/MM/') ?

Robert

Re: can't load library that exists (Oracle.sl)

[Hardy Merrill]

Since nobody else has replied yet, I'll take a shot - note that I haven't
used perl or Oracle or perl DBI in quite a while, and I've never worked
on an HP Cluster, but looking at your error my guess is that you either

  1. don't have DBD::Oracle installed on the failover machine, or
  2. do have DBD::Oracle installed, but haven't compiled it on the
  failover machine (note that you need at least the Oracle client
  on that machine), or
  3. DBD::Oracle is installed and compiled, but is not properly
  linked

Hopefully someone else will chime in with a more definite diagnosis.

Hope this helps.

Hardy Merrill

>>> "Rashid, Mohammad M, NTWOP" <[EMAIL PROTECTED]> 5/12/2006 3:34 PM >>>
Hi:

Any help in this matter would be greatly appreciated.

We have a HP cluster that has per, perl DBI, and oracle.

Machine A runs fine.

When we fail over to Machine B, on some applications we get the following error:
  (please see below the error; the file exists! and it is executable!)

  please help.

Thanks

-Mamun

___
Can't load 
'/opt/perl582/lib/site_perl/5.8.2/PA-RISC2.0/auto/DBD/Oracle/Oracle.sl' for 
module DBD::Oracle: No such file or directory at /
opt/perl582/lib/5.8.2/PA-RISC2.0/DynaLoader.pm line 229.
 at /opt/netscape/apache/docs/nerdv2/nerd.pm line 13
 Compilation failed in require at /opt/netscape/apache/docs/nerdv2/nerd.pm line 
13.
 BEGIN failed--compilation aborted at /opt/netscape/apache/docs/nerdv2/nerd.pm 
line 13.
 Compilation failed in require at /opt/netscape/apache/docs/nerdv2/index.cgi 
line 13.
 BEGIN failed--compilation aborted at 
/opt/netscape/apache/docs/nerdv2/index.cgi line 13.
 [Mon Nov 21 21:05:53 2005] [error] [client 135.173.9.69] Premature end of 
script headers: index.cgi, referer: http://bgtsgvpo1.ops.worldn 
 et.att.net:8080/navigation.html
_

bgtsgvpo1:mrashid:file 
/opt/perl582/lib/site_perl/5.8.2/PA-RISC2.0/auto/DBD/Oracle/Oracle.sl
/opt/perl582/lib/site_perl/5.8.2/PA-RISC2.0/auto/DBD/Oracle/Oracle.sl:  
PA-RISC2.0 shared library -not stripped
bgtsgvpo1:mrashid:ls -l 
/opt/perl582/lib/site_perl/5.8.2/PA-RISC2.0/auto/DBD/Oracle/Oracle.sl
-r-xr-xr-x   1 root   sys8966144 Nov 14  2003 
/opt/perl582/lib/site_perl/5.8.2/PA-RISC2.0/auto/DBD/Oracle/Oracle.sl
bgtsgvpo1:mrashid:

RE: (Fwd) suggestion for DBI

[Hardy Merrill]

It's been a long time since I've actively written Perl (and DBI) code, but I
wanted to add that IIRC if you use "trace" (or $dbh->trace(2)  or trace(3) ??)
around the DBI code in question, the trace output includes(?) the SQL along
with the placeholder values being inserted.  Hope I'm not mistating the facts
here but it's at least worth a look at the DBI docs - look up "trace".

HTH.

Hardy Merrill

>>> "Martin J. Evans" <[EMAIL PROTECTED]> 4/13/2006 10:08:12 AM >>>
> - Forwarded message from Tomas Karlsson <[EMAIL PROTECTED]> -
> I would like to suggest a new feature/function in DBI. It would really 
> be nice if there was a way to print out the actual sql statement for 
> debugging purpose, in particular in cases with placeholders like this 
> example:

I've been working on something that would probably satisfy this but up until
now just using it internally in a project I'm working on here.

If you replace your:

use DBI;
$dbh = DBI->connect()

with 

use DBIx::Log4perl;
$dbh = DBIx::Log4perl->connect()

create a Log4perl config file (example included) and optionally set
$DBIx::Log4perl::LogMask you will get a variety of SQL, parameters, methods
called etc in the log file.

e.g. like these:

DEBUG - prepare: 'insert into mytest values (?,?)'
DEBUG - $execute = [1,'one'];
DEBUG - $execute = [2,'two'];
DEBUG - $execute = [3,'three'];

DEBUG - $bind_param_array = [1,[51,1,52,53]];
DEBUG - $bind_param_array = [2,['fiftyone','fiftytwo','fiftythree','one']];
DEBUG - $execute_array = {'ArrayTupleStatus' => []};
DEBUG - $execute = [51,'fiftyone'];
DEBUG - $execute = [1,'fiftytwo'];
DEBUG - $execute = [52,'fiftythree'];
DEBUG - $execute = [53,'one'];
ERROR - execute_array error:
ERROR - $Error = [1062,'Duplicate entry \'1\' for key 1','S1000'];
ERROR -  for 1,fiftytwo

and if an error is caught something like this:

FATAL
  DB: mjetest, Username: bet
  handle type: st
  SQL: insert into mytest values (?,?)
  msg: Duplicate entry '1' for key 1
  ParamValues: 1,onetwothree,
  DBI error trap at /home/martin/tools/modules/DBIx-Log4perl/lib//DBIx/L
og4perl/st.pm line 18
DBIx::Log4perl::st::execute('DBIx::Log4perl::st=HASH(0x84018fc)') called
 at ./MyDBI_test.pl line 91

I never really intended to release it as such and it is rather rough around the
edges but if it helps someone else fair enough.

I'd be happy to hear any comments, suggestions, fixes, enhancements etc but
please bare in mind 1) I've packaged it up and documented it in about 1 hour
this morning to get it here and 2) it pretty much does what I need it for now
and never started out as something anyone else would use.

ftp://ftp.easysoft.com/pub/odbc-odbc-bridge/Perl/modules/DBIx-Log4perl-0.01.tar.
gz

I'm afraid you will need DBI 1.50 and Log::Log4perl 1.04 to get the best
results although it will work with earlier Log4perl (1.01) but some of the
logging does not come out. You may also want to look a the notes in the pod as
you will need to make a small fix to DBI to get Username and ParamArrays out.

Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com 


On 12-Apr-2006 Tim Bunce wrote:
> - Forwarded message from Tomas Karlsson <[EMAIL PROTECTED]> -
> 
> Date: Wed, 12 Apr 2006 16:47:18 -0400
> From: Tomas Karlsson <[EMAIL PROTECTED]>
> Reply-To: [EMAIL PROTECTED] 
> Organization: 10 East Corp.
> To: [EMAIL PROTECTED] 
> Subject: suggestion for DBI
> 
> Hi Tim,
> 
> I would like to suggest a new feature/function in DBI. It would really 
> be nice if there was a way to print out the actual sql statement for 
> debugging purpose, in particular in cases with placeholders like this 
> example:
> 
> my @row = ($employee_id, $dist_id, $delivery_carrier, $delivery_method,
>$weight, $shipping_charges, $comments, $date_shipped);
> 
> $sql = qq|
>   INSERT INTO per_emp_dist
>   (employee_id, dist_id, delivery_carrier, delivery_method,
>weight, shipping_charges, comments, date_shipped)
>   VALUES ( @{[ join ',' => ('?') x @row ]} )|;
> 
> $sth = $dbh->prepare($sql);
> $sth->execute(@row);
> 
> 
> Thanks for the grand DBI module!
> 
> Tomas
> 
> - End forwarded message -

Re: PostgreSQL trouble

[Hardy Merrill]

I'm not being wise here, but did you do

use DBI;

at the top of your script?

Hardy Merrill

>>> "Christian Stalp" <[EMAIL PROTECTED]> 1/23/2006 11:57 AM >>>
I tried this:

my $dbh = DBI->connect("dbi:Pg:database=test2;host=127.0.01;port=5432",
"postgres") or die $DBI:errstr;
test2 is the name of the database.

and got this:
Global symbol "$DBI" requires explicit package name at ./postgre_test_1.pl
line12.
syntax error at ./postgre_test_1.pl line 12, near "$DBI:"
Execution of ./postgre_test_1.pl aborted due to compilation errors.

Gruss Christian

-- 
DSL-Aktion wegen gro*er Nachfrage bis 28.2.2006 verl*ngert:
GMX DSL-Flatrate 1 Jahr kostenlos* http://www.gmx.net/de/go/dsl

RE: Order by statement stopped working but not in all cases

[Hardy Merrill]

Sorry for the OT response but I couldn't let this go.

Anna K's fame has faded??  She still has a few fans :)
Ron, if you have any influence at the Australian Open, next year
try to get them to sign a tv contract with ABC, NBC, or CBS - instead
of ESPN2 which I don't get.  Thanks.

Hardy Merrill

>>> Ron Savage <[EMAIL PROTECTED]> 1/20/2006 6:21 PM >>>

[1] We're in the middle of the Australian Tennis Open, and a goanna is a native 
animal, and Go Anna! is a joke about a Russian tennis player called Anna K. 
(whose fame seems to have faded).

-- 
Cheers
Ron Savage, [EMAIL PROTECTED] on 21/01/2006
http://savage.net.au/index.html 
Let the record show: Microsoft is not an Australian company

Web hosting companies that offer Perl and MySQL or Postgres?

[Hardy Merrill]

Sorry, I know this is off (DBI) topic, but it does relate to Perl and a 
database.

I'm about to start developing an app with Perl DBI with the thought that 
eventually I'd like to put the app on the web - for which I plan to get a 
website hosting company, like Yahoo or whatever.  So I'm trying to decide which 
database to use - I want to use one that I know will be supported by the 
webhosting company I choose.  I've used both MySQL and PostgreSQL and have no 
problem using either.  I know Yahoo hosts websites and their "Standard" and 
"Premium" packages both offer Perl and MySQL.

Are there some web hosting companies that offer Perl and Postgres?

TIA.

Hardy Merrill

Re: Executing PL/SQL packages in Perl

[Hardy Merrill]

The DBD::Oracle perl module documentation has this

http://search.cpan.org/dist/DBD-Oracle/Oracle.pm#PL/SQL_Examples 

HTH.

Hardy Merrill

>>> logesh kumar <[EMAIL PROTECTED]> 11/9/2005 6:36 AM >>>
Hi all,
  Can anybody tell me how to execute a PL/SQL package from Perl through 
a small example?
 
Thanks in advance,
Lokesh
 


-
 Yahoo! FareChase - Search multiple travel sites in one click.  

RE: Oracle and dbh -> last_insert_id

[Hardy Merrill]

Another alternative is to submit 2 queries - the first would be to retrieve
the sequence "nextval":

   SELECT [sequence name].NEXTVAL as my_seq_nextval FROM dual

and the next would be the INSERT where you actually provide the
value you just SELECT'ed.  It does involve an initial SELECT query to get 
the value of the sequence nextval, but with this approach you don't need
to use the dbi last_insert_id.

Hardy Merrill

>>> "Gaul, Ken" <[EMAIL PROTECTED]> 10/6/2005 9:00 AM >>>

-Original Message-
From: Ron Savage [mailto:[EMAIL PROTECTED] 
Sent: 06 October 2005 11:30
To: List - DBI users
Subject: Oracle and dbh -> last_insert_id

Hi Folks

I can't get last_insert_id() to work with Oracle.

OS: GNU/Linux
DBI: V 1.48
Oracle: 9.2.0

Demo (to save you typing): http://savage.net.au/last.pl 

I've tried various values for catalog and schema, including the schema
for the 
(test) table.

Any idea?
-- 
Cheers
Ron Savage, [EMAIL PROTECTED] on 6/10/2005
http://savage.net.au/index.html 
Let the record show: Microsoft is not an Australian company

I would doubt that dbd Oracle supports this. I would recommend using the
insert...returning clause, 
INSERT INTO emp
  (empno, ename)
  VALUES
  (seq_emp.NEXTVAL, 'Morgan')
  RETURNING empno
  INTO x;

Ken.

Re: dbiproxy connection failure...

[Hardy Merrill]

I'm taking a wild guess here since it's been a long time since I've used
dbiproxy and/or PostgreSQL - going by the error message

failed: FATAL:  IDENT authentication failed for user "mason"

I'd say that your connection string user name/password was rejected by
Postgres.  I've found PostgreSQL's authentication can be rather tricky
to get working right.

Hardy Merrill

>>> Mason Loring Bliss <[EMAIL PROTECTED]> 9/12/2005 12:40:35 PM >>>
On Mon, Sep 12, 2005 at 12:14:07PM -0400, Mason Loring Bliss wrote:

> I can connect to the database using $dsn directly, and I can connect
using
> psql, but dbiproxy logs this when I try to connect using it:

FWIW, I get more when I log to the terminal, instead of syslog:

Mon Sep 12 12:37:03 2005 err, 1, Error while connecting to
DBI:Pg:dbname=oracle as mason: DBI::ProxyServer
connect('dbname=oracle','mason',...) failed: FATAL:  IDENT
authentication failed for user "mason"
 [err=1,state=S1000] at /usr/lib/perl5/DBI.pm line 604 thread 1
DBI::__ANON__('undef', 'undef') called at /usr/lib/perl5/DBI.pm
line 660 thread 1
DBI::connect('DBI::ProxyServer', 'DBI:Pg:dbname=oracle',
'mason', 'testing', 'HASH(0x85947e8)') called at
/usr/lib/perl5/DBI/ProxyServer.pm line 200 thread 1   
eval {...} called at /usr/lib/perl5/DBI/ProxyServer.pm line 189
thread 1
   
DBI::ProxyServer::AcceptUser('DBI::ProxyServer=HASH(0x839f69c)',
'mason', 'testing') called at /usr/share/perl5/RPC/PlServer.pm line 171
thread 1
RPC::PlServer::Accept('DBI::ProxyServer=HASH(0x839f69c)')
called at /usr/share/perl5/Net/Daemon.pm line 506 thread 1
eval {...} called at /usr/share/perl5/Net/Daemon.pm line 505
thread 1
Net::Daemon::HandleChild('DBI::ProxyServer=HASH(0x839f69c)')
called at /usr/share/perl5/Net/Daemon.pm line 466 thread 1
Net::Daemon::__ANON__('DBI::ProxyServer=HASH(0x839f69c)',
'HandleChild') called at /usr/share/perl5/Net/Daemon.pm line 468 thread
1
eval {...} called at /usr/share/perl5/Net/Daemon.pm line 468
thread 1

Mon Sep 12 12:37:03 2005 err, 1, Refusing client



Thanks in advance for clues!

-- 
   /\
Mason Loring Bliss/()\http://blisses.org/ 
 Squeak to me of love!   /\   [EMAIL PROTECTED]

RE: string help!

[Hardy Merrill]

Really!  Why not?  I don't have any experience with Sybase but I haven't
that comment about any other databases that *shouldn't* use
placeholders.

>>> "Anderson, James H (Company IT)" <[EMAIL PROTECTED]>
3/31/2005 2:52 PM >>>
Probably not the optimum solution if you're using Sybase... 

-Original Message-
From: Hardy Merrill [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 31, 2005 2:11 PM
To: [EMAIL PROTECTED]; dbi-users@perl.org 
Subject: Re: string help!

Use "placeholders" - all your quoting issues will go away.

Read about them by doing

   perldoc DBI

at a command prompt and searching for "Placeholders".

HTH.

Hardy Merrill

>>> "Ron Stephan" <[EMAIL PROTECTED]> 3/31/2005 1:52 PM >>>
Help --

I am lost trying figure out what occurs to me a really simple problem.


CGI->Perl->MySQL.


Users pounding data into a CGI-Perl web page feeding MySQL.

If the user enters a special character it crashes.

As in "Bob's Market"...  Yeah - trap for special chars and escape
them.

This seems so simple - yet the solutions that come to mind make me
feel
like I am reinventing the wheel.

There must be some really simple solution everybody is using.



Ron "Elvis" Stephan
[EMAIL PROTECTED] 

 
NOTICE: If received in error, please destroy and notify sender.  Sender
does not waive confidentiality or privilege, and use is prohibited. 
 

RE: string help!

[Hardy Merrill]

Just in case it's not clear to you, the "placeholders" are the question
marks in the VALUES part of the sql  ;-)

>>> "Ronald J Kimball" <[EMAIL PROTECTED]> 3/31/2005 2:36 PM >>>
Ron Stephan [mailto:[EMAIL PROTECTED] wrote: 

> Placeholders?
> 
> Like how?

Here's a quick example:

my $sth = $dbh->prepare(<<"EndOfSQL");
  INSERT INTO mytable
(col1, col2)
  VALUES
(?, ?)
EndOfSQL

$sth->execute($val1, $val2);

The DBI documentation has more details.


Ronald

Re: string help!

[Hardy Merrill]

Use "placeholders" - all your quoting issues will go away.

Read about them by doing

   perldoc DBI

at a command prompt and searching for "Placeholders".

HTH.

Hardy Merrill

>>> "Ron Stephan" <[EMAIL PROTECTED]> 3/31/2005 1:52 PM >>>
Help --

I am lost trying figure out what occurs to me a really simple problem.


CGI->Perl->MySQL.


Users pounding data into a CGI-Perl web page feeding MySQL.

If the user enters a special character it crashes.

As in "Bob's Market"...  Yeah - trap for special chars and escape
them.

This seems so simple - yet the solutions that come to mind make me feel
like I am reinventing the wheel.

There must be some really simple solution everybody is using.



Ron "Elvis" Stephan
[EMAIL PROTECTED] 
 

Re: Entering a TimeStamp value into DB2

[Hardy Merrill]

Try googling next time for an answer to your question.

I went to google and searched for "db2 timestamp" and this
reference had some good info:

   http://www.craigsmullins.com/dbu_1200.htm

Here's a snippet:

Formatting

If formatting the date and time data is a major consideration for your
application then DATE and TIME may be a better solution. DB2 provides
formatting options for DATE and TIME columns via local DATE and TIME
exits. This means that you can specify a DB2 subsystem-wide default for
the way that date and time values should be displayed. Likewise the CHAR
function and DATE and TIME precompiler options exist for formatting DATE
and TIME data. If the date and time information is to be extracted and
displayed on a report or by an online application, the availability of
these DB2-provided facilities for DATE and TIME columns should be
considered when making your decision.

These facilities are not available for TIMESTAMP columns. There is a
single format for a timestamp value in DB2, and it looks like the sample
shown previously. A timestamp value always begins with the date
component, in the format -MM-DD combined using a hyphen ("-") to the
time component in the format:

   hh.mm.ss.zzzzzz.

HTH.

Hardy Merrill

>>> <[EMAIL PROTECTED]> 03/23/05 11:21 PM >>>




I am having a problem using the Perl DBD::DB2 module to enter a
'TimeStamp'
value into a DB2 table. I have been able to write a perl script which
can
enter an 'integer' or 'varchar' value into a DB2 table. I also need to
enter a 'TimeStamp' value into the same table. I have not been able to
correctly format the 'TimeStamp' value in my script so that the
'INSERT'
command executes without errors.

Does anyone have a perl example script for entering 'TimeStamp' values
into
a DB2 table?

*
Richard Rice
Continental Tire North America, Inc.
(704) 583-8668
*

Re: quer y about dbi

[Hardy Merrill]

If you plan to do a project with Perl / DBI / MySQL, then I'd suggest
you buy the "Programming the Perl DBI" book written by Tim Bunce
and Alligator Descartes - it's an O'Reilly book.  I think you'll find
that
all your DBI/DBD::MySQL questions will be answered by that book.

And if you don't have yourself a good book on MySQL yet, I'd
recommend getting one.  Personally I like "MySQL" - 2nd Edition
by Paul Dubois.  I have the 1st edition and think that is an excellent
book.  Teaches you about all aspects of MySQL, and also gives a
primer on SQL.

For Perl I like "Learning Perl", "Programming Perl", and
"Perl Cookbook" - all O'Reilly books.

HTH.

Hardy Merrill

>>> Greg Armer <[EMAIL PROTECTED]> 03/15/05 5:28 AM >>>
On Mon, 2005-03-14 at 20:55 -0800, venkatesh shapur wrote:

> I want know about the deatails of the how to use DBI connectivity in
perl language with MySQL
> database.And for the datails of the How to Insert an item into the
MySQL  using DBI interface
> in Perl language.And similarly all operations regarding
delete,update,display seperately.
> Because we want to do the project in the LAMP(Linux Apache MySQL
Perl) plz respond to us.
> As possible as early.
> 
> 

And how much are you going to pay us to do your work for you ?

This list is not a documentation resource, you can find more than
adequate examples and documentation at
http://dbi.perl.org  

You should find all the information you require there.


> __
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection around 
> http://mail.yahoo.com 


regards,

Greg Armer
Systems Manager | Senior Developer

GPG Fingerprint = B4A1 0808 CB05 B34C 8647  5D57 E525 CD45 613E B823

RE: Value deletion

[Hardy Merrill]

First things first.  When someone suggests something to you on this
list, especially
when they suggest it more than once, you should try it.  I missed this
before, but I'm 
remembering now an earlier suggestion by Michael Chase - when you
notice an error
in a dbi statement, print out $DBI::errstr - something like this:

   my($sth) = $::dbh->prepare($sql) or &Error("Unable to prepare
statement: $DBI::errstr.");

and you should be sure to do that on every dbi statement (connect,
prepare, execute, etc.).
That will go a long way towards helping to figure out what is wrong.

Along the lines of trying suggestions made to you, did you try my
suggestion of doing
your UPDATE using a command line client of your database?  I don't
remember you
saying which database you are using.

I have *no* experience with stored procedures so I can't help debug
that part - I have
no idea why the code would be executed twice.

Hardy Merrill

>>> "Moreno, Javier" <[EMAIL PROTECTED]> 03/11/05 12:11 PM >>>
Ok. So I moved away from the UPDATE and now I am running a stored
procedure. When I look at the trace, the stored procedure runs exactly
as expected, however, it then runs a second time and screws things up.
You can see on the below trace what I mean. I believe this is what is
clobbering the value on the database. But why would it happen? Please
see code below and then trace.  

DBI->trace(1,"log_file.log");
$sql = "EXEC [OEEMGR].[dbo].[_Update_Global_Settings] '$lang_code',
'$old_lang_code'";
my($sth) = $::dbh->prepare($sql) or &Error("Unable to prepare
statement.");
$ref = $sth->execute or &Error("Unable to execute statement", $sql);

 End Code

DBI 1.46-ithread default trace level set to 0x0/1 (pid 3352)
<- prepare('EXEC [OEEMGR].[dbo].[_Update_Global_Settings] 'SP',
'EN'')= ( DBI::st=HASH(0x19c71d4) ) [1 items] at OEEMGR_Lang.pl line
128
<- execute= 1 at OEEMGR_Lang.pl line 129
<- disconnect= 1 at OEEMGR_Lang.pl line 171
<- DESTROY(DBI::st=HASH(19c71d4))= undef
<- disconnect_all= '' at DBI.pm line 671
!   <- DESTROY(DBI::db=HASH(1988be0))= undef during global destruction
!   <- DESTROY(DBI::dr=HASH(18f3ae0))= undef during global destruction
DBI 1.46-ithread default trace level set to 0x0/1 (pid 2872)
<- prepare('EXEC [OEEMGR].[dbo].[_Update_Global_Settings] '',
'SP'')= ( DBI::st=HASH(0x19c57c0) ) [1 items] at OEEMGR_Lang.pl line
128
<- execute= 1 at OEEMGR_Lang.pl line 129
<- disconnect= 1 at OEEMGR_Lang.pl line 171
<- DESTROY(DBI::st=HASH(19c57c0))= undef
<- disconnect_all= '' at DBI.pm line 671
!   <- DESTROY(DBI::db=HASH(17cae1c))= undef during global destruction
!   <- DESTROY(DBI::dr=HASH(18f5680))= undef during global destruction

-Original Message-
From: Hardy Merrill [mailto:[EMAIL PROTECTED] 
Sent: Friday, March 11, 2005 8:34 AM
To: Moreno, Javier
Cc: dbi-users@perl.org 
Subject: RE: Value deletion


I'm not familiar enough with trace output to know exactly what's
happening there, but I'm guessing that the UPDATE is the problem.
When you do an UPDATE, don't you *have* to have a WHERE
clause?

I see you're using ODBC - not sure if you've done this yet, or if you
have an ODBC client (or client for your database) you can use to
execute SQL at a client command prompt, but if I were using Oracle I
would use the "sqlplus" Oracle client to try doing

UPDATE GLOBALSettings SET Lang = 'EN'

at the client command prompt.

If that works, then you know your problem is with DBI/DBD::ODBC.  If
that doesn't work, the client will give you some kind of SQL error, so
you
know your problem is with the SQL.

I'm wondering if the UPDATE is actually happening.  See if you can
find
a way to test that UPDATE not using Perl / DBI / DBD::ODBC.

HTH.

Hardy Merrill

>>> "Moreno, Javier" <[EMAIL PROTECTED]> 03/10/05 5:16 PM >>>
The trace was a good idea. However I do not understand the log very
much. There is an error there but there's also an error on my next
select which basically was to test the update, see if it fetched.

DBI 1.46-ithread default trace level set to 0x0/4 (pid 2804)
-> prepare for DBD::ODBC::db (DBI::db=HASH(0x19a40d8)~0x19c5694
'UPDATE GLOBALSettings SET Lang = ?') thr#15d4374
New DBI::st (for DBD::ODBC::st, parent=DBI::db=HASH(0x19c5694),
id=)
   
dbih_setup_handle(DBI::st=HASH(0x19c5784)=>DBI::st=HASH(0x1974888),
DBD::ODBC::st, 19c5790, Null!)
dbih_make_com(DBI::db=HASH(0x19c5694), 19c5c54, DBD::ODBC::st,
208,
0) thr#15d4374
dbd_preparse scanned 1 distinct placeholders
SQLPrepare returned 0

dbd_st_prepare'd sql f28514864, ExecDirect=0
UPDAT

RE: Value deletion

[Hardy Merrill]

I'm not familiar enough with trace output to know exactly what's
happening there, but I'm guessing that the UPDATE is the problem.
When you do an UPDATE, don't you *have* to have a WHERE
clause?

I see you're using ODBC - not sure if you've done this yet, or if you
have an ODBC client (or client for your database) you can use to
execute SQL at a client command prompt, but if I were using Oracle I
would use the "sqlplus" Oracle client to try doing

UPDATE GLOBALSettings SET Lang = 'EN'

at the client command prompt.

If that works, then you know your problem is with DBI/DBD::ODBC.  If
that doesn't work, the client will give you some kind of SQL error, so
you
know your problem is with the SQL.

I'm wondering if the UPDATE is actually happening.  See if you can
find
a way to test that UPDATE not using Perl / DBI / DBD::ODBC.

HTH.

Hardy Merrill

>>> "Moreno, Javier" <[EMAIL PROTECTED]> 03/10/05 5:16 PM >>>
The trace was a good idea. However I do not understand the log very
much. There is an error there but there's also an error on my next
select which basically was to test the update, see if it fetched.

DBI 1.46-ithread default trace level set to 0x0/4 (pid 2804)
-> prepare for DBD::ODBC::db (DBI::db=HASH(0x19a40d8)~0x19c5694
'UPDATE GLOBALSettings SET Lang = ?') thr#15d4374
New DBI::st (for DBD::ODBC::st, parent=DBI::db=HASH(0x19c5694),
id=)
dbih_setup_handle(DBI::st=HASH(0x19c5784)=>DBI::st=HASH(0x1974888),
DBD::ODBC::st, 19c5790, Null!)
dbih_make_com(DBI::db=HASH(0x19c5694), 19c5c54, DBD::ODBC::st, 208,
0) thr#15d4374
dbd_preparse scanned 1 distinct placeholders
SQLPrepare returned 0

dbd_st_prepare'd sql f28514864, ExecDirect=0
UPDATE GLOBALSettings SET Lang = ?
<- prepare= DBI::st=HASH(0x19c5784) at OEEMGR_Lang.pl line 116
-> execute for DBD::ODBC::st (DBI::st=HASH(0x19c5784)~0x1974888
'EN') thr#15d4374
bind 1 <== 'EN' (attribs: ), type 0
SQLDescribeParam idx = 1.
dbd_st_execute (outparams = 0)...
bind 1 <== 'EN' (size 2/3/0, ptype 4, otype 1, sqltype 1)
bind 1 <== 'EN' (len 2/2, null 0)
bind 1: CTy=1, STy=CHAR, CD=2, Sc=2, VM=2.
dbd_st_execute (for hstmt 28514864 before)...
dbd_error: err_rc=0 rc=0 s/d/e: 28514864/28513096/28512928
dbd_error: err_rc=0 rc=0 s/d/e: 0/28513096/28512928
dbd_error: err_rc=0 rc=0 s/d/e: 0/0/28512928
dbd_describe sql 28514864: num_fields=0
dbd_describe skipped (no result cols) (sql f28514864)
dbd_st_execute got no rows: resetting ACTIVE, moreResults
<- execute= 1 at OEEMGR_Lang.pl line 117
-> prepare for DBD::ODBC::db (DBI::db=HASH(0x19a40d8)~0x19c5694
'SELECT Lang FROM GLOBALSettings') thr#15d4374
New DBI::st (for DBD::ODBC::st, parent=DBI::db=HASH(0x19c5694),
id=)
dbih_setup_handle(DBI::st=HASH(0x19c582c)=>DBI::st=HASH(0x19c585c),
DBD::ODBC::st, 19c5838, Null!)
dbih_make_com(DBI::db=HASH(0x19c5694), 19c5c54, DBD::ODBC::st, 208,
0) thr#15d4374
SQLPrepare returned 0

dbd_st_prepare'd sql f28516032, ExecDirect=0
SELECT Lang FROM GLOBALSettings
<- prepare= DBI::st=HASH(0x19c582c) at Functions.pl line 182 via
C:/oracle/ora92/Apache/Apache/ITTWeb/cgi-bin/OEEMGR/OEEMGR_Lang.pl line
121
-> execute for DBD::ODBC::st (DBI::st=HASH(0x19c582c)~0x19c585c)
thr#15d4374
dbd_st_execute (outparams = 0)...
dbd_st_execute (for hstmt 28516032 before)...
dbd_error: err_rc=0 rc=0 s/d/e: 28516032/28513096/28512928
dbd_error: err_rc=0 rc=0 s/d/e: 0/28513096/28512928
dbd_error: err_rc=0 rc=0 s/d/e: 0/0/28512928
dbd_describe sql 28516032: num_fields=1
  col  1: CHAR (1) len=  2 disp=  3, prec=  2 scale=0
  col  1: 'Lang' sqltype=CHAR, ctype=SQL_C_CHAR, maxlen=3, (dp = 0,
cp = 0)
<- execute= -1 at Functions.pl line 191 via
C:/oracle/ora92/Apache/Apache/ITTWeb/cgi-bin/OEEMGR/OEEMGR_Lang.pl line
121
-> fetchall_arrayref for DBD::ODBC::st
(DBI::st=HASH(0x19c582c)~0x19c585c) thr#15d4374
   SQLFetch rc 0
dbih_setup_fbav for 1 fields => 0x19c88a0
fetch num_fields=1
fetch col#0 Lang datalen=2 displ=3
   SQLFetch rc 100
<- fetchall_arrayref= [ ARRAY(0x19c8900) ] row1 at Functions.pl
line 196 via
C:/oracle/ora92/Apache/Apache/ITTWeb/cgi-bin/OEEMGR/OEEMGR_Lang.pl line
121
<> DESTROY(DBI::st=HASH(0x19c582c)) ignored for outer handle (inner
DBI::st=HASH(0x19c585c) has ref cnt 1)
-> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x19c585c)~INNER)
thr#15d4374
<- DESTROY= undef at OEEMGR_Lang.pl line 121
dbih_clearcom 0x19c582c (com 0x19c941c, type 3) done.

-> disconnect for DBD::ODBC::db (DBI::db=HASH(0x19a40d8)~0x19c5694)
thr#15d4374
<- disconnect= 1 at OEEMGR_Lang.pl line 163
-- DBI::END
-> disconnect_all for DBD::ODBC::dr
(DBI::dr=HASH(0x18f3a70)~0x19a4108) thr#15d4374

RE: Value deletion

[Hardy Merrill]

Two things:
  1. copy and paste your connect statement in here so we can see
  exactly how you are connecting, and
  2. user "trace" to see exactly what is being sent by DBI to the
  database.  Read about this in the perldocs by doing

   perldoc DBI

  at a command prompt and search for "trace".  Start using a low
  number like "1", and if you don't get enough detail, try "2",
etc.
  But "1" should be good to start with.

HTH.

Hardy Merrill

>>> "Moreno, Javier" <[EMAIL PROTECTED]> 03/10/05 12:29 PM >>>
Actually it is now getting worse. It has AutoCommit set to 0 so I am
doing this:

$::sql = "UPDATE GLOBALSettings SET Lang = ?";
$::crt = $::lang_code;
&Debug("About to prepare: $::sql with criteria: $::crt");
$::sth = $::dbh->prepare($::sql) or &Error("Unable to prepare
statement.");
$::rec = $::sth->execute ($::crt) or &Error("Unable to execute
statement");
$::dbh->commit;

But it is now writing a  value on the DB. That &Debug line
shows:

About to prepare: UPDATE GLOBALSettings SET Lang = ? with criteria: SP

So the value is fine but it is not updating right. What I found was
that the second field on the SQL table was set to no nulls so the update
was failing on the webserver log because of that. BTW responding to the
"app bombs" ambiguity, I use the value for an auto META REFRESH value to
find out the directory of the language I am running.

Regards,

Javier Moreno

-Original Message-
From: Michael A Chase [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 10, 2005 11:21 AM
To: Moreno, Javier
Cc: dbi-users@perl.org 
Subject: Re: Value deletion


Quoting "Moreno, Javier" <[EMAIL PROTECTED]>:

> I have a script that writes a single record to a single row on a
> GLOBALSettings table. I have a web page that does that and it does
so
> successfully. I go into the database with SQL Server Enterprise
Manager and
> see the value on the table. Then I use another page that takes
advantage of
> that value, it selects with no criteria the needed field and the
value gets
> wiped out from the DB and of course the app bombs. I tried using a
> dbh->commit at the end of the update script but that didn't work
either. Does
> anyone know what is wrong? Sample code below:

You need either $dbh->{AutoCommit} set to 1 or you must use
$dbh->commit or the
value will be rolled back when your script exits.

You didn't show us your DBI->connect() call, so we don't know what it
set.

>  INSERT SCRIPT
>
> # Insert into DB
> my($sql) = "UPDATE GLOBALSettings SET Lang = '$::lang_code'";

# Bind variables should be used whenever possible.
my $sql = "UPDATE GLOBALSettings SET Lang = ?";

> my($criteria_var) = "";
> my($criteria) = "";
> my($reference) = &SQL_Update($sql, $criteria_var, $criteria);

my $reference = &SQL_Update( $sql, $criteria_var, $::lang_code,
$criteria);
$dbh -> commit;

> # Disconnect from database
> $::rc = $::dbh->disconnect or
>&Error("Unable to disconnect from database.");

# If you are trapping individual errors, you should include
# the DBI error message or you won't have a clue what went wrong.
$::rc = $::dbh->disconnect or
   &Error( "Unable to disconnect from database, $DBI::errstr." );

> sub SQL_Update {
>my ($sql, $criteria_field, $criteria) = @_;

   # If you collect all the bind values in an array, you don't
   # have to worry about a special case when there aren't any.
   my ( $sql, $criteria_field, @value ) = @_;

> # Append criteria if present
> if ($criteria_field && $criteria) {
>$sql.= " WHERE $criteria_field = ?";
> }
>
> # Debug the SQL statement (uncomment for testing)
> &Debug("$sql");
>
> # Prepare a SQL statement
> my $sth = $::dbh->prepare( $sql ) or
>&Error("Unable to prepare statement.");

# Again, _always_ include the error text from DBI.
my $sth = $::dbh->prepare( $sql ) or
   &Error( "Unable to prepare statement, $DBI::errstr." );

> # Execute the SQL statement prepared above, use with or without
criteria
> depending on if it is present
> # or not
> if ($criteria) {
>my $rc = $sth->execute ( $criteria ) or
>   &Error("Unable to execute statement", "$sql with criteria
$criteria");
> } else {
>my $rc = $sth->execute or
>   &Error("Unable to execute statement", $sql);
> }

   # Because of the array, having criteria isn't a special case.
   my $rc = $sth->execute( @value ) or
  &Error( "Unable to execute statement, $DBI::errstr",
 "$sql with: " . join " ", map { "'$_'" } @value );

># Return a TRUE value just so that the variable which gets
assigned to the
> query execution on the caller
># program does not report an empty value. If we got this far then
all went
> OK
>1;
> }

--
Mac :})

RE: Value deletion

[Hardy Merrill]

Ronald, see my question "HM>> below".

>>> "Ronald J Kimball" <[EMAIL PROTECTED]> 03/10/05 10:39 AM >>>

> Javier, I might be way off here, but looks to me like this
statement:
> 
>my($sql) = "UPDATE GLOBALSettings SET Lang = '$::lang_code'";
> 
> has $::lang_code in *single quotes* - I'm not sure about this in
this
> context, but won't the single quotes will keep $::lang_code from
being
> interpolated?

The string is a double-quoted string.  The single-quotes are just
literal
characters inside a double-quoted string.  They have no special
meaning
here.

HM>> I understand the single quotes have no meaning here in this
double quoted string, but what about when the string is used in the
execute - will $::lang_code be interpolated properly even though it
is being surrounded by single quotes?  Can you explain to me how this
gets processed - seems like it should be a simple concept, but quoting
in situations like this has always been a stumbling block for me :-(

Thanks.

Hardy


This is still good advice though:

> Why not make that
> 
>   my($sql) = 'UPDATE GLOBALSettings SET Lang = ?';
> 
> and then add the $::lang_code as a parameter in the execute, like
> this:
> 
>  if ($criteria) {
>   my $rc = $sth->execute ( $::lang_code, $criteria ) or
>   &Error("Unable to execute statement",
> "$sql with criteria $criteria");
>  } else {
>   my $rc = $sth->execute ($::lang_code) or
>   &Error("Unable to execute statement",
> $sql);
>  }


Ronald

Re: Value deletion

[Hardy Merrill]

By saying "...of course the app bombs" isn't very descriptive.  You need
to be a
little more clear about just what the problem is and what is happening.
 Are you saying
that the value $::lang_code is not being written to the database?  Copy
and paste
in the exact error that comes up - this will help debug.

Javier, I might be way off here, but looks to me like this statement:

   my($sql) = "UPDATE GLOBALSettings SET Lang = '$::lang_code'";

has $::lang_code in *single quotes* - I'm not sure about this in this
context, but won't the single quotes will keep $::lang_code from being
interpolated?  Why not make that

  my($sql) = 'UPDATE GLOBALSettings SET Lang = ?';

and then add the $::lang_code as a parameter in the execute, like
this:

 if ($criteria) {
  my $rc = $sth->execute ( $::lang_code, $criteria ) or
  &Error("Unable to execute statement",
"$sql with criteria $criteria");
 } else {
my $rc = $sth->execute ($::lang_code) or
      &Error("Unable to execute statement",
$sql);
 }

HTH.

Hardy Merrill

>>> "Moreno, Javier" <[EMAIL PROTECTED]> 03/10/05 10:10 AM >>>
Hi all,

I have a script that writes a single record to a single row on a
GLOBALSettings table. I have a web page that does that and it does so
successfully. I go into the database with SQL Server Enterprise Manager
and see the value on the table. Then I use another page that takes
advantage of that value, it selects with no criteria the needed field
and the value gets wiped out from the DB and of course the app bombs. I
tried using a dbh->commit at the end of the update script but that
didn't work either. Does anyone know what is wrong? Sample code below:

 INSERT SCRIPT

# Insert into DB
my($sql) = "UPDATE GLOBALSettings SET Lang = '$::lang_code'";
my($criteria_var) = "";
my($criteria) = "";
my($reference) = &SQL_Update($sql, $criteria_var, $criteria);

# Disconnect from database
$::rc = $::dbh->disconnect or
&Error("Unable to disconnect from database.");

sub SQL_Update {

my ($sql, $criteria_field, $criteria) = @_;

# Append criteria if present
if ($criteria_field && $criteria) {
$sql.= " WHERE $criteria_field = ?";
}

# Debug the SQL statement (uncomment for testing)
&Debug("$sql");

# Prepare a SQL statement
my $sth = $::dbh->prepare( $sql ) or
&Error("Unable to prepare statement.");

# Execute the SQL statement prepared above, use with or without
criteria depending on if it is present
# or not
if ($criteria) {
my $rc = $sth->execute ( $criteria ) or
&Error("Unable to execute statement", "$sql with
criteria $criteria");
} else {
my $rc = $sth->execute or
&Error("Unable to execute statement", $sql);
}

# Return a TRUE value just so that the variable which gets
assigned to the query execution on the caller
# program does not report an empty value. If we got this far
then all went OK
1;
}
# SELECT SCRIPT

# Select current language
my ($sql)= "SELECT Lang FROM GLOBALSettings";
my ($reference) = &SQL_Select($sql);
my ($lang) = @[EMAIL PROTECTED];

sub SQL_Select {

my ($sql, $criteria_field, $criteria, $add_criteria) = @_;

# Debug the SQL statement (uncomment for testing)
&Debug("$sql");

# Append criteria if present
if ($criteria_field && $criteria) {
$sql.= " WHERE $criteria_field = ?";
}

# Any AND, OR or additional clauses
if ($add_criteria) {
$sql.= " $add_criteria";
}

# Prepare a SQL statement
my $sth = $::dbh->prepare( $sql ) or
&Error("Unable to prepare statement.");

# Execute the SQL statement prepared above, use with or without
criteria depending on if it is present
# or not
if ($criteria) {
my $rc = $sth->execute ( $criteria ) or
&Error("Unable to execute statement", "$sql with
criteria $criteria");
} else {
my $rc = $sth->execute or
&Error("Unable to execute statement", $sql);
}

# Return an array reference pointing to the results
return my $result = $sth->fetchall_arrayref;
}

Regards,

Javier Moreno

Re: connect to a remote Postgresql server using DBI

[Hardy Merrill]

Typically for every "client" machine, you would do as Alec
suggested - first install the client libraries for the specific
database being used, and then install the DBD:: for that 
specific database.

There is an alternative, and that is to use DBI::ProxyServer
and DBD::Proxy.  It's been several years since I've used it so
I'm not going to attempt to explain it as it is quite involved.
If you're interested, google for DBI Proxy and I'm sure you'll
find everything you need.

HTH.

Hardy Merrill

>>> "Gokul P. Nair" <[EMAIL PROTECTED]> 03/04/05 12:48 PM >>>
Thanks very much for ur response, i have one more
question though. Isn't there some kind of arrangement
like MS ODBC where u just download the driver for the
specific database and then create a DSN (data source
name) and write ur scripts (perl script or anything
else) to execute queries on the remote database. Do i
have to download and install the database driver as
well as client libraries for each computer running the
perl script to execute queries on the remote server?
What if i were to run these perl scripts on a windows
machine, would downloading and installing the driver
alone not suffice?

Thanks again.
Gokul
 
--- Alec Brecher <[EMAIL PROTECTED]> wrote:

> You need to install the PostgreSQL client libraries
> on the box running DBD::Pg.  The client libraries
> need to be installed before installing DBD::Pg.
> 
> See the bottom of this page for client installation
> directions.
>
http://www.postgresql.org/docs/7.4/static/install-procedure.html 
> 
> -Alec
> 
>  On Thu, 3 Mar 2005 21:08:27 -0800 (PST), Gokul P.
> Nair wrote:
> > i would like to connect to a remote postgresql 8.1
> server using a
> > perl script. The machine i'm trying to connect
> from is running
> > Debian and perl v5.6.1. In order to connect to the
> remote
> > postgresql server i assume i need DBI as well as
> DBD::Pg. well the
> > DBI installation went well but when i try to
> install DBD::Pg this
> > is what it gives me:
> >
> > debian:/usr/local/src/DBD-Pg-1.40# perl
> Makefile.PL Configuring Pg
> > Remember to actually read the README file! Path to
> pg_config? OS:
> > linux PostgreSQL version: ..
> >
> > 
> > WARNING! DBD::Pg no longer supports versions less
> than 7.2.
> > It is highly recommended that you upgrade
> PostgreSQL to a newer
> > version. 
> >
> > Using DBI 1.47 (for perl 5.006001 on i386-linux)
> installed in
> > /usr/local/lib/perl/5.6.1/auto/DBI/ Checking if
> your kit is
> > complete... Looks good
> > Note (probably harmless): No library found for
> -lpq Using DBI 1.47
> > (for perl 5.006001 on i386-linux) installed in
> > /usr/local/lib/perl/5.6.1/auto/DBI/ Writing
> Makefile for DBD::Pg
> >
> > Do i even need DBD::Pg installed on the machine
> which is trying to
> > access the postgresql server through a perl
> script? if so why is
> > the very first step i.e. "perl Makefile.pl"
> failing? if no, how do
> > i connect to the server and run queries using
> perl?
> >
> > thanks in advance for all ur help.
> >
> > Gokul
> >
> >
> > __
> Do You Yahoo!?
> > Tired of spam?  Yahoo! Mail has the best spam
> protection around
> > http://mail.yahoo.com 
> 
> 
> 





__ 
Celebrate Yahoo!'s 10th Birthday! 
Yahoo! Netrospective: 100 Moments of the Web 
http://birthday.yahoo.com/netrospective/

RE: Oracle error handling

[Hardy Merrill]

Susan, this is all explained in the excellent DBI perldocs which you can
view by doing

perldoc DBI

at a command prompt.  Look for the section titled "Transactions".

There's also website "www.perldoc.com" but it doesn't seem to be
responding at the moment :-(  Anyone know what's wrong with that site?

HTH.

Hardy Merrill

>>> "Reidy, Ron" <[EMAIL PROTECTED]> 02/24/05 8:55 AM >>>
Wrap your calls in an eval block.

$dbh = DBI->connect(...);
eval {
  $sth = $sbh->prepare("select * from mytable");
  $sth->execute;
  ...
};

if ($@) {   # an error occured
  print $@;
  # other actions 
}

--
Ron Reidy
Lead DBA
Array BioPharma. Inc.


-Original Message-
From:   susan lam [mailto:[EMAIL PROTECTED] 
Sent:   Thu 2/24/2005 6:08 AM
To: dbi-users@perl.org 
Cc: 
Subject:Oracle error handling
Hi,
I'm new to perl. I apologize if this is not the right
place to post newbie questions. 

If mytable does not exist, I would like to trap the
Oracle error message and write the message to an
Oracle table. I know how to write to a table but I do
not know how to trap the message. 
 
$dbh =
DBI->connect("dbi:Oracle:mydb",scott,tiger,{RaiseError
=> 1, AutoCommit => 0}) || die "Database connection
failed: $DBI::errstr";
$sql = qq {select * from mytable};
$sth = $dbh->prepare($sql);
$sth->execute;
$dbh->disconnect;

Also, if mytable does exist, how can I obtain the row
count returned from the above query and assign it to a
variable?

I appreciate any help I can get. 

thanks.

susan



__ 
Do you Yahoo!? 
Yahoo! Mail - Helps protect you from nasty viruses. 
http://promotions.yahoo.com/new_mail 




This electronic message transmission is a PRIVATE communication which
contains
information which may be confidential or privileged. The information is
intended 
to be for the use of the individual or entity named above. If you are
not the 
intended recipient, please be aware that any disclosure, copying,
distribution 
or use of the contents of this information is prohibited. Please notify
the
sender  of the delivery error by replying to this message, or notify us
by
telephone (877-633-2436, ext. 0), and then delete it from your system.

RE: finding and inserting

[Hardy Merrill]

I'm no database expert (I'm probably way off here), but doesn't that
need to be more like this:

   INSERT INTO district2
   SELECT employee_id, ...
   FROM district1

And, that still doesn't address the issue of duplicate employee id's. 
I think to address that problem you'd have to define a unqiue index in
the district2 table that includes just the employee_id column.  Assuming
that has been done, I don't know how that will affect the INSERT
statement above, since there are presumably many duplicate employee id's
- will the first dup cause the above INSERT to die?

>>> <[EMAIL PROTECTED]> 02/11/05 9:16 AM >>>
This would definitely be the better solution but assuming you are
constrained by an exiting schema you can accomplish your goal with a
simple insert statement.

The following almost complete insert statement will do what I think
you
want to do:

INSERT INTO district_table
SELECT 'District 2', employee_id, ...
FROM district_table
WHERE district = 'District 1'

Hope this help
Mark

-Original Message-
From: amonotod [mailto:[EMAIL PROTECTED] 
Sent: Friday, February 11, 2005 9:00 AM
To: Robert
Cc: dbi-users@perl.org 
Subject: Re: finding and inserting


> From: "Robert" <[EMAIL PROTECTED]>
> Date: 2005/02/11 Fri AM 07:10:25 CST
> 
> I have about 200 users in "district 1". I need to add each of those 
> users into "district 2" using the same information that they have in

> "district 1". I also need to make sure they are only inserted 1 time.

> They have a unique employeeID, so I know I can use that to make sure

> that they are inserted 1 time.

This is simply an opinion, so take it at face value, okay?  I think
that
you need to redesign your database, so that users and disctricts are
not
directly related, and then use a lookup table to correlate them
together.  That way, no matter which district a user is allowed to be
"in", that user will have only one ID and one password.  Below is code
for table creation in MSSQL, for one each simple employee table,
district table, and employee<->district lookup table...

CREATE TABLE [dbo].[Districts] (
[DistID] [int] NOT NULL ,
[DistName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[DistDesc] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[EmpDistLkp] (
[EmpID] [int] NOT NULL ,
[DistID] [int] NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Employees] (
[EmpID] [int] NOT NULL ,
[EmpFName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[EmpLName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[EmpPwd] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Districts] ADD 
CONSTRAINT [PK_Districts] PRIMARY KEY  CLUSTERED 
(
[DistID]
)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Employees] ADD 
CONSTRAINT [PK_Employees] PRIMARY KEY  CLUSTERED 
(
[EmpID]
)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[EmpDistLkp] ADD 
CONSTRAINT [FK_EmpDistLkp_Districts] FOREIGN KEY 
(
[DistID]
) REFERENCES [dbo].[Districts] (
[DistID]
),
CONSTRAINT [FK_EmpDistLkp_Employees] FOREIGN KEY 
(
[EmpID]
) REFERENCES [dbo].[Employees] (
[EmpID]
)
GO


v/r,
amonotod


--

`\|||/ amonotod@| sun|perl|windows
  (@@) charter.net  | sysadmin|dba
  ooO_(_)_Ooo
  _|_|_|_|_|_|_|_|

RE: finding and inserting

[Hardy Merrill]

Another way to do it, using DBI, conceptually (pseudo-code) would be to
  * define a unique index on the district 2 table that includes just
the
   employee id.  After you've done this, then if you try to insert
a row into
   the district 2 table with an employee id that already exists in
that table,
   Oracle will reject the insert with some kind of a "duplicate
key" error.
  * have 2 statement handles - one for reading from district 1, and the
other
 for writing to district 2.
  * loop - fetch from district 1
  eval {
### do INSERT into district 2 "execute" here - if it
fails, it will
### fill $@ with the error message and control will
transfer to 1st 
### statement after the eval.
  }
  if ($@) {
if ($@ =~ /duplicate/) {
 ### error was caused by trying to insert a
duplicate - just bypass
### this one...
}
else {
 ### error was NOT caused by trying to insert a
duplicate - we
 ### better rollback now...
}
  }
 end loop

Read about Transactions and error handling in the excellent DBI
perldocs by doing

perldoc DBI

at a command prompt.

HTH.

Hardy Merrill

>>> "Reidy, Ron" <[EMAIL PROTECTED]> 02/11/05 9:14 AM >>>
There are so many ways to do this.  Below is a PL/SQL way:

#
#  not tested
#

my $sth = $dbi->prepare(qq{
  BEGIN

FOR d IN (SELECT * FROM district1)
LOOP
  BEGIN
INSERT INTO district2 (column_list)
VALUES (d);
  EXCEPTION
WHEN dup_val_on_index THEN
  NULL;
  END;
END LOOP;
  END;
}) || die $DBI::errstr;

You could also change the above to use bulk processing (array inserts)
in PL/SQL.  I leave this investigation to you and your DBA.

--
Ron Reidy
Lead DBA
Array BioPharma, Inc.

-Original Message-
From:   Robert [mailto:[EMAIL PROTECTED] 
Sent:   Fri 2/11/2005 6:10 AM
To: dbi-users@perl.org 
Cc: 
Subject:Re: finding and inserting
Let me see if I can explain it better today.  :-)

I have about 200 users in "district 1". I need to add each of those
users 
into "district 2" using the same information that they have in
"district 1". 
I also need to make sure they are only inserted 1 time. They have a
unique 
employeeID, so I know I can use that to make sure that they are
inserted 1 
time.


"Ron Reidy" <[EMAIL PROTECTED]> wrote in message 
news:[EMAIL PROTECTED]
What do you mean by 'insert that user into a new district'?  Do you
mean 
create an oracle account?

--
Ron Reidt
Lead DBA
Array BioPharma, Inc.


-Original Message-
From: Robert [mailto:[EMAIL PROTECTED] 
Sent: Thu 2/10/2005 11:26 AM
To: dbi-users@perl.org 
Cc:
Subject: finding and inserting
I am not sure how to go about this.

I have an Oracle database with users in a district. Every time I have
to add
a new district I have to reset the password. I thought, now why don't I
use
Perl to just copy the users current info and insert that into the new
district with the name of the district changing.

What I need to do is loop through the database and find a user, insert
that
user into the new district, find next user, make sure that user is not
already in the new district to prevent duplicates, insert user into
new
district changing the district information.  There will be exceptions
but I
think I can handle those if I can get started on the first part.

My Perl and DBI skills are not quite up to this...so help would be
appreciated.

Robert






This electronic message transmission is a PRIVATE communication which 
contains
information which may be confidential or privileged. The information is

intended
to be for the use of the individual or entity named above. If you are
not 
the
intended recipient, please be aware that any disclosure, copying, 
distribution
or use of the contents of this information is prohibited. Please notify
the
sender  of the delivery error by replying to this message, or notify us
by
telephone (877-633-2436, ext. 0), and then delete it from your system.






This electronic message transmission is a PRIVATE communication which
contains
information which may be confidential or privileged. The information is
intended 
to be for the use of the individual or entity named above. If you are
not the 
intended recipient, please be aware that any disclosure, copying,
distribution 
or use of the contents of this information is prohibited. Please notify
the
sender  of the delivery error by replying to this message, or notify us
by
telephone (877-633-2436, ext. 0), and then delete it from your system.

Re: no rows selected

[Hardy Merrill]

Mark, you would typically do something like this:

   my $ct_rows_processed = 0;
   while (@fetch = $sth->fetchrow) {
 $ct_rows_processed++;

 ### do stuff with a row of data ###

   } ### end while

   If ($ct_rows_processed = 0) {
### perform "no rows selected" tasks here ###
   }

HTH.

Hardy Merrill

>>> Mark Martin <[EMAIL PROTECTED]> 01/28/05 7:58 AM >>>
Hi,
I'm running a test to see whether certain criteria in a SELECT
statement 
return record or not. If I get a "no rows selected" from SQL then I
want to 
perform a specific action . But I don't how perform the test for "no
rows 
selected " ?

$sql = qq{SELECT RECORD FROM TABLE WHERE FIELD = ? } ;
$sth = $dbh->prepare($sql) ;
$sth->execute($variable) ;

while (@fetch = $sth4->fetchrow)
{   
 if ("no rows selected " .?? )  ### Don't
know 
how to test this
  {
 #perform my tasks
  } 

Regards,
Mark

Re: how to connect postgres database

[Hardy Merrill]

Like Michael has nicely suggested to you twice now,

  1. read the documentation, and after you've read the documentation,
  2. try to do a connect yourself using examples in the documentation,
and
  *then* if you still have problems,
  3. post a message to dbi-users@perl.org, and in your message
 - post the code that you tried
 - post the error that you got

If you do these things then people will be happy to help you.  But we
have to know that you've at least read the documentation and tried what
the documentation suggests.

HTH.

Hardy Merrill

>>> Michael A Chase tech <[EMAIL PROTECTED]> 01/21/05 5:47 AM >>>
On 01/21/2005 06:59 AM, sudheer raghav said:

>   am new to perl.
>  how to connect postgresql database to perl.

I replied to an identical message from you dated 01/17/2005 11:43 PM
with:

=
Please look at http://www.catb.org/~esr/faqs/smart-questions.html .

For DBI,
   http://search.cpan.org/~timb/DBI/DBI.pm 
   http://search.cpan.org/~timb/DBI/ 
   http://search.cpan.org/~timb/DBI/lib/DBI/FAQ.pm 

For DBD::Pg,
   http://search.cpan.org/author/RUDY/DBD-Pg/Pg.pm 
   http://search.cpan.org/author/RUDY/DBD-Pg/ 

I am not an expert on DBD::Pg, so replying directly to me is useless. 
Reply to the list so someone else can help you.
=

I will ignore any further emails from you.  The correct forum is 
dbi-users@perl.org .

-- 
Mac :})
** I usually forward private questions to the appropriate mail list.
**
Ask Smarter: http://www.catb.org/~esr/faqs/smart-questions.html 
Give a hobbit a fish and he eats fish for a day.
Give a hobbit a ring and he eats fish for an age.

RE: DBI connections to oracle sometimes hang

[Hardy Merrill]

I've never done this, but Ron's advice ($SIG{ALRM}) is in line with the
DBI perldocs.

Here is a snippet from 'perldoc DBI' - I searched for "timeout":

  Signal Handling and Canceling Operations
The first thing to say is that signal handling in Perl is
currently
*not* safe. There is always a small risk of Perl crashing and/or
core
dumping when, or after, handling a signal. (The risk was reduced
with
5.004_04 but is still present.)

The two most common uses of signals in relation to the DBI are for
canceling operations when the user types Ctrl-C (interrupt), and
for
implementing a timeout using "alarm()" and $SIG{ALRM}.

To assist in implementing these operations, the DBI provides a
"cancel"
method for statement handles. The "cancel" method should abort the
current operation and is designed to be called from a signal
handler.

However, it must be stressed that: a) few drivers implement this at
the
moment (the DBI provides a default method that just returns
"undef");
and b) even if implemented, there is still a possibility that the
statement handle, and possibly the parent database handle, will not
be
usable afterwards.

If "cancel" returns true, then it has successfully invoked the
database
engine's own cancel function. If it returns false, then "cancel"
failed.
    If it returns "undef", then the database engine does not have
cancel
implemented.

HTH.

Hardy Merrill

>>> "Reidy, Ron" <[EMAIL PROTECTED]> 01/20/05 12:08 PM >>>
You could set up a SIG{ALARM} and the attempt the connection.

Or better still, get your DBAs to fix the problems in the DBs.

-
Ron Reidy
Lead DBA
Array BioPharma, Inc.


-Original Message-
From: David Goodman [mailto:[EMAIL PROTECTED] 
Sent: Thursday, January 20, 2005 10:01 AM
To: dbi-users@perl.org 
Subject: DBI connections to oracle sometimes hang


On occassions when an oracle instance is having
trouble, my DBI connections hang. For example, if the
oracle archive log destination is full or the OS
itself is hung, the DBI connection will also hang.

With DBI's trace level 3, it remains hung on the
following:

-> connect for DBD::Oracle::dr
(DBI::dr=HASH(0x40245c28)~0x4055c1f4 'DS1' 'su' 
HASH(0x40553c28))

The program itself connects to a list of Oracle
servers and works fine except when the oracle instance
itself is in difficulties. This stops the processing
of the rest of the list, and does not even return an
error message.

At this site, there are a number of perl programs
performing various functions across multiple
dataservers. This issue has surfaced with Oracle but
not with Sybase.

Is there a way to have connections timeout when they
do not respond?

regareds,

David

This electronic message transmission is a PRIVATE communication which
contains
information which may be confidential or privileged. The information is
intended 
to be for the use of the individual or entity named above. If you are
not the 
intended recipient, please be aware that any disclosure, copying,
distribution 
or use of the contents of this information is prohibited. Please notify
the
sender  of the delivery error by replying to this message, or notify us
by
telephone (877-633-2436, ext. 0), and then delete it from your system.

Re: 2 cursors? at the same time

[Hardy Merrill]

Hi Nina,

A couple of things:
  1. Where in the perldocs did you find the part about setting
AutoCommit
  off would fix your problem?  Was that in DBD::Ingres?

  I haven't used DBI in a while, but I don't remember having a
problem
 using a database connection with one statement handle to read
from
 one table, and a different statement handle to write to a
different table -
  so I'm curious why setting AutoCommit off would affect that. 
I've
  never used DBD::Ingres so if the info was in there then I can
understand.

  2. I've attached new_make_action.pl with a few sections labelled
   ##
   ### HM ###
   ##

   to show you how you can do things a little differently using
   placeholders - prepare your insert once, and then when you
   need to insert, your insert doesn't have to prepare the same
   basic insert over and over again - you can just execute it,
giving
   the values for the placeholders.  This method is much more
   efficient.  Read about "Placeholders" in the perldocs by doing
 
   perldoc DBI

at a command prompt.

>>> Nina Markova <[EMAIL PROTECTED]> 01/19/05 3:11 PM >>>
Hi,

How to use select and insert at  the same time?

I select information from one table, process and reorganize it and want
to 
insert it in another table.  I receive error :

DBD::Ingres::st execute failed: E_US08B4 No MST is currently in
progress, cannot 
declare another cursor.
(Wed Jan 19 14:55:58 2005) at ../make_action.pl line 69.
DBD::Ingres::st fetchrow_hashref failed: E_LQ0058 Cursor 
's0_ticketdescriptionfro' not open for 'fetch' command. at
../make_action.pl 
line 123.
Use of uninitialized value in concatenation (.) or string at
../make_action.pl 
line 158.
DBI::db=HASH(0x14f070)->disconnect invalidates 1 active statement
handle (either 
destroy statement handles or call finish on them before disconnecting)
at 
../make_action.pl line 160.

Usually I use 
 1) perl dbi script for processing which writes to a text file f.txt
 2) load data with 'copy table from f.txt' statement
 
 This time information I reorganize is a large text field, which
includes new 
lines. When I'm trying to load with 'copy from ...', new lines blow
Ingres 
eroors. I don't know how to preserve this formating  and at the same
time be 
able to load data, so decided to insert directly from same perl
script.
 
 Code attached.
 
 This is the exaple of formating I'd like to keep:
 --
Unused partition found, added in to directory tree at:

/web1/htdocs/nehp/hplots

This does two things, one keeps the file structure the same for
applications 
writing heliplot files and two gives the operations phone a break from
/web1 
diskspace messages for atleast a handful of months.

Thanks in advance,

Nina Markova
DBA


new_make_action.pl
Description: Perl program

Re: Problem with connecting to an Oracle database using Perl

[Hardy Merrill]

It's probably worth pointing out here that the use of the BEGIN block
*IS* described in the DBD::Oracle perldocs - as the perldocs are
included when you install any module, after you install DBD::Oracle you
can do

   perldoc DBD::Oracle

to read the excellent perldocs.  Look for the paragraphs titled "Oracle
Environment Variables" and "Connection Examples Using DBD::Oracle".

Hardy

>>> "Hardy Merrill" <[EMAIL PROTECTED]> 01/19/05 7:46 AM >>>
Hi Charles,

Try setting your environment variables in a BEGIN block, like this:

#!/usr/bin/perl -w
use strict;
BEGIN {
  $ENV{'NLS_LANG} = 'american';
  $ENV{ORACLE_HOME} = 'D:\Oracle\ora92';
}

### the rest of your code goes here ###

I haven't done Perl/DBI/Oracle in quite a while, but that's what I
remember doing.  I think that must be spelled out either in the DBI
perldocs or the DBD::Oracle perldocs - to use a BEGIN block to set
environment variables that is ;-)

Hardy Merrill

>>> "Charles Lawrence" <[EMAIL PROTECTED]> 01/18/05 2:54
PM
>>>
Gentlemen:

 

I have struggled with this problem for a while now with no success at
finding the answer, so I am asking for help from anyone who might know
the solution.

 

I am using Windows XP, ActiveState Perl 5.8.4, Oracle 9.2.0.1.0 client
and server, DBI (1.45) and DBD-Oracle (1.16) installed as ppm packages
from ftp.esoftmatic.com/outgoing/DBI/5.8.4/ 

 

I do not have a problem connecting to the remote database using the
Oracle Enterprise Manager program from my PC.  However, I am getting
the
following error when I try to connect to the database from perl:

 

DBI connect('host=10.1.10.61;sid=ssdb','system',...) failed: ERROR
OCIInitialize. Check ORACLE_HOME and NLS settings etc. at
D:\PerlScripts\ora1.

pl line 17

Unable to connect to database: ERROR OCIInitialize. Check ORACLE_HOME
and NLS settings etc.

 

My perl script is:

 

#!/usr/bin/perl

 

use strict;

use DBI;

use DBD::Oracle qw(:ora_session_modes); # ORA_SYSDBA & ORA_SYSOPER
constants

 

my ($dsn,$usr,$pwd);

my ($dbh);

 

$ENV{NLS_LANG} = 'american';

$ENV{ORACLE_HOME} = 'D:\Oracle\ora92';

 

# connect to remote Oracle DB

$dsn='DBI:Oracle:host=10.1.10.61;sid=ssdb';

$usr='system';

$pwd='manager';

$dbh = DBI->connect($dsn,$usr,$pwd) || die "Unable to connect to
database: $DBI::errstr\n";

 

I have used NLS_LANG settings of 'american_america.we8iso8859p1' and
'american_america.utf8' to no avail.  I know the ORACLE_HOME to be
correct.  I cannot figure out why I am getting the OCIInitialize
error.
Any help will be greatly appreciated.  Thanks in advance.

 

Charles Lawrence

[EMAIL PROTECTED] 

 

Re: Problem with connecting to an Oracle database using Perl

[Hardy Merrill]

Hi Charles,

Try setting your environment variables in a BEGIN block, like this:

#!/usr/bin/perl -w
use strict;
BEGIN {
  $ENV{'NLS_LANG} = 'american';
  $ENV{ORACLE_HOME} = 'D:\Oracle\ora92';
}

### the rest of your code goes here ###

I haven't done Perl/DBI/Oracle in quite a while, but that's what I
remember doing.  I think that must be spelled out either in the DBI
perldocs or the DBD::Oracle perldocs - to use a BEGIN block to set
environment variables that is ;-)

Hardy Merrill

>>> "Charles Lawrence" <[EMAIL PROTECTED]> 01/18/05 2:54 PM
>>>
Gentlemen:

 

I have struggled with this problem for a while now with no success at
finding the answer, so I am asking for help from anyone who might know
the solution.

 

I am using Windows XP, ActiveState Perl 5.8.4, Oracle 9.2.0.1.0 client
and server, DBI (1.45) and DBD-Oracle (1.16) installed as ppm packages
from ftp.esoftmatic.com/outgoing/DBI/5.8.4/ 

 

I do not have a problem connecting to the remote database using the
Oracle Enterprise Manager program from my PC.  However, I am getting
the
following error when I try to connect to the database from perl:

 

DBI connect('host=10.1.10.61;sid=ssdb','system',...) failed: ERROR
OCIInitialize. Check ORACLE_HOME and NLS settings etc. at
D:\PerlScripts\ora1.

pl line 17

Unable to connect to database: ERROR OCIInitialize. Check ORACLE_HOME
and NLS settings etc.

 

My perl script is:

 

#!/usr/bin/perl

 

use strict;

use DBI;

use DBD::Oracle qw(:ora_session_modes); # ORA_SYSDBA & ORA_SYSOPER
constants

 

my ($dsn,$usr,$pwd);

my ($dbh);

 

$ENV{NLS_LANG} = 'american';

$ENV{ORACLE_HOME} = 'D:\Oracle\ora92';

 

# connect to remote Oracle DB

$dsn='DBI:Oracle:host=10.1.10.61;sid=ssdb';

$usr='system';

$pwd='manager';

$dbh = DBI->connect($dsn,$usr,$pwd) || die "Unable to connect to
database: $DBI::errstr\n";

 

I have used NLS_LANG settings of 'american_america.we8iso8859p1' and
'american_america.utf8' to no avail.  I know the ORACLE_HOME to be
correct.  I cannot figure out why I am getting the OCIInitialize
error.
Any help will be greatly appreciated.  Thanks in advance.

 

Charles Lawrence

[EMAIL PROTECTED] 

 

Re: passing dbh

[Hardy Merrill]

This thread is getting a bit OT, but...

>>> David <[EMAIL PROTECTED]> 01/10/05 12:36PM >>>
On Mon, Jan 10, 2005 at 12:28:44PM -0500,
[EMAIL PROTECTED] wrote:

Passing arguments explicitly by reference is only useful is you want
to
pass an array or hash as a single entity.

HM>> I respectfully disagree :)   It was already pointed out in this
thread that the argument list gets flattened out, so hashes and arrays
lose their identity when passed as any argument other than the first,
since the receiving hash and array arguments in the subroutine can't
know how many items belong to them and will swallow up the rest of the
arguments.  So IMHO the simple rule of thumb is to pass each and every
argument as a scalar:

  * pass a scalar as itself  (by value)
  * pass a reference to an array (which is a scalar) instead of the
array itself
  * pass a reference to a hash (which is a scalar) instead of the hash
itself

this way each argument is a scalar and there are no hashes or arrays in
the argument list to get flattened out.

This is now probably as clear as mud ;-)

Hardy Merrill

Re: Problem with "while/fetchrow_array"

[Hardy Merrill]

Ron, someone else will correct me if I'm wrong, but long ago this bit me
also.   I believe I remember the answer being that "SHOW" is a mysql
client command - that is not a valid SQL command.

I'm not sure if mysql has a mysql-only sql command you can give to get
the names of the databases - or maybe they have some system table that
contains the names of the databases and you can SELECT from that.  Check
the mysql docs to see if there's maybe a mysql specific SQL command you
can give - similar to SHOW but not specific to the mysql client - to
list databases.

If you have the "Programming the Perl DBI" book, I believe there's a
place in there that describes how to get the names of available drivers
and datasources (which may contain the database names).   I'd also read
the perldocs for DBI and DBD::mysql to see what $dbh and $sth methods
are available - maybe one of them will give you a list of database
names.

Sorry I can't be more help - I'm not using DBI nor MySQL regularly :-( 
Not that I don't want to...

Hardy Merrill

>>> "Ron Wingfield" <[EMAIL PROTECTED]> 12/29/04 01:07PM >>>
Hello everyone,

I'm having difficulty solving this code problem.  I can load a popup
list of states from a "states" table, but I can't seem to get-it-right
when I execute a SHOW, rather than a SELECT query.  Actually, I want to
create a popup list of all DATABASES through the CGI.  From the command
line, this works:

  mysql> SHOW DATABASES;
  +---+
  | Database  |
  +---+
  | cookbook  |
  | test  |
  +---+
  2 rows in set (0.00 sec)

As you can see, there are two databases on my server, "cookbook" and
"test".  I prepare the query request, and execute it without any error
complaints.  The while/fetchrow_array returns only one iteration or row,
. . .apparently the last row which contains the "test" database, and
ultimately my popup list only contains the "test" database row.  I'm not
getting any error from the fetchrow_array function; however I only see
one diagnostic display of "GOT HERE! db_name=test".  Apparently, the
while loop has only looped one time.  Regarding the following code, why
is "cookbook" missed?

  $sth=$dbh->prepare("SHOW DATABASES;");
  $sth->execute( );
  DBI_error("Query execution to SHOW DATABASES failed.","7");
  while($db_name = $sth->fetchrow_array( ))
  {
  DBI_error("Fetchrow_array failed.","7");# Test/no
exit-on-error.
  push(@db_name_values, $db_name);# Save each value in
array.
  DBI_error("GOT HERE!  db_name=$db_name","1");  TEMP!
   # When used with option "1",
^
   # this always prints a
diagnostic "trace" message. 
  }
  print # Start of print block
  $cgi-> popup_menu
  (
  -name=>'db_name',
  -values=>[EMAIL PROTECTED]
  ),

Thanks and OTTF,
Ron Wingfield

FreeBSD 4.8  --  Apache http 2.0.28 -- MySQL client/server 4.1.7
Perl 5.8.5 -- p5-DBD-mysql-2.9004 driver -- p5-DBI-1.46

Re: Evaluated "require $database" not found.

[Hardy Merrill]

Ron, my comments below as HM>>  Sorry for the ridiculous (lack of)
quoting - old version of Groupwise at work :-(

Hardy Merrill

>>> "Ron Wingfield" <[EMAIL PROTECTED]> 12/27/04 07:41PM >>>
Hello everyone,

I do appreciate all of your suggestions.  Actually, now I do have a
good handle on the problems.  I was in a "couldn't see the forest for
the trees" scenario.  I've gone through my three proof-of-concept Perl
programs and they are working very well.  There is still a lot of
functionality to add to the programs (such as the important function of
returning the MySQL query results back to the "middle" program, i.e.,
the appication user interface program) , but basically I have the
following in place:

DB_Select.pl   calls   Contacts_01.plcallsDBI_API_01.pl

The DBI_API_01.pl program is totally generic and handles all database
maintenance I/O (Inquire, Update, Insert, and Delete) for any
database/table.  As it turns out, the parameter passing problem was a
non-issue once I realized that I was using a technique of Mr. Paul
DuBois that incorporated a custom Perl library module to handle
connections to (some of) his Cookbook Recipe examples.

Also very frustrating is this little nuance:

  $dsn="DBI:mysql:host=$host_name;database=$db_name";   # CORRECT
SYNTAX!
  $dbh=DBI->connect($dsn, $userid, $passwd,{PrintError=>0,
RaiseError=>0});
  DBI_error("Could not connect to $dsn."); # Test/exit on error.

HM>> I've never done error handling like this before.  Looks like you
are *always* calling the DBI_error subroutine - I'm curious to know what
DBI_error contains for error handling code.  As you may know, typical
error handling involves an "or" (or "||"), something like this: 

 $dbh=DBI->connect($dsn, 
   $userid,
   $passwd,
  {PrintError=>0,
RaiseError=>0}
  ) || die "Could not connect to $dsn: $DBI::errstr";

???  I'm familiar with error trapping as described in "perldoc DBI" -
here's a snippet from the paragraph titled "Transactions":
=
  Transactions
Transactions are a fundamental part of any robust database system.
They
protect against errors and database corruption by ensuring that
sets of
related changes to the database take place in atomic (indivisible,
all-or-nothing) units.

This section applies to databases that support transactions and
where
"AutoCommit" is off. See "AutoCommit" for details of using
"AutoCommit"
with various types of databases.

The recommended way to implement robust transactions in Perl
applications is to use "RaiseError" and "eval { ... }" (which is
very
fast, unlike "eval "...""). For example:

  $dbh->{AutoCommit} = 0;  # enable transactions, if possible
  $dbh->{RaiseError} = 1;
  eval {
  foo(...)# do lots of work here
  bar(...)# including inserts
  baz(...)# and updates
  $dbh->commit;   # commit the changes if we get this far
  };
  if ($@) {
  warn "Transaction aborted because $@";
  # now rollback to undo the incomplete changes
  # but do it in an eval{} as it may also fail
  eval { $dbh->rollback };
  # add other application on-error-clean-up code here
  }
=

A lot of good info there, but the main point about error trapping is
this:
  $dbh->{RaiseError} = 1;
  eval {
### dbi statements here ###
  };
  if ($@) {
### if any dbi statements in an eval fail with a "die"
when
### RaiseError is on(1), then the die message gets put
### into $@, and control passes to the next statement
### after the eval.
  }

Not that any of this will affect your errant connect statement, but I
thought I would include it here just in case you weren't aware of it
;-)

Hardy Merrill

. . .if the construct of the $dsn variable includes a space as in 
  $dsn="DBI:mysql:host=$host_name;database=$db_name"; # DO NOT USE
SPACE! 
then the connect will fail, but the DBI_error( ) handler will not trap
the error and report the failed attempt to connect.  The program will
continue processing and the DBI_error( ) handler will report errors if
subsequent SELECTS, are attempted on a valid table in the database, for
example:

  DBI Error 1046:  No database selected
  Query execution to select from states failed. 
  Process terminated.

Notice that this was a failure of an operation to load a pop-up
selection list of states fro

Re: USE Statement

[Hardy Merrill]

Ron, your message did make it to the list :)

And from 'perldoc -q use':

==
  What's the difference between require and use?
Perl offers several different ways to include code from one file
into
another. Here are the deltas between the various inclusion
constructs:

1)  do $file is like eval `cat $file`, except the former
1.1: searches @INC and updates %INC.
1.2: bequeaths an *unrelated* lexical scope on the eval'ed
code.

2)  require $file is like do $file, except the former
2.1: checks for redundant loading, skipping already loaded
files.
2.2: raises an exception on failure to find, compile, or
execute $fi
le.

3)  require Module is like require "Module.pm", except the
former
3.1: translates each "::" into your system's directory
separator.
3.2: primes the parser to disambiguate class Module as an
indirect o
bject.

4)  use Module is like require Module, except the former
4.1: loads the module at compile time, not run-time.
4.2: imports symbols and semantics from that package to the
current
one.

In general, you usually want "use" and a proper Perl module.
==

>From that it sounds to me like 'require' might be what you want,
instead of 'use'.

HTH.

Hardy Merrill

>>> "Ron Wingfield" <[EMAIL PROTECTED]> 12/23/04 12:40PM >>>
I would like to the specify the USE argument dynamically as for
example:

 USE $database;

Apparently, the scalar is not recognized (compile error).  Is it
possible to
provide a dynamic insertion such that a user could specify via a
run-time
parameter, which database the program is to use?

BTW, I'm a new forum user.  Can someone please respond is this message
appears on the forum?

Thanks,
Ron W.

RE: Please Help !!! Unable to install DBD-Oracle ( I am using perl 5.6 on solaris 8)

[Hardy Merrill]

Ron, I could be way off here, but the OP may(?) already have Perl
installed on his Solaris systsem - he may just need DBD::Oracle.  I
think the original issue was that he tried to use *PPM* to install
DBD::Oracle - is that right Vivekananda?  I don't have any Solaris
experience, but if this is the case, he probably just needs to download
the DBD::Oracle .tar.gz, read the README(is there a readme for Solaris?)
and build it.

I just wanted to throw this out there as a possibility, before going
down the download and install Perl road ;-)

Hardy

>>> "Reidy, Ron" <[EMAIL PROTECTED]> 12/17/04 08:50AM >>>
Sure.  

1.  Download perl from http://www.perl.com 
2.  Unpack (tar xvzf stable.tar.gz)
3.  Read the README file

After you have built and installed Perl, google "install perl modules
using cpan"

Remeber, **ALWAYS** read the README* files for all modules before you
install them or if you have problems/issues during the make/make
test/make install phases.

--
Ron Reidy
Lead DBA
Array BioPharma, Inc.


-Original Message-
From:   [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] 
Sent:   Fri 12/17/2004 2:19 AM
To: Reidy, Ron
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED];
[EMAIL PROTECTED] 
Subject:RE: Please Help !!! Unable to install DBD-Oracle ( I am
using perl 5.6 on solaris 8)

Hi Reidy

Thanks for the advice!!!

But I have never build perl,DBI and DBD before.

Can you kindly let me know 'the process of building perl,DBI and DBD'.

Best Regards,
Vivekananda M
BP Operate - BeyondBP

Phone: 00-91-80-28520408 (Ext #5359)
Wipro Technologies
Email: [EMAIL PROTECTED] 


Helpdesk URL: ehelpline.bpweb.bp.com
Helpdesk Phone: 00-91-80-51381734/00-91-80-51381748/00-91-9845796558





-Original Message-
From: Reidy, Ron [mailto:[EMAIL PROTECTED] 

Sent: Thursday, December 16, 2004 9:23 PM
To: Vivekananda M (WT01 - UTILITIES); [EMAIL PROTECTED] 
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] 
Subject: RE: Please Help !!! Unable to install DBD-Oracle ( I am using
perl 5.6 on solaris 8)

You will need (I think) to build Perl from scratch.  Check which C
compiler was used to build Perl.  If it is not the C compiler you have
on your system, you will need to build it yourself.  After that you
will
need to build DBI and DBD::Oracle yourself.

-
Ron Reidy
Lead DBA
Array BioPharma, Inc.


-Original Message-
From: [EMAIL PROTECTED] 
[mailto:[EMAIL PROTECTED] 
Sent: Wednesday, December 15, 2004 7:11 PM
To: [EMAIL PROTECTED] 
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] 
Subject: RE: Please Help !!! Unable to install DBD-Oracle ( I am using
perl 5.6 on solaris 8)



Thanks Jeff!!!

Yes you were right ...I installed DBI from Active State's site
earlier.

Can you kindly let me know a place where I can get DBD-Oracle for
solaris 8 and perl 5.6 using PPM??? Please help me in this regard.

Thanks again!!

Best Regards,
Vivekananda M
BP Operate - BeyondBP

Phone: 00-91-80-28520408 (Ext #5359)
Wipro Technologies
Email: [EMAIL PROTECTED] 


Helpdesk URL: ehelpline.bpweb.bp.com
Helpdesk Phone: 00-91-80-51381734/00-91-80-51381748/00-91-9845796558





-Original Message-
From: Jeff Urlwin [mailto:[EMAIL PROTECTED] 

Sent: Wednesday, December 15, 2004 8:49 PM
To: Vivekananda M (WT01 - UTILITIES); [EMAIL PROTECTED];
[EMAIL PROTECTED] 
Cc: [EMAIL PROTECTED] 
Subject: RE: Please Help !!! Unable to install DBD-Oracle ( I am using
perl 5.6 on solaris 8)

> -Original Message-
> From: [EMAIL PROTECTED] 

> [mailto:[EMAIL PROTECTED] 

> Sent: Tuesday, December 14, 2004 8:28 PM
> To: [EMAIL PROTECTED]; [EMAIL PROTECTED] 
> Cc: [EMAIL PROTECTED] 
> Subject: Re: Please Help !!! Unable to install DBD-Oracle ( I

> am using perl 5.6 on solaris 8)
>

>

>


The esoftmatic.com (my) site only supplies binaries for Win32, not
Solaris and not for
Perl 5.6.

You will need to build DBD-Oracle yourself.  I believe, before, you
used
PPM to install
DBI from ActiveState's site, not from my site.  That would explain why
you are having
issues.

Regards,

Jeff




Confidentiality Notice


The information contained in this electronic message and any
attachments
to this message are intended
for the exclusive use of the addressee(s) and may contain confidential
or privileged information. If
you are not the intended recipient, please notify the sender at Wipro
or
[EMAIL PROTECTED] immediately
and destroy all copies of this message and any attachments.

This electronic message transmission is a PRIVATE communication which
contains
information which may be confidential or privileged. The information
is
intended

to be for the use of the individual or entity named above. If you are
not the

intended recipient, please be aware that any disclosure, copying,
distribution

or use of the contents of this information is prohibited. Please
notify
the
sender  of the delivery error by replying to this message, or notify
us
by
telephone (877-633-2436, ext. 0), and then delete it f

RE: Newbie request

[Hardy Merrill]

My comments are below designated by [hm]...

Hardy Merrill

>>> "Reidy, Ron" <[EMAIL PROTECTED]> 12/16/04 05:43PM >>>
See below ...

-
Ron Reidy
Lead DBA
Array BioPharma, Inc.


-Original Message-
From: Follett, Robert [mailto:[EMAIL PROTECTED] 
Sent: Thursday, December 16, 2004 3:30 PM
To: [EMAIL PROTECTED] 
Subject: Newbie request


 I am a total perl newbie and was wondering if someone could take a
look
at my code to determine if I am doing this the most efficient way.  It
works as it should, but I am sure an expert out there may have some
constructive comments to help me make it better.
 
For a little background: I am pulling some ids from a MySQL table into
an array, then connecting to Oracle and pulling lab information based
on
the id's in the array.  The lab info will then be loaded back into
MySQL.
 
Thanks in advance for any time you have to look at this.

[hm] I'm assuming your "connect" is *before* this code where you do
"prepare" and "execute" :)
 
 
$sql = "SELECT mbr_no, mrn, make8mrn FROM make8mrn";
 my $sth = $dbh->prepare($sql);
 $sth->execute();

 while (my @data = $sth->fetchrow_array()) {
  push @hold_mrns, [EMAIL PROTECTED];
 }
 
 #Connect to Oracle db
  my $OraDBH = DBI->connect("DBI:Oracle:ourdb",$user,$pwd ,
{RaiseError
=> 1});

[ron reidy] You need to set 'AutoCommit => 0' (a good practice, even if
not needed here).

[hm] I'm not sure I agree with Ron here.  Ron is sure the expert here
(I am no database expert), but in my limited database experience I'm not
sure I agree that AutoCommit => 0 is always good practice.  AutoCommit
=> 0 is useful for transaction processing that usually involves one or
more steps including  INSERT's and/or UPDATE's, but this app is strictly
SELECT'ing info.  IMHO, AutoCommit should always be specified, but "good
practice" is knowing *when* to use AutoCommit => 0 and when to use
AutoCommit => 1.

Robert, read up on AutoCommit in the DBI perldocs by doing

perldoc DBI

at a command prompt.  If you're on *nix, search for "AutoCommit" using
the forward slash(/AutoCommit).  If you plan to do more database coding
with Perl and DBI, I would also highly recommend the book "Programming
the Perl DBI" by Tim Bunce and Alligator Descartes.
 
 
 #Build date parms
 (my $, my $mm, my $dd) = Today();

[hm] This can be done like:
my ($, $mm, $dd) = Today();

 my $sql_to_date = "to_date('" . sprintf("%02d", $mm ) .
sprintf("%02d",
$dd) . "$','MM/DD/')";
 ($, $mm, $dd) = Add_Delta_YMD(Today(), 0, -6, 0); 
 my $sql_from_date = "to_date('" . sprintf("%02d", $mm ) .
sprintf("%02d", $dd) . "$','MM/DD/')"; 

[ ron reidy]  Go read up about the SYSDATE pseudo column in the Oracle
docs.  The above section of code is not necessary.

[hm] Ron is right, SYSDATE should take care of this - no need for any
of this extra date code in Perl.  I don't know if you can just do this:

 AND COLLECTION_DATE = SYSDATE

since date columns include time, so you might have to play around with
TO_CHAR so that you are only comparing the MMDD of each, something
like this:

That's about all I see.

HTH.

Hardy Merrill

AND TO_CHAR(COLLECTION_DATE, 'MMDD') =
  TO_CHAR(SYSDATE, 'MMDD')
 
 $sql = "SELECT COLLECTION_DATE, TEST, RESULT FROM LABTABLE" .
 "WHERE (MRN = ? AND COLLECTION_DATE >= $sql_from_date AND
COLLECTION_DATE <= $sql_to_date " .
 "AND (TEST = 'HgbA1C' OR TEST = 'Chol'))";
 
$sth = $OraDBH->prepare_cached($sql);

 #Get lab info from Oracle
 for my $aref ( @hold_mrns ) {
get_labs($OraDBH, $sth, @$aref[2]);
}
}

 
sub get_labs {
 my ($dbh, $sth, $mrn) = @_;
 
 my @hold_labs;
 $sth->execute($mrn);
 while (my @data = $sth->fetchrow_array()) {
  push @hold_labs, [EMAIL PROTECTED];
 }
 
 for my $aref ( @hold_labs ) {
   #Load this into mySQL
 }
}

Re: search fails if number at end of string

[Hardy Merrill]

By "no records are returned" do you mean you are trying to SELECT from
an access database?  Copy/paste in an example of something that's not
working for you, along with what versions of these you are using:
   Perl
   DBI
   DBD::ODBC(?) - which DBD module are you using to access "Access"?

>>> PerlDiscuss - Perl Newsgroups and mailing lists
<[EMAIL PROTECTED]> 12/15/04 01:04PM >>>
If the string passed to Access has a number at the end no records are
returned.

If I remove the number and there are records in the file that match
the
remaining characters then records are returned.

I can go into Access and use the same string and the search works.

Any help would be greatly appreciated

Ross McQueen
Department of Soil Science
University of Manitoba

RE: Nested query problem

[Hardy Merrill]

Ronald,

I realize I'm splitting hairs here, and I'm no database expert, but I'm
curious about your answer to this - wouldn't this be even slightly more
efficient to write the WHERE clause conditions as most restricting
first?  In other words, 

   SELECT feature.id
   FROM   feature,
  reporter
   WHERE  reporter.attributes_id = ? <=== most restrictive 1st
AND feature.reporter_id = reporter.id  <=== next most
restrictive

I was once told (or read?) that it is most efficient to put the most
restrictive conditions first in the WHERE - is that right?  I've always
tended to put my joins towards the end of the WHERE when I have other
criteria that I'm looking for - just curious to know if I've been doing
it wrong.

Thanks.

Hardy Merrill

>>> "Ronald J Kimball" <[EMAIL PROTECTED]> 12/10/04 03:08PM >>>
kaustubh shinde [mailto:[EMAIL PROTECTED] wrote:
> 
> Hi,
> I am using MySQL 4.0.21.
> Following is the problem:
> I want to
> Select ID from Reporter where Attributes_ID = something
> and then use the resultant IDs to find
> Select ID from Feature where Reporter_ID = the IDs found in above
> statement.
> 
> Please note that the first query will give thousands of IDs and I
want to
> compare them all to the second one.
> 
> It doesnt stop here. But if I get solution to this, rest shud be
easy.
> Now, I could probably use nested queries here. But MySQL 4.0 doesnt
> support
> it.
> I want to do this thru DBI and was wondering if its possible to get
result
> of a select query in a statement handle and use it to do a join with
some
> other table. If not, is there any other way to perform the above
> operations?


An inner join should work here, I think:

SELECT feature.id
FROM   feature,
   reporter
WHERE  feature.reporter_id = reporter.id
  AND  reporter.attributes_id = ?


Ronald

Re: Connecting to a remote server

[Hardy Merrill]

Michael, note here that it's been years since I used MySQL, but I think
I remember MySQL authentication being "host" based.  That's not the
right term, but connecting locally with mysql user "user1" is *NOT* the
same as connecting remotely to the same server with mysql user "user1". 
There is a way to specify on the server that you want to allow remote
connections as "user1" - as I remember it is very flexible in the way
that it allows you to specify a specific IP address to allow from, or a
portion (subnet) of an IP address, etc.

Sorry I can't be more help.  Without my "MySQL" book by Paul Dubois I
can't get any more specific.  If you don't have a book, go to the MySQL
website and read up authentication.

HTH.

Hardy Merrill

>>> "Michael David" <[EMAIL PROTECTED]> 12/02/04 12:06AM
>>>
Hello,

I have a script that is connecting to two different databases , one is
local 
and the other remote.
The local Informix DBI connection works a treat but I am having trouble

getting the remote mysql connection to work.

I keep get error: Client does not support authentication protocol
requested 
by server.
I have checked and upgraded, and now both local and remote machines are

running mysql 4.1, and the same client.
I have also checked and the mysql port is open on the remote server.

All the search results I could find regarding this error said to
upgrade the 
the mysql server, which I have done and now
there should be no protocol barriers since they are running the same 
version.

Does anyone have any ideas what may be causing this error other than 
different versions of the server. Or probably even better, some
instructions 
on how to really "turn up" or "tweak" my dbh debugging so that I can
get 
more details on the failed connection attempt.

this is my connection statement:
my $db2 = "dbname";
my $dbhMysql = DBI->connect("DBI:mysql:$db2:xxx.xxx.xxx.xxx", 'uname',

'pword') || die print "Could not connect to database($db2): $!\n";
this being the same as my local connection (minus the host IP address)
which 
does work.


Thanks,
mike 

Re: fetchrow_array performance

[Hardy Merrill]

Sorry for the top post - old version of Groupwise at work :-(

You aren't error checking your dbi statements - do you have RaiseError
set to "on" (1)?  If you don't, check each dbi statement like this:

 my $Statement = $Database->prepare($SQL)
 or die("Prepare died: $DBI::errstr");

Read about error checking in the perldocs by doing 'perldoc DBI' at a
command prompt.

*** I don't understand your problem.  Seem like you are saying that the
script finishes right after it prints the last row - how is that wrong? 
How would you expect it to finish any sooner?  The fact that it may take
hours to print all the rows selected doesn't matter, does it?  Printing
is inherently slow, in comparison to the small amount of time it takes
to loop through all the rows without printing them.

I'm probably just not understanding your problem - I don't understand
why you are surprised that it takes a long time to print all the rows
selected.

Hardy Merrill


>>> Schoenwaelder Oliver <[EMAIL PROTECTED]> 12/01/04
07:07AM >>>
Hi,

I've got a problem with a simple fetch which just takes hours
(literaly) to
complete.
It's a simple select statement which, in the current case, returns
about 300
rows within seconds.
But the loop is finished a couple of hours later then the last result
is
printed.
Here's a code snippet:

my $SQL = "select oh_deliverynumber, ol_linereference,
oh.batchrunidentifier, oh.transactiondate
 from orderheader oh, orderline ol
 where ol_linereference not in (select sc_linereference
from
shipconfirm)
 and oh_deliverynumber=ol_deliverynumber
 and
to_date(oh.transactiondate,'MMDDHH24MISS')+8<=sysdate";

my $Statement = $Database->prepare($SQL);

if (defined($Database->errstr)) {
$Rollback=1;
warn $Database->errstr;
}

$Statement->execute();
if (defined($Statement->errstr)) {
$Rollback=1;
warn $Statement->errstr;
}
if (defined($Statement->fetchrow_array)) {
$Rollback=1;
warn $TextOnResult;
my @row;
while ( @row = $Statement->fetchrow_array ) {
warn "@row\n";
}
}

So for me it looks like the fetchrow_array function doesn't notice
that
there are no more rows to fetch.
But I don't know whether it's my database, the DBD::Oracle module or
DBI
itself.
Has anyone experienced the same?
Any other idea?

DBI: 1.45
DBD::Oracle 1.16
Oracle client: 8.1.7.0
Database: 8.1.7.2

Best regards,

Olly

Re: Oracle problem

[Hardy Merrill]

Tim, can you elaborate a little?  I've tried to find the referenced
documentation but can't.  I don't quite understand how a CHAR datatype
can cause this problem.

Thanks.

Hardy Merrill 

>>> Tim Bunce <[EMAIL PROTECTED]> 11/24/04 07:35AM >>>
On Tue, Nov 23, 2004 at 10:13:30PM +, Bart Kelsey wrote:
> I'm having some trouble with DBD::Oracle...
> When I execute this code:
> ***
> 
>$sth = $dbh->prepare("select * from abbrev where type = ?");
>$sth->execute("PAYMENT");
>while((@row) = $sth->fetchrow_array) {
>  print(join(", ", @row), "\n");
>}
>$sth->finish;

[Don't call finish at the end of fetch loops. See the docs.]

> ... no rows are returned.  However, when I execute this code here:
> 
>$sth = $dbh->prepare("select * from abbrev where type =
'PAYMENT'");
> 
> ... it correctly returns a row.  Does anyone know what the problem
might be

I'd guess the "type" column is a CHAR field.

Try:
use DBD::Oracle qw(:ora_types);
...
$dbh->{ora_ph_type} = ORA_CHAR

before the prepare(). See
http://search.cpan.org/~timb/DBD-Oracle-1.16/Oracle.pm#Database_Handle_Attributes


See also the String Comparison section in the Datatypes chapter of the
Oracle OCI manual for more details.

Tim.

Re: Googlebait: Postgres and 'expression too complex'

[Hardy Merrill]

>>> "Greg Sabino Mullane" <[EMAIL PROTECTED]> 11/16/04 07:54AM >>>

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
 
> Your logic that "because it worked, MySQL's limit must be 65kb" may
be as >  > bogus
> as the PostgreSQL 32kb logic, but I don't know MySQL well enough to
say.

Lighten up Greg.  Ron did say "I admit I'm guessing re the statement
length limits."

Re: bind_columns() question

[Hardy Merrill]

So what did the problem turn out to be?

>>> Robert <[EMAIL PROTECTED]> 10/20/04 11:23PM >>>
Robert wrote:
> My select looks like this:
> 
> SELECT surname, first_name, msf810.email_address,
msf810.employee_id
> FROM msf810, msf020, MSF829
> WHERE msf810.employee_id = msf020.employee_id
> AND msf810.employee_id = msf829.employee_id
> AND msf020.dstrct_code = 'ATTU' AND physical_loc = '05'
> AND global_profile != 'JRADMIN'
> 
> My bind_columns looks like this:
> 
> $sth->bind_columns( \$last, \$first, \$email, \$userid );
> 
> I then do a loop over the contents but I am only getting the $last
and
> $first to print and not the other columns that are bound.
> 
> I am using Perl 5.8.4 from ActiveState on Windows using Oracle 9i as
the
> database.
> 
> Robert
> 
> 
Ah, never mind. I got it.

Robert

Re: DBI and Oracle 9.2

[Hardy Merrill]

Just in case you didn't get the meaning behind Tim's comment, if you
upgrade your version of Oracle, you then always need to rebuild
DBD::Oracle.  DBD::Oracle needs to be built _after_ installing any
version of Oracle.

>>> Tim Bunce <[EMAIL PROTECTED]> 09/17/04 06:21AM >>>
Rebuild DBD::Oracle.

Tim.

On Thu, Sep 16, 2004 at 07:45:37AM -0700, Robert wrote:
>  
> I have upgraded Oracle from 8.1.7 to 9.2, after changing to the new
Oracle environment when I run the cgi script which always used to work
in Oracle 8.1.7 to Oracle 9.2 is giving the following error.
> Software error:
> DBI connect('user1','user123',...) failed: ERROR OCIEnvInit at
/home/user1/www/cgi-bin/test.cgi line 260
> 
> For help, please send mail to this site's webmaster, giving this
error message and the time and date of the error. 
> 
>  
> 
> I have changed the Oracle 9.2 environment in the cgi script 
(OraHome1 is the Oracle 9.2 )
> 
> $ENV{ORACLE_HOME} = q[/ora1/u01/oracle/OraHome1];
> $ENV{LD_LIBRARY_PATH} = qq[$ENV{ORACLE_HOME}/lib];
> 
>  
> 
> Tnsping is working and also I can connect to oracle using sqlplus, is
there any compatibility issue with DBI-1.43/DBD-Oracle-1.15 and Oracle
9.2? .
> 
>  
> 
> Thanks in advance
> 
> 
>  
> 
> 
> 
> 
> 
>   
> -
> Do you Yahoo!?
> vote.yahoo.com - Register online to vote today!

Re: MySQL INSERT problem...

[Hardy Merrill]

I know you said the problem column is an enum column, but can you copy
and paste in a "describe" of the table in question(acct_hist)?  I've
never used an "enum" column so I'm not sure I'll be any help, but a
describe might help jog someones memory.

Hardy Merrill

>>> "NIPP, SCOTT V (SBCSI)" <[EMAIL PROTECTED]> 09/16/04 10:10AM >>>
I have a database that I am working on a Perl script for to
manage.  Basically, this is a database of user accounts on about 80
Unix
boxes.  Most of it is working great, but I have one problem with the
two
of the INSERTs.  The INSERTs are putting data into a history table
such
that we can maintain when changes to user accounts occurred.  One
field
of the INSERTs is not populating a column.  The column is an ENUM type
with either "D" (for deleted account) or "U" (for update to account)
as
the valid input.  The INSERT actually populates all the other columns
but this one.  What am I doing wrong?  
This is a MySQL database running on HP-UX 11i.  This is running
Perl 5.6.0, and I do not have easily available the DBD and DBI
versions.
Here is the section of code with the problem INSERT"

<---code snipped above--->
  if ($old[0]) {
print "$key1 requires updating in database.  Updating
entry
now.\n";
$type = "U";
#  Insert existing data into acct_hist.
$dbh->do(qq{
 INSERT INTO acct_hist (key1, uid, gid, gcos,
home,
shell, e
nt_time, update_type, arc_time)
 VALUES (?,?,?,?,?,?,?,?,NOW())},
 undef,@old,$type);
#  Delete existing data from acct_db.
$dbh->do(qq{DELETE FROM acct_db WHERE key1 = ?},
undef,$key1);
#  Insert new entry into acct_db.
$dbh->do(qq{
   INSERT INTO acct_db
VALUES(?,?,?,?,?,?,NOW(),NOW())},
   undef, $key1, $uid, $gid, $gcos, $home, $shell)
or print "Error updating database:  ", $dbh->errstr,
"\n";
<---code snipped below--->

This first INSERT is the problem one.  The second INSERT works
fine.  Thanks in advance for any suggestions.

Scott Nipp
Phone:  (214) 858-1289
E-mail:  [EMAIL PROTECTED] 
Web:  http:\\ldsa.sbcld.sbc.com

Re: fetchrow_array vs fetchrow_arrayref question

[Hardy Merrill]

Generally the "rows" method is unreliable - here's a section from
'perldoc DBI':

"rows"
  $rv = $sth->rows;

Returns the number of rows affected by the last row affecting
command, or -1 if the number of rows is not known or not
available.

Generally, you can only rely on a row count after a *non*-"SELECT"
"execute" (for some specific operations like "UPDATE" and
"DELETE"),
or after fetching all the rows of a "SELECT" statement.

For "SELECT" statements, it is generally not possible to know how
many rows will be returned except by fetching them all. Some
drivers
will return the number of rows the application has fetched so far,
but others may return -1 until all rows have been fetched. So use
of
the "rows" method or $DBI::rows with "SELECT" statements is not
recommended.

One alternative method to get a row count for a "SELECT" is to
execute a "SELECT COUNT(*) FROM ..." SQL statement with the same
"..." as your query and then fetch the row count from that.

---

If you are fetching all the rows anyone one by one, why not just count
them as you go?

HTH.

Hardy Merrill

>>> Zhiliang Hu <[EMAIL PROTECTED]> 09/12/04 11:40AM >>>

Case:
When I used $query_a->fetchrow_array
I use it again in $query_a->rows to count number or rows.

However, when I used $query_a->fetchrow_arrayref
$query_a->rows returns nothing - it seems $query_a is nullified.

Is it normal or due to my other problem?

Thanks in advance!

Zhiliang

Re: Insert/update CLOB

[Hardy Merrill]

Remember the mantra...read the perldocs...read the perldocs...

There's a ton of BLOB and CLOB info in the DBD::Oracle perldocs - read
them by doing

   perldoc DBD::Oracle

at a command prompt.  Or find the DBD::Oracle perldocs online - off the
top of my head I don't know the address, but go to google and search for
"perldoc DBD::Oracle" and I'm sure you'll find it ;-)

Two variables you'll be interested in are LongReadLen and LongTruncOk.

HTH.

Hardy Merrill

>>> "Zhang, George" <[EMAIL PROTECTED]> 09/02/04 08:22AM
>>>
Hello,

I am trying to insert/update some big text into a CLOB filed, but it
seemed
to fail if the length of the text exceeds 4000.  Any ideas?

I am running perl on Windows 2k, Oracle 9i.

Thanks

George Zhang

Re: How to use sequences in DBI/DBD::Oracle with bind

[Hardy Merrill]

I'm no Oracle expert, but I've always done it the way you originally did it:

$sth->prepare ( "insert into foo (my_id, data) values
(my_id_seq.nextval, 'bar')" );
$sth->execute();

I searched for "seq" in 'perldoc DBD::Oracle' and found this under LOB locator stuff:
--
 my $lob_id = $dbh->selectrow_array( <<"   SQL" );
SELECT lob_example_seq.nextval FROM DUAL
 SQL

 my $sth = $dbh->prepare( <<"   SQL" );
INSERT INTO lob_example
( lob_id, bindata, chardata )
VALUES ( ?, ?, ? )
 SQL
 $sth->execute( $lob_id, '', '' );


I know your question has nothing to do with LOB locators, but this could be another 
way to solve the same problem.  Not sure what performance impact doing a SELECT on the 
sequence nextval and then INSERT'ing, but this way you can at least PREPARE your 
insert just once.

HTH.

Hardy Merrill

>>> Jean-Pierre Utter Löfgren <[EMAIL PROTECTED]> 08/31/04 07:19AM >>>
Does anybody have a clue on how to bind oracle sequences.

Background:

Due to the large amount of data to be loaded into our oracle9i db by our
applications, a bulk-load software would have been our first choice to
handle the inserts. However, since our data is higly dynamic in its
construction, this is not an option.

In order to solve this, I've developed a perl package to handle the
inserts. So far so good, but now our DBAs are running wild since I
produce a fair amount of latching and statements prepares (400+/s),
chocking the production database (SUN Enterprise 4500, 14 CPU).

Every row inserted is unique, but some use the same columns to insert,
so I'd like to use $sth->bind_param or the $sth->execute($1, $2 )
for those, to minimize my preparing of statments.

You might think I should have user bind variables from the start, and I
tried, trust me, but the problem back then is the same as I will try to
adress now.

The Problem

is however that I use sequences in the database inserts for various
reasons, as some inserts use the same primary key, sub-data needs to be
referenced to each other etc. Using DBI/DBD, it handles inserts using
sequneces very nicely as long as I do

$sth->prepare ( "insert into foo (my_id, data) values
(my_id_seq.nextval, 'bar')" );
$sth->execute();

But if I rearange the code to be more efficient...

$sth->prepare ( "insert into foo (my_id, data) values (?,?)" );
$my_id = "my_id_seq.nextval";
$data = "bar";
$sth->execute($my_id,$data);

or

$sth->prepare ( "insert into foo (my_id, data) values (?,?)" );
$sth->bind_param( 1, "my_id_seq.nextval" );
$sth->bind_param( 1, "bar");
$sth->execute();

, I get "ORA-01722: Invalid number" on the sequence.

I tried to find infomation on ora_types argument to indicate the
argument type in the bind, but am so far unsuccessful. I've even tried
to bind a sub-select like "(select my_id_seq.nextval from dual)", but
got the same result...

Anybody got any id.ea if this is possible or not to achieve this, i.e.
using oracle sequences in binds?

/JP

Re: multiple prepare calls

[Hardy Merrill]

Original OP - how about some more info?  Copy and paste in the errors
that you are getting.

Hardy Merrill

>>> "Jenda Krynicky" <[EMAIL PROTECTED]> 08/25/04 01:47PM >>>
From: "Registry" <[EMAIL PROTECTED]>
> I hope I am addressing this question to a correct email.
> 
> I am trying to make multiple prepare statements on one database
> connection, however I am getting errors.
> I would just like to know if this is possible. The logic goes
> something like:
> 
> my $DBH = DBI->connect();
> my $sth = $DBH->prepare( SELECT ...);
> my $rv = $sth->execute();
> 
> my $sth2 = $DBH->prepare( SELECT ...);
> my $rv2 = $sth2->execute();
> 
> Thank you in advance.

Yes it should be possible.

But some drivers require that you first read the whole resultset 
before you try to prepare or execute another statement.

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

RE: Error logs for DBI

[Hardy Merrill]

I could be mistaken since I haven't used Apache on Linux in a while, but
don't cgi prints to STDERR by default go to the Apache error log??

Hardy Merrill

>>> "Reidy, Ron" <[EMAIL PROTECTED]> 08/23/04 12:13PM >>>
So what exactly don't you understand?

Disclaimer:  NOT TESTED!

$SIG{__DIE__} = sub {
print LOG "@_\n";
};

open(LOG,">>$fname") || die $!;
$dbi = DBI->connect(...) || die $DBI::ERRSTR;
$sth = $dbh->prepare("select * from xxx") || die $DBI::errstr;
$sth->execute;
...
close(LOG) || warn "file I/O error writing to log: $!";

Or instead of opening a file, you could change the above and eliminate
the signal handler ($SIG) and the open() and close() statements, and
just capture output from STDOUT to a file:

./file.pl | tee -a err.log

-
Ron Reidy
Senior DBA
Array BioPharma, Inc.


-Original Message-
From: Joel West [mailto:[EMAIL PROTECTED] 
Sent: Monday, August 23, 2004 10:07 AM
To: Reidy, Ron
Cc: [EMAIL PROTECTED] 
Subject: RE: Error logs for DBI


Thank you ...

And having done so I am still having the problem I was earlier.

How do I create an error log for a perl DBI script.



On Mon, 23 Aug 2004, Reidy, Ron wrote:

> perldoc open
> perldoc print
> perldoc DBI
>
> -
> Ron Reidy
> Senior DBA
> Array BioPharma, Inc.
>
>
> -Original Message-
> From: Joel West [mailto:[EMAIL PROTECTED] 
> Sent: Monday, August 23, 2004 9:38 AM
> To: [EMAIL PROTECTED] 
> Subject: Error logs for DBI
>
>
> How do I create log files with just errors?
>
>
>
> This electronic message transmission is a PRIVATE communication which
contains
> information which may be confidential or privileged. The information
is intended
> to be for the use of the individual or entity named above. If you are
not the
> intended recipient, please be aware that any disclosure, copying,
distribution
> or use of the contents of this information is prohibited. Please
notify the
> sender  of the delivery error by replying to this message, or notify
us by
> telephone (877-633-2436, ext. 0), and then delete it from your
system.
>
>

This electronic message transmission is a PRIVATE communication which
contains
information which may be confidential or privileged. The information is
intended 
to be for the use of the individual or entity named above. If you are
not the 
intended recipient, please be aware that any disclosure, copying,
distribution 
or use of the contents of this information is prohibited. Please notify
the
sender  of the delivery error by replying to this message, or notify us
by
telephone (877-633-2436, ext. 0), and then delete it from your system.

RE: Trouble with bind variables

[Hardy Merrill]

I don't see anything glaringly wrong, and in fact I don't know why it's
not working for you.  Maybe I'm just overlooking something, but why not
include the $orasid and $server as bind variables?  Like this:

   my $insert_sql = qq { insert into user_history_stats (database,
server, year, week, username, cpu, io)
values (?, ?, ?, ?, ?, ?, ?)
};

   my $target_sth = $target_dbh->prepare( $insert_sql ) or die "Can't
prepare insert statement: " . $target_dbh->errstr;;
   
   while ($source_sth->fetch) {
  $target_sth->bind_param(1, $orasid);
  $target_sth->bind_param(2, $server);
  $target_sth->bind_param(3, $year);
  $target_sth->bind_param(4, $week);
  $target_sth->bind_param(5, $userid);
  $target_sth->bind_param(6, $cpu);
  $target_sth->bind_param(7, $io);

  $target_sth->execute;

   }

???

HTH.

Hardy Merrill




>>> "Bart Kersteter" <[EMAIL PROTECTED]> 08/17/04 11:06AM
>>>
Here's the full main loop script, I'm still relatively new to DBI, so
I
may have messed things up a bit.  I have gone through the perldocs a
few
times.


my $db_list = new IO::File("< /home/oracle/utils/metrics_db_list.txt")
or die "Can't open oratab file";

while (<$db_list>) {
   my @db_info = split(/:/);
   my $orasid = $db_info[0];
   my $server = $db_info[1];
   my $source_dbh = DBI->connect( "dbi:Oracle:$orasid", '',
',
 {RaiseError => 1, AutoCommit => 0}) || die DBI->errstr;

   my $target_dbh = DBI->connect( "dbi:Oracle:fd1p", '',
'',
  {RaiseError => 1, AutoCommit => 0}) || die
DBI->errstr;

   my $source_sql = qq{ SELECT username,  to_char(sample_Date, '')
,  to_char(sample_date, 'IW') , sum(cpu) , sum(IO) 
  from daily_user_stats
  group by username, to_CHAR(sample_date, 'IW')
, to_char(sample_Date, '')
  order by 1,2 };

   my $source_sth = $source_dbh->prepare( $source_sql ) or die "Can't
prepare source statement: " . $source_dbh->errstr;
   $source_sth->execute;
   my ($userid, $year, $week, $cpu, $io);
   $source_sth->bind_columns(\$userid, \$year, \$week, \$cpu, \$io);

 
   my $insert_sql = qq { insert into user_history_stats (database,
server, year, week, username, cpu, io)
values ('$orasid', '$server', ?, ?, ?, ?, ?)
};

   my $target_sth = $target_dbh->prepare( $insert_sql ) or die "Can't
prepare insert statement: " . $target_dbh->errstr;;
   
   while ($source_sth->fetch) {
  $target_sth->bind_param(1, $year);
  $target_sth->bind_param(2, $week);
  $target_sth->bind_param(3, $userid);
  $target_sth->bind_param(4, $cpu);
  $target_sth->bind_param(5, $io);

  $target_sth->execute;

   }

   # Clean up and exit loop
   $target_dbh->commit();
   $source_dbh->disconnect();
   $target_dbh->disconnect();
}


The literal 'FD1P' you referred to is the SID of one of my databases. 

Thanks,

Bart



Bart Kersteter

Senior DBA - Corporate Database
Assurant
576 Bielenberg Drive
Woodbury, MN 55125
[EMAIL PROTECTED] 
(651) 361-5796


**
This e-mail message and all attachments transmitted with it may contain
legally privileged and/or confidential information intended solely for
the use of the addressee(s). If the reader of this message is not the
intended recipient, you are hereby notified that any reading,
dissemination, distribution, copying, forwarding or other use of this
message or its attachments is strictly prohibited. If you have received
this message in error, please notify the sender immediately and delete
this message and all copies and backups thereof.

Thank you.
**

RE: SQL- Insert problem

[Hardy Merrill]

>>> "Ronald J Kimball" <[EMAIL PROTECTED]> 08/13/04 01:59PM >>>
Hardy Merrill [mailto:[EMAIL PROTECTED] wrote:
> 
> Just in case the op doesn't make the connection, he needs to test
his
> variables for undef before the execute, and if one is undef, then
put
> null("") in it, something like this:
> 
>  if (!defined($a)) {$a = "";}
>  if (!defined($b)) {$b = "";}
>... and so on - you get the idea...
>  $sth=$dbh->prepare(insert into OPER_QUERY values
(?,?,?,?,?)
> )
>  $sth->execute($a,$b,$c,$d,...$x) or die;

Why does he need to do that?  I have never had to test my bind values
for
defined-ness when using placeholders.  An undef in Perl becomes a NULL
in
Oracle, and vice versa.

Ronald


I have to plead ignorance - I thought his problem was caused by one or
more the variables (being passed to placeholders) being undefined. 
Sorry, I must not have read his post well enough that included the
actual error(s).

Hardy

RE: SQL- Insert problem

[Hardy Merrill]

Just in case the op doesn't make the connection, he needs to test his
variables for undef before the execute, and if one is undef, then put
null("") in it, something like this:

 if (!defined($a)) {$a = "";}
 if (!defined($b)) {$b = "";}
   ... and so on - you get the idea...
 $sth=$dbh->prepare(insert into OPER_QUERY values (?,?,?,?,?)
)
 $sth->execute($a,$b,$c,$d,...$x) or die;

HTH.

Hardy Merrill

>>> "Reidy, Ron" <[EMAIL PROTECTED]> 08/13/04 01:13PM >>>
>From your original post ...

!! ERROR: 936 'ORA-00936: missing expression (DBD: error possibly
near <*> indicator at char 252 in 'insert into OPER_QUERY
(end_date,site,tester_type,program,total,oper_yes,rmenu_yes,opr_dev_type
,opr_test_type,opr_temp_type,opr_lot_type,rmenu_dev_type,rmenu_test_type
,rmenu_temp_type,rmenu_lot_type)
values(08/12/04,DMOS5,POLARIS100,P1831C02,91,22,49,<*>,2,10,,,2,32,)')'

The error is ---^

This is invalid SQL, as are the others in this statement like this. 
Because are building dynamic SQL and your variables are 'undef'ined,
this is the statement you are sending to the RDBMS engine.  You need to
ensure your variables have a value and all should be well.

However, you should still look at bind variables.

-
Ron Reidy
Senior DBA
Array BioPharma, Inc.


-Original Message-
From: Shah, Urmil [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 13, 2004 11:07 AM
To: Reidy, Ron; [EMAIL PROTECTED] 
Subject: RE: SQL- Insert problem


I tried to use different methods to insert but all fails. 

1) $sth=$dbh->prepare(insert into OPER_QUERY values ($a,$b,$c,$d) )

$sth->execute() or die;

2) $sth=$dbh->prepare(insert into OPER_QUERY (col1, col2, col3..co15)
values($a,$b,$c,$d) )
 $sth->execute() or die;

3) $sth=$dbh->prepare(insert into OPER_QUERY values (?,?,?,?,?) )
 $sth->execute($a,$b,$c,$d,...$x) or die;
  

For case 1 and 2 it gives error saying the value is empty. In case 3
it
just hangs and does not populate anything and I have to kill the
script.
If there is any other way to insert please let me know.

Urmil  
 

-Original Message-
From: Reidy, Ron [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 13, 2004 11:57 AM
To: Shah, Urmil; [EMAIL PROTECTED] 
Subject: RE: SQL- Insert problem

You have a NULL (not the word NULL) in your statement.

FYI - You should look into using bind variables.

-
Ron Reidy
Senior DBA
Array BioPharma, Inc.


-Original Message-
From: Shah, Urmil [mailto:[EMAIL PROTECTED] 
Sent: Friday, August 13, 2004 10:43 AM
To: [EMAIL PROTECTED] 
Subject: SQL- Insert problem


Need help in trying to insert an sql statement.

 

1)   I have 15 columns in oracle database defined.

2)   Through Perl DBI I am trying to loop and populate these 15
columns for 'n' of rows.

3)   Some of the column values that I am trying to insert is
null/empty and it is ok if they are empty <-- It gives me error when
it
find null value.

 

Code:

 

foreach my $type(sort keys %H_CNT_BY_PGM){

 foreach my $pgm(sort keys %{$H_CNT_BY_PGM{$type}}){

 

 $sth1 = $dbh->prepare(qq{insert into OPER_QUERY
(end_date,site,tester_type,program,total,oper_yes,rmenu_yes,opr_dev_type
,opr_test_type,opr_temp_type,opr_lot_type,rmenu_dev_type,rmenu_test_type
,rmenu_temp_type,rmenu_lot_type) values
($mydate,$site,$type,$pgm,$Tot,$Tot_yes,$Tot_rmenu,$dev_type_err,$test_t
ype_err,$temp_type_err,$lot_type_err,$rmenu_dev_type_err,$rmenu_test_typ
e_err,$rmenu_temp_type_err,$rmenu_lot_type_err)}) or die"Cannot
prepare
sql:$DBI::errstr\n";

 

$sth1->execute() or die"Cannot prepare sql:$DBI::errstr\n";

 

 }#foreach

}#foreach

 

 

dbi Trace(1) output :

 

start inserting:
08/12/04,DMOS5,POLARIS100,P1831C02,91,22,49,,2,10,,,2,32,

DBI::db=HASH(0x49fcbc) trace level set to 1 in DBI 1.20-nothread

Note: perl is running without the recommended perl -w option

!! ERROR: 936 'ORA-00936: missing expression (DBD: error possibly
near <*> indicator at char 252 in 'insert into OPER_QUERY
(end_date,site,tester_type,program,total,oper_yes,rmenu_yes,opr_dev_type
,opr_test_type,opr_temp_type,opr_lot_type,rmenu_dev_type,rmenu_test_type
,rmenu_temp_type,rmenu_lot_type)
values(08/12/04,DMOS5,POLARIS100,P1831C02,91,22,49,<*>,2,10,,,2,32,)')'

<- prepare('insert into OPER_QUERY
(end_date,site,tester_type,program,total,oper_yes,rmenu_yes,opr_dev_type
,opr_test_type,opr_temp_type,opr_lot_type,rmenu_dev_type,rmenu_test_type
,rmenu_temp_type,rmenu_lot_type)
values(08/12/04,DMOS5,POLARIS100,P1831C02,91,22,49,,2,10,,,2,32,)'
CODE)= undef at oper_query.pl line 351

DBD::Oracle::db prepare failed: ORA-00936: missing expression (DBD:
error possibly near <*> indicator at char 252 in 'i

Re: Oracle 9.2 upgrade breaks Perl DBI

[Hardy Merrill]

Did you recompile DBD::Oracle since Oracle 9.2 was installed?

>>> "Richard Freedman" <[EMAIL PROTECTED]> 08/10/04 07:52PM
>>>
Our DBA has recently upgraded Oracle from 8.1.7 to 9.2.  I tried
changing my ORACLE_HOME enviornment and it broke my DBI/DBD::Oracle.

If I run
$ENV{ORACLE_HOME} = "/opt/oracle/products/8.1.7";
$db = DBI->connect ("dbi:Oracle:xxx", "uname", "pword",
{AutoCommit=>0});
all is well

If I run
$ENV{ORACLE_HOME} = "/opt/oracle/products/9.2";
$db = DBI->connect ("dbi:Oracle:xxx", "uname", "pword",
{AutoCommit=>0})
or croak "Database Error: $DBI::errstr\n";
I get DBI->connect(xxx) failed: ERROR OCIEnvInit

I am running Sun Solaris, Perl 5.6.1, DBI 1.20, DBD::Oracle 1.12


Please help.

Richard Freedman
Bioinformatics Scientist
Cytokinetics
(650)624-3027

Re: Derefencing DBI SQL statements

[Hardy Merrill]

Use "trace".

Here's the whole section from "perldoc DBI" about using trace:

"trace"
  DBI->trace($trace_level)
  DBI->trace($trace_level, $trace_filename)

DBI trace information can be enabled for all handles using the
"trace" DBI class method. To enable trace information for a
specific
handle, use the similar "$h->trace" method described
elsewhere.

Trace levels are as follows:

  0 - Trace disabled.
  1 - Trace DBI method calls returning with results or errors.
  2 - Trace method entry with parameters and returning with
results.
  3 - As above, adding some high-level information from the
driver
  and some internal information from the DBI.
  4 - As above, adding more detailed information from the
driver.
  5 and above - As above but with more and more obscure
information.

Trace level 1 is best for a simple overview of what's
happening.
Trace level 2 is a good choice for general purpose tracing.
Levels 3
and above (up to 9) are best reserved for investigating a
specific
problem, when you need to see "inside" the driver and DBI.

The trace output is detailed and typically very useful. Much of
the
trace output is formatted using the "neat" function, so strings
in
the trace output may be edited and truncated.

Initially trace output is written to "STDERR". If
$trace_filename is
specified and can be opened in append mode then all trace
output
(including that from other handles) is redirected to that file.
A
warning is generated is the file can't be opened. Further calls
to
"trace" without a $trace_filename do not alter where the trace
output is sent. If $trace_filename is undefined, then trace
output
is sent to "STDERR" and the previous trace file is closed. The
"trace" method returns the *previous* tracelevel.

See also the "$h->trace" and "$h->trace_msg" methods and the
"DEBUGGING" section for information about the "DBI_TRACE"
environment variable.
---
HTH.

Hardy Merrill

>>> <[EMAIL PROTECTED]> 08/11/04 02:56AM >>>

Is there a way to dereference the SQL statement that is send to the
DBD?
I am loading a master table with 19+ fields and there seem to be a
problem 
at the end of the SQL statement. I want to see what is the DBI/DBD
sending 
to the Database. 

example: 

$q3 = $dbh->prepare("INSERT INTO MasterRealtor (@qtColumns, date)
VALUES 
(@Insert,\"$date\")"); 

$q3->execute or die "ERROR: $q3->errstr \n"; 

Want to see what the value/string of $q3 is? 


Tnank you all 


 -Max

Re: fetch entire table

[Hardy Merrill]

Not sure if this is what you're looking for, but do

   perldoc DBI

at a command prompt and search for "selectall_arrayref",
"selectall_hashref", and
"fetchall_arrayref".

Hardy Merrill

>>> Xinyu Wen <[EMAIL PROTECTED]> 08/11/04 12:34PM >>>
Hi, 

I would like to know if there is a better way to fetch an entire table
to a file (the file has to be tab delimited) instead of using
fetchrow_array function? Fetchrow_array is working fine but slow to
work with a big table.

Thanks,

Adam.

RE: web development tool

[Hardy Merrill]

Sorry for the poor message quoting - groupwise.

>>> "MCMULLIN, NANCY" <[EMAIL PROTECTED]> 08/09/04 11:57AM >>>
From: Hardy Merrill [mailto:[EMAIL PROTECTED] 

I think you'll find that Perl can do any and all of what you asked
about, but you're asking about pretty general topics.  If you can be
more specific it's likely you'll get more specific answers.

Hardy,
Specifically, we need to do Oracle database development running in a
browser, which includes: 
- add, delete, modify records
  >>HM Standard database interaction for a cgi script - Perl or
other...

- display on-screen reports of that data
  >>HM Nothing special about that

- print hard-copy reports
  >>HM Pretty standard

- run batch processes
  >>HM All(?) languages have functionality which permits external
   commands to be executed.  CGI scripts are no different.

- allow for concurrent use
  >>HM Not quite sure what you mean here - concurrent use is the
   job of the webserver, isn't it??


One thing I can't comment on is the impact mod_perl could have on what
you're trying to achieve.  I've never used mod_perl, but using it is a
way to increase the performance of scripts running through your
webserver - and it gives you access to webserver internals.

HTH.

Hardy Merrill

We've already put together some simple Perl / HTML / SQL scripts that
display data, but I  just didn't know if Perl and HTML were robust
enough to do all the aforementioned.

Thanks.
Nancy

Since you ask here on the Perl *DBI* mailing list, in my experience,
which includes (not to say I'm an expert in these ;-) Perl, PHP,
Python,
some ASP, no other tool in that list has the mature database
independent
interface that Perl does.  This is an excellent tool that allows you
to
write database independent code for any application that interfaces
with
a database.  The major "pro" of DBI is that if you write your apps for
"Oracle" (DBD::Oracle), that sometime down the road if you change your
database to DB2, that your code will not have to change much -
assuming
that you don't take advantage of oracle-specific functionality
provided
by Oracle.

I don't know Coldfusion or Java so I can't offer any insight there.

HTH.

Hardy Merrill

>>> "MCMULLIN, NANCY" <[EMAIL PROTECTED]> 08/09/04 11:26AM >>>
Generic question to the group...

It's been requested that we expand our Perl / Database / Web
development to do more advanced things, such as database maintenance,
reporting and monitoring (over the web).  

Would you recommend continuing to use strictly Perl for all of this,
or
jump to another tool, such as Coldfusion, PHP, Java or something else? 

I guess I'm wondering how easy or hard it would be to develop
everything
using only Perl.

Thanks for the advice.
Nancy

Re: web development tool

[Hardy Merrill]

I think you'll find that Perl can do any and all of what you asked
about, but you're asking about pretty general topics.  If you can be
more specific it's likely you'll get more specific answers.

Since you ask here on the Perl *DBI* mailing list, in my experience,
which includes (not to say I'm an expert in these ;-) Perl, PHP, Python,
some ASP, no other tool in that list has the mature database independent
interface that Perl does.  This is an excellent tool that allows you to
write database independent code for any application that interfaces with
a database.  The major "pro" of DBI is that if you write your apps for
"Oracle" (DBD::Oracle), that sometime down the road if you change your
database to DB2, that your code will not have to change much - assuming
that you don't take advantage of oracle-specific functionality provided
by Oracle.

I don't know Coldfusion or Java so I can't offer any insight there.

HTH.

Hardy Merrill

>>> "MCMULLIN, NANCY" <[EMAIL PROTECTED]> 08/09/04 11:26AM >>>
Generic question to the group...

It's been requested that we expand our Perl / Database / Web
development to do more advanced things, such as database maintenance,
reporting and monitoring (over the web).  

Would you recommend continuing to use strictly Perl for all of this, or
jump to another tool, such as Coldfusion, PHP, Java or something else?  
I guess I'm wondering how easy or hard it would be to develop everything
using only Perl.

Thanks for the advice.
Nancy

Re: ANNOUNCE: Advanced DBI tutorial slides

[Hardy Merrill]

Tim, is DBI v2 going to coincide with the release of the 2nd edition of
Programming the Perl DBI?

Hardy Merrill

>>> [EMAIL PROTECTED] 08/02/04 10:31AM >>>
I uploaded the slides for my Advanced DBI tutorial shortly before
OSCON
but forgot to announce them:

  file: $CPAN/authors/id/T/TI/TIMB/DBI_AdvancedTalk_2004.tar.gz
  size: 3122405 bytes
   md5: b8a8f1732a0d67c6c69e48e4541c3162

Most easily read online via:

  http://search.cpan.org/src/TIMB/DBI_AdvancedTalk_2004/index.htm 

you'll also find some mention of DBI v2 here:

  http://search.cpan.org/src/TIMB/DBI_AdvancedTalk_2004/sld097.htm 

Tim.

Re: Different types of prepare statements

[Hardy Merrill]

My comments(HM>>) are below.  Sorry for the poor message quoting -
thanks to Groupwise on Windoze XP.

>>> "Robert" <[EMAIL PROTECTED]> 07/29/04 11:51AM >>>
I have seen:

my $sth = $dbh->prepare( <> I've never used this method but there's no reason it shouldn't
work.  This uses what perl calls "Here Documents" to bound a multi-line
string.

my $sth = $dbh->prepare(q{
SELECT blah
FROM blah
WHERE blah = blah
});

HM>> A single q is the same as using single quotes to surround a
string, except that q{} allows you to put single quotes inside it and
those single quotes won't interfere with the q{} quoting of the whole
string.  Note that a string bounded by single quotes (or q{}) does *NOT*
allow $variables to be interpolated - so if you did:

 my $sth = $dbh->prepare(q{
 SELECT blah
FROM blah
   WHERE column1 = $value1
  });

the value of $value1 would *NOT* appear in the SELECT.

my $sth = $dbh->prepare(qq{
SELECT blah
FROM blah
WHERE blah = blah
});

HM>> qq{} is the same as using double quotes to surround a string only
qq{} allows double quotes to appear inside without affected the quoting
of the whole string.  Double quotes *DOES* allow variable
interpolation.

What is the difference? I am new to Perl/DBI stuff.

Robert

HM>> Suggestion - read up on using "Placeholders".  Do

 perldoc DBI

at a command prompt and read the excellent perldocs for the DBI module.
 Placeholders are described in-depth there.  The way I prefer is to use
q{} with placeholders - better performance and no quoting issues :-)

HTH.

Hardy Merrill

RE: no DBD:MSSQL?

[Hardy Merrill]

Janet, I've got DBD::ODBC installed on my Windows XP machine.  When I do
"perldoc DBD::ODBC" in a command prompt window I can see the excellent
documentation that is part of the DBD::ODBC module.  Here are some of
the section titles in that perldoc:
-
What is DBD::ODBC? Why can't I connect? Do I need an ODBC driver? What
is the ODBC driver manager?

Where do I get an ODBC driver manager for Unix/Linux?

How do I access a MS SQL Server database from Linux?

For Unix -> Windows DB see Tom Lowery's write-up.

http://tlowery.hypermart.net/perl_dbi_dbd_faq.html#HowDoIAccessMSWin 
dowsDB


So for any module you have installed, at a command prompt you can do
"perldoc [module name]" to see the documentation included with the
module.  You can also see the perldocs online - the main perldoc site is
http://www.perldoc.com.  This link might be some help:

 
http://www.perldoc.com/perl5.6.1/lib/DBI/FAQ.html#3.1-What's-the-difference-between-ODBC-and-DBI-

Hopefully this will give you some more info to go on.

Hardy Merrill


>>> Janet Goldstein <[EMAIL PROTECTED]> 07/13/04 09:58PM >>>
On Tue, 13 Jul 2004, Tim Johnson wrote:

>
> Why not DBD::ODBC?

 because, I'm embarrassed to admit, I have no clue how ODBC works
in
Unix.. ;-}

Re: Re: Truncation Issues with SQL Server Insert Statement

[Hardy Merrill]

Sorry amonotod,

I don't recall what Michael (Peppler) said about BLK API, but if he
said it I'm sure it's true :-)   Michael is *the* guy for DBD::Sybase
and dare I say one of the most knowledgable DBI, DBD::Sybase, Perl,
Sybase, etc. people around.  I don't mean to slight any others - there
are a lot of excellent people here.  My first introduction to Perl DBI
was several years ago with Sybase and DBD::Sybase, and Michael was very
fast to respond to problems/questions and was very helpful.  Too many
other excellent people to list here, but since you were wondering about
something Michael said I thought I would elaborate a little ;-)

>>> amonotod <[EMAIL PROTECTED]> 07/13/04 04:11PM >>>
> From: "Hardy Merrill" <[EMAIL PROTECTED]>
> Date: 2004/07/13 Tue PM 07:42:25 GMT
 
> Original poster: did you use either the "quote" method, or better
yet
> did you use placeholders?? 

You know, to be more accurate, I am indeed using placeholders, 
after constucting my sql statement from my field names.  Placeholders 
are a really cool way to do it, I've been getting 300K+ inserts done 
in less than a minute.  

I noticed, in the message this morning from M. Peppler, that he
mentioned
 a BLK API, but I've never heard of this before.  Does DBI in general 
support this, or only certain DBDs?

> Hardy Merrill

Thanks,
amonotod (all lower case, btw... :-)


-- 
`\|||/ amonotod@| subject line: 
  (@@) charter.net  | no perl, no read...
  ooO_(_)_Ooo
  _|_|_|_|_|_|_|_|

Re: Truncation Issues with SQL Server Insert Statement

[Hardy Merrill]

The problem with the original question/issue is that the poster did not
include any DBI code.  Gathering from Amonotod's comments below, the
issue may be the quoting (or lack of proper quoting) done.

Original poster: did you use either the "quote" method, or better yet
did you use placeholders??  If you could post some of the DBI code for
your inserts into SQL Server, that might help ;-)

Hardy Merrill

>>> amonotod <[EMAIL PROTECTED]> 07/13/04 03:36PM >>>
> From: Tim Bunce <[EMAIL PROTECTED]>
> Date: 2004/07/13 Tue PM 06:38:51 GMT

>I have been writing a perl script that takes a row of 43 fields,
splits on a tab, and performs an
>execute_array into an SQL Server database, all in a loop.  The
insert statement works fine, it doesn't
>generate any errors as far as I can tell, and all the data gets
inserted into the database.  The only
>problem is, a few of the fields are truncated.  

I was having issues similar to this, until I switched to DBD::CSV to
parse 
my input files and started using quote() instead of s///... Works great

now.  So, "Thanks, Jeff and Jochen!"...

HTH,
amonotod


-- 
`\|||/ amonotod@| subject line: 
  (@@) charter.net  | no perl, no read...
  ooO_(_)_Ooo
  _|_|_|_|_|_|_|_|

Re: no DBD:MSSQL?

[Hardy Merrill]

Janet, I think David was saying that you might(?) be able to use the
DBD::Sybase driver to connect to MS SQL Server ;-)

Hardy Merrill

>>> Janet Goldstein <[EMAIL PROTECTED]> 07/13/04 11:14AM >>>
David N Murray wrote:
> 
> google turns up http://search.cpan.org/search?module=DBD::ADO 
> ADO & ODBC are your only options, AFAIK, on Windows.
> Maybe someone more informed can comment on using the Sybase driver.

Thanks for your reply, Dave. Actually, I'm not limited to Windows; I
would just as soon access the database from my Unix box. However, it's
not Sybase I'm trying to get to here; it's Microsoft SQL Server.

Re: Automating database full backup

[Hardy Merrill]

A bit OT here, but I'm curious - does mysqlhotcopy allow you to do a
backup without shutting down MySQL?

http://dev.mysql.com/doc/mysql/en/mysqlhotcopy.html

I know this is over-simplifying, but couldn't automating MySQL backups
with mysqlhotcopy be as simple as adding a MySQL user with read(?) privs
to the database(s) to be backed up, and then putting a "mysqlhotcopy"
command with all the necessary options into a cron job or windows
scheduler?

Hardy

>>> Tim Bunce <[EMAIL PROTECTED]> 07/07/04 01:30PM >>>
I believe mysql also ships with mysqlhotcopy (which I wrote)
you may find it useful.

Tim.

On Wed, Jul 07, 2004 at 10:05:42AM -0400, Hardy Merrill wrote:
> I quick search on google for "automate mysql backup perl dbi"
> 
> revealed this as one of the 1st 5 links:
>  http://www.csc.liv.ac.uk/~u2pj/SQL_Perl.htm 
> 
> Check out "RW MySQL Dump" - looks to me like it might be just what
> you're looking for.  Or if you still want to do it yourself, just
> reading its description might put you on the right track.
> 
> You'll get more help here if you actually try to help yourself first
-
> at least make an effort to find some information *before* you ask
for
> help.
> 
> Hardy Merrill
> 
> >>> "John" <[EMAIL PROTECTED]> 07/07/04 09:52AM >>>
> I want to automate a mysql backup
> 
> Can this be done via perl and how?

Re: Automating database full backup

[Hardy Merrill]

I quick search on google for "automate mysql backup perl dbi"

revealed this as one of the 1st 5 links:
 http://www.csc.liv.ac.uk/~u2pj/SQL_Perl.htm

Check out "RW MySQL Dump" - looks to me like it might be just what
you're looking for.  Or if you still want to do it yourself, just
reading its description might put you on the right track.

You'll get more help here if you actually try to help yourself first -
at least make an effort to find some information *before* you ask for
help.

Hardy Merrill

>>> "John" <[EMAIL PROTECTED]> 07/07/04 09:52AM >>>
I want to automate a mysql backup

Can this be done via perl and how?

Re: Query error

[Hardy Merrill]

Andy, *what* is that statement supposed to be doing?

Typically you
   1. prepare the sql
   2. execute the sql

This statement:

   my $sql = qq{ SELECT * FROM fasta WHERE accession LIKE 'NM_000367'
}
  or die "Can't prepare SQL statement: 
 ",$ucscHandle->errstr(), "\n";

is neither.  It In fact, the warning indicates that Perl thinks you are
trying to do a comparison.  Where is the 'prepare' - where is the
'execute'??  All that statement does is set $sql equal to a string - the
die is meaningless.

Hardy Merrill


>>> Andy Hammer <[EMAIL PROTECTED]> 07/01/04 08:54PM >>>
I am getting the warning:
Found = in conditional, should be == at NM_000367.pl line 32.

line 32 contains my sql query:
my $sql = qq{ SELECT * FROM fasta WHERE accession LIKE 'NM_000367' }
  or die "Can't prepare SQL statement: 
",$ucscHandle->errstr(), "\n";

The query fuctions fine, but I am curious why I am getting that
warning...?

-- 
Andy Hammer
Gesteland / Atkins Lab

Re: perl5.004 for AIX5.1

[Hardy Merrill]

How about http://www.perl.com/download.csp

>>> "rachakonda muralikrishna" <[EMAIL PROTECTED]> 06/30/04
12:45AM >>>
Hi all,

 I would like to know where I can get the perl version 5.004 for AIX.

 I haven't found that on any of the prel sites.

 Any help in this regard is highly appreciated.
Thanks,
Murali

RE: DBD Error on test

[Hardy Merrill]

IIRC (and that's a BIG if since I have never done this myself on HP-UX)
there is a separate DBD::Oracle README file for HP-UX.

Here is one link I found on google by searching for "perl dbd::oracle
hp ux readme":

 http://perl.active-venture.com/README.hpux.html

HTH.

Hardy Merrill

>>> "Reidy, Ron" <[EMAIL PROTECTED]> 06/28/04 10:36AM >>>
Did you:

1.  Read the README files?
2.  Ensure your Oracle environment is specified correctly
(LD_LIBRARY_PATH or it's HP-UX equivalent, etc.)?

-
Ron Reidy
Senior DBA
Array BioPharma, Inc.


-Original Message-
From: MCMULLIN, NANCY [mailto:[EMAIL PROTECTED] 
Sent: Monday, June 28, 2004 8:07 AM
To: [EMAIL PROTECTED] Org (E-mail)
Subject: DBD Error on test


Still attempting to load DBD::Oracle  (HPUX 11i , Perl 5.8)
  
I got it past make by substituting all +s and +n's with -W1 and -Wl . 


Now test is failing with  the error (s) :
Exec format error at
/opt/perl/lib/5.8.0/PA-RISC1.1-thread-multi/DynaLoader.pm line 229.
 at (eval 1) line 3
Perhaps a required shared library or dll isn't installed where
expected
 at t/base.t line 19  
 
(Perl works fine with other modules)
 
If anyone has any ideas of where to go from here, I certainly would
appreciate it.  I'm almost to the point of using Activestate and windows
as the Perl Server, connected to the HP, since that works right now.  I
hate saying that, because I hate windows products.  I really would like
to make this work under Unix!!
 
Thanks.
Nancy 

 


This electronic message transmission is a PRIVATE communication which
contains
information which may be confidential or privileged. The information is
intended 
to be for the use of the individual or entity named above. If you are
not the 
intended recipient, please be aware that any disclosure, copying,
distribution 
or use of the contents of this information is prohibited. Please notify
the
sender  of the delivery error by replying to this message, or notify us
by
telephone (877-633-2436, ext. 0), and then delete it from your system.

RE: DBI_DSN, DBD-Oracle & ODBC Examples?

[Hardy Merrill]

George, just in case you're not familiar with 'perldoc', here's a little
primer - 'perldoc' is the excellent documentation utility installed when
Perl is installed.  Start by doing

perldoc perl

at a command prompt.

As Ron suggested below, to looking up setting environment variables,
do

   perldoc perlvar

And, virtually every Perl module comes with a perldoc that describes
how to use that module.  For example, to read the excellent DBI
perldocs, after installing the DBI module you would do "perldoc DBI" at
a command prompt.  Likewise for the DBD::Oracle module, you would do
"perldoc DBD::Oracle" at a command prompt.

The perldocs are also available online at www.perldoc.com.

And on Windows if you've installed ActiveState Perl, you can get the
perldocs documentation for core modules (and modules installed by ppd)
by going to

   Start
  - All Programs
  - ActiveState ActivePerl 5.8 (or whatever version you have)
  - Documentation

HTH.

Hardy Merrill

>>> "Reidy, Ron" <[EMAIL PROTECTED]> 06/24/04 01:01PM >>>
George,

1.  Setting environment variables - perldoc perlvar
2.  Using DBI, DBD::ODBC, DBD::Oracle - perldoc DBI, perldoc DBD::ODBC,
perldoc DBD::Oracle

For Oracle connectivity issues, see the Oracle Networking docs or your
friendly neighborhood DBA

-
Ron Reidy
Senior DBA
Array BioPharma, Inc.



-Original Message-
From: HSIA, GEORGE (SBCSI) [mailto:[EMAIL PROTECTED] 
Sent: Thursday, June 24, 2004 10:45 AM
To: Hardy Merrill; [EMAIL PROTECTED]; [EMAIL PROTECTED] 
Subject: DBI_DSN, DBD-Oracle & ODBC Examples?


Thanks Hardy, Dennis, and Jeff,

I have corrected my path and got all (DBI, DBD-Oracle, DBD-ODBC)
installed successfully(under c:\Perl\site).
I also appreciate two other gentlemen's emails this morning from
dbi-user group.

1) How do I setup the DBI_DSN environment variable for my Perl and
Oracle db residing locally on my pc?
2) Do you have a short Perl example using DBD-Oracle to call
DBI->connect() and DBI->prepare(???) and DBI->execute(???)?
3) Another example using the DBD-ODBC to call DBI->connect()
and
DBI->prepare(???) and DBI->execute(???)locally?

Thanks all again.
George


-Original Message-
From: HSIA, GEORGE (SBCSI) 
Sent: Thursday, June 24, 2004 9:31 AM
To: 'Hardy Merrill'; [EMAIL PROTECTED] 
Subject: RE: DBI,DBD-Oracle install for AS Perl URGENT!!


Thanks Hardy, I have found my path is not right.
I have installed it successfully.
Your great help over the cyberspace and another help from Dennis' over
the continent(Austria?) is very appreciated!!
George 



This electronic message transmission is a PRIVATE communication which
contains
information which may be confidential or privileged. The information is
intended 
to be for the use of the individual or entity named above. If you are
not the 
intended recipient, please be aware that any disclosure, copying,
distribution 
or use of the contents of this information is prohibited. Please notify
the
sender  of the delivery error by replying to this message, or notify us
by
telephone (877-633-2436, ext. 0), and then delete it from your system.

Re: DBI,DBD-Oracle install for AS Perl URGENT!!

[Hardy Merrill]

George, Jeff has now told you *twice* what you need to do, and you
haven't done what he suggested yet.  If you're going to ask for help
here you need to at least try what people suggest.  And
just-so-you-know, asking for phone numbers isn't really appropriate.

Here are the ftp url's that Jeff told you to use:

   ppm install ftp://ftp.esoftmatic.com/outgoing/DBI/5.8.3/DBI.ppd
And
   ppm install
ftp://ftp.esoftmatic.com/outgoing/DBI/5.8.3/DBD-Oracle.ppd

-

And here is the one you have problems with:

  ppm install ftp://ftp.esoftmatic.com/outgoing/5.8.3/DBI.ppd

Do you see the difference between yours and Jeff's DBI.ppd path???

Hardy Merrill


>>> "HSIA, GEORGE (SBCSI)" <[EMAIL PROTECTED]> 06/23/04 04:43PM >>>

I have ActiveState Perl 5.8.3.
I used the PPM to install the DBD-Oracle driver to my pc:
C:\>ppm install ftp://ftp.esoftmatic.com/outgoing/5.8.3/DBI.ppd 

But I got the Error: Failed to download URL
ftp://ftp.esoftmatic.com/outgoing/5.8.3/DBI.ppd: 404 Can't chdir to
5.8.3

So, I went to ftp://ftp.esoftmatic.com/outgoing/ and click the 5.8.3,
then chose DBI.ppd, but there is no such file "DBI.ppd" there!!

Could you check that FTP website for me??? -- I will need both the DBI
and DBD-Oracle.

Alternatively, I also went to your CPAN site to check the drivers for
DBD-Oracle-1.15   and DBI-1.42   and
found
they both are the *.tar. Gz files.
How do I use the PPM on the command line to install these drivers for
AS
Perl 5.8.3? -- I need the detailed commands step-by-step.

Thanks.
George Hsia
(925)823-2417

Re: stored precedures with array as parameter

[Hardy Merrill]

Have you read the DBD::Oracle perldocs?  I haven't done that myself, but
I'm almost certain the DBD::Oracle perldocs describe how to invoke a
stored procedure.

>>> <[EMAIL PROTECTED]> 06/21/04 11:11AM >>>
Hi all,

I'm looking for a way to call stored precedures directly with one or
more 
arrays as input parameter via DBD:Oracle interface. 
Does anybody has a hint for me how I can do this ?

Let me say that the function that I would like to call looks similar to

the one below:

CREATE OR REPLACE PACKAGE mystorage  IS
  TYPE my_tagnames IS TABLE OF varchar2(200) INDEX BY BINARY_INTEGER;

FUNCTION  insert_sm  (msgid IN varchar2,
tagnamesIN my_tagnames,
tagvalues   IN my_tagnames,
sf_info OUT VARCHAR2
   ) RETURN NUMBER;

 
END mystorage;
/

How should the prepare statement look like and how can I forward the 
array's to the function ?

Thank for any help in advance !

kind regards,

frank

Re: Perldocs website?

[Hardy Merrill]

Thanks Jeff.  That explains why I don't see DBI show up in the Active
State documentation - I wasn't able to get PPM to through our firewall
and/or proxy, so I resorted to downloading the package and installing it
manually.

>>> Jeff Zucker <[EMAIL PROTECTED]> 06/04/04 10:33AM >>>
Hardy Merrill wrote:

> One question - does the documentation for CPAN modules that I
install
> get inserted into the Active State documentation?

AFAIK, anything you install with Activestate ppm will add its docs to 
the HTMLized help.  If you install with CPAN or manually, the docs
won't 
get added.  You could always do a pod2html on the module and copy that

into the Activestate tree.

Personally, since I got broadband, I often go to search.cpan.org to
look 
at docs because it has cool things on top of the HTML - like a button
to 
look directly at the source and the ability to diff versions from a
URL. 
   OTOH, things like perlpod and perlfunc I tend to look at locally.

-- 
Jeff

Re: Perldocs website?

[Hardy Merrill]

I see folder C:\Perl\html\site\lib\DBI, but no html files in there -
just these 4 empty directories:
C:\Perl\html\site\lib\DBI\Const
C:\Perl\html\site\lib\DBI\Const\GetInfo
C:\Perl\html\site\lib\DBI\DBD
C:\Perl\html\site\lib\DBI\ProfileDumper

Can anyone else shed more light on how to access installed module
documentation on Windoze XP - other than command prompt "perldoc ABC"??

>>> Michael Ragsdale <[EMAIL PROTECTED]> 06/04/04 10:25AM >>>
At 10:19 AM 6/4/2004, Hardy Merrill wrote:
>Thanks Jeff - didn't know about that, but the Active State
documentation
>pages are very nice!
>
>One question - does the documentation for CPAN modules that I install
>get inserted into the Active State documentation?  For example, I
have
>DBI installed but I can't seem to locate it in the Active State
>documentation - is it there somewhere?
>
>Thanks.
>
>Hardy

On my Windoze machine, it's located here:
C:\Perl\html\site\lib\DBI.html

-Mike 

Re: Perldocs website?

[Hardy Merrill]

Thanks Jeff - didn't know about that, but the Active State documentation
pages are very nice!

One question - does the documentation for CPAN modules that I install
get inserted into the Active State documentation?  For example, I have
DBI installed but I can't seem to locate it in the Active State
documentation - is it there somewhere?

Thanks.

Hardy

>>> Jeff Zucker <[EMAIL PROTECTED]> 06/04/04 09:56AM >>>
Hardy Merrill wrote:
> Hi Randal,
> 
> I'm a relative newbie on Windoze XP, but in order to view the
perldocs
> there I have to bring up a command prompt window and type 'perldoc
DBI'

I don't know XP, but on 98, go to 
start-programs-activestate-documentation and you'll find a fully 
HTMLized version of the perldocs and moudle pods.  You can use window's

"Find" to do full text searches on it.

-- 
Jeff

Re: Perldocs website?

[Hardy Merrill]

Hi Randal,

I'm a relative newbie on Windoze XP, but in order to view the perldocs
there I have to bring up a command prompt window and type 'perldoc DBI'
(or whatever) and the window I get(done with 'more')  is not
searchable(with forward slash "/") the way it is on *nix.  Not having
the search facility makes the perldocs in a command prompt window not
nearly as useful, so I prefer to go to the perldocs website.  Is there
something I could do on Windoze XP to make the perldocs searchable??

Thanks.

Hardy Merrill

>>> Randal L. Schwartz <[EMAIL PROTECTED]> 06/03/04 03:50PM >>>
>>>>> "Hardy" == Hardy Merrill <[EMAIL PROTECTED]> writes:

Hardy> Anyone know where the perldocs website went?  I just went to
google and
Hardy> nothing seems to come up.  I can't find much on www.perl.org.

Why do you need a *website* for what is already on *your disk*?

/me boggles

-- 
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503 777
0095
<[EMAIL PROTECTED]> http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl
training!

Re: DBI questoin

[Hardy Merrill]

This link might help you get on the right track:

http://search.cpan.org/~timb/DBI/DBI.pm#bind_param

and look for a paragraph titled "Data Types for Placeholders" - that
shows you what stuff can be put in that 3rd bind_param argument.

HTH.

Hardy Merrill

>>> <[EMAIL PROTECTED]> 06/03/04 12:13PM >>>
Hi Mr. Bunce,

 

I am a developer recently using Perl on one of my projects. I got a
DBI
question but couldn't find solution by searching the web. I'll greatly
appreciate if you could help me out.

 

I am trying to port a C code into Perl using your DBI module. The C
code
uses ODBC API SQLBindParameter like below:

 

nRetVal = SQLBindParameter( m_hstmt, 1,
SQL_PARAM_INPUT, SQL_C_WCHAR, SQL_WVARCHAR,

15, 0,
Col2Values[nIndex] , 15, &lParameterSize);

 

The 4th and 5th parameter value type SQL_C_WCHAR, SQL_WVARCHAR must be
passed to the driver for Unicode support. However, your DBI module
bind_param function only provides 3 interfaces:

 

$rc = $sth->bind_param($p_num, $bind_value);

$rc = $sth->bind_param($p_num, $bind_value, $bind_type);

$rc = $sth->bind_param($p_num, $bind_value, \%attr);

 

I tried the 3rd interface, but I don't know what attributes the \%attr
expects. In ODBC.pm, I only saw attributes are NAME, NULLABLE, TYPE,
PRECISION and SCALE.

 

Would you please advise how can I use bind_param call in DBI to get
the
equivalent of the above C code.

 

Thank you in advance for your help,

Shuyan He

OCP, OCDBA

MCP, MCSD

SCJP

 

Re: Perldocs website?

[Hardy Merrill]

Yes - thanks.  Ok, I must have been hallucinating again - I could swear
I tried www.perldoc.com and it didn't come up, and then I went to google
and searched for 'perldocs' it didn't show up there either.  Searching
for 'perldoc' it does come up.  My bad :-/

Hardy

>>> Michael Ragsdale <[EMAIL PROTECTED]> 06/03/04 11:15AM >>>
At 11:17 AM 6/3/2004, Hardy Merrill wrote:
>Anyone know where the perldocs website went?  I just went to google
and
>nothing seems to come up.  I can't find much on www.perl.org.
>
>TIA.
>
>Hardy Merrill

Do you mean http://perldoc.com/ ?

Perldocs website?

[Hardy Merrill]

Anyone know where the perldocs website went?  I just went to google and
nothing seems to come up.  I can't find much on www.perl.org.

TIA.

Hardy Merrill

Re: Possible DBI logos

[Hardy Merrill]

You mean I'm older than Tim :-(   Happy Birthday Tim!

I think I prefer the orange/rust background with the yellow/gold
letters.

Have you tried "DBI" with just the "I" having the dark background "D"??
 That's the only variation I might suggest trying.

Hardy Merrill

>>> Jeff Zucker <[EMAIL PROTECTED]> 06/02/04 08:28PM >>>
In honor of Tim Bunce's 40th birthday, here are some samples of
possible 
DBI logos:

http://www.vpservices.com/jeff/programs/dbi-logo/ 

-- 
Jeff

Re: Problems using MySQL with Bugzilla

[Hardy Merrill]

I'm not sure what your particular problem is, but I went to google and
searched for "Can't connect to local MySQL server through socket " and
found lots of info.  Forgive me for questioning the obvious, but are you
sure MySQL is running?

Hardy Merrill

>>> <[EMAIL PROTECTED]> 04/29/04 09:57AM >>>
Hi,

i have a problem using MySQL (4.0.18) (together) with the current
Bugzilla release.
I think, that during installation of modules some configuration went
wrong
resulting in the error i now have to deal with:

Output of Bugzillas checksetup.pl:
...
Precompiling templates ...
DBI connect(';localhost;3306','bugs',...) failed: Can't connect to
local MySQL server through socket '/tmp/mysql.sock' (2) at
./checksetup.pl line 1189
[Thu Apr 29 15:39:47 2004] checksetup.pl: DBI
connect(';localhost;3306','bugs',...) failed: Can't connect to local
MySQL server through socket '/tmp/mysql.sock' (2) at ./checksetup.pl
line 1189
Software error:
...

In fact the MySQL installation i use is in /usr/local with the
databases in /usr/local/data.
The file mysql.sock is also in /usr/local/data.

What do i have to do, to get it running.

Thanks in advance
Ronald Nikel
_

Dipl.-Inform. Ronald Nikel
-System Development/Systementwicklung-
Heusch/Boesefeldt GmbH
Tempelhofer Str. 4-6
D-52068 Aachen
Tel: +49(0)241-9669-148
GSM: +49(0)177-7769536
Fax: +49(0)241-9669-177
WWW: http://www.heusch-boesefeldt.de 
mailto:[EMAIL PROTECTED] 

RE: Using Perl to "Source" variables

[Hardy Merrill]

I think Srikanth made a slight typo - see my comment below.

>>> Srikanth Madani <[EMAIL PROTECTED]> 04/08/04 09:51AM
>>>
>I have a general problem sourcing out variables in perl.  I certainly
can
>read the environment variables, but setting them persistently even
after
>the script has finished is something I'm having a little more
difficulty
>with.

Hi Scott,

You can set environment variables in a Perl script using
$ENV{ORA_VAR} = "value";
This will set the environment variable for that and all child
processes.
When the script exists, this variable will no longer be available.
  ^

Did you mean "exits"??  ;-)


So what you can do is call the subsequent program (which needs the
updated/new environment variable value) from within (and after) the
Perl
script which sets it.

If you really want to access the parent shell, see 2.8 of
comp.unix.questions [as suggested by perldoc -q environment] at
http://www.cs.uu.nl/wais/html/na-dir/unix-faq/faq/part2.html 

Cheers,

Srikanth Madani

perl -e'$_="87 69S69E75C69S78 68A32W73A83Y32!";($p,$i,$n,$k
,$f,$l,$o,$y,$d,$s)=(localtime,join"",map(chr,split/\D/));$o>4?print$s,
HERE:print$s,435660-86400*$o-3600*$n-60*$i-$p,join"",split/\d+/'
   

Re: My DBI script wont UPDATE a Table!

[Hardy Merrill]

Mark, you need to read the perldocs for the DBI module - you can do that
by doing

   perldoc DBI

at a command prompt.  Search (on *nix by using the forward "/" slash)
in the perldocs for "Transaction" and "eval".  You can also look here:

   http://www.perldoc.com/perl5.6.1/lib/DBI.html#Transactions

Here's a snippet showing how to use eval for error trapping:

  $dbh->{AutoCommit} = 0;  # enable transactions, if possible
  $dbh->{RaiseError} = 1;
  eval {
  foo(...)# do lots of work here
  bar(...)# including inserts
  baz(...)# and updates
  $dbh->commit;   # commit the changes if we get this far
  };
  if ($@) {
  warn "Transaction aborted because $@";
  $dbh->rollback; # undo the incomplete changes
  # add other application on-error-clean-up code here
  }

The "eval" causes die's inside that block to populate "$@" with the
text of the die message, and then causes execution to continue with the
next statement after that eval block.  I'm not sure what your specific
problem is, but this info. might give you some ideas.  Post back if
you're still having problems.

Hardy Merrill

>>> Mark Martin <[EMAIL PROTECTED]> 04/02/04 09:23AM >>>
Hi,
Database is Oracle.
The user does have update permissions
Connect Statement - $dbh = DBI->connect( "dbi:Oracle:database",
"user",
"pwd" ) or die "Can't connect to Oracle database: $DBI::errstr\n";
Error checking - yes the real programs have error checking on but
nothing is
coming back.
You asked about -RaiseError,PrintError, and AutoCommit - afraid I'm
not
familiar with these.
Thanks for responding,
Mark

- Original Message - 
From: "Hardy Merrill" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Friday, April 02, 2004 3:06 PM
Subject: Re: My DBI script wont UPDATE a Table!


> Paste in your database connect statement.  Are you setting
RaiseError,
> PrintError, and AutoCommit?  Which database are you using?  Are you
> doing any error checking of DBI statements?  And as was just pointed
out
> by someone else, does the user you are connecting with have update
> permissions in your production database?
>
> >>> Mark Martin <[EMAIL PROTECTED]> 04/02/04 08:52AM >>>
> Hi,
> I designed my application on a test database and it worked fine
without
> commiting the database handle. But now when I've moved it to the
> production database my updates wont commit - even when I do issue a
> commit on the DBH?!?!
>
> while(certain condition) {
> $sql = qq{UPDATE table SET
> field=value} ;
> $sth = $dbh->prepare($sql) ;
> $sth->execute;
>
> $dbh->commit();
>
>   }
> $sth->finish;
> $dbh->disconnect;
>
> I've tried the commit outside the while statement with no luck. Any
> ideas?
> Mark

Re: My DBI script wont UPDATE a Table!

[Hardy Merrill]

Paste in your database connect statement.  Are you setting RaiseError,
PrintError, and AutoCommit?  Which database are you using?  Are you
doing any error checking of DBI statements?  And as was just pointed out
by someone else, does the user you are connecting with have update
permissions in your production database?

>>> Mark Martin <[EMAIL PROTECTED]> 04/02/04 08:52AM >>>
Hi,
I designed my application on a test database and it worked fine without
commiting the database handle. But now when I've moved it to the
production database my updates wont commit - even when I do issue a
commit on the DBH?!?!

while(certain condition) {
$sql = qq{UPDATE table SET
field=value} ;
$sth = $dbh->prepare($sql) ;
$sth->execute;

$dbh->commit();

  }
$sth->finish;
$dbh->disconnect;

I've tried the commit outside the while statement with no luck. Any
ideas?
Mark

Re: insert into two table and check table perl DBI

[Hardy Merrill]

I believe that currently the only way to properly handle that is to
capture the error output  ( 'perldoc DBI' at a command prompt and search
for "Transaction" and "eval") and parse that output for something common
to all instances where you try to insert a duplicate record.  Something
like the string "Duplicate entry" maybe.  If you find "Duplicate entry"
in the error output, then you can display "Cannot insert, same data" if
you want.

A few months ago we had discussed on this list _standardizing_ error
handling for things like trying to insert duplicate records, but I kind
of dropped the ball - changing jobs and all didn't leave me enough time
to persue that standardization with members of this list.  And since now
I don't use Perl in my new job, I don't have time to persue it at all. 
Anyone else have time to revisit standardizing error handling for things
like "Trying to insert duplicate record"???  Basically all it involved
was putting together a list of common error conditions and finding the
corresponding ODBC error codes.

Hardy Merrill

>>> Michael Ragsdale <[EMAIL PROTECTED]> 03/31/04 04:09PM >>>

>hai everyone, i have write scripts perl on the top and if i input
data
>12345678,Hendra Kusnandar,123456 data will insert
>into table user, and if i input wrong format data will insert into
log.
>this scripts is work but if i insert again 12345678,Hendra
Kusnandar,123456
>scripts will error and the message are : DBD::mysql::st execute
failed:
>Duplicate entry '12345678' for key 1 at ./test.pl line 20, <> line 1.
>Unable to execute query: DBI::db=HASH(0x81e1ed0)->errstr
>i know field nik in table user on mysql is primary key that i can't
insert
>same data...how to correct this scripts if i insert the same data and
will
>show the message on shell "cannot insert, same data".

Actually, you can insert the same data.  Instead of using INSERT, use 
REPLACE.  If the data does not exist, the statement will act just like
an 
INSERT statement.  If the data (unique value) already exists, the
fields in 
the REPLACE statement will be overwritten with the new data.  This
won't 
give you any sort of "same data" error, but it will prevent the
"execute 
failed" errors.  This may or may not do the trick for you, depending on
how 
you want to handle duplicate data from your input source.

-Mike 

Re: Access into MVS DB2 using DBI or ???

[Hardy Merrill]

You normally need DBI *and* the appropriate DBD:: module for your
particular database - in this case probably DBD::DB2.  I have no
experience connecting to MVS DB2 so I can't offer any more help here.

Hardy Merrill

>>> "Wagner, David --- Senior Programmer Analyst --- WGO"
<[EMAIL PROTECTED]> 03/30/04 06:34PM >>>
Uuncertain where to start or really what to use to try and see
if I can do this?  I searched under Activestate and found a large group
of DBI modules. 

Do I just need DBI? Or are there other modules which I work with
DBI?

I am on XP Professional running service pack 1 and Perl AS 5.8.2
build 808.

  Any questions and/or problems, please let me know.

  Thanks.

Wags ;)
Int: 9-8-002-2224
Ext: 408-323-4225x2224




**
This message contains information that is confidential
and proprietary to FedEx Freight or its affiliates.
It is intended only for the recipient named and for
the express purpose(s) described therein.
Any other use is prohibited.

RE: Why won't my script terminate?

[Hardy Merrill]

"Wow, I was way off!" - quote from "Dumb and Dumber".  Like I said,
Ronald is much more knowledgable than I :-)

Hardy

>>> "Ronald J Kimball" <[EMAIL PROTECTED]> 03/24/04 04:50PM >>>
The $sth->finish() isn't doing anything; there's an implicit
$sth->finish()
when $sth->fetchrow_hashref() gets to the end of the result set in
your
while loop.

I think that another session had a lock on the table, which was causing
the
UPDATE statement to block, and that this other session has since
released
the lock.

I created my own ChunkMail table, and the original script worked just
fine.

Ronald


> -Original Message-
> From: Laurie Vien [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, March 24, 2004 4:08 PM
> To: 'James D. White'; Laurie Vien
> Cc: '[EMAIL PROTECTED]' 
> Subject: RE: Why won't my script terminate?
> 
> Thank you very much to everyone who replied.  The combination of
your
> answers put me on the right path; I got it!
> 
> I moved the "$sth->finish();" up to right after the close of the
While
> loop,
> instead of leaving it at the end of the script.  That solved it.
> 
> Thanks, all, for your help.
> 
> Laurie
> 
 

RE: Why won't my script terminate?

[Hardy Merrill]

Ronald is much more knowledgable than I am, but I _do_ think your
problem is related to opening STDOUT.  Try an earlier suggestion of
opening and writing to a different filehandle other than STDOUT, and see
if your script still hangs - if that works fine than you know your
problem is related to opening STDOUT.

Just to beat the dead horse into total submission, here's one final
article on using OPEN to redirect STDOUT to a file:

http://www.rocketaware.com/perl/perlfunc/open.htm

Here's a snippet from that fairly short article:

=

Here is a script that saves, redirects, and restores STDOUT and STDERR:


#!/usr/bin/perl
open(SAVEOUT, ">&STDOUT");
open(SAVEERR, ">&STDERR");


open(STDOUT, ">foo.out") || die "Can't redirect stdout";
open(STDERR, ">&STDOUT") || die "Can't dup stdout";


select(STDERR); $| = 1; # make unbuffered
select(STDOUT); $| = 1; # make unbuffered


print STDOUT "stdout 1\n";  # this works for
print STDERR "stderr 1\n";  # subprocesses too


close(STDOUT);
close(STDERR);


open(STDOUT, ">&SAVEOUT");
open(STDERR, ">&SAVEERR");


print STDOUT "stdout 2\n";
print STDERR "stderr 2\n";

=

HTH.

Hardy Merrill

>>> Laurie Vien <[EMAIL PROTECTED]> 03/24/04 03:18PM >>>
There are only 3 records in the input table, and they've all been
processed
to the output file.  :-)

-Original Message-
From: Ronald J Kimball [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, March 24, 2004 3:22 PM
To: 'Hardy Merrill'; [EMAIL PROTECTED]; [EMAIL PROTECTED] 
Subject: RE: Why won't my script terminate?


She's not re-opening STDOUT as an "in memory" file, she's opening it as
a
regular file handle.  This doesn't apply.

I doubt very much that opening STDOUT to a file is the cause of the
script
not terminating.  I suspect that either there is an infinite loop, or
there's just a lot of data to process and the original poster isn't
allowing
enough time for the script to finish.

Ronald


> -Original Message-
> From: Hardy Merrill [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, March 24, 2004 3:06 PM
> To: [EMAIL PROTECTED]; [EMAIL PROTECTED] 
> Subject: Re: Why won't my script terminate?
> 
> I just found this in 'perldoc -f open':
> 
>     Though if you try to re-open "STDOUT" or "STDERR" as an "in
> memory" file, you have to close it first:
> 
> close STDOUT;
> open STDOUT, '>', \$variable or die "Can't open STDOUT: $!";
> 
> So try closing STDOUT first before opening it for append.
> 
> HTH.
> 
> Hardy Merrill
> 
> >>> Laurie Vien <[EMAIL PROTECTED]> 03/24/04 02:44PM >>>
> I am running a very simple Perl script using DBI (skeleton of it
> follows).
> It does everything I expect it to, but the problem is it doesn't
> finish
> until I Ctrl-C, at which time I get the message "Terminating on
signal
> SIGINT(2)".  What have I left out or done in the wrong order that
> causes it
> not to terminate?:
> 
> #MyPerlScript.pl
> use DBI;
> use Date::Manip;
> 
> $dbh = DBI->connect($DB_CONN, $DB_USER, $DB_PASS) || die "Can't
> connect:
> $dbi::errstr";
> open(STDOUT, ">>myfile.txt") or die "\nCould not open STDOUT: $!";
> 
> $sth = $dbh->prepare("SELECT * FROM MyTable");
> $sth->execute();
> 
> # < Do bunches of stuff here >
> #
> #
> 
> $sth->finish();
> $dbh->disconnect();
> close(STDOUT);
> 
> Laurie
> 

Re: Why won't my script terminate?

[Hardy Merrill]

I just found something else at

   http://perlmonks.thepen.com/11007.html

that talks about using IO::Handle to redirect STDIN, STDOUT, or STDERR
to a file.

HTH.

Hardy Merrill

>>> Laurie Vien <[EMAIL PROTECTED]> 03/24/04 02:44PM >>>
I am running a very simple Perl script using DBI (skeleton of it
follows).
It does everything I expect it to, but the problem is it doesn't
finish
until I Ctrl-C, at which time I get the message "Terminating on signal
SIGINT(2)".  What have I left out or done in the wrong order that
causes it
not to terminate?:

#MyPerlScript.pl
use DBI;
use Date::Manip;

$dbh = DBI->connect($DB_CONN, $DB_USER, $DB_PASS) || die "Can't
connect:
$dbi::errstr";
open(STDOUT, ">>myfile.txt") or die "\nCould not open STDOUT: $!";

$sth = $dbh->prepare("SELECT * FROM MyTable");  
$sth->execute();

# < Do bunches of stuff here >
#
#

$sth->finish();
$dbh->disconnect();
close(STDOUT);

Laurie

Re: Why won't my script terminate?

[Hardy Merrill]

I just found this in 'perldoc -f open':

Though if you try to re-open "STDOUT" or "STDERR" as an "in
memory" file, you have to close it first:

close STDOUT;
open STDOUT, '>', \$variable or die "Can't open STDOUT: $!";

So try closing STDOUT first before opening it for append.

HTH.

Hardy Merrill

>>> Laurie Vien <[EMAIL PROTECTED]> 03/24/04 02:44PM >>>
I am running a very simple Perl script using DBI (skeleton of it
follows).
It does everything I expect it to, but the problem is it doesn't
finish
until I Ctrl-C, at which time I get the message "Terminating on signal
SIGINT(2)".  What have I left out or done in the wrong order that
causes it
not to terminate?:

#MyPerlScript.pl
use DBI;
use Date::Manip;

$dbh = DBI->connect($DB_CONN, $DB_USER, $DB_PASS) || die "Can't
connect:
$dbi::errstr";
open(STDOUT, ">>myfile.txt") or die "\nCould not open STDOUT: $!";

$sth = $dbh->prepare("SELECT * FROM MyTable");  
$sth->execute();

# < Do bunches of stuff here >
#
#

$sth->finish();
$dbh->disconnect();
close(STDOUT);

Laurie

Re: Why won't my script terminate?

[Hardy Merrill]

I don't see anything wrong with your code - is the "bunches of stuff"
section too big to include?  Also include the standard versions of OS,
Perl, DBI, which database you're using and which DBD (and version of
DBD).

>>> Laurie Vien <[EMAIL PROTECTED]> 03/24/04 02:44PM >>>
I am running a very simple Perl script using DBI (skeleton of it
follows).
It does everything I expect it to, but the problem is it doesn't
finish
until I Ctrl-C, at which time I get the message "Terminating on signal
SIGINT(2)".  What have I left out or done in the wrong order that
causes it
not to terminate?:

#MyPerlScript.pl
use DBI;
use Date::Manip;

$dbh = DBI->connect($DB_CONN, $DB_USER, $DB_PASS) || die "Can't
connect:
$dbi::errstr";
open(STDOUT, ">>myfile.txt") or die "\nCould not open STDOUT: $!";

$sth = $dbh->prepare("SELECT * FROM MyTable");  
$sth->execute();

# < Do bunches of stuff here >
#
#

$sth->finish();
$dbh->disconnect();
close(STDOUT);

Laurie

Re: DBI and Mysql Insert

[Hardy Merrill]

I'm only familiar with the "build the SQL" method, but if there's an
alternate method it would be described in the excellent DBI perldocs,
which you can access by doing

perldoc DBI

at a command prompt.

HTH.

Hardy Merrill

>>> "Chris Faust" <[EMAIL PROTECTED]> 03/12/04 09:33AM >>>
Good Morning,

Please forgive if this is considered off topic, I thought it would be
the best place to start.

I think I have a very simple question, I have a perl hash in which all
the keys are my tables field names and the values are the values that I
wish to insert into my table.

Is there is a simple way using DBI that I can just pass the hash off
for a insert or is the only way to do it is to first foreach over the
hash and construct the SQL statement and then when finished, execute
it..

I guess I'm wondering if there is any kind of magic where I could say
something like

$sth = $db->prepare("Insert into table")
$sth->execute(%hash_where_keys_are_fieldnames_and_values_are_the_insert_values);

Thanks
-Chris

RE: "identifier too long"?

[Hardy Merrill]

Notice too that in Jeff's example he used the q() construct which is
fine too when using placeholders since no variables need to be
interpolated.

>>> "Mark Galbraith" <[EMAIL PROTECTED]> 03/11/04 06:00PM
>>>
Jeff Zucker  wrote:

> Your quote marks were messing you up and the easiest way to
> avoid that
> and other problems is to use placeholders (the ? in the $sql_dmd).

Or use the Perl qq() construction which avoids the problem
alltogether.

-- 
Mark Galbraith
CERT UNIX Application Engineer
Data Return LLC

RE: How to connect Oracle 9i (Windows )

[Hardy Merrill]

>>> "Zhang, George" <[EMAIL PROTECTED]> 03/11/04 10:03AM
>>>
Yes, it is possible to connect to Oracle on Windows (actually, it
doesnot
matter what OS oracle runs on since Oracle takes care of network
communication).

You need to install Oracle client on your linux system.  The doc here
is
very helpful:
http://www.oracle-base.com/articles/9i/Oracle9iInstallationOnFedora1.php.
You can find other docs for other platform on the site too.

Once you installed Oracle client, config your tnsnames.ora (located at
$ORACLE_HOME/network/admin) file so that it can access the database on
the
Oracle server.

George



And once you've done what George suggests above, then you need to
install DBI and DBD::Oracle on the Red Hat Linux system.  _Then_ you
should be able to connect from your RH Linux client to the Oracle server
on Windoze using Perl.

HTH.

Hardy Merrill
 

-Original Message-
From: Mahi.G [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 11, 2004 9:50 AM
To: [EMAIL PROTECTED] 
Subject: How to connect Oracle 9i (Windows )

Hai 

 I am working on Redhat Linux system with Perl. But I want to connect
with
Oracle 9i database in my Network.
But The Oracle is installed in Windows System. Is it possible to
connect
the Oracle 9i by using Perl script. If possible pls guide me ( I am new
for
database connection ).

 If u know any good urls pls forward to me

Thanks

Mahi. G



__
Do you Yahoo!?
Yahoo! Search - Find what you're looking for faster
http://search.yahoo.com

Re: DBI - copying tables - a better way?

[Hardy Merrill]

If your goal is just to make an exact copy of a table to a table with a
different name, you may not need DBI for that.  I just did this a few
weeks ago in Oracle - this is from memory and may not be quite right,
but you'll hopefully get the idea:

   create table table_b as (select * from table_a)

I did that command in sqlplus - table_b is an exact copy of table_a.

HTH.

Hardy Merrill

>>> Mark Martin <[EMAIL PROTECTED]> 03/11/04 04:21AM >>>
Hi,
bin using DBI for a while for data warehousing type stuff and up till
now I've had to change data on the fly as it moves from one database to
another : 

my $sql01 = qq{SELECT FIELD01 .FIELDn FROM PRODUCTION_TABLE};
my $sql02 = qq{INSERT INTO DATAWAREHOUSE VALUES (?,?,?);
my $sth = $dbh->prepare($sql01);
$sth->execute;
while (my @row = $sth->fetchrow) {  $data1 = $row[0];   $data2 =
$row[1]; .manipulate the dat with perl . and so on

But, now, I just want to copy one table into an exact replica and to
save on coding I want to avoid having to reference each DB column and
the contents of it per row. Here's what I've got so far, the SELECT is
working and I know pushing onto seperate arrays is wrong (gives
HASH(0x820c324) etc. with print print "@rows";) but I'm stuck on how to
take it further. 

#!/usr/bin/perl
use DBI;use DBD::Oracle;
$dbh01 = DBI->connect("dbi:Oracle:PROD_DB", "user", "pwrd");
$dbh02 = DBI->connect("dbi:Oracle:DATA__WAREHOUSE", "user", "pwrd");
my $sth01 = $dbh->prepare(<execute();
while( my $row = $sth01->fetchrow_hashref) {
foreach my $col (keys %{$row} ) {
print "$col = $row->{$col}\n";
push(@cols,$col);
push(@rows,$row);
}
}

my $fields = join(', ', @cols);
my $values = join(', ', @rows);
$sql02 = qq{"INSERT INTO DATA__WAREHOUSE($cols) VALUES ($rows)"};
$sth02 = $dbh02->prepare($sql02) or die "Can't prepare SQL statement:
$DBI::errstr\n";
$sth02->execute();

Re: TeraNews: RE: Avoiding coding username/password directly into perl script

[Hardy Merrill]

I see in 'perldoc DBD::ODBC' that the user id and password _is_ required
when using DBD::ODBC to connect to an ODBC data source.  I looked up a
few of my ODBC 'System' data sources, and although they do contain the
user id, they do _NOT_  contain the password.  So I'll stick with my
previous recommendation of locating the database login parameters in
their own Perl module in a safe place, and then "use"ing that module to
give your scripts access to those login parameter variables.

Hardy

>>> "News Reader" <[EMAIL PROTECTED]> 03/10/04 02:06PM >>>
On Wed, 10 Mar 2004 12:50:59 -0600, "Brad Fike"
<[EMAIL PROTECTED]>
said:

Thanks for the ideas. I did try entering them in as blanks, but I
still
got this error:

DBI->connect(BWEB) failed: [Microsoft][ODBC SQL Server Driver][SQL
Server]Login failed for user '(null)'. Reason: Not associated with a
trusted SQL Server connection. (SQL-28000)(DBD: db_login/SQLConnect
err=-1) at test.pl line 18

I know the username/password is in the system DSN somewhere.

In reply to the suggestions about putting the username/password into
another [more secure] file, this is not a bad idea, and I can
certainly
do this. It would address the security issue and it would address the
maintenance issue to some extent (at least we wouldn't have to modify
several scripts), but it still means the info is in two places (this
new
file as well as the system DSN, which other non-perl things use).

> If the user\pass is already in the DSN you should be able to just...
> 
> my $db = 
> DBI->connect("dbi:ODBC:$dsn","","");
> 
> 
> >-Original Message-
> >From: Sterin, Ilya (I.) [mailto:[EMAIL PROTECTED] 
> >Sent: Wednesday, March 10, 2004 12:43 PM
> >To: [EMAIL PROTECTED] 
> >Subject: RE: Avoiding coding username/password directly into 
> >perl script
> >
> >
> >well, if your database requires a username/password, then you 
> >can't just pass it the path (DSN).  Not even sure how you 
> >expect anyone to do that.  I believe certain ODBC clients 
> >might have the capability to embed that either inside the DSN 
> >and/or somewhere else on the system, so that when a certain 
> >DSN is recognized, that username/password is used, but not 
> >sure what clients and/or version do that.
> >
> >Thanks.
> >
> >Ilya
> >
> >> -Original Message-
> >> From: News Reader [mailto:[EMAIL PROTECTED] 
> >> Sent: Wednesday, March 10, 2004 1:36 PM
> >> To: [EMAIL PROTECTED] 
> >> Subject: Avoiding coding username/password directly into perl
script
> >> 
> >> 
> >> Hi there,
> >> 
> >> I figured this would be a pretty common problem, but I didn't see
> >> anything in the FAQ or docs.
> >> 
> >> We've got a script that uses DBI to connect to SQL Server.
> >> 
> >> There is a system DSN set up and this already has the username
and
> >> password. So do we really need to pass them again, as shown
below?
> >> 
> >> my $db = 
> >DBI->connect("dbi:ODBC:$dsn","$db_user_name","$db_password");
> >> 
> >> We tried the following (removing the username/password
parameters),
> >> but we got a connection error.
> >> 
> >> my $db = DBI->connect("dbi:ODBC:$dsn");
> >> 
> >> It would be nice (for maintenance) and obviously more secure if
we
> >> could pass only the DSN name.
> >> 
> >> Thanks,
> >>  - ML

Re: Avoiding coding username/password directly into perl script

[Hardy Merrill]

You _do_ need to include the user name and password in the DBI connect
statement, but the way to make it more secure is to not hardcode those
in your scripts - create a Perl module in which you place variables that
_do_ contain the hardcoded values, but place that perl module in a
secure place on your filesystem that is not accessible by regular users.
 Then "use" that module in your script so that you have access to the
variables in the module that contain the actual values of the user name
and password.

At a command prompt do

perldoc perl

to get started.  Note that there are several perldoc's that refer to
perl modules - the one you probably want to start with is perlmod, which
you can view by doing

   perldoc perlmod

at a command prompt.

HTH.

Hardy Merrill

>>> "News Reader" <[EMAIL PROTECTED]> 03/10/04 01:35PM >>>
Hi there,

I figured this would be a pretty common problem, but I didn't see
anything in the FAQ or docs.

We've got a script that uses DBI to connect to SQL Server.

There is a system DSN set up and this already has the username and
password. So do we really need to pass them again, as shown below?

my $db = DBI->connect("dbi:ODBC:$dsn","$db_user_name","$db_password");

We tried the following (removing the username/password parameters),
but we got a connection error.

my $db = DBI->connect("dbi:ODBC:$dsn");

It would be nice (for maintenance) and obviously more secure if we
could pass only the DSN name.

Thanks,
 - ML

RE: Trying to make DBI work for me

[Hardy Merrill]

Notice that your version of Perl is 5.8.0 - on Jeff's ftp site he has
DBI and DBD versions for Perl 5.8.2 and 5.8.3 - _not_ for Perl 5.8.0. 
That _might_ be why you're getting the perl error.  If you're going to
go this route, you might want to consider first upgrading your perl from
5.8.0 to 5.8.2, and _then_ installing the DBI and DBD's from Jeff's ftp
site.

Hope this helps.

Hardy

>>> Laurie Vien <[EMAIL PROTECTED]> 03/08/04 04:05PM >>>
Thanks, Hardy.  I did "perl -V"; I have Perl V5.8.0 installed.  And
when you
say "Oracle client", do you mean DBD-Oracle.ppd?  If so, yes, I
installed it
with the following results (which is weird because it says it's
installed in
ActivePerl 5.6.1.633, but I have V5.8.0 ...):

C:\Perl>ppm install
ftp://ftp.esoftmatic.com/outgoing/DBI/5.8.2/DBD-Oracle.ppd 
Note: Package
'ftp://ftp.esoftmatic.com/outgoing/DBI/5.8.2/DBD-Oracle.ppd' 
is already installed.

Install 'DBD-Oracle' version 1.15 in ActivePerl 5.6.1.633.

Successfully installed DBD-Oracle version 1.15 in ActivePerl
5.6.1.633.

Any more ideas?

Laurie


-Original Message-
From: Hardy Merrill [mailto:[EMAIL PROTECTED] 
Sent: Monday, March 08, 2004 3:56 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] 
Subject: Re: Trying to make DBI work for me


Laurie, you can find your version of Perl by doing 'perl -V' at a
command prompt - it's at the top of the output.  Mine says 'Summary of
my perl5 (revision 5 version 8 subversion 2) configuration:'.

I don't know about the perl error, but as for the Oracle error, it
looks to me like Perl can't find DBD::Oracle.  Do you have the Oracle
Client installed?  Did you get any errors when installing Jeff's
DBD::Oracle binary? (I'm not sure if you _would_ get an error even if
there was a problem).  Search down your perl tree and see if you find
an
'Oracle.dll' anywhere inside a 'DBD' folder.  Sorry I can't be more
help
- pretty new to Windows myself.

Hardy

>>> Laurie Vien <[EMAIL PROTECTED]> 03/08/04 03:15PM >>>
I followed Hardy's example below by uninstalling/reinstalling the same
3 DBI
components (first I upgraded from Perl 5.6 to Perl 5.8.0).  Then I ran
Hardy's little test script; but I got the following error in a popup
window:
"The procedure entry point Perl_Ilockhook_ptr could not be located in
the
dynamic link library perl58.dll."

When I dismissed that error popup, I got these additional messages:
"install_driver(Oracle) failed: Can't load
'C:/Perl/site/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 229.
at (eval 1) line 3
Compilation failed in require at (eval 1) line 3.
Perhaps a required shared library or dll isn't installed where
expected
at test_dbi.pl line 5

What should I do to resolve this?  (Also, how do I find out what
version of
Perl I actually have installed?)

Thanks very much--

Laurie


-Original Message-
From: Hardy Merrill [mailto:[EMAIL PROTECTED] 
Sent: Friday, March 05, 2004 9:12 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED] 
Subject: RE: Accessing oracle from windows 2000


Just wanted to let people know what worked for me for connecting to an
Oracle database on a Windows XP platform, using both DBD::ODBC *AND*
DBD::Oracle.  Thanks to Jeff Urlwin for all the help - see Jeff's
suggestions below for reference.  NOTE that my version of ActiveState
Perl is 5.8.2.

  1. Using 'ppm uninstall DBD::ODBC' I uninstalled DBD::ODBC.
  2. Using 'ppm uninstall DBD::Oracle' I uninstalled DBD::Oracle.
  3. Using 'ppm uninstall DBI' I uninstalled DBI.
  4. Did 'ppm install
ftp://ftp.esoftmatic.com/outgoing/DBI/5.8.2/DBI.ppd '
  to install the DBI binary for AS Perl 5.8.2 from Jeff's
repository
  5. Did 'ppm install
ftp://ftp.esoftmatic.com/outgoing/DBI/5.8.2/DBD-ODBC.ppd '
  to install the DBD::ODBC binary for AS Perl 5.8.2 from
Jeff's
  repository.
  6. Did 'ppm install
ftp://ftp.esoftmatic.com/outgoing/DBI/5.8.2/DBD-Oracle.ppd '
   to install the DBD::Oracle binary for AS Perl 5.8.2 from
Jeff's
   repository.

Then for the DBD::Oracle test, I created this small test script:

#!C:\Perl\bin
use strict;
use DBI;

my $dbh = DBI->connect('dbi:Oracle:host=MY_HOST;sid=MY_SID',
  'my_user',
  'my_password',
  { RaiseError => 1, PrintError => 1 }
) || die "Can't connect: $dbi::errstr";

print "Successful Connect: \$dbh=[$dbh]\n";



And it worked.

Hardy Merrill

>>> "Jeff Urlwin"