[Geomoose-users] Query.php and data types

[Johan Forsman]

Responding to my own post here I'd like to report a working fix.

In the source for query.php I found line 218 and changed it to

$p = new Predicate($layer, $_REQUEST['fieldname'.$i], "'".$value."'", $operator, $comparitor, $blank_okay);

where I prepended and appended the single quotes to the $value variable. Ugly, I'm sure, but it appears to have solved the problem I was facing. To maintain integrity I saved the file with a new name and reference the new file from the mapbook.

Next I'd like to be able to display and work with only the returned records rather than the highlighted subset on top of the entire layer. Is this something I can accomplish with my current version (GM 2.2)?

Thanks!

> -----Original Message-----
> From: Johan Forsman [mailto:Johan.Forsman at LA.GOV]
> Sent: Tuesday, July 05, 2011 7:30 AM
> To: 'geomoose-users at lists.sourceforge.net'
> Subject: [Geomoose-users] Query.php and data types
> 
> All:
> 
> On GM 2.2, I am having some difficulty obtaining results from query.php
> using PostGIS as the datastore.
> 
> There are 2 fields from 1 table being searched. The user selects the
> analyte_code values from a dropdown and sample_collection_date bracketed
> values are typed in.
> 
> The query executes but returns no results.
> 
> I found the debug lines in the query.php source and uncommented those
> lines which revealed the FILTER string as:
> 
> analyte_code = 1005 and sample_collection_date >= 2009-12-31 and
> sample_collection_date <= 2011-07-01
> 
> If I isolate the values individually it appears that the values are passed
> to the database as integers, which results in data-type errors from the
> database.
> 
> For analyte_code:
> Operator does not exist: character = integer.
> 
> And for sample_collection_date:
> Operator does not exist: timestamp without time zone >= integer
> 
> Now that last error looks a little off; is the postqresql trying to
> interpret the string and do math?
> 
> The data are in fact stored in the database as "character" and "timestamp
> without time zone", respectively.
> 
> >From the SQL console I can issue
> 
> select *
> from geodata.webmaps_mcl_violations
> where
> 	analyte_code = '1005' and
> 	sample_collection_date >= '2009-12-31' and
> 	sample_collection_date <= '2011-07-01';
> 
> which returns the expected results.
> 
> The hint from the sql console when the error is generated from the filter
> string is that I might need to add explicit type casts. If this is indeed
> the case, where do I add these? I experimented by adding single quotes in
> the query definition in the mapbook but those were escaped to \' which the
> database didn't care much for.
>