Re: Generating SQL

From: Mark Nicholls (Nicholls.Mark_at_mtvne.com)
Date: 11/08/04 

Date: 8 Nov 2004 01:46:41 -0800

"Otavio C. Decio" <REMOVEodecio@earthlink.REMOVE.net> wrote in message news:<r7KdnWnCSqFhtxPcRVn-qg@giganews.com>...
> Sorry for the bad formatting. Hope this will show up better:
>
>
>
> ArrayList songArray;
>
> /* Entity class, basically a bunch of property
> sets/gets */
>
> Song song;
>
> /* Helper class to define search criteria = WHERE clause */
> SearchCriteria sc = new SearchCriteria();
>
> /* Overloaded function to define bindings of various types */
> sc.AddBinding(SearchBoolean.AND, "Singer",SearchOperator.EQUAL,"Rammstein");
>
>
> /* DAO will use the type of the Song object, reflect it and generate the SQL
> */
> songArray = DAO.Load(sc, typeof(Song));
>
> /*//Notice that I have strong typing here */
> ((Song)soft[0]).mp3Comments = "Big band";
>
> /*Same concept here, reflection and attributes will create the appropriate
> SQL and return # of records affected. */
> iCount = DAO.Update((Song)soft[0]);
>
>
> "Otavio C. Decio" <REMOVEodecio@earthlink.REMOVE.net> wrote in message
> news:3bSdnfNXfd-XtxPcRVn-iw@giganews.com...
> > Mark,
> >
> > Here's an example of something I wrote (C#):
> >
> > ArrayList songArray;
> > Song song; //Entity class, basically a bunch of property
> > sets/gets
> > SearchCriteria sc = new SearchCriteria(); //Helper class to define
> > search criteria = WHERE clause
> > sc.AddBinding(SearchBoolean.AND,
> "Singer",SearchOperator.EQUAL,"Rammstein");
> > //Overloaded function to define bindings of various types
> > songArray = DAO.Load(sc, typeof(Song)); // DAO will use the type of the
> > Song object, reflect it and generate the SQL
> > ((Song)soft[0]).mp3Comments = "Big band"; //Notice that I have strong
> > typing here
> > iCount = DAO.Update((Song)soft[0]); //Same concept here, reflection and
> > attributes will create the appropriate SQL and return # of records
> affected.
> >
> > Seriously, do you think somethink like this would be helpful? I developed
> an
> > entire application using this, and it is in production right now. But as I
> > wrote it by myself I have very little feedback on its usability.
> >
> > Otavio
> >
> >

I've done similar things, but I've only really got as far as single
query/filter. I like it because I can autogenerate the SQL when the
data dictionary changes, change visibility of field to different
'views', etc etc, there was always a problem though when the SQL got a
bit fiddly...i.e. inner queries, self joins, not impossible but it all
gets a little messy.

I think there is value here. Apart from the above and the obvious
isolation of the SQL dialect (or other) from the code, I think it
makes sense to express the logic in the same language as the code
itself and simply map that to SQL when required.

I suspect you did it for a reason...did you think it was a good idea?

Relevant Pages

  • Re: SQL Puzzle - too many dimensions
    ... Oracle, DB2, PostgreSQL, Derby, H2, SQL Server and a few others are excellent DBMS products. ... SONG_ID | SONG_VERSION_ID | RATING | ... And I have three versions of this song, one by Barrett Strong, one by ... this is a Java newsgroup. ...
    (comp.lang.java.programmer)
  • Re: SQL Puzzle - too many dimensions
    ... I know this is a java group, not a SQL group, I am trying to bounce ... So I've got a table, song_versions, in an Oracle database. ... SONG_ID | SONG_VERSION_ID | RATING | ... And I have three versions of this song, one by Barrett Strong, one by ...
    (comp.lang.java.programmer)
  • SQL Puzzle - too many dimensions
    ... I know this is a java group, not a SQL group, I am trying to bounce ... And I have three versions of this song, one by Barrett Strong, one by ... My job is to come up with one SQL query which will return the ... the numerical highest rating for each song, ...
    (comp.lang.java.programmer)
  • Re: Exclude duplicates in a report...
    ... From this you are able to print out the song lists they use. ... Up until now, any duplicate songs that appear on say, 2 CD's they own ... What I would like to do is change the SQL source of the report to ... Public Function IsDupe(strTrack as string, ...
    (comp.databases.ms-access)