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, "[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '(Last Name like 'A'\012 and First Name like ''\012)'.") 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
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, "[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '(Last Name like 'A'\012 and First Name like ''\012)'.")
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 )
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, "[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '(Last Name like 'A'\012 and First Name like ''\012)'.")
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
You need to quote the names with a double quote, I believe, for the odbc driver to use them. I've done this in Cold Fusion for dealing with an Access database using names with spaces. In dtml, using quotes simply tells Zope to evalute the quoted expression as a python expression, which isn't quite what you want. I think this will work although I haven't tried it (I've used it for single quotes successfully): column="\"Last Name\"" In other words, escape the double quote using a backslash before it. This escaping syntax is standard Python, and Python should proceed to create a string with the double quotes in it. Let us know if it works or not. Cheers, Tom P [Loren Stafford]
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...
participants (3)
-
Alan Capesius -
Loren Stafford -
Thomas B. Passin