06.04 - Commands

'execute' 'query.sql' on 'database'

What: This command executes a SQL query represented by a query.sql resource on the specified database target.
EXECUTE execute WITH {query<Res:query.sql>} ON {<Tar:database>} AS {result<Res:result.sql>}
Input:
  • query<Res:query.sql>: The name (in the context) of the resource which references a SQL query (Resource of category query.sql).
  • <Tar:database>: The name (in the context) of the database to use (Target of category database).
Output:
  • result<Res:result.sql>: The name of the resource which contains the result of the SQL query(Resource of category result.sql).

Example:

LOAD path/to/my_query.sql AS query.file
CONVERT query.file TO query.sql (query) AS query1

EXECUTE execute WITH query1 ON mydatabase-db AS my_query_result


'execute' 'query.sql' on 'database' via 'parameter.indexed.sql'

What: This command executes a SQL query represented by a query.sql resource on the specified database target via indexed parameters.
EXECUTE execute WITH {query<Res:query.sql>} ON {<Tar:database>} AS {result<Res:result.sql>} USING {config<Res:parameter.indexed.sql>}
Input:
  • query<Res:query.sql>: The name (in the context) of the resource which references a SQL query (Resource of category query.sql).
  • <Tar:database>: The name (in the context) of the database to use (Target of category database).
  • config<Res:parameter.indexed.sql> : The name of the resource which contains indexed parameters for the SQL query. (Resource of category parameter.indexed.sql).
Output:
  • result<Res:result.sql>: The name of the resource which contains the result of the SQL query(Resource of category result.sql).

Example:

LOAD path/to/my_query.sql AS query.file
CONVERT query.file TO query.sql (query) AS query1

LOAD path/to/parameter-indexed_value.properties AS value.file
CONVERT value.file TO parameter.indexed.sql (from.text) AS value.properties

EXECUTE execute WITH query1 ON mydatabase-db AS my_query_result USING value.properties


'execute' 'query.sql' on 'database' via 'parameter.named.sql'

What: This command executes a SQL query represented by a query.sql resource on the specified database target via named parameters.
EXECUTE execute WITH {query<Res:query.sql>} ON {<Tar:database>} AS {result<Res:result.sql>} USING {config<Res:parameter.named.sql>}
Input:
  • query<Res:query.sql>: The name (in the context) of the resource which references a SQL query (Resource of category query.sql).
  • <Tar:database>: The name (in the context) of the database to use (Target of category database).
  • config<Res:parameter.named.sql>: The name of the resource which contains named parameters for the SQL query. (Resource of category parameter.named.sql).
Output:
  • result<Res:result.sql>: The name of the resource which contains the result of the SQL query (Resource of category result.sql).

Example:

LOAD path/to/my_query.sql AS query.file
CONVERT query.file TO query.sql (query) AS query1

LOAD path/to/parameter-named_value.properties AS value.file
CONVERT value.file TO parameter.named.sql (from.text) AS value.properties

EXECUTE execute WITH query1 ON mydatabase-db AS my_query_result USING value.properties

'execute' 'script.sql' on 'database'

What: This command executes a SQL script represented by a script.sql resource on the specified database target.
EXECUTE execute WITH {script<Res:script.sql>} ON {<Tar:database>} AS $() [ USING $(keep.separator: <keepSeparator>)]
Input:
  • script<Res:script.sql>: The name (in the context) of the resource which references a SQL script (Resource of category script.sql).
  • <Tar:database>: The name (in the context) of the database on which the SQL script should be used (Target of type database).
  • <keepSeparator>: Indicate to the command to keep or remove the separator (";") at the end of each SQL query of the script. This parameter can two value: "true" or "false". By default this parameter is set to "true"

Example 1:

LOAD path/to/my_script.sql AS script.file
CONVERT script.file TO script.sql (script) AS script1

EXECUTE execute WITH script1 ON mydatabase-db AS $()

Example 2:

EXECUTE execute WITH script1 ON mydatabase-db AS $() USING $(keep.separator:false)


'get.all' on 'database'

What: This command permits to create a DbUnit dataset from a specific database.
EXECUTE get.all WITH $() ON {<Tar:database>} AS {result<Res:dataset.dbunit>} [USING [{<Res:conf.dbunit>}],[{<Res:conf.dbunit.ppk>}] ]
Input:
  • <Tar:database>: The name (in the context) of the database to use (Target of category database).
  • <Res:conf.dbunit>: This resource contains DbUnit configuration properties.
  • <Res:conf.dbunit.ppk>: The name of the resource which references a configuration file to define the pseudo primary keys.
Remarks:
  1. If for a table a primary key and a pseudo primary key are defined, so the pseudo primary key override the primary key.
  2. If for a table a pseudo primary key is defined with one or more non existents cololumns so the command fails.

Output:
  • result<Res:dataset.dbunit>: The name of the resource which contains the DbUnit dataset of all the database.

Example 1:

EXECUTE get.all WITH $() ON myDatabase-db AS myDataset

Example 2:

LOAD path/to/dbunit-conf.properties AS conf.file
CONVERT conf.file TO conf.dbunit (structured) AS conf.dbu

EXECUTE get.all WITH $() ON my_Database-db USING conf.dbu AS my_dataset


'insert' 'dataset.dbunit' on 'database'

What: This command insert a DbUnit dataset on the specified database target.
EXECUTE insert WITH {dataset<Res:dataset.dbunit>} ON {<Tar:database>} AS $() [USING [$(operation : <type>)],[{<Res:conf.dbunit>}],[{<Res:conf.dbunit.ppk>}]]
Input:
  • dataset<Res:dataset.dbunit>: The name of the resource which references a DbUnit dataset (Resource of type dataset.dbunit)
  • <Tar:database>: The name (in the context) of the database to use (Target of category database).
  • <type>: 4 values are possible for this parameter :
    • 'INSERT': for a simple operation of insert. This operation assumes that table data does not exist in the target database and fails if this is not the case.
    • 'CLEAN_INSERT': a 'delete all' is realised before the 'insert' operation
    • 'UPDATE': this operation assumes that table data already exists in the target database and fails if this is not the case.
    • 'REFRESH': data of existing rows are updated and non-existing row get inserted. Any rows which exist in the database but not in dataset stay unaffected.
Remark: If '$(operation : <type>' is not defined, property is by default 'CLEAN_INSERT'
  • <Res:conf.dbunit>: This resource contains DbUnit configuration properties
  • <Res:conf.dbunit.ppk>: The name of the resource which references a configuration file to define the pseudo primary keys
Remarks:
  1. If for a table a primary key and a pseudo primary key are defined, so the pseudo primary key override the primary key.
  2. If for a table a pseudo primary key is defined with one or more non existents cololumns so the command fails.

Example:

LOAD path/to/dataset_to_insert.xml AS dataset_file
CONVERT dataset_file TO xml (structured) AS dataset_xml
CONVERT dataset_xml TO dataset.dbunit (dataset) AS dataset_dbu

LOAD path/to/dbunit-conf.properties AS conf_file
CONVERT conf_file TO conf.dbunit (structured) AS conf_dbu

EXECUTE insert WITH dataset_dbu ON my_database-db USING conf_dbu,$( operation : INSERT ) AS $()

'delete' 'dataset.dbunit' on 'database'

What: This command delete a DbUnit Dataset on the specified database target.
EXECUTE delete WITH {dataset<Res:dataset.dbunit>} ON {<Tar:database>} AS $() [USING [$(operation : <type>)],[{<Res:conf.dbunit>}],[{<Res:conf.dbunit.ppk>}]]
Input:
  • dataset<Res:dataset.dbunit>: The name of the resource which references a DbUnit dataset (Resource of category dataset.dbunit)
  • <Tar:database>: The name (in the context) of the database to use (Target of category database).
  • <type>: 2 values are possible for this parameter :
    • 'DELETE': This operation deletes only the dataset contents from the database. This operation does not delete the entire table contents but only data that are present in the dataset.
    • 'DELETE_ALL': Deletes all rows of tables present in the specified dataset. If the dataset does not contains a particular table, but that table exists in the database, the database table is not affected. Table are truncated in reverse sequence.
Remark: If '$(operation : <type>' is not defined, property is by default 'DELETE_ALL'
  • <Res:conf.dbunit>: This resource contains DbUnit configuration properties
  • <Res:conf.dbunit.ppk>: The name of the resource which references a configuration file to define the pseudo primary keys
Remarks:
  1. If for a table a primary key and a pseudo primary key are defined, so the pseudo primary key override the primary key.
  2. If for a table a pseudo primary key is defined with one or more non existents cololumns so the command fails.

Example:

LOAD path/to/dataset_to_insert.xml AS dataset_file
CONVERT dataset_file TO xml (structured) AS dataset_xml
CONVERT dataset_xml TO dataset.dbunit (dataset) AS dataset_dbu

LOAD path/to/dbunit-conf.properties AS conf_file
CONVERT conf_file TO conf.dbunit (structured) AS conf_dbu

EXECUTE delete WITH dataset_dbu ON my_database-db USING conf_dbu,$( operation : DELETE ) AS $()