Auto generated SQL

  • Hi,

    (Note to begin: Sorry if this is the wrong forum to put this in.)

    I have just finished writing a VB.Net component for mining data from a SQL db (doesn't strictly have to be T-SQL, just needs to support joins using the LEFT/RIGHT etc syntax rather than ANSI *=)

    I wondered if I could get any feedback from people as to whether it is the sort of thing which might be genuinly useful for people.

    The idea is that you can, free form, pick any fields you would like from the db and the component will work out the join strategy which allows these fields to be selected (a little like FireFox).

    It will allow for there to be one or more PK-FK relationship between any pair of tables (these do not have to be SQL enforced PK-FK relations just relationships from an underlying data perspective)

    It will even cope with situations where there are multiple paths of tables which link a pair of tables together (Something which I think no other system can deal with)

    It cannot (Because I believe it is impossible?) deal with self referencing joins (but these can almost always be collapsed to the underlying data with a suitable view)

    At the moment their are three steps needed to configure the component

    1.Create a list of tables you would like to be able to use in any queries.

    2.Create a list of fields you would like to make available (there is an option to have all the fields in the table populated automatically)

    3.Define the 'real' PK-FK relations in the data (This is not necessarily a mapping of the SQL constraints but will very broadly be the same) (again there is an option to gather the info from the SQL db structure)

    Thats it

    To use it

    Create an instance of the component

    .Add the fields you want (say if you want 'all rows' which will use an appropriate OUTER JOIN)

    get the SQL with a from the .SQL property

    Execute it!

    Absolutely any comments very welcome. Particularly if they are constructive.

    The reason I built this was as part of a reporting engine which allows completely freeform selection of data by the users to mine add hoc data.

    Thanks in advance

    Stephen

  • This was removed by the editor as SPAM

  • It is definitely a useful procedure to have, especially when there are cases when you would like to be left alone and get on with the real work instead of being pestered by middle management or colleagues for more and more arcane reports!

    Cheers,

    GdeF

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply