[Zope] Space in column name with ZODBC

Alan Capesius alanc@tech-world.com
Thu, 5 Jul 2001 20:47:07 -0500


Try single quotes instead of double quotes.

Another things you might try is brackets [First Name]
That's the way Access does it. (you can see the MSA SQL in the Design Screen
SQL View)
Brackets are also necessary if the field name matches an SQL keyword in
Access like [Desc], without the bracket you'll get a syntax error.

Better yet, lose all the dtml-sqltest stuff and just write SQL with the
parameters in dtml-sqlvar stmts.
Like this:

select * from CompByParent
where Parent = <dtml-sqlvar item type=int>
and Req like <dtml-sqlvar code type=string>
order by CItem

I'm not really sure why all the sql-test/sqlgroup stuff was necessary in
DTML, I've never had any use for it.

- Alan
---------------------------------------
Zope tips and tricks site
http://twsite.bizland.com/zopetips.htm



> -----Original Message-----
> From: zope-admin@zope.org [mailto:zope-admin@zope.org]On Behalf Of Loren
> Stafford
> Sent: Thursday, July 05, 2001 7:59 PM
> To: zope@zope.org
> Subject: [Zope] Space in column name with ZODBC
>
>
> I'm trying to create an ODBC interface to an existing (MSAccess) database
> that has spaces in its column names. My ZSQL looks like this...
>
> select * from "Applicant Database"
> <dtml-sqlgroup where>
>  <dtml-sqltest LastName op=like column="Last Name" type=string>
> <dtml-and>
>  <dtml-sqltest FirstName op=like column="First Name" type=string>
> </dtml-sqlgroup>
>
> ...but it generates this error...
>
>   File D:\Intranet\Zope224\lib\python\Products\ZODBCDA\db.py, line 198, in
> query
> sql.error: ('37000', -3100, &quot;[Microsoft][ODBC Microsoft
> Access Driver]
> Syntax error (missing operator) in query expression '(Last Name
> like 'A'\012
> and First Name like ''\012)'.&quot;)
>
> There's a lot of garbage in that generated SQL, but at least I'd expect to
> see the column names quoted or something. Is there a trick to it? Or am I
> trying to do something impossible? Or is there a bug in ZODBC?
>
> -- Thanks
> -- Loren
>
>
> _______________________________________________
> 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 )
>