Re: Fwd: Re: [Modeling-users] ec.fetch and SQL "in"

[Sebastien B. <sbi...@us...>]

  Nice try, but that was not the place where the test should go. Since
  you tried to dive into the quite complex class SQLExpression, I'll be
  a little verbose and "techy" in the explanation ;)

  This actually works because when the sql is built for this qualifier,
it ultimately tries to format the value for your attribute; since your
attribute is an integer, you end up with sqlStringForNumber() being
called.  But then, you'll still need to be replicate the same code for
strings, floats, etc. to support list with one item only. Moreover,
these methods are intended to work with a single value of a given type,
not with lists! That's why I added method sqlStringForInOperatorValue.

  However, your proposal immediately rang a bell and I thank you again,
  indeed, because it revealed a flaw in the last patch I posted a few
  minutes ago: when it comes to operators 'in' and 'not in', it's a
  non-sense to call either of these methods (e.g. sqlStringForNumber,
  triggered by sqlStringForKeyValueQualifier, through sqlStringForValue
  and formatValueForAttribute).

  =3D> please un-apply the previous patch and use that one instead.

-- S=E9bastien.


PS: To be completely honest there's a remaining problem here, because
    each value in the list should be formatted depending on the
    attribute's type (for example, with sqlStringForNumber() for an int
    attribute). However, you'll *never* encounter any problem unless you
    build your qualifiers instances explicitly (vs. from a string) with
    a list of values that need to be formatted (Date is a good example).
    I've no time for this now, but this will obviously be tested and
    corrected before it is committed into CVS.

------------------------------------------------------------------------
--- SQLExpression.py.ko Tue Jul  8 20:19:19 2003
+++ SQLExpression.py    Tue Jul  8 20:34:53 2003
@@ -1190,9 +1190,6 @@
     key=3DaQualifier.key()
     value=3DaQualifier.value()
=20=20=20=20=20
-    if aQualifier.operator() in (QualifierOperatorIn, QualifierOperatorNot=
In):
-      value=3Dtuple(value)
-=20=20=20=20=20=20
     if not caseInsensitive:
       operatorStr=3Dself.sqlStringForSelector(aQualifier.operator(), value)
=20
@@ -1201,13 +1198,24 @@
       value=3Dself.sqlPatternFromShellPattern(value)
=20
     keyString=3Dself.sqlStringForAttributeNamed(key)
-    valueString=3Dself.sqlStringForValue(value, key)
+    if aQualifier.operator() in (QualifierOperatorIn, QualifierOperatorNot=
In):
+      valueString=3Dself.sqlStringForInOperatorValue(value)
+    else:
+      valueString=3Dself.sqlStringForValue(value, key)
=20
     if not caseInsensitive:
       return keyString+' '+operatorStr+' '+valueString
     else:
       return self.sqlStringForCaseInsensitiveLike(keyString, valueString)
=20
+  def sqlStringForInOperatorValue(self, aList):
+    """
+    """
+    if len(aList)=3D=3D1:
+      return '(%s)'%aList[0]
+    else:
+      return str(tuple(aList))
+=20=20=20=20
   def sqlStringForNegatedQualifier(self, aQualifier):
     """
     Returns the SQL string for the supplied Qualifier
------------------------------------------------------------------------


Yannick Gingras <yan...@sa...> wrote:
> On July 8, 2003 01:42 pm, Yannick Gingras wrote:
> > Argh, I think we have a problem when there is only one elem in the
> > choice list :
> >
> > <Fault 1: 'Modeling.Adaptor.GeneralAdaptorException:Couldn\'t evaluate
> > expression SELECT t0.id, t0.gl_id, t0.fs2_id FROM FSLINK t0 WHERE
> > (t0.fs2_id NOT IN (4,) AND t0.gl_id LIKE 1). Reason:
> > _mysql_exceptions.ProgrammingError:(1064, "You have an error in your SQL
> > syntax near \') AND t0.gl_id LIKE 1)\' at line 1")'>
> >
> > I don't think that : "NOT IN (4,)" is legal...
>=20
> I don't know if it's where the test should go but the following patch
> fix the problem :
>=20
> --- SQLExpression.old.py        2003-07-08 13:47:22.000000000 -0400
> +++ SQLExpression.py    2003-07-08 14:08:58.000000000 -0400
> @@ -1235,7 +1235,10 @@
>      See also: formatValueForAttribute()
>      """
>      if aNumber is not None:
>      return str(aNumber)
> +      if type(aNumber) =3D=3D type((1,)) and len(aNumber) =3D=3D 1:
> +        return "(%d)" % aNumber
> +      else:
> +        return str(aNumber)
>      else: return 'NULL'
>=20
>    def sqlStringForQualifier(self, aQualifier):






>=20
> --=20
> Yannick Gingras
> Byte Gardener, Savoir-faire Linux inc.
> (514) 276-5468
>=20
>=20
>=20
>=20
> -------------------------------------------------------
> This SF.Net email sponsored by: Parasoft
> Error proof Web apps, automate testing & more.
> Download & eval WebKing and get a free book.
> www.parasoft.com/bulletproofapps
> _______________________________________________
> Modeling-users mailing list
> Mod...@li...
> https://lists.sourceforge.net/lists/listinfo/modeling-users