We currently support Microsoft SQL and Oracle databases natively, but you can also use ODBC to fetch data from any data source that supports it.
To use a Database Connector, add one to the application and complete the Connection Editor. Continue by adding the queries or stored procedures that are needed by your application. Finally, use the connector on controls or call them in scripts.
Add a Database Connector
Connection Editor Properties
Connection TypeThis can be one of 3 values: SQL Server, Oracle or ODBC.
Server Name/Dsn Name
A SQL or Oracle server name This is typically a <network machine name>/<SQL instance name> combination.
For ODBC use the Dsn name you specified when setting up the ODBC Datasource. You'll need a User DSN to connect via the Designer, and a Service DSN on the server to connect through the Generated App. Make sure you add a "System DSN" here, as a "User DSN" does not work. If you are working on a 64-bit environment, set up a 64-bit ODBC Data Source.
SQL Server/Oracle/ODBC AuthenticationSQL Server, Oracle and all ODBC databases can be set up to be accessible by way of a username and password combination for the database server.
Use Windows Authentication (SQL Server only)SQL Server can be set up to be accessible by using Windows credentials.
Select or enter a database name (SQL and ODBC)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.
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. Instead, select the desired Stored Procedure to call from the Stored Procedure dropdown.
Tables & Views PaneThis 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.
SQL Query EditorThis 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.
Fields & Parameters PaneThis 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.