### Samples MySQL DB Access

Sample subs to access a MySQL database and tables.
`Sub Process_Globals
...
Private gSQL As SQL
Private gDBConnected As Boolean
End Sub`
Connect to the database
'Host like localhost or 192.168.0.16
'Database like notes
'User like notesuser
'Password like yourpassword
`Sub MySQLConnect(host As String, DB As String, user As String, pw As String) As Boolean
Log("DBConnect :: Start = " & host & ", " & DB & ", " & user & ", " & pw)
Dim result As Boolean = False
Try
'Connect to the mysql db via jdbc: //HOST/DB/USER/PASSWORD
gSQL.Initialize("com.mysql.jdbc.Driver", "jdbc:mysql://" & host & "/" & DB & "?user=" & user & "&password=" & pw)
result = True
Catch
Log(LastException.Message)
End Try
Log("DBConnect :: End = " & result)
Return result
End Sub`
Get a list of tables from the database opened
'Returns a list of tables
`Sub MySQLGetTables As List
Log("DBGetTables :: Start")
Dim l As List : l.Initialize
Dim cursor As ResultSet
Try
cursor = gSQL.ExecQuery ("SHOW tables")
Do While cursor.NextRow
For i = 0 To cursor.ColumnCount - 1
l.Add(cursor.GetString2(i))
Log(cursor.GetColumnName(i) & " = " & cursor.GetString2(i))
Next
Loop
Catch
Log("ERROR: " & LastException.Message)
End Try
l.Sort(True)
Log(">#Tables: " & l.Size)
Log("DBGetTables :: End")
Return l
End Sub`
Select records from the remote mysql db
`Sub MySQLGetColumns(Table As String) As List
Log("MySQLGetColumns :: Start = " & Table)
Log(">Show the columns of the table [notes].")
Dim l As List : l.Initialize
Dim cursor As ResultSet
Try
cursor = gSQL.ExecQuery ("SHOW COLUMNS FROM notes")
Do While cursor.NextRow
Dim m As Map : m.Initialize
For i = 0 To cursor.ColumnCount - 1
m.Put(cursor.GetColumnName(i), cursor.GetString2(i))
Log(">" & cursor.GetColumnName(i) & " = " & cursor.GetString2(i))
Next
Loop
l.Add(m)
Catch
Log("ERROR: " & LastException.Message)
End Try
Log("MySQLGetColumns :: End = Columns: " & l.size)
Return l
End Sub`
Select records from the remote mysql db
`Sub MySQLSelectRecords(Table As String)
Log("Select Records :: Start = " & Table)
Dim cursor As ResultSet
Try
cursor = gSQL.ExecQuery("SELECT * FROM " & Table)
'Get columnnames to add as columnheaders
Dim cl As List : cl.Initialize
For i = 0 To cursor.ColumnCount - 1
cl.Add(cursor.GetColumnName(i))
Next
'Get the data
Dim dl As List : dl.Initialize
Do While cursor.NextRow
Dim m As Map : m.Initialize
For i = 0 To cursor.ColumnCount - 1
m.Put(cursor.GetColumnName(i), cursor.GetString2(i))
Log(">" & cursor.GetColumnName(i) & " = " & cursor.GetString2(i))
Next
dl.Add(m)
Loop
TableView_Create(pnInfo, cl, dl)
Catch
Log("ERROR: " & LastException.Message)
End Try
Log("Select Records :: End")
End Sub`
Add the records to a TableView
'Create a tableview and add to a pane
'p as the pane the tableview is added to
'cl as the list holding the tableview columns
'dl as the list holding the maps with the column data
'Example:
' Dim cl As List : cl.Initialize : cl.AddAll(Array As String("Field","Type","Null","Key","Default","Extra"))
' Dim dl As List : dl.Initialize : dl.AddAll(Array As String("note","varchar(255)","YES","","null",""))
' TableView_Create(pnInfo, cl, dl)
'
`Sub TableView_Create(p As Pane, cl As List, dl As List)
p.RemoveAllNodes
Dim tv As TableView
tv.Initialize("tv")
tv.SetColumns(cl)
For i=0 To tv.ColumnsCount -1
tv.SetColumnWidth(i, ((p.PrefWidth - 5) / tv.ColumnsCount))
Next
p.AddNode(tv, 0, 0, p.PrefWidth, p.PrefHeight)
For i = 0 To dl.Size - 1
Dim m As Map = dl.Get(i)
Dim r(tv.ColumnsCount) As Object
For i = 0 To m.Size -1
r(i) = m.GetValueAt(i)
Next
tv.Items.Add(r)
Next
End Sub`

Changed: , Category: MySQL, File: T6C223M1653.htm, ID: 1653