On Sunday 10 February 2008 12:45 pm, Adrian Klaver wrote: > On Sunday 10 February 2008 12:36 pm, Ed Leafe wrote: > > On Feb 10, 2008, at 2:31 PM, Adrian Klaver wrote: > > > Here is the sequence of events leading up to the error and the full > > > error > > > message. > > > > > > pl_biz=self.getBizobj() > > > pl_ds=pl_biz.getDataSet() > > > pl_rs=pl_ds.execute("select * from dataset where common ilike 'agast > > > %'") > > > > > > Traceback (most recent call last): > > > File "<input>", line 1, in <module> > > > File "/home/aklaver/dabo_program/dabo/db/dDataSet.py", line 351, in > > > execute > > > self._cursor.execute(sqlExpr, params) > > > OperationalError: near "ilike": syntax error > > > > That's what I thought. DataSets are based on SQLite, no matter what > > backend they originated from. So the only syntax that works is what > > SQLite understands. According to the SQLite docs: > > > > "The LIKE operator does a pattern matching comparison. The operand to > > the right contains the pattern, the left hand operand contains the > > string to match against the pattern. A percent symbol % in the pattern > > matches any sequence of zero or more characters in the string. An > > underscore _ in the pattern matches any single character in the > > string. Any other character matches itself or it's lower/upper case > > equivalent (i.e. case-insensitive matching). (A bug: SQLite only > > understands upper/lower case for 7-bit Latin characters. Hence the > > LIKE operator is case sensitive for 8-bit iso8859 characters or UTF-8 > > characters. For example, the expression 'a' LIKE 'A' is TRUE but 'æ' > > LIKE 'Æ' is FALSE.)." > > > > -- Ed Leafe > > So the option here is to go the myBizobj.getTempCursor() route.
Tried the temp Cursor method. I did not get a syntax error but I also did not get any data. The sequence of commands was: pl_biz=self.getBizobj() pl_tc=pl_biz.getTempCursor() pl_tc.execute("select * from plant1 where common ilike 'agast%'" pl_tc.fetchall() [] The same query from psql returns : production=> SELECT * from plant1 where common ilike 'agast%'; p_item_no | common | genus | species | variety | plant_type | series | season | ts_insert | ts_update | user_update | user_insert | color | category_type | category_sub_type -----------+---------------------------------+-----------+------------+--------- --------+------------+--------+-----------+---------------------+--------------- ------+-------------+-------------+-------+---------------+------------------- 8 | agastache heather queen | agastache | cana | mint | flower | | perennial | 2004-01-01 00:00:00 | 2007-12-20 16:04:02 | postgres | postgres | NULL | plant | normal 6 | agastache camphor hyssop | agastache | | camphor hyssop | herb | | unknown | 2004-01-01 00:00:00 | 2008-01-30 10:01:20 | aklaver | postgres | NULL | plant | normal 9 | agastache mosquito plant | agastache | cana | mint | herb | | unknown | 2004-01-01 00:00:00 | 2008-01-30 10:01:20 | aklaver | postgres | NULL | plant | normal 7 | agastache apricot sunrise | agastache | NULL | apricot sunrise | herb | | unknown | 2004-01-01 00:00:00 | 2008-01-30 10:01:20 | aklaver | postgres | NULL | plant | normal 10 | agastache licorice mint(purple) | agastache | foeniculum | anise hyssop | herb | t | unknown | 2004-01-01 00:00:00 | 2008-02-01 19:19:34 | postgres | postgres | NULL | plant | normal 11 | agastache korean mint | agastache | rugosa | mint | herb | | unknown | 2004-01-01 00:00:00 | 2008-02-02 15:14:17 | aklaver | postgres | NULL | plant | normal 732 | agastache rupestris | agastache | rupestris | | herb | | unknown | 2004-01-01 00:00:00 | 2008-02-02 15:14:17 | aklaver | postgres | NULL | plant | normal (7 rows) -- Adrian Klaver [EMAIL PROTECTED] _______________________________________________ Post Messages to: Dabo-users@leafe.com Subscription Maintenance: http://leafe.com/mailman/listinfo/dabo-users Searchable Archives: http://leafe.com/archives/search/dabo-users This message: http://leafe.com/archives/byMID/dabo-users/[EMAIL PROTECTED]