Re: Silly question -- with an answer (long)

In article <6hfu4b$q7j$1@news.xmission.com>, Leffler, Jonathan <jleffler@visa.com> writes > >I said: "I hope you can read the attachment. If not, I'll resubmit this >message." >I couldn't read it, so I'm resubmitting it. Sorry for the previous almost >illegible posting. > > If MS doesn't screw this up to badly, it should at least be vaguely > legible, unlike my previous attempt which relied upon MS Word to export > the document in text-only-with-line-breaks format which doesn't work, > and doesn't export text-only since it screws around with all the quote > characters. Hate MS! > >--------------------------------------------------------------------------- > >Silly question time: > How do you determine the value of TabID for a given table in an > Informix database? > >The basic answer is as simple as the question: > > SELECT TabID FROM SysTables WHERE TabName = 'tablename';> >So, what's Leffler thinking about when he asks such a simple question with >such an obvious answer? And how on earth does he manage to write such a >long message when he's already given the answer? > Mmmmm! >There are several complicating factors: > > * Consider a MODE ANSI database. > * Consider DELIMIDENT (no, on second thoughts, try not to consider > DELIMIDENT). > * Consider owner names with quotes > * Consider owner names without quotes. > * Consider remote databases. > * Consider whether the components of the name were typed in all lower > case, all upper case, or in some mixture of cases. > >I'm trying to add the INFO statement to my general purpose SQL command >interpreter, SQLCMD, and that forces me into wondering exactly how to >handle all the following table names, in both MODE ANSI and non-ANSI >databases (when the program does not know whether it is currently connected >to a MODE ANSI or a non-ANSI database): > > * INFO INDEXES FOR tablename > * INFO INDEXES FOR TableName > * INFO INDEXES FOR TABLENAME > * INFO INDEXES FOR owner.tablename > * INFO INDEXES FOR OWNER.TABLENAME > * INFO INDEXES FOR 'owner'.tablename > * INFO INDEXES FOR "owner".tablename > * INFO INDEXES FOR database:tablename > * INFO INDEXES FOR database@server:tablename > * INFO INDEXES FOR database{x}@{y}server{z}:{p}'owner'{q}.{r}TABLENAME > >The context means that this is an ESQL/C program, and the table name as a >whole has been read into a character string. The various components of the >table name are also available in separate string variables. If there are >quotes around the owner name, they have been preserved. The comments in >the last example have been duly ignored, as have any spaces, tabs, and >newlines anywhere in the commands. > >The remote database part is much the easiest to handle; you have to prefix >the SysTables part of the SELECT statement with whatever database name (and >optional server name) is provided in the table name that you are >processing. You cannot have a server name specified without a database >name too. The only place where the notation '@server' is allowed is in a >CONNECT statement, and it does not specify a table name. > >Both MODE ANSI and non-ANSI database allow you to specify the owner of >tables. MODE ANSI databases require you to do so when you don't own the >table. Consequently, it is best to write either 'informix'.SysTables or >"informix".SysTables in place of SysTables in the original solution. If >you consider DELIMIDENT, it is best to use the double-quote notation. The >user ID should be a delimited identifier rather than a string (and if you >don't understand that, just remember to use double quotes around the owner >name for absolute safety). Note that the capitalization of SysTables >doesn't matter; it's a Lefflerian idiosyncrasy to capitalize the system >catalogue names that way. > >Handling the various ways of capitalizing the tablename is easy enough. >The name should be case-converted to all lower case, unless DELIMIDENT is >in effect and the table name is enclosed in double quotes, whereupon it >must be left exactly as it is. Ignoring the issues of table ownership, >we're left with code that looks somewhat like: > > if (database and server specified) > then prefix = "database@server:"; > else if (database specified) > then prefix = "database:"; > else prefix = ""; > systab = prefix || """informix"".SysTables"; > > tablename = LOWER(tablename); > > query = "SELECT TabID FROM " || systab || > " WHERE TabName = '" || tablename || "'"; > Just about got you. >This will yield the correct answer in a non-ANSI database if the table >exists and the owner was not specified. In a MODE ANSI database, it is >only correct if the user owns the table, so for a MODE ANSI database, the >query should be: > > query = "SELECT TabID FROM " || systab || > " WHERE TabName = '" || tablename || "' AND Owner = USER; > >Table ownership is the big complicating factor. When the owner name is >specified in (single or double) quotes, the query is simple. To avoid >problems in the presence of DELIMIDENT, the code strips the quotes from the >quoted owner name and embeds the result in single quotes in the query: > > Owner = stripquotes(owner); > query = "SELECT TabID FROM " || systab || > " WHERE TabName = '" || tablename || "'" || > " AND Owner = '" || owner || "'"; > >Even that wasn't too complex, but unquoted owner names are murder. In a >MODE ANSI database, the unquoted name is case-converted to upper case; in a >non-ANSI database, the unquoted name is case-converted to lower case. In a >MODE ANSI database, there could be two tables with the same basic table >name, but with different owner names, one in upper case and one in lower >case: > > CREATE TABLE "user1".tablename (...); > CREATE TABLE "USER1".tablename (...); > Tell the users to stop messing about and just do it in lowercase. I know you can handle both but I'd put some restrictions in and get the users to stop messing about. Personally I think the engine is just too flexible in this regard. Look, let's just get a consistent way of doing things, shall we! >In a MODE ANSI database, if the user writes user1.tablename, then the >relevant table is the second of the two. By contrast, in a non-ANSI >database, you could not create the second table, but even if you could, the >first one would be the relevant one. A MODE ANSI database could also >contain other tables with the same basic table name but with mixed case >owner names, but those owner names must be specified in quotes. > I would not allow this. owners in lowercase. If you don't like it fix your database...we need more consistency and less trying to be clever. Simply, and if people have odd configurations they will just have to change them. Lowercase only and no DBDELIMITER. If thing