RE: Question with DBI versus PERL

From: Ron Reidy (Ron.Reidy_at_arraybiopharma.com)
Date: 05/17/04 

Date: Mon, 17 May 2004 08:11:46 -0600
To: "Mathieu Barbeau" <mathieu_barbeau@hotmail.com>, <dbi-users@perl.org>

Matieu,

See below:

----------------
Ron Reidy
Senior DBA
Array BioPharma, Inc.

-----Original Message-----
From: Mathieu Barbeau [mailto:mathieu_barbeau@hotmail.com]
Sent: Monday, May 17, 2004 7:00 AM
To: Reidy, Ron; dbi-users@perl.org
Subject: RE: Question with DBI versus PERL

Ok,

but, my SQL file contains procedure with variables. If I execute one
statement at once (if I parse my SQL file) my pl/sql will work?

I'm not sure I understand you here.

When you say:
"1. All spool statements will turn to Perl print() calls.
2. Any PL/SQL dbms_output calls will need to be handled with the
DBD::Oracle calls to enable dbms_output."

What I understands:

1. I need to intecept my spool statement and call print()?

'spool' is a SQL*Plus command - not a SQL command. DBI only runs SQL commands. As you know, the spool command captures output to the file given as an argument to 'spool' in SQL*Plus. Because you are not running SQL*Plus, this mechanism is not available to you. To get the output, you will need to use the Perl built-in print() function.

2. I can't use DBI interface? I need to use directly the DBD::Oracle?

DBI is a general interface to databases. You still need a driver (an implementation layer) to perform your work. In your case, you will either need DBD::Oracle or DBD::ODBC (I recommend DBD::Oracle, but this is just my personal preference). The driver is specified in the DBI connect() function.

Thanks a lot!

P.S. My script run with dbi but for one sql statement at once

Are you saying you want to run all statements at once? You will need to do this within a PL/SQL block. Output will need to be managed using DBMS_OUTPUT (be aware of buffer limits in DBMS_OUTPUT).

From: "Reidy, Ron" <Ron.Reidy@arraybiopharma.com>
To: "Mathieu Barbeau" <mathieu_barbeau@hotmail.com>, <dbi-users@perl.org>
Subject: RE: Question with DBI versus PERL
Date: Fri, 14 May 2004 15:19:08 -0600
MIME-Version: 1.0
Received: from onion.perl.org ([63.251.223.166]) by mc7-f10.hotmail.com with
Microsoft SMTPSVC(5.0.2195.6824); Fri, 14 May 2004 14:20:03 -0700
Received: (qmail 6131 invoked by uid 1005); 14 May 2004 21:19:42 -0000
Received: (qmail 6116 invoked from network); 14 May 2004 21:19:42 -0000
Received: from x1.develooper.com (63.251.223.170) by onion.develooper.com
with SMTP; 14 May 2004 21:19:42 -0000
Received: (qmail 3314 invoked by uid 225); 14 May 2004 21:19:42 -0000
Received: (qmail 3306 invoked by alias); 14 May 2004 21:19:41 -0000
Received: from smtp1.arraybiopharma.com (HELO smtp1.arraybiopharma.com)
(65.114.206.101) by la.mx.develooper.com (qpsmtpd/0.27.1) with ESMTP; Fri,
14 May 2004 14:19:41 -0700
Received: from ims01bldr.arraybp.com by smtp1.arraybiopharma.com id
i4ELIsJQ012696; Fri, 14 May 2004 15:18:55 -0600
Received: from fiji.arraybp.com ([10.10.10.215]) by ims01bldr.arraybp.com
with Microsoft SMTPSVC(5.0.2195.6713); Fri, 14 May 2004 15:18:56 -0600
X-Message-Info: JGTYoYF78jHEESQ34gOiw0saBXPgxldm
Mailing-List: contact dbi-users-help@perl.org; run by ezmlm
Precedence: bulk
List-Post: <mailto:dbi-users@perl.org>
List-Help: <mailto:dbi-users-help@perl.org>
List-Unsubscribe: <mailto:dbi-users-unsubscribe@perl.org>
List-Subscribe: <mailto:dbi-users-subscribe@perl.org>
Delivered-To: mailing list dbi-users@perl.org
Delivered-To: dbi-users@perl.org
X-Spam-Status: No, hits=0.0 required=7.0tests=
X-Spam-Check-By: la.mx.develooper.com
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
Content-Class: urn:content-classes:message
Message-ID: <17CAB0BF27BCFC47B0E4554A0E2F962B2B3B3F@fiji.arraybp.com>
X-MS-Has-Attach:
X-MS-TNEF-Correlator:
Thread-Topic: Question with DBI versus PERL
thread-index: AcQ57y3We6zZtYoiTTqCuDV5UAeudwAABJ8w
X-OriginalArrivalTime: 14 May 2004 21:18:56.0814 (UTC)
FILETIME=[110114E0:01C439F9]
X-Spam-Rating: onion.develooper.com 1.6.2 0/1000/N
Return-Path: dbi-users-return-22919-mathieu_barbeau=hotmail.com@perl.org

Mathieu,

Sure, you can run the SQL statements in your file with DBI, but you will
need to parse the file to get all the SQL statements and then run them
through the DBI interface. I would suggest that if you need to use
Perl/DBI, that you instead look at a different method than running the SQL
statements from the file, parsing the individual SQL statements, and instead
look at possibly using the Class::Phrasebook and Class::Phrasebook::SQL
modules from CPAN.

The number of SQL statements is not significant with Perl/DBI.

The following will be your output issues:

1. All spool statements will turn to Perl print() calls.
2. Any PL/SQL dbms_output calls will need to be handled with the
DBD::Oracle calls to enable dbms_output.

You will need to:

1. Read the Perl docs to get familiar with the syntax of the language.
2. Read and understand the DBI docs (perldoc DBI).
3. Read and understand the DBD::Oracle docs (perldoc DBD::Oracle).

Hopefully, I have not clouded the issue for you.

-----------------
Ron Reidy
Senior DBA
Array BioPharma, Inc.

-----Original Message-----
From: Mathieu Barbeau [mailto:mathieu_barbeau@hotmail.com]
Sent: Friday, May 14, 2004 2:08 PM
To: dbi-users@perl.org
Subject: Question with DBI versus PERL

Hi,

I have a really big sql scripts with many select, procedures and spool spool
off statements. I want to know how can i run this sql script with DBI.

With sqlplus, this sql script run correctly. But I don't know how can i run
this script with perl.

Can you help me?

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.

Relevant Pages

  • Re: Perl 6 DBI API ideas
    ... Though I didn't raise my hand when you asked for people to contribute to a DBI module for Perl 6, I've had some ideas that I thought about sharing. ... I would also love to see some standardization on the driver names ("mysql" when it's normally written ... DBI is supposed to be query language agnostic, even if SQL is the most commonly used group of languages, and if a user can declare this explicitly, it saves the driver from having to guess what they were given, which might be ambiguous. ...
    (perl.dbi.users)
  • RE: Question with DBI versus PERL
    ... statement at once (if I parse my SQL file) my pl/sql will work? ... All spool statements will turn to Perl print() calls. ... I can't use DBI interface? ... I want to know how can i run this sql script with DBI. ...
    (perl.dbi.users)
  • Re: DBI and fetchhasref
    ... >> I'm using Active State Perl 5.8.4 with DBD-Oracle and Oracle 9i ... > Post the aforementioned short-but-complete script. ... when I copy the sql statement to sql+ i see all expected ... Its not the first time that I use DBI, but the first time I use a sub ...
    (comp.lang.perl.misc)
  • RE: how to invoke .sql file from dbi
    ... recommended changing all the SQL*Plus scripts into DBI calls. ... The sql statements are SELECT statements and we are expecting plain (no ... to just point the dbi to a sql file and dbi taking care of it. ...
    (perl.dbi.users)
  • DBI not generating expected error messages
    ... I copied some sample code from the Perl DBI book in the error ... when I deliberately make a typo (ie. bad password, malformed SQL, etc) to ... #deliberately mistyped password 'tiger' to try to generate error ... ...
    (comp.lang.perl.misc)