B4J Question Having trouble connecting to mysql database

j_o_h_n

Active Member
Licensed User
I first downloaded usbwebserver in order to create a wampserver on my windows 10 machine
I didn't install anything, I just ran the usbwebserver program.
Then using the phpMyAdmin that comes with it I was able to add a database called users and put some data in that.

Then I downloaded Peter Simpsons tutorial on connecting to various databases
Using this I was able to interact with a database on the postgresql server I have installed on my database without any problems at all.
However no matter what I try I'm not having any luck with mysql.

Here is what I think is the relevant part of the program:

B4X:
Region Project Attributes
    #CommandLineArgs:
    #MergeLibraries: True

    'MySQL Connector/J Driver
    #AdditionalJar: mysql-connector-java-5.1.47-bin.jar

#End Region

Sub Process_Globals
    Private SQL As SQL

#Region Database Location
    Private DBLocation As String = "localhost" 'Can use IP address or domain name
    Private DBUsername As String = "root"
    Private DBPassword As String = "usbw"
#End Region
End Sub

Sub AppStart (Args() As String)
    LogError("---------- NorthWind Database (MySQL) ----------")
    SQL.InitializeAsync("MySQL", "com.mysql.jdbc.Driver", $"jdbc:mysql://${DBLocation}/users?useSSL=false"$, DBUsername, DBPassword)
    SQL.Close
    StartMessageLoop 'only required in a console app

After the last line the program waits for a bit and then gives the a whole lot of error logging.
Waiting for debugger to connect...
Program started.
---------- NorthWind Database (MySQL) ----------
com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:990)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:342)
at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2197)
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 java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at java.base/jdk.internal.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.base/java.lang.reflect.Constructor.newInstance(Constructor.java:490)
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/java.sql.DriverManager.getConnection(DriverManager.java:677)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)
at anywheresoftware.b4j.objects.SQL.Initialize2(SQL.java:58)
at anywheresoftware.b4j.objects.SQL$1.call(SQL.java:99)
at anywheresoftware.b4j.objects.SQL$1.call(SQL.java:1)
at anywheresoftware.b4a.BA$4.run(BA.java:272)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
at java.base/java.util.concurrent.FutureTask.run(FutureTask.java:264)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: java.net.ConnectException: Connection refused: connect
at java.base/java.net.PlainSocketImpl.connect0(Native Method)
at java.base/java.net.PlainSocketImpl.socketConnect(PlainSocketImpl.java:101)
at java.base/java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:399)
at java.base/java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:242)
at java.base/java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:224)
at java.base/java.net.SocksSocketImpl.connect(SocksSocketImpl.java:403)
at java.base/java.net.Socket.connect(Socket.java:591)
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:211)
at com.mysql.jdbc.MysqlIO.<init>(MysqlIO.java:301)
... 23 more

The other day I was having what are probably similar issues with the third Harris tutorial on abMaterial.
I could not get the database interaction to happen then either. I'd be very grateful for any help anyone can give.

II#d
 

OliverA

Expert
Licensed User
Longtime User
1) You're closing you DB as quickly as you open it
2) Your async functions must happen outside of AppStart. See: https://www.b4x.com/android/forum/t...t-for-sleep-in-server-handlers.81833/#content
B4X:
Sub AppStart (Args() As String)

    StartDB
    StartMessageLoop 'only required in a console app
    SQL.Close
End Sub

Sub StartDB
    LogError("---------- NorthWind Database (MySQL) ----------")
    SQL.InitializeAsync("MySQL", "com.mysql.jdbc.Driver", $"jdbc:mysql://${DBLocation}/users?useSSL=false"$, DBUsername, DBPassword)
End Sub
 
Upvote 0

j_o_h_n

Active Member
Licensed User
Thank you OliverA
I altered it as you suggested there but unfortunately the same output log appeared.
(Actually, earlier I commented out the SQL.Close line and that also made no difference.)
I think it's either something wrong with my connection string or with the way I setup (even though it's simply a matter of unzipping) usbwebserver
I've run usbwebserver both on a usb key and in a folder on drive C but that made no difference either.
 
Upvote 0

OliverA

Expert
Licensed User
Longtime User
Looks like USBWebserver uses a non-default MySQL port of 3307 (instead of 3306). Makes sense for the purpose of USBWebserver, but it also means you have to include the port number in your JDBC URL
B4X:
#Region Database Location
    Private DBLocation As String = "localhost" 'Can use IP address or domain name
    Private DBUsername As String = "root"
    Private DBPassword As String = "usbw"
    Private DBPort As Int = 3307
#End Region

B4X:
Sub StartDB
    LogError("---------- NorthWind Database (MySQL) ----------")
    SQL.InitializeAsync("MySQL", "com.mysql.jdbc.Driver", $"jdbc:mysql://${DBLocation}:${DBPort}/users?useSSL=false"$, DBUsername, DBPassword)
End Sub

Edit: USBWebserver docs: http://www.usbwebserver.net/downloads/manual.pdf
 
Upvote 0

j_o_h_n

Active Member
Licensed User
Wow! Thank you so much for going to the trouble to find that out for me. It worked!
 
Upvote 0
Top