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.