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.
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.
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)
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.