Re: What's your favorite SQL generation module?

There are arguments for and against SQL generation.

Coming from a DBA background I personally tend to do a minimal wrapping of DBI as a lot of these modules don't process transactions effectively and can be quite difficult to get to write optimised SQL code.

SQL::Interpolate does a mimimal amount of work for you. Allowing you to be helped by it(i.e. selects, inserts, updates especially) but still have control enough to bypass it during a transaction if you have to.

My opinion is you should always hand code a delete if you have to do it as these are too risky to leave to a code generator. However it is better to do a logical delete and then you can roll it back at any time.

Hope it helps
UnderMIne

Comment on Re: What's your favorite SQL generation module?

Replies are listed 'Best First'.
Re^2: What's your favorite SQL generation module? by perrin (Chancellor) on Jun 07, 2006 at 11:56 UTC
I have data from a form that has to be turned into a select statement and it has too many options to write out every possibility, so there is no other option but to generate SQL to some degree. Since I'm only looking for something to create the actual SQL, not to handle any of the DBI work, transaction management will be up to me.	[reply]
Re^3: What's your favorite SQL generation module? by santonegro (Scribe) on Jun 07, 2006 at 16:07 UTC
Since no-one has mentioned DBIx::Class and its powerful synergy with SQL::Abstract (a good SQL generator on its own), I now mention it. Jesse Vincent's DBIx::SearchBuilder is heavily used. And we can't forget DBIx::Recordset for a change in approach, though I am not happy with what happened to all my bugfixes to it. qw	[reply]
Re^4: What's your favorite SQL generation module? by perrin (Chancellor) on Jun 07, 2006 at 16:50 UTC
Thanks for the suggestions. I looked at DBIx::Class, but it doesn't seem to have encapsulated the SQL generation into a single piece that can be used without the rest of it. SQL::Abstract is okay for WHERE clauses but can't handle anything fancy, particularly joins. DBIx::SearchBuilder bears looking into. At first glance it seems like it wants to the DBI part too though, which would disqualify it. DBIx::Recordset always put me off with its syntax, but I'll give that another look too.	[reply]