A relational database is accessed via DatabaseDataSource:
The connection with a database is established via the Connection URL, also called URL connection string.
For access to external databases, explicit input fields are available for the user name and password. The password is saved in encrypted form; this way, it is not visible to users at any time.
The following table shows connection URLs for standard databases, including the name of the user and password, because this form is also valid. But this is a security risk, as the user names and passwords entered in the URL appear in clear text in the log file:
Database | Connection URL |
---|---|
Microsoft SQL Server | jdbc:jtds:sqlserver://localhost:1433/databasename;user=username;password=password |
Microsoft SQL Server with instance | jdbc:jtds:sqlserver://localhost:1433/databasename;instance=instancename;user=username;password=password |
hsqldb | jdbc:hsqldb:hsql://localhost/xdb;user=user;password=password |
Oracle | jdbc:oracle:thin:username/password@localhost:1521/orcl |
PostgreSQL | jdbc:postgresql://localhost:5432/databasename?user=postgres&password=postgres |
JDBC-ODBC-Bridge | jdbc:odbc:Driver={PostgreSQL Unicode};Server=localhost;port=5432;Database=databasename;UID=postgres;PWD=postgres; |
Note:
Connections entered in the Connection URL field, including user name and password, represent a security risk, because these data appear in clear text in the log file! Please use the user name and password only in the input fields of the same name.
Note:
Unicode support is available only for URLs that point to native JDBC drivers. The JDBC-ODBC bridge does not support Unicode.
If the Connection URL is written without stating the user name and password, no semicolon has to be added at the end.
In this case, the Connection URL for hsqldb is then, for example, jdbc:hsqldb:hsql://localhost/xdb
The results of requests to external relational databases are returned as strings by default. Using the option Convert Integer, Double and Boolean, DatabaseDataSource can be set in such a way that the database column types of the external relational databases are converted to the primitive EEC data types Integer, Double and Boolean.
Depending on the database, there are special points to note:
The JDBC standard specifies the SQL types and a standard mapping on Java types. The respective JDBC driver specifies the mapping of native database types on the JDBC types. Finally, it depends on the database and the JDBC driver whether the primitive type conversion works 100%.
An example is the definition of the data type Bool in PostgreSQL: Via the PostgreSQL-ODBC driver, the data type Boolean is mapped on java.sql.Types.CHAR, so that a conversion to the primitive type Boolean does not work.
Further information on SQL request of various databases:
The different supported databases exhibit special characteristics in handling the uppercase / lowercase spelling of column and table names, which are shown below for each database. This is followed at the end by a recommendation concerning the choice of column / table names if there is to be an option to change databases.
The examples are based on a table called Parts with a column PartNumber, a table called SUPPLIERS with a column SUPPLIERNUMBER, and a table called Customers with a column CustomerNumber.
Microsoft SQL Server Express
The Microsoft SQL Server Express is created as an instance.
Syntax of the URL:
jdbc:jtds:sqlserver://<Server name>/<Database name>;instance=SQLEXPRESS
Note:
Upper-case / lower-case differentiation is not relevant for the <Database name> specification.
The following settings are carried out by means of SQL Server Configuration Manager:
- For the SQL Server activate the TCP/IP protocol.
- For the SQL Native Clients activate the TCP/IP protocol.
- Start the services SQL Server Browser and SQL Server (SQLEXPRESS).
The following settings have to be carried out by using the Microsoft SQL Server Management Studio:
- Activate the Windows identifier.
- Activate SQL-Login.
Microsoft SQL Server with instance
A local SQL login has to be specified for the login process. The Windows user identification is not supported by EEC! You have to observe upper-case / lower-case spelling at both the server and the instance name.
There can be several instances of a Microsoft SQL Server database on a server. For such instances, the URL addresses must be extended to include additional properties. For more information on possible scenarios, visit the following web address:
Note:
The entries for Connection URL, Default table, User and Password could be overwritten by means of EEC arguments. The EEC arguments are given in the initialization file (e.g.ec.ini).
See also:
de.eplan.eec.datasource.defaultTable
de.eplan.eec.datasource.password
More:
General recommendation for the choice of column and table names