[Zope] RE: ZODBC problem (and ZmxODBC)
Capesius, Alan
CapesiusA@Sysmex.com
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.