### Create Table Example 2

Example creating a mysql table on a Raspberry Pi (Name 33, IP Address:192.168.0.10)
Login Raspberry Pi
As user pi.
Login mysql as user root
pi@33:~ $ mysql -uroot -hlocalhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 5.5.53-0+deb8u1 (Raspbian)
Create the Database flights and grant priviledges
mysql> CREATE DATABASE flights;
mysql> CREATE USER 'flightsuser'@'localhost' IDENTIFIED BY 'fu';
mysql> GRANT ALL PRIVILEGES ON flights.* TO 'flightsuser'@'localhost';
mysql> FLUSH PRIVILEGES;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'flightsuser'@'%' identified by 'fu';
mysql> FLUSH PRIVILEGES;
mysql> select host, user from mysql.user where user='flightsuser';
+-----------+-------------+

# host | user |

+-----------+-------------+

# % | flightsuser |

# localhost | flightsuser |

+-----------+-------------+
mysql> exit
Create the Table flights and insert records
pi@33:~ $ mysql -uflightsuser -hlocalhost flights -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 43
Server version: 5.5.53-0+deb8u1 (Raspbian)
mysql> CREATE TABLE flights (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,flightno VARCHAR(50));
mysql> INSERT INTO flights(flightno) VALUES ('flightno 1');
mysql> INSERT INTO flights(flightno) VALUES ('flightno 2');
mysql> INSERT INTO flights(flightno) VALUES ('flightno 3');
mysql> INSERT INTO flights(flightno) VALUES ('flightno 4');
mysql> INSERT INTO flights(flightno) VALUES ('flightno 5');
mysql> SELECT * FROM flights;
+----+------------+

# id | flightno |

+----+------------+

# 1 | flightno 1 |

# 2 | flightno 2 |

# 3 | flightno 3 |

# 4 | flightno 4 |

# 5 | flightno 5 |

+----+------------+
mysql> SELECT * FROM flights WHERE flightno LIKE 'flightno 4%';
+----+------------+

# id | flightno |

+----+------------+

# 4 | flightno 4 |

+----+------------+
B4J Non-UI Example
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.39-bin
End Region
`Sub Process_Globals
Private RemoteIP As String = "192.168.0.10"
Private DBName As String = "flights"
Private Username As String = "flightsuser"
Private Password As String = "fu"
Private TableFlights As String = "flights"
Private QuerySelectAllFlights As String = $"SELECT * FROM ${TableFlights}"$
End Sub`
`Sub AppStart (Args() As String)
SelectAllRecords
SelectRecordsByQuery
ExitApplication
End Sub`
`Sub SelectAllRecords
Private sqlObj As SQL
Private jdbcURL As String
jdbcURL = $"jdbc:mysql://${RemoteIP}/${DBName}?user=${Username}&password=${Password}"$
Try
sqlObj.Initialize("com.mysql.jdbc.Driver", jdbcURL)
Catch
Log($"Error initializing the remote database connection: ${LastException.Message}"$)
Log(LastException.Message)
Return
End Try
Dim cursor As ResultSet
Dim sb As StringBuilder
sb.Initialize
Try
cursor = sqlObj.ExecQuery (QuerySelectAllFlights)
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
End Sub`
`Sub SelectRecordsByQuery
Private sqlObj As SQL
Private jdbcURL As String
jdbcURL = $"jdbc:mysql://${RemoteIP}/${DBName}?user=${Username}&password=${Password}"$
Try
sqlObj.Initialize("com.mysql.jdbc.Driver", jdbcURL)
Catch
Log($"Error initializing the remote database connection: ${LastException.Message}"$)
Log(LastException.Message)
Return
End Try
Dim cursor As ResultSet
Dim sb As StringBuilder
sb.Initialize
Try
' Select a single record using LIKE defined as var
Dim fno As String = "flightno 1"
'Dim fno As String = "flightno 4" & "%"
cursor = sqlObj.ExecQuery2($"SELECT * FROM ${TableFlights} WHERE flightno LIKE ?;"$, Array As String(fno & "%"))
' Select a single record using LIKE
' cursor = sqlObj.ExecQuery2($"SELECT * FROM ${TableFlights} WHERE flightno LIKE ?;"$, Array As String("flightno 4%"))
' Select a single record using =
'cursor = sqlObj.ExecQuery2($"SELECT * FROM ${TableFlights} WHERE flightno = ?;"$, Array As String("flightno 4"))
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
End Sub`

Changed: , Category: MySQL, File: T6C223M1650.htm, ID: 1650