B4J Question Connection to Database MSQL

[Peter Lewis]

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

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]

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

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]

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

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

#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]

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]

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]

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]

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]

These are some of th options I tried

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]

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]

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

    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