MySQL

Boilerplate code is equal copies of code that you have to repeat many times in a program to make it work. This includes stuff like checking return values from library functions and printing error messages, or to including modules and header files at the top of a program.

MySQL prepared statements in Java

One of the worst time-consuming copy-paste-editing you will ever come over is creating a prepared statement. Under follows an example in the Java-language (which is copy-pasted-edited from some page found on Google). Notice that the word PreparedStatement is used seven times, and that you have to deal with the member methods setString and setInt of this object for every single variable.

Class.forName("com.mysql.jdbc.Driver");
connect = DriverManager.getConnection("jdbc:mysql://localhost/animal_kingdom?" + "user=dog&password=i_hate_cats");

// It is extremely boring work to set all this fields
preparedStatement = connect.prepareStatement("INSERT INTO animal_kingdom.persons VALUES (?,?,?,?,?)");
preparedStatement.setString(1, "Jo");
preparedStatement.setString(2, "Lene");
preparedStatement.setString(3, "jolene@hotmail.com");

// Sometimes we need to store strange properties of a person
preparedStatement.setInt(4, 135);
preparedStatement.setInt(5, 4);
preparedStatement.executeUpdate();

You begin to wonder why on earth you have to tell Java that "Jo" is a string type. You also have the manual work of counting all the question marks and setting the variables in correct order. This work, of course, becomes really fun when you also have to deal with column names in the query and you have 20 columns of different types to insert.

The P* author, also referred to as 'someone' in this article, has once been forced to write a wrapper class just to keep track of column names, values and question marks. We don't want to spend time doing this, we want to create web pages! Let's have some more meat.

MySQL prepared statements in P*

The Windows build of P* does not yet support MySQL

Next comes the same example in P*, lets just specify the variables first instead of inlining, like you would normally do:


string name = "Jo";
string surname = "Lene";
string email = "jolene@hotmail.com";
int weight = 135;   /* In kilograms */
int width = 4;      /* In feet */

SQL sql {
	INSERT INTO animal_kingdom.persons VALUES (
		{@name}, {@surname}, {@email}, {@weight}, {@width}
	)
}

MYSQL mysql->connect("localhost", "dog", "i_hate_cats");
mysql->select_db("animal_kingdom");

MYSQL_STMT stmt = mysql->new_statement();
stmt->prepare(sql);
stmt->execute();

Common reactions to this example

  • Wait a minute...You said you wouldn't inline the variables, but you did?
  • You idiot!!! This is SQL -- IN -- JEC -- TION -- !!!!

Counter-strike

This way of specifying a prepared statement is P* magic. We would not normally, and shouln't either, put variables inside the SQL-string. However, because we are lazy (and to avoid the horrible and error-prone code you see in the first example), we might be tempted to do just that. With P* you can put the variables inside the SQL-string WITHOUT them being concatenated.

The whole SQL-object is a chain of strings and value pointers, and P* does the dirty work of passing each variable to MySQL with the correct type set. The variables are replaced with question marks before passed to MySQL, making it look like the Java-example.