Re: Tables in a database, mode ANSI

On Fri, 5 Sep 1997, Peter Lancashire wrote: > Nils Myklebust wrote: > > Mark Fisher <fisherm@britannic.co.uk> wrote: > > > > :Is it possible to have 2 tables of the same name but owned by > > :2 different users on 1 database? > > > > You can in a mode ansi database, but not in a regular Informix one. > > I wouldn't ever set up a database as mode ansi. The problems are too > > many. > > I used to use mode ANSI but I was forced to abandon it at 7.10. The > benefit of allowing separate user schemas, which you need, comes with a > heavy price. > > 1. 4GL 6.02 will not compile some forms. Can you give an example? > 2. User names are stored in upper case. Only if you don't treat them correctly. ANSI mandates the upper-case treatment, but there's a get-out clause -- if the owner name is in quotes (single quotes, please), then it is treated exactly as written. It isn't an accident that programs such as dbexport use the notation: create table 'username'.tablename ... (And if it uses double quotes, make sure you never have DELIMIDENT set when you run the dbimport command!) > No problem, you might think, but USER is in lower case on Unix so all > your user-selected views fail... This is the killer. Only if you write: SELECT * FROM username.table If you treat owner names carefully, then you write: SELECT * FROM 'username'.table and everyuthing works fine. If your forms do not use the correct table alias scheme (as documented in the manuals): TABLES table = 'username'.table then it won't compile forms, you're quite correct. But if you do use the correct alias notation, then there shouldn't be any problems. > I understand Informix themselves have problems with mode ANSI. Not that I know of. It works fine as long as you understand what it means. I don't recommend that people use MODE ANSI databases if they do not have a very disciplined approach to handling databases, but they work perfectly well and provide some useful features. > Strange how the manuals show no examples using it. The manuals use the stores database for most examples, and the default stores database is not even logged, let alone MODE ANSI. > I suspect it is a tick-list feature. Only in part. It is crucial for things like TPC benchmarks too. And for people who require standards conformance, and for people who want to be sure that their database runs as securely as possible. > Why can't Informix let us have the bits of mode ANSI separately? The product is quite complicated enough without adding phased MODE ANSI compliance. > At the moment they are a rag-bag of things thought to be needed for ANSI > compliance. I disagree with the terms 'rag-bag' and 'thought'. The products have been tested for ANSI compliance at SQL-89 and SQL-92 entry-level. The features are needed to attain that level of compliance. > There are a vast number of other things needed, such as real schemas and > catalogs and tons of extra syntax and semantics... I think you're referring to the higher levels of SQL-92. I agree that they would be desirable, but none of the other major database vendors has full compliance to the top SQL-92 level as yet. I don't think that's a particularly good reason for Informix not to get there -- in fact, it would be a useful tick box item to have full compliance, and it would probably force other people to match us -- but that is separate from the issue of the MODE ANSI database behaviours. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>