1. Home
  2. OpenKM 6.3 - CE
  3. Administration guide
  4. Database query
  1.  

Database query

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

Allowed types:

TypeDescriptionListExport

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.
  • Is allowed writing more than one query.
  • Each query must be written on a single line, break lines are not allowed.
  • Execute will process all written queries in same order has been written, top to bottom. Queries are executed as a FIFO ( first in first out ).
  • In case some query fails, that will not stop the execution of the others.

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

Example:

SELECT|country

Metadata country sample available at Creating your own database tables.

Allowed sentences

TypeFormatExample

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
  • Character $ is used to indicate that the word is a column name.

Execute queries from sql file

  • On bottom right choose a valid sql file.
  • Click on Import SQL script button.