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:
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.
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:
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.
The database connector logs version information for each datasource definition as it makes initial contact with your database(s).
To log the actual SQL statement, arguments, number or rows returned and success status, set logging level to INFO.
There are different logfiles for the Standalone and tomcat bundle installations.
There are four logfiles in <db-home>\database\database-connector-2.5.x\logs
Turning on DEBUG level logging
The database connector writes all output to a single logfile <frevvo-home>\tomcat\logs\database-connector.YYYY-MM-DD.log
Turning on DEBUG level logging
Switch the Database Connector log in JSON format
Logging output defaults to text format. To switch to JSON format, add this property to the frevvo-config.properties for the frevvo-tomcat bundle or to dbconnector.properties file for the standalone bundle.
If a submission doesn't reach the Database Connector or when certain database operations fail, will
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
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) :
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 -
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:
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:
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>
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.