This is a cheat sheet reference for configuration of the beans used in the Reporting package of MIStudio and TransSECS (see Reporting and Historical Servers).

In the references below, substitute “hostname” in the Database URL with the ip address of the database server. The format for a cloud connection will be slightly different, and can be found in the references provided when you set up the database in the cloud. In all examples below the name of the database is “db_name” for consistency. In most cases the port is not necessary if you are using the default.

The JDBC driver that is compatible with java8 (jre8) could be found at:

https://repo1.maven.org/maven2/com/microsoft/sqlserver/mssql-jdbc/12.4.2.jre8/

Database Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver

Database URL:

 jdbc:sqlserver://hostname:1433;databaseName=db_name 

More information on configuring and using SQL Server Express for the jdbc driver

The JDBC driver that is compatible with java8 could be found at:

https://repo1.maven.org/maven2/com/mysql/mysql-connector-j/9.2.0/mysql-connector-j-9.2.0.jar

Note: The MariaDB driver (below) will also work for MySQL

Database Driver Class: com.mysql.cj.jdbc.Driver (this has changed, but older drivers will use com.mysql.jdbc.Driver)

Database URL:

 
jdbc:mysql://hostname/db_name,
or, with parameters, such as: 
jdbc:mysql://hostname/db_name?useSSL=false
jdbc:mysql://<hostname>:<port>/<database>?useSSL=false&serverTimezone=UTC
Default port is 3306. The timezone may not be required - depends on the timezone of the server.  
jdbc:mysql://hostname/db_name?socketTimeout=5000&connectTimeout=5000 

get the latest JDBC driver: https://mariadb.com/kb/en/about-mariadb-connector-j/

Database Driver Class: org.mariadb.jdbc.Driver

Database URL:

 jdbc:mariadb://hostname:3306/db_name 
  or, with parameters, such as:  
 jdbc:mariadb://hostname:3306/db_name?user=root&password=myPassword 

get the latest JDBC driver: https://jdbc.postgresql.org/

Database Driver Class: org.postgresql.Driver

Database URL:

 jdbc:postgresql://hostname:5430/db_name
  or, with parameters, such as:  
 jdbc:postgresql://hostname/db_name?user=fred&password=secret&ssl=true 

HSQLDB is a file based database, so the format of the URL will not include the “hostname”.

get the latest JDBC driver: https://sourceforge.net/projects/hsqldb/files/ (its included in the distribution)

see also: http://hsqldb.org/doc/2.0/apidocs/org.hsqldb/org/hsqldb/jdbc/JDBCDriver.html

Database Driver Class: org.hsqldb.jdbc.JDBCDriver

Database URL:

 jdbc:hsqldb:file:db_name 

SQLite is a file based database, so the format of the URL will not include the “hostname”.

get the latest JDBC driver from SourceForge: https://sourceforge.net/projects/sqlite-jdbc-driver.mirror/files/latest/download

Database Driver Class: org.sqlite.JDBC

Database URL:

 jdbc:sqlite:db_name 

Example: jdbc:sqlite:testdb.sqlite

This will create a file called testdb.sqlite in the directory where the application is running. When testing in MIStudio this will be the MIStudio main directory. If you are testing in TransSECS (Builder) this file will appear in the TransSECS/Builder directory.

Note: usually add .sqlite to the file name for SQLite databases, so for example, db_name.sqlite

The JDBC driver that is compatible with Java 8 could be found at:

https://repo1.maven.org/maven2/com/microsoft/sqlserver/mssql-jdbc/12.4.2.jre8/

JDBC needs to be a Jar in the “drivers” directory of the project. Right-click the “Drivers” node in MIStudio to add it.

Choose the first file “mssql-jdbc-12.4.2.jre8.jar” and add it.

close the project and reload it after adding the driver, or you can exit MIStudio and restart.

Set MIStudio to LIVE when connecting to the database.

Database Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver

Database URL:

jdbc:sqlserver://hostname:1433;databaseName=db_name

or with parameters, such as:
 
jdbc:sqlserver://hostname:1433;databaseName=db_name;encrypt=true;trustServerCertificate=true;