[DB-SIG] mxODBC question

Karsten Weinert k.weinert@gmx.net
Wed, 28 Aug 2002 16:17:50 +0200


This is a multi-part message in MIME format.

------=_NextPart_000_0005_01C24EAE.74A23A20
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi,
the following behavior of the Access ODBC driver=20
and/or Python with mxODBC is a problem for me: I open a ODBC connection, =
do some
SQL-statements, for example create a new table an put some data in it.
Then I close the connection. When I open the connection again, the new
table has disappeared!

Here is some code which illustrates this:
#---------------------- snip
import mx.ODBC.Windows

dsn=3D"DataSourceName"
conn=3DNone
curs=3DNone

def test():
  # open connection
  conn=3Dmx.ODBC.Windows.Connect(dsn)
  curs=3Dconn.cursor()

  # create a table
  sqlmsg=3D'CREATE TABLE test13 (nr Integer, val Integer)'
  print(sqlmsg)
  curs.execute(sqlmsg)

  # put some data in the table
  for i in range(10):
    sqlmsg=3D"INSERT INTO test13 VALUES (%d,%d)" % (i, i*i)
    print(sqlmsg)
    curs.execute(sqlmsg)

  # see that there is really data in the table
  curs.execute("SELECT count(nr) FROM test13")
  res=3Dcurs.fetchall()
  print("There are now %s rows in the table." % res[0][0])

  # but what happens when the connection is closed and reopened?
  curs.close()
  conn.close()
  conn=3Dmx.ODBC.Windows.Connect(dsn)
  curs=3Dconn.cursor()
  curs.execute("SELECT count(nr) FROM test13")
  res=3Dcurs.fetchall()
  print("There are now %s rows in the table." % res[0][0])

The first select count() statement returns 10, the second statement =
produces an error messages "table not found".
 =20
I am using python 2.2 on Win98 with the Access-ODBC-driver (Desktop
Driver Pack 3.0). I don't have Access itself installed.

Can you please give me some advice what I did wrong?
Regards,
Karsten


------=_NextPart_000_0005_01C24EAE.74A23A20
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2600.0" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2><PRE>Hi,
the following behavior of the Access ODBC driver=20
and/or <B style=3D"COLOR: black; BACKGROUND-COLOR: #ffff66">Python with =
mxODBC is a problem for me:</B> I open a ODBC connection, do some
SQL-statements, for example create a new table an put some data in it.
Then I close the connection. When I open the connection again, the new
table has disappeared!

Here is some code which illustrates this:
#---------------------- snip
import mx.ODBC.Windows

dsn=3D"DataSourceName"
conn=3DNone
curs=3DNone

def test():
  # open connection
  conn=3Dmx.ODBC.Windows.Connect(dsn)
  curs=3Dconn.cursor()

  # create a table
  sqlmsg=3D'CREATE TABLE test13 (nr Integer, val Integer)'
  print(sqlmsg)
  curs.execute(sqlmsg)

  # put some data in the table
  for i in range(10):
    sqlmsg=3D"INSERT INTO test13 VALUES (%d,%d)" % (i, i*i)
    print(sqlmsg)
    curs.execute(sqlmsg)

  # see that there is really data in the table
  curs.execute("SELECT count(nr) FROM test13")
  res=3Dcurs.fetchall()
  print("There are now %s rows in the table." % res[0][0])

  # but what happens when the connection is closed and reopened?
  curs.close()
  conn.close()
  conn=3Dmx.ODBC.Windows.Connect(dsn)
  curs=3Dconn.cursor()
  curs.execute("SELECT count(nr) FROM test13")
  res=3Dcurs.fetchall()
  print("There are now %s rows in the table." % res[0][0])
</PRE><PRE>The first select count() statement returns 10, the second =
statement produces an error messages "table not found".
 =20
I am using <B style=3D"COLOR: black; BACKGROUND-COLOR: =
#ffff66">python</B> 2.2 on Win98 with the Access-ODBC-driver (Desktop
Driver Pack 3.0). I don't have Access itself installed.

Can you please give me some advice what I did wrong?
Regards,
Karsten</PRE></FONT></DIV></BODY></HTML>

------=_NextPart_000_0005_01C24EAE.74A23A20--