B4J Question Connection to Database MSQL

Peter Lewis · Apr 14, 2017

Hi

I have been trying to connect to my database with no luck. I have been thru the tutorials and tried all the examples as well as adding my IP address on the server as one of the ones allowed to access.

I have also used another SQL program and it access my database from my IP address with exactly the same info, I copied and pasted.

It seems like I am missing something

The error code is

Error: (SQLException) java.sql.SQLException: Access denied for user ''@'169.0.105.121' (using password: NO)

Which normally tells me my Ip is not authorised, but it is.

Here is the code, BTW I have changed the usernames and passwords to something else

B4X:

Region  Project Attributes
    #MainFormWidth: 600
    #MainFormHeight: 400
    #AdditionalJar: mysql-connector-java-5.1.41-bin

#End Region

Sub Process_Globals
    Private Fx As JFX
    Private MainForm As Form
    Private Sql1 As SQL
    Private Url, Driver, UserName, PassWord As String
    Dim ListView1 As ListView
End Sub


Sub AppStart (Form1 As Form, Args() As String)
MainForm = Form1
MainForm.RootPane.LoadLayout("1")
MainForm.Show
  
    Try
        Driver = "com.mysql.jdbc.Driver"
        Url = "jdbc:mysql://www.himel.co.za:3306/himel_dia"
        UserName = "himel_peter"
            PassWord = "3749957)P"
         Sql1.Initialize(Driver, Url & ";user=" & UserName & ";password=" & PassWord)
 
    '    sql1.Initialize("com.mysql.jdbc.Driver","jdbc:mysql://localhost/test?characterEncoding=utf8")

Dim rs As ResultSet
rs = Sql1.ExecQuery("SELECT * FROM Customer;")
ListView1.Items.Add(rs) 
Log("result: " & LastException)

    Catch 
Log("Error: " & LastException)
    End Try
End Sub

Thank you for any advice you may provide

EnriqueGonzalez · Apr 14, 2017

Its very likely that by doing this, you are not scaping values correctly:

B4X:

Sql1.Initialize(Driver, Url & ";user=" & UserName & ";password=" & PassWord)

even more so because your password has a ")"

may be if you try with initialize2 and test again.

Peter Lewis · Apr 14, 2017

Enrique Gonzalez R said:
Its very likely that by doing this, you are not scaping values correctly:

B4X:

Sql1.Initialize(Driver, Url & ";user=" & UserName & ";password=" & PassWord)

even more so because your password has a ")"

may be if you try with initialize2 and test again.

I did go and change the password not to include a ")" and also copied the values from B4J on the server info , into the SQL Manager for MySQL and the SQL Manager Worked but not this code.

Here are now the correct URLS and Passwords. It is just a test database so it is not a problem

B4X:

#Region  Project Attributes
    #MainFormWidth: 600
    #MainFormHeight: 400
    #AdditionalJar: mysql-connector-java-5.1.41-bin

#End Region

Sub Process_Globals
    Private Fx As JFX
    Private MainForm As Form
    Private Sql1 As SQL
    Private Url, Driver, UserName, PassWord As String
    Dim ListView1 As ListView
End Sub


Sub AppStart (Form1 As Form, Args() As String)
MainForm = Form1
MainForm.RootPane.LoadLayout("1")
MainForm.Show
    
    Try
        Driver = "com.mysql.jdbc.Driver"
        Url = "jdbc:mysql://www.himel.co.za:3306/himelcoz_diamonds"
        UserName = "himelcoz_testing"
        PassWord = "ZXNb^iVIP1_="
'    Sql1.Initialize(Driver, Url & ";user=" & UserName & ";password=" & PassWord)
   
         Sql1.Initialize(Driver, Url & ";user=" & UserName & ";password=" & PassWord)
   
    '    sql1.Initialize("com.mysql.jdbc.Driver","jdbc:mysql://localhost/test?characterEncoding=utf8")

Dim rs As ResultSet
rs = Sql1.ExecQuery("SELECT * FROM Customer;")
ListView1.Items.Add(rs)   
Log("result: " & LastException)

    Catch   
Log("Error: " & LastException)
    End Try
End Sub

EnriqueGonzalez · Apr 14, 2017

and you are still receving this very same error?

Error: (SQLException) java.sql.SQLException: Access denied for user ''@'169.0.105.121' (using password: NO)

You should really try to use initialize2.

EnriqueGonzalez · Apr 14, 2017

I just have another idea!

just after the URL
"jdbc:mysql://www.himel.co.za:3306/himelcoz_diamonds" write a "?" and not ";"

according to mysql documentation
jdbc:mysql://[host1][:port1][,[host2][:port2]]...[/[database]] »
[?propertyName1=propertyValue1[&propertyName2=propertyValue2]...]

https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html

Peter Lewis · Apr 15, 2017

Enrique Gonzalez R said:
initialize2

Same result

I tried initialize2 but it said that the parameters were incorrect and it would not give me any help of the parameters required

Peter Lewis · Apr 15, 2017

Enrique Gonzalez R said:
I just have another idea!

just after the URL
"jdbc:mysql://www.himel.co.za:3306/himelcoz_diamonds" write a "?" and not ";"

according to mysql documentation
jdbc:mysql://[host1][:port1][,[host2][:port2]]...[/[database]] »
[?propertyName1=propertyValue1[&propertyName2=propertyValue2]...]

https://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-configuration-properties.html

I did try many variations including using the ? as i did see it on one of the boards, I also tried putting in the data directly.

Every time still the same error

Peter Lewis · Apr 15, 2017

These are some of th options I tried

B4X:

Sql1.Initialize(Driver, Url&"?user="&UserName&"password="&password)
   
'    Sql1.Initialize("com.mysql.jdbc.Driver","jdbc:mysql://162.215.252.76:3306/himelcoz_diamonds?"&user = "himelcoz_testing"&password="ZXNb^iVIP1_=")
    '    Sql1.Initialize
    '    Sql1.Initialize("com.mysql.jdbc.Driver", "jdbc:mysql://162.215.252.76:3306/himelcoz_diamonds?" & "user=himelcoz_testing"&"password=ZXNb^iVIP1_=")

Erel · Apr 16, 2017

Peter Lewis said:
I tried initialize2 but it said that the parameters were incorrect and it would not give me any help of the parameters required

Can you post the code?

Peter Lewis · Apr 16, 2017

Erel said:
Can you post the code?

Hi Erel

I found the format on the online manual

I went down the route of Initilise2 as suggested and after changing a few items got it to work

Here is the code

B4X:

    MainForm.Icon = fx.LoadImage(File.DirAssets, "smalldiamond.png")
    Sql1.Initialize2("com.mysql.jdbc.Driver", "jdbc:mysql://162.215.252.76:3306/himelcoz_diamonds", "himelcoz_testing", "ZXNb^iVIP1_=")

    Dim rs As ResultSet = Sql1.ExecQuery("SELECT barc, location , datetime , SigName, signature FROM movement")
    Do While rs.NextRow
        Dim msgs As List
        msgs.Initialize
        msgs.Add(rs.GetString("barc"))
        msgs.Add(rs.GetString("location"))
       
        ListView1.Items.Add(rs.GetString("barc")  &"  "& rs.GetString("location") &"  "& rs.GetString("datetime")&"  "& rs.GetString("SigName")&"  "& rs.GetString("signature"))
     '   ChoiceBox1.
       
       
    Loop
    rs.Close
End Sub

B4J Question Connection to Database MSQL

Peter Lewis

Active Member

EnriqueGonzalez

Well-Known Member

Peter Lewis

Active Member

EnriqueGonzalez

Well-Known Member

EnriqueGonzalez

Well-Known Member

Peter Lewis

Active Member

Peter Lewis

Active Member

Peter Lewis

Active Member

Erel

B4X founder

Peter Lewis

Active Member

Similar Threads

B4J Question Connection to Database MSQL

Active Member

Well-Known Member

Active Member

Well-Known Member

Well-Known Member

Active Member

Active Member

Active Member

B4X founder

Active Member

Similar Threads

Privacy & Transparency

Privacy & Transparency