[Zope] Space in column name with ZODBC

[Loren Stafford]

Thanks for the tip. This works:

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>

You're probably right about the sqlgroup stuff too. According to the manual,
it's suppose to help with optional parameters, but, given the vaguaries of
HTML forms processing -- where parameters may be missing; or not missing,
but empty -- I guess I have to handle the various cases myself.

-- Thanks Alan and others who replied
-- Loren


> -----Original Message-----
> From: zope-admin@zope.org [mailto:zope-admin@zope.org]On Behalf Of Alan
> Capesius
> Sent: Thursday, July 05, 2001 18:47
> To: zope@zope.org
> Subject: RE: [Zope] Space in column name with ZODBC
>
>
> 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
> >