RE: [Zope] RE: ZODBC problem (and ZmxODBC)

8 Mar 2001

      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.

Capesius, Alan

tags

participants (1)