Massimo Di Pierro wrote:
I think we are talking apples and oranges.
According to Microsoft, who proposed ODBC,
http://msdn.microsoft.com/en-us/library/aa198030%28SQL.80%29.aspx
"The driver passes SQL statements to SQL Server and returns the
results of the statements to the application."
No, before an ODBC app talks to an ODBC Driver, it should use ODBC
metadata calls to understand the capabilities of the underlying
driver.
That's how ODBC applications are supposed to be written.
ODBC is not a simple CLI (as per what you get from DBMS vendors) for
executing SQL statements, far from it.
The purpose of the web2py DAL is that of generating the SQL
statements
(in the case of RDBS) and it does not care whether the connection is
established via ODBC or not.
Its an abstraction that sits atop data access APIs.
Putting my question differently, what happens when I use ODBC
against a
DBMS that isn't DB2 or MS SQL Server re. this interface? Basically,
can
I substitute either via the ODBC hook ?
We do not use ODBC on Oracle, for example, but we use cx_Oracle
instead because, according to this is a quote from the Oracle web
page:
http://www.oracle.com/technology/pub/articles/rubio-python-turbogears.html
"cx_Oracle serves as the core and lowest-level API for bridging a
Python environment to an Oracle database. cx_Oracle is to Python what
an Oracle JDBC driver is to Java, enabling an application to execute
raw SQL queries and manipulate database cursors with a fine level of
detail."
Yes, what do you expect Oracle to say? Something like: we have an
interface that lacks the deductive capabilities of the ODBC interface
that ultimately makes DBMS swapping nice and easy?
We could use pyODBC on Oracle as we do with with DB2 but the
installation process would be more cumbersome.
Depends on which ODBC drivers you use. In fact, you can install our
Drives using an installer on any platform we support (and thats a vast
array of platforms).
Post installation we even give you basic connection exercisers or a
platform port of the ODBC Driver Manager that verifies DSNs post
creation.
ODBC does a lot but in the end it just gives a connection and a
cursor
object. It does not give us complete database abstraction which is
what the web2py DAL does.
Of course it gives you complete DBMS abstraction. What you really mean
is this: the interface you are using doesn't really exploit ODBC to
the
max. I believe this ODBC layer comes from someone else, right?
One of the most important issue from our prospective is that the
web2py DAL works with Non Relational Databases too. Currently only
Google Big Table on the Google App Engine but we are working on
MongoDB and CouchDB support. ODBC does not work with non-relational
databases.
Virtuoso provides an ODBC interface that actually lets you work with
XML, RDF, Web Services and other data representations and model
combinations. Jena, Redland, and Sesame abstractions all work with
Virtuoso via its JDBC or ODBC drivers. Again, ODBC capabilities are
very
dependent on who's ODBC implementation (app or driver) you are working
with.
In our world you can join a relation table and Web Addressable
Resource
via SPARQL pattern in Procedure Views (Table Valued Functions in
Oracle
parlance) executed inside the Virtuoso SQL (i.e., SPASQL [1]).
Naturally, you can also to the opposite i.e., surface SQL
functionality
in SPARQL[2] etc..
Running on the Google App Engine cloud has some limitations. We can
only upload pure Python or pure Java programs, no C/C++ modules or
pre-compiled modules. We can only use the Google API or something
that
they call GQL. There is no SQL on GAE because it is not a relational
database. Hence, ODBC is not option. Building our system on ODBC
simply would not cut it. Yet the Google App Engine is the single best
thing that happened to the world of web development in some time
(practically free, unlimited scalability, zero maintenance, no
messing
up with server or database administration). We use ODBC when we can
but we are interested in pushing our technology in places where ODBC
is not an option.
Least but not last I want to make the case that everybody is doing
web
apps with database connections these days. This is now off-the-shelf
technology and people should not pay for it. Most ODBC drivers are
commercial products or require a commercial license for enterprise
use. Our Database Abstraction Layer is released under the GPL2
license, only requires third party GPL or BSD drivers, and has been
tested by more than 1500 registered users and 50 developers.
I am not questioning your vision. And when it comes to data access, I
think REST based Open Data Access (Linked Data) is something I
advocate
strongly, as a superior solution to ODBC, JDBC, ADO.NET, OLE-DB, or
XMLA when dealing disparately shaped data sources.
My fundamental point to you is this: ODBC is a DBMS independent API,
so
you shouldn't speak in DBMS specific terms re. ODBC. You have Native
CLIs from DBMS vendors that occupy the Database Specific Data Access
API
box :-)
ODBC is about making the backend DBMS engines interchangeable, one
size
doesn't fit all re. ODBC Drivers or ODBC compliant applications. I
should be able to simply slot in our Virtuoso ODBC Driver, or our ODBC
Drivers for all the other DBMS engines we support without issue via
your
ODBC hook. Same should apply to any other ODBC Driver vendor.
To conclude; when I have a moment I'll have your ODBC layer tested
against our ODBC Drivers, if there are issues, we'll make a simply
report highlight ODBC APIs issues etc. (for you or whoever is behind
the
ODBC layer. Worst case we can fix it ourselves as we already done many
other interfaces).
Links:
1. http://docs.openlinksw.com/virtuoso/rdfsparql.html#rdfsparqlinline
2. http://docs.openlinksw.com/virtuoso/rdfsparql.html#rdfsqlfromsparql
Kingsley
Massimo
On Dec 24, 2009, at 9:30 AM, Kingsley Idehen wrote:
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
--
Regards,
Kingsley Idehen Weblog: http://www.openlinksw.com/blog/~kidehen
President & CEO
OpenLink Software Web: http://www.openlinksw.com