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. DatabaseSpy will determine the path to the Java Virtual Machine (JVM) from the following locations, in this order: a) The custom JVM path you may have set in application Options, see Java Settings; b) The JVM path found in the Windows registry; c) The JAVA_HOME environment variable.
•Make sure that the platform of DatabaseSpy (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 JDBC libraries (.jar files) downloaded separately, if available and supported by the database, 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.
Connecting 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.
Setting up a JDBC connection
1.Start the database connection wizard.
2.Click JDBC Connections.
3.Optionally, enter a semicolon-separated list of .jar file paths in the "Classpaths" text box. The .jar libraries entered here will be loaded into the environment in addition to those already defined in the CLASSPATH environment variable. When you finish editing the "Classpaths" text box, any JDBC drivers found in the source .jar libraries are automatically added to the "Driver" list (see the next step).
4.Next to "Driver", select a JDBC driver from the list, or enter a Java class name. Note that this list contains any JDBC drivers configured through the CLASSPATH environment variable (see Configuring the CLASSPATH), as well as those found in the "Classpaths" text box.
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 to 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. |
5.Enter the username and password to the database in the corresponding boxes.
6.In the Database URL text box, enter the JDBC connection URL (string) in the format specific to your database type. The following table describes the syntax of JDBC connection URLs (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: | Syntax variations to the formats listed above are also possible (for example, the database URL may exclude the port or may include the username and password to the database). Check the documentation of the database vendor for further details. |
7.Click Connect.