Adding a JDBC driver in WinSQL

WinSQL comes with a handful of JDBC drivers for popular databases. However, Synametrics Technologies, Inc. cannot add a driver for every available database due to licensing restrictions. However, you can add unlimited drivers in WinSQL and then connect to the back-end database.

Finding a JDBC Driver

Most JDBC drivers can be found using a simple search on the Internet. For example, if you're trying to connect to Google Spanner, search "JDBC driver for Google Spanner" online. In this case, the actual driver is uploaded to a Maven repository. It is recommended that you download a JAR file that bundles every dependency, which makes it easier to add in WinSQL. If the driver you're using has additional JAR files that are not bundled together, you must save those files in %ALLUSERPROFILE%\WinSQL\jars folder. The variable %ALLUSERPROFILE% often resolves to C:\ProgramData folder, but can be different on your machine.

It is recommended that you save the downloaded JAR file to a folder that is accessible to every user on the machine. In other words, do not save the JAR file in the "Downloads" or the "Desktop" folder. This is because users other than yourself will not have access to files residing on your Desktop.

Registering A Driver

Use the following steps to register a new JDBC driver.

• Click File/Open JDBC Manager
• Select the Drivers tab
• Click the Add button
• Click the Browse button and select the downloaded JAR file. WinSQL will analyze the JAR file and automatically populate the value for Discovered Drivers. If no drivers are found in a JAR, you will receive an error indicating you have downloaded an incorrect JAR.

Authentication in URL

FFor certain backends, such as Google Spanner, the authentication is not provided by the user at runtime. Instead, the credentials are saved in a text file, which can be in JSON format on the machine running WinSQL. In such cases, you must check the Authentication in URL checkbox. When this box is checked, WinSQL will disable the User ID and Password fields when establishing a connection and will read the credentials from the file specified in the URL.

URL Syntax

Every JDBC driver has a different syntax for their URL. Therefore, you must consult the driver's documentation for the actual values. For example, in case of Google Spanner, refer to the documentation on GitHub.

You can add placeholders that are presented as form fields in WinSQL when adding a database. For example, in case of Google Spanner, the example on Github says:

jdbc:cloudspanner:/projects/my-project/instances/my-instance/databases/my-db;credentials=/path/to/credentials.json

In the above example, the values colored in red are variables that can change on your end. WinSQL can prompt the end-user for the actual values, provided you use the following rules to specify them in the URL.

• Variables are enclosed within curly braces.
• They have three components, each separated by a | symbol:
• Label
• Default value
• Data type, which can be S for strings, N for numeric, and B for boolean

Using the following rules, you can specify the URL for Spanner as:

jdbc:cloudspanner:/projects/{Project Name|my-project|S}/instances/{Instance Name|my-instance|S}/databases/{Database Name|my-db|S};credentials={JSON Path|/path/to/credentials.json|S}

You should see Google Spanner listed in the Drivers tab after saving this new driver, as shown below.

Once done, WinSQL will display the following screen when a user creates a DSN for Google Spanner.