Running a query manually when using web modules

[none]

Hi. I'm implementing a web deployment using 9i. I have a table for STOCK,
which has one or more PURCHASES, which have one or more PURCHASE_ITEMS.

When a purchase item is INSERTED, I'd like the web module component to
update the relevant grandparent record of STOCK, and when UPDATED, to
remember the previous level and adjust accordingly.

I'm sure that this can probably be done in PL/SQL, but don't have a clue
how - all books and tutorials I have found take a 'PHP-style' approach or
use servlets and don't seem to use web modules.

I am mostly familiar with Design Editor. Can anyone help? Thanks.

[Dave]

Re: Running a query manually when using web modules

"none" <none@none.co m> wrote in message news:<f417bd5e2 4720a912e0e0d78 d3ffc487@news.t eranews.com>...[color=blue]
> Hi. I'm implementing a web deployment using 9i. I have a table for STOCK,
> which has one or more PURCHASES, which have one or more PURCHASE_ITEMS.
>
> When a purchase item is INSERTED, I'd like the web module component to
> update the relevant grandparent record of STOCK, and when UPDATED, to
> remember the previous level and adjust accordingly.
>
> I'm sure that this can probably be done in PL/SQL, but don't have a clue
> how - all books and tutorials I have found take a 'PHP-style' approach or
> use servlets and don't seem to use web modules.
>
> I am mostly familiar with Design Editor. Can anyone help? Thanks.[/color]

What do you mean by web modules, is this a tool or product?

You can certainly create PL/SQL code in your database to execute your
logic. And I assume you can call a PL/SQL procedure from PHP. I know
you can do it from a servlet using a JDBC connection. So I'm not sure
what your issue is, unless you do not understand PL/SQL. Perhaps you
could provide more detail.

Dave

[none]

Re: Running a query manually when using web modules

We are using Design Editor in 9i to create modules. We then generate the
modules as 'web modules'. Editor generates all the server code to carry out
simple transactions like insert, update etc.

Someone else's page on web modules is

http://www.iherve.com/oracle/wg_title.htm

What I think we need is a way to get a web module to take fields in the
insert/update of a purchased item (which would be something like '50 watches
of make W123'), run a query to get current stock for W123, and then update
the current stock by adding purchased stock to current stock. We're not
using PHP or servlets, but the Oracle web server. The entire thing is
created from within Oracle itself. I do not understand PL/SQL, although I
have had a look at some tutorials they do not mention anything about
integrating a transaction into a module created by Oracle.

"Dave" <davidr212000@y ahoo.com> wrote in message
news:5e092a4e.0 404140539.77fe1 315@posting.goo gle.com...[color=blue]
> "none" <none@none.co m> wrote in message[/color]
news:<f417bd5e2 4720a912e0e0d78 d3ffc487@news.t eranews.com>...[color=blue][color=green]
> > Hi. I'm implementing a web deployment using 9i. I have a table for[/color][/color]
STOCK,[color=blue][color=green]
> > which has one or more PURCHASES, which have one or more PURCHASE_ITEMS.
> >
> > When a purchase item is INSERTED, I'd like the web module component to
> > update the relevant grandparent record of STOCK, and when UPDATED, to
> > remember the previous level and adjust accordingly.
> >
> > I'm sure that this can probably be done in PL/SQL, but don't have a clue
> > how - all books and tutorials I have found take a 'PHP-style' approach[/color][/color]
or[color=blue][color=green]
> > use servlets and don't seem to use web modules.
> >
> > I am mostly familiar with Design Editor. Can anyone help? Thanks.[/color]
>
> What do you mean by web modules, is this a tool or product?
>
> You can certainly create PL/SQL code in your database to execute your
> logic. And I assume you can call a PL/SQL procedure from PHP. I know
> you can do it from a servlet using a JDBC connection. So I'm not sure
> what your issue is, unless you do not understand PL/SQL. Perhaps you
> could provide more detail.
>
> Dave[/color]

[Dave]

Re: Running a query manually when using web modules

"none" <none@none.co m> wrote in message news:<baae4c0c9 943ed45446b6351 4b8c13f5@news.t eranews.com>...

Ah ok. I learned something today. Never heard of design editor before.
:)

I don't know what you table names and columns are, so I made some
assumptions...

Looks to me like you have two options.

1.) PL/SQL procedure, which you will have to figure out how to call
from your web module...which would look something like this....

create or replace procedure UpdateStock(p_p roduct_number number, p_qty
number)
is
begin
UPDATE STOCK SET STOCK_LEVEL = STOCK_LEVEL + p_qty
WHERE PRODUCT_NUMBER = p_product_numbe r;
commit;
end;
/

2.) Create a trigger on the purchase items table which will be
completely transparent to your application code. Whenever you insert
into the table, it will update the stock levels within the same
Transaction context. This may be easier for you to implement, but it
may depend if you as a developer are allowed to touch the database
schema. The :NEW is special syntax in the trigger representing the new
column values being inserted into the PURCHASE_ITEM table.

create trigger UpdateStock
AFTER INSERT
on PURCHASE_ITEM
for each row
begin
UPDATE STOCK SET STOCK_LEVEL = STOCK_LEVEL + :NEW.PURCHASE_Q TY
WHERE PRODUCT_NUMBER = :NEW.PURCHASED_ PRODUCT_NUMBER;
end;
/

Also, for a great new Oracle web development tool/environment. Check
out Oracle HTML DB. It is very cool. But knowing PL/SQL is probably
important for creating very custom solutions.

Dave
[color=blue]
> We are using Design Editor in 9i to create modules. We then generate the
> modules as 'web modules'. Editor generates all the server code to carry out
> simple transactions like insert, update etc.
>
> Someone else's page on web modules is
> http://www.iherve.com/oracle/wg_title.htm
>
> What I think we need is a way to get a web module to take fields in the
> insert/update of a purchased item (which would be something like '50 watches
> of make W123'), run a query to get current stock for W123, and then update
> the current stock by adding purchased stock to current stock. We're not
> using PHP or servlets, but the Oracle web server. The entire thing is
> created from within Oracle itself. I do not understand PL/SQL, although I
> have had a look at some tutorials they do not mention anything about
> integrating a transaction into a module created by Oracle.
>
> "Dave" <davidr212000@y ahoo.com> wrote in message
> news:5e092a4e.0 404140539.77fe1 315@posting.goo gle.com...[color=green]
> > "none" <none@none.co m> wrote in message[/color]
> news:<f417bd5e2 4720a912e0e0d78 d3ffc487@news.t eranews.com>...[color=green][color=darkred]
> > > Hi. I'm implementing a web deployment using 9i. I have a table for[/color][/color]
> STOCK,[color=green][color=darkred]
> > > which has one or more PURCHASES, which have one or more PURCHASE_ITEMS.
> > >
> > > When a purchase item is INSERTED, I'd like the web module component to
> > > update the relevant grandparent record of STOCK, and when UPDATED, to
> > > remember the previous level and adjust accordingly.
> > >
> > > I'm sure that this can probably be done in PL/SQL, but don't have a clue
> > > how - all books and tutorials I have found take a 'PHP-style' approach[/color][/color]
> or[color=green][color=darkred]
> > > use servlets and don't seem to use web modules.
> > >
> > > I am mostly familiar with Design Editor. Can anyone help? Thanks.[/color]
> >
> > What do you mean by web modules, is this a tool or product?
> >
> > You can certainly create PL/SQL code in your database to execute your
> > logic. And I assume you can call a PL/SQL procedure from PHP. I know
> > you can do it from a servlet using a JDBC connection. So I'm not sure
> > what your issue is, unless you do not understand PL/SQL. Perhaps you
> > could provide more detail.
> >
> > Dave[/color][/color]