Database query
This feature helps administrator accessing database data, easily and quickly without thirdparty database software client.
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 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;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.
