[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.