Versions Compared

Key

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

...

Setting

Property (default UDM sample database setup)

What is does

Comment

Maximum prepared statements idle

server.{index}.maxPreparedStatementsIdle=100

All Servoy generated SQL statements are in the form of Prepared statements, to increase the performance of statement execution. This setting determines how many prepared statements are kept in cache.

While exposed in Servoy, tuning this setting requires indepth insight of the JDBC driver used. As such, this setting is not further documented. Refer to the JDBC driver documentation for more information.

Query validation type

server.{index}.connectionValidationType=0

Some databases automatically end connections when they have been idle for a certain period of time. This setting controls if and how Servoy validates a connection leased from the connection pool, before using it. There are three variations:

  • exception validation (value = 0, default): With exception validation no validation occurs before using the connection. When a exception occurs, the connection is destroyed. While this method has no overhead, the downside of this method is that the user is presented with the exception.
  • query validation (value = 1): With query validation, each connection is validated by executing a validation query upon leasing the idle connection from the pool. If the execution of the validation query results in an exception, the connection is destroyed and a new connection is leased from the pool. This process continues, until a connection correctly handles the validation query. The actual validation query can be set using the 'Validation query' setting (see below). This method has the greatest overhead of the three options, because the validation query is fired for every request towards the database.
  • metadata validation (value = 2): With meta data validation the JDBC driver is asked for some metadata about the connection. This method is not as useful on all databases, as some JDBC driver cache the meta data, so they return their result without actual communication with the database itself
    In most scenario's the exception validation will be sufficient, as the Servoy Application Server and the Database Server are connected via a reliable network connection (or are hosted on the same machine). Instead of opting in for any of the other validation types, it is advised to solve any connection issues between the Application Server and the Database Server, instead of changing the validation type. Changing the validation type should be the last resort.
  • driver based validation (value= 3, since 8.4): With driver based validation, the connection validation mechanism of the JDBC driver is used; note that this method may send a query to the database server, depending on the driver implementation.
    In case of an old (JDBC3) driver the validation will fall back to exception validation.

MySQL is notorious for invalidating existing connections after little idle time. While setting 'Query validation' as the validation type will solve the issue in most cases, it's better to configure MySQL to not invalidate connections or not invalidate them as fast

Validation query

server.{index}.validationQuery=

The SQL statement fired at the database if the 'Query validation type' is set to 'Query validation'. The SQL statement used should be a statement with as little overhead as possible, for example an efficient query such as 'SELECT 1'. While this statement is valid SQL92, it does not work on all databases through

 

Data model cloned from

server.{index}.dataModelCloneFrom=

This setting allows marking a Database Server as a clone of another Database Server. When marked as such, if a Solution is imported on the Servoy Application Server, any updates to the datamodel of the master Database Server are also applied to the Database Servers that are marked as a clone of the master Database Server. 

Solutions in Servoy are designed against a named Database Server. The actual Database Server against which a Solution runs can be switched at runtime from within the solution's code. When a Solution is imported on a Servoy Application Server, it will automatically update the datamodel in the database to match the datamodel against which the Solution was designed. In case the functionality to switch the Database Server against which the Solution runs is used, it's essential that the datamodel updates are done on all the 'clones' of the master database against which the Solution was designed. This setting can be used to identify a database as a clone of another database. When marked as such, if a solution gets imported that updates the datamodel in the master database, the datamodel in the clone will also be updated.

Enabled

server.{index}.enabled=true

Whether or not the database is enabled.

 

Skip System Tables

server.{index}.skipSysTables=false

Whether or not System Tables and Views from the database are to be exposed in Servoy.

 

Log server

servoy.log_server=

Servoy has functionality that allows to automatically track all insert/updates/deletes on tables. This functionality can be enabled through the Security layer inside the Solution. This functionality relies on one of the enabled Database Servers configured on the Servoy Application Server being marked at 'Log server'. This setting defines if the Database Server is the Log server.

 

Enable stored proceduresserver.{index}.queryProcedures=falseWhether server should expose stored procedures from the database. Default value is false except for Progress database where value is true. If set to true, will show stored procedures in solution explorer view and in code completion under datasources. From datasources node can call a stored procedure. 
Maximum number of values for IN-clauses in SQL queriesserver.{index}.selectINValueCountLimit=200When Servoy needs to query the database using an IN-clause, it will use a temporary table if the number of values is above a certain limit. This limit can be configured per database server, or globally (setting servoy.selectINValueCountLimit).
Tthe default limit is 200.

Since release 8.3.2.

Note that when this is set to a too large value, queries may fail because the driver may not accept such large queries with so many parameters.

 

All settings for the Database Servers are stored in {serverInstall}/application_server/servoy.properties. For example the connection defined for the UDM sample database:

...