Child pages
  • Database Connections
Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 9 Next »

Servoy utilizes a three tier architecture for database access: Clients communicate with the Servoy Application Server and the Application Server communicates with the database.

The Application Server connects to databases through [JDBC|http://www.oracle.com/technetwork/java/javase/jdbc/index.html], a universal Java technology for Java applications to communicate with SQL databases.


For each defined database, the Servoy Application Server manages a pool of database connections, the size of which is configurable, to minimize to overhead of connection creation.

h3. JDBC Drivers

In order to be able to connect to a database, the Servoy Application Server requires a JDBC Driver. JDBC drivers usually come with the database, are provided separately by the database vendor or are available from third party vendors.

Servoy ships JDBC drivers for a several databases. For a full overview of the JDBC drivers shipped with a specific Servoy version, check the Servoy Stack info page in the Programming Reference Guide for the Servoy version used. 

The JDBC drivers are stored in the ../application_server/drivers/ directory.

Additional JDBC Drivers can be manually placed in this directory or can be uploaded through the Upload Library section of the Servoy Admin page. In both scenario's the Application Server requires a restart.

(warning)  While one database is more strict than the other, it is important to use the correct version JDBC Driveror a specific version of the database. Notably Oracle is very strict: even using the JDBC driver of another minor version might cause unexpected results! Refer to the documentation of the database vendor for the correct JDBC driver.

oraclelobfix.jar

The oraclelobfix.jar file in the ../application_server/drivers directory is not an Oracle JDBC driver, but a library containing a fix for an issue in the Oracle JDBC driver. When connecting to Oracle databases, it's still required to add the correct Oracle JDBC driver.



h3. Configuring database access

Connection settings

Pooling settings

Performance settings

Access to databases can be configured through Database Servers section of the Servoy Admin page and are stored in the servoy.properties file.

In order to connect the Servoy Application Server to a database, the following information is required:
|| Property || Description || Required || Comment ||
| Server name | | Yes | |
| User name | | Yes
| |
| Password | | Yes
| |
| URL | | Yes | |
| Driver | | Yes | |
| Catalog | | Depends | |
| Schema | | Depends | |
| Max. Connection Active | | Yes | |
| Max. Connections Idle | | Yes | |
| | | | |
| | | | |
| | | | |
| | | | |
| | | | |
| | | | |
| | | | |
* Server Name
name used within Servoy solutions to access the a certain database
* User Name & password
The username and password used by the Servoy Application Server to connect to the database
* Connection URL
The url used by the Servoy Application Server to connect to the database
* Driver ClassName
* Catelog or Schema (optional)


h3. Connection Pooling configuration 

Servoy relies on SQL databases for data storage. Servoy is database agnostic and can connect to any database that supports JDBC.

Connections to databases are configured on the Servoy Application Server and the Servoy Application Server will automatically create a pool of connections.

Clients never directly connect to a database, but instead connect to the Servoy Application Server, which handles the  

Connection Pooling


connection pooling works like this
at the moment a client (smart or web) does a query, it takes one from the pool if there is 1 idle (if not it will create one)
then if it is done it puts it back into the pool and it keeps idle connections until the max idle count 

Besides that you also have a max number of connections that are in use.. If that is reached then the next one cant get a connection until another releases one (gives it back as idle)

I think idle of 10 connections is fine for most useage, but do set the max number of connections a bit higher to handle peak connections
What exactly the right thing is is a bit hard to say maybe if you say that you can have max 100 users at the same time
you should have 50 max number of connections and 20 idle...
(so max is users/2 and idle could be users/5)

But this is also a bit depending on your solution, if you use long transactions for example then the max number should be users/1 ....

-------------------
The Database Server section on the Servoy Admin page gives an overview of all defined database connections with the options to edit the connections and create new connections.

Server Connection Settings
The connection to a database is defined by the following properties:

URL

The JDBC URL to access the database. See the JDBC driver's documentation for the correct syntax

Driver

The className of the JDBC driver to use. See the JDBC driver's documentation for the correct name. See JDBC Drivers for more info on JDBC drivers

Username

The name of the user with which the Servoy Application Server can connect to the database

Catalog

The name of the catalog within the database to which to connect (optional, not supported by all databases)

Schema

The name of the schema within the database to which to connect (optional, not supported by all databases)

Maximum connections active & Maximum connections idle

The maximum number of active and idle connections parameters are used to configure how many connections are kept in the pool. The maximum number of active connections is the maximum number of simultaneous connections that the server will ever use, forcing clients to wait for a connection if all connections are in use. The maximum number of idle connections is the maximum number of open but unused connections that are allowed to be in the pool.

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

ServerManager.numberOfServers=4
server.0.URL=jdbc\:sybase\:Tds\:localhost\:2638?ServiceName\=servoy_repository&CHARSET\=utf8
server.0.catalog=<none>
server.0.driver=com.sybase.jdbc3.jdbc.SybDriver
server.0.maxConnectionsActive=10
server.0.maxConnectionsIdle=10
server.0.password=encrypted\:aD4kOmHPzcM\=
server.0.schema=<none>
server.0.serverName=repository_server
server.0.userName=DBA
  • No labels