>>I may have a first try with odbc-based sql-pass-thru to start with.
Consider creating a dbc and using parameterized Remote Views for a start. Even if you move on to SQL Passthrough or CA, RVs are SQL-Injection-proof and it's an easy way to come up with SQL that can be reused and edited. In particular- you want to use parameterized SQL rather than concatenated SQL ... IOW
lcMyvar="Allo"
lcSQL="Select * from mytable where myfield=?lcMyvar"
- not -
lcSQL="Select * from mytable where myfield='"+lcMyvar+"'"
since many modern DB (notably SQL Server) will cache the parameterized query for subsequent efficient use.
Also- consider what will happen if somebody can give lcMyvar a value of
Allo' or 1<>0;drop table mytable--Now concatenation gives a SQL String
Select * from mytable where myfield='Allo' or 1<>0;drop table mytable--'
SQLExecute that, and the whole mytable is grabbed before being dropped. Not possible if you use the parameterized version.
"... They ne'er cared for us
yet: suffer us to famish, and their store-houses
crammed with grain; make edicts for usury, to
support usurers; repeal daily any wholesome act
established against the rich, and provide more
piercing statutes daily, to chain up and restrain
the poor. If the wars eat us not up, they will; and
there's all the love they bear us."
-- Shakespeare: Coriolanus, Act 1, scene 1
