JDBC Connection
JDBC (Java Database Connectivity) is a database access interface which is part of the Java software platform from Oracle. JDBC connections are generally more resource-intensive than ODBC connections but may provide features not available through ODBC.
Prerequisites
•JRE (Java Runtime Environment) or Java Development Kit (JDK) must be installed. This may be either Oracle JDK or an open source build such as Oracle OpenJDK. StyleVision will determine the path to the Java Virtual Machine (JVM) from the following locations, in this order: (i) the custom JVM path you may have set in application Options; ; (ii) the JVM path found in the Windows registry; (iii) the JAVA_HOME environment variable.
•Make sure that the platform of StyleVision (32-bit, 64-bit) matches that of the JRE/JDK.
•The JDBC drivers from the database vendor must be installed. These may be JDBC drivers installed as part of a database client installation, or supported JDBC libraries (.jar files) that are downloaded separately. See also Database Connection Examples.
•The CLASSPATH environment variable must include the path to the JDBC driver (one or several .jar files) on your Windows operating system. When you install some database clients, the installer may configure this variable automatically. See also Configuring the CLASSPATH.
Connect to SQL Server via JDBC with Windows credentials
If you connect to SQL Server through JDBC with Windows credentials (integrated security), note the following:
•The sqljdbc_auth.dll file included in the JDBC driver package must be copied to a directory that is on the system PATH environment variable. There are two such files, one for the x86 and one for x64 platform. Make sure that you add to the PATH the one that corresponds to your JDK platform.
•The JDBC connection string must include the property integratedSecurity=true.
For further information, refer to Microsoft JDBC driver for SQL Server documentation, https://docs.microsoft.com/en-us/sql/connect/jdbc/building-the-connection-url.
Set up a JDBC connection
1.Start the database connection wizard and click JDBC Connections.
2.If required, enter a semicolon-separated list of .jar file paths in the Classpaths field. The .jar libraries entered here will be loaded into the environment in addition to those already defined in the CLASSPATH environment variable. JDBC drivers found in the source .jar libraries referenced via the Classpaths field and the system's CLASSPATH are listed in the Driver dropdown list (see next step).
3.In the Driver field, select a JDBC driver from the list or enter a Java class name. The list will contain the JDBC drivers configured through in the Classpaths field (see above) and the CLASSPATH environment variable.
The JDBC driver paths defined in the CLASSPATH variable, as well as any .jar file paths entered directly in the database connection dialog box are all supplied to the Java Virtual Machine (JVM). The JVM then decides which drivers to use in order to establish a connection. It is recommended that you keep track of Java classes loaded into the JVM so as not to create potential JDBC driver conflicts and avoid unexpected results when connecting to the database. |
4.Enter the username and password of the database in the corresponding fields.
5.In the Database URL field, enter the JDBC connection URL (JDBC connection string) in the format specific to your database type. The following table describes the syntax of JDBC connection strings for common database types.
Database | JDBC Connection URL |
|---|---|
Firebird | jdbc:firebirdsql://<host>[:<port>]/<database path or alias> |
IBM DB2 | jdbc:db2://hostName:port/databaseName |
IBM DB2 for i | jdbc:as400://[host] |
IBM Informix | jdbc:informix-sqli://hostName:port/databaseName:INFORMIXSERVER=myserver |
MariaDB | jdbc:mariadb://hostName:port/databaseName |
Microsoft SQL Server | jdbc:sqlserver://hostName:port;databaseName=name |
MySQL | jdbc:mysql://hostName:port/databaseName |
Oracle | jdbc:oracle:thin:@hostName:port:SID jdbc:oracle:thin:@//hostName:port/service |
Oracle XML DB | jdbc:oracle:oci:@//hostName:port:service |
PostgreSQL | jdbc:postgresql://hostName:port/databaseName |
Progress OpenEdge | jdbc:datadirect:openedge://host:port;databaseName=db_name |
Sybase | jdbc:sybase:Tds:hostName:port/databaseName |
Teradata | jdbc:teradata://databaseServerName |
Note that syntax variations of the formats listed above are also possible. For example, the database URL may exclude the port or may include the username and password of the database. Check the documentation of the database vendor for further details.
6.Click Connect.