RE: Silly question -- with an answer (long)
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?
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 || "'";
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 (...);
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.
Given all these complexities, is there a reasonably simple algorithm that
can determine the TabID for an arbitrary table name? Yes, but it is not
one that can be written sensibly in pure SQL, so there isn't a single SQL
statement which will do the trick. The algorithm that follows does derive
the TabID in no more than two SQL statements. The examples that follow
ignore the database and server information as they add minimal extra
complexity.
1. Validate whether the table exists by preparing 'SELECT * FROM
fulltablename'. If this works, the table exists and can be located
using just the information in fulltablename. This avoids some problems
with MODE ANSI databases when no owner name is given.
2. If the owner name is specified in quotes, then the follow-on query is
quite simple. In fact, if the owner name is given in quotes, you do
not have to do the