My comments embedded below <[EMAIL PROTECTED]> wrote on 09/30/2004 04:18:02 PM:
> > 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. triggers and stored procedures won't exist until MySQL 5.X. All of the administration that you automated through triggers will now have to be coded into your application. Any action that you had coded in a stored procedure will now have to be recoded into your application. > > 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?. Views are not stored bits of information. They are queries that you query against. If you need to store information, use either a table or a temporary table. > 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. Where you once were able to query the results of a query (the contents of a view), you will have to rewrite your SQL statements so that they get their data directly from the tables the views would have abstracted for you. You lose one layer of abstraction by not having views available. > > 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 According to this page: http://dev.mysql.com/downloads/ You have several options of APIs. Use which one fits your development environment. > > 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; This stored procedure would need to be recoded. Possibly as a class's method or as a stand alone function. No matter where in your code it resides, you will have to manually manage the records and values once automated for you by the cursor. You would run a query to get 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; Then step through those results one record at a time until you exhaust your results. During each step through your results you would need to duplicate the processing that occurred during each step of the cursor through its recordset. > > > Could you please let me know the alternatives for stored procedures > and Triggers in mysql 4.0. > As I mentioned above, YOU and your application code are now responsible for MANUALLY performing all of the actions once automated by your triggers. That may mean that you have to pre-verify certain steps or include certain updates with every query in order to maintain your data integrity. You are now totally in control of what happens to the data in your database. > > thanks, > Narasimha > <major snippage>
