SetConnect

Boka demo Om oss

QueryDatabase

Database::QueryDatabase
Description

Execute one or more queries on the database referenced by "handle". The results of the query will be put in a recordset which is also referenced by "handle".

When the "Save Result to Recordset" option is enabled the result of the (last) query will also be stored in the configured recordset (handle). Note that this must NOT be an existing pre-configured (database) handle but a different handle. Using this option it's possible to store the result of a database-query in a (new) recordset and run queries on this recordset. The tablename to use in this query is the value you specify in the "Name" textbox.

It's possible to save as many results to the same recordset as long as you use new "Name"'s for these results.

Example:
handle1: "select * from DemoTable1", Save Result to Recordset "NEW", name=myTable1
handle2: "select * from DemoTable2", Save Result to Recordset "NEW", name=myTable2
handle3: "select * from DemoTable3", Save Result to Recordset "NEW", name=myTable3

You can now run QueryDatabase with option "Query Recordset" enabled to run queries on myTable1, myTable2 and myTable3.

All queries have to be closed by the ; sign. This means you can place CrLf's inside a query to make the query more readable.

All lines starting with the - sign are considered comment.

NOTE-1: When using a "Preset Database Handle" the QueryDatabase can run realtime queries on the selected handle (database) using the "Preview" option.

NOTE-2: When you query a handle which uses the XML-driver you need to use the XQuery/XPath syntax. See https://www.w3schools.com/xpath/xpath_syntax.asp for more information. The default namespace will be added using the "ns" prefix! So you may need to use the "ns" prefix to query all default nodes.

NOTE-3: When you query a handle which uses the JSON-driver you need to use the JSONPath syntax. See https://www.w3schools.com/js/js_json_syntax.asp for more information.
Options

Handle

 Name of the handle which references the desired database and name of the handle by which the resulting recordset can be referenced.

Output Handle

 Optional handle which will contain the result of the query. If the handle does not exist it will be automatically created.

Load Queries From File

 Optional. If configured, the queries to execute will be imported from the specified file.

XML-Output Compatible

Only valid when using the SQL-Server driver. If enabled, the result is formatted to be exported to an XML file (rsOutput2File - XML). The query must also include the "FOR XML" statement.

Load Queries From File

 Only valid when using the SQL-Server driver. If enabled, the result is formatted to be exported to a JSON file (rsOutput2File - JSON). The query must also include the "FOR JSON" statement.

Ignore Runtime Errors

When checked, the error-flag will not be set when there is an error during execution of the queries.

Query

 The query to be executed.

note-1: all queries must be closed with the ; sign. This allows you to use more then one line for one query. The query will only be considered complete when the ; sign is found.

note-2: you can specify more then one query.

note-3: all lines starting with the - sign will be considered comment.

Save Result to Recordset

 When checked, the result of the (last) query will also be saved to the configured recordset and the result will be saved using "Name" as the name..

Ignore Runtime Errors

When checked, the error-flag will not be set when there is an error during execution of the queries.

Query Recordset

When checked, the queries will be run on the data inside the recordset of "hande" and not on the database referenced by "handle".
Preview
With the "Preview" option in QueryDatabase it is possible to execute queries in realtime on live databases (Preset Databases). All queries which are present in the "Query" window are available in-, and can be executed from, the Query pulldown-menu.

Documentation

Release notes