Skip to Content

Wiki source code of IIOT V-BOX KẾT NỐI MYSQL

Version 1.1 by MinhTam Chau on 2025/10/14 18:18
Show last authors
1 = IIOT V-BOX KẾT NỐI MYSQL =
2
3
4 ~* Các phần mềm hỗ trợ MySQL
5
6 [[image:1760435953217-245.png]]
7
8 -Phần mềm install MySQL : [[https:~~/~~/dev.mysql.com/downloads/installer/>>https://dev.mysql.com/downloads/installer/]]
9
10 -Phần mềm MySQL WorkBench: [[https:~~/~~/downloads.mysql.com/archives/workbench/>>https://downloads.mysql.com/archives/workbench/]]
11
12 -Phần mềm Navicat: [[https:~~/~~/www.navicat.com/en/products/navicat-for-mysql>>https://www.navicat.com/en/products/navicat-for-mysql||style="background-color: rgb(255, 255, 255);"]]
13
14 -Phần mềm HeidiSQL:     [[https:~~/~~/www.heidisql.com/download.php#google_vignette/>>https://www.heidisql.com/download.php#google_vignette/]]
15
16 ~* Thiết lập với MySQL Workbench
17
18 [[image:1760436159071-115.png||height="370" width="995"]]
19
20 - Tạo new connections và kết nối với MySQL
21
22 [[image:1760436190808-409.png||height="399" width="1036"]]
23
24
25 - Tại mục User and Privileges . Hiệu chỉnh User sẽ sử dụng với Limit to Hosts Matching  = %
26
27 [[image:1760436271555-690.png||height="404" width="1034"]]
28
29
30 Setting roles tại muc Administrator Roles
31
32 [[image:1760436552647-583.png||height="405" width="1027"]]
33
34 Có thể thiết lập User bằng lệnh
35
36
37 [[image:1760436598010-851.png||height="360" width="325"]][[image:1760436610001-831.png||height="492" width="702"]]
38
39 Tạo một table chứa dữ liệu
40 VD : Table “ Student” với columm “num, name, age”
41
42
43 [[image:1760436643889-627.png]]
44
45 Sau khi tạo xong sẽ có một bảng dữ liệu với các trường đã khai báo
46
47
48 **V-Box Script**
49
50 ~-~- ~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~##~-~-
51
52 mysql = require("mysqlclient")
53 \\function DataInitRight()
54 local dbName = “vbox_connection"
55 local user = “Vbox"
56 local pwd = “password"
57 local host = "192.168.1.40"
58 local port = 3306
59 local character = "utf8mb4"
60
61 mysql.init(dbName, user, pwd, host, port, character)
62 end
63 \\function ExecFunc()
64 status, errorString = mysql.exec("delete from student where Name = 'XXX';")
65
66 ~-~-Delete statement, column name = table element
67 if nil == status then
68 print("ExecFunc() error:", errorString)
69 return -1
70 else
71 print("the number of rows affected by the command:", status)
72 end
73 return 0
74 end
75 function ExecWithResultFunc()
76 status, errorString = mysql.execWithResult("select * from student;")
77 if nil == status then
78 print("ExecWithResultFunc() error:", errorString)
79 return -1
80 else
81
82 print("ExecWithResultFunc() success : status type = ", type(status))
83 print("ExecWithResultFunc() success : status len = ", #status)
84 local num = #status
85 local i = 1
86 if num > 0 then
87 for i = 1, num, 1 do
88 local var = string.format("select result[%d] :Num = %d，Name = %s，Age = %d", i, status[i].Num, status[i].Name,status[i].Age)
89
90 ~-~-Iterate through the data in the table, noting whether the elements are strings or numbers        
91 print(var)
92 end
93 end
94 print("~-~-~-~-~-~-~-~-~-~-~-~-~-~--")
95 end
96 return 0
97 end
98
99
100 function MySQL.main()
101 print("script running ...")
102 DataInitRight()
103 ~-~- use exec demo
104 if ExecFunc() < 0 then
105 return
106 end
107 ~-~- use execWithResult demo
108 if ExecWithResultFunc() < 0 then
109 return
110 end
111 \\ print("script running success")
112 end
113
114 ~-~- ~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~#~-~-
115
116
117 **MySQL.init( string sourcename, string username, string password, string host, number port, string character )**
118
119 -Khai báo kết nối với mySQL
120
121 -String sourcename : database source name (dbname)
122
123 -String username : user name login MySQL
124
125 -String password: password login MySQL cùng với user name tương ứng
126
127 -String Host: Tên Host, chỉ mục lưu trữ MySQL
128
129 - Number port : Port sử dụng cho MySQL ( mặc định 3306 )
130
131 -String character : chuẩn kí tự sử dụng
132
133
134 Ví dụ
135
136 local dbName = “vbox_connection"
137 local user = “Vbox"
138 local pwd = “password"
139 local host = "192.168.1.40"
140 local port = 3306
141 local character = "utf8mb4"   
142 mysql.init(dbName, user, pwd, host, port, character)
143
144
145 **~* MySql.exec(string statement)**
146
147 -Thực hiện lệnh gửi đến MySQL và không cần nhận thông tin phản hồi
148 VD:
149
150 status, errorString = mysql.exec("delete from student where Name = 'XXX
151
152 if nil == status then
153
154 print("ExecFunc() error:", errorString)
155
156 return -1
157
158 else
159
160 print("the number of rows affected by the command:", status)
161
162 end
163
164 return 0
165
166
167 **~* MySql.execWithResult(string statement)**
168
169 -Thực hiện lệnh gửi đến MySQL và nhận thông tin phản hồi
170 VD:
171
172 status, errorString = mysql.execWithResult("select * from student;")
173
174 if nil == status then
175
176 print("ExecWithResultFunc() error:", errorString)
177
178 return -1
179
180 else
181
182 print("ExecWithResultFunc() success : status type = ", type(status))
183
184 print("ExecWithResultFunc() success : status len = ", #status)
185
186 local num = #status
187
188 local i = 1
189
190 if num > 0 then
191
192 for i = 1, num, 1 do
193
194 local var = string.format("select result[%d] :Num = %d，Name = %s，Age = %d", i, status[i].num, status[i].name,status[i].age)
195
196 print(var)
197
198 end
199
200 end
201
202 print("~-~-~-~-~-~-~-~-~-~-~-~-~-~--")
203
204 end
205
206 return 0
207
208
209 **Kết quả**
210
211 [[image:1760436978587-311.png||height="502" width="617"]]
212
213 **~* Một số lỗi thường gặp**
214
215 [[image:1760437024647-385.png||height="566" width="993"]]
216
217 -MySQL không cấp quyền truy cập dữ liệu
218
219 à  Kiểm tra lại phần cấp quyền Administrator Roles
220
221
222 -MySQL: Host  192.168.XXX.XXX  is not allowed to connect.
223
224 à  Kiểm tra khai báo source, host, user name, password, port ….
225
226
227 -MySQL Error :
228
229 -Error 1175 :  You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.  0.000 sec
230
231 Go to Edit ~-~-> Preferences
232
233 Click "SQL Editor" tab and uncheck "Safe Updates" check box
234
235 Query ~-~-> Reconnect to Server ~/~/ logout and then login
236
237 Now execute your SQL query
238
239
240 -Error Code: 1046. No database selected Select the default DB to be used by double-clicking its name in the SCHEMAS list in the sidebar.  0.000 sec
241
242 à  Set as default SCHEMAS
243
244
245
246