DBI Question 
Author Message
 DBI Question

I'm a little confused on how to accomplish something.  Perhaps some kind
soul will be gracious enough to point me to the way of enlightenment.

I know how to use the DBI.  I use it often to query an Oracle database,
extract the data and present it on a browser window.

I know how to write PL/SQL and stored procedures.  I've used them under
Oracle's Web Application Server.  I wrote some procedures which would query

the database, fetch out the data, format it, and present it in a browser
window.

Now I find myself in a situation where I want to use the speed of a stored
procedure but the flexibility (and string manipulation capabilities) of
perl.  So, how do I connect the two together?  I understand the "how" of
calling the stored procedure using DBI.  What I'm confused about is how to
represent the data.  Stated another way, how do I loop through the data and

extract the individual rows?

In the stored procedure, I can simply use one of may flavors of loops to
loop through the fetched rows of the cursor query results.  But that loop
is on the inside of the procedure.  I need to be able to loop through these

rows on the outside of the procedure.

How do I write my procedure to do this?  What does the PL/SQL procedure /
function return to represent multiple rows and how will perl represent
this?  (list of hashes maybe?)  I suppose I could stuff everything in a
table of records.

TYPE t_myrecord is RECORD (
   var1   VARCHAR2(50),
   var2   VARCHAR2(50)
);

TYPE t_myTabRec IS TABLE of t_myrecord
   index by binary_integer;

Now suppose that I populate the table with data and return it.

v_tabvar   t_myTabRec;

v_tabvar(0).var1 := 'rec1 val1';
v_tabvar(0).var2 := 'rec1 val2';
v_tabvar(1).var1 := 'rec2 val1';
v_tabvar(1).var2 := 'rec2 val2';

Question: what will perl do if I return v_tabvar?

Basically I'm confused as to how to get multiple records out of a procedure

and then manipulate them in perl.  Some guidance would be appreciated.

Thanks

-=} Randall {=-   Guidance: See under Advice. Advice: See under Guidance.
=====================================================
Shameless Plug:  Go visit my latest site: http://www.*-*-*.com/



Fri, 14 Feb 2003 03:00:00 GMT  
 DBI Question

Quote:
>Now I find myself in a situation where I want to use the speed of a stored
>procedure but the flexibility (and string manipulation capabilities) of
>perl.  So, how do I connect the two together?  I understand the "how" of
>calling the stored procedure using DBI.  What I'm confused about is how to
>represent the data.  Stated another way, how do I loop through the data and
>extract the individual rows?

a stored procedure - just like a vanilla select query - will return a
record set .. you use the same mechanism as you do with a select query
to iterate over the returned rows from the store procedure

Quote:
>In the stored procedure, I can simply use one of may flavors of loops to
>loop through the fetched rows of the cursor query results.  But that loop
>is on the inside of the procedure.  I need to be able to loop through these
>rows on the outside of the procedure.

the last statement in your stored procedure will be some sort of select
statement

test first in SQL directly interfacing with the database .. then once
you've got the stored procedure outputting the record set that you want
- get back into Perl and call it from there

  [ removed alt.perl and comp.lang.perl.modules ]

--



Fri, 28 Feb 2003 11:26:16 GMT  
 DBI Question

Quote:


>>Now I find myself in a situation where I want to use the speed of a stored
>>procedure but the flexibility (and string manipulation capabilities) of
>>perl.  So, how do I connect the two together?  I understand the "how" of
>>calling the stored procedure using DBI.  What I'm confused about is how to
>>represent the data.  Stated another way, how do I loop through the data and
>>extract the individual rows?

>a stored procedure - just like a vanilla select query - will return a
>record set .. you use the same mechanism as you do with a select query
>to iterate over the returned rows from the store procedure

Well, that depends quite strongly on the specifics of the DBD
driver and the database being used. For example, the DBD::DB2
driver is not currently able to return a multiple rowset as
a return.

[This is actually one of my current projects -- teach myself
enough XS to hack the driver so that I can make it do what
DBD::Oracle does, and have an attribute I can set in order
to make it return a cursor to the rowset as the outbound
in-out parameter].

Kitty
--

"Bad times can make you fall in love with a penguin" -- Laika



Mon, 03 Mar 2003 03:00:00 GMT  
 DBI Question

Quote:

> "Bad times can make you fall in love with a penguin" -- Laika

s/times/OSs/;

--
Jeff



Sun, 09 Mar 2003 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Not enough Memory BDE Error

2. property TQuery.RecNo always return -1

3. Webspaceprovider, der Delphi und die BDE unterst├╝tzt

4. Masks, DBEdit, dates and NULL values

5. Uploading Pascal programs

6. INSERT after ROLLBACK ??? Very important! Help please!

7. Re : turbo pascal requirements (2)

8. BDE and useless fetch

9. Interbase language problem

10. CTRL-BRK

11. Can anyone Help me with an Exhaustive Search Algorithm?

12. Geting coordinates of ICON

 

 
Powered by phpBB® Forum Software