[Zope] RE: ZODBC problem (and ZmxODBC)
Júlio Dinis Silva
juliodinis@hotmail.com
Thu, 08 Mar 2001 23:03:09 -0000
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.