SQL macros

Table of Contents Checking SQL queries Avoiding modification of database Safe passing values of variables to queries Automatic loop and result variables creation

Checking SQL queries

Our library provides special functions (macros) for executing SQL queries. They have similar functionality to methods of System.Data.SqlClient.SqlCommand class, but all the strings passed to them are verified at compile-time. They are being sent to database by compiler, so the database provider is used here as a verification program. If it returns an error, the compilation is stopped with a message pointing to an invalid SQL statement.

Avoiding modification of database

ExecuteNonQuery ("INSERT INTO employee VALUES ('John', 'Boo')", conn);

When the compiler executes any query, it adds a transaction around it and makes a rollback after the execution to avoid modification of the database. So, an SQL statement is executed here to verify if it is correct and then it is reverted.

Safe passing values of variables to queries

Most of the queries in application are parametrized with program variables. For example, we read an employee name from a form and then search for corresponding entries in the database. In such case we want to use some variable inside the query. We can obtain it in Nemerle functions by writing the $ character followed by the name of variable.

def myparm = "John";
def count = ExecuteScalar ("SELECT COUNT FROM employee WHERE firstname = $myparm", 
                           dbcon);

Note that passing the value of myparm is done safely using .NET database provider SqlParameter class. This prevents an often used technique of exploiting database applications using SQL code insertion (if a parameter is inserted as a string, one could set its value to some malicious SQL code)

Automatic loop and result variables creation

Because we run queries at compile-time, we can obtain additional information useful for compilation. For example, we know which columns of the table were returned by the query and what are their types. This way the compiler can automatically declare local variables and assign to them corresponding values from the result.

ExecuteReaderLoop ("SELECT * FROM employee WHERE firstname = $myparm", 
                   dbcon, 
{
  Nemerle.IO.printf ("Name: %s %s\n", firstname, lastname)
});

The example above shows even more features. It creates a loop reading rows returned from selection one by one. For each of them, it declares variables containing values from all columns as mentioned before. Additionally, the entire query is created using a myparm variable from program scope.

You might want to see the full code of the above examples see.