...

<query name="getRoleInMsg" autocreate="true">
	<retrieve>
		<statement> SELECT role FROM roles WHERE tenant="tn14" </statement>
		<cache>
			<timeToIdle>300</timeToIdle>
		</cache>
	</retrieve>
</query>

The timeToIdle parameter specifies the number of seconds that must expire before a fresh object is retrieved from the database to be cached again. In the example, this is set for 300 seconds. If you set this value to 0, caching will never expire.

• Negative values for cache are not allowed.
• If you do not include the cache element, then your retrieve statements are not configured for caching. Results will be retrieved from the database every time the query runs.

SQL Statements

You define the scripts that will work with your database in the configuration file. For example, the sample configuration file defines a query called customers. That query assumes that a table such as the one below exists in your Data Source.

...

...

Empty resultsets

If the results of a query are empty, the Database Connector returns an empty HTTP response. This is because the attribute emptyStringForEmptyResultSet="true" is configured by default. You may notice the error message below in the debug console when testing your forms if no rows are returned by a query.

Image Added

SQL Statements

You define the scripts that will work with your database in the configuration file. For example, the sample configuration file defines a query called customers. That query assumes that a table such as the one below exists in your Data Source.

CREATE TABLE customers (
customerId INT,
firstName  VARCHAR(50),
lastName   VARCHAR(50)
)

...

<query name="customers">          
   <retrieve>
   <!-- maps to the http GET method --> 
      <statement> 
        SELECT * FROM customers WHERE customerId='{customerId}'
      </statement>                                     
   </retrieve>
   <!-- Omitted other statements -->       
</query>

You can use any valid SQL statement in the configuration.    

Note the string {customerId}. The database connector SQL statements are actually templates that are resolved at run time using values passed in the http request. In the example above, if there is a parameter in the http GET request that hits the connector with customerId=1234 than the statement would return the record for customer 1234.

Conditional NULL in MySQL Insert Statement

If you are using the MySQL database, there is a special syntax to insert a conditional null in an insert statement. For Example, let's say your form/flow has an optional date field in a form that is designed to insert a row into the database. The insert will fail because the database connector inserts an empty string where MySQL expects NULL.

Use this Insert statement to resolve the issue:

INSERT into log (VisitorName,optional_date)  VALUES  </retrieve>
   <!-- Omitted other statements -->       
</query>

You can use any valid SQL statement in the configuration.    

...

('{VisitorName}', (CASE {optional_date} WHEN '' THEN NULL ELSE {optional_date} END))

In this example, {optional_date} refers to a column of type date and is optional. When its empty in your form, NULL is inserted fulfilling the MySQL requirement otherwise the value in the field will be inserted.

Stored Procedures

You can also execute stored procedures via the database connector. Here is an example of a mySql stored procedure.

...