### Sample MySQL Select Records Remote DB

This is an example of a Non-UI application to connect to a MySQL remote database and select records from a table.
For this example:
• MySQL database notes located on a Raspberry Pi with IP address 192.168.0.58
• MySQL username is notesuser
• MySQL Table is notes
• Libraries used: jSQL
#Region Project Attributes
'Ensure to download and copy the mysql jdbc connector to the additional libraries folder. Set the right version matching the file.
#AdditionalJar: mysql-connector-java-5.1.37-bin
#End Region
`Sub Process_Globals
Private Const CAPPINFO As String = "B4JHowTo - MySQLEx5 v20160526"
Private RemoteIP As String = "192.168.0.58"
Private DBName As String = "notes"
Private Username As String = "notesuser"
Private Password As String = "*****"
Private TableNotes As String = "notes"
Private QuerySelectAllNotes As String = $"SELECT * FROM ${TableNotes}"$
End Sub`
`Sub AppStart (Args() As String)
Log(CAPPINFO)
SelectRecords
ExitApplication
End Sub`
'Select records from the remote mysql db
`Sub SelectRecords
'Define the SQL object
Private sqlObj As SQL
Private jdbcURL As String
'Build the jdbc connection URL, like jdbc:mysql://192.168.0.58/notes?user=notesuser&password=SECRET
jdbcURL = $"jdbc:mysql://${RemoteIP}/${DBName}?user=${Username}&password=${Password}"$
'Init the SQL object with the mysql JDBC driver
Try
sqlObj.Initialize("com.mysql.jdbc.Driver", jdbcURL)
Catch
Log($"Error initializing the remote database connection: ${LastException.Message}"$)
Log(LastException.Message)
Return
End Try
'Get the records
Dim cursor As ResultSet
Dim sb As StringBuilder
sb.Initialize
Try
cursor = sqlObj.ExecQuery (QuerySelectAllNotes)
Log("Records found:")
Do While cursor.NextRow
For i = 0 To cursor.ColumnCount - 1
sb.Append(cursor.GetColumnName(i) & " = " & cursor.GetString2(i) & CRLF)
Next
Loop
Log(sb.ToString)
Catch
Log($"Error selecting records: ${LastException.Message}"$)
End Try
'
Log("Exit program")
End Sub`

Changed: 2018-06-07 09:17, Category: MySQL, File: T6C223M1652.htm, ID: 1652