Re: Standard DBI Proposal

On Tue, 30 Oct 2007, Kevin Kenny wrote:

tunity5@xxxxxxxxx wrote:
I have followed this thread with a slight interest. As with the

....

I don't quite understand what you're asking for here.

If you're asserting that there is no ODBC on non-Windows platforms,
unixODBC (http://www.unixodbc.org/) appears to be both popular and
well-maintained. There was a release shipped just a couple of weeks
ago. I can't comment on how well recent versions work. I haven't
tried it in several years.

If you're asserting that Tcl entirely lacks an interface to ODBC
on non-Windows platforms, I know that tclodbc has been built on
Unix hosts. Again, I haven't tried it there in quite some time.

I will describe my specific project to bring in some discussion aspect
which is NOT primarily interested in "database objects" or inventing a
new SQL language/syntax (but by the other way I did it). But what I
needed was an abstraction of the data model, "externalization" of the db
logics (server side sql functions with the different server languages)
and many "formatting" and some "introspection" functions.

Currently I'm working on a project which supports the 4 db-backends
PostgreSQL, MySQL, Oracle and MS-SQL. It consists of a web-part
programmed in PHP and many online-modules, all written in TCL.
I'm using the native interfaces pgtcl, mysqltcl, oratcl and tclodbc
(only for Microsoft SQL Server). The software has been tested on
Windows, several Linux distributions and partly on AIX.

As there is a dedicated functionality, it was obvious to implement the
abstraction layer in one TCL and one PHP file, i.e. no line of code
has to be changed by changing the database or - maybe - by implementing
an additional backend.
Typical datebase growth is ca. 20 - 30 million records per year, many
grouping functions are used, like:
give me the daily max-values of a specific month, the month-max values
of a year, all values within a day, all capacity changes of a year...

This was the main reason, why most functionality has been implented as
database procedures which are also more easy to abstract. Also this is
the reason, why additional db-layers (like ODBC) are not relevant as
only the "clean utilization data" must be transferred to the client and
only minimal logics (and data) is necessary on the client side.
The data is used to create graphics, charts and lists/tables which
become displayed on a web sides or on text-oriented and graphical TCL
clients.

The interface has been implemented as some sort of "communication"
meta-language, so no SQL-knowledge on client-programmers side is
necessary - example:
GET DATA PATH[,FROM,UNTIL[,RES]]
GET ... keyword for data retrieval
DATA .. sql data select (also other "data types" are used like INFO)
PATH .. tree like "addressing" server/type/service/resource/...
of the data source
FROM/UNTIL .. time range to extract
RES ... evaluation resolution like, DAY, WEEK, SECOND...

Example:
GET DATA "licsrv/FlexLM/27000","2007-05-02 00:00:00",
"2007-05-03 00:00:00","HOUR"
... retrieves all hour-max values withing one day for all licences of
a the license service 27000.

Typical interface functions are:
list of tables ("user objects")
table description
insert, update, delete record (requires unique ID)
connect/disconnect
sql function calls
etc.

The mainly used objective is to "hide" database specific handling, like
the different syntaxes of SQL function/procedure calls, the absolutely
different date handlings and the methods to retrieve database structures
like table descriptions.

Finaly I show the list of all TCL funtions, which MUST be implemented
for each database. A '+' in front means a namespace-exported function,
a '-' means "internally used only" - some of them are very application-
specific:
# -proc Set_DB_Error {code emsg}
# +proc Get_DB_Error {args}
# -proc Connect {db host user pass}
# -proc Disconnect {}
# +proc OpenConnection {db host user pass}
# +proc CloseConnection {}
# +proc OpenDB {db host user pass}
# +proc CloseDB {}
# -proc ColFormat {dsc}
# +proc Describe {table args}
# -proc DateFormat {date}
# +proc Feed_Snapshot {sqlh query_agent time_stamp value provider
# restype service resource capacity}
# -proc SQL_Select {sqlh stmt head}
# +proc SQL_Execute {sqlh stmt}
# +proc PLSQL_Execute {sqlh stmt}
# +proc Interval_Usage {sqlh provider restype service resource
from until interval}
# +proc Select {sqlh table what where order head args}
# +proc UserObjects {sqlh obj user}
# +proc SetResnameFilter {sqlh rlist}
# +proc EmptyResnameFilter {sqlh}

I hope this may lead to other sights for implementing DBI's which have
not been discussed so far.
--
Gerhard Reithofer
Tech-EDV Support Forum - http://support.tech-edv.co.at
.

Relevant Pages

  • strangest hanging problem with re-attaching functions...
    ... back-end database. ... So the ribbon button option calls a macro ... I put up a browse dialogue in the StartProgram ... and put an onAction call on the button (see proc below). ...
    (comp.databases.ms-access)
  • RE: Probs executing a Stored Proc
    ... If the owner of the proc is not the current user. ... If the proc is not in the current database, you also need to add the ... > Dim QryCommand As ADODB.Command ... > Dim QryParameter As ADODB.Parameter ...
    (microsoft.public.vb.database.ado)
  • Re: How to secure a database ...?
    ... important for security, support, IP reasons etc, that the users are not able to access my databse schema. ... What do I have to do to ensure that users will not be able to access my database? ... On a middle tier, you can stored ... I don't want end users looking "inside" the database (i.e. to see table names, stored proc names, and be able to analyse stored proc logic etc). ...
    (comp.databases.ms-sqlserver)
  • Re: More newbie questions on mass delete
    ... which resides in the sybsystemprocs database, may be executed from within *ANY* database in the dataserver. ... Any references in said proc to system tables will access the system tables in the database where the proc is executed. ... By prefacing the stored proc invocation with the name of the database you are, in essence, telling the dataserver to do a 'use ' followed by executing said proc ... ...
    (comp.databases.sybase)
  • Re: [ANN] EasyTable 6.10 with new SQL functions
    ... I am impressed to see you adding so many features in an incremental ... SQL functions description is modified in Developer's Guide. ... EasyTable is BDE alternative database system with SQL support. ...
    (borland.public.delphi.thirdpartytools.general)