O P E N R O A D F R E Q U E N T L Y A S K E D Q U E S T I O N S ( F A Q ) This version: draft Sep97 Last version: N/A Copyright (C) 1997 by Pat McGibbon This document is COPYRIGHTED to control its distribution and to prevent it from being used for profit. PLEASE DO NOT DISTRIBUTE IT WITHOUT THIS DISCLAIMER OR THE COPYRIGHT NOTICE. Replication of this document by any means is explicitly encouraged, provided that the content is not altered in any way. ------------------------ D I S C L A I M E R -------------------------- This document is not an official CA product information release. They know nothing of its contents. Well, I hope they know something about the subject, but you know what I mean. It is a voluntary effort by many contributors, co-ordinated by me. Their respective employers are in no way responsible for the content of this FAQ. Nothing in this FAQ should be regarded as true. Any item of information may become obsolete or out of date at any time. Information about undocumented features should always be regarded as potentially harmful. The co-ordinator, contributors, and distributors of this document make no representations as to the correctness of the information contained herein, and make no promises to correct any errors or omissions. You use this information at your own peril. Pat McGibbon pat@megadata.demon.co.uk MegaData Ltd., Dairy Cottage, High Street, MeonStoke Tel: 0(044)1489 877887 Southampton SO32 3NH UK ----------------------------------------------------------------------- If you don't see the '---- END OF FAQ ----' line, something is missing. With apologies to Roy Hann, whose Ingres FAQ header I shamelessly stole and made my own. ----------------------------------------------------------------------- Contents. --------- The contents section is just the set of questions/titles in the order they appear in the FAQ. At this point they are not numbered, so that if I reorganise stuff, it won't be too much hassle. Introduction. ------------- What is OpenROAD? ----------------- History. -------- who makes it? ------------- how do I get it? ---------------- Current Release. ---------------- what platforms? --------------- what system requirements? ------------------------- what databases can I talk to? ----------------------------- printing -------- top tips -------- Too many UserEvents can be a bad thing. --------------------------------------- unnamed buttons and find method on parentfield.childfields ---------------------------------------------------------- clientdata, clienttext ---------------------- tablefield access ----------------- use of choicelists as fast lookups ---------------------------------- qo painters ----------- field templates --------------- password field template ----------------------- dropdownedit field template --------------------------- reusing global lists -------------------- avoid piggybacking events ------------------------- #-- for comments that the compiler won't see -------------------------------------------- parent attribute for procedures as well as frames ------------------------------------------------- books/periodicals ----------------- how do I deal with strings > 2000? ---------------------------------- how do I handle images? ----------------------- Patches: their numbers, contents, where to get ---------------------------------------------- Upcoming Releases & their features ---------------------------------- Access to non-Ingres DB's: how to & experiences ----------------------------------------------- Connecting to more than one type of database from the same application. ----------------------------------------------------------------------- on-line resources ---------------- training -------- consultancy ----------- 3rd party products ------------------ When it's better to use the Object-Oriented extension of the language instead of writing standard (no O-O) software ? ------------------------------------------------------------ Net Trawlings. -------------- How do I assign a script to a dynamically created object? --------------------------------------------------------- Compatibility between platforms ------------------------------- E_LQ004B Repeated query 'mdbcache1' is not defined. --------------------------------------------------- Use of SQLSelect ---------------- InstallShield for OpenROAD. --------------------------- Fonts in OpenROAD ----------------- Code generator for OpenROAD --------------------------- Accessing SQL Server on NT from SunOS OpenROAD app -------------------------------------------------- 32k limit on scripts -------------------- Background colour for tablefields. ---------------------------------- iigcc exits immediately ----------------------- Installing OpenIngres 1.2 and OpenROAD 3.5 in Win NT 3.51 --------------------------------------------------------- Menu Fonts ---------- Accessing Oracle and Ingres from the same OpenROAD app? ------------------------------------------------------- How do I display Swedish characters correctly? ---------------------------------------------- How do I port an OR app from Ingres to Informix? ------------------------------------------------ Is it possible to use a network directory and place all of your .IMG files? --------------------------------------------------------------------------- How do I get cut/copy/paste for entry fields? --------------------------------------------- Can I use gateways to talk to Ingres and Oracle from the same app? ------------------------------------------------------------------ ---------------------------End of Contents------------------------------------------------------ Introduction. ------------- This FAQ will attempt to answer some of the most common questions concerning CA-OpenROAD. It is aimed mostly at those fairly new to the product but there may even be some snippets to interest the old sea dogs out there. It is not a general Ingres FAQ - you will find one of these on the NAIUA pages. I will refer to CA-OpenROAD mostly as OpenROAD or just OR; this is not because I have anything against Computer Associates - it just saves typing and reads a bit easier. If the 'style' of the document seems to vary wildly at times, this is probably because I freely trawled the dejanews archives for old comp.databases.ingres articles that were relevant to OpenROAD. Since some of the pieces are lifted directly from the newsgroup, 'I' may not always refer to me, if you see what I mean. Where information is incomplete or just plain wrong, I would welcome corrections via email. (As of this edition, the system requirements/platforms sections are woefully short; I tried various people at CA for info, but never got a reply.) Or if you have any questions/answers to add to the FAQ, they would also be gladly received. What is OpenROAD? ----------------- CA-OpenROAD: CA is Computer Associates who own the tool along with the whole Ingres product set. OpenROAD = OPEN Rapid Object Application Development. Naming convention follows the best acronym standards - think of a name and fit in the words later. According to one 'insider': There's a funny story that goes with this. The unlamented Pier Carlo Falotti actually invented the name, and suggested it to Steve Weyl (at the time, the head of the Tools business unit), but in Pier's weird French-Italian accent it sounded like "OpenRod". Steve smiled and nodded, they way you do when the boss says something indescribably dumb. It didn't hit Steve until hours later than Pier had said "Open *Road*, and that it was a fantastic name. OR is a suite of development application tools, comprising a GUI builder and 4GL, and a set of configuration and application management tools. It facilitates the development of GUI applications on a number of windowing platforms with the ability to communicate with a number of relational databases. History. -------- The history of OpenROAD is tied to the history of Ingres in general, so here goes. The Ingres relational database was a follow on from a University project at Berkeley in the 70's. It was originally marketed by Relational Technology Inc and later by ASK, and finally taken over by Computer Associates in 1994. Originally, the main application development tool was ABF(Application By Forms) which allowed the development of character based applications, and is still widely used. In the late 80's, ASK foresaw the rise of the graphical user interface and launched a product to take advantage of the new platform. Thus was born Windows4GL. It was mainly a UNIX/X-Motif product with a (fairly disastrous) port to MSWin 3.x. Version 2 of Windows4GL arrived circa 91 with a number of changes. 2.0 was even worse on Win 3.1, at least to begin with (there were an enormous number of patches done). The big feature in 2.0 was the debugger, though they also added multiple database session support and support for DBevents, among other things. There was also an OS/2 version of 2.0, which was eventually withdrawn after siphoning off many of the programming cycles which should have gone into improving the Windows version. There was also an OpenLook version, released the day Sun announced that they were moving to Motif. (Not kidding.) In mid 1994, version 3 of Windows4GL arrived. It coincided with a major new release of the Ingres database, and to celebrate the fact both were renamed, as OpenROAD and OpenIngres, respectively. OpenROAD was more than just a set of 'user wish list' improvements (although it meant I could put ticks against most of my list). One of the major changes to OpenROAD (and the one which was least well publicised) was the move to a fully object oriented development tool. For the first time, the developer could create their own classes as part of an inheritance structure and write their own methods for them. Another major point about version 3 was that it marked the acceptance of Microsoft as a major development platform, and WinNT became one of the primary platforms for OR. OpenROAD also broke the automatic tie between itself as a development tool and the Ingres database. Up until this point, the use of W4GL had necessitated the Ingres RDBMS, both for development and deployment. OR allowed the developer to choose between Ingres, Oracle and SQL Server for development and/or deployment and, indeed, allowed them to develop on one an deploy on any of the others. OpenROAD also allowed for far greater reuse of code through the introduction of Frame Templates, Field Templates, include scripts and the pre-compiler. who makes it? ------------- OpenROAD is developed by Computer Associates, by their team in Alameda, NY. how do I get it? ---------------- You talk to your local CA office. Australia: (61)(2)923-2006 Austria: (43)(1)894-1913 Belgium: (32)(2)773 28 11 Canada: (905)676-6700 Denmark: (45)(42)95 86 00 France: (33)(1)40-97-50-50 Germany: (49)(6151)949-0 Holland: (31)(3402)483 45 Ireland: (353)(1)478 0800 New Zealand: (64)(4)801-7654 UK: (44)(753)577733 US: 1-800-225-5224 Or call International Operations at 1-516-342-5224 for the number of your local office. Current Release. ---------------- Latest release (if I ever finish this FAQ) is 3.5, with the beta for OR 4.0 being released as I type. For info on release 4.0, the OpenROAD 4.0 Development Manager, Durwin Wright, posted some info on the upcoming release to the OpenROAD mailing list. This information is available from the OpenROAD Information and Resources page on the NAIUA web site. The appropriate URL is: http://www.naiua.org/open_road.html The text includes info on the major changes being made for Release 4.0, and also discusses the upcoming beta testing for this release. what platforms? --------------- OpenROAD is currently available on MS WinNT and 3.x, most of the major versions of UNIX and VMS. I am short of hard information here; I have tried CA without success. If anyone who is listening has the answer, I would welcome the info for future revisions. what system requirements? ------------------------- I currently run OR 3.5 and OpenIngres 1.2 on an Intel WinNT box. I need all of my P200 and 64 meg to gain reasonable performance. As a client only, I believe you can get away with 16meg and a 486. Same comments on hard info as previous section. what databases can I talk to? ----------------------------- Currently, Oracle, SQL Server, Ingres 6.4 and OpenIngres. what versions are there? ------------------------ Original release 3.0. Subsequently 3.5, whose main feature was the introduction of Assistant technology to help automate the use of field templates. Version 4.0 due 3Q97 - see http://www.naiua.org/or4_0.html printing -------- In my opinion, one of the most obvious gaps in OpenROAD is the lack of printing facilities. There are NO built in printing routines either to do screen prints or create formatted reports. There are various 3rd party tools and a couple of unsupported routines to help get round this problem, but until version 4, where I believe the problem is being addressed properly, it's a matter of make do and mend. Screen Printing Screen printing can be achieved, via an unsupported DLL available from NAIUA (to members) written by Jon Machtynger, formerly of CA, for MS platforms only. Report Printing Various 3rd party tools (see the 3rd party vendors' section.) Ability to call into Report Writer/RBF reports if using Ingres database. Printing can be achieved via DDE to, say, MS Word, using the (again unsupported) interop routines that come packaged with OpenROAD. (On my NT installation, they live in \ingres\w4glsamp\interop in the form of an exported application - this give an example of talking to MS Excel.) top tips -------- The following section is concerned with coding practice in OpenROAD. It is largely based on my own experience, and therefore is heavily flavoured with my own opinions on good practice. If your opinions differ, then fine, but I hope there is something of use for most coders buried in there somewhere. Too many UserEvents can be a bad thing. --------------------------------------- UserEvents get used a lot in OpenROAD, probably too much. There are a number of reasons for this, but most of the reasons given probably no longer hold water (if they ever really did). Local procedures did not come along until version 3.0. There were thoughts about a very simple type of local procedure in version 2 -- no arguments, no local variables, full access to the caller's local variables -- to allow code reuse, because it could be added very quickly, but it didn't solve enough problem to be worthwhile. The full local procedure support in R3 was a big job that couldn't have begun to fit into the R2 schedule. This was probably the biggest single shortcoming in the product and led to a proliferation of CurFrame.SendUserEvents masquerading as local procedures. There is a fundamental difference in the way UserEvents and procedure calls are handled. Procedure calls are executed immediately, whereas user events (just like any other event) are placed on the event queue, to be executed in their turn, possibly never (depending on the processing of events ahead of them in the queue.) There is no excuse for continuing to use UserEvents as local procedures in OpenROAD. OpenROAD has proper local procedures, and these should be used whenever a common piece of code is required to be executed 'right now'. There are one or two specific instances where UserEvents are the right thing to use; for example, to ensure that a piece of initialization code takes place after all field validation has taken place, put it in a userevent block, and send yourself the userevent from the frame's initialization block. Also, you cannot communicate directly between open frames, (e.g. by trying to execute a call() against the prochandle of a local procedure in the other frame). This needs a senduserevent. Note that you do not need to send a userevent to change the value of a field on another frame. E.g. fld = frameexec.FieldByFullName(value='o.name'); fld.SetFieldValue(value = 'George the giraffe'); This kind of direct synchronous processing is preferable (and more reliable) than UserEvents. Another common (mis)use of UserEvents is to keep track of opened frames. E.g. Frame a is going to open frame b, or if b is currently open, then bring it to front. But how does a know if b is open or not? One way is as follows (NB NEVER DO THIS) In frame b: ON CLICK close_btn, ON TERMINATE = DECLARE ENDDECLARE BEGIN FrameExec(CurFrame.ParentFrame).SendUserEvent('Closing'); END; In frame a: INITIALIZE = DECLARE FrameBPtr = FrameExec DEFAULT NULL; /* This must be default null */ ENDDECLAE BEGIN END; ON USEREVENT 'Closing' = DECLARE ENDDECLARE BEGIN FrameBPtr = NULL; END; ON CLICK open_frameb_btn = DECLARE ENDDECLARE BEGIN IF FrameBPtr IS NULL /* not yet opened or opened and subsequently closed */ THEN FrameBPtr = OPENFRAME frame_b; ELSE /* Reuse frame b with a bring to front */ ENDIF; END; This is not lovely and gets decidedly less so the more frames that need to be opened. Better is to make use of the undocumented (but it is hidden in the release notes) attribute, FrameExec.WidgetID. It has a value of zero if the frame is closed, and something else if the frame is open. (It is very unwise to rely on the value of WidgetId as being anything more specific than 'something else' for an open frame.) So now, in the situation above, frame b needs no code at all to tell a its status. In frame a: INITIALIZE = DECLARE FrameBPtr = FrameExec; /* This must NOT be default null */ ENDDECLAE BEGIN END; ON CLICK open_frameb_btn = DECLARE ENDDECLARE BEGIN IF FrameBPtr.WidgetId = 0 /* not yet opened or opened and subsequently closed */ THEN FrameBPtr = OPENFRAME frame_b; ELSE /* Reuse frame b with a bring to front */ ENDIF; END; unnamed buttons and find method on parentfield.childfields ---------------------------------------------------------- Often it is desirable to have buttons next to fields to which they are functionally related, but this can be difficult to achieve if the field represents the attribute of an object, mapped by a composite field, whose datatype is set to the appropriate user class. The compiler will not allow any named fields in the composite, other than those that match the attribute names. One solution is to add attributes to the object to cope with the name of the button. This is not really a solution at all. Imagine saying to a DBA, "Could you add a couple of columns to a table for me so I can display some buttons on the screen?". The response would be predictable. The object model should be treated with the same reverence; we don't go adding attributes to objects that the user has defined for us, just for display purposes. So what to do...? A solution that works quite nicely is to group the field in question in, say, an *unnamed* FlexibleForm, with an *unnamed* button. The compiler can't object because it can't 'see' the unnamed fields. Then, in the code for the top level composite, mapped to the object: ON CHILDCLICK = DECLARE ct = integer not null; Fld = FormField DEFAULT NULL; BtnFld = ButtonField DEFAULT NULL; ENDDECLARE BEGIN BtnFld = CurFrame.TriggerField; /* you can make the find as general or as specific as necessary */ BtnFld.ParentField.ChildFields.Find('ClassName', Value='EntryField', RowNumber=BYREF(ct)); Fld=BtnFld.ParentField.ChildFields[ct]; /* Fld is now pointing at the field next to the button that was clicked */ Fld.DoWhateverYouLike(); END; clientdata, clienttext ---------------------- Note that from patch 4119 onwards, every class has the ClientData attribute. Very useful in all sorts of ways. Gives the ability to link any two objects. ClientText can be very useful for holding additional information about a field, including, for instance the name of its validation routine. tablefield access ----------------- (Non MicroSoft platforms) When loading a TableField with a large number of rows, it may pay you to load the data into an array of the same class first and then duplicate() the array to the TableField array. The reason for this is that if you load directly into the TableField, there can be a noticeable delay at the end of the select where the TableField scroll bar seems to be flickering for a number of seconds. This is because that every time a row is loaded into the TableField an event is queued to resize the scrollbar. At the end of the select loop (or whatever) the system then processes all nine zillion queued events and you get to sit and watch. If you load direct into an array and then duplicate() across, you only get the one resize event. use of choicelists as fast lookups ---------------------------------- The ChoiceList class can be very useful for storing fast lookup lists by making use of its built in search methods: IndexByText, TextByValue, etc. These lookup searches are faster than the more generally useful Find method available for all arrays, and allow you to hold lookups of just about anything: EnumText, EnumValue, EnumBitMap, ClientData attributes are available for every row of the ChoiceList's ChoiceItems array. These are very nice. Recommended! qo painters ----------- QueryObjects are not easy to use and indeed were not really designed for public consumption - they were going to be part of the Architect product - but they do pay dividends once mastered. They allow the data access layer of the application to be designed rather than coded on the fly; no more ever spreading lumps of SQL littered about the code. The main problem with QueryObjects is probably the lack of documentation. The section on QO's in TFM is sketchy at best. It was done at the time of the take-over and maybe the writer's mind was concentrated on more pressing matters. This means that it is not easy to see which are the important attributes to use - some of them are redundant - and in what order they need to be set - yes, it matters. Having worked out which attributes matter, the setting of these attributes is a laborious matter requiring many lines of awkward code, which lends itself to typing errors. We then come to the second major drawback in using QueryObjects; the error feedback is almost non-existent. Basically, you get told something is wrong - if you are lucky; they can fail silently, as well - and are left to figure out the rest for yourself. There is a crying need for some sort of QueryObject painter, which allows you to see what need to be set and what their current values are. Imagine if you had to dynamically create all your frames, instead of using the Frame Editor; do I need to say any more...? If I ever finish writing this FAQ, I intend to produce a QO painter and make it available through the NAIUA Tool archives. Maybe, I will even have it ready before OR 5. The point is that QueryObjects are actually worth the grief and a lot of real life OpenROAD projects are taking the trouble to use them because the benefits definitely outweigh the drawbacks. While we are awaiting the arrival of the QO Painter, here are a few points to look out for. TargetPrefix can be set early because it is just a label, whereas TargetArray is a real array reference, which must exist non null in the QO's scope. The Targets attribute allows multiple targets for each column selected. It is probably best only to make use of Targets[1] and do any extra assignment in the 4GL code. The Targets area is one of the most error prone. If the IsSelectTarget attribute is set FALSE then the data is not fetched for that column; if the IsUpdateTarget and/or IsInsertTarget are not set, then the transaction may not be sent and the QO method fails silently. If the IsUpdateWhere, IsInsertWhere attributes are not set TRUE for key columns then the transaction hits every row. Before opening a QO, make sure it is closed. E.g. IF QO.Status = QO_ACTIVE THEN QO.Close(); ENDIF; Now set the target array, if doing an array mode select, so that Scope is uppermost in your mind. Scope errors can be a nightmare to debug. Set the scope for the QO. E.g. QO.Scope = CurFrame.Scope; /* not too bad */ QO.Scope = CurMethod.Parent.Scope; /* now where did I declare that array...? */ Now you can do an Open() on your QueryObject which creates and sends the SQL. You can use the Debugger Log, with DB checked, to watch the SQL going to the server. You can also keep an eye on QO.Query.Value. While developing your QO, you can use CheckCols=TRUE in the Open() to improve feedback (a little). field templates --------------- OpenROAD comes with a whole load of useful field templates built in. They would have been even more useful with the source code, but I guess you can't have everything. It is very easy to develop your own field templates and can pay rich dividends. Here are a few examples. password field template ----------------------- Although OpenROAD doesn't supply 'Password' type fields that echo *'s for characters typed, it is easy enough to set up a field template to do the job for you. Again, when I get a minute I'll post an example to the NAIAU Tools archive. dropdownedit field template --------------------------- The principles used in the password field template can be used to simulate a searchable drop down list, where as the user types into an EntryField, the search homes in on the appropriate entry in the list. Both templates rely on a SendUserEvent loop which every time it is activated sends itself a delayed user event and checks what the user has typed. In the case of the password template, it adjusts the number of displayed *'s; in the case of the drop down edit field, it finds the appropriate place in the list. The loop is broken by purging the user event when the user leaves the field. Yes, I will post an example when I get time. reusing global lists -------------------- Standard selection lists should not be hard coded in every frame where they appear. What happens when the list changes? Are you sure you know every frame in which the list appears. One approach is to have a globally accessible frame where the lists are coded and then they are duplicated in every frame that needs them. The original list can be hard coded or data driven, but either way you achieve single point of change. avoid piggybacking events ------------------------- Often there may be several triggers for a similar action in a frame. E.g., the close action will be triggered by a menu button, a button on the screen, a toolbar icon, etc. The close action checks for unsaved changes before it returns, which code you do not want to have to duplicate. One way of avoiding the code repetition is to 'piggyback' the triggers on to on event block: ON CLICK close_btn, ON CLICK menu.file.close_btn, ON CLICK toolbar.close_btn = DECLARE ENDDECLARE BEGIN /* Check unsaved changes and if OK then close */ END; This might seem an eminently sensible way of approaching the problem, but in fact is a slippery slope, paved with good intentions, on the road to nowhere (holy mixed metaphor). The problem lies in the fact that as soon as my user tells me that they no longer like the toolbar, and I obligingly remove it, my code no longer compiles. I have a horrible cross dependency between my code structure and the objects that happen to be on the screen. Ugh! But obviously I do not want to recode the same block several times, so I parcel it up into a frame level local procedure and put a call to it behind each of the relevant 'close action' objects. Thenceforth, I can add and delete such object to the frame as the user sees fit without having to touch already stable code. #-- for comments that the compiler won't see -------------------------------------------- To put in comments that won't be even picked up by the compiler: For Blocks of text: #if 0 loads of stuff in here that won't even show in the processed script #endif For single lines: #-- this is not documented but it works parent attribute for procedures as well as frames ------------------------------------------------- Ever wondered how to find the calling frame/procedure from a procedure or method? You can't use ParentFrame, because it's not defined at this level. The undocumented Parent attribute does the same job and works for ProcExecs and MethodExecs as well. Use it at your own risk. books/periodicals ----------------- None, except for the manuals that come with the product. Available from CA to holders of any CA product licence, but not otherwise. The only books that I know of pertaining to OpenROAD are those sold by Computer Associates. There are 4 main manuals: System Reference Summary, Programming Guide, Language Reference Manual, and the Application Editors User's Guide. Remembering a debate here on this newsgroup (comp.databases.ingres) several months ago about whether individuals with no Ingres licenses could purchase these documents, I called CA's document ordering number (1-800-841-8743 in the US) to get their story. I was told that as long as you have ANY sort of CA license (doesn't have to be an Ingres product) you can purchase the above-mentioned set of manuals for $50 (that figure sounds low, but hey, that's what I was told). If you don't have ANY CA licenses, you are out of luck, they are not allowed to sell you any of their documents. I think this documentation issue has gotten unnecessarily murky here! OpenROAD version 3.0 came in a big box with printed manuals including the "Language Reference Manual," the "Application Editor User Guide," and so on. These are nice to have around in paper form, but they are not available for OpenROAD 3.5 and beyond. In 3.5, the current version, CA reorganised the documentation to put a lot into on-line help files, accessible from inside the development environment. Additionally, however, they "reprinted" the old manuals more or less in full (and without much new material), also in on-line format. In the MS-Windows distribution, all of the old manuals are available on-line, and you reach them by clicking the "on-line documentation" icon in the OpenROAD development folder. So there's more documentation on-line than what you get in the help files, if you know where to go for it. I don't know how they did the on-line documentation for the UNIX distributions, but I know for a fact that they did not do paper documentation for 3.5. Basically, I didn't want anyone to get his or her hopes up too much--the documentation, on-line or not, is really not what it could be, and it would indeed be nice if some kind soul would write an OpenROAD book to flesh things out a bit more. how do I deal with strings > 2000? ---------------------------------- The only way to handle strings above the varchar limit of 2000 bytes is to use StringObjects and their associated methods for filehandling and database handling. They are well documented in TFM. how do I handle images? ----------------------- Use BitMapObjects; they work a lot like StringObjects. Again, RTFM. Patches: their numbers, contents, where to get ---------------------------------------------- Information on the latest patches and the bugs that they fix can be obtained from CA Tech Support. Patches for OpenROAD can also be obtained via anonymous ftp from mf.cai.com When I last went to look (2nd September 97), the appropriate directory for me was: /CAproducts/ingres/int_wnt/OpenROAD/3.5_02_00/Ingres/p4796/ but YMMV. Upcoming Releases & their features ---------------------------------- At the time of writing OpenROAD 4 is due any day now. Information is available from CA at (http://www.cai.com/products/ingres.htm) and from NAIUA at http://www.naiua.org/or4_0.html Access to non-Ingres DB's: how to & experiences ----------------------------------------------- There follows a series of hints and tips for converting between Ingres and Oracle as the target database for OpenROAD applications. It was originally posted on comp.databases.ingres by Graham Bolton, Principal Consultant Elegant Relational Development (ERD) bv Hoogstraat 125a, NL-3111 HD SCHIEDAM, The Netherlands Tel: +31.10.2732233, Fax: +31.10.2731114 INTRODUCTION One of ERD's clients had seen our system generator, Elegance, in action, generating OpenROAD applications using an INGRES database. They had one problem: they are an Oracle shop. (Asked which version, they replied "an average of six": they have Oracle 5, 6 and 7 in production!) They understood the concepts upon which Elegance is founded and wanted to use it to generate GUIs to work with Oracle. The OpenROAD Driver to Oracle was not yet on the market, but the client realised that OpenROAD was worth waiting for. They chose to rent Ingres to serve as their development database until we were able to provide them with our Oracle offering. They were able to build up the specifications of their system in the ERD Data Dictionary resident in a CA-INGRES 6.4 database, testing the resulting application as they worked, with the knowledge that we would move them to ORACLE within three months of the Driver to Oracle coming onto the market. This document contains a translation of the notes made by the development team in the two months which they spent changing the code generator to speak native Oracle SQL instead of native Ingres SQL. We do not use Open SQL, the subset of Ingres SQL, since it prevents the use of the better features of both databases. The fact that rules and procedures in Ingres do not translate one-to-one to triggers and functions in Oracle is not a good reason to avoid their use! TRANSLATION OF DEVELOPMENT TEAM NOTES Copy function There is no equivalent for the Ingres copy() statement in Oracle. We had to hand build file handling statements for each table in the ERD Data Dictionary in order to implement our meta-data import and export functions. The Oracle Data Loader (ODL) is a separate utility and its functionality is not available via the OpenROAD SQL connection. Datatype conversion While we were able to find equivalents for the various we still had some problems with datatype conversion. For example, conversion in INGRES of INT2(1,000,000) does not give an error but the result is incorrect - Oracle does inform you that the value is too large. If you want to write portable applications be careful. If you have used a smallint in INGRES, you have a range of -32768 to 32767. In Oracle you have to use the datatype number(6), so your users can then use a value of 40000! We explicitly check the lower- and upperbound so that the data in Oracle could be exported back to Ingres. Date 'today' and 'now', DBMSINFO() Use for "date('now')" the Oracle function "trunc(sysdate)" If you want the client time you still can use "date('now')" SELECT sysdate FROM dual select TRUNC(sysdate) FROM dual Dbmsinfo('dba'): via user_role_privs with granted_role='DBA' Dbmsinfo('database'): No idea! Any suggestions? Dbmsinfo('user') SELECT user FROM dual /* or */ SELECT username FROM user_users Naming of variables In database procedures we gave variables the same name as the corresponding columns of tables. With Oracle this is a problem. So we have added p$ before all parameter names. NULL value with strings An empty string ('') is not allowed in Oracle, it will be saved as a NULL. If you want to look for an empty string in Oracle, use NULL or the NVL function. We also use the statement IFNULL(,'') in Ingres: the Oracle equivalent NVL(,'') is has no effect because if the column is NULL the empty string will be used and an empty string is NULL. Patterns Selects with character matching patterns are not possible (e.g. like '0\[1-9\]' escape '\'; we have found no equivalent for this. Raise_application_error The call of raise_application_error stops a function or procedure. We use a return value of -1 to communicate to the client that we had an error. This is not possible in Oracle. So we initialize an in/out parameter to a value of -1 and set it to 1 at the end of the procedure if everything went well. Return value It is not possible to return a value with a database procedure, use instead a function. INGRES create procedure employee$del( .... return 1; end; Oracle create or replace function employee$del( ... return 1; end employee$del; Reserved words The set of reserved words differs from INGRES. This makes life interesting! String and date's with NULL values INGRES sees a difference between a string (or date) with the value '' and NULL. Oracle sees all '' values as NULL. Special character "@" The character "@" is reserved for dblink in Oracle. So if you want to use these characters for a table name you must delimit the name with quotes ("). Statements in Oracle After each statement you must add a semicolumn (;), also after an endif or endwhile in a database procedure. In Ingres, the semicolon acts as a statement seperator, in Oracle as a statement terminator. INGRES: if iirowcount != 0 then raise error 1 'There are still Employees, Department not deleted' endif; Oracle: if sql%rowcount!= 1 then rollback work; raise_application_error( -2000, 'There are still Employees, Department not deleted'); endif; String concatenation Instead of using '+' for string concatenation use '||'. Session In Oracle you can change the session variables with 'alter profile' and 'alter session'. The session variables are the CPU time per call and session, the connect time per session and trace facility. A user has always a profile, starting with a default. Setting of the total CPU time per session ALTER PROFILE LIMIT cpu_per_session { | UNLIMITED | DEFAULT } Setting of the total CPU time per call (fetch, execute) ALTER PROFILE LIMIT cpu_per_call { | UNLIMITED | DEFAULT } Setting of total connection time ALTER PROFILE LIMIT connect_time { | UNLIMITED | DEFAULT } Setting of maximum idle connection-time ALTER PROFILE LIMIT idle_time { | UNLIMITED | DEFAULT } Setting of maximum read of data blocks per call ALTER PROFILE LIMIT logical_reads_per_call { | UNLIMITED | DEFAULT } Setting of maximum cost per session: ALTER PROFILE LIMIT composite_limit { | UNLIMITED | DEFAULT } On/off of the trace facility ALTER SESSION SET sql_trace = { TRUE | FALSE } /* trace info does NOT appear in the trace window */ System catalogs The Oracle system catalogs of course are quite different to those of Ingres: Your own tables: USER_TABLES Their columns: USER_TAB_COLUMNS Accessible functions/procedures: ALL_OBJECTS where object_type='{ PROCEDURE | FUNCTION }' Information of users in the database: ALL_USER Use of newline in scripts When you create database procedures or functions dynamically using a text-string be careful. Between "begin" and "end" it is not possible to use special characters like HC_NEWLINE. In all other objects you are free to use them (e.g. in the value parameters of the INSERT statement). Oracle files interpreted with SQL+ must end with a string with the character '/'. Compile errors with database objects After the creation of database objects they must be checked. Not all errors will result in compilation errors, so you do not know if your objects will work! Check them with a select on the view "user_objects" with the status != 'VALID'. Problems can occur with functions/procedures which call each other. If you have a main procedure which calls a child procedure and you redefine the child procedure, the master procedure will become invalid. Create or replace In Oracle you do not have to drop a function/procedure explicitly in order to change it. You can use the statement CREATE OR REPLACE FUCTION Parameter passing Parameters are not passed by name, so you must be careful to put the parameters in the right order (just like in C!). INGRES create rule employee#r1 after insert or update on employee execute procedure department#reg ( number = new.number, message = 'Department' ); Oracle create trigger employee#a before insert or update on medeweker for each row begin ... department#reg(:new.number, 'Department'); end; Parameter passing With a function/procedure you can pass parameters. We are used to specify all necessary parameters with 'not null not default'. With Oracle you have to declare them with 'IN'. Synonyms The use of synonyms can be useful, you do not have to specify the user. Do not forget to remove the synonym with a drop table. Table owner Like in INGRES each table has a table owner. The principle is the same. Oracle has an exception. If you want to retrieve information from an other non-dba user you have to add the owner of the table in the from clause (FROM 'owner.table', see the "User's Guide" of the Oracle7 driver). iirowcount and iierrornumber in database procedures After an database action we use the following check. First check rowcount, if it is < 0 we have a problem. After this we check errornumber, if errornumber !=0 Ingres has already sent a message to the client, so we do not. This is completely different with Oracle because SQLCODE is only used in the exception part but with a user defined exception we always find SQLCODE of 1. Raise_application_error Raise_application_error(, ) is what we have used instead of raise error. The errornumber must lie between -20999 and -20000 and has a maximum length of 512 bytes. The procedure will only perform a rollback for the actions in the function/procedure where it is used. Rollback with an error You can use the procedure raise_application_error() from Oracle to send an errormessage (and an errornumber) to the client. The action of the procedure is not a complete ROLLBACK, only the statements within the function/procedure are rolled back! So if you want to be sure that your complete transaction will be rolled back you have to put an explicit ROLLBACK before a call to the procedure raise_application_error; Rollback with drop table A drop table can not be rolled back in Oracle, so be careful! Triggers With Oracle you can use before and after triggers. With each type of action (insert, update or delete) you can only define one before or after trigger. In tests we found that even if you define a before trigger on an INSERT, the INSERT will be performed first. So you cannot write a before insert trigger to check for duplicate key on INSERT because Oracle will give its own error message before your trigger can. It is possible to call several procedures within one trigger. Within a procedure it is possible to call an other procedure. Execute immediate Be careful not to end strings passed to execute immediate with ';' (See the "User's Guide" of the Oracle7) Inquire_ingres, Inquire_sql: do not use them We were used (of course) to using inquire_sql. In OpenROAD it is better to use the attribute DBMSError. This object also has other methods and attributes like Errornumber, database, rowcount, commitwork(), rollbackwork(), connect(), opennewconnection() en disconnect(). If you use these you will be more database independent without incurring significant overhead. Return value In a strange way we always get a return value 1 of a database function. We use a value != 1 to indicate that we have encountered an error. Now we use the DMBSError attribute of the databasesession object rather than the return code. SQL statements in code Try to avoid the use of functions in SQL statements within your 4GL code. Use the functions within OpenROAD. Trace of queries If you want to trace queries into a file, use the parameter IIQL_QUERY_LOG = in config.ing of Open Road (See the "User's Guide" of the Oracle7 driver). Values from the database The values retrieved from the database seems to be all right. You must be careful with char length (255) and date intervals (See the "User's Guide" of the Oracle7 driver). Connecting to more than one type of database from the same application. ----------------------------------------------------------------------- We have data residing in CA-INGRES databases and ORACLE database. We would like to use an OpenROAD application to access data from both databases concurrently. We were told that an OR app could only connect using a single driver to the backend. Assuming I am running on a platform that has CA-OpenROAD drivers for INGRES and ORACLE, is there a way to have a single app access data from both backends? Would a gateway help the situation? You cannot use both drivers at once, so a gateway might be the solution. Or you could call a 3GL module from the O/R app that would make ODBC calls to access data in one system, while you use the driver to access the other. on-line resources ---------------- There are a few on-line resources for OR. Computer Associates' home page on the web is at http://www.cai.com The North American Ingres Users' Association is very good for all things Ingres - http://www.naiua.org. In particular they have some news on OR 4 - http://www.naiua.org/or4_0.html Elena M. Yatzeck (emy@gbsun.uchicago.edu) runs the OpenROAD Special Interest Group which has an automatic mailing list (Majordomo@gsbpop.uchicago.edu). training -------- CA run OpenROAD training both in the UK and US. In the UK, they run two OR courses. Application development in OR (5 days) which covers the basics from scratch. Developing Production Standard Applications in OR (4 days) which goes into more detail on how to get the best from the product in real applications. Both the UK courses stress the OO features of OpenROAD, and constitute a radical rethink of the approach used for Windows4GL training. US Application development in OR (4 days) covers basics from scratch. http://www.cai.com/profserv/courses/oi180.htm First Half: Using the OpenROAD Environment * Creating Applications, Frames, and Fields * Writing 4GL Scripts * Objects and System Classes * Composite Fields * User Classes * Accessing Data * 4GL Coding and Debugging * Creating Menus Second Half: Building Applications * Object Orientation and Application Design * 4GL Coding Revisited * Storing and Displaying Images * ChoiceFields and TableFields * Inter-Frame Communications * Error Handling and Transaction Management * Application Management There follows a quote from CA training in the US. Start Quote >>>>>>>> CA's basic course in OpenROAD is OI180, which has just been revised.(July 97). The new version of the course, which is what will be given starting now, emphasises basic application-building skills. The first half is devoted towards gaining familiarity with the development environment, and the second is devoted towards building a simple application (basically, a personnel application that allows employees to be listed by department and entered using two frames). The course is four days long, and requires either OI140, our introduction to application development with OpenINGRES, or previous application development experience with Ingres or OpenINGRES as a prerequisite. The course will be upgraded to OpenROAD 4.0 (or possibly supplanted with a 4.0 version of the same course), but we have not set a time for when that will occur. I don't expect the changes to be major; basically, we will replace the screen shots and interface descriptions in the first half of the course with their 4.0 equivalents. At the moment, there are no advanced courses in OR offered in the US (but see the below comments about our plans). However, there is an advanced Windows4GL course that we offer that applies to OpenROAD as well. It covers GUI design, portability considerations, and font and colour customization. We are currently in the early stages of creating two advanced OpenROAD courses: one on dynamic programming techniques using OpenROAD (including prochandles, dynamically added fields, and creating frames, procedures, and user classes at run-time), and the other about advanced database access techniques in OpenROAD, including synchronizing tablefield contents with database tables, the datastream classes, dynamic SQL, and dynamic expressions. I can't give you a date for when these will be ready. We are also considering creating a third advanced course about integrating 3GLs with OpenROAD. I don't know if this will happen; if it does, it is lower priority than the other two. A fourth possible course will be in using the OpenROAD 4.0 report writing facility. We haven't discussed this much yet; my guess is that it will happen before the hypothetical 3GL integration course, but after the other two. A fifth possibility is a US version of a GUI design course that's currently offered in the UK. I just received a copy of this, and haven't had a chance to review it yet, so I don't know where that will go. If it looks readily usable, it may show up on our schedules fairly soon; if we feel that it will take a major effort to make it palatable to US audiences, it will either take a long time or never happen. All of the above discussions of new courses is subject to change, depending mostly on customer feedback. If we hear that the reporting facility is much more important than the others, for instance, its priority can be changed. <<<<<<