A tutorial on using MySQL in a Lua program _lua

Import MySQL

We can import the SQLite library with a simple statement, assuming that LUA is properly implemented and completed. During the installation process, the folder Libsql contains database-related files.

Copy Code code as follows:

MySQL = require "luasql.mysql"

Variable MySQL will provide access to this feature by referencing the main MySQL table.
Establish a connection

We can set up a MySQL-starting environment and then create a connection to the environment. as shown below.

Copy Code code as follows:

Local env = Mysql.mysql ()
Local conn = env:connect (' Test ', ' root ', ' 123456 ')

The connection above will connect to the existing MySQL file and establish a connection to the newly created file.
execution Function

This will help us do the creation, insert, delete, update, and so on, all the database operations perform simple functions. The syntax is shown below

Copy Code code as follows:

Conn:execute ([[' Mysqlstatement ']])

In the above syntax, we need to make sure Conn is open and existing MySQL connection and replace "mysqlstatement" using the correct statement.
example of creating a table

A simple example of creating a table is shown below. It creates a table of type int and varchar type, two parameter IDs and name.

Copy Code code as follows:

MySQL = require "luasql.mysql"

Local env = Mysql.mysql ()
Local conn = env:connect (' Test ', ' root ', ' 123456 ')
Print (Env,conn)

status,errorstring = Conn:execute ([[CREATE TABLE Sample2 (id INTEGER, name TEXT);]]
Print (status,errorstring)

When running the above program, the table named sample will have two columns, ID and name will be created.

Copy Code code as follows:

MySQL environment (004bb178) MySQL connection (004BE3C8)
0 Nil

If there is an error, the nil Error statement is returned. A simple error statement below is shown below.

Copy Code code as follows:

Luasql:error executing query. Mysql:you have an error in your SQL syntax; Check the manual that corresponds to your MySQL server version for the right syntax to use near ' "ID INTEGER, name TEXT) ' At line 1

Examples of INSERT statements

The INSERT statement for MySQL is shown below.

Copy Code code as follows:

Conn:execute ([INSERT into sample values (' One ', ' Raj ')]]

Example of an UPDATE statement

The MySQL UPDATE statement is shown below.

Copy Code code as follows:

Conn:execute ([[[[UPDATE sample3 SET name= ' John ' WHERE id = ' 12 ']])

Example of deleting a DELETE statement

The DELETE statement-mysql as shown below.

Copy Code code as follows:

Conn:execute ([[[[[DELETE from sample3 where id = ' 12 ']])

Example of a SELECT statement

In the case of a SELECT statement, we need to iterate through each row and extract the required data. The following simple SELECT statement is shown below.

Copy Code code as follows:

cursor,errorstring = Conn:execute ([[SELECT * from sample]])
Row = Cursor:fetch ({}, "a")
While row do
Print (String.Format ("Id:%s, Name:%s", Row.id, Row.name))
--reusing the table of results
row = Cursor:fetch (row, "a")
End

In the above code, Conn is an open MySQL connection. The cursor is returned by the execution statement, and the desired selection data can be returned by the reaction of the table.
a complete example

A complete example of all of the above statements gives the following references.

Copy Code code as follows:

MySQL = require "luasql.mysql"

Local env = Mysql.mysql ()
Local conn = env:connect (' Test ', ' root ', ' 123456 ')
Print (Env,conn)

status,errorstring = Conn:execute ([[[CREATE TABLE sample3 (id INTEGER, name TEXT)]]
Print (status,errorstring)

status,errorstring = Conn:execute ([INSERT into sample3 values (' ', ' Raj ')]]
Print (status,errorstring)

cursor,errorstring = Conn:execute ([[SELECT * from Sample3]])
Print (cursor,errorstring)

Row = Cursor:fetch ({}, "a")
While row do
Print (String.Format ("Id:%s, Name:%s", Row.id, Row.name))
row = Cursor:fetch (row, "a")
End
--Close everything
Cursor:close ()
Conn:close ()
Env:close ()

When you run the above program, you will get the following output.

Copy Code code as follows:

MySQL environment (0037b178) MySQL connection (0037EBA8)
0 Nil
1 Nil
MySQL cursor (003778A8) nil
Id:12, Name:raj

To perform a transaction:

Transactions are a mechanism for ensuring data consistency. A transaction should have the following four attributes:

• Atomicity: The transaction either completes or none of the changes occur.
• Consistency: A transaction must start a consistent state, allowing the system to be in a consistent state.
• Quarantine: The intermediate result of a transaction is not visible outside the current transaction.
• Persistence: When a transaction is committed, this effect is persistent even if the system fails.

The transaction begins the start TRANSACTION and the commit or ROLLBACK statement ends.
Start a transaction

To start a transaction, we need to execute the statement under LUA, assuming that Conn is an open MySQL connection.

Copy Code code as follows:

Conn:execute ([[[START TRANSACTION;]])

Rolling back a transaction

We need to do the following statement to roll back and forth the changes made after the start transaction.

Copy Code code as follows:

Conn:execute ([[ROLLBACK;]])

Commit a transaction

We need to do the following statement to commit the changes made after the execution of the start transaction.

Copy Code code as follows:

Conn:execute ([[[COMMIT;]])

We already know about MySQL and the following section describes basic SQL operations. Keep in mind the transaction, but Sqlite3 will not explain it, but the same statement works in Sqlite3.