storing index or primary key combination in a character variable

S

shafee212

Member
How can we store the primary key combination of a table in a variable .
for eg if pu key of a table x is combination of fields a , b and c

Then variable c = "a,b,c" .

Or in short , how can we retirive pu key of a table .
 
Virtual system Table. If you look into the data dictionary Set show hidden table menu on, then u can see a set of table created by Progress to store all the schema details. U can see the table names starts with _ . Eg. _index, _Table etc... You may not get the required info from a single table, u have to spent some time to find the relation with other tables and extract.
Regars
Philip P oommen
 
I believe the code below does what you are after:
Code: 
/* Some variables we need */
DEFINE VARIABLE hBuffer AS HANDLE    NO-UNDO.
DEFINE VARIABLE cData   AS CHARACTER NO-UNDO.
DEFINE VARIABLE cKey    AS CHARACTER NO-UNDO.
 
/* Use these to make this test work... fill in "the blanks" */
&SCOPED-DEFINE useDBName <your ldbname here>.
&SCOPED-DEFINE tableName <your table name here>
&SCOPED-DEFINE separator ,
 
/* Make sure we have a record to play with data as well */
FIND FIRST {&useDBName}{&tableName} NO-LOCK NO-ERROR.
 
/* Get the handle to the database buffer */
hBuffer = BUFFER {&tableName}:HANDLE.
 
/* Go get the fields that make the primary index of the table by using 
   the "_" tables -- better make sure we use no-lock on those */
FOR FIRST {&useDBName}_file        NO-LOCK
    WHERE {&useDBName}_file._file-name EQ "{&tableName}":U,
    FIRST {&useDBName}_index       NO-LOCK
    WHERE RECID(_index) EQ _file._prime-index,
    EACH  {&useDBName}_index-field NO-LOCK OF _index,
    FIRST {&useDBName}_field       NO-LOCK OF _index-field:
 
    ASSIGN cData = cData
                 + (IF cData NE "":U THEN "{&separator}":U ELSE "":U)
                 + TRIM(hBuffer:BUFFER-FIELD(_field._field-name):STRING-VALUE)
           cKey  = cKey
                 + (IF cKey NE "":U THEN "{&separator}":U ELSE "":U)
                 + _field._field-name.
 
END. /* for first */
 
MESSAGE "KEY"  cKey  SKIP(1)
        "DATA" cData 
        VIEW-AS ALERT-BOX INFORMATION.

HTH

Paul
 
Or:

Code: 
[FONT=Arial][SIZE=2][FONT=Arial]DEFINE VARIABLE ctable AS CHARACTER NO-UNDO.
DEFINE VARIABLE hbuffer AS HANDLE NO-UNDO.
DEFINE VARIABLE hqueryindex AS HANDLE NO-UNDO.
DEFINE VARIABLE hbuffile AS HANDLE NO-UNDO.
DEFINE VARIABLE hbufindex AS HANDLE NO-UNDO.
DEFINE VARIABLE icount AS INTEGER NO-UNDO.
DEFINE VARIABLE cindex AS CHARACTER NO-UNDO.
DEFINE VARIABLE cprimary AS CHARACTER NO-UNDO.
DEFINE VARIABLE icount2 AS INTEGER NO-UNDO.[/FONT][/SIZE][/FONT]
[FONT=Arial][SIZE=2][FONT=Arial]ctable = "<tablename>".[/FONT][/SIZE][/FONT]
[FONT=Arial][SIZE=2][FONT=Arial]CREATE BUFFER hbuffer FOR TABLE ctable.[/FONT][/SIZE][/FONT]
[FONT=Arial][SIZE=2][FONT=Arial]CREATE QUERY hqueryindex.
CREATE BUFFER hbuffile FOR TABLE "_file".
CREATE BUFFER hbufindex FOR TABLE "_index".[/FONT][/SIZE][/FONT]
[FONT=Arial][SIZE=2][FONT=Arial]hqueryindex:SET-BUFFERS(hbuffile,hbufindex).
hqueryindex:QUERY-PREPARE(SUBSTITUTE("for each _file where _file-name = &1, each _index of _file",QUOTER(ctable))).
hqueryindex:QUERY-OPEN.
hqueryindex:GET-FIRST(NO-LOCK).
DO WHILE NOT hqueryindex:QUERY-OFF-END: 
    icount = icount + 1.
    IF cindex = "" THEN cindex = hbuffer:INDEX-INFORMATION(icount).
    ELSE cindex = cindex + "|" + hbuffer:INDEX-INFORMATION(icount). 
    hqueryindex:GET-NEXT(NO-LOCK).
END.
hqueryindex:QUERY-CLOSE.
DELETE OBJECT hqueryindex.[/FONT][/SIZE][/FONT]
[FONT=Arial][SIZE=2][FONT=Arial]DO icount = 1 TO NUM-ENTRIES(cindex,"|"):
    IF ENTRY(2,ENTRY(icount,cindex,"|")) = "1" AND ENTRY(3,ENTRY(icount,cindex,"|")) = "1" THEN 
    DO icount2 = 1 TO NUM-ENTRIES(ENTRY(icount,cindex,"|"),","):
        IF icount2 > 4 AND LOOKUP(ENTRY(icount2,ENTRY(icount,cindex,"|"),","),"0,1") = 0 THEN
        DO: 
            IF cprimary = "" THEN cprimary = ENTRY(icount2,ENTRY(icount,cindex,"|"),",").
            ELSE cprimary = cprimary + "," + ENTRY(icount2,ENTRY(icount,cindex,"|"),","). 
        END.
    END.
END.
[/FONT][/SIZE][/FONT]
[FONT=Arial][SIZE=2][FONT=Arial][/FONT][/SIZE][/FONT]
 
You must log in or register to reply here.
Back
Top