Ring/Lessons/MySQL Functions

MySQL Functions In this chapter we are going to learn about the MySQL functions provided by the Ring programming language. Before using the functions load the mysqllib.ring library: load "mysqllib.ring" MySQL_Info() MySQL_Init() MySQL_Error() MySQL_Connect() MySQL_Close() MySQL_Query() MySQL_Insert_ID() MySQL_Result() MySQL_Next_Result() MySQL_Columns() MySQL_Result2() MySQL_Escape_String() MySQL_AutoCommit() MySQL_Commit() MySQL_Rollback() MySQL_Info() Function We can get the MySQL Client version using the MySQL_Info() function. Syntax: MySQL_Info() ---> string contains the MySQL Client version Example: see "MySQL Client Version : " + mysql_info() Output: MySQL Client Version : 6.1.5 MySQL_Init() Function We can start using MySQL Client through the MySQL_Init() function. Syntax: MySQL_Init() ---> MySQL Handle MySQL_Error() Function We can get the error message from the MySQL Client using the MySQL_Error() function. Syntax: MySQL_Error(MySQL Handle) ---> Error message as string MySQL_Connect() Function We can connect to the MySQL database server using the MySQL_Connect() function. Syntax: MySQL_Connect(MySQL Handle, cServer, cUserName, cPassword) ---> lStatus MySQL_Close() Function We can close the connection to the MySQL database using the MySQL_Close() function Syntax: MySQL_Close(MySQL Handle) MySQL_Query() Function We can execute SQL queries using the MySQL_Query() function Syntax: MySQL_Query(MySQL Handle, cSQLQuery) Create Database The next example connect to MySQL Server then create new database. See "MySQL Test - Create Database" + nl con = mysql_init() See "Connect" + nl if mysql_connect(con,"localhost","root","root") = 0 see "Cann't connect" + nl see "Error : " + mysql_error(con) + nl mysql_close(con) bye ok See "Create Database..." + nl mysql_query(con,"CREATE DATABASE mahdb") See "Close Connection" + nl mysql_close(con) Output: MySQL Test - Create Database Connect Create Database... Close Connection Create Table and Insert Data The next example create new table and insert records func main see "Create Table and Insert Records" + nl con = mysql_init() see "Connect" + nl if mysql_connect(con, "localhost", "root", "root","mahdb") = 0 system_error(con) ok see "Drop table" + nl if mysql_query(con, "DROP TABLE IF EXISTS Employee") system_error(con) ok see "Create table" + nl if mysql_query(con, "CREATE TABLE Employee(Id INT, Name TEXT, Salary INT)") system_error(con) ok see "Insert data" + nl if mysql_query(con, "INSERT INTO Employee VALUES(1,'Mahmoud',15000)") system_error(con) ok if mysql_query(con, "INSERT INTO Employee VALUES(2,'Samir',16000)") system_error(con) ok if mysql_query(con, "INSERT INTO Employee VALUES(3,'Fayed',17000)") system_error(con) ok see "Close connection" + nl mysql_close(con) func system_error con see mysql_error(con) mysql_close(con) bye Output: Create Table and Insert Records Connect Drop table Create table Insert data Close connection MySQL_Insert_ID() Function We can get the inserted row id using the MySQL_Insert_ID() function Syntax: MySQL_Insert_ID() ---> Inserted row id as number Example: con = mysql_init() see "connect to database" + nl mysql_connect(con,"localhost","root","root","mahdb") see "drop table" + nl mysql_query(con, "DROP TABLE IF EXISTS Customers") see "create table" + nl mysql_query(con, "CREATE TABLE Customers(Id INT PRIMARY KEY AUTO_INCREMENT, Name TEXT)") see "insert record" + nl mysql_query(con, "INSERT INTO Customers(Name) VALUES('Mahmoud')") see "insert record" + nl mysql_query(con, "INSERT INTO Customers(Name) VALUES('Samir')") see "insert record" + nl mysql_query(con, "INSERT INTO Customers(Name) VALUES('Fayed')") see "insert record" + nl mysql_query(con, "INSERT INTO Customers(Name) VALUES('Test 2015')") see "inserted row id : " + mysql_insert_id(con) + nl see "close database" + nl mysql_close(con) Output: connect to database drop table create table insert record insert record insert record insert record inserted row id : 4 close database MySQL_Result() Function We can get the query result (data without column names) using the MySQL_Result() function. Syntax: MySQL_Result(MySQL Handle) ---> List contains the query result MySQL_Next_Result() Function We can move to the next query result using the MySQL_Next_Result() function. We use this function when we have multiple SQL statements in the same query. Syntax: MySQL_Next_Result(MySQL Handle) Print Query Result The next example execute a query on the database then print the result. con = mysql_init() see "Connect to database" + nl mysql_connect(con, "localhost", "root", "root","mahdb") see "Execute Query" + nl mysql_query(con, "SELECT Name FROM Employee WHERE Id=1;"+ "SELECT Name FROM Employee WHERE Id=3") see "Print Result" + nl see mysql_result(con) mysql_next_result(con) see mysql_result(con) see "close database" + nl mysql_close(con) Output: Connect to database Execute Query Print Result Mahmoud Fayed close database MySQL_Columns() Function We can get a list of columns names using the MySQL_Columns() function. Syntax: MySQL_Columns(MySQL Handle) ---> List contains columns information Example: con = mysql_init() see "Connect to database" + nl mysql_connect(con, "localhost", "root", "root","mahdb") see "Execute Query" + nl mysql_query(con, "SELECT * FROM Employee") see "Result" + nl see mysql_columns(con) see "Close database" + nl mysql_close(con) Output: Connect to database Execute Query Result Id 11 3 32768 Name 65535 252 16 Salary 11 3 32768 Close database MySQL_Result2() Function Instead of using MySQL_Result() to get the result data without columns names, we can use the MySQL_Result2() to get all of the column names then the query result in one list. Syntax: MySQL_Result2(MySQL Handle) ---> List (query result starts with columns names) Example: con = mysql_init() see "Connect to database" + nl mysql_connect(con, "localhost", "root", "root","mahdb") see "Execute Query" + nl mysql_query(con, "SELECT * FROM Employee") see "Print Result" + nl see mysql_result2(con) see "Close database" + nl mysql_close(con) Output: Connect to database Execute Query Print Result Id Name Salary 1 Mahmoud 15000 2 Samir 16000 3 Fayed 17000 Close database MySQL_Escape_String() Function We can store binary data and special characters in the database after processing using MySQL_Escape_String() function Syntax: MySQL_Escape_String(MySQL Handle, cString) ---> String after processing Save Image inside the database Example: See "Read file" + nl cFile = read("tests\mahmoud.jpg") con = mysql_init() See "Connect to database..." + nl mysql_connect(con, "localhost", "root", "root","mahdb") See "Escape string..." + nl cFile = mysql_escape_string(con,cFile) stmt = "INSERT INTO photo(id, data) VALUES(1, '" + cFile + "')" See "Insert data..." + nl mysql_query(con,stmt) See "Close database..." + nl mysql_close(con) Output: Read file Connect to database... Escape string... Insert data... Close database... Restore Image From The Database Example: con = mysql_init() See "Connect to database..." + nl mysql_connect(con, "localhost", "root", "root","mahdb") See "Read data from database..." + nl mysql_query(con,"SELECT data FROM photo WHERE id=1") See "Write new file" + nl result = mysql_result(con) write("tests\mahmoud2.jpg",result[1][1]) See "Close database..." + nl mysql_close(con) Output: Connect to database... Read data from database... Write new file Close database... MySQL_AutoCommit() Function We can enable or disable the auto commit feature using the MySQL_AutoCommit() function. Syntax: MySQL_AutoCommit(MySQL Handle, lStatus) # lstatus can be True/False MySQL_Commit() Function We can commit updates to the database using the MySQL_Commit() function. Syntax: MySQL_Commit(MySQL Handle) MySQL_Rollback() Function We can rollback updates to the database using the MySQL_Rollback() function. Syntax: MySQL_Rollback(MySQL Handle) Transaction Example The next example presents the usage of MySQL_Autocommit(), MySQL_Commit() & MySQL_RollBack() functions. Example: func main con = mysql_init() see "Connect" + nl if mysql_connect(con, "localhost", "root", "root","mahdb") = 0 system_error(con) ok see "Drop table" + nl if mysql_query(con, "DROP TABLE IF EXISTS Employee2") system_error(con) ok see "Create table" + nl if mysql_query(con, "CREATE TABLE Employee2(Id INT, Name TEXT, Salary INT)") system_error(con) ok see "Insert data" + nl if mysql_query(con, "INSERT INTO Employee2 VALUES(1,'Mahmoud',15000)") system_error(con) ok if mysql_query(con, "INSERT INTO Employee2 VALUES(2,'Samir',16000)") system_error(con) ok if mysql_query(con, "INSERT INTO Employee2 VALUES(3,'Fayed',17000)") system_error(con) ok mysql_autocommit(con,False) mysql_query(con, "INSERT INTO Employee2 VALUES(4,'Ahmed',5000)") mysql_query(con, "INSERT INTO Employee2 VALUES(5,'Ibrahim',50000)") mysql_query(con, "INSERT INTO Employee2 VALUES(6,'Mohammed',50000)") See "Save transaction (y/n) " give nChoice if upper(nChoice) = "Y" mysql_commit(con) else mysql_rollback(con) ok see "Close connection" + nl mysql_close(con) func system_error con see mysql_error(con) mysql_close(con) bye Output: Connect Drop table Create table Insert data Save transaction (y/n) y Close connection Ring programming language