Databases
Last updated
Last updated
This guide will demonstrate how to manage database connections. Applications built in Servoy are inherently data-connected and data-driven. Developers can connect to any database, web-service, flat file or legacy system.
Servoy works particularly well with relational database systems (RDBMS), providing enhanced capabilities for connecting, querying and updating databases. Developers can blend data from many sources, quickly build data-bound user interfaces and easily execute logic against their data models.
Among the first tasks when getting started in Servoy is to setup your . Most commonly, developers will connect to existing databases and sometimes create or modify new databases.
Below are a few examples to begin working with databases.
Servoy applications can connect to databases that you already use.
To connect to an existing database, right-click the Database Servers node in the Solution Explorer and select Connect to Existing Database.
From the drop-down menu, choose your database vendor or you can choose empty.
The Database Server Connection Editor will be opened. If you selected a particular vendor, then some of the advanced properties will be pre-filled. You must enter the minimum required properties to connect to your database, including:
Server Name The Server Name is the identifying name of the Database Server connection. This name will be used in various data bindings and can be referenced in scripts, so it should not change.
A database connection's Server Name property does not need to match the name of the underlying database.
Host Name
The Host Name is the name or IP address of the target database server. The following are all examples of a valid host name: localhost , 127.0.0.1 or demo.code.us-east-1.rds.amazonaws.com
Database Name The Database Name property is the name of the database, as it is defined in the database server that you want to connect to.
Credentials (User name and password if needed) Most databases will require authentication. Enter the User Name and Password properties if they are required by your database.
Which username should I use? Servoy will connect to your application on behalf of your application users. This is more like a "System User" and you typically do not need to provide database credentials for each of your end-users.
Once you have entered the connection properties, you can test your connection by clicking the Test Connection button. If you are satisfied, you can Save your database connection.
Once the connection is saved, Servoy Developer will (re)load the the database schema, including Tables, Views and Procedures (if enabled) and you will be ready to design your application.
Depending on your database vendor and configuration, you may need to enter some advanced settings when you create the connection. Click the "Advanced Settings" link in the editor to expand more properties.
JDBC Connectivity Servoy relies on the JDBC standard to connect to databases. Therefore, your application can connect to any database having a valid JDBC driver. Consult your database vendor' documentation for specifics on connecting client applications.
While there are many properties in the advanced settings, a few of the common properties are listed below.
URL The connection URL is a standard JDBC convention to connect to various databases and the form it takes will depend on the specific database vendor. It typically takes the following form:
<protocol>:<sub-protocol>://<host-name>:<port>/<database-name>?[options]
...where the sub-protocol, default port and options are vendor-specific. For example, a connection to a PostgreSQL database named "demo" on the local machine would look like this:
jdbc:postgresql://localhost:5432/demo
Driver The Driver property is the qualified class name of a registered JDBC Driver and will be specific to the database vendor of choice. Servoy ships several drivers and vendor-specific connection templates. However developers may install their own driver. Registered drivers will appear in the dropdown field for the Driver property.
While these are just a few common settings, a complete list of database connection properties can be found in the Database Servers reference documentation.
While many developers already have databases to connect to, it is nonetheless easy to create your own databases in Servoy Developer.
To create a new database, right-click the Database Servers node in the Solution Explorer and select Create PotgreSQL Database.
Enter the name of the new database in the dialog prompt. A database will be created and a new connection will be configured.
PostgreSQL is a popular, open-source database and while Servoy is vendor-agnostic, Servoy Developer includes, by default, an instance of PostgreSQL, sample databases and pre-configured connections.
If you have disabled the optional installation of PostgreSQL, then the Create Database command will not be available.
The following links provide additional reading on the topic of databases in Servoy
For a complete list of properties available for database connection configuration, please see the reference page, Database Server Connection.
Once you have created a database connection, you are ready to work with database tables. See our guide on Working with Tables.
Servoy can easily work with any SQL Views you have defined in your database. See our guide, Working with SQL Views
Servoy enabled developers to call their database's Stored Procedures and even bind objects to the results. See our guide, Working With Stored Procedures
In Servoy, developers can write scripts and program logic using robust data APIs. For mor information see the Programming Guide's chapter, Working With Data
Database connections are configured for a single named data source, however this datasource can be context-based, meaning all the data-bound objects and logic will still work against another back-end database having the same schema.
It is, for example, quite common to have different databases for development, testing and production deployment. Fortunately, Servoy not only let's you switch easily between databases, it can also keep track of schema changes and automate updates during deployments!
For more information, see our Deployment Guide.
