manatt@lll-winken.ARPA (Doug Manatt ) (08/19/87)
I have been working with RTIs ingres for some time now, but have only just noticed what I consider a serious flaw in the functionality that it provides. From all I know about it, it is impossible to have their RDBMS provide your program with a row of your data, allow you program to transform it and the replace the transformed data in the same table in place of the original data. Their RDBMS provides a "REPLACE" capability, but only replaces that can be computed in the "BACKEND" are allowed. The other alternatives are to load the rows to be worked on into arrays, temporary files, or "form" structures. If I am missing something simple, please send me mail, but if this is actually so please comment in news if this is the general situation for other products, or if this is for some reason desirable. Doug Manatt LLNL (415)422-7257 manatt@lll-winken.arpa
cim2@pyuxv.UUCP (Robert L. Fair) (08/20/87)
>References: <640@lll-winken.ARPA> >Problem with > I've been working with RTI ingres for over 5 years (both QUEL and SQL versions) and havn't found any problems like you are discussing. I assume you are programming in EQUEL/C or ESQL/C rather than interactively * For ESQL/C simply use DEFINE CURSOR... FOR UPDATE then do UPDATE <updatestat> WHERE CURRENT OF <cursor>; to update the values in the current record. * For EQUEL/C single record updates are simple do, quickest is to use the tid for the update: ## long itid; ## begin transaction /* Ensure updates are atomic */ ## retrieve (<data>, itid=<table>.tid ) where <qual> <munch data> ## replace <table> ( <update> ) where <table.tid>=itid ## end transaction * For multi-record updates either do a calculated update, for example: ## replace emp (salary=emp.salary*raises.percent) ## where emp.dept="sales" ## and emp.grade=raises.grade * or use program variables: ## int iraise; ## replace emp (salary=emp.salary*iraise) where ... There are basically two types of updates: 1. Single record updates 2. Set or group updates INGRES allows both of these (as shown above) with no problems. Note that the QUEL language doesn't allows nested queries, so attempts to emulate the SQL UPDATE WHERE CURRENT OF don't work in QUEL (Of course you can always use ESQL/C from RTI if you need that) If you are having problems try stepping back and rethinking your application design, in particular if you are an SQL person learning QUEL you will need to "unlearn" a number of things. If your application involves showing the user a number of records, allowing random update by the user then writing the changes away, try one of: a) Loading the data into a FORMS TABLEFIELD dataset then doing a batch update with ##unloadtable once the user has viewed/changed all the data. TABLEFIELDS are *really* useful. This is probably best for up to a few hundred records. b) Write the updated records into a temp file, then use COPY to bulk-update the database. This is fastest if many records have been changed. Having implemented both QUEL and SQL languages from scratch (my version of QUEL *does* allow nested updates by the way) it's my humble opinion that QUEL is generally a cleaner, easier to use, and more powerful language than SQL. Tough sh*t that IBM invented SQL, so thats the new standard :-( Regards, Bob Fair Bellcore/CHC ihnp4!pyuxww!pyuxv!cim2
waltermy@brl-adm.ARPA (Mike Waltermyer ) (08/21/87)
And what happens when RTI decides to drop support for tids??? Also, in using tids to do the updates, we've experienced problems in getting the wrong record. What operations would be responsible for changing the tid of a record. I assume a modify would do it, but what other operation would? Mike...
manatt@lll-winken.UUCP (09/01/87)
In my article: About updates in RTI ingres In article <640@lll-winken.ARPA>, manatt@lll-winken.ARPA (Doug Manatt ) writes: > > From all I know about it, it is impossible to have their > RDBMS provide your program with a row of your data, allow you program to > transform it and the replace the transformed data in the same table in place > of the original data. Their RDBMS provides a "REPLACE" capability, but only > replaces that can be computed in the "BACKEND" are allowed. The other > alternatives are to load the rows to be worked on into arrays, temporary > files, or "form" structures. In article <312@pyuxv.UUCP>, cim2@pyuxv.UUCP (Robert L. Fair) writes: > > * For EQUEL/C single record updates are simple do, quickest is to use > the tid for the update: Thanks for pointing out that this is possible using SQL. Note on tids: RTI repeatedly claims that TIDs are unsupported and may change with any future release > * For multi-record updates either do a calculated update, for example: > > ## replace emp (salary=emp.salary*raises.percent) > This works fine only if you are performing a function supported by the ingres backend. > Note that the QUEL language doesn't allow nested queries, so attempts > to emulate the SQL UPDATE WHERE CURRENT OF don't work in QUEL > > a) Loading the data into a FORMS TABLEFIELD dataset then doing a batch > update with ##unloadtable once the user has viewed/changed all > the data. TABLEFIELDS are *really* useful. This is probably > best for up to a few hundred records. > > b) Write the updated records into a temp file, then use > COPY to bulk-update the database. This is fastest if many > records have been changed. These seem to be rather drastic measures when all that one wants to do is perform a simple update operation with a function that is not provided by RTIs backend. Note: What is needed is not the nested query capibility, but the ability to "check out" data that will be returned as the next database operation. This could also be worked around by allow the user to specify a library of transformation functions that would be called by the backend. Doug Manatt Lawrence Livermore Lab (415)422-7257 manatt@lll-winken.arpa