ID:79592
 
DB by Dantom
Access databases from DM.
13 fans · Created Nov 30 2002
Recently the usage of MySQL with BYOND has taken a step forward and is no longer limited to people running their games on Linux. This has opened the door for many new advancements in data management.

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).
Really cool, MySQL is an amazing tool and should be utilized wherever possible.

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.

var/list/row_data = GetColumnData()

That should be qry.GetColumnData()
var/list/row_data = qry.GetColumnData()
Also, now that I have it working, I feel this should be GetRowData(), since not only does GetColumnData() not exist, but in the context of the code, Row would make more sense than Column.
Perhaps it would be best if you gave a full, and simple desciption of what MySQL is, and what its supposed to do...

Especially for those who have never heard of MySQL, and would like to learn it...
Which is why I linked to the MySQL website.
It wouldn't hurt to mention that the mysql dll/so is required to get MySQL working in BYOND.
Murrawhip wrote:
It wouldn't hurt to mention that the mysql dll/so is required to get MySQL working in BYOND.

Why state the obvious? If you're too incompetent to rationalise you need to install MySQL before using it, then you might as well give up using the database. So much spoon-feeding is needed for your average DM programmer.
Neblim wrote:
Why state the obvious? If you're too incompetent to rationalise you need to install MySQL before using it, then you might as well give up using the database. So much spoon-feeding is needed for your average DM programmer.

It would actually help to state the requirement, so the user doesn't say ... pick up 64 bit MySQL drivers (perfectly possible and the default on Linux package managers for x86_64 systems), or install them to C:\Windows\system32 on Windows x64 systems.

In x64 Windows, C:\Windows\system32 is rather confusingly reserved for x64 DLLs only, but is a valid search path. Placing the 32 bit MySQL driver will result in undefined behaviour (The issue Murrawhip brought to me was a completely illogical Danton.DB error message). In this scenario, they should be installed in C:\Windows\SysWOW64.

Because you're dealing with OS-specific linking path and architecture rules, specifying the DLL/SO requirements is actually not spoon-feeding at all and quite a sensible idea. Particularly as we spent 30 minutes talking through the install scenario to pin-point the problem, and I wouldn't say either of us are particularly dim.