Introduction

Microsoft .NET is a set of Microsoft software technologies for connecting your world of information, people, systems, and devices. It enables an unprecedented level of software integration through the use of XML Web services: small, discrete, building-block applications that connect to each other as well as to other, larger applications via the Internet.

For more information about .NET, refer to the following FAQ: http://www.microsoft.com/net/defined/faq.asp

Presently, developers can access MySQL using three different methods.

• The ODBC.NET Solution - MyODBC Driver
• Using MySQL Native .NET Providers
• Using the OLEDB.NET Solution - MyOLDDB Provider

In the following sections of this article, we will discuss all these solutions in detail with some examples.

The ODBC.NET Solution - MyODBC Driver

ODBC provides a solution for having a complete interoperable application, as ODBC is the industry standard for accessing any database.

Connector/ODBC, or MyODBC, is the MySQL ODBC Driver, and can be used in the .NET environment for accessing MySQL through ODBC.NET. The ODBC .NET Data Provider is a data provider that wraps an existing ODBC connection.

The data provider is defined in the System.Data assembly and ships as a standard part of the 1.1 version of the framework. Users still building with .NET 1.0 can download the ODBC provider directly from Microsoft.

ODBC.NET - MyODBC Architecture

Here is the simple control flow when using ODBC.NET solution to access MySQL.

Requirements - Setup of the ODBC.NET Environment

1. Download and install the latest .NET Framework SDK.
2. Install Microsoft Data Access Components (MDAC) 2.6 or later. MDAC 2.8 is the latest and is recommended.
3. Install the ODBC.NET Provider. Note that this step is only necessary if you are building applications using .NET 1.0. The ODBC provider comes standard with .NET 1.1. Note: Using ODBC.NET with MyODBC, while fetching empty string (0 length), will give SQL_NO_DATA exception as shown below.

    System.Data.Odbc.OdbcException: NO_DATA - no error information available
        at System.Data.Odbc.OdbcConnection.HandleError(IntPtr hHandle, SQL_HANDLE hType, RETCODE retcode)
        at System.Data.Odbc.OdbcDataReader.GetData(Int32 i, SQL_C sqlctype, Int32 cb)
        at System.Data.Odbc.OdbcDataReader.internalGetString(Int32 i)
        at System.Data.Odbc.OdbcDataReader.GetValue(Int32 i, TypeMap typemap)
        at System.Data.Odbc.OdbcDataReader.GetValue(Int32 i)
        at System.Data.Odbc.OdbcDataReader.IsDBNull(Int32 i)
        at GameShopWS.GameProvider.GetGameInfo(Int32 game_ID) in ..

   You can fix the above problem by applying the patch for ODBC32.DLL from: http://support.microsoft.com/default.aspx?scid=kb;EN-US;q319243.

4. Install MySQL Server. For more information on how to install and setup the MySQL Server, refer to the Installation chapter of the MySQL Reference Manual.
5. Now install MySQL ODBC Driver-MyODBC 3.51; and for installation instructions, refer to
   the Installation section of the Connector/ODBC documentation.
6. Setup an MyODBC DSN to be used for connecting to MySQL by following the instructions in the “DSN on Windows” section of the Connector/ODBC documentation.

Now you have everything you need for working with MySQL through ODBC.NET.

Developing MyODBC-ODBC.NET Provider Applications

The System.Data namespace contains the ODBC .NET Data Provider. A .NET data provider provides functionality for connecting to a data source, executing commands, and retrieving results. Those results can be processed directly, or placed in an ADO.NET DataSet for further processing while in a disconnected state. While in the DataSet, data can be exposed to the user, combined with other data from multiple sources, or passed remotely between tiers. Any processing performed on the data while in the DataSet can then be reconciled to the data source.

All .NET data providers are designed to be lightweight. They consist of a minimal layer between the data source and your code. This extends functionality without sacrificing performance.

These are the core classes that make up a ODBC.NET data provider:

Core classes:

Delegates:

Enumerations:

For more information about all these commands, and its usage, refer to the ODBC.NET Data Provider Documentation that comes with ODBC.NET.

To use the ODBC .NET Data Provider, you must import the System.Data namespace to your application, as the following code illustrates:

 [Visual Basic]
 Imports System.Data.Odbc

 [C#]
 using System.Data.Odbc;

You also must include a reference to the .DLL when you compile your code. For example, if you are compiling a C# program, your command line should include:

  csc /r:System.Data.dll

Demo Example - Establishing a Connection

In the demo example, we will look at how to connect to MySQL server through MyODBC using ODBC.NET.

1. Import the System.Data.Odbc namespace (ODBC.NET) to your application using the following statement:

     using System.Data.Odbc;

   In case of VB, it should be:

     Imports System.Data.Odbc;

2. Once the namespace is imported in your application, you can create a simple class and establish a connection to MySQL server through MyODBC using an OdbcConnection object.

       string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" +
                            "SERVER=localhost;" +
                            "DATABASE=test;" +
                            "UID=venu;" +
                            "PASSWORD=venu;" +
                            "OPTION=3";
   
       OdbcConnection MyConnection = new OdbcConnection(MyConString);
       MyConnection.Open();

   The above one uses DSN-less connection, if you have a MyODBC DSN defined already then you can just use “DSN=dsn_name” as the connection string i.e.

       OdbcConnection MyConnection = new OdbcConnection("DSN=myodbc3-test");
       MyConnection.Open();

   In case of VB, it should be

       Dim MyConnection As New OdbcConnection(MyConString)
       MyConnection.Open()

3. Once connected, you can execute the SQL statements using the interfaces provided by ODBC.NET.

For a complete example in C# and VB and how to build it, refer to the online MyODBC FAQ, which provides a demo sample with all basic commands.

Using MySQL Native .NET Providers

There are a number of fully managed .NET providers available to help MySQL users develop applications in the .NET environment.

Two popular choices are:

1. ByteFX.Data
2. CoreLabs

The following ADO.NET classes are implemented by both providers:

ByteFX.Data

ByteFX, Inc. has developed a fully managed .NET provider for MySQL database connectivity. This provider is open-source, available under the LGPL license, developed entirely in C#, and offers access to advanced functionality such as compression, batch SQL submission, and VS.Net integration.

ByteFX.Data implements the ADO.NET managed provider interface, so it behaves exactly like Microsoft’s SqlClient and OledbClient providers. You use the familiar Connection, Command, DataAdapter, and DataReader classes just as you would with SQL Server.

Some documentation is provided with the latest release which show the proper way to interact with the classes, but almost any Microsoft example can be used by simply changing any System.Data.SqlClient objects to `ByteFX.Data.MySqlClient objects.

Read more information about ByteFX.Data.

CoreLabs

Core Lab is a closed-source fully-managed .NET provider for MySQL. It is available in standard and professional configurations and can be purchased from http://www.crlab.com.

ADO.NET Architecture

The basic architecture of a MySQL ADO.NET provider looks like:

The architecture will be similar to that of ADO.NET as described at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconadonetarchitecture.asp.

ADO.NET Requirements - Setup

1. Download and install the latest .NET Framework SDK.
2. Install Microsoft Data Access Components (MDAC) 2.6 or later. MDAC 2.7 is recommended.
3. Install MySQL Server. For more information on how to install and setup the MySQL Server, refer to the Installation chapter of the MySQL Reference Manual.
4. Now install a MySQL managed .NET provider. Refer to the installation instructions in the provider’s README file.

Developing Applications Using MySQL ADO.NET

The following section, will explain how to connect to the MySQL server through MySQL managed providers.

1. Take any basic ADO.NET samples either from the Microsoft Framework SDK or from the MySQL managed provider, and make sure to replace the system.data.sqlclient namespace with provider specific namespace (in case the example is from Microsoft).

       MySQLNet    :  using ByteFX.Data.MySqlClient;
       CoreLab     :  using CoreLab.MySql;

   In case of VB, instead of ‘using’ use ‘Imports’ i.e.:

       Imports ByteFX.Data.MySqlClient  or
       Imports CoreLab.MySql

2. Once the namespace is imported in your application, you can create a simple class and establish a connection to the MySQL server using:

       string DataSource = "localhost";
       string Database   = "test";
       string UserID     = "root";
       string Password   = "root";
   
       string MyConString  = Data Source=" + DataSource +
           ";Database=" + Database +
           ";User ID=" + UserID +
           ";Password=" + Password;
   
       MySqlConnection mycon = new MySqlConnection(MyConString);
       mycon.Open();

For more connection options and programming considerations, refer to the provider’s README file.

Using the OLEDB.NET Solution - MyOLDDB Provider

The OLDDB.NET provider can be used in a similar way to ODBC.NET, for exploring MySQL through the MyOLEDB Provider.

MySQL currently doesn’t officially support MyOLEDB, so this solution will not be discussed here.

Comparison Between Different Implementations

The basic advantages and disadvantages of these two (ODBC.NET and native .NET Provider) implementations are listed below:

ODBC.NET

Advantages:

• ODBC is designed for maximum interoperability i.e. the ability of a single application to access different database management systems (DBMS) with the same source code
• Ease of Use

Disadvantages:

• Performance is a big negative factor for ODBC as it involves too many layers.

Native .NET Provider

Advantages:

• Speed
• VM benefits including garbage collection and security
• Good exposure of database specific syntax

Disadvantages:

• Not a standardized way of accessing.

Conclusion

This article aims to help MySQL users get started in the .NET environment. If you have any questions or comments, please join the MyODBC mailing list.

By Giuseppe Maxia
05 June 2001

Introduction

	Basic problem definition. Starting from a list of values, we want to group them by field A and create a column for each distinct value of field B.
		The desired result is a table with one column for field A, several columns for each value of field B, and a total column.

According to some authoritative sources (Joe Celko, “SQL for smarties”) we should use specialized (and expensive) statistical tools to achieve this purpose with a database server.

My recent experience with MySQL has shown that you don’t have to invest a fortune to have server-side cross-tabulations. The way I found it is littered with errors and disappointment, and in perspective it should appear quite boring. This is the chronicle of how I would have liked to find out a solution.

A call for help - Defining the problem

Wizards never go home early.

He recognized me and asked how he could help. I told him. He listed patiently, without asking silly questions in between, and finally said “I think I could provide you with some useful tool. See you in half an hour” and hung up.

Twenty five minutes later he was in my office, sitting in front of me and mentioning coffee. Then he started to ask questions.

“So, what exactly do you need to do?”

“I need to get a cross-tab out of my database.”

“How did you solve the problem so far?”

“In the beginning, I used to export my data to a spreadsheet, and let the users do the work. You know, nowadays these spreadsheets can do almost anything, provided that you are smart enough. Therefore, I gave my users the possibility of exporting records to their favorite application so that they could twist the data as they liked.”

“And why you can’t do it anymore?”

“We’re growing, you know. When I started the database, we had just a few hundred records, and everything worked just fine. But then, we hit the market really hard, and before I could realize it, we had more than ninety thousand records that were about to go down to the spreadsheets and the users complained that it was slow and asked me to integrate the x-tab into the main application. They say that, after all, these nice desktop databases that I don’t want to mention — the Wizard nodded approvingly — have such features, and my application should have it as well.”

“Then you did what they asked for. You created a function to integrate the cross-tabulation into your application.” He was reading me like an open book.

“Yes, of course I did it, even though it was not easy. To minimize the amount of data transferred from the server to the client I was only dealing with summarized data, you know, GROUP BY two fields. An then I found a way of translating the values of the second field into columns and summing up the data.”

The wizard looked at his coffee mug for a long time and said “You seem to have solved your problem then. Haven’t you?”

“Well, not exactly. Dealing with a large matrix of data is not what C language seems to be made for. I mean, it is, but I can’t cope with it as well as I can work out a few spreadsheet macros. And the algorithm has to take care of peculiar cases where the server does not return a value for the second field, and then the management wanted to have the cross-tab broken down by additional rows and columns, and they want such reports to be available into the main application by Monday, and I don’t think my algorithm could cope with such request. This is why I asked your help.”

I looked at him expectantly. I knew that now he could do one of two things: he could either say that the problem was trivial and uninteresting and he would leave me in the glue, or he should ask to see the algorithm and tell me what was wrong with it and give me a simple and efficient solution, which will make my application scalable to solve the darn problem. He had done that before for me. He had looked at my application, found the reason for inefficiency, suggested a simpler approach, and left me happy, wiser and puzzled.

That day, though, he did none of that. He didn’t want to see the code at all. Instead, he asked for a refill of coffee and told me “Give me a tour of your company.”

I understood what he wanted. He wasn’t looking for a walk among the desks, but he wanted to see the data. I explained that I could not show him the real data, since my boss was really concerned about the competition learning what we are up to, but happily I had some dummy data that I was using with our real structure when I was testing the database, and I showed him that.

“This is the design of the main tables in the DB.” I explained. “We have an employees table, code-related with departments and locations, which is also related to countries. The sales table has references to the employees and categories tables. Only employees belonging to the Sales department can be involved in sales, but they can sell anything, from software to services to education.”

The Wizard studied the diagram for a few minutes, nodding from time to time, as if recognizing an old friend. Then he looked at me and said “OK. Let’s do some cross tabulation. Do you have anything especially urgent?”

“As a matter of fact, I have more than one specific urgent job, but if you don’t mind, I would like to see the solution of a simple one, so that I can work it out on a more complex one.”

“It’s fine with me.” He said. “Show me your case.”

“OK. I will query two tables, employees and locations, and get the list of employees with the town where they work.”

I opened a Xterm in my Linux box and connected to MySQL. There I entered:

mysql> SELECT name, gender, location

    -> FROM locations INNER JOIN employees USING (loc_code);

“What I would like to have,” I explained, “Is a row for each town, with a column for each gender and a total column.”

Opening the path

“We will go for it manually.” He said. “This way, we are going to understand what to ask the database engine to do.” It was typical of the Wizard. When he was in this explaining mood, I should better let him talk.

“Let’s start with the first row. It’s Roma. The employee is male, so we write 1 under M and 0 under F. Then we get the second line. It’s again Roma. Which gender is this one? If we have a male, then we are going to add 1 to the value under M, and add a 0 under F, and so on.”

He looked at me, as if expecting me to see the light and have a magic understanding of the algorithm he was hinting at. My blank stare must have told him that I was still at large.

“Don’t you get it? It’s simple. We sum to M if the employee is male, and we sum to F if she’s a female.” He stressed the words sum and if. Then he grabbed my keyboard and modified my previous statement:

mysql> SELECT location, SUM(IF(gender='M',1,0)) AS M, SUM(IF(gender='F',1,0)) AS F
    -> FROM locations INNER JOIN employees USING (loc_code) GROUP BY location;

“So we are telling the engine to do exactly the same thing that we would have done manually. Sum … if. Only the engine will do it faster.”

I said “Wow!” but my mind was racing to see how this incredibly simple statement could be of help. “What about the total column?” I asked.

“Oh, that. Here you are.” And he modified the statement once more:

mysql> SELECT location, SUM(IF(gender='M',1,0)) AS M,
    -> SUM(IF(gender='F',1,0)) AS F, COUNT(*) AS total
    -> GROUP by location;

“I don’t think I really understand, though.” I said. “We need to count, but we are summing up. How comes?”

“From the SQL point of view, we are doing the same thing. COUNT of star and SUM of one are the same thing. Try it yourself. Type a ’select COUNT star from employees’”.

mysql> SELECT COUNT(*) from employees;

“Now replace COUNT of star with SUM of one.”

mysql> SELECT SUM(1) from employees;

“It’s the same!” I said, excited.

“No, actually it’s not. COUNT of star is optimized by MySQL, and it is performed from the table descriptor, without actually counting the records. You can’t realize the difference in such a small table. If you had one million records, and you were actually counting by groups, you would see that SUM takes a couple of milliseconds more than COUNT, and I think we can live with that. Notice that we could not use COUNT in our cross-tab, because it would have counted all the rows anyway. Try it.”

mysql> SELECT location, COUNT(IF(gender='M',1,0)) AS M,
    -> COUNT(IF(gender='F',1,0)) AS F,
    -> COUNT(*) AS total
    -> FROM locations INNER JOIN employees USING (loc_code)
    -> GROUP BY location;

“See? That’s why we have to sum up, instead of counting. COUNT is a dumb function which will count any piece of junk it finds. SUM has some grace, in its choice.”

It looked so trivial that I was ashamed of myself for not having found it alone.

But suddenly I saw something that didn’t seem right to me. “Here we have a simple case, where we know all the values that will go into the columns. But what should we do if we don’t know? What if we want the departments instead?”

The Wizard took a glance at the diagram and typed:

mysql> SELECT dept from departments;

“Yeah. I see.” I said, with a hint of disappointment in my voice. “You mean that I have to compose the query manually, entering a SUM/IF statement for each value in departments?”

Some help from SQL itself

The Wizard smiled. “If you like to, you’re welcome. However, you could get some help from the database engine itself, provided that you ask nicely.” And while he was speaking he typed a very cryptic statement:

mysql> SELECT CONCAT(', SUM(IF(dept = "',dept,'", 1,0)) AS `',dept,'`')
    -> FROM departments;

“You know,” he went on, “you can also use SQL to produce SQL code. This is one of the cases. You have in front of you the list of columns that you should include in your query. Now, with some cut-and-paste, we could get the result you want. Here.”

mysql> SELECT location
    -> , SUM(IF(dept = "Development", 1,0)) AS `Development`
    -> , SUM(IF(dept = "Personnel", 1,0)) AS `Personnel`
    -> , SUM(IF(dept = "Research", 1,0)) AS `Research`
    -> , SUM(IF(dept = "Sales", 1,0)) AS `Sales`
    -> , SUM(IF(dept = "Training", 1,0)) AS `Training`
    -> , COUNT(*) AS total
    -> FROM locations INNER JOIN employees USING (loc_code)
    -> INNER JOIN departments USING (dept_code)
    -> GROUP BY location;

The wizard was now unstoppable. He had reached the stage where he simply couldn’t help giving away his knowledge.

“Before we go on,” he said, taking possession of my keyboard, “let’s see how this same method can do more than counting. Let’s replace those 1s with a numeric field, and do real summing up.”

mysql> SELECT location
    -> , SUM(IF(dept = "Development", salary,0)) AS `Development`
    -> , SUM(IF(dept = "Personnel", salary,0)) AS `Personnel`
    -> , SUM(IF(dept = "Research", salary,0)) AS `Research`
    -> , SUM(IF(dept = "Sales", salary,0)) AS `Sales`
    -> , SUM(IF(dept = "Training", salary,0)) AS `Training`
    -> , SUM(salary) AS total
    -> FROM locations INNER JOIN employees USING (loc_code)
    -> INNER JOIN departments USING (dept_code)
    -> GROUP BY location;

“Don’t forget to change also the total field. A simple SUM without IF, and your total is ready.”

I was looking at the screen, which was showing what seemed to be the complete solution to my problem, but the wizard was shaking his head. “As a matter of fact,” he was saying, there is something that we can improve here. We have two queries, in which we are reading the departments table. So we are reading it twice twice. The whole process could be improved, by querying for department code the first time, and omitting the join with departments the second time.”

mysql> SELECT CONCAT(', SUM(IF(dept_code = "',dept_code,'", 1,0)) AS `',dept,'`')
    -> FROM departments;

“Here. Let’s get the columns once more. Good. And there it is. This one looks better.”

mysql> SELECT location
    -> , SUM(IF(dept_code = "1", 1,0)) AS `Personnel`
    -> , SUM(IF(dept_code = "2", 1,0)) AS `Training`
    -> , SUM(IF(dept_code = "3", 1,0)) AS `Research`
    -> , SUM(IF(dept_code = "4", 1,0)) AS `Sales`
    -> , SUM(IF(dept_code = "5", 1,0)) AS `Development`
    -> , COUNT(*) AS total
    -> FROM locations INNER JOIN employees USING (loc_code)

    -> GROUP BY location;

He changed the previous two SQL statements, executed them, with some cut-and-pasting in the middle, and got exactly the same result. Now he was explaining me why he did it. “The first query is scanning all the departments table, and we know that its results will be used to build the second query containing the employees table, which has already a department code. Therefore, we can skip the join with departments, since the only purpose of that join was to get the names of the departments.”

“I see” was the only comment I could offer, Since I was overwhelmed by his continuous insight. I got the idea, and I couldn’t help thinking that he must have done that before. All those pieces of information were coming just too fast for me. Luckily, all logs were on, so I knew that I would be able to get all the statements back when he would leave. Which was not the case yet. The wizard was now ready to give me his philosophical view of cross tabulating.

The golden rules

“Rule number one:” — wizards have always a rule n. 1 for everything — “cross-tabulating is a simple algorithm with a complex implementation. Once you know the principle, you can do everything you need for your statistics. The difficult part is making the process automatic. You don’t know in advance which values you should use for your columns. Hence, you have to find such values every time, before creating the second query. The real problem with cross-tabs is that there are no simple cases. Even the ones that look as such, have sticky problems. And you should consider this as rule number two.”

I was about to lose heart, but he had more for me.

“On the other hand,” he continued, “This algorithm is so flexible that will let you do things that your ordinary spreadsheet won’t let you. For example, let’s suppose that you want a x-tab with the number of employees by gender and the total of their salaries as well. In your common graphical tool you can do either of them. With this system, you can combine both. It’s no difficult, just complicated. Now, if you fetch some more coffee, I’ll show you.”

And when I came back from the kitchenette, there it was.

mysql> SELECT location
    -> , SUM(IF(gender='M',1,0)) AS M
    -> , SUM(IF(gender='M',salary,0)) AS salary_M
    -> , SUM(IF(gender='F',1,0)) AS F
    -> , SUM(IF(gender='F',salary,0)) AS salary_F
    -> , COUNT(*) AS empl
    -> , SUM(salary) AS tot_salary
    -> FROM locations INNER JOIN employees USING (loc_code) GROUP BY location;

He took a sip from his mug and added, “And of course nobody could prevent you from inserting a totally unrelated line into your query, like this one:”

mysql> SELECT location ,SUM(IF(gender='M',1,0)) AS M
    -> , SUM(IF(gender='M',salary,0)) AS salary_M
    -> , SUM(IF(gender='F',1,0)) AS F
    -> , SUM(IF(gender='F',salary,0)) AS salary_F
    -> , SUM(IF(dept_code = "4", 1,0)) AS `Sales`
    -> , COUNT(*) AS empl
    -> , SUM(salary) AS tot_salary FROM locations
    -> INNER JOIN employees USING (loc_code) GROUP BY location;

“Here we have results coming from three different sources: the counting of employees by gender, the sum of their salaries by gender, and the total number of the employees in the Sales department, regardless of their gender. I know that this particular example doesn’t make much sense, but this is something that people in the management, for reasons that completely escape my intellectual hold, want to put together. Don’t underestimate such possibility, which could prove to be useful in many occasions.”

I knew by then that his enthusiasm was growing thinner, and I should have expected him to leave abruptly any moment. I had to refuel his good disposition and ask some juicy questions.

“This is really wonderful,” I said, “but in a real application you would not use SQL alone. How can I implement this algorithm with a general purpose language?”

Putting the pieces together

The wizard startled, as if awaken from a troubled sleep and suddenly his eyes were sparkling with renewed interest.

“Yes, of course,” he replied. “If we stick to one-level cross-tabulations, this algorithm will fit nicely into any high level language. We should avoid the multi-level ones, for the moment, because the general concept is more important than the thorny details. Let me draw you a flow-chart diagram.”

I had already relinquished my desktop, and I stayed quietly at his side, while he was skillfully drawing this diagram, which he explained almost as fast as he was designing.

“The required actions are quite simple. However, you need some planning before starting. First, you have to identify the source for the columns. It could be the same source from where you need to count or sum, but in a well organized and normalized database it should be in a separated table. Either way, your first action with the database will be to query for distinct column values. Then you will merge such values within the summary statements composing your query. And at that point you’ll be ready to execute. You can only omit the initial query if you are 100% sure that your values have not changed. This could be the case, for example, if your column values are in a read-only table. But usually this is not the case, or else cross-tabulations wouldn’t be that hard. Well, let’s start. Any preferred language?”

This was a false democratic question, which I knew by experience. I could mention any language and he would be proficient in it, but he would reject on some ground, until I would eventually manage to name the language he had in mind. Having gone through the motions before, I had my answer ready:

“I think that Perl would serve the purpose,” I said hastily.

He nodded, approving my wisdom, and fired a copy of vim from a xterm.

“Sorry if you are an Emacs guy,” but his voice betrayed his complete lack of sorrow, “Nothing like vim to highlight Perl syntax. I am sure you can follow me into this, and besides, I have the helm.”

That, I knew for sure. My computer was firmly in his possession, and I started doubting that I would ever have it back. The wizard, unaware of my anxiety, was already writing:

#!/usr/bin/perl -w

use DBI;
use strict;

(note: You can check here the full script, with plenty of comments).

Now, you would expect your average wizard to be adventurous and careless, keen of programming without constraints. Not this one. My wizard is a wizard because he behaves like one, but, being a database wizard, he is also very strict in matter of coding. More than once he gave me a speech on the importance of catching the errors in advance, before they can catch you. Since I knew that lecture by heart, I did not comment. My only contribution was to provide the IP address of my server and the location where the DBI could look for my user-name and password. I suspect that the wizard already knew that much, but he wanted to make me feel important by letting me provide some tiny contribution to the script.

my %params;
while (<>) { # gets the configuration from the input pipe
	next if /^#/; # skips comments
	my ($name,$value) = split '=',$_;
	chomp $value;
	$params{$name} = $value ;
}
$params{database} or die "no valid input givenn";

“We are going to read the parameters into a hash from a configuration file. They are too many for the command line, and it’s better to save them to a file, which will be even clearer. In a production case, you would rather store those parameters into a database table. Four our purposes, we are going to use a text file. Actually, let’s write it down, before we continue. I think it would be better to start with the simple case, location by gender.”

title=location-gender
database=xcompany
row_name=location
row_alias=town
col_name=gender
col_alias=gender
col_value=1
col_from=FROM employees
col_where=
col_order=
row_from=FROM locations INNER JOIN employees USING(loc_code)
row_where=
row_order=ORDER BY loc_sort_order
row_group=group by location

He saved those parameters into a location-gender-count.xtab and explained:

“See, our parameters are the description of the final query. In order to build the query, we need to set its parts. In this particular case, we are not going to use a WHERE and a ORDER BY clause for the columns, but you know that we may need them for the others, so we’ll leave them. Our parsing mechanism can easily take care of the empty strings. In this script I assume that all these parameters are properly set in the configuration file. I will skip all the error checking on that, and you can implement it later on.”

my $dbh = DBI->connect("DBI:mysql:$params{database};"
	."host=172.16.35.1;mysql_read_default_file=$ENV{HOME}/.my.cnf")
	or die "can't connect $! n";

“This is fairly simple. Just the connection to the database. Now we have a database handle $dbh and with it we can go for the first query.”

my $row_statement = "";

my $sth = $dbh->prepare("SELECT DISTINCT $params{col_name}, $params{col_alias} "
	. $params{col_from} . " " . $params{col_where} . $params{col_order});

$sth->execute();

while (my ($colname, $colalias) = $sth->fetchrow_array()) {
	$row_statement .= ", SUM(IF($params{col_name} = \"${colname}\","
	. " $params{col_value}, 0)) AS `${colalias}` n"

}

$sth->finish();
$dbh->disconnect();

I recognized the proceedings. He was instructing Perl to do the same thing that he was previously doing manually. I told him that much.

“Yes, exactly,” he approved. “And in addition to that, using a high level language will grant us some more freedom. Since we have already collected the field list from our configuration file, we are not in a hurry to compose our second query. Having the parameters in a hash will also give us some amount of control, since any missing parameter from the configuration file will be duly reported by the Perl compiler. At this point, the only thing we need to do is to gather all the pieces together and produce our final query.”

# ------------ add total column and row details

$row_statement .= ", "
        . (($params{col_value} eq "1")? "COUNT(*)" : "SUM($params{col_value})")
	. " AS total n"
	. $params{row_from}
	. $params{row_where} ; 

print "use $params{database};n";

$row_statement =~ s/nn/n/g; # remove double EOL

print "SELECT $params{row_name} AS $params{row_alias} n",
      "$row_statementn$params{row_group} n $params{row_order} ;n"; # cross table

print "SELECT 'TOTAL'n$row_statement;n"; # the total line

He continued explaining.

“Since you have seen the manual examples and the flow-chart, what we are doing shouldn’t come as a surprise to you. We add a total line, which will be a real SUM or a COUNT, depending on the parameter that was entered. Then we add the FROM and WHERE clause, and we don’t care if this last one is empty. The resulting script will include a USE database statement, followed by a query that should look much like the one we entered manually with cut-and-paste.”

I wanted desperately to enter the discussion, so I told him what was troubling me.

“Why you did not complete the $row_statement with the GROUP and ORDER-BY clauses? Why did you stop at the WHERE level?”

Almost annoyed by my lack of insight, he replied “You should see it by yourself. The $row_statement as I made it is what we have in common between the normal selection and the total line. To get the grand total you should not GROUP, and since we are going to get only one line, it doesn’t make much sense to ORDER it. Does it? Now, since you mentioned the total, this solution is just for a quick demonstration, which I can do with a single line of Perl. If your tables are those big babies with millions of record that you might have in your data warehouse, then you’d better send the output of this query to a temporary table and then get the grand total from there. For tables with less than one hundred thousand records, MySQL will slurp this kind of cross-tabs in a blink.”

He got up from my chair, and I knew that my lesson was over.

“Wait a minute.” I said hastily. “This script doesn’t execute the cross-tab query. How do you use it?”

Still halfway from my chair, he bent to the keyboard, and typed:

$ perl xtab.pl < location-gender-count.xtab | mysql -t

“Just add your password and host, if you need it, and it will give you the X-tab.”

“But? wait! I have a few questions about multi-level cross-tabulations.”

He smiled in a devilish way.

“I am sure you do. But I have a girlfriend who wants to discuss database theory with me,” he looked at his watch and added “in exactly ten minutes at that new Chinese restaurant downtown, and I shouldn’t make her wait. I think you have enough food for thought to fill your weekend. Give me a call next week, and perhaps, if I can spare half an hour, I can give you a hand.”

And while offering his metaphoric limb, he extended his real one to shake mine, and off he went before I could say ’son of a wizard!’.

Conclusion

The Wizard is a fictitious character. He does not exist in the wild, although some laboratories in Outer China are collecting evidence about the theoretical possibility that he could be real. Any resemblance with any individual in the physical world is purely coincidental. Translated into plain English: if you think that the Wizard looks like somebody you know, especially if he or she is a pain in the neck, it is because you are unlucky. Wizards are grumbling fellows who call Perl or SQL their mother tongue and feel ill at ease while speaking English (or Italian, Hungarian, Flemish or whichever language they used to speak before learning C++). They are usually kind and willing to please you, but they often manage to upset you while doing so. Wizards also enjoy gathering with others of their kind, since they find common people boring. However, if you put two wizards in the same room, you are likely to see some sparks after a while. That’s why wizards are usually alone, and they like to be left as such.

ACKNOWLEDGEMENT

I want to thank many people who made this article possible. All the programmers who created GNU, Linux, MySQL, StarOffice, perl, DBI, nedit (I am not the Wizard. I can’t cope with vi!), and many utilities without which I would be most likely writing about embedded databases in C. (There is nothing wrong about embedding databases, and doing that in C is quite honorable, since I have done that for a while, but MySQL is a toy on a totally different scale). In a word, I am grateful to all the open source community, which has made profitable exchanging knowledge (not to mention the fun!).

RESOURCES

You must have realized that I (or perhaps the Wizard) have taken for granted that you know the basics of MySQL, SQL, client server architecture, perl, the DBI, and maybe something else that I don’t recall now. Don’t be angry with me. Teaching the basics is a social duty, but sometimes it makes you feel good when you can exchange some unusual experience.

You might be interested in a few links that offer the basics that I have so happily skipped:

• http://www.mysql.com/documentation/ One of the best manuals you can find about MySQL, the one written by the developers themselves.
• In addition, http://www.devshed.com/ offers many tutorials and articles for beginners (and intermediate and expert users as well).
• DBI can be learned online at http://www.symbolstone.org/technology/perl/DBI/.

About Perl, I don’t dare suggesting anything that could contravene the official policy that TIMTOWTDI (there is more than one way to do it). Larry Wall has written some thousand pages on this subject, with the conclusion that Perl’s official policy is that there is no official policy. I would leave it at that.

SOURCE CODE

To let you appreciate the examples in this article, I prepared some scripts:

• The Xcompany dump file, when fed into MySQL, will re-create the database for you.
• The xtab.pl script, general purpose cross-tabulation builder, will let you feel the thrill first hand.
• Some configuration files for xtab.pl will spare you the boredom of re-typing.
  • location-gender-count.xtab is the simplest one. Counting of employees by ocations and gender.
  • age-gender-count.xtab will count the employees by age range (using MySQL “WHEN” instruction) and gender.
  • country-dept-salary-sum.xtab sums the salaries by country and department.
  • month-category-sales-sum.xtab will summarize monthly sales by category.

Now, please. Don’t let me do too much. Try it on your databases, and tell me how it worked.

This article is the first in an educational series offered by MySQL AB aimed towards providing the reader with valuable insight into the MySQL database server. Although future articles will delve into some of the more complicated topics surrounding MySQL, including replication, ODBC and optimization, it was thought to be prudent if the first tutorial started, well, at the beginning. Therefore the goal of this article is to thoroughly acquaint the reader with various topics surrounding the basic functioning of MySQL. A synopsis of what is covered is shown in the Table of Contents, listed below. You can go to any topic listed in the Table of Contents simply by clicking on its title.

Assumptions

At this point, it is assumed that the reader has successfully installed the MySQL database server. If MySQL has not yet been installed, please take some time to review the information provided in the installation section of the MySQL documentation. It is also assumed that mysql database has been created (using mysql_install_db), and the MySQL database server has been started using safe_mysqld. If this has not yet been accomplished, take a moment to read “Post-Installation Setup and Testing”, located in the MySQL documentation.

It is also assumed that the reader has at least a basic comprehension of SQL (Structured Query Language) syntax. For those readers new to the world of SQL, the following links point to a few particularly useful SQL tutorials:

• Philip Greenspun’s “SQL for Web Nerds”
• Mike Chapple’s Introduction to SQL
• James Hoffman’s Introduction to SQL

Table of Contents

• So You’ve Installed MySQL. Now What?
• The MySQL Configuration File: my.cnf
• The MySQL Privilege Tables
• Connecting to the MySQL Server For the First Time
  • Exiting and Reconnecting to the MySQL Monitor
    • Careful With That Password!
• Selecting a Database
• mysqladmin
• Securing a Database
  • The GRANT Command
  • The REVOKE Command
• Database Backups
  • mysqldump
  • mysqlhotcopy
• Conclusion

So You’ve Installed MySQL. Now What?

The installation instructions were scrutinized, the latest distribution was downloaded, coffee was brewed and drank and brewed again. The familiar configure, make and make install were wielded to once again produce another beautifully compiled application. Nods were exchanged, pats on the back traded, frothy capuccino toasts are proposed in succession. Yes, there is reason to celebrate in the office today, as the MySQL database server has been successfully installed.

You lounge back in your deskchair, surrounded by colleagues hailing the wisdom of you, the newly-christened MySQL administrator. If they only knew the pain and anguish swirling around in your mind right now, as you ponder the question, “So now what?”.

The purpose of this tutorial is to acquaint new MySQL users with several of the key aspects of this wonderful database server. Issues regarding general server functionality, security, user and privilege administration, working with databases and tables, and data backups will all be introduced to some degree. While the reader will likely find much of this material easy to understand, keep in mind that these concepts lay much of the foundation for efficiently and properly working with the MySQL database server, in addition to implementing more complicated aspects which will be discussed in later tutorials. Therefore it is suggested that the reader take the time to not only read the tutorial, but also to actually follow along with the steps described herein, experimenting with his own MySQL installation.

The MySQL Configuration File: my.cnf

It’s very likely that the first task the administrator will want to undertake is proper configuration of MySQL’s configuration file. This file, entitled my.cnf, stores default startup options for both the server and for clients. Correct configuration of this file can go a long way towards optimizing MySQL, as various memory buffer settings and other valuable options can be set here.

Interestingly, the scope of this file can be set according to its location. The settings will be considered global to all MySQL servers if stored in /etc/my.cnf. It will be global to a specific server if located in the directory where the MySQL databases are stored (/usr/local/mysql/data for a binary installation, or /usr/local/var for a source installation). Finally, its scope could be limited to a specific user if located in the home directory of the MySQL user (~/.my.cnf). Keep in mind that even if MySQL does locate a my.cnf file in /etc/my.cnf (global to all MySQL servers on that machine), it will continue its search for a server-specific file, and then a user-specific file. You can think of the final configuration settings as being the result of the /etc/my.cnf, mysql-data-dir/my.cnf, and ~/.my.cnf files.

In order to aid administrator’s in the proper configuration of this file, the MySQL developers have included four sample my.cnf files within the distribution. Their names are my-huge.cnf.sh, my-large.cnf.sh, my-medium.cnf.sh, and my-small.cnf.sh, and each denotes recommended configuration settings in accordance with system resource availability.

Further Reading

• Option Files

The MySQL Privilege Tables

Before delving into the many examples that consititute this tutorial, a brief introduction of one of the most important (and most misunderstood!) aspects of the MySQL server is in order; that is the mechanism from which MySQL secures its data and integrity: The MySQL privilege tables. The MySQL privilege tables are responsible for authenticating user access to the MySQL server, and subsequently associating those users granted access with a set of privileges. This privilege set decides what a user is capable of doing while connected to the MySQL server, controlling the user’s activities on a server-wide, database, tabular and even columnar level. For example, an administrator could grant a user only enough privileges to connect to one specific MySQL database, and restrict access to all others. Furthermore, that same user might be granted only certain privileges while connected to that database, selection, insertion, and modification privileges for example. Associated with only these three privileges, that user would be denied any attempt to delete data, since the user has not been granted the deletion privilege.

Although in-depth introduction to the privilege tables is out of the scope of this article, it is important that the reader understands the very important role these tables play in securing the MySQL server. While working through the examples presented throughout the remainder of this article, keep in mind that the privilege tables are playing a role in every single query and command that takes place, ensuring that the user executing these commands/queries has proper permissions for doing so. It is also strongly recommended that the reader takes some time to review the links listed below, as each describes in great detail the underlying mechanics of these tables.

Further Reading

• How the Privilege System Works
• How to Make MySQL Secure Against Crackers
• Adding New User Privileges to MySQL

Connecting to the MySQL Server For the First Time

The MySQL client program, also known as the MySQL monitor, is an interface that allows the user to connect to the MySQL server, create and modify databases, and execute queries and view their results. This program is started by executing the command mysql at the shell prompt. In general, the syntax for this command is:

%>mysql [options] [database]

Where [options] can be one or a series of options used in conjunction with the mysql program, and [database] is the name of the database to use. Since it is assumed to be the reader’s first time using the MySQL monitor, take a moment to review all offered options by executing the following command:

%>mysql --help

This produces a long list of options that can be used in conjunction with the mysql program. For the moment, however, the main goal is to simply connect to the database server. Therefore, execute the following command:

%>mysql -u root

The following should appear:

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 8 to server version: 3.23.28-gamma-log

Type 'help;' or 'h' for help. Type 'c' to clear the buffer

mysql>

Congratulations, you are now connected to the MySQL monitor as the almighty root user. Your first official action as this supreme leader of the MySQL database server should be to ensure that nobody else can declare this position. Therefore, make it possible to only connect as root in the future by supplying a password. Change the password from its current blank (or null) value, to something difficult to guess using the following command:

mysql>SET PASSWORD FOR 'root'@'localhost' = PASSWORD('secret_password');

The ‘root’, which is the username, and ‘localhost’, which is the hostname, constitute a unique user in MySQL. For those readers perhaps unfamiliar with networking terminology, ‘localhost’ is a name used to specify the local server; that is, the server upon which MySQL resides. Therefore, by stating ‘root’@'localhost’, this command is telling the MySQL server to set the password for a user named ‘root’ that will connect specifically from the local server (thus ‘localhost’). More specifically, this command will change the password by updating what are commonly known as the MySQL privilege tables. These tables, collectively located in the mysql database, contain information regarding the connection and usage capabilities of all users intended to use the MySQL database server. More specifically, this command will update the user table, updating the password field of the row in which the user field’s value is ‘root’. The password field will be updated with the encrypted value of the string enclosed within the Password() function.

Of course, do not forget this password. Since it is stored in encrypted text on the database server, it cannot simply be looked up if forgotten.

There is also an alternative method for updating a password:

%>mysqladmin -u root  password 'secret_password'

This command will accomplish the same results as the one previously introduced.

Exiting and Reconnecting to the MySQL Monitor

In order to test the new password, exit the MySQL database using the following command:

mysql>q

This will return you to the system shell. Now log back into the monitor, this time using the following command:

%>mysql -u root -p

Doing so will result in a prompt for the root user password, as follows:

Enter password:

Go ahead and enter the password supplied within the update command that was used to set the root password. Assuming it is entered correctly, the standard MySQL greeting will appear, and root will be connected to the MySQL server once again.

Careful With That Password! Many readers may be tempted to instead try to include the password on the same line as the mysql connection command, as follows:

%>mysql -u root -psecret_password

Do not do this! Not only is it a highly insecure method for entering the password, but it will not produce the expected results! It is insecure not only because it will allow any onlookers the possibility of seeing the password in its plaintext format, but also because any user can use the Unix ‘ps’ command to look at what commands you are executing and view the password in its plaintext format from there.

It might be a good idea to store the password in your my.cnf configuration file, located in ~/.my.cnf. If you don’t know what this file is, please read the earlier section entitledThe MySQL Configuration File: my.cnf.

Selecting a Database

Of course, simply connecting to the MySQL server isn’t going to accomplish much. Chances are you will want to select a database to work with. This is accomplished in one of two ways:

1. Including the name of the database along with the mysql connection command. For example, the command used to both connect to the MySQL server and select the mysql database is:

   %>mysql -u root -p mysql

   This might be misleading for some readers, as it seems as if the intent is to input mysql as the password. This is not correct. Take a moment to review the syntax as described in the mysql –help output, and it will be apparent that -u root -p actually comprise the [options] component of the syntax, and mysql comprises the [database] component.

2. Once connected, select the database using the use command, as follows:

   mysql>use mydatabase

Once executed, all queries not explicitly specifying a database name will be directed towards the hypothetical mydatabase database.

mysqladmin

The mysqladmin program is used to administrate various aspects of the MySQL database server. Using it, the administrator can perform tasks such as: create and delete databases, shutdown the database server, update the privilege tables, and view running MySQL processes. The general syntax is:

%>mysqladmin [options] command(s)

Where [options] can be one or a series of options used in conjunction with the mysqladmin program, and [database] is the name of the database to use. Since it is assumed to be the reader’s first time using the MySQL monitor, take a moment to review all offered options by executing the following command:

%>mysqladmin --help

This produces a long list of options that can be used in conjunction with the mysqladmin program. As a demonstration of how these options are used, let’s use mysqladmin to create a new database named widgets, which will be used throughout the remainder of this article to demonstrate various other useful MySQL functions. A new database is created as follows:

%>mysqladmin -u root -p create widgets
Enter Password:

Upon execution, mysqladmin will create the database and return to the shell prompt. Typically, the next step is to secure the new database by modifying the privilege tables. Details regarding how this is accomplished is the subject of the next section.

Further Reading

• Administering a MySQL Server

Securing a Database

Security should be the first thought that comes to a MySQL administrator’s mind after creating a new database. Privilege as was discussed in the section, “The Privilege Tables”, securing a database is accomplished through modifications made to the tables found in the mysql database. In this section, the reader will learn how to secure the newly created widgets database. Before doing so, a brief summary of exactly how the privilege tables are modified is in order.

There are two methods used to modify the privilege tables. The first is through the use of typical SQL statements such as INSERT, UPDATE and DELETE. However, this method has largely been deprecated through the introduction of the second method, which involves the use of the special commands GRANT and REVOKE. Therefore, only this second method will be discussed in this section.

The Grant Command

The GRANT function is used both to create new users, and to assign privileges to users. Its syntax is:

mysql>GRANT priv_type [(column_list)] [, priv_type [(column_list)] ...]
      ON {tbl_name | * | *.* | db_name.*}
      TO user_name [IDENTIFIED BY 'password']
      [, user_name [IDENTIFIED BY 'password'] ...]
      [WITH GRANT OPTION]

An understanding of how GRANT works is best gained through examples. In the first example, GRANT is used to add a new user to the database. This user will be used to access the widgets database:

mysql>GRANT usage ON *.* TO widgetAdmin@localhost
    ->IDENTIFIED BY 'ilovewidgets';

This will create a new user named widgetAdmin, capable of connecting to the MySQL database server via the host localhost using the password ilovewidgets. Keep in mind that this only grants connection privileges. It will not allow the user to do anything with the MySQL server! Go ahead and switch to the mysql database and execute the following query:

mysql>SELECT * FROM user;

Notice that the row containing the widgetAdmin user has N values for all of the privileges. This is good, since the user table contains a user’s global privilege settings. To clarify this, if a Y value is set for any user’s privilege in the user table, that user can apply that privilege to any MySQL database. Therefore, it is almost always best to set all privileges to N within this table.

So how then are user privileges assigned for a particular database? This is easily done just like the usage privilege was set in the previous example. For example, assume that the administrator wanted to grant user widgetAdmin with SELECT, INSERT, UPDATE and DELETE privileges for the widget database. This is accomplished using the following GRANT command:

mysql>GRANT SELECT, INSERT, UPDATE, DELETE
    ->ON widgets.* TO widgetAdmin@localhost;

Upon execution, the user widgetAdmin can immediately begin using these privileges.

The privileges introduced thus far are not the only ones available to the administrator. Table 1-1 provides a listing of all available privileges.

Table 1-1: Privileges available for use within GRANT and REVOKE commands

To view the new privilege table updates, execute the following query:

mysql>SELECT * FROM db;

Notice that a row has been added to the db table for user widgetAdmin, with Y values assigned to the SELECT, INSERT, UPDATE and DELETE fields.

Incidentally, it is also possible to bypass the usage query, instead both creating the new user and assigning user privileges simply by executing a variation of the previous query:

mysql>GRANT SELECT, INSERT, UPDATE, DELETE
    ->ON widgets.* TO widgetAdmin@localhost
    ->IDENTIFIED BY 'ilovewidgets';

Assuming that the user widgetAdmin did not yet exist when this query is executed, both the user and db tables will be updated with the necessary rows.

Of course, the administrator can revoke previously granted privileges. Exactly how this is accomplished in discussed in the following section.

The Revoke Command

The REVOKE command is used to rescind privileges previously granted to a user. Its syntax is:

REVOKE priv_type [(column_list)] [, priv_type [(column_list)] ...]
       ON {tbl_name | * | *.* | db_name.*}
       FROM user_name [, user_name ...]

As is the case with the GRANT command, perhaps the best way to really understand how it operates is to experiment with several examples. Assume that the administrator wants to repeal the DELETE privilege from the user widgetAdmin. This is accomplished using the following command:

mysql>REVOKE DELETE ON widgets.*
    ->FROM widgetAdmin@localhost;

Refer to Table 1-1 for a complete listing of privilege types which can be used within the REVOKE command.

One point to keep in mind is that while REVOKE can remove all privileges (including connection privileges) from a user, it does not explicitly remove that user from the privilege tables. To illustrate this, consider the following command:

mysql>REVOKE ALL PRIVILEGES ON widgets.*
    ->FROM widgetAdmin@localhost;

While this would result in all privileges being revoked from the user widgetAdmin, it would not delete the relevant rows from the privilege tables! If completely removing the user from the database is the intention, the rows would have to be removed using the delete command, as follows:

mysql>DELETE FROM user WHERE user = 'widgetAdmin';
Query OK, 1 row affected (0.00 sec)
mysql>flush privileges;

This will effectively deny that user from connecting to the MySQL server. Note that rows from the user table will have to be explicitly removed using DELETE should the administrator wish to entirely remove the user from the privilege tables.

Further Reading

• Grant and Revoke Syntax

Database Backups

The final concept discussed in this tutorial is indeed an important one: data backups. In this section, two methods for making backups of MySQL data and database structures are discussed, namely mysqldump and mysqlhotcopy.

mysqldump

The utility mysqldump provides a rather convenient way to dump existing data and table structures. Note that while mysqldump is not the most efficient method for creating backups (mysqlhotcopy is, described next), it does offer a convenient method for copying data and table structures which could then be used to repopulate another SQL server, that server not even necessarily being MySQL.

The function mysqldump can be used to backup all databases, several databases, one database, or just certain tables within a given database. In this section, the syntax involved with each scenario is provided, followed with a few examples.

Using mysqldump to backup just one database:

%>mysqldump [options] db_name

Using mysqldump to backup several tables within a database:

%>mysqldump [options] db_name table1 table2 . . . tableN

Using mysqldump to backup several databases:

%>mysqldump [options] --databases [options] db_name1 db_name2 . . . db_nameN

Using mysqldump to backup all databases:

%>mysqldump [options] --all-databases [options]

The options can be viewed by executing the following command:

%>mysqldump --help

Examples:

Backing up both the structure and data found within the widgets database would be accomplished as follows:

%>mysqldump -u root -p --opt widgets

Alternatively, perhaps just a backup of the data is required. This is accomplished by including the option –no-create-info, which means no table creation data:

%>mysqldump -u root -p --no-create-info widgets

Another variation is just to backup the table structure. This is accompished by including the option –no-data, which means no table data:

%>mysqldump -u root -p --no-data widgets

If you are planning on using mysqldump for reason of backing up data so it can be moved to another MySQL server, it is recommended that you use the option ‘–opt’. This will give you an optimized dump which will result in a faster read time when you later load it to another MySQL server.

While mysqldump provides a convenient method for backing up data, there is a second method which is both faster and more efficient. It is described in the next section.

mysqlhotcopy

The mysqlhotcopy utility is a perl script that uses several basic system and SQL commands to backup a database. More specifically, it will lock the tables, flush the tables, make a copy, and unlock the tables. Although it is the fastest method available for backing up a MySQL database, it is limited to backing up only those databases residing on the same machine as where it is executed.

The function mysqlhotcopy can be executed to backup one database, a number of databases, or only those databases matching a name specified by a regular expression. In this section, the syntax involved with each scenario is provided, followed with a few examples.

Using mysqlhotcopy to backup just one database:

%>mysqlhotcopy [options] db_name /path/to/new_directory

Using mysqlhotcopy to backup just several databases:

%>mysqlhotcopy [options] db_name_1 ... db_name_n /path/to/new_directory

Using mysqlhotcopy to backup only those tables within a given database that match a regular expression:

%>mysqlhotcopy [options] db_name./regex/

The options can be viewed by executing the following command:

%>mysqlhotcopy --help

Examples:
Experiment with mysqlhotcopy by backing up the widgets database to the directory path “/usr/mysql/backups/”. Execute the following command:

%>mysqlhotcopy -u root -p widgets /usr/mysql/backups

As a second example, assume that the widgets database contains the tables: “products2000″, “products2001″, “clientele2000″, and “clientele2001″, with the four digits at the end of each name representing the year for which that data represents. The administrator wants to backup only those tables relative to the year “2000″:

%>mysqlhotcopy -u root -p widgets./^.+('2000')$/ /usr/mysql/backups

In the above example, the regular expression /^.+(’2000′)$/ tells mysqlhotcopy to only backup those tables ending with the string “2000″.

Further Reading

• Dumping MySQL Database and Table Structures and Data
• Replication in MySQL
• Copying MySQL Databases and Tables

Conclusion

The goal of this tutorial was to introduce the essential topics which surround the basic functionality of the MySQL server. It is suggested that the novice reader devote some time to experimenting with all of the examples, as “learning by doing” is certainly the fastest way to become comfortable with MySQL.

Next time, we’ll take a look at some general configuration issues, focusing on the my.cnf files first introduced at the beginning of this tutorial.

By Ernest Bonat, Ph.D.
Visual WWW, Inc.

Introduction
Required Software
LoopUp Table Data Loading
Generic ListItemClass Class
LoopUp Project Example
Getting Selected ID and Name Values
Finding ID and Name Values
Finding the State and Capital Names by Postal Code
Executing Stored Procedures in MySQL 5.0/VB.NET 2005
Conclusions

Introduction

Lookup tables contain, in general, a fixed list of data. This data doesn’t change very often in database business applications. Examples of this data could be a product list, category type, supplier list, state name, zip code, phone area code, etc. In Windows and Internet web business applications, most of these lookup tables are graphically implemented by using ComboBox, ListBox or CheckListBox read-only controls. These controls are loaded with data using two main columns, ID and Name. For example, the USA state table, the ID could be ‘CA’ and the Name ‘California’. Some times, for standard Windows form and Internet web page we need to show data to the end-users from many of these lookup tables. A fast data loading process and defining the main column values for each lookup table is required. In this article I will show you standard lookup data loading procedure and the generic classes object to store and read-only the values of the ID and Name columns from the lookup tables. Selecting and finding the ID and Name values will be provided. Executing stored procedures with input/output parameters in MySQL 5.0/VB.NET 2005 will be covered in detail too.

Required Software

• MySQL Database Server 5.0.41
• MySQL Connector/NET 5.0.6
• Toad for MySQL Freeware 2.0.3
• Microsoft Visual Basic 2005 Express Edition

LoopUp Table Data Loading

Let’s look for a simple way to load the data in a ComboBox from a lookup table. Because we may have many lookup tables, it makes sense to develop a generic class for data loading from these tables. Listing 1 shows the structure of the LookUpClass public class within the LoopUpLibrary Namespace. The MySql.Data.MySqlClient library has been imported to reference the Connector/NET 5.0.6. The standard public class ObjectDisposeClass was included for releasing .NET unmanaged resources. Inside the LookUpClass class body we will develop our custom properties, methods and events.

Imports MySql.Data.MySqlClient
Namespace LoopUpLibrary
    Public Class LoopUpClass
        Inherits ObjectDisposeClass
        ' Developed custom properties, methods and events
    End Class
#Region " IDisposable Object ..."
    Public Class ObjectDisposeClass
        Implements IDisposable
        Private disposedValue As Boolean = False
        Public Sub Dispose() Implements IDisposable.Dispose
            Dispose(True)
            GC.SuppressFinalize(Me)
        End Sub
        Protected Overridable Sub Dispose(ByVal disposing As Boolean)
            If Not Me.disposedValue Then
                If disposing Then
                    ' TODO: free unmanaged resources when explicitly called
                End If
                ' TODO: free shared unmanaged resources
            End If
            Me.disposedValue = True
        End Sub
    End Class
#End Region
End Namespace

Listing 1: LookUpClass class structure

The LoopUpDataLoad() subroutine is shown in Listing 2. This subroutine has two main input parameters, the control to be loaded (pComboBox) and the stored procedure (pStoredProcedureString) that contains the SQL Select statement of the Lookup table. Some user stored procedures examples will be explained late in this article (Listing 5 and 13). As we know, MySQL Server 5.0 introduces, for the first time, the capability of development stored procedures, functions, triggers and views database objects. Because of that, instead of passing a dynamic SQL Select statement, the stored procedure name is passed by value to the subroutine. Stored procedures offer several distinct advantages over dynamic (embedding) SQL in your application code. In the future, I’m going write a single paper about stored procedures development and implementation using MySQL 5.0/VB.NET 2005. I can see many Open Source application developers still using dynamic or/and parameterized SQL statements today with MySQL Server 5.0. It seems to me that they don’t know how to design and develop stored procedures using SQL:2003 language. Just in case, a very good reference book about this topic was published in 2006 by O’Reilly Media, Inc., ISBN: 0-596-10089-2, “MySQL Stored Procedure Programming” by Guy Harrison and Steven Feuerstein. I highly recommend reading this book for any Windows or Internet web application development with MySQL 5.0 database engine.

Public Sub LoopUpDataLoad(ByVal pComboBox As ComboBox, _