<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>PHP_Mapscript QueryByAttributes on PostGIS layer - bug or feature?</TITLE>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2900.2963" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=885310308-24102006>Hi All,</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=885310308-24102006></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=885310308-24102006>My QueryByAttributes PostGIS problem seems to
be worse in version 4.10. </SPAN></FONT><FONT face=Arial color=#0000ff
size=2><SPAN class=885310308-24102006>In 4.8.4, at least I could
do:</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=885310308-24102006></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial size=2><SPAN
class=885310308-24102006>$oLayer->queryByAttributes("roadname","roadname like
'EMERALD RD'",MS_MULTIPLE);<FONT face="Times New Roman" size=3> // must
include SQL operators for query to work</FONT></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=885310308-24102006></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=885310308-24102006>But now, even that doesn't work anymore. It seems that
it trips over the single quotes around the value. Without the quotes, I can no
longer search on string values in a PostGIS table. What has been changed since
4.8?</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=885310308-24102006></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=885310308-24102006>My query does:</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=885310308-24102006></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=885310308-24102006>$gotResult =
$oLayer->queryByAttributes("pglayer","name like 'new layer'",MS_MULTIPLE);
</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=885310308-24102006></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=885310308-24102006></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=885310308-24102006>My log says:</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=885310308-24102006>------------ <SPAN
class=885310308-24102006>------------ <SPAN
class=885310308-24102006>------------ <SPAN
class=885310308-24102006>------------ <SPAN
class=885310308-24102006>------------ <SPAN
class=885310308-24102006>------------ <SPAN
class=885310308-24102006>------------ <SPAN
class=885310308-24102006>------------</SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=885310308-24102006><FONT size=2>[24-Oct-2006 16:08:28] PHP Warning:
[MapServer Error]: prepare_database(): Error executing POSTGIS DECLARE (the
actual query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT
"name"::text,asbinary(force_collection(force_2d(the_geom)),'NDR'),OID::text from
pgpoly WHERE (name like ) and (the_geom && setSRID( 'BOX3D(-149999998.5
-99999999,149999998.5 99999999)'::BOX3D,find_srid('','pgpoly','the_geom') ))'
Postgresql reports the error as 'ERROR: syntax error at or near ")" at character
148</DIV></FONT></SPAN></FONT>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=885310308-24102006><SPAN class=885310308-24102006>------------ <SPAN
class=885310308-24102006>------------ <SPAN
class=885310308-24102006>------------ <SPAN
class=885310308-24102006>------------ <SPAN
class=885310308-24102006>------------ <SPAN
class=885310308-24102006>------------ <SPAN
class=885310308-24102006>------------ <SPAN
class=885310308-24102006>------------</SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006>I'd appreciate it if someone can help me figure this
out. I did not see any bugs in Bugzilla for
this.</SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006>regards,</SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006>Jacob</SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006><SPAN class=885310308-24102006><SPAN
class=885310308-24102006></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff
size=2></FONT> </DIV><BR>
<BLOCKQUOTE dir=ltr
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px solid; MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> UMN MapServer Users List
[mailto:MAPSERVER-USERS@LISTS.UMN.EDU] <B>On Behalf Of </B>Delfos,
Jacob<BR><B>Sent:</B> 19 September 2006 09:06<BR><B>To:</B>
MAPSERVER-USERS@LISTS.UMN.EDU<BR><B>Subject:</B> [UMN_MAPSERVER-USERS]
PHP_Mapscript QueryByAttributes on PostGIS layer - bug or
feature?<BR></FONT><BR></DIV>
<DIV></DIV><!-- Converted from text/rtf format -->
<P><FONT face=Arial size=2>Hi List,</FONT> </P>
<P><FONT face=Arial size=2>I'm trying to perform a QueryByAttributes operation
on a PostGIS layer, but I get an error. Basically, it seems that the where
clause being generated leaves out the attribute name and operator. Am I doing
something wrong?</FONT></P>
<P><FONT face=Arial size=2>My code is:</FONT> </P>
<P><FONT face=Arial size=2> $gotResult =
$oLayer->queryByAttributes("roadname","EMERALD RD",MS_MULTIPLE);</FONT>
</P>
<P><FONT face=Arial size=2>My PHP error is:</FONT> </P>
<P><FONT face="Courier New" size=2>[19-Sep-2006 08:53:34] PHP Warning:
[MapServer Error]: prepare_database(): Error executing POSTGIS DECLARE (the
actual query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT
roadname::text,asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text
from roads WHERE (EMERALD RD) and (the_geom && setSRID(
'BOX3D(-149999998.5 -99999999,149999998.5
99999999)'::BOX3D,find_srid('','roads','the_geom') ))'
<br><br></FONT></P>
<P><FONT face=Arial size=2>It works if I do:</FONT> </P>
<P><FONT face=Arial size=2>$oLayer->queryByAttributes("roadname","roadname
like 'EMERALD RD'",MS_MULTIPLE);</FONT> </P>
<P><FONT face=Arial size=2>Am I supposed to perform the QueryByAttributes
operation differently for different data sources? It gets more difficult if I
don't know in advance whether the type is text or number….</FONT></P>
<P><FONT face=Arial size=2>I searched the list and bugzilla for something
similar, but couldn't find it. I'm using php_mapscript 4.8.3 (I tried using
4.10beta2, but all my mapfiles get invalid map extent errors for some
reason).</FONT></P>
<P><FONT face=Arial size=2>Regards,</FONT> </P>
<P><FONT face=Arial size=2>Jacob</FONT> </P><BR>
<P><B><FONT face=Arial color=#009500>J</FONT><FONT face=Arial color=#009500
size=1>ACOB</FONT> <FONT face=Arial color=#009500>D</FONT><FONT face=Arial
color=#009500 size=1>ELFOS<BR></FONT><FONT face=Arial
color=#009500>GIS</FONT><FONT face=Arial color=#009500 size=1></FONT> <FONT
face=Arial color=#009500>A</FONT><FONT face=Arial color=#009500
size=1>NALYST</FONT><BR></B><FONT face=Arial color=#000000 size=2>Maunsell
Australia Pty Ltd<BR>629 Newcastle Street, Leederville, WA 6007</FONT>
<BR><FONT face=Arial color=#000000 size=2>PO Box 81, Leederville, WA
6902</FONT> <BR><FONT face=Arial color=#000000 size=2>Western Australia</FONT>
<BR><FONT face=Arial color=#000000 size=2>ABN 20 093 846 925<BR><BR>Tel
+ 61 8 9281 6185<BR>Fax + 61 8 9281
6297<BR></FONT><U><FONT face=Arial color=#009c21
size=2>jacob.delfos@maunsell.com</FONT></U> </P></BLOCKQUOTE></BODY></HTML>