2 single: MySQL Functions; Introduction
8 In this chapter we are going to learn about the MySQL functions provided by the Ring programming language.
21 * MySQL_Escape_String()
26 Before using the next function load the mysqllib.ring library
34 pair: MySQL Functions; MySQL_Info()
39 We can get the MySQL Client version using the MySQL_Info() function.
45 MySQL_Info() ---> string contains the MySQL Client version
51 see "MySQL Client Version : " + mysql_info()
57 MySQL Client Version : 6.1.5
60 pair: MySQL Functions; MySQL_Init()
65 We can start using MySQL Client through the MySQL_Init() function.
71 MySQL_Init() ---> MySQL Handle
74 pair: MySQL Functions; MySQL_Error()
76 MySQL_Error() Function
77 ======================
79 We can get the error message from the MySQL Client using the MySQL_Error() function.
85 MySQL_Error(MySQL Handle) ---> Error message as string
88 pair: MySQL Functions; MySQL_Connect()
90 MySQL_Connect() Function
91 ========================
93 We can connect to the MySQL database server using the MySQL_Connect() function.
99 MySQL_Connect(MySQL Handle, cServer, cUserName, cPassword) ---> lStatus
102 pair: MySQL Functions; MySQL_Close()
104 MySQL_Close() Function
105 ======================
107 We can close the connection to the MySQL database using the MySQL_Close() function
113 MySQL_Close(MySQL Handle)
116 pair: MySQL Functions; MySQL_Query()
118 MySQL_Query() Function
119 ======================
121 We can execute SQL queries using the MySQL_Query() function
127 MySQL_Query(MySQL Handle, cSQLQuery)
130 pair: MySQL Functions; Create Database
135 The next example connect to MySQL Server then create new database.
139 See "MySQL Test - Create Database" + nl
143 if mysql_connect(con,"localhost","root","root") = 0
144 see "Can't connect" + nl
145 see "Error : " + mysql_error(con) + nl
150 See "Create Database..." + nl
151 mysql_query(con,"CREATE DATABASE mahdb")
153 See "Close Connection" + nl
160 MySQL Test - Create Database
166 pair: MySQL Functions; Create Table and Insert Data
168 Create Table and Insert Data
169 ============================
171 The next example create new table and insert records
176 see "Create Table and Insert Records" + nl
180 if mysql_connect(con, "localhost", "root", "root","mahdb") = 0
184 see "Drop table" + nl
185 if mysql_query(con, "DROP TABLE IF EXISTS Employee") system_error(con) ok
187 see "Create table" + nl
188 if mysql_query(con, "CREATE TABLE Employee(Id INT, Name TEXT, Salary INT)")
191 see "Insert data" + nl
192 if mysql_query(con, "INSERT INTO Employee VALUES(1,'Mahmoud',15000)")
195 if mysql_query(con, "INSERT INTO Employee VALUES(2,'Samir',16000)")
198 if mysql_query(con, "INSERT INTO Employee VALUES(3,'Fayed',17000)")
201 see "Close connection" + nl
204 func system_error con
205 see mysql_error(con) mysql_close(con) bye
212 Create Table and Insert Records
220 pair: MySQL Functions; MySQL_Inser_ID()
222 MySQL_Insert_ID() Function
223 ==========================
225 We can get the inserted row id using the MySQL_Insert_ID() function
231 MySQL_Insert_ID() ---> Inserted row id as number
238 see "connect to database" + nl
239 mysql_connect(con,"localhost","root","root","mahdb")
240 see "drop table" + nl
241 mysql_query(con, "DROP TABLE IF EXISTS Customers")
242 see "create table" + nl
243 mysql_query(con, "CREATE TABLE Customers(Id INT PRIMARY KEY AUTO_INCREMENT, Name TEXT)")
244 see "insert record" + nl
245 mysql_query(con, "INSERT INTO Customers(Name) VALUES('Mahmoud')")
246 see "insert record" + nl
247 mysql_query(con, "INSERT INTO Customers(Name) VALUES('Samir')")
248 see "insert record" + nl
249 mysql_query(con, "INSERT INTO Customers(Name) VALUES('Fayed')")
250 see "insert record" + nl
251 mysql_query(con, "INSERT INTO Customers(Name) VALUES('Test 2015')")
253 see "inserted row id : " + mysql_insert_id(con) + nl
254 see "close database" + nl
272 pair: MySQL Functions; MySQL_Result()
274 MySQL_Result() Function
275 =======================
277 We can get the query result (data without column names) using the MySQL_Result() function.
283 MySQL_Result(MySQL Handle) ---> List contains the query result
286 pair: MySQL Functions; MySQL_Next_Result()
288 MySQL_Next_Result() Function
289 ============================
291 We can move to the next query result using the MySQL_Next_Result() function.
292 We use this function when we have multiple SQL statements in the same query.
298 MySQL_Next_Result(MySQL Handle)
301 pair: MySQL Functions; Print Query Result
306 The next example execute a query on the database then print the result.
311 see "Connect to database" + nl
312 mysql_connect(con, "localhost", "root", "root","mahdb")
313 see "Execute Query" + nl
314 mysql_query(con, "SELECT Name FROM Employee WHERE Id=1;"+
315 "SELECT Name FROM Employee WHERE Id=3")
316 see "Print Result" + nl
317 see mysql_result(con)
318 mysql_next_result(con)
319 see mysql_result(con)
320 see "close database" + nl
335 pair: MySQL Functions; MySQL_Columns()
337 MySQL_Columns() Function
338 ========================
340 We can get a list of columns names using the MySQL_Columns() function.
346 MySQL_Columns(MySQL Handle) ---> List contains columns information
353 see "Connect to database" + nl
354 mysql_connect(con, "localhost", "root", "root","mahdb")
355 see "Execute Query" + nl
356 mysql_query(con, "SELECT * FROM Employee")
358 see mysql_columns(con)
359 see "Close database" + nl
384 pair: MySQL Functions; MySQL_Result2()
386 MySQL_Result2() Function
387 ========================
389 Instead of using MySQL_Result() to get the result data without columns names, we can use the MySQL_Result2() to get all of the
390 column names then the query result in one list.
396 MySQL_Result2(MySQL Handle) ---> List (query result starts with columns names)
403 see "Connect to database" + nl
404 mysql_connect(con, "localhost", "root", "root","mahdb")
405 see "Execute Query" + nl
406 mysql_query(con, "SELECT * FROM Employee")
407 see "Print Result" + nl
408 see mysql_result2(con)
409 see "Close database" + nl
435 pair: MySQL Functions; MySQL_Escape_String()
437 MySQL_Escape_String() Function
438 ==============================
440 We can store binary data and special characters in the database after
441 processing using MySQL_Escape_String() function
447 MySQL_Escape_String(MySQL Handle, cString) ---> String after processing
450 pair: MySQL Functions; Save Image Inside the Database
452 Save Image inside the database
453 ==============================
460 cFile = read("tests\mahmoud.jpg")
462 See "Connect to database..." + nl
463 mysql_connect(con, "localhost", "root", "root","mahdb")
464 See "Escape string..." + nl
465 cFile = mysql_escape_string(con,cFile)
466 stmt = "INSERT INTO photo(id, data) VALUES(1, '" + cFile + "')"
467 See "Insert data..." + nl
468 mysql_query(con,stmt)
469 See "Close database..." + nl
477 Connect to database...
483 pair: MySQL Functions; Restore Image From The Database
485 Restore Image From The Database
486 ===============================
493 See "Connect to database..." + nl
494 mysql_connect(con, "localhost", "root", "root","mahdb")
495 See "Read data from database..." + nl
496 mysql_query(con,"SELECT data FROM photo WHERE id=1")
497 See "Write new file" + nl
498 result = mysql_result(con)
499 write("tests\mahmoud2.jpg",result[1][1])
500 See "Close database..." + nl
507 Connect to database...
508 Read data from database...
514 pair: MySQL Functions; MySQL_AutoCommit()
516 MySQL_AutoCommit() Function
517 ===========================
519 We can enable or disable the auto commit feature using the MySQL_AutoCommit() function.
525 MySQL_AutoCommit(MySQL Handle, lStatus) # lstatus can be True/False
528 pair: MySQL Functions; MySQL_Commit()
530 MySQL_Commit() Function
531 =======================
533 We can commit updates to the database using the MySQL_Commit() function.
539 MySQL_Commit(MySQL Handle)
542 pair: MySQL Functions; MySQL_Rollback()
544 MySQL_Rollback() Function
545 =========================
547 We can rollback updates to the database using the MySQL_Rollback() function.
553 MySQL_Rollback(MySQL Handle)
556 pair: MySQL Functions; Transaction Example
561 The next example presents the usage of MySQL_Autocommit(), MySQL_Commit()
562 & MySQL_RollBack() functions.
573 if mysql_connect(con, "localhost", "root", "root","mahdb") = 0
576 see "Drop table" + nl
577 if mysql_query(con, "DROP TABLE IF EXISTS Employee2")
580 see "Create table" + nl
581 if mysql_query(con, "CREATE TABLE Employee2(Id INT, Name TEXT, Salary INT)")
584 see "Insert data" + nl
585 if mysql_query(con, "INSERT INTO Employee2 VALUES(1,'Mahmoud',15000)")
588 if mysql_query(con, "INSERT INTO Employee2 VALUES(2,'Samir',16000)")
591 if mysql_query(con, "INSERT INTO Employee2 VALUES(3,'Fayed',17000)")
594 mysql_autocommit(con,False)
595 mysql_query(con, "INSERT INTO Employee2 VALUES(4,'Ahmed',5000)")
596 mysql_query(con, "INSERT INTO Employee2 VALUES(5,'Ibrahim',50000)")
597 mysql_query(con, "INSERT INTO Employee2 VALUES(6,'Mohammed',50000)")
598 See "Save transaction (y/n) " give nChoice
599 if upper(nChoice) = "Y"
605 see "Close connection" + nl
608 func system_error con
622 Save transaction (y/n) y