### Connect using JDBC

To connect to databases via JDBC, you need the JDBC Driver and JDBC URL Format.
Gathered information for following Databases:
MySQL, SQLite, Oracle, FireBird, MS Access
General Information
Download the driver from the reference site.
Copy the driver to the additional libraries folder: Menu Tools > Configure Path > Additional Libraries
Add the driver(s) to the Project Attributes as #AdditionalJar.
Reference the jSQL Library in the B4J IDE Library Manager.
Important:
The driver version used in below information/examples might not be the latest version.
Check out for any latest version.
Look here: http://docs.oracle.com/javase/tutorial/jdbc/ to learn more about JDBC.
List of JDBC Drivers:
http://www.databasedrivers.com/jdbc/
http://www.oracle.com/technetwork/java/index-136695.html
MYSQL DATABASE
Information & Download:
http://dev.mysql.com/downloads/connector/j/
Additional Jar:
mysql-connector-java-5.1.27-bin.jar
Note: If newer versions available, then change the version information accordingly.
JDBC Driver Class:
com.mysql.jdbc.Driver
JDBC URL Format:
jdbc:mysql://localhost/test?characterEncoding=utf8
Example:
Private gSQL As SQL
gSQL.Initialize("com.mysql.jdbc.Driver", "jdbc:mysql://localhost/test?characterEncoding=utf8")
SQLITE DATABASE
Additional Jar:
sqlite-jdbc-3.7.2
JDBC Driver Class:
The SQLite native jar is included in the IDE installation. There is also a helper subroutine called InitializeSQLite.
JDBC URL Format:
There is also a helper subroutine called InitializeSQLite.
Example:
Private gSQL As SQL
gSQL.InitializeSQLite(File.DirApp, "data/1.db", True)
ORACLE DATABASE
Information & Download:
http://razorsql.com/docs/help_oracle.html
Additional Jar:
Oracle Thin JDBC Driver
In order to use the Oracle thin driver, the jar or zip file containing the Oracle thin driver must
be obtained.
Oracle provides this download free of charge, but may require you to register on the Oracle site to
proceed with the download. The Oracle thin Driver is also usually included in the Oracle client
installation. If you have the Oracle client installation on your machine, the location of the thin
drivers should be in the classes12.zip file or the ojdbc14.jar file for newer Oracle versions.
Consult the documentation from Oracle for details.
JDBC Driver Class:
oracle.jdbc.driver.OracleDriver
JDBC URL Format:
jdbc:oracle:thin:@//:/ServiceName
or
jdbc:oracle:thin:@::
FIREBIRD
Information & Download:
http://www.firebirdsql.org/en/jdbc-driver/
Additional Jar:
jaybird-full-2.2.4.jar
JDBC Driver Class:
org.firebirdsql.jdbc.FBDriver
JDBC URL Format:
jdbc:firebirdsql:localhost/3050:?user=USER&password=xyz
Example:
Private gSQL As SQL
gSQL.Initialize("org.firebirdsql.jdbc.FBDriver","jdbc:firebirdsql:localhost/3050:d:\fdb\myfdb.Fdb?user=USER&password=xyz")
MS ACCESS DATABASE
Information & Download:
http://ucanaccess.sourceforge.net/site.html
Additional Jar:
ucanaccess-2.0.2.jar
hsqldb.jar
jackcess-2.0.3.jar
commons-logging-1.0.4.jar
commons-lang-2.6.jar
JDBC Driver Class:
net.ucanaccess.jdbc.UcanaccessDriver
JDBC URL Format:
jdbc:ucanaccess://;memory=true
Example:
Private gSQL As SQL
gstrDBFile = File.DirApp & "\cars.mdb"
gSQL.Initialize("net.ucanaccess.jdbc.UcanaccessDriver", "jdbc:ucanaccess://" & gstrDBFile & ";memory=true")
DBUtils.ExecuteTableView(gSQL, "SELECT * FROM cars", Null, 0, tvwMain)
ORACLE DATABASE
Information & Download:
http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html
http://docs.oracle.com/javase/tutorial/jdbc/
Additional Jar:
ojdbc6.jar
Note: If newer versions avaibale, then change the version information accordingly.
JDBC Driver Class:
oracle.jdbc.driver.OracleDriver
JDBC URL Format:
Old syntax: jdbc:oracle:thin:@[HOST][:PORT]:SID
New syntax: jdbc:oracle:thin:@//[HOST][:PORT]/SERVICE
Example:
Private gSQL As SQL
' Old Syntax
gSQL.Initialize("oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@myhost:1521:orcl")
' New Syntax
gSQL.Initialize("oracle.jdbc.driver.OracleDriver", "jdbc:oracle:thin:@//myhost:1521/orcl")
MS VISUAL FOXPRO
Information & Download:
http://www.caigen.com
This driver supports many formats, like DBase etc.
Note:
The dbfdriver is not for free. Download first from the Caigen website.
Additional Jar:
dbfdriver.jar
JDBC Driver Class:
com.caigen.sql.dbf.DBFDriver
JDBC URL Format:
jdbc:dbf:/./?caseInsensitive=true
Example:
Private gSQL As SQL
' In this Example the path parameter for the driver expects the databasefile in the dirapp folder.
' The database file is opened using the select statement.
' The tablename (territorities) must be the same as the database filename (territorities.dbf) - without extension.
gSQL.Initialize("com.caigen.sql.dbf.DBFDriver", "jdbc:dbf:/./?caseInsensitive=true")
' Query and show the result in a tableview
DBUtils.ExecuteTableView(gSQL, "SELECT * FROM territories", Null, 0, tvwMain)
MICROSOFT SQL SERVER (6.5, 7, 2000, 2005, 2008 AND 2012)
SYBASE ADAPTIVE SERVER ENTERPRISE (10, 11, 12 AND 15)
Information & Download:
http://jtds.sourceforge.net
Additional Jar:
jtds-1.3.1.jar
JDBC Driver Class:
net.sourceforge.jtds.jdbc.Driver
Not Tested.
JDBC URL Format:
jdbc:jtds:://[:][/][;=[;...]]
Not Tested.
Example:
Not Tested.
JAVA DB / APACHE DERBY
Information & Download:
Java DB is Oracle's supported distribution of the Apache Derby open source database.
It supports standard ANSI/ISO SQL through the JDBC and Java EE APIs. Java DB is included in the JDK.
http://www.oracle.com/technetwork/java/javadb/overview/javadb-156712.html
Additional Jar:
AdditionalJar: derby
JDBC Driver Class:
org.apache.derby.jdbc.EmbeddedDriver
JDBC URL Format:
jdbc:derby:[subsubprotocol:][databaseName][;attribute=value]
Reference: http://www.codejava.net/java-se/jdbc/connect-to-apache-derby-java-db-via-jdbc#DerbJdbcConnection
Example:
'Connect to the javadb. A javadb "testdb" is created. A subfolder testdb is created.
Try
'Connect to the JavaDB - installed as part of the JDK (see folder db)
sqlObj.Initialize("org.apache.derby.jdbc.EmbeddedDriver", "jdbc:derby:directory:testdb;create=true")
sqlObj.ExecNonQuery("DROP TABLE mynotes")
sqlObj.ExecNonQuery("CREATE TABLE mynotes (Title VARCHAR(50), Content VARCHAR(255))")
sqlObj.ExecNonQuery ("INSERT INTO mynotes VALUES ('Title Note 1', 'Content Note 1')")
sqlObj.ExecNonQuery ("INSERT INTO mynotes VALUES ('Title Note 2', 'Content Note 2')")
Dim cursor As ResultSet
Dim sb As StringBuilder
sb.Initialize
cursor = sqlObj.ExecQuery ("SELECT * FROM mynotes")
Do While cursor.NextRow
sb.Append("RECORD (Field = Value)" & CRLF)
For i = 0 To cursor.ColumnCount - 1
sb.Append(cursor.GetColumnName(i) & " = " & cursor.GetString2(i) & CRLF)
Next
Loop
Catch
Log(LastException.Message)
End Try
Example using ij.
Run "c:\Program Files (x86)\Java\jdk8\db\bin\ij"
IJ-Version 10.10
ij> CONNECT 'jdbc:derby:testDB;create=true';
ij> CREATE TABLE mynotes (Title varchar(50) , Content varchar(255));
ij> INSERT INTO mynotes VALUES ('Title Note 1', 'Content Note 1');
ij>

Changed: 2018-06-07 09:09, Category: Database, File: T6C181M1304.htm, ID: 1304