database access !? ODBC, mxODBC!?

[Aaron Fransen]

I use the native ODBC interface (though it's only compliant with the 1.0
API) with *almost* no issues.

In my usage, I've written web and console based applications that use both
MySQL and an IBM AS/400 from a Windows 2000 platform.

For Select, Update, Insert and other general statements it works
beautifully. The only issue is output parameters on stored procedures, ie:
{CALL MYPROG(?,?,?)} won't return parameters, though it will run the
program. Strange.

Here's an example:

Let's say you've got an ODBC connection configured in Windows to your MySQL
server (*must* be a SYSTEM DSN, NOT as a User DSN) called "SQLSERV". In
Python your code might be:

    import dbi,odbc
    myvar = "Select * from MyTable where Name='Joe' "
    dbc = odbc.odbc("SQLSERV","myusername","mypassword")
    cursor = dbc.cursor()
    cursor.execute(myvar)
    result = cursor.fetchall()

Now, the fetchall returns a list, but without named headers. Let's say your
table had the column names "Name,Address,City". A piece of Python code that
would let you use the header names is:

    x = 0
    col = {}
    for column in cursor.description:
        col[column] = x
        x = x + 1

Then you cycle through the records (remember that "result" here returns a
list) with:

    for record in result:
        namevar = record[col['Name']]
        addressvar = record[col['Address']]
        cityvar = record[col['City']]
        print "This record is %s, %s, %s" % (namevar,addressvar,cityvar)
    cursor.close()
    dbc.close()

If you *didn't* use the "cursor.description" method shown above, you could
reference each of the fields with:
        namevar = record[0]
        addressvar = record[1]
        cityvar = record[2]

But that gets kind of messy and you have to keep track of which column is
which. Much easier the first way.

Bottom line is it should work with just about any ODBC compliant app without
having to go to mxODBC unless you really need some of the features mxODBC
offers.

"Kenneth Tsang" <smt at pacific.net.hk> wrote in message
news:9nju88$pae$1 at hfc.pacific.net.hk...
> hi, in my applicaitons I need database access to different backend like
> mySQL, Access and SQLServer. Please advice which db module I should use!?
> I;ve seen mxODBC, however, it seems that it's a commercial one. Thx.
>
> cheers, Kenneth
> --
>
> --
> Kenneth Tsang
> email: smt at pacific.net.hk tel: +852 9468 4772
>
>