Database query
This feature helps administrator accessing database data, easily and quickly without thirdparty database software client.
The results of the queries can be exported to SQL or CSV file formats.
Allowed types:
| Type | Description | List | Export |
|---|---|---|---|
|
JDBC |
SQL queries supported by the configured database. |
When choosen is shown a list of available tables. |
Yes. |
|
Hibernate |
Neutral database queries based on hibernate queries format. |
No. | |
|
Metadata |
Application specific format only for doing queries over OKM_DB_METADATA_VALUE. For more information see Creating your own database tables. |
When choosen is shown a list of available metadata tables. |
Yes. |
|
|||
Execute JDBC query
- On bottom right choose type JDBC.
- Write some valid SQL.
- Click on Execute button.
- Optionaly the results can be exported to SQL or CSV
- Click on
Export to sql icon.
- Click on
Export to csv icon.
- Click on
Example:
// nodeBase = documents + folders + mails + records
SELECT count(*) from OKM_NODE_BASE;
SELECT count(*) from OKM_NODE_DOCUMENT;
SELECT count(*) from OKM_NODE_FOLDER;
SELECT count(*) from OKM_NODE_MAIL;
SELECT count(*) from OKM_NODE_RECORD;
// select first 10 records
SELECT * from OKM_NODE_BASE LIMIT 10;
SELECT * from OKM_NODE_DOCUMENT LIMIT 10;
SELECT * from OKM_NODE_DOCUMENT_VERSION LIMIT 10;
SELECT * from OKM_NODE_FOLDER LIMIT 10;
SELECT * from OKM_NODE_MAIL LIMIT 10;
SELECT * from OKM_NODE_RECORD LIMIT 10;Execute Hibernate query
- On bottom right choose type Hibernate.
- Write some valid HQL.
- Optionally you can check the "Show SQL" option.
- Click on Execute button.
When "Show SQL" option is checked is shown in results the real sql executed.
Example:
select count(*) from NodeBase;
select count(*) from NodeFolder;Execute Metadata query
- On bottom right choose type Hibernate.
- Write some valid metadata query.
- Click on Execute button.
- Optionaly the results can be exported to SQL or CSV
- Click on
- Click on
- Click on
Example:
SELECT|countryMetadata country sample available at Creating your own database tables.
Allowed sentences
| Type | Format | Example |
|---|---|---|
|
SELECT
|
SELECT|TABLE SELECT|TABLE|FILTER |
SELECT|country SELECT|country|$country_name='Canada' SELECT|country|col01='Canada' |
|
UPDATE |
UPDATE|TABLE|COLUMN UPDATE|TABLE|COLUMN|FILTER |
UPDATE|country|$country_name='France' UPDATE|country|$country_name='France' | $country_id ='001' |
|
DELETE |
DELETE|TABLE DELETE|TABLE|FILTER |
DELETE|country DELETE|country|$country_id ='001' |
|
SENTENCE |
SENTENCE|TABLE1,..,TABLEn|QUERY |
SENTENCE|country|select $country_id, $country_name from DatabaseMetadataValue dmv where dmv.table='country' order by $country_name |
|
||
Execute queries from sql file
- On bottom right choose a valid sql file.
- Click on Import SQL script button.
