[DB-SIG] MS SQL Server and odbc getting 'None'

[Carey David SSgt 109CF/SCBJ]

 Ok, I believe I see. If I'm doing multiple updates/inserts inside a loop,
is it better to commit inside the loop with each execute, or outside the
loop and commit just once? I would assume that committing outside the loop
would be the best.

I hope I made that clear.

Thanks
Dave

-----Original Message-----
From: Vernon Cole [mailto:wnvcole at peppermillcas.com] 
Sent: Friday, August 13, 2004 12:02 PM
To: 'Carey David SSgt 109CF/SCBJ'
Cc: 'db-sig at python.org'
Subject: RE: [DB-SIG] MS SQL Server and odbc getting 'None'

Dave:
  The problem you may be having is that you must "commit" the transaction(s)
using the connection object after you perform them using the cursor object.
Look just above the "except ValueError" in the enclosed script. 

I was looking for a snippet to send you, but this program is not much longer
and is in production at my site, so I know it is complete and works.  The
following script is stored on a public share on the SQL server. It allows a
user to interactively select a row from a table, check its value, and
optionally modify a field.
vvvvvvvvvvvvvvvvvv cut here vvvvvvvvvvvvvvvvvvvvvvvvvv """ pbOK800.py --
program to set/clear "OK800" flags in the Casino phonebook """
import sys
import adodbapi             #use the ADO data access toolkit

_OK800 = False
_c = None

def get_ext(e):
    global _OK800
    # ##########################
    # SQL command
    SQLcmd = """
    select extension, Casino, Department, PositionLine, OK800
     from tbldepartment
    where extension = '%s'
    """
    _c.execute(SQLcmd % e)
    rec = _c.fetchone()
    if not rec:
        print 'Extension',e, 'not found in PhoneBook'
        return None
    else:
        ext = rec[0]
        if rec[4].upper() == 'Y':
            _OK800 = True
        else:
            _OK800 = False
        print 'Extension=', ext, rec[1], rec[2], rec[3]
        print '    OK800=', _OK800
        return ext
    
def bool_input(prompt):
    while True:
        s = raw_input(prompt)
        if len(s) > 0:
            c = str()
            c = s[0].upper()
            
            if c == 'Y' \
            or c == '1' \
            or c == 'T' \
            or c == 'S':
                return True
            elif c=='N' \
            or c == '0' \
            or c == 'F':
                return False
            else:
                print 'Incorrect input. Try again...'

def OK():
    global _OK800
    global _c
    _computername="FRANKLIN" #name of computer with Data Warehouse SQL
Server
    _databasename="PhoneBook" #name of database
    connStrSQLServer = r"Initial Catalog=%s; Data Source=%s;
Provider=SQLOLEDB.1; Integrated Security=SSPI" %(_databasename,
_computername)
    # print connStrSQLServer
    print 'Connecting to:',_computername,_databasename

    s = adodbapi.connect(connStrSQLServer) #connect to datamine server "s"
    _c = s.cursor()   # create a cursor to send commands to "s"

    print """
    You may enter 1, Y, T or S for YES,
    enter 0, N or F for NO.

    """

    keep_trying = True
    while keep_trying:    
        try:
            ex = raw_input('Examine which Extension number? :')
            ext = get_ext(ex)
            if (ext):
                    go = bool_input('Change OK800 for this extension?
[Y/N]:')
                    if go:
                        if _OK800:
                            NewValue = 'N'
                        else:
                            NewValue = 'Y'
                            
                        u = "Update tblDepartment "\
                            "set OK800 = '%s' where extension = '%s'" % \
                            (NewValue, ext)
                        _c.execute(u)
                        s.commit()
                        check = get_ext(ext)
        except ValueError:
            print 'Incorrect input. Try again.'

        keep_trying = bool_input('Continue? [Y/N]:')

    _c.close()
    s.close()

if __name__ == '__main__':
    OK()
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ end of clip ^^^^^^^^^^^^^^^^^^^^^^^^ This is
called by executing the following which is actually stored as an icon on the
users desktop.
vvvvvvvvvvvvvvvvvvvvvvvvvvvv begin call script vvvvvvvvvvvvvvvvvvvvvvvv p =
"//franklin/PhoneBook/Utilities"
import sys
if p not in sys.path:
    sys.path.append(p)
from PbOK800 import OK
OK()
^^^^^^^^^^^^^^^^^^^^^^^^^^^^ end call script ^^^^^^^^^^^^^^^^^^^^^^^^^ Good
luck,
----
Vernon

-----Original Message-----
From: Carey David SSgt 109CF/SCBJ [mailto:David.Carey at nyscot.ang.af.mil]
Sent: Friday, August 13, 2004 8:35 AM
To: 'Vernon Cole'
Cc: 'db-sig at python.org'
Subject: RE: [DB-SIG] MS SQL Server and odbc getting 'None'


Thanks for the suggestion! I will take a look at it. mxODBC worked when
pulling the information, but when I tried to update or insert records, it
would act like it worked. Then when I went to the database nothing changed.

So I'm using odbc to push information and mxODBC to pull it with 2 different
connection strings. 

Maybe this will help me roll it in one.

Thanks for the info!
Dave