[Zope] Resolving nvarchar fields via ZmxODBCDA

[Capesius, Alan]

This query works under ZODBCDA. It allows searching of SQL7 tables for
specific field names or search strings. 
e.g. %NAME% will find any field name containing 'NAME'
     NAME will find any field named 'NAME'


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


It fails under ZmxODBC
the following example shows how to convert the nvarchar fields to char type
fields within an SQL query so that ZmxODBC can deal with them:


SELECT 
table_catalog = CONVERT(char(30),table_catalog),
table_name = CONVERT(char(30),table_name),
column_name = CONVERT(char(30),column_name),
data_type = CONVERT(char(30),data_type)
FROM INFORMATION_SCHEMA.COLUMNS
where column_name like <dtml-sqlvar search_string type=string> 
order by ordinal_position

The same technique should apply to other unsupported types.


Alan Capesius, MCSE/NTCIP+20
Technical Support Engineer
Sysmex Corporation of America
capesiusa@sysmex.com

http://twsite.bizland.com/zopetips.htm