B4J Tutorial JDBCSQL Connector/J - Connect directly to MySQL, MSSQL, PostgreSQL, MariaDB and Oracle databases

Peter Simpson

Expert
Licensed User
Hello fellow B4X'ers,
Here is some example code for connecting your B4J projects to 5 different types of database engines, this tutorial is for learning purposes only and nothing else.

Disclaimer:
This code with some minor changes may also work in B4A. MySQL and MSSQL connectors definitely do work in B4A but I'm not sure about the PostgreSQL, MariaDB and Oracle connectors. Please check on the corresponding connector/J developers website for more information about using and distributing their connector/J drivers.

Directions:
First download and copy the 5 JDBC Cconnector/J files into your additional libraries folder, then open the attached B4J project and set the DBLocation, DBUsername and DBPassword for your particular databases, that's all you have to do.

Download link for all 5 JDBC Connector/J drivers:
https://www.dropbox.com/sh/q2h7qmiwrparzru/AACmjXllUKcYF1XhNOrYkGh3a?dl=0

In my attached example the MariaDB Connector/J is deliberately connecting to a MySQL database, this is because the MariaDB Connector/J can connect to both MySQL and MariaDB database engines with ease. Both database formats were founded by Michael Widenius.

Update file: I've added Oracle Connector/J
I decided to add Oracle Connector/J to the attached file named Database5ConnectionTypes.zip, so download that file instead.

Picked up _JAVA_OPTIONS: -Xmx1024m -Xms1024m -XX:-UseGCOverheadLimit
Waiting for debugger to connect...
Program started.
---------- Northwind Database (MySQL) ----------
categories
customercustomerdemo
customerdemographics
customers
employees
employeeterritories
order details
orders
products
region
shippers
suppliers
territories
---------- Northwind Database (MSSQL) ----------
CustomerDemographics
Region
Employees
Categories
Customers
Shippers
Suppliers
Orders
Products
Order Details
CustomerCustomerDemo
Territories
EmployeeTerritories
---------- Northwind Database (PostgreSQL) ----------
us_states
customers
orders
employees
shippers
products
order_details
categories
suppliers
region
territories
employee_territories
customer_demographics
customer_customer_demo
---------- Northwind Database (MariaDB) ----------
categories
customercustomerdemo
customerdemographics
customers
employees
employeeterritories
order details
orders
products
region
shippers
suppliers
territories
---------- XE Database (Oracle) ----------
LOGMNRGGC_GTLO
LOGMNRGGC_GTCS
LOGMNR_PARAMETER$
LOGMNR_SESSION$
ROLLING$CONNECTIONS
ROLLING$DATABASES
ROLLING$DIRECTIVES
ROLLING$EVENTS
ROLLING$PARAMETERS
ROLLING$PLAN
ROLLING$STATISTICS
ROLLING$STATUS
MVIEW$_ADV_WORKLOAD
MVIEW$_ADV_BASETABLE
MVIEW$_ADV_SQLDEPEND
MVIEW$_ADV_PRETTY
MVIEW$_ADV_TEMP
MVIEW$_ADV_FILTER
MVIEW$_ADV_LOG
MVIEW$_ADV_FILTERINSTANCE
MVIEW$_ADV_LEVEL
MVIEW$_ADV_ROLLUP
MVIEW$_ADV_AJG
MVIEW$_ADV_FJG
MVIEW$_ADV_GC
MVIEW$_ADV_CLIQUE
MVIEW$_ADV_ELIGIBLE
MVIEW$_ADV_OUTPUT
MVIEW$_ADV_EXCEPTIONS
MVIEW$_ADV_PARAMETERS
MVIEW$_ADV_INFO
MVIEW$_ADV_JOURNAL
MVIEW$_ADV_PLAN
AQ$_QUEUE_TABLES
AQ$_QUEUES
AQ$_SCHEDULES
AQ$_INTERNET_AGENTS
AQ$_INTERNET_AGENT_PRIVS
SCHEDULER_PROGRAM_ARGS_TBL
SCHEDULER_JOB_ARGS_TBL
LOGSTDBY$PARAMETERS
HELP
SQLPLUS_PRODUCT_PROFILE
LOGMNR_GT_TAB_INCLUDE$
LOGMNR_GT_USER_INCLUDE$
LOGMNR_GT_XID_INCLUDE$
LOGMNRT_MDDL$
OL$
OL$HINTS
OL$NODES
LOGMNR_DICTSTATE$
LOGMNRC_GTLO
LOGMNRC_GTCS
LOGMNRC_SEQ_GG
LOGMNRC_CON_GG
LOGMNRC_CONCOL_GG
LOGMNRC_IND_GG
LOGMNRC_INDCOL_GG
LOGMNRC_SHARD_TS
LOGMNRC_TSPART
LOGMNRC_TS
LOGMNRC_GSII
LOGMNRC_GSBA
LOGMNR_SEED$
LOGMNR_DICTIONARY$
LOGMNR_OBJ$
LOGMNR_TAB$
LOGMNR_COL$
LOGMNR_ATTRCOL$
LOGMNR_TS$
LOGMNR_IND$
LOGMNR_USER$
LOGMNR_TABPART$
LOGMNR_TABSUBPART$
LOGMNR_TABCOMPART$
LOGMNR_TYPE$
LOGMNR_COLTYPE$
LOGMNR_ATTRIBUTE$
LOGMNR_LOB$
LOGMNR_CON$
LOGMNR_CONTAINER$
LOGMNR_CDEF$
LOGMNR_CCOL$
LOGMNR_ICOL$
LOGMNR_LOBFRAG$
LOGMNR_INDPART$
LOGMNR_INDSUBPART$
LOGMNR_INDCOMPART$
LOGMNR_LOGMNR_BUILDLOG
LOGMNR_NTAB$
LOGMNR_OPQTYPE$
LOGMNR_SUBCOLTYPE$
LOGMNR_KOPM$
LOGMNR_PROPS$
LOGMNR_ENC$
LOGMNR_REFCON$
LOGMNR_IDNSEQ$
LOGMNR_PARTOBJ$
LOGMNRP_CTAS_PART_MAP
LOGMNR_SHARD_TS
LOGSTDBY$APPLY_PROGRESS
LOGMNR_SESSION_EVOLVE$
LOGMNR_GLOBAL$
LOGMNR_PDB_INFO$
LOGMNR_DID$
LOGMNR_UID$
LOGMNRC_DBNAME_UID_MAP
LOGMNR_LOG$
LOGMNR_PROCESSED_LOG$
LOGMNR_SPILL$
LOGMNR_AGE_SPILL$
LOGMNR_RESTART_CKPT_TXINFO$
LOGMNR_ERROR$
LOGMNR_RESTART_CKPT$
LOGMNR_FILTER$
LOGMNR_SESSION_ACTIONS$
REDO_DB
REDO_LOG
LOGSTDBY$EVENTS
LOGSTDBY$APPLY_MILESTONE
LOGSTDBY$SCN
LOGSTDBY$FLASHBACK_SCN
LOGSTDBY$PLSQL
LOGSTDBY$SKIP_TRANSACTION
LOGSTDBY$SKIP
LOGSTDBY$SKIP_SUPPORT
LOGSTDBY$HISTORY
LOGSTDBY$EDS_TABLES

Enjoy...
 

Attachments

Last edited:

Kope

Member
Licensed User
Great... but i tried using an IP Address but had an error. Am using Xampp. It works fine with LocalHost

B4X:
java.sql.SQLException: Access denied for user 'root'@'DESKTOP-O6QVCU0' (using password: NO)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3978)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3914)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:871)
    at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1714)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1224)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2199)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2230)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2025)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:778)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:47)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
    at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:386)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:330)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:247)
    at anywheresoftware.b4j.objects.SQL.Initialize2(SQL.java:55)
    at anywheresoftware.b4j.objects.SQL$1.call(SQL.java:89)
    at anywheresoftware.b4j.objects.SQL$1.call(SQL.java:1)
    at anywheresoftware.b4a.BA$4.run(BA.java:272)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)
Program terminated (StartMessageLoop was not called).
 

Peter Simpson

Expert
Licensed User
Great... but i tried using an IP Address but had an error. Am using Xampp. It works fine with LocalHost
Hello Hope,
That's not an IP address issue, you have to grant the correct permission to the user on the IP address.

The logs in my original post were connected to the databases using the IP address of my online server which starts with 188.xx.xxx.xxx.

Update: I've just downloaded and installed XAMPP V7.3.7 and also the above library
Database5ConnectionTypes.zip onto my spare development laptop, XAMPP works perfect using both localhost and 127.0.0.1.
 
Last edited:

Kope

Member
Licensed User
Am still trying to resolve the issue but haven’t yet.

I installed xampp on a spare laptop. Which also has the database. I have my App on my Working Laptop but want to get the data from my Spare Laptop. I used the IP Address from my Spare Laptop in the App which gave me the error.

Will be grateful if this could be resolved

Thank You
 

Peter Simpson

Expert
Licensed User
Will be grateful if this could be resolved
You need to grant the correct permission on your spare laptop. You should do a quick Google search for 'mysql grant all privileges to root', you will find the answer. It will look something like this 'GRANT ALL ON *.* TO 'root'@'...' WITH GRANT OPTION;', but it's not something I do on a daily basis, so you should double check on Google first. Once you have your XAMPP server privileges sorted out, it will work for you as expected
 
Last edited:

embedded

Member
Licensed User
Hello fellow B4X'ers,
Here is some code for connecting your B4J projects to 5 different types of databases.

Disclaimer:
This code with some minor changes may also work in B4A. MySQL and MSSQL connectors definitely do work with B4A but I'm not sure about the PostgreSQL, MariaDB and Oracle connectors.

This was created because I needed a simple way to connect to multiple databases types. Using the attached project you can use this JDBCSQL B4J code to connect to either MySQL, MS SQL, PostgreSQL, MariaDB or Oracle databases with ease, thank you Erel.

Directions:
First download and copy the 5 JDBC Cconnector/J files into your additional libraries folder, then open the attached B4J project and set the DBLocation, DBUsername and DBPassword for your particular databases, that's all you have to do.

Download link for all 5 JDBC Connector/J drivers:
https://www.dropbox.com/sh/q2h7qmiwrparzru/AACmjXllUKcYF1XhNOrYkGh3a?dl=0

In my attached example the MariaDB Connector/J is deliberately connecting to a MySQL database, this is because the MariaDB Connector/J can connect to both MySQL and MariaDB databases with ease. Both database formats were founded by Michael Widenius.

Update file: I've added Oracle Connector/J
I decided to add Oracle Connector/J to the attached file named Database5ConnectionTypes.zip, so download and use that file.

Picked up _JAVA_OPTIONS: -Xmx1024m -Xms1024m -XX:-UseGCOverheadLimit
Waiting for debugger to connect...
Program started.
---------- Northwind Database (MySQL) ----------
categories
customercustomerdemo
customerdemographics
customers
employees
employeeterritories
order details
orders
products
region
shippers
suppliers
territories
---------- Northwind Database (MSSQL) ----------
CustomerDemographics
Region
Employees
Categories
Customers
Shippers
Suppliers
Orders
Products
Order Details
CustomerCustomerDemo
Territories
EmployeeTerritories
---------- Northwind Database (PostgreSQL) ----------
us_states
customers
orders
employees
shippers
products
order_details
categories
suppliers
region
territories
employee_territories
customer_demographics
customer_customer_demo
---------- Northwind Database (MariaDB) ----------
categories
customercustomerdemo
customerdemographics
customers
employees
employeeterritories
order details
orders
products
region
shippers
suppliers
territories
---------- XE Database (Oracle) ----------
LOGMNRGGC_GTLO
LOGMNRGGC_GTCS
LOGMNR_PARAMETER$
LOGMNR_SESSION$
ROLLING$CONNECTIONS
ROLLING$DATABASES
ROLLING$DIRECTIVES
ROLLING$EVENTS
ROLLING$PARAMETERS
ROLLING$PLAN
ROLLING$STATISTICS
ROLLING$STATUS
MVIEW$_ADV_WORKLOAD
MVIEW$_ADV_BASETABLE
MVIEW$_ADV_SQLDEPEND
MVIEW$_ADV_PRETTY
MVIEW$_ADV_TEMP
MVIEW$_ADV_FILTER
MVIEW$_ADV_LOG
MVIEW$_ADV_FILTERINSTANCE
MVIEW$_ADV_LEVEL
MVIEW$_ADV_ROLLUP
MVIEW$_ADV_AJG
MVIEW$_ADV_FJG
MVIEW$_ADV_GC
MVIEW$_ADV_CLIQUE
MVIEW$_ADV_ELIGIBLE
MVIEW$_ADV_OUTPUT
MVIEW$_ADV_EXCEPTIONS
MVIEW$_ADV_PARAMETERS
MVIEW$_ADV_INFO
MVIEW$_ADV_JOURNAL
MVIEW$_ADV_PLAN
AQ$_QUEUE_TABLES
AQ$_QUEUES
AQ$_SCHEDULES
AQ$_INTERNET_AGENTS
AQ$_INTERNET_AGENT_PRIVS
SCHEDULER_PROGRAM_ARGS_TBL
SCHEDULER_JOB_ARGS_TBL
LOGSTDBY$PARAMETERS
HELP
SQLPLUS_PRODUCT_PROFILE
LOGMNR_GT_TAB_INCLUDE$
LOGMNR_GT_USER_INCLUDE$
LOGMNR_GT_XID_INCLUDE$
LOGMNRT_MDDL$
OL$
OL$HINTS
OL$NODES
LOGMNR_DICTSTATE$
LOGMNRC_GTLO
LOGMNRC_GTCS
LOGMNRC_SEQ_GG
LOGMNRC_CON_GG
LOGMNRC_CONCOL_GG
LOGMNRC_IND_GG
LOGMNRC_INDCOL_GG
LOGMNRC_SHARD_TS
LOGMNRC_TSPART
LOGMNRC_TS
LOGMNRC_GSII
LOGMNRC_GSBA
LOGMNR_SEED$
LOGMNR_DICTIONARY$
LOGMNR_OBJ$
LOGMNR_TAB$
LOGMNR_COL$
LOGMNR_ATTRCOL$
LOGMNR_TS$
LOGMNR_IND$
LOGMNR_USER$
LOGMNR_TABPART$
LOGMNR_TABSUBPART$
LOGMNR_TABCOMPART$
LOGMNR_TYPE$
LOGMNR_COLTYPE$
LOGMNR_ATTRIBUTE$
LOGMNR_LOB$
LOGMNR_CON$
LOGMNR_CONTAINER$
LOGMNR_CDEF$
LOGMNR_CCOL$
LOGMNR_ICOL$
LOGMNR_LOBFRAG$
LOGMNR_INDPART$
LOGMNR_INDSUBPART$
LOGMNR_INDCOMPART$
LOGMNR_LOGMNR_BUILDLOG
LOGMNR_NTAB$
LOGMNR_OPQTYPE$
LOGMNR_SUBCOLTYPE$
LOGMNR_KOPM$
LOGMNR_PROPS$
LOGMNR_ENC$
LOGMNR_REFCON$
LOGMNR_IDNSEQ$
LOGMNR_PARTOBJ$
LOGMNRP_CTAS_PART_MAP
LOGMNR_SHARD_TS
LOGSTDBY$APPLY_PROGRESS
LOGMNR_SESSION_EVOLVE$
LOGMNR_GLOBAL$
LOGMNR_PDB_INFO$
LOGMNR_DID$
LOGMNR_UID$
LOGMNRC_DBNAME_UID_MAP
LOGMNR_LOG$
LOGMNR_PROCESSED_LOG$
LOGMNR_SPILL$
LOGMNR_AGE_SPILL$
LOGMNR_RESTART_CKPT_TXINFO$
LOGMNR_ERROR$
LOGMNR_RESTART_CKPT$
LOGMNR_FILTER$
LOGMNR_SESSION_ACTIONS$
REDO_DB
REDO_LOG
LOGSTDBY$EVENTS
LOGSTDBY$APPLY_MILESTONE
LOGSTDBY$SCN
LOGSTDBY$FLASHBACK_SCN
LOGSTDBY$PLSQL
LOGSTDBY$SKIP_TRANSACTION
LOGSTDBY$SKIP
LOGSTDBY$SKIP_SUPPORT
LOGSTDBY$HISTORY
LOGSTDBY$EDS_TABLES

Enjoy...
Great Tutorial....This tutorial open door for infinite possibility for my next projects....A ton of thanks to Peter...Namsate from India.
 

netsistemas

Active Member
Licensed User
Be carefoul:

Conect to sql server error with name server type MACHINE\INSTANCE (slash \ or 'contra barra' in spanish)
Server name:
- Error: 192.168.1.115\sql2017
- Error DESKTOP-MIPC\sql2017
- Find, and replace the instance with port, and put some like this:
Private DBLocation As String = "DESKTOP-JOT5NCL:56874"
 
Top