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]
