 |
|
|
 | System Language and Charset |  | Database | Database Compression |  |
Each database has several system tables, that contain object definitions, table definitions, user list and another important items.
It's not recommended to work with these tables directly. You can use the interactive developer interface or the appropriate API functions or SQL statements for creating, altering and deleting objects instead. The system tables' structure may change in future versions of 602SQL.
The system tables are:
| OBJTAB | table of objects of all applications (queries, procedures, roles etc.) - accessible for reading and writing |
| TABTAB | table of tables of all applications - only for reading |
| USERTAB | table of users and groups - only for reading |
An overview of some columns in the system tables:
| Column | OBJTAB | TABTAB | USERTAB | Type | Meaning |
| OBJ_NAME | yes | -- | -- | CHAR(31) | object name |
| TAB_NAME | -- | yes | -- | CHAR(31) | table name |
| LOGNAME | -- | -- | yes | CHAR(31) | user name or group name |
| CATEGORY | yes | yes | yes | CHAR | object category |
| APL_UUID | yes | yes | -- | BINARY(12) | application identification |
| USR_UUID | -- | -- | yes | BINARY(12) | user (group) identification |
| DEFIN | yes | yes | -- | BLOB | text definition of an object |
| FOLDER_NAME | yes | yes | -- | CHAR(31) | folder name on the control panel |
| LAST_MODIF | yes | yes | -- | TIMESTAMP | time and date of creation or last change |
Example: get the names of database users
Solution: ask a query
SELECT logname FROM Usertab WHERE (Usertab.category=Chr(CATEG_USER))
Example: select all objects from the "APPLICATION1"
Solution: ask these two queries
SELECT T2.obj_name,Ord(T2.category) FROM Objtab T1, Objtab T2 WHERE T1.apl_uuid=T2.apl_uuid AND (T1.obj_name="APPLICATION1" AND Ord(T1.category)=CATEG_APPL)
and
SELECT Tabtab.tab_name FROM Objtab, Tabtab WHERE Objtab. apl_uuid =Tabtab. apl_uuid AND Objtab.obj_name="APPLICATION1"
These constants identify the object categories in 602SQL. All of them are Integer type:
| application objects category | ||
| CATEG_TABLE | table | 0 |
| CATEG_CURSOR | query | 3 |
| CATEG_PGMSRC | data transfer description | 4 |
| CATEG_APPL | application | 7 |
| CATEG_ROLE | application role | 10 |
| CATEG_DRAWING | diagram | 13 |
| CATEG_PROC | stored procedure | 16 |
| CATEG_TRIGGER | trigger | 17 |
| CATEG_FOLDER | folder | 19 |
| CATEG_SEQ | sequence | 20 |
| CATEG_INFO | fulltext | 21 |
| CATEG_DOMAIN | domain | 22 |
| CATEG_STSH | stylesheet | 23 |
| CATEG_XMLFORM | XML form | 24 |
| database objects category | ||
| CATEG_USER | user | 1 |
| CATEG_GROUP | usergroup | 9 |
BEWARE! The CATEGORY column in the system tables is Char type, therefore don't forget to make the appropriate conversion by the Ord or Chr functions when comparing.
| System Language and Charset | | Database | Database Compression | |