[Zope] RE: ZODBC problem (and ZmxODBC)
Capesius, Alan
CapesiusA@Sysmex.com
Thu, 8 Mar 2001 17:23:18 -0600
>From the Docs for ZmxODBCDA:
Added experimental support for Unicode. This needs Python 2.0 (it should
work with Python 1.6 too). To enable the Unicode support, compile the
subpackages with -DWANT_UNICODE_SUPPORT.
I don't have time to look into the 2.0 conversion and recompile of ZmxODBC
so I'll live with the thing the way it is. My queries are all functioning at
this point.
>>>-----Original Message-----
>>>From: Jzlio Dinis Silva [mailto:juliodinis@hotmail.com]
>>>Sent: Thursday, March 08, 2001 5:03 PM
>>>To: CapesiusA@Sysmex.com
>>>Cc: zope@zope.org
>>>Subject: RE: [Zope] RE: ZODBC problem (and ZmxODBC)
>>>
>>>
>>>Hi,
>>>
>>>And there is also a Internal Error that happen when I do a
>>>select to tables with a text datatype. Wether the text datatype
>>>or maybe some internacional chars I have in this text fields, one
>>>of this are also not supported by zmxodb.
>>>
>>>cia,
>>>Julio Dinis Silva
>>>
>>>
>>>>From: "Capesius, Alan" <CapesiusA@Sysmex.com>
>>>>To: Jzlio Dinis Silva <juliodinis@hotmail.com>
>>>>CC: zope@zope.org, mal@lemburg.com
>>>>Subject: RE: [Zope] RE: ZODBC problem (and ZmxODBC)
>>>>Date: Thu, 8 Mar 2001 15:35:59 -0600
>>>>
>>>>OOPS.
>>>>
>>>>I do get the error. I apologize, I was looking at the wrong
>>>ODBC_ link.
>>>>ZmxODBC browser is failing as well for me.
>>>>
>>>>The problem is that ZmxODBC does not support the nvarchar data type
>>>>(unicode
>>>>varchar) in use in the Information_schema.columns table.
>>>This could render
>>>>ZmxODBCDA useless under certain conditions.
>>>>
>>>>
>>>>
>>>>An interesting aside:
>>>>I use three queries to pull browse info from the tables
>>>list. This is much
>>>>faster than using the browser feature. Set the data source
>>>to your ODBC
>>>>source.
>>>>
>>>>
>>>>Query 1 A_Table_Scan(search for all fieldnames containing
>>>searchstring
>>>> %__% wildcards OK):
>>>>============================================================
>>>==========
>>>>Arguments: search_sting
>>>>SQL String:
>>>>SELECT table_catalog,table_name,column_name,data_type FROM
>>>>INFORMATION_SCHEMA.COLUMNS
>>>>where column_name like <dtml-sqlvar search_string type=string>
>>>>order by ordinal_position
>>>>
>>>>Query 2 A_Table_Structure (Show structure of selected table):
>>>>============================================================
>>>==========
>>>>Arguments: table
>>>>SQL String:
>>>>SELECT table_catalog,table_name,column_name,data_type FROM
>>>>INFORMATION_SCHEMA.COLUMNS
>>>>where table_name = <dtml-sqlvar table type=nb>
>>>>order by ordinal_position
>>>>
>>>>Query 3 A_Tables (List tables):
>>>>============================================================
>>>==========
>>>>Arguments: NONE
>>>>SQL String:
>>>>SELECT * FROM INFORMATION_SCHEMA.TABLES
>>>>
>>>>
>>>>
>>>> >>>-----Original Message-----
>>>> >>>From: Jzlio Dinis Silva [mailto:juliodinis@hotmail.com]
>>>> >>>Sent: Thursday, March 08, 2001 2:46 PM
>>>> >>>To: CapesiusA@Sysmex.com
>>>> >>>Cc: zope@zope.org
>>>> >>>Subject: RE: [Zope] RE: ZODBC problem (and ZmxODBC)
>>>> >>>
>>>> >>>
>>>> >>>Hi
>>>> >>>
>>>> >>>I have the same versions but I still get the InterfaceError.
>>>> >>>
>>>> >>>I am able to create a table, insert some records and
>>>> >>>query the table, but commands like sp_who or sp_tables, or
>>>> >>>if I try to
>>>> >>>view the "browse" tab I get an InterfaceError.
>>>> >>>
>>>> >>>Please confirm if the only changes you made was related
>>>to moving
>>>> >>>the mx directory to lib/python, and the rest was just like the
>>>> >>>installations instructions said.
>>>> >>>
>>>> >>>Do you have the sqlserver7 on the same machine you have zope?
>>>> >>>
>>>> >>>Thanx,
>>>> >>>Julio Dinis Silva
>>>> >>>
>>>> >>>
>>>> >>>>From: "Capesius, Alan" <CapesiusA@Sysmex.com>
>>>> >>>>To: Jzlio Dinis Silva <juliodinis@hotmail.com>
>>>> >>>>Subject: RE: [Zope] RE: ZODBC problem (and ZmxODBC)
>>>> >>>>Date: Thu, 8 Mar 2001 09:26:58 -0600
>>>> >>>>
>>>> >>>>No problems with the browse tab here.
>>>> >>>>I am running MDAC 2.5 drivers.
>>>> >>>>Thats 3.70.08.20 for SQL Server.
>>>> >>>>SQL is SQL7 SP2
>>>> >>>>And the ZmxODBC is 0.0.2 release.
>>>> >>>>
>>>> >>>>
>>>> >>>>
>>>> >>>> >>>-----Original Message-----
>>>> >>>> >>>From: Jzlio Dinis Silva [mailto:juliodinis@hotmail.com]
>>>> >>>> >>>Sent: Thursday, March 08, 2001 6:16 AM
>>>> >>>> >>>To: CapesiusA@Sysmex.com
>>>> >>>> >>>Cc: zope@zope.org
>>>> >>>> >>>Subject: Re: [Zope] RE: ZODBC problem (and ZmxODBC)
>>>> >>>> >>>
>>>> >>>> >>>
>>>> >>>> >>>Hi,
>>>> >>>> >>>
>>>> >>>> >>>I had some problems in the past using zmxodbcda and sql7.
>>>> >>>> >>>
>>>> >>>> >>>What version of sqlserver drivers are you using?
>>>> >>>> >>>
>>>> >>>> >>>If you click on the tab "browse" of your zmxodbda
>>>connection
>>>> >>>> >>>do you get a
>>>> >>>> >>>InterfaceError?
>>>> >>>> >>>
>>>> >>>> >>>thanx,
>>>> >>>> >>>Julio Dinis Silva
>>>> >>>> >>>
>>>> >>>> >>>
>>>> >>>> >>>>From: "Capesius, Alan" <CapesiusA@Sysmex.com>
>>>> >>>> >>>>To: bryanbz@2s2i.com
>>>> >>>> >>>>CC: zope@zope.org
>>>> >>>> >>>>Subject: [Zope] RE: ZODBC problem (and ZmxODBC)
>>>> >>>> >>>>Date: Wed, 7 Mar 2001 17:49:09 -0600
>>>> >>>> >>>>
>>>> >>>> >>>>Excellent. The ZmxODBC driver has resolved the
>>>problem for me.
>>>> >>>> >>>>Performance is now unaffected by the slow queries.
>>>> >>>> >>>>
>>>> >>>> >>>>I'm using Access and SQL7 dbs, not sure if Access ODBC is
>>>> >>>> >>>multi-threaded,
>>>> >>>> >>>>so
>>>> >>>> >>>>I may need to run a mix of drivers, but the test server
>>>> >>>> >>>hasn't crashed yet.
>>>> >>>> >>>>
>>>> >>>> >>>>A note on the ZmxODBC install, the docs indicate that you
>>>> >>>> >>>should copy the
>>>> >>>> >>>>mx
>>>> >>>> >>>>folder to the Shared folder. This will not work
>>>(and is the
>>>> >>>> >>>default). The
>>>> >>>> >>>>coding requires that you move the mx folder up one level
>>>> >>>> >>>after extraction
>>>> >>>> >>>>it
>>>> >>>> >>>>into the Zope tree. It should be at ZopePath\lib\python\mx
>>>> >>>> >>>>
>>>> >>>> >>>>Thanks Bryan,
>>>> >>>> >>>>
>>>> >>>> >>>>Alan
>>>> >>>> >>>>
>>>> >>>> >>>>
>>>> >>>> >>>> >>>-----Original Message-----
>>>> >>>> >>>> >>>From: Bryan Baszczewski [mailto:bryanbz@2s2i.com]
>>>> >>>> >>>> >>>Sent: Wednesday, March 07, 2001 1:08 PM
>>>> >>>> >>>> >>>To: 'Capesius, Alan'; zope@zope.org
>>>> >>>> >>>> >>>Cc: zope-dev@zope.org
>>>> >>>> >>>> >>>Subject: RE: [Zope-dev] RE: ZODBC problem
>>>> >>>> >>>> >>>
>>>> >>>> >>>> >>>
>>>> >>>> >>>> >>>Alan, I am having a similar lock-out problem while
>>>> >>>> >>>> >>>performing a complex
>>>> >>>> >>>> >>>query on SQL Server 2K. All other hits cannot
>>>> >>>connect until
>>>> >>>> >>>> >>>the query
>>>> >>>> >>>> >>>returns the results. My research has so far
>>>taken me here:
>>>> >>>> >>>> >>>
>>>> >>>> >>>> >>>http://www.zope.org/Members/petrilli/DARoadmap
>>>> >>>> >>>> >>>Which is telling me I am probably using at most a
>>>> >>>Level-2 DA
>>>> >>>> >>>> >>>with my ZODBC.
>>>> >>>> >>>> >>>
>>>> >>>> >>>> >>>And then here:
>>>> >>>> >>>> >>>
>>>> >>>> >>>>
>>>> >>>>
>>>> >>>>>>>>>http://www.zope.org/Members/djay/ZmxODBC/ZmxODBC_0_0_2.
>>>> >>>tgz/README
>>>> >>>> >>>> >>>Which is a Level-3 mxZODBC DA (multithreaded).
>>>> >>>> >>>> >>>
>>>> >>>> >>>> >>>If anyone sees a problem with what I am about
>>>to do, let me
>>>> >>>> >>>> >>>know. What do
>>>> >>>> >>>> >>>you think?
>>>> >>>> >>>> >>>
>>>> >>>> >>>> >>>
>>>> >>>> >>>> >>>
>>>> >>>> >>>> >>>-----Original Message-----
>>>> >>>> >>>> >>>From: zope-dev-admin@zope.org
>>>> >>>> >>>> >>>[mailto:zope-dev-admin@zope.org]On Behalf
>>>> >>>> >>>> >>>Of Capesius, Alan
>>>> >>>> >>>> >>>Sent: Wednesday, March 07, 2001 12:31 PM
>>>> >>>> >>>> >>>To: zope@zope.org
>>>> >>>> >>>> >>>Cc: zope-dev@zope.org
>>>> >>>> >>>> >>>Subject: [Zope-dev] RE: ZODBC problem
>>>> >>>> >>>> >>>
>>>> >>>> >>>> >>>
>>>> >>>> >>>> >>>Thanks Dieter, I was kinda looking for a solution, I
>>>> >>>> >>>> >>>reviewed the mailing
>>>> >>>> >>>> >>>lists but do not see any solutions. I've done
>>>some more
>>>> >>>> >>>testing here:
>>>> >>>> >>>> >>>
>>>> >>>> >>>> >>>Summary: The user selects an involved
>>>financial report. The
>>>> >>>> >>>> >>>report takes
>>>> >>>> >>>> >>>about 55 seconds for Zope to prepare. The
>>>first second is
>>>> >>>> >>>> >>>the SQL server
>>>> >>>> >>>> >>>request and reply. The remaining time is Zope
>>>processing
>>>> >>>> >>>> >>>(dual 200mhz NT
>>>> >>>> >>>> >>>box).
>>>> >>>> >>>> >>>
>>>> >>>> >>>> >>>My queries are SELECT JOINS. No tables are modified at
>>>> >>>> >>>the server.
>>>> >>>> >>>> >>>
>>>> >>>> >>>> >>>The problem I see here is that Zope/ZODBCDA
>>>is holding the
>>>> >>>> >>>> >>>connection to SQL
>>>> >>>> >>>> >>>open via ODBC until all processing is
>>>complete and then
>>>> >>>> >>>> >>>issuing an SQL
>>>> >>>> >>>> >>>statement: IF @@TRANCOUNT>0 COMMIT TRAN
>>>> >>>> >>>> >>>
>>>> >>>> >>>> >>>Zope could free up this connection after one
>>>second. (at
>>>> >>>> >>>> >>>least in this case)
>>>> >>>> >>>> >>>Until Zope does the commit, other users are
>>>locked out.
>>>> >>>> >>>> >>>
>>>> >>>> >>>> >>>For read only queries, there is no need to have the
>>>> >>>> >>>request under
>>>> >>>> >>>> >>>transaction control. In any case, Zope should
>>>be able to
>>>> >>>> >>>> >>>early release the
>>>> >>>> >>>> >>>SQL connection and then process on the results.
>>>> >>>> >>>> >>>
>>>> >>>> >>>> >>>Certainly increasing processor speed will
>>>mask this problem
>>>> >>>> >>>> >>>and provide
>>>> >>>> >>>> >>>better response to users, but I'm hoping for
>>>a real fix.
>>>> >>>> >>>> >>>
>>>> >>>> >>>> >>>Thanks,
>>>> >>>> >>>> >>>Alan
>>>> >>>> >>>> >>>
>>>> >>>> >>>> >>>Network Monitor trace Wed 03/07/01 10:51:50
>>>c:\cap1.TXT
>>>> >>>> >>>> >>>
>>>> >>>> >>>> >>>Frame Time Src MAC Addr Dst MAC Addr Protocol
>>>> >>>> >>>> >>>1 4.413 006008F6062B COMPAQ851F04 TCP
>>>> >>>> >>>> >>>2 4.413 006008F6062B COMPAQ851F04 TCP
>>>> >>>> >>>> >>>3 4.413 006008F6062B COMPAQ851F04 TCP
>>>> >>>> >>>> >>>[....]
>>>> >>>> >>>> >>>193 5.072 006008F6062B COMPAQ851F04 TCP
>>>> >>>> >>>> >>>194 5.073 COMPAQ851F04 006008F6062B TCP
>>>> >>>> >>>> >>>195 5.090 COMPAQ851F04 006008F6062B TCP
>>>> >>>> >>>> >>>196 5.090 006008F6062B COMPAQ851F04 TCP
>>>> >>>> >>>> >>>197 5.090 006008F6062B COMPAQ851F04 TCP
>>>> >>>> >>>> >>>198 5.091 COMPAQ851F04 006008F6062B TCP
>>>> >>>> >>>> >>>199 5.109 COMPAQ851F04 006008F6062B TCP
>>>> >>>> >>>> >>>200 5.127 COMPAQ851F04 006008F6062B
>>>TCP // SQL
>>>> >>>> >>>> >>>server goes idle
>>>> >>>> >>>> >>>after this
>>>> >>>> >>>> >>>
>>>> >>>> >>>> >>>// python DTML processing occurs here
>>>> >>>> >>>> >>>
>>>> >>>> >>>> >>>
>>>> >>>> >>>> >>>201 10.834 006008F6062B COMPAQ851F04
>>>TCP // not
>>>> >>>> >>>> >>>sure what this is,
>>>> >>>> >>>> >>>Data is 0xA8
>>>> >>>> >>>> >>>202 10.834 COMPAQ851F04 006008F6062B
>>>TCP // reply,
>>>> >>>> >>>> >>>data is empty
>>>> >>>> >>>> >>>
>>>> >>>> >>>> >>>// page returns to user at 60 second mark
>>>> >>>> >>>> >>>// then Zope closes the connection
>>>> >>>> >>>> >>>203 60.385 COMPAQ851F04 006008F6062B TCP
>>>> >>>> >>>> >>> SQL: IF @@TRANCOUNT>0 COMMIT TRAN
>>>> >>>> >>>> >>>204 60.386 006008F6062B COMPAQ851F04 TCP
>>>> >>>> >>>> >>>205 60.601 COMPAQ851F04 006008F6062B TCP
>>>> >>>> >>>> >>>
>>>> >>>> >>>> >>>
>>>> >>>> >>>> >>>>Capesius, Alan writes:
>>>> >>>> >>>> >>>> > ...
>>>> >>>> >>>> >>>> > with further testing I
>>>> >>>> >>>> >>>> > have determined that the Zope server itself is not
>>>> >>>> >>>> >>>locking up, but
>>>> >>>> >>>> >>>rather
>>>> >>>> >>>> >>>> > requests to the database (MS SQL7 via
>>>ZODBCDA) seem to
>>>> >>>> >>>> >>>be queueing up.
>>>> >>>> >>>> >>>
>>>> >>>> >>>> >>>>My memory tells me faintly that such an issue has
>>>> >>>already been
>>>> >>>> >>>> >>>>discussed in the mailing lists (zope, zope-dev).
>>>> >>>> >>>> >>>>When I remember right, ZODBCDA synchronizes all
>>>> >>>> >>>database requests
>>>> >>>> >>>> >>>>--> searchable list archives...
>>>> >>>> >>>> >>>
>>>> >>>> >>>> >>>
>>>> >>>> >>>> >>>>Dieter
>>>> >>>> >>>> >>>
>>>> >>>> >>>> >>>_______________________________________________
>>>> >>>> >>>> >>>Zope-Dev maillist - Zope-Dev@zope.org
>>>> >>>> >>>> >>>http://lists.zope.org/mailman/listinfo/zope-dev
>>>> >>>> >>>> >>>** No cross posts or HTML encoding! **
>>>> >>>> >>>> >>>(Related lists -
>>>> >>>> >>>> >>> http://lists.zope.org/mailman/listinfo/zope-announce
>>>> >>>> >>>> >>> http://lists.zope.org/mailman/listinfo/zope )
>>>> >>>> >>>> >>>
>>>> >>>> >>>>
>>>> >>>> >>>>_______________________________________________
>>>> >>>> >>>>Zope maillist - Zope@zope.org
>>>> >>>> >>>>http://lists.zope.org/mailman/listinfo/zope
>>>> >>>> >>>>** No cross posts or HTML encoding! **
>>>> >>>> >>>>(Related lists -
>>>> >>>> >>>> http://lists.zope.org/mailman/listinfo/zope-announce
>>>> >>>> >>>> http://lists.zope.org/mailman/listinfo/zope-dev )
>>>> >>>> >>>
>>>> >>>>
>>>>>>_____________________________________________________________
>>>> >>>> >>>____________
>>>> >>>> >>>Get Your Private, Free E-mail from MSN Hotmail at
>>>> >>>>http://www.hotmail.com.
>>>> >>>
>>>> >>>_____________________________________________________________
>>>> >>>____________
>>>> >>>Get Your Private, Free E-mail from MSN Hotmail at
>>>>http://www.hotmail.com.
>>>>
>>>>_______________________________________________
>>>>Zope maillist - Zope@zope.org
>>>>http://lists.zope.org/mailman/listinfo/zope
>>>>** No cross posts or HTML encoding! **
>>>>(Related lists -
>>>> http://lists.zope.org/mailman/listinfo/zope-announce
>>>> http://lists.zope.org/mailman/listinfo/zope-dev )
>>>
>>>_____________________________________________________________
>>>____________
>>>Get Your Private, Free E-mail from MSN Hotmail at
http://www.hotmail.com.