Massimo Di Pierro wrote:
Hi Kingsley,
Internally we use the Python ODBC driver for the connection to DB2
and
to MSSQL.
But how have you come to conclude that ODBC only works with DB2 and
MSSQL? What about ODBC Drivers that have been developed to work
consistently across:
Virtuoso, Oracle, SQL Server, DB2, Informix, Ingres, Sybase, Progress,
MySQL, Firebird, other ODBC Drivers, JDBC Drivers etc? Thus, you could
have the option ODBC as opposed to: ODBC for DB2 and MS SQL Sever.
What about the fact that you have ODBC Drivers for Virtuoso that
enable
to make one ODBC connection to all the DBMS engines above, and treat
their tables as though they were part of one DBMS i.e., Virtual DBMS
based Relational Data Virtualization (or Federation).
We use more specific drivers for the other databases, as recommended
by the official documentation.
What official documentation? URL please.
Anyway, from the Python programming point of view they all expose the
same Python API.
The fact is ODBC only unifies the data access API and that is a small
part of we needed since different database still have different
dialects.
Our DAL completely abstracts the database and writes the SQL in the
specific dialect of specific backend.
Are you aware of the fact that via ODBC metadata calls you can
deductively discern the capabilities and SQL dialiects supported by an
DBMS. This is all about the ODBC Drivers you are working with, not the
ODBC spec itself (which is vast and very sophisticated).
For example a limit query in SQLite is done with "SELECT ... FIRST
N"
The same query in MSSQL is done with "SELECT .... TOP N", in Oracle
it
requires two nested selects "SELECT ... FROM (SELECT w_tmp.*, ROWNUM
w_row FROM (SELECT ...) w_tmp WHERE ROWNUM<=N)...;". In the case of
the Google App Engine there is not even SQL so our DAL translates
directly into Google API calls.
Again, you should focus on the functionality you want, and then see if
the underling ODBC Driver can handle what you want, if it can't you
can
drop back to your work arounds.
The same query in the web2py DAL reads like, for example:
db=DAL('postgresql://username:passw...@localhost', pool_size=100)
db.define_table('person',Field('name'))
db.person.insert(name='Ken')
rows = db(db.person.name=='Ken').select(limitby = (0,10))
The first line choose the most appropriate connection driver (which
may be ODBC). The second line tried to define a table "person". If it
does not exist, it is CREATEd. If it exists and has a different
stricture, it is ALTERed. The third line inserts a second. The forth
line is performs the limit query.
As you can see we do not use raw SQL anywhere in our system, although
our system may use SQL to talk to the database. Using raw SQL also
presents the disadvantage that, if one is not very careful in
escaping
variables, one may introduce SQL Injections vulnerabilities. We do
not
have to worry about that with the DAL.
I do understand the abstraction.
I worked with a product called DAL [1] for Mac OS Classic in the early
1990's (and others pre ODBC, which arrived in 1992 as Windows port of
what was then the SAG CLI) :-)
The RDF tagging is done at the DAL level:
db.person.name.rdf = { .... }
Anyway, it is possible there is some feature of ODBC that we have
overlooked and we may be able to take advantage of.
Yes.
You have ODBC itself. Then you have Virtuoso VDBMS (think scalable
high
performance variant of Microsoft Access JET Engine which major
benefits).
Kingsley
Massimo
On Dec 24, 2009, at 6:57 AM, Kingsley Idehen wrote:
Massimo Di Pierro wrote:
Hello everybody,
I am a new member of this list and first of all I wish everybody
Happy
Holidays.
I also take the occasion to introduce the RDF plugin for web2py.
http://web2py.com/semantic
web2py is an Open Source web framework for rapid application
development written in Python and programmable in Python. web2py
runs
almost everywhere and includes everything you need for web
development
in one package including a ssl-enabled web server, a transaction-
safe
relational database, a web-based IDE, a web-based database
administration tool, and a Database Abstraction Layer that writes
SQL
for you and works transparently on SQLite, MySQL, Postgresql,
Oracle,
MSSQL, FireBird, DB2, Informix, Ingres, and on Google App Engine.
Web2py requires no installation: just download, unzip and click to
start it.
You can see an online demo here:
http://web2py.com/demo_admin
The RDF plugin allows to label (tag) database fields and relations
with ontologies and web2py automatically exposes the data in the
database as Linked Data via a RESTful web service. Works with all
database back-ends listed above.
Any comment and feedback will be appreciated.
Any reason why you don't use ODBC for your data access? Your
references
above imply you implemented data access APIs on a DBMS by DBMS
basis.
ODBC is not only superior to all Native DBMS APIs, it is generic
thereby
shrinking you development and maintenance costs.
Kingsley
Massimo
-------------------------------------------------------
Massimo Di Pierro
Associate Professor
School of Computing and Digital Media
DePaul University
243 S Wabash Ave
Chicago IL 60604
+1-312-375-6536 (phone)
+1-312-375-6116 (fax)
--
Regards,
Kingsley Idehen Weblog: http://www.openlinksw.com/blog/
~kidehen
President & CEO
OpenLink Software Web: http://www.openlinksw.com
--
Regards,
Kingsley Idehen Weblog: http://www.openlinksw.com/blog/~kidehen
President & CEO
OpenLink Software Web: http://www.openlinksw.com
