...

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 Support for NULL or Blank Values

By default, optional/empty form fields will send a null from the form field to the Db Connector and return an xml element or json property for an optional/empty form field when the database column contains a null. This is controlled by the emit Null column feature.

if you prefer optional/empty form fields to send/return an empty string, you can disable this behavior for the whole db connector or at the queryset/query level.

There are two ways to disable the emit null feature:

1. Add the dbconnector.emitNullColumns=false property to the dbconnector properties file for your installation.

2. OR add the emitNullColumns="false" attribute to the configuration.xml file for your installation:
   
   For example:

• To disable it at the instance level add the attribute at the top of configuration.xml

  Code Block
  <dbconnector version="2.5" emitNullColumns="false">

• To disable it at the queryset level, add the attribute to the respective queryset in configuration.xml

  Code Block
  queryset name="BIRT" emitNullColumns="false">

• To disable it at the query level, add the attribute to the respective query in configuration.xml

  Code Block
  <query name="lkadgroupmembers2" emitNullColumns="false">

If you choose to turn off the emit NULL columns feature, there is a way to insert a conditional null in an insert statement if you are using a MySQL database. Refer to the Conditional NULL in MySQL Insert Statement topic for the details.

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 ('{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.

Support for NULL or Blank Values

By default, optional/empty form fields will send a null from the form field to the Db Connector and return an xml element or json property for an optional/empty form field when the database column contains a null. This is controlled by the emit null feature.

if you prefer optional/empty form fields to send/return an empty string, you can disable this behavior for the whole db connector or at the queryset/query level.

Disabling the emit null feature can

...

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.

...