Am Freitag, 12. August 2005 16:47 schrieb Damon Courtney:
> >Just some other remarks on different syntax at least between Mysql and
> > Oracle: you have to think about the different syntax for outer joins for
> > example: Mysql:
> >SELECT ...
> >LEFT JOIN tab1 ON tab1.field1 = tab2.field2
> >
> >Oracle:
> >SELECT ....
> >FROM tab1, tab2
> >WHERE tab1.field1(+) = tab2.field2
> >
> >I think it is a hard job to make that DB independent.
> >At the moment my solution is:
> >
> >switch $::_DB_TYPE {
> >mysql {
> > set outer_tabs "LEFT JOIN tab1 ON tab1.field1 = tab2.field2"
> > set outer_where ""
> > }
> >oracle {
> > set outer_tabs "tab1, tab2"
> > set outer_where "AND tab1.field1(+) = tab2.field2"
> > }
> >
> >
> >}
> >
> >set sel_str "SELECT ... FROM .... $outer_tabs WHERE 1 = 1 $outer_where"
>
> I wrote a Query class for DIO a while back (also not committed) that
> would help with this. Basically, the way I ended up doing it was to
> create different .query files for use in my application. So, I had a
> mysql.query file and a postgres.query file that defined all of my
> queries using the new Query class. Like so:
>
> mysql.query:
>
> Query FetchUsers -query "select users from user_table"
> Query FetchPermissions -query "select permission from permission_table"
> etc...
>
> And then, with a similar file for Postgres. By also creating a
> common.query file, I could put things in whose syntax was common to both
> databases. Then, with the Query class and some new additions to DIO,
> you can just do:
>
> set result [DIO exec FetchUsers]
that sounds OK.
The problem in my case was I had "huge" selects which were sometimes generated
dynamically from a Tcl proc dependent on selected output fields and
conditions entered from a form UI from the enduser with about 80 to 100 lines
(reports for marketing people) where 90% of the select statement where
identically and only one or 2 outer joins did not fit into the common part,
that's why I choose the above solution (sometimes the only "static" part were
the outer joins above).
Arnulf
>
> It was never finished though. The classes are done, but the exec
> "magic" was never quite completed. It was mostly an experiment anyway.
> I'll have to commit my work here soon and let others play around with
> it. Maybe someone can pick up the torch and finish it.
>
> Damon
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
