ANSI mode databases, Was: Returning SQLCA values: How?

"Chao Y. Din" <cdin@explorer.csc.com> wrote: :Nils Myklebust wrote: :> :> The SQLCA info on the number of rows affected is of course the answer, :> although it isn't allways easy to get at from non Informix languages. :> Don't set your database to ANSI mode. You'll get nothing but problems. :> :I am not sure why you oppose to ANSI mode database. All our databases :are ANSI mode and they have been in the production for several years. :The single most important reason we choose ANSI mode is to force :programmers to use transactions. :Please let me know the problems with ANSI mode. First you'll have to excuse a somewhat exessive blurt on this. It's a long time since I checked out the ANSI mode and at the time I disliked the restrictions. That's the major part that has stuck, and I realy should have checked it out again before posting anything about it. I post this long answer here so anybody who think I am wrong can correct me and I may evaluate this once more. So here goes from my recolection: 1. There is no need to set mode ANSI to force transactions. You simply need to turn transactions on and still be in Informix mode. 2. The one major reason for ANSI mode is to use nothing but ANSI compliant SQL statements. The only purpose I know of for doing that, is to make it easy to change to another database server. The limitations you are up against here in an Informix database are however far to big for us to even consider. We need several of the Informix extensions to be able to make our applications fly. When we go to the Universal Server there will be a very large number of aditional extensions as compared to the current ANSI standard. There is no way you can reap all the benefits of the Universal Server without using most of these. This is sad, but true, although many of the extensions will be compliant with SQL3. This standard is however not planed to be finished until 1999 I believe, and that's a long time to say the least. 3. If you are using Informix extensions to SQL extensively I can see no reason to set mode ANSI. There are also some extensions that can't be used at all in this case. 4. In a mode ANSI database you are allways inside a transaction as soon as you connect to (open) the database. A commit work allways also starts a new transaction. This is a flaw in the ANSI standard. A transaction should allways be started explisitly like it is with the begin work statement in an Informix database. I am not 100% sure about all the ramifications of this in an Informix database (in ANSI mode), but it seems to me you might get even more problems with logging as every user will allways have an open transaction in effect. With a large number of active users this may not be a significant problem. At least you have to make very sure you use for read only on read only cursors. Otherwise you will see a lot of locking for no purpose whatever. 5. Tables, indexes, synonyms, views and so on are owned by someone and priviledges can be granted to others. However in a mode ANSI database you allways have to spesify the owner to access a table, view or synonym (if you aren't the owner which you normaly won't be in a multiuser environement). That creates to much extra work for nothing for our purposes. In a standard Informix database you can still create two tables by the same name if they are owned by different users. This is the only case where you have to explisitly give the user name. We don't do this. For our purposes every "object" in the database is owned by the "corporation" and that's the way we want it to be. We don't want users to create their own tables. They are all created by design at a corporate level. I relise there *may* be cases where it may be advantageous to have tables split by user. We just haven't come across such a situation yet, and we can still do it without mode ANSI. 6. An advantage with a mode ANSI database for most cases is that there are no default table level priviledges when a new table is created. They must all be granted. You can however still control this reasonably easily in a normal Informix database, so this isn't enough of an advantage for us to use mode ANSI. 7. On a mode ANSI database you aren't supposed to be able to turn of transactions at any time. This is a significant problem for us for mass loading of data. There may be other problems or advantages. I fail to see any significant advantages though except for the one noted in point 6. Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org