databasereferencecheatsheet

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
databasereferencecheatsheet [2022/01/02 23:06]
wikiadmin [MySQL] 		databasereferencecheatsheet [2024/01/26 10:59] (current)
amyw [SQL Server Express]
Line 3: Line 3:
 This is a cheat sheet reference for configuration of the beans used in the Reporting package of MIStudio and TransSECS (see [[vibreportinganddatabaseserverbeansdocumentation|Reporting and Historical Servers]]). This is a cheat sheet reference for configuration of the beans used in the Reporting package of MIStudio and TransSECS (see [[vibreportinganddatabaseserverbeansdocumentation|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 cases the name of the database is "db_name" for consistency.+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.
  
 <note important>Important: if given a choice between JRE versions, use a driver compatible with Java 8 (JDK or JRE 8)</note> <note important>Important: if given a choice between JRE versions, use a driver compatible with Java 8 (JDK or JRE 8)</note>
Line 15: Line 15:
  
 **Database URL**: <code> jdbc:sqlserver://hostname:1433;databaseName=db_name </code> **Database URL**: <code> jdbc:sqlserver://hostname:1433;databaseName=db_name </code>
 +
 +[[sql_server_express_connnection_tutorial|More information on configuring and using SQL Server Express for the jdbc driver]]
  
  
Line 21: Line 23:
 The latest community edition of the JDBC driver can be found at: https://dev.mysql.com/downloads/connector/j/ The latest community edition of the JDBC driver can be found at: https://dev.mysql.com/downloads/connector/j/
  
 +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 Driver Class**: com.mysql.cj.jdbc.Driver (this has changed, but older drivers will use com.mysql.jdbc.Driver)
Line 27: Line 30:
   or, with parameters, such as:     or, with parameters, such as:  
  jdbc:mysql://hostname/db_name?socketTimeout=5000&connectTimeout=5000 </code>  jdbc:mysql://hostname/db_name?socketTimeout=5000&connectTimeout=5000 </code>
 +
 +====MariaDB====
 +
 +get the latest JDBC driver: https://mariadb.com/kb/en/about-mariadb-connector-j/
 +
 +**Database Driver Class**: org.mariadb.jdbc.Driver
 +
 +**Database URL**: <code> jdbc:mariadb://hostname:3306/db_name 
 +  or, with parameters, such as:  
 + jdbc:mariadb://hostname:3306/db_name?user=root&password=myPassword </code>
 +
 +====PostgreSQL====
        
 +get the latest JDBC driver: https://jdbc.postgresql.org/
  
 +**Database Driver Class**: org.postgresql.Driver
 +
 +**Database URL**: <code> jdbc:postgresql://hostname:5430/db_name
 +  or, with parameters, such as:  
 + jdbc:postgresql://hostname/db_name?user=fred&password=secret&ssl=true </code>
 +
 +====HSQLDB====
 +
 +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**: <code> jdbc:hsqldb:file:db_name </code>
 +
 +====SQLite====
 +
 +SQLite is a file based database, so the format of the URL will not include the "hostname".
 +   
 +get the latest JDBC driver: https://www.sqlite.org/download.html (the driver is included in the distribution,i.e. sqlite-jdbc-3.27.2.1.jar)
 +
 +**Database Driver Class**: org.sqlite.JDBC
 +
 +**Database URL**: <code> jdbc:sqlite:db_name </code>
 +
 +Note: usually add .sqlite to the file name for SQLite databases, so for example, db_name.sqlite
 +
 +====SQL Server Express====
 +
 +The latest JDBC driver could be found at:
 +
 +[[https://learn.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver16]]
 +
 +JDBC needs to be a Jar in the “drivers” directory of the project.  Right-click the “Drivers” node in MIStudio to add it.
 +
 +{{:jarsfiles.jpg?400|}}
 +
 +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;
  
  
 +  
  
  
  • databasereferencecheatsheet.1641186403.txt.gz
  • Last modified: 2022/01/02 23:06
  • by wikiadmin