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:

• 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 $()