[nem-en] SQL macros (dataSqlClient.n)

[Ildar Mulyukov]

	Hello, Kamil,
almost month has passed. I started to use the new set of macros, and it  
appeared not as cool, as I expected.

My idea of this piece of code is that it is intended to be cute (emm, I  
mean, useful), and DB-independent. This means, that one develop a DB  
application with one DBMS and deploy it with another. In my case, it's  
SQLite for developing and MySQL for deploying. This may (or may not)  
imply one recompilation before installation.

Unfortunately it doesn't work that way. In the example below (by  
Kamil), conn is Mono.Data.SqliteClient.SqliteConnection . When I  
declare conn:IDbConnection , compilation brakes down. So apparently one  
cannot make one code working with different databases.

What do you think? Is it worth to make a ":>" operation in macros  
ExecuteReaderLoop etc.?

I expect opinions. Thanks for your attention.

Regards, Ildar.

On 23.10.2006 02:02:03, Kamil Skalski wrote:
>  I have managed to move sql macros implementation to single common   
> code  base, which is now included in Nemerle.Macros.dll. It uses  
> reflection  in single place - to instantiate Connection object of  
> chosen DB  provider and then operates only on IDbConnection,  
> IDbCommand, etc.  interfaces.
> Now, to use sql macros in Nemerle, you just need following code  
> template:
> using Nemerle.Data;
> 
> [assembly: ConfigureConnection
> ("Mono.Data.SqliteClient.SqliteConnection",
> "URI=file://data.db,Version=3")]
> 
> module M {
>    Main () : void {
>     def conn = Mono.Data.SqliteClient.SqliteConnection
> ("URI=file://data.db,Version=3,timeout=2000");
>     conn.Open ();
>     ExecuteReaderLoop ("SELECT * FROM terminal", conn, {
>       System.Console.WriteLine ($"id=$id, nr=$nr, address=$address,
> deleted=$deleted, userid=$userid");
>     });
>     conn.Close (); } }
> 
>  ConfigureConnection is a macro, which takes full name of DB provider  
> and a "compile-time" connection string.  You also need to reference  
> assembly, which contains the specified  connection class.
> 
>  To add support for new DB provider... you don't need to do  anything  
> It should work for all of them just as it is... except for one minor   
> issue - the prefix used in sql parameter names. For example SQL   
> Server  uses '@' char and Postgres / Sqlite uses ':'   So I have made  
> ':' the  default one - if some other is necessary it must be added to  
> supported  cases in macros/Data.n
> 
>  It seems to work fine for me with SQL Server, Postrgress and Sqlite.  
> However in Sqlite the macros do not show their full power, because   
> Mono Sqlite provider do not give us any useful information about   
> types  of query result columns with GetSchemaTable() method - so all  
> local  variables introduced by macro will have default type = string.

-- 
Ildar  Mulyukov,  free SW designer/programmer
================================================
email: ildar at users.sourceforge.net
projects: http://os-development.sourceforge.net/
home: http://tuganger.narod.ru/
ALT Linux Sisyphus
================================================