IDS Forum

Re: How to determine primary key column name?

Posted By: Jonathan Leffler
Date: Wednesday, 20 April 2011, at 12:01 p.m.

In Response To: Re: How to determine primary key column name? (HERNANDO DUQUE)

On Wed, Apr 20, 2011 at 05:10, HERNANDO DUQUE <hduquec@hotmail.com> wrote:

> Thank you very much for your help, your article is very good.
>
> I decided to write this post just for those newbies like me, that are
> reading
> this forum and wanted to find the step by step for obtaining primary key
> columns.
>
> 1.- Get the tabid for your table:
> SELECT tabid FROM systables WHERE tabname = 'my_table';
>

Determining tabid is a very important step - and this mechanism usually
works, most (but not all) of the time.

2.- Get the idxname for the index that holds the primary key:
> SELECT * FROM sysconstraints WHERE tabid = my_tabid AND constrtype = 'P';
>
> 3.- Get the colum(s) id that hold the primary key (look into fields <part1>
> through <part16>):
> SELECT * FROM sysindexes WHERE tabid = my_tabid AND idxname = 'my_idxname';
>
> 4.- Get the colum(s) name:
> SELECT colname FROM syscolumns WHERE tabid = my_tabid AND (colno = 1 OR
> colno
> = 2 ... OR colno = 16);
>
> You will get up to 16 rows with the columns names, depending on how many
> fields your primary key is defined.
>

Way back - 13 years ago - I wrote a long email about how to determine the
tabid for a given table in a MODE ANSI database with DELIMIDENT set and so
on. I found it in my archives, and I'll repeat it here. (Oh, and SQLCMD
has had INFO operations for a long time now, too. The code to determine the
'object ID' for a named object (table, procedure, trigger) is available in
the SQLCMD source in sqlownobj.ec.

---------------------------------------------------------------------------

From: Leffler, Jonathan on Tue, 21 Apr 1998 05:44
Subject: RE: Silly question -- with an answer (long)
To: 'informix-list@iiug.org'; 'Jonathan Leffler'

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 first query, but it is better to do so as the error

condition gives the correct missing table name regardless of the type

of database.

SELECT TabID FROM "informix".SysTables

WHERE TabName = 'tablename'

AND Owner = 'owner';

3. If the owner name was specified without quotes, the first row returned

by the following query is the relevant TabID:

SELECT TabID, Owner FROM "informix".SysTables

WHERE TabName = 'tablename'

AND Owner IN ('owner', 'OWNER')

ORDER BY Owner;

If it is a MODE ANSI database and both the "OWNER".tablename and

"owner".tablename tables exist, then the 'OWNER' entry will appear

before the 'owner' entry (because, in ASCII, the upper case letters

sort before the lower case letters). This is the correct one to use;

the unquoted OWNER would have been case-converted. If the OWNER entry

did not exist, then the initial (step 1) query would have failed. If

it is a non-ANSI database, then only the 'owner'.tablename entry will

exist.

4. Otherwise, the owner name was not specified and either:

* There is an entry where the user owns the table (and there may

also be other tables with the same name and other owners), or

* There is no entry where the user owns the table, but there is an

entry where someone else owns the table.

This requires a moderately complex disjoint UNION query to handle both

the possibilities correctly. The first condition can apply in both

MODE ANSI and non-ANSI databases; the second condition can only apply

in a non-ANSI database.

SELECT TabID FROM "informix".SysTables

WHERE TabName = 'tablename'

AND Owner = USER

UNION

SELECT TabID FROM "informix".SysTables

WHERE NOT EXISTS(SELECT * FROM "informix".SysTables

WHERE TabName = 'tablename'

AND Owner = USER)

AND TabName = 'tablename' AND Owner != USER;

It might be possible to combine the three variant statements into one
enormous UNION, but it would probably not be worth the trouble to exclude
the other cases each time. The Owner column would have to be selected in
all the variants, and the ordering clause would still be needed.

Establishing the TabID for a table is the key to accessing the rest of the
system catalogue for information about that table. It was tempting to use
the INFO COLUMNS FOR OWNER.TABLENAME as the example INFO statement, but the
information for that can be gleaned by describing the step 1 SELECT. To
get complete information including whether each column accepts nulls or
not, you would have to use an SQL descriptor; all the other needed
information is available via the sqlda structure as well as an SQL
descriptor. This would sidestep the process of finding the TabID.
However, for information such as the indexes on a table, the TabID is
needed, so the full algorithm above simplifies the processing.

If anybody can see any problems in the above, or a simpler way of achieving
the same results unambiguously, I would like to know about it.

Yours sincerely,
Jonathan Leffler (jleffler@visa.com) #include <many-aliases.h>

---------------------------------------------------------------------------

End of historical relic

--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2008.0513 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."

--bcaec543079a12f7db04a15bbd4c

Messages In This Thread

• How to determine primary key column name?
  HERNANDO DUQUE -- Friday, 15 April 2011, at 7:53 a.m.
  • Re: How to determine primary key column name?
    Jack Parker -- Friday, 15 April 2011, at 7:58 a.m.
    • Re: How to determine primary key column name?
      Art Kagel -- Friday, 15 April 2011, at 8:46 a.m.
      • Re: How to determine primary key column name?
        Jack Parker -- Friday, 15 April 2011, at 1:49 p.m.
        • Re: How to determine primary key column name?
          Art Kagel -- Friday, 15 April 2011, at 5:52 p.m.
        • Re: How to determine primary key column name?
          Art Kagel -- Friday, 15 April 2011, at 6:04 p.m.
        • Re: How to determine primary key column name?
          Jack Parker -- Friday, 15 April 2011, at 6:04 p.m.
        • Re: How to determine primary key column name?
          Art Kagel -- Friday, 15 April 2011, at 6:15 p.m.
        • Re: How to determine primary key column name?
          Jack Parker -- Friday, 15 April 2011, at 6:21 p.m.
    • Re: How to determine primary key column name?
      HERNANDO DUQUE -- Wednesday, 20 April 2011, at 8:10 a.m.
      • Re: How to determine primary key column name?
        Jack Parker -- Wednesday, 20 April 2011, at 8:16 a.m.
      • Re: How to determine primary key column name?
        Jonathan Leffler -- Wednesday, 20 April 2011, at 12:01 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.