Ring/Lessons/MySQL Functions

MySQL Functions

edit

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

edit

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

edit

We can start using MySQL Client through the MySQL_Init() function.

Syntax:

	MySQL_Init() ---> MySQL Handle

MySQL_Error() Function

edit

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

edit

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

edit

We can close the connection to the MySQL database using the MySQL_Close() function

Syntax:

	MySQL_Close(MySQL Handle)

MySQL_Query() Function

edit

We can execute SQL queries using the MySQL_Query() function

Syntax:

	
	MySQL_Query(MySQL Handle, cSQLQuery)

Create Database

edit

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

edit

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

edit

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

edit

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

edit

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)
edit

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

edit

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

edit

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

edit

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

edit

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

edit

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

edit

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

edit

We can commit updates to the database using the MySQL_Commit() function.

Syntax:

	MySQL_Commit(MySQL Handle)

MySQL_Rollback() Function

edit

We can rollback updates to the database using the MySQL_Rollback() function.

Syntax:

	MySQL_Rollback(MySQL Handle)

Transaction Example

edit

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