Postgres support

[Paul Boddie]

Steve Williams <stevewilliams at wwc.com> wrote in message news:<3B4B458C.A0F96E5D at wwc.com>...
> Paul Boddie wrote:
> 
> > How do you manage to do different types of joins?  Do any of the systems
> > you mention use the explicit join syntax, for example?
> 
> My work lately has been in DB2 and I use explicit joins exclusively--they
> provide the level of documentation and control I want and need.  It's been a
> long time since I've used anything else.  I also use Interbase for a
> BiTemporal database I'm developing and, again, I use explicit joins.  When I
> worked in SQL-Server, I used a sad mixture of implicit and explicit joins--I
> hadn't seen the light yet.  I can't remember specifically about my work with
> the others.

I suspect that if you used Sybase ASE (and not ASA), you would have
needed to use implicit joins. I suppose it would be possible to
provide some kind of statement syntax independence within a database
framework, but the amount of work required probably isn't worth it.
It's far better off providing approaches which manage the differences
between systems in an understandable and easy-to-use way.

> > What about the statement parameter (bind variable) syntax?

I've corrected your quoting here...
  
> As far as binding, Python does a nice job for me:
>
> cursorODS = connectionODS.cursor()
> intInvoiceNumber = int(str(parmInvoiceNumber))
> parmList = (intInvoiceNumber,)
> cursorODS.execute("select " \
> + "InvoiceNumber,LineNumber,PartNumber,Description,Quantity,UnitOfMeasure,"
> + "Price,ExtendedSale,Cost,ExtendedCost,GP,GPPercent " \
> + "from ODS.OrderLineDetail " \
> + "where InvoiceNumber = ?",parmList)
> resultSet = cursorODS.fetchall()
> cursorODS.close()
> return resultSet
>
> This works for DB2 and Interbase via ODBC and mxODBC.

OK, so you're using paramstyle as 'qmark', as I think DB-API 2 would
say. I think the only reasonable abstraction which could be provided
over the differing parameter syntaxes is that of a dictionary, with
keywords in use within the statement itself. Something like paramstyle
as 'pyformat' or, in order to distinguish it from straightforward
string substitution (a bad thing), paramstyle as 'named'.

[Something about standards]

> > Which standard are we talking about now? ADO and COM aren't exactly
> > there on most flavours of UNIX, if at all. Of course, there's always
> > ODBC. ;-)
> 
> I know very little about ADO or COM.  Although mxODBC is a marvellous
> package, I'm always suspicious of the actual ODBC drivers.  I would rather
> directly interface with my target database.  But I don't have compile
> capability on some of my platforms (AIX or anything not WinTel, actually),
> so my contribution to this thread was a fantasy about pre-compiled drivers
> for all the databases on all the platforms.

I was surprised to see Sybase ASA directly supporting ODBC - going
against what Mr Wilson was asserting about its death - but, in
general, I suppose that the greater the number of layers between one's
application and the database system, the greater the risk for
something to fail in obscure ways. In the case of Sybase ASA, I found
things to be more reliable if I linked mxODBC directly to the library
provided, rather than through the iODBC driver manager.

> In general, I don't see much emphasis on relational databases in the Python
> jungle.

I think that it only appears that way because of the low traffic on
the DB-SIG list and because there isn't any other focal point for
database-related activity.

Paul