[DB-SIG] Examples on how to use mxODBC?

[Vernon Cole]

Everyone:
Sorry about that last message!
MS Outlook burped and sent it before I could edit anything!
I also work in a shop where Microsoft predominates. I dislike the mailer,
along with many other things. I also perceive python as a subtle way of
loosening MS's grip on our shop.

Joe:
	If you are trying to go open source, then mxODBC may not be your
best choice, since it is proprietary. I am only a little bit ahead of you on
that steep learning curve, but I have concluded that mxODBC would be too
expen$ive for my organization to approve. 
	I have successfully used two different database API's on windows
2000 workstation to access SQL2000 databases. The first is bundeled with the
Pythonwin package (http://starship.python.net) and uses ODBC.
vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
import dbi, odbc
"""This program will list the JackPot/Fill locations for all
    Booths and Windows which exist in the database."""

#the connection string is the DSN/username/password
ConnS2 = r"Oasis Data/cdsaccess/xxxxxxxxx"

Cdb = odbc.odbc(ConnS2)
c2 = Cdb.cursor()
s = "select description from GEN_Casino"
c2.execute(s)
Casino_Name = c2.fetchone()[0]
print
print 'Unassigned JF_workstations in Casino=', Casino_Name
s = """select Booth_id, window from jf_workstation where    WorkStation_Id =
-1 order by booth_id, window"""
c2.execute(s)
for rec in c2.fetchall():
    print(rec)
c2.close()
Cdb.close()
try:
    s = dir(pywin)
except NameError:
    s = raw_input('Hit <Enter> to close window...')
print
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   I needed a way to access sevaral databases, all with the
same name, on different servers, so ODBC was not ideal. ADO seemed to be the
answer. I found adodbapi on sourceforge.net and tried that. The results were
good.
   Here is a sample which opens a table using "integrated security." Each
row in that table contains the name of another SQL server, each of with is
opened using "SQL security". A stored proceedure is called, and its output
printed out. 
(caution -- Email will wrap long lines below. Believe the tab level to
detect such.)
vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv
import adodbapi

_computername="NIXON" #or name of computer with SQL Server
_databasename="WcMine" #or something else
connStrSQLServer = r"Initial Catalog=%s; Data Source=%s;
Provider=SQLOLEDB.1; Integrated Security=SSPI" %(_databasename,
_computername)
print connStrSQLServer
try:
    s = adodbapi.connect(connStrSQLServer)
    cur = s.cursor()
    cur.execute('select Casino_Code, Description, Server_Name from
GEN_Casino')
    for rec in cur.fetchall():        
        Casino_Code = str(rec[0])
        Description = str(rec[1])
        ServerName = str(rec[2])
        print Casino_Code, ServerName, Description
                
        ConnS2 = r"Initial Catalog=WinOasis; Data Source=%s;
Provider=SQLOLEDB.1; User ID=cdsaccess; Password=xxx" %(ServerName)
        Cdb = adodbapi.connect(ConnS2)
        c2 = Cdb.cursor()

        app_id = 1
        curUser = 0
        LockKey = -1
        TaxForm = 2
        ret = c2.callproc('CDS_W2G_GET_BYW2G_ID',(0
,app_id,curUser,LockKey,TaxForm))
        print 'ret=', ret
        for rec2 in c2.fetchall():
             print 'rec2=', rec2
                
except NameError,e:
        print 'error ', e, 'undefined'
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Good luck in your efforts.
--------
Vernon


-----Original Message-----
From: jgoldtest [mailto:joe at bar-s.com]
Sent: Tuesday, October 21, 2003 11:36 PM
To: db-sig at python.org
Subject: [DB-SIG] Examples on how to use mxODBC?

Hi all,
...
 I work at a Microsoft  shop but I'm trying go get them away from .Net and
into open source.
...