QuerySet/Query Validation

When the Database Connector loads the configuration.xml, validation for all the statements, querysets and queries in the configuration.xml file is performed. Validation results are shown when you browse the status URL and appropriate error messages are displayed in the browser.

This provides an easy way to find all syntactic or schema related issues such as:

• If a Retrieve operation or template is not properly defined in the configuration.xml
• If a query is empty or disabled
• Invalid Query statement

• Invalid credentials

• Incomplete xml element

• Database URL parameter is not present

You may also see warnings for valid queries. For example, it is recommended that the useServerPrepStmts=true to a MySQL connection url to improve statement validation. If this parameter is missing, you may see a warning message to add it.    
If the configuration.xml file is not found, the status URL will report it immediately. See the Common Problems section for some examples.

When the Database Connector is running, it is constantly looking for configuration.xml file changes. If you have to make a change to a query in your configuration.xml to fix a reported issue, you Do not have to restart after you make the correction.The connector will pick up the changes.

The image shows the the queryset/query validation and other entries that you will see in the database log file when the connector loads.

REST parameters for the test URL

It is very helpful to first test your queries by entering the query URL directly into your web browser and verify that the data resultset returned to your browser as a web page is as you expect.

Once you have the Database Connector working with your database you can test your query by copying a test URL into your browser.

The URL to the database connector has the following REST parameters:

http://<host>:<port>/database/<queryset-name>/<query-name>

Where:

• <host> - the URL for the web server on which you deployed database.war.
• <port> - port where the container is listening for http requests. This defaults to 8082 if the database connector was deployed in the  tomcat bundle or port 8081 if you are running the Database Connector in Standalone mode.
• <queryset name> - queryset name defined in the database connector configuration file.
• <query name> – query name  defined in the database connector configuration file.

Logfiles

The database connector logging output is very helpful for troubleshooting database SQL issues. This example shows the log entry generated when browsing with an incorrect query name.

2017-10-26 10:31:35.359 ERROR   12048 --- [http-nio-8082-exec-5] c.f.connectors.database.QueryResource    : Query BIRT/allcustomers not found
2017-10-26 10:31:35.372  INFO   12048 --- [http-nio-8082-exec-5] org.restlet.Component (1199403432)       : 2017-10-26    10:31:35    0:0:0:0:0:0:0:1    -    0:0:0:0:0:0:0:1    8082    GET    /database/BIRT/allcustomers    -    

The database connector logs version information for each datasource definition as it makes initial contact with your database(s).

2017-09-15 15:50:01.554  INFO   28614 --- [ost-startStop-1] c.f.c.d.service.ConfigurationService     : DriverName: Microsoft JDBC Driver 6.3 for SQL Server
2017-09-15 15:50:01.554  INFO   28614 --- [ost-startStop-1] c.f.c.d.service.ConfigurationService     : DriverVersion: 6.3.1.0
2017-09-15 15:50:01.554  INFO   28614 --- [ost-startStop-1] c.f.c.d.service.ConfigurationService     : DriverMajorVersion: 6
2017-09-15 15:50:01.554  INFO   28614 --- [ost-startStop-1] c.f.c.d.service.ConfigurationService     : DriverMinorVersion: 3
2017-09-15 15:50:01.554  INFO   28614 --- [ost-startStop-1] c.f.c.d.service.ConfigurationService     : DatabaseProductName: Microsoft SQL Server
2017-09-15 15:50:01.554  INFO   28614 --- [ost-startStop-1] c.f.c.d.service.ConfigurationService     : DatabaseProductVersion: 14.00.900
2017-09-15 15:50:01.554  INFO   28614 --- [ost-startStop-1] c.f.c.d.service.ConfigurationService     : DatabaseMajorVersion: 14
2017-09-15 15:50:01.554  INFO   28614 --- [ost-startStop-1] c.f.c.d.service.ConfigurationService     : DatabaseMinorVersion: 0

The Database Connector logfile is located in <frevvo-home>\tomcat\logs for the tomcat bundle and in <db-home>\database-connector-X.X.X\logs directory for the Standalone bundle. X.X.X is the version of the Database Connector. It is named database-connector.YYYY-MM-DD.log

In standalone mode, you will see three additional logfiles.

1. DbConnector.err.log - currently empty.
2. DbConnector.out.log - logs the database connector version.

3. DbConnector.wrapper.log - empty unless the database connector is running as a servers. Logs starts/stops date/time.

Turning on DEBUG level logging

1. Stop if you are using the tomcat bundle or the Database Connector service if you are using the Standalone bundle.
2. Edit the:
   1. Standalone bundle - <db-home>\database-connector-DBC version number\config\dbconnector.properties file
   2. Tomcat bundle - <frevvo-home>/tomcat/conf/dbconnector.properties file.

3. Add the following line.

    logging.level.com.frevvo.connectors.database=DEBUG

4. Save the file
5. Restart the Standalone Database Connector service or if you are using the tomcat bundle.

Common Problems

Some common issues with solutions are listed below.

Doc Post Failure

If a submission doesn't reach the Database Connector or when certain database operations fail,  will

• Flag the submission with an error in the Submissions Table
• Send Doc Post Failure notification email(s) reporting information about the error to all tenant administrators or flow admins (if configured)

 Example 1:

The email reports a Connection refused error - To resolve, verify the URL to your database connector is correct in business rules or Doc URI's

Example 2:

All DBC SQL operations are performed within a database transaction. When the following SQL errors occur, flags the submission with an error and generates Doc Action Failure emails to the tenant/flow admin(s) :

• An update statement updates ) rows and autocreate is false
• A query with autocreate=true is missing a create statement
• A query when autodelete is true and is missing the delete statement
• A query with the Delete statement and the delete Key is not defined
• A delete statement where the specified delete key cannot be found in the retrieved resultset
• If any one of the SQL operations (except Update) fails
  •  For example: When calling a querySet with 1 or more autocreate or autodelete=true, internally the DBC is likely to execute multiple SQL statements:
    • It will try to delete all rows that were deleted from the form resultSet that were originally in the resultSet retrieved from the database.
    • It will try to update rows from the form resultSet - and if autocreate is true and the update fails, it will execute an insert for each update failure. 
• Browsing the URL to generate the schema for querysets

Updates do not trigger errors due to several valid use cases for update failures ex: autocreate=true

Log entries for the error are captured in the database-connector.YYYY-MM-DD.log

2017-10-26 13:46:05.828 ERROR mycompany 93f95ebc-6a85-4426-a015-eb34a1c96042 17048 --- [http-nio-8082-exec-8] c.f.connectors.database.QueryResource    : Create operation not defined for query BIRT/createOrder
2017-10-26 13:46:05.829  INFO mycompany 93f95ebc-6a85-4426-a015-eb34a1c96042 17048 --- [http-nio-8082-exec-8] org.restlet.Component (2011499821)       : 2017-10-26    13:46:05    127.0.0.1    -    127.0.0.1    8082    POST    /database/BIRT/createOrder    -    404    328    535    6    http://localhost:8082    Jakarta Commons-HttpClient/3.1    -

Live Forms tomcat running on non-default port

If you have configured your tomcat in <frevvo-home>/tomcat/conf/server.xml to run on a port other than the default 8082, then you must set the port in the Url to the database connector to that different port number.

This will not work: http://localhost:8082/database/myStore/allOrders. Change 8082 to your tomcat port.

Your browser is not on the same machine as the database connector

If your web browser is not running on the machine where your database connector is installed then you cannot use "localhost" in the Url. You must replace this with the name or ipaddr of the machine running the database connector.

This will not work: http://localhost:8082/database/myStore/allOrders. Change "localhost" to the actual hostname or ipaddr. 

The configuration.xml file is not found

If the Database Connector cannot find the configuration.xml file, an error message will be reported in the browser:

Check the reported path. Verify that the configuration.xml is present in the same directory as the database.war file or in the C:\database\database-connector-2.5.x\config directory for a Standalone installation. If you are running the connector in the tomcat bundle, verify that the value of the  "frevvo.connectors.database.configuration parameter in the <frevvo-home>\tomcat\conf\frevvo-config.properties file reflects the correct location of the configuration.xml. An example of this property is shown below:

frevvo.connectors.database.configuration=file:///C:/<path to configuration.xml>

The format of your test query URL is invalid

If you type an invalid URL into the browser when testing a query, you will see this message:

Revise the URL to follow the correct format.

Warning Message if Connection threshold is exceeded

If the time to connect to the Database Connector exceeds the default value of 2000 ms you will see a warning in the Database Connector log similar to the one shown below:

2018-04-20 13:06:37.790  WARN mycompany c852c55e-d529-42b0-a7f2-a4bc8d370d95 2068 --- [http-nio-8082-exec-4] c.f.connector.database.QueryResource     : Database operation took 6962 ms exceeding the threshold of 2000 ms 

You can override the default by adding the frevvo.connectors.database.configuration.connectionThresholdMillis with a value in ms to the <frevvo-home>\tomcat\conf\dbconnector.properties file (tomcat bundle) or the <database connector-home>\dbconnector-2.5.5.zip\database-connector-2.5.5\config\dbconnector.properties file.

frevvo.connectors.database.configuration.connectionThresholdMillis=<value in ms>