DataBase
 | DataBase | This node provides bi-directional communication with ODBC, MSSQL, OleDB, Oracle or MySQL databases within a scene. |
 | Query | This node provides a database query command. |
| NonQuery | This node provides a database non-query command. |
| Row | This node provides a database row access. |
| ColConcat | This node provides a database column concatenation functionality. |
| Scalar | This node provides a scaler database command. |
The Database functionality inside Ventuz is based on the ADO.NET (System.Data .NET framework and supports ODBC, MS-SQL, OleDB, Oracle and MySQL. Setting up and querying a database is a complex topic and doing a comprehensive introduction is beyond the scope of this documentation. It is therefore assumed that the reader has sufficient knowledge of databases that this documentation can focus on the Ventuz specific aspects.
It is currently not possible to directly access an sqlite file. For more information what specific setups are supported, please refer to the MSDN: System.Data Framework documentation.
Database
The Database node is the central element of interacting with a database. In addition to the database Type, a ConnectionString has to be specified. A connection string specifies all information that is required to access a database, including the URI of the database, required passwords and user accounts, and much more. To construct an appropriate connection string, consult the MSDN documentation:
- MSDN: ODBC connection string
- MSDN: OleDB connection string
- MSDN: Oracle connection string
- MSDN: SQL connection string
As an example, to connect to an Access database file located at C:\Temp\database.db the connection string might look like this:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\database.db;User Id=admin;Password=;
Please note that many ODBC/Ole Providers are not available for Windows 64bit operating system! Since Ventuz is running as a 64 bit process it is not able to load the 32 bit version in WoW mode!
Query
To query a database for information, add a Query node to the scene and bind its Database input property to a Database node. To edit the query string itself, press the Edit SQL button at the bottom of the Property Editor.
Ventuz currently uses the Script Editor UI for specifying the query string. Since each database language has its particularities, the Script Editor may report errors for some language specific constructions which are perfectly valid. To verify a query was correctly compiled, save & close the query and check the Message View for errors.
Besides the actual query statement itself, a number of input and output properties can be created. Input properties can be used to fill parameters in the query statements, for example an input property with the name @street would be filled into the WHERE-clause.
SELECT description from Club WHERE address=@street
The Output Properties can be used for getting output values. If the name of an output property exactly matches the name of one of the column names specified in the query, its value will be filled with the value of that column in the row specified with Row.
The @-syntax for parameters are MS-SQL specific and may be different in other SQL dialects. Please check the documentation of your SQL server.
The MaxRows property defines how many row are stored into the result DataSet. If possible try to avoid SQL queries that return thousand of rows which are limited by MaxRows. This could impact the query performance drastically. The use of PAGE or ROWCOUNT techniques may help here - but are not supported by all SQL dialects.
Row
If a Query returns more than one row, one needs a way to iterate over the different rows. This is done with the Row Node which can be bound to a Query Node. Same as with the Query, the Custom Model can be used to add output properties that have the same name as the table columns. By changing the Row input property, a specific row of the result data set can be selected.
Column Concatenation
As an alternative to the Row node, a Column Concatenation can be used to produce one joined string composed of the entries of the individual rows. The node accepts both a Column name and a Separator string which is inserted between two consecutive entries. The result string could be used to create a dynamic SQL statement again (see Convert to Text node).
Scalar Operation
The Scalar Node provides the ability to execute aggregate functions that deliver a numeric value such as COUNT.
Non Query
Non-Query is the .NET Framework term for database operations that do not retrieve or update table entries. It can be used to query information about or change the structure of tables among other things.