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

[Kamil Skalski]

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 :D
 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.


On 10/21/06, Kamil Skalski <kamil.skalski at gmail.com> wrote:
> Ok, so probably the best way is to make the Helper module encapsulate
> all the DB access during compile-time. If it used the
> Mono.Data.SqlCharp, then we could have the single macro library
> generating code for various DB providers.
>
> On 10/21/06, Ildar Mulyukov <ildar at users.sourceforge.net> wrote:
> > On 18.10.2006 15:37:05, Kamil Skalski wrote:
> > >  And for the compile-time execution I cannot see other choices than
> > > using reflection to instantiate provider specific objects and having
> > > the customized implementation of stuff, where providers differ
> > > between  each other in some helper classes. Making this
> > > provider-independent  seems very hard and not worth the effort though.
> >
> >         Hi!
> > I've found one nice library, that already implements the reflection for
> > databases. It is Mono.Data.SqlSharp.DatabaseBrowser.dll from the
> > SqlSharpGtk project:
> > http://forge.novell.com/modules/xfmod/project/?sqlsharpgtk
> >
> > Another code example is in Mono tree itself, called SQL#:
> > mono-1.1.17.2/mcs/tools/sqlsharp ,
> > though this one is just a standalone application.
> >
> > IMO it's worthy to redesign dataSqlClient.n to use
> > Mono.Data.SqlSharp.DatabaseBrowser.dll and have a ready-to-use database
> > independent Macro.
> >
> > Regards, Ildar.
> > --
> > 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
> > ================================================
> >
> > _______________________________________________
> > https://nemerle.org/mailman/listinfo/devel-en
> >
>
>
> --
> Kamil Skalski
> http://nazgul.omega.pl
>


-- 
Kamil Skalski
http://nazgul.omega.pl