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

Kope · Jul 14, 2019

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 · Jul 14, 2019

Kope said:
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.

Kope · Jul 15, 2019

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 · Jul 15, 2019

Kope said:
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

rraswisak · Aug 16, 2019

@Peter Simpson thank you for sharing this, i am new for B4J and start to build web service using B4J, and this is very useful

embedded · Feb 28, 2020

Peter Simpson said:
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.

ibs06t · Sep 13, 2020

Hi

its possible to connect remote mysql via b4a....

netsistemas · Sep 15, 2020

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"

swamisantosh · Jan 1, 2021

netsistemas said:
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"

Private MSLocation As String = "192.168.43.191/global:1433;instanceName=ULTIMATE;databaseName=global;user=sa;password=solution.123;encrypt=true;trustServerCertificate=false;loginTimeout=30;"
Private MSUsername As String = "sa"
Private MSPassword As String = "solution.123"

'--global is database

LogError("---------- NorthWind Database (MSSQL) ----------")
SQL.InitializeAsync("MSSQL","net.sourceforge.jtds.jdbc.Driver", $"jdbc:jtds:sqlserver://${MSLocation}/global"$, MSUsername, MSPassword)
SQL.Close
StartMessageLoop 'only required in a console app

Mashiane · Mar 14, 2022

Hi Peter,

can you please share your northwind db, perhaps its the most up to date.

Thanks.

Peter Simpson · Mar 14, 2022

Mashiane said:
Hi Peter,

can you please share your northwind db, perhaps its the most up to date.

Thanks.

I'll share it later for you, I'll have to rip it from my server as it maybe one that I covered...

Peter Simpson · Mar 15, 2022

Mashiane said:
can you please share your northwind db, perhaps its the most up to date.

Hello @Mashiane, please look at the attached file. These are the smallest NW databases that I have on my online server, I actually have larger NW databases on my development machines but they are too large to attach to this post.

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

Attachments

Kope

Active Member

Peter Simpson

Expert

Kope

Active Member

Peter Simpson

Expert

rraswisak

Active Member

embedded

Active Member

ibs06t

Member

netsistemas

Active Member

swamisantosh

Member

Mashiane

Expert

Peter Simpson

Expert

Peter Simpson

Expert

Attachments

Similar Threads