Unfortunately not many people know how to use the library yet, this article will hopefully shed some light on it for them.
The DBConnection datum
The DBConnection datum is what handles the entire connection and authentication process, using the Connect() proc associated with the datum.
Setting some variables up for use later
A good way to keep from having to type too much when using MySQL with BYOND is to define a few global variables for usage later in the code. The most common of these are the DBI string, the username, and the password.
The DBI (database independent interface) is a string you pass through the MySQL library to tell your server you're connecting to a MySQL database and not another type of database. The stucture of the DBI is very important.
var/DBI = "dbi:mysql:[my_database]:[my_server]:[server_port]"
Lets take this apart, 'dbi:mysql:' is what tells the server to access the MySQL daemon running on it, [my_database] would be the database you want to connect to, [my_server] would be the server, which is usually 'localhost'; and the [server_port] would be the port MySQL is running on, usually 3306.
Next you'll want to make a variable for your username and your password.
var
username = "Myuser"
Now wait, before you store your password as a plain-text variable you need to remember that these values can be accessed by anyone with the dmb file, that's not good! So lets not store it as plain text, we'll generate it at runtime.
proc/GeneratePassword()
// Lets say your password is 'mypassword'
return "my" + "p" + "assw" + "ord"
That should stop most would-be password thieves but not all of them. To take it to the next level you'd want to make use of ascii2text() to really throw them off.
Now just store the result of the proc in a variable or even use it within Connect() (like I'm going to do)
Using Connect() to make a connection to your database
The first part of any MySQL operation is of course connecting to your database.
var
DBConnection/my_connection = new()
connected = my_connection.Connect(DBI,username,GeneratePassword())
Now the value of 'connected' will be null of the connection fails, otherwise it means the connection was a success.
if(!connected)
usr << "Oh no! The database connection failed!"
else
usr << "The database connection was a success!"
Now you have an error message, but no real details. This brings me to my next proc example...
Using ErrorMsg() to find out what's wrong
All of the datums of the DB library have an ErrorMsg() proc, this proc will return an error string if a connection or query goes wrong. This is very helpful for debugging your application.
if(!connected)
usr << "Connection failed: [my_connection.ErrorMsg()]"
This will give you any details as to why the connection failed.
Disconnect() and IsConnected()
These two functions do exactly what they say they do, Disconnect() will disconnect you from your database. IsConnected() will return true if a connection is already established, false otherwise.
Quote(string)
This function is used to sanitize strings you intend to pass through a MySQL query, this will prevent things like injection attacks and malformed queries.
var/mystring = my_connection.Quote("How's everybody?")
This example will cause 'mystring' to equal "How\'s everybody?" which keeps MySQL from assuming the usage of ' is a query terminator.
Using NewQuery() for quick queries
The NewQuery() proc is used for quick database queries without having to manually create a DBQuery datum and all of that. It's a shortcut, that's all.
var/DBQuery/qry = my_connection.NewQuery("SELECT * FROM `my_table`")
qry.Execute()
Which is the same as:
var/DBQuery/qry = new("SELECT * FROM `my_table`")
qry.Execute()
// And...
var/DBQuery/qry = new()
qry.Execute("SELECT * FROM `my_table`")
We'll get into how to use the DBQuery object next.
The DBQuery datum, talkin' to the database
This is where you actually work with the database, the DBQuery datum is used to execute MySQL queries on the active MySQL connection.
Getting data from the database
var/DBQuery/qry = new()
qry.Execute("SELECT * FROM `my_table`")
if(qry.RowCount() > 0)
while(qry.NextRow())
var/list/row_data = qry.GetRowData()
for(var/D in row_data)
usr << "[D] = [row_data[D]]"
This example will grab every value from the 'my_table' table and display it.
There are a few procs I used in here, how about I explain what they do?
RowCount()
This proc simply returns the amount of rows the query picked up.
NextRow()
This proc will fetch the next row of the database and return the values in a number-based list.
GetRowData()
This proc takes the number-based list returned by NextRow() and gives you a nice friendly value-based list like list("mycolumn"="myvalue").
The other DBQuery procs
Columns
This proc will return the Columns your query has affected, it is used to build the GetRowData() proc's information. It will return a DBColumn datum, which I will explain more later.
Connect(DBConnection)
This proc allows you to alter which DBConnection the DBQuery datum is associated with, simply pass a new DBConnection datum through it.
Execute(sql,cursor)
You probably noticed I used this proc earlier, this is a very important function. The Execute() proc is what tells the query to, well, execute. Nothing will happen until this proc is called. When called without any arguments it will execute the query stored in the datum's 'sql' variable. The second argument is only important for advanced users, as most cursor-related stuff is. If you don't know what a cursor is you probably shouldn't be using them.
RowsAffected()
When using UPDATE or INSERT queries this proc will return the amount of rows altered or added by the query.
Close()
This proc will close out the query, making it impossible to continue to use the datum for any future action.
Quote(string)
Works exactly like DBConnection.Quote()
SetConversion(column,conversion)
This proc will convert a column from one data type to another.
Adding and removing data
Another major part of MySQL usage is the ability to add and remove new data, this is done by simply executing an INSERT or DELETE query. Check the MySQL manual on how to form these queries.
When adding or removing data the use of RowsAffected() is good for making sure the process was a success.
The DBColumn datum
The DBColumn datum is used to store information relating to a data column retrieved from your database. It can be created by calling the Columns() proc during a query.
The variables
name -- This is the name of the column.
table -- This is the table that contains the column.
position -- This is the (1-based) position of the column in the table.
sql_type -- This is the type of data the column contains, such as INT, TEXT, BIGINT, etc...
length -- This is how much data the column contains.
max_length -- This is the most data the column can contain.
The SqlTypeName(type) proc
The SqlTypeName() proc is used to convert the library-defined data type constants into text for easy reading.
That's about it, folks. There's not much else to using MySQL, all you need to learn now is the SQL syntax, which can be learned from the MySQL website -- and as always, the DMCGI Guild is a good place to ask for help in matters relating to web-type DM usage (which MySQL falls under).
The problem is people aren't going to learn MySQL that easily, and the MySQL website isn't that good of a learning solution to those newer to programming. When I started, I absolutely could not learn from the manual and many creator-made learning materials for many languages. You should find some alternative MySQL tutorials made for people who aren't used to it. MySQL is like visual basic, and is REALLY like it once you get into stored routine programming. People need to be eased into it.