Database Connector

currently support Microsoft SQL and Oracle databases natively, but you can also use ODBC to fetch data from any data source that supports it.

Do this:

1. Add the Database Connector to your application and complete the Connection Editor settings.

2. Add the queries or stored procedures that are needed by your application to the connector.

3. Use the connector on controls or call them in Scripts.

---

Add a Database Connector and a SQL query

---

Connection Editor Properties

To add a Connector, click on the Connector button on the toolbar, then select the Database option.

Provide the relevant details for making a connection to your database.

Connection Type Select from this list: SQL Server, Oracle or ODBC.

When using SQL Server:

• Server Name A SQL Server name. This is typically a <network machine name>/<SQL instance name> combination.

• Authentication The username and password combination to access the database server.

• Select or enter a database name The database you wish to access when using the connection. If the database server is accessible from the designer machine, then you can use the drop-down to find the database.

When using Oracle:

• Server Name An Oracle server name. This is typically a <network machine name>/<SQL instance name> combination.

• Authentication The username and password combination to access the database server.

When using ODBC:

• DSN Name Use the System DSN name you specified when setting up your ODBC Datasource. For further assistance, go here for details on how to create a DSN.

  An alternative to using DSN is to use a connection string that contains the ODBC driver details, as set out below.

• Authentication The username and password combination to access the database server.

• Select or enter a database name The database you wish to access when using the connection. If the database server is accessible from the designer machine, then you can use the drop-down to find the database.

Using an ODBC driver name to make a connection

1. Add a Database Connector by clicking the Connector button in the toolbar.

2. Close the Database Connection Editor without adding any connection details.

3. Go to the Properties panel of your Database Connector to add the relevant connection details:

• Set the Connection Type to ODBC (A)

• Type in your Connection String (B) Example:

  Driver={MySQL ODBC 8.0 ANSI Driver};Server=localhost;Port=3306;Database=sakila;User=xxxxx;Password=xxxxx;

For more help on connection strings go to connectionstrings.com

---

Query Editor

To add a query or stored procedure, click on the relevant Connector's + sign:

A query placeholder is created. Click on the query name to open the Query Editor:

The query editor canvas is divided into three sections. From left to right we have the Tables & Views, the SQL Query Editor and the Fields & Parameters sections respectively.

Stored procedures cannot be edited via the SQL Query Editor, but can be called by selecting he desired Stored Procedure from the Stored Procedure dropdown.

1. Tables & Views Pane

   This pane shows a list of names of the tables, views and columns contained within the database. Right-click on a table name to generate templates of common Sql commands.

2. SQL Query Editor

   This editor provides syntax highlighting for SQL text. Prepend SQL parameter names with an '@@'. A '?' can be used to denote ODBC parameters. Use the "Fetch Fields and Parameters" button to parse the SQL text for any SQL fields and parameters which will then be displayed in the Fields & Parameters pane. The ability to test a query is also available by clicking on the "Test Query" button.

3. Fields & Parameters Pane

   This pane displays the list of fields and parameters parsed from the SQL text in the SQL Query Editor. The fields and parameters are available to be used in page controls and Scripts where their values can be individually read or set.