Primary Key Information

[abrymn]

Hi,
I am unfamilier with progress and I need to write a sql statement that returns the primary keys of tables when I pass it the database and the table. I think i need to query the SYS_KEYCOL_USAGE table but not sure. I need this in order to download the key information into Corvu v.5. We a currently using Progress9. I hope this is enough information but if you need more please let me know.

Thanks

 

[Lee Curzon]

I don't know about the SYS tables, but you can get the info from the VSTs in the following way (it assumes you are connected to the correct database, if not you need to find _file in a similar way from _db):

 FIND _File WHERE _File-Name = <table name>.
 FIND _Index WHERE RECID(_Index) = _File._Prime-Index.
 DISPLAY _Index._Index-Name.

I don't know SQL to write the equivalent query, but hopefully you can work it out from the above.

You can also access this information and more through Progress > Data Administration (including Schema Dumps and SQL Exports), if you have access to that.

HTH

 

[Lee Curzon]

Ok, I've just realised this isn't enough, because you also need the components of the index. You can get this from the _Field table via _Index-Field (I think). I don't have time to work it out now, but will get back to you later if no-one else does.

Please let us know if you sort it, and post your solution.

Lee

 

[Casper]

Maybe something like:

FOR EACH _file /* WHERE _file._file-name = 'customer' */,
    FIRST _index WHERE RECID(_index) = _File._Prime-index:
    DISP _File._file-name.
      FOR  EACH _index-field OF _index,
           FIRST _field OF _index-field :
        DISP _index._index-name _Field._field-name.
      END.
END.

wrote it with 2 for eaches to display the values a bit better without using forms and stuff....

Casper.

 

[Lee Curzon]

To help you translate to SQL, you need to be aware that:

FOR EACH iterates through a set of records (determined by the WHERE clause).

FIND (and FIRST) returns a single record (again determined by the WHERE clause), no iteration.

OF relates the second Table to the first by means of the (undeclared) Foreign key.

So you will need to (bearing in mind I'm speaking as a SQL numskull) declare a cursor based on a SELECT...WHERE similar to above, and traverse the results from that.

I guess there may be a complicated SELECT statement which will imitate Caspers example, I don't know, but please post your solution anyway.


TIA

Lee

ps. The best solution may be to use Data Admin, if you have access, as various tools are available.

 

[Casper]

Well,

after messing around with jdbc i found the following.

SELECT IDXNAME,COLNAME FROM "SYSINDEXES" where "SYSINDEXES"."TBL" = 'customer' AND "SYSINDEXES"."IDXTYPE" = 'U';

This returns the unique index for table customer. (to be exactly, this returns 1 record for each index field of the table...).
Unfortunately In de sysindexes table isn't a field called "isprimary"... otherwise this would be a good example. :awink:

anyway. This kinda works....

Casper.

 

[Lee Curzon]

If you have jdbc, there is apparently a getprimarykeys java method...

 

[Lee Curzon]

I want to delve into the SYS tables because I don't know anything about them, but that damn SQL explorer tool hates me.

I cannot get the thing to connect.

You would think the SYS tables would be properly documented somewhere, but I can find next to nothing apart from the stuff in the SQL reference, which is basically just a schema dump.

 

[Casper]

Hi Lee,

What error do you get when trying to connect?
True, sql documentation on systables isn't very good. Fot the example I gave, i needed to "guess" the field-name of IDXTYPE as well. SYSINDEXES has 20 fields only 16 are documented in the sql92 guide....

Casper.

 

[Lee Curzon]

If you have time, and going back to original post, what info does cnstrname field have in SYS_KEYCOL_USAGE?

[Edit] Forget it. I reckon you're looking in the right place. What values does IDXSEQ/IDXTYPE have (apart from 'U')?

IDXTYPE is a two character field, so I'm thinking maybe 'PU' type of thing.

 

[Casper]

I haven't got a clue. When looking with sqlexplorer I can't see any data for this table. In other words, the table is empty....

Looking futher in sql92 guide the field also exist is sys_chk_constr.

sys_chk_constr:

contains one row for each check constraint specified on a user table. The chkclause column contains the content of the check clausule.

So cnstrname is probably: constraint name. (unique, not null etc... ?)

Casper.

 

[Lee Curzon]

It's interesting, and I'm sure the info is there somewhere, but I've just realised its Saturday night, so I'm off.

TTFN

 

[Casper]

lol.....

Forget my previous post as well then

In the databse I'm looking in IDXTYPE has the values "U" and "D".

Googling gave the folowing on sql (dunno of this applies here as well, but sounds reasonable to me):
U : Unique
D: yes (duplicate values are allowed)
V: No (duplicate NULL values are allowed)

Idxseq seems to give the number of index fields of the index starting from 0.

e.g. if an index contains 4 fields then you have four rows in SYSINDEXES starting with IDXSEQ 0 (followed bij 1,2 and 3).

Casper.

 

[Casper]

Yeah,

you're right. Time for :drink:

 

[pubb]

I'm new to Progress, but I think this is correct:

select SYSPROGRESS.SYSTABLES_FULL.TBL, pub."_index"."_index-Name"
from pub."_index", SYSPROGRESS.SYSTABLES_FULL
WHERE SYSPROGRESS.SYSTABLES_FULL."PRIME_INDEX" = pub."_index".rowid
AND SYSPROGRESS.SYSTABLES_FULL.TBLTYPE = 'T'