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 take one of two values: "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:
If for a table a primary key and a pseudo primary key are defined, so the pseudo primary key override the primary key.
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:
If for a table a primary key and a pseudo primary key are defined, so the pseudo primary key override the primary key.
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:
If for a table a primary key and a pseudo primary key are defined, so the pseudo primary key override the primary key.
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 $()