Versions Compared

Old Version 1

changes.mady.by.user Former user

Saved on

compared with

New Version Current

changes.mady.by.user Former user

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Section
Column
 

Once you have started the Database Connector up and running and configured with your database, access the connector by typing the status URL below directly in your browser to quickly verify proper installation.

Tomcat Bundle:

http://localhost:8082/database/status - Change localhost and port 8082 to the <server> and <port> for your  tomcat  installation where you are deploying the connector.

Standalone:

http://localhost:8081/database/status - the default port for Standalone mode is 8081. Change localhost and port 8081 to the <server> and <port> where you installed the Standalone connector.
Image Removed
Image Added

The configuration model shows two additional properties: lastModified and digest. These properties are set when the configuration.xml is read and will change when modifications are made to the configuration.xml file.
  • SHA-1 digest of of all the file content is generated
  • Date/time of the last modification to configuration.xml.
Column
width300px

On this page:

Table of Contents
maxLevel1

...

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

REST parameters for the test URL

...

  • <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

Logfiles11:37:25.394 |-WARN

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.

Excerpt

The Database Connector uses SLF4J with logback, which is the same logging infrastructure used in  .Log MDC entries for configuration path, the querySet name, the query name, correlation.id, tenant id, subject.id. These will be captured when processing client requests.

Code Block
Code Block
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-8] [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).

Code Block
2017-09-15 15:50:01.554  INFO   28614 --- [ost-startStop-1] c.f.c.d.s.MetaDataService] [mycompany ] [26d27ffe-1213-4c82-890b-cdc1a2e00e87 ] [  ] - Could not find retrieve operation for query STUDENT/addStudent. Skipping ...
11:37:27.186 |-ERROR [http-nio-8082-exec-8] [   .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.QueryResource] [mycompany ] [26d27ffe-1213-4c82-890b-cdc1a2e00e87 ] [  ] - Error processing request for querySet STUDENT

Exceptions are properly logged with their stacktraces. This makes it easier to diagnose errors, which sometimes are frevvo/DBConnector specific and sometimes database specific.

All JDBC statements are logged in the DEBUG logging level. This includes the actual SQL statement used, the actual arguments, the number or rows returned and whether it was successful
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

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.

Standalone mode

Three logfiles are created in the

There are four logfiles in <db-home>\database\database-connector-2.5.

0

x\logs

directory:

  1. DbConnector.err.log - there is no content in this log at this timecurrently empty.
  2. DbConnector.out.log - logs the database connector standard out file. You can see the results of the queryset/queries validation along with non-error log lines that are appended to the file.version.
  3. DbConnector.wrapper.log - Date/Times of connector starts/stops when the Database Connector empty unless the database connector is running as a service.dbconnectorservers. Logs starts/stops date/time.

  4. database-connector.YYYY-MM-DD.log -

    this log

    This is

    created in the current working directory when the connector starts  - database

    the main logfile all output.

Turning on DEBUG level logging
  1. Stop the Standalone database connector.
  2. Edit the <db-home>\database-connector-2.5.0 when you unzip the database.zip file. This log replicates the DbConnector.out.log content in JSON format3\config\dbconnector.properties file.

  3. Add the following line.

    Code Block
     logging.level.com.frevvo.connectors.database=DEBUG
  4. Restart the connector.

Tomcat bundle

When debugging database queries refer to output error messages in The database connector writes all output to a single logfile <frevvo-home>\tomcat\logs\catalina.log and frevvo_database-connector.YYYY-MM-DD.log

Log levels are controlled by the default or configured profile.

Common Problems

Turning on DEBUG level logging
  1. Stop . You do not have to stop the Insight Server.
  2. Edit the <frevvo-home>\tomcat\conf\frevvo-config.properties file.

  3. Add the following line.

    Code Block
     logging.level.com.frevvo.connectors.database=DEBUG
  4. Save the file
  5. Restart

Switch the Database Connector log in JSON format

Logging output defaults to text format. To switch to JSON format, add thisproperty to the frevvo-config.properties for the frevvo-tomcat bundle or to dbconnector.properties file for the standalone bundle.

Code Block
spring.profiles.include=logging-json 

Common Problems

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

Image Added

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

Image Added

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

Code Block
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.0x\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\Catalina\localhost\database.xml frevvo-config.properties file reflects the correct location of the configuration.xml. An example of this property is shown below:

Code Block
<Parameter name="frevvo.connectors.database.configuration" value="c:\<frevvo-home>\frevvo\dbconfig\configuration.xml" override="false"/>=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:

...