Hi,
Thank you very much for your reply.
The existed system , a network management tool , is developed using C as
program lanuage and oracle 7.3 as the database. There are 3 layers in the database
like table layer, view layer and DBview layer. The DBView layer communicates through
API's with both table and view layers. Views and tables are created dynamically
through API's. Here Views are created for each table (reason i too donot know). And
used some stored procedures at module level and some triggers on some table
before/after updating.
The above existed system's whole database layer including table layer/views layer and
DBView layer to be migrated to mysql 4.0 with out innodb. Need to change the API's
also. So, the views/stored procedurs (some of them used cursors)/ triggers need to be
converted to mysql 4.0 from oracle 7.3. That is the requrement.
Views - instead of running queries against views, you will have to run your queries
against the underlying tables. This may mean lots of changes to your SQL statements.
What once appeared as a single query may now have to be performed in multiple steps to
achieve the same results. Usually, this kind of redesign actually improves
application performance as you no longer rely on a set of table abstractions which
require additional overhead to maintain.
--- If i want to use the same data by querying underlying tables again how to use
that?. where to store that data for using again?. Shall i need to write the same query
again when i need the same data?. In the existed system views are created only once at
runtime. could you please explain me in detail.
Cursors - Any SQL-scripted processing you did using cursors will have to be replaced
with C/C++ routines that perform the same functions. You navigate recordsets according
to the methods exposed by whichever library you use to interact with the server.
Consult the documentation for the library you will use for specific details.
--- Supggest me the best API's to perform SQL- scripted performance?. Could you please
mention what are all the libraries we needed for the general cursor processings.
Given the oracle code which is used the cursors, could you please mention the
equivalent libraries for those
CREATE procedure pstub(pname varchar2, uname varchar2,
stubSpec in out varchar2, stubText in out varchar2,
flags varchar2 := '6') is
rc varchar2(40);
ty varchar2(5);
cursor tub (una varchar2, dbna varchar2, luna varchar2, luty varchar2) is
select line from sys.pstubtbl
where (una is null or username = una) and
(dbna is null or dbname = dbna) and
lun = luna and lutype = luty
order by lineno;
begin -- main
sys.pstubt(pname, uname, '', flags, rc);
if rc like '$$$%' then stubText := rc; return; end if;
if not (rc = 'PKG' or rc = 'SUB')
then stubText := '$$$ other'; return;
end if;
stubSpec := '';
stubText := '';
if rc = 'PKG' then
for s in tub(uname, '', pname, 'PKS') loop
stubSpec := stubSpec || s.line;
end loop;
end if;
if rc = 'PKG' then ty := 'PKB'; else ty := 'SUB'; end if;
for s in tub(uname, '', pname, ty) loop
stubText := stubText || s.line;
end loop;
end;
Could you please let me know the alternatives for stored procedures and Triggers in
mysql 4.0.
thanks,
Narasimha
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Fri 10/1/2004 1:16 AM
To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS)
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: views/cursors in mysql 4.0
Views - instead of running queries against views, you will have to run your
queries against the underlying tables. This may mean lots of changes to your SQL
statements. What once appeared as a single query may now have to be performed in
multiple steps to achieve the same results. Usually, this kind of redesign actually
improves application performance as you no longer rely on a set of table abstractions
which require additional overhead to maintain.
Cursors - Any SQL-scripted processing you did using cursors will have to be
replaced with C/C++ routines that perform the same functions. You navigate recordsets
according to the methods exposed by whichever library you use to interact with the
server. Consult the documentation for the library you will use for specific details.
Sorry I couldn't be more exact in my descriptions but you were not very clear
on your requirements. Please respond with more detailed descriptions of exactly what
you need from the database and I am sure someone on the list can help.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
<[EMAIL PROTECTED]> wrote on 09/30/2004 03:24:52 PM:
> Hi,
> Thank you for your response. Strictly we have to use mysql 4.
> 0 only. Actually the source data base is oracle 7.3, we need to
> migrate that database to mysql 4.0 . In oracle 7.3 views/cursors are
> used. That is why i need an alternative for views/cursors in mysql
> 4.0. We use C/C++ for Programming. could you please explain me the
> alternatives for views/cursors in mysql 4.0 and how to implement
> those in mysql 4.0?.
>
> Thanks,
> Narasimha
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Fri 10/1/2004 12:40 AM
> To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS)
> Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Subject: Re: views/cursors in mysql 4.0
>
> Views and Cursors are not available in MySQL 4.x. Usually your
> scripting language handles cursors for you (having them in 4.x would
> do you little good as stored procedures don't exist yet. Look at the
> current 5.x development for cursors and stored procedures). What
> language do you script/program with?
>
> What would you like to have done with your views and/or cursors if
> they _were_ available? Workarounds exist for almost every purpose
> but some of them use language-specific or library-specific options.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
> <[EMAIL PROTECTED]> wrote on 09/30/2004 03:01:51 PM:
>
> >
> > Hi,
> > How to implement views/cursors in mysql 4.0?. Normally in
> > mysql how the selected data is maintained in the recordsets?. Is
> > there any other alternatives to implement views/cursors in mysql 4.
> > 0?. Please advise me for the better solution.
> >
> > Thanks,
> > Narasimha
> >
> >
> >
> > 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.
>
>
>
> 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.
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.
