Page History
...
* Catalog & Schema: JDBC defines that a database may have a set of catalog and each catalog may have a set of schema's. However, each database/JDBC driver vendor has interpreted this differently. In general a Catalog contains all the system/metadata tables/views, while the schema contains all the "user" defined tables, views, triggers etc. Within the context of Servoy, the Catalog is hardly used, while the schema setting is used when connection to Oracle
Besides the above mentioned settings that deal with how to connect to the database, a database Server definition within Servoy has a few additional settings:
Setting | What is does | Comment |
|---|---|---|
Maximum prepared statements idle | 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 | 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:
| 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 | 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 | 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 | Whether or not the database is enabled. |
|
Skip System Tables | Whether or not System Tables and Views from the database are to be exposed in 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. |
|
All settings for the Database Servers are stored in {serverInstall}/application_server/servoy.properties. For more information on updating servoy.properties manually, see Application Server.
Connection pooling
Clients do not directly access the databases, instead all their query requests are send to the Application Server which then delegates the query to the correct database.
...
For the bundled PostgreSQL database engine for example, the maximum is 100 connections. However, these 100 connections are for all connections made to the PostgreSQL database server instance. This means that if there are multiple Database Servers defined in the Servoy Application Server which are all hosted on the same PostgreSQL database server instance, the max. 100 connections are for all Database Servers combined. This must be taken into account when setting up the maximun number of active connections.
Log Server
Servoy has functionality that allows
TODO's
Explain all the different Exception Validations
- http://forum.servoy.com/viewtopic.php?f=4&t=11332&hilit=+validation+query+greg
- http://forum.servoy.com/viewtopic.php?f=4&t=4365&hilit=+validation+query+
- http://forum.servoy.com/viewtopic.php?f=5&t=4837&hilit=+validation+query+
Check the stuff about Catalog & Schema
Enabled
Clones
servoy.properties
Configuring database access
Maximum prepared statements idle
The maximum number of idle prepared statements parameter specifies the maximum number of prepared statements that are pooled per connection. It depends on the JDBC driver and the backend whether the prepared statements are cached on the database side, thereby improving query performance. For some databases, prepared statements are not cached on the server at all, in which case the maximum number of idle prepared statements can be set to 0. On other databases, the prepared statements automatically time out (and thus need to be re-prepared) after a small timeout period (for example 1 minute), in which case a large value of this parameter is also unnecessary. Prepared statements are cached on a LRU basis, which means that when the pool is full, the least recently used prepared statement is removed.
...
Query validation type & Validation query
...
Some databases automatically end connections when they have been idle for a certain period of time. To prevent using a pooled connection that was already disconnected by the database, a connection validation method can be specified which checks database connections when they are taken from the pool. There are three methods of validation:
- exception validation - the exception validation method invalidates a connection whenever an error has occurred (I/O, or SQL) on that connection
- metadata validation - the meta data validation method asks the database for some database meta data to validate the connection. Note that this is not useful on all databases, as sometimes the JDBC driver returns the meta data without actual communication with the database itself
- query validation - this method is the safest method (but also has the greatest impact on performance). Whenever a connection is retreived from the pool, the specified query is performed, and the connection is considered valid only if the query succeeds (i.e. no I/O or SQL error occurs). Note that in the worst case, the number of queries on the database doubles, although an efficient query such as 'SELECT 1' has very little performance penalty (the 'SELECT 1' query, even though it is valid SQL92, does not work on all databases)
...
Data model cloned from
...
In a SaaS deployment when using a database clone per tenant, any update to the datamodel for a solution on solution import needs to be applied to all database clones. Using this property a database can be marked as a clone of the master database, the database against which the solution was designed. When a solution is imported into the Servoy Application Server that requires changes to the datamodel, the same change will be applied to all clones of the master database.
...
Enabled
...
Whether or not this server is enabled
...
Log Server
...
Whether or not this server will be used as the log server.
Update Servoy sequences for...
| Code Block |
|---|
ServerManager.numberOfServers=4 |
...
Update Servoy Sequences
Servoy supports an internal Primary Key sequence generator mechanism, in addition to database managed PK sequence generators. If Servoy Sequences are used within a Solution and either Servoy is connected to an existing database with data for the first time, or while Servoy was connected to the database and external processes inserted new records in the tables for which Servoy manages the sequences when Solutions insert records into those tables, the internal counter will be out of sync with the actual records in the database. The option to update the Servoy Sequences will look at the max value in each table and update the internal counter inside Servoy, bringing them in sync again.
When records are inserted through Servoy while the internal Servoy sequence counter is out of sync with the actual data in the database, exceptions can occur.
Overview
Content Tools
Activity