Re: Report Writers, Stored Procedures, etc

A week or two ago the following article appeared from Adam Tiller in Australia:- > In article <1992Oct17.022140.24880@NeoSoft.com> freyder@NeoSoft.com (Rob Freyder) writes: > >Article 1614 (93 more) in comp.databases.informix: > >Organization: Western Atlas Int'l - Houston TX > > >We are looking for report writers for Informix Online V5.0. > > We are using IQ (version 3) on Informix Online 5.0. > > We haven't found any problems. > IQ can handle Informix views. > We have put stored procedures under the views, > and this way we can get IQ to take full advantage of Stored Procedures too. > > > Adam > -- > ,-_|\\ Adam Tiller Phone: +61 3 286 3603 (was 280 2322) > / \\ Co-Cam Computer Group Fax: +61 3 416 2985 > \\_,-._/ Abbotsford, Melbourne Work: adamt@auzodt3.mel.cocam.oz.au > v AUSTRALIA Play: adamt@peg.apc.org This mail got me quite excited for a while and I wrote and asked for how he managed to get Stored Procedures to be called by Informix views. Unfortunately I never received a reply. Having investigated I am sure that he is referring to some other capability but the suggestion got me thinking. One of the things that we are currently developing at DHL is a Data Access Module that will sit between our applications and the database engine. This module will handle all updates and queries along with all integrity checking (Eg. Domain, Referential, Complex Business, etc). It will also handle any tightly bound processing that may need doing on the database for a business transaction. We are implementing this through a combination of C, Stored Procedures and Views. (The views are used for external report writers) My idea was that if Stored Procedures could be used as part of the Create view statement in place of the select statement and the StoredProcedures had some way of knowing whether an Insert, Delete, Update or Select function had been used. Using this method much of our Data Access Module could be replaced by these views which could be used as if the underlying database looked like the view but in reality the Stored Procedure was handling the work on as many tables as is necessary for the business transaction. This would have a number of benefits. 1) It avoids the restrictions on updating through views that currently exist. 2) It would allow the underlying database structure to be radically changed but leave applications the same. This is the purpose of writing the Data Access Module in the first place but this would greatly reduce coding changes above the Stored Procedure level to achieve the same thing. 3) It would allow the introduction of views that could much more closely mirror particular reporting, application or business requirements placing the processing burden on the server end and reducing a lot of client programming and processing. The above suggestion is thrown out for discussion and comment before possibly being put forward as a feature request. Comments from anybody, including Informix, would be appreciated. Note SPL would probably need to be extended to cope with dynamically allocated arrays and would definitely require access to the SQLCA structure (Something that is planned for 6.0?). Cheers - Jim -------------------------------------------------------------------- Name: Jim Gordon Internet: jgordon@ssf-sys.DHL.COM Company: DHL Systems Inc Phone: (415) 358-5911 (Work) Address: 1700 S. Amphlett Blvd. (415) 882-9728 (Home) San Mateo, CA 94402 Fax: (415) 571-6429 --------------------------------------------------------------------