JDBC Connection syntax

[Janet Hill]

In my department we have to access a MySQL database which is transported between Windows and Unix machines and it seems that JDBC would be an ideal way of providing connectivity. However I cannot get the syntax correct for reading from a local file. Using a simple test database:

local jar "mysql-connector-java-8.0.24.jar"
local driverclass "com.mysql.cj.jdbc.Driver"
local url "jdbc:mysql://D:\UniServerZ\core\mysql\data\stata"
local user "root"
local pass "root"
jdbc connect, jar("`jar'") driverclass("`driverclass'") url("`url'") user("`user'") password("`pass'")

. jdbc showtables
connection failed
Cannot load connection class because of underlying exception: com.mysql.cj.exceptions.WrongArgumentException: Failed to parse the host:port pair 'D:\UniServerZ\core\mysql\data\stata'.

. java query
Java settings
set java_heapmax 4096m
set java_home C:\Program Files\Stata17\utilities\java\windows-x64\zulu-jdk11.0.10\

Java system information
initialized yes
heap usage 13m
heap committed 256m
runtime vendor Azul Systems, Inc.
runtime version 11.0.10+9-LTS
system classpath C:\Program Files\Stata17\utilities\jar\aspose-words-18.11-jdk16.jar;C:\Program Files\Stata17\utilities\jar\libdeps-core.jar;C:\Program Files\Stata17\utilities\jar\libstat
> a-core.jar;C:\Program Files\Stata17\utilities\jar\sfi-api.jar;
javacall classpath C:\Program Files\Stata17\ado\base\jar\libstata-plugin.jar;C:\Users\Janet\OneDrive\ado\personal\my sql-connector-java-8.0.24.jar
library path C:\Program Files\Stata17\utilities\java\windows-x64\zulu-jdk11.0.10\bin\server\jvm.dll

It appears to be a problem in the way I am defining the url and I would be grateful for any advice on correcting this.
Janet

[Kevin Crow (StataCorp)]

What happens when you use the below macro for the URL?

. local url "jdbc:mysql:/D:\UniServerZ\core\mysql\data\stata"

[Janet Hill]

I now get:

. jdbc showtables
connection failed
    Communications link failure

The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.{p_end}
r(681);

[Kevin Crow (StataCorp)]

Try using the syntax specified here for the URL value: https://docs.oracle.com/cd/E17952_01...rl-format.html

.local url "jdbc:mysql://localhost:3306/stata"

[Janet Hill]

Unfortunately the same error.

[Kevin Crow (StataCorp)]

You may need to specify the correct connection settings to use for your specific MySQL server version. For example, for my test MySQL machine, I use the following URL.

local url "jdbc:mysql://localhost:3306/cert?autoReconnect=true&useSSL=false&serverTimezone=UTC"

These settings may or may not work for your MySQL database. Can you contact your network admin to get the correct settings to pass to the JDBC driver?

[Janet Hill]

Thank you. I will try that.

Janet

[Luis Pecht]

Hi Kevin, on the same subject, regarding mssql connection

. jdbc connect, jar("mssql-jdbc-9.2.1.jre15.jar") driverclass("com.microsoft.sqlserver.jdbc.SQLServerDriver") url("jdbc:mssql://...) user("...") password("...")

. jdbc showtables
connection failed; JDBC driver class com.microsoft.sqlserver.jdbc.SQLServerDriver not found

What would be the correct .jar file with com.microsoft.sqlserver.jdbc.SQLServerDriver class? or any are corrrect ?

thks.

[Kevin Crow (StataCorp)]

Each JDBC driver/database will have a different jar file and class name. Your database vendor should provide the class name with the installation instructions. For my test machine, the settings are

    local j "mssql-jdbc-8.4.1.jre11.jar"
    local c "com.microsoft.sqlserver.jdbc.SQLServerDriver"

Notice the jre11.jar at the end. Stata will not work jre15. Stata works with jre11.

[Luis Pecht]

Thks Kevin, for your quick reply.

I have just downloaded the driver from

http://repo1.maven.org/maven2/com/mi....4.1.jre11.jar

however, it keeps returning:

. jdbc connect, jar("mssql-jdbc-8.4.1.jre11.jar") driverclass("com.microsoft.sqlserver.jdbc.SQLServerDriver") url("jdbc:mssql://...") user("...") password("...")

. jdbc showtables
connection failed; JDBC driver class com.microsoft.sqlserver.jdbc.SQLServerDriver not found
r(681);

Any chance Is this error more an authorization issue, than a unproperly java class call ?

thks,

[Kevin Crow (StataCorp)]

You might try and download the jar file from Microsoft. I downloaded the new driver released in March from

https://docs.microsoft.com/en-us/sql...l-server-ver15

and it seems to work.

. local j "mssql-jdbc-9.2.1.jre11.jar"

. local c "com.microsoft.sqlserver.jdbc.SQLServerDriver"

. local url "jdbc:sqlserver://localhost\sqlexpress:1433;"

. local u "u"

. local p "p"

.
. jdbc conn, jar("`j'") driverclass("`c'") url("`url'") user("`u'") password("`
> p'")

.
. jdbc showtable

Database: master
-------------------------------------------------------------------------------
Tables
-------------------------------------------------------------------------------
spt_fallback_db
spt_fallback_dev
spt_fallback_usg
spt_monitor
trace_xe_action_map
trace_xe_event_map
-------------------------------------------------------------------------------

[Luis Pecht]

Kevin, no success here, even with jar file from Microsoft.

. jdbc showtables
connection failed; JDBC driver class com.microsoft.sqlserver.jdbc.SQLServerDriver not found
r(681);

Tried a mySQL connection and worked just fine, but not the case with a msSQL connection.

So, I will wait for others users reporting , here, similar cases as mine, to solve it .

thanks anyway.

[Kevin Crow (StataCorp)]

One last thing I noticed was

url("jdbc:mssql://...")

should be

jdbc:sqlserver://

Can you post the entire do-file with the database name, username, password, and URL changed?

[Luis Pecht]

Kevin, unfortunately I can not post the connection details here, but it is an Azure Cloud Database in which Stata 16 connects daily, via ODBC.

It seem weird, but this error happens after a another previous jDBC connection was made.

. local url "url"
. local u "u"
. local p "p"

. jdbc connect, jar("mysql-connector-java-8.0.22.jar") driverclass("com.mysql.cj.jdbc.Driver") url("`url'") user("`u'") password("`p'")

. jdbc showtable
connection failed
    No suitable driver found for url
r(681);

. jdbc connect, jar("mssql-jdbc-9.2.1.jre11.jar") driverclass("com.microsoft.sqlserver.jdbc.SQLServerDriver") url("`url'") user("`u'") password("`p'")

. jdbc showtable
connection failed; JDBC driver class com.microsoft.sqlserver.jdbc.SQLServerDriver not found
r(681);

maybe you could try to replicate/confirm that.

thks again,

[Kevin Crow (StataCorp)]

I was able to reproduce this bug and fix it. Currently in Stata 17, switching JDBC drivers in the same Stata session without using discard causes this error. We will release the fix in the next executable update.