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

Discussion in 'B4J Tutorials' started by Peter Simpson, Jul 11, 2019.

  1. Peter Simpson

    Peter Simpson Expert 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...
     

    Attached Files:

    Last edited: Sep 25, 2019
  2. Kope

    Kope Member Licensed User

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

    Code:
    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).
     
    Mashiane likes this.
  3. Peter Simpson

    Peter Simpson Expert Licensed User

    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: Jul 15, 2019
    DonManfred likes this.
  4. Kope

    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
     
  5. Peter Simpson

    Peter Simpson Expert Licensed User

    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: Jul 15, 2019
    DonManfred likes this.
  6. rraswisak

    rraswisak Active Member Licensed User

    @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 :)
     
    Peter Simpson likes this.
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice