Database query

This feature helps administrator accessing database data, easily and quickly without thirdparty database software client.

Tip

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.

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.

Note

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  Export to sql icon.
  • Click on  Export to csv icon.

Example:

SELECT|country

Note

Metadata 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.