<div dir="ltr">Great! Thanks Jeff. Turns out it was the single quotes in other parts of the connection string... Not sure why. Possibly special characters in the password or username? After removing them, I no longer have connection issues. With that being solved, removing the WHERE clause at least returns a blank image instead of an error.<div><br></div><div>I then brought back the WHERE clause, which returned an error despite working in GDAL.</div><div>This WHERE clause returns a single raster result in GDAL with the exact same connection string as above:</div><div>gdalinfo "PG:.. where='timestamp=\'2019-03-05 00:00:00\''"</div><div><br>However, no matter the combination of string literals, escape characters, and whatnot, I could not get MapServer to handle this clause without error.<br><br>But wait! Enter Postgres's dollar-quoted string constants:<br>where='timestamp=$$2019-03-05 00:00:00$$' <br><br>The DATA connection string now works. Hope this helps anyone in the future.<br><br>Regards,<br>Thomas</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, Mar 5, 2019 at 3:24 PM Jeff McKenna <<a href="mailto:jmckenna@gatewaygeomatics.com">jmckenna@gatewaygeomatics.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Hi Thomas,<br>
<br>
I would try the following:<br>
<br>
- remove all single quotes from your DATA statement (most times they are <br>
unnecessary for my use, and cause me problems)<br>
- remove the WHERE clause<br>
- also try to access another table from your database, but not raster, <br>
using that same DATA connection statement (I bet the problem has nothing <br>
to do with the raster table, but instead is your connection parameters)<br>
- verify that remote connections can access your database (check your <br>
file pg_hba.conf)<br>
<br>
hope that brings ideas and opens doors...<br>
<br>
-jeff<br>
<br>
<br>
<br>
-- <br>
Jeff McKenna<br>
MapServer Consulting and Training Services<br>
<a href="https://gatewaygeomatics.com/" rel="noreferrer" target="_blank">https://gatewaygeomatics.com/</a><br>
<br>
<br>
On 2019-03-04 2:38 PM, Thomas Horner wrote:<br>
> I have a PostGIS connection string that works fine in GDAL, as I get a <br>
> raster response when running<br>
> ---<br>
> gdalinfo "PG:host=... port=5432 user='...' password='...' <br>
> dbname='weather_models' schema='rasters' <br>
> table='wpc_snow_50pct_1551268800' column='rast' mode=1 <br>
> where='timestamp=\'2019-03-02 00:00:00\''"<br>
> ---<br>
> <br>
> On this very same server, I have cgi-mapserver running, and the output <br>
> from /usr/lib/cgi-bin/mapserv/ -v is<br>
> ---<br>
> MapServer version 7.2.1 OUTPUT=PNG OUTPUT=JPEG OUTPUT=KML SUPPORTS=PROJ <br>
> SUPPORTS=AGG SUPPORTS=FREETYPE SUPPORTS=CAIRO SUPPORTS=SVG_SYMBOLS <br>
> SUPPORTS=RSVG SUPPORTS=ICONV SUPPORTS=FRIBIDI SUPPORTS=WMS_SERVER <br>
> SUPPORTS=WMS_CLIENT SUPPORTS=WFS_SERVER SUPPORTS=WFS_CLIENT <br>
> SUPPORTS=WCS_SERVER SUPPORTS=SOS_SERVER SUPPORTS=FASTCGI <br>
> SUPPORTS=THREADS SUPPORTS=GEOS SUPPORTS=PBF INPUT=JPEG INPUT=POSTGIS <br>
> INPUT=OGR INPUT=GDAL INPUT=SHAPEFILE<br>
> ---<br>
> <br>
> The problem is, when I use that same PG:... connection string as the <br>
> DATA for a LAYER in MapServer, I get an unhelpful error: Unable to <br>
> access file. Corrupt, empty or missing file '<snip of PG connection <br>
> string>' Couldn't establish a database connection.<br>
> <br>
> Despite turning DEBUG level to 5, and CPL_DEBUG to ON in the MAP block <br>
> of my mapfile, I don't get any helpful debug information whatsoever in <br>
> my error log -- just the connection string and "Couldn't establish a <br>
> database connection." I have tried a variety of where clauses and other <br>
> tables and get the same result. I have no idea if the actual error is <br>
> coming from GDAL or MapServer, despite all those strings working fine <br>
> when running them directly via GDAL.<br>
> <br>
> By the way, it's the same error whether I use 'timestamp'= or timestamp=.<br>
> <br>
> Here is the mapfile:<br>
> ---<br>
> MAP<br>
> CONFIG "MS_ERRORFILE" "/map/error.txt"<br>
> CONFIG "CPL_DEBUG" "ON"<br>
> DEBUG 5<br>
> PROJECTION<br>
> "init=epsg:4326"<br>
> END<br>
> LAYER<br>
> NAME wpc_snow_50pct<br>
> TYPE RASTER<br>
> STATUS ON<br>
> VALIDATION<br>
> "timestamp" ".*"<br>
> END<br>
> PROJECTION<br>
> "init=epsg:4326"<br>
> END<br>
> METADATA<br>
> "wms_title" "wpc_snow_50pct"<br>
> "wms_srs" "EPSG:4326"<br>
> "wms_extent" "-108.5 41 -104.5 36"<br>
> "wms_enable_request" "*"<br>
> END<br>
> DATA "PG:host=... port=5432 dbname='weather_models' user='...' <br>
> password='...' schema='rasters' table='wpc_snow_50pct_1551268800' <br>
> column='rast' mode=1 where='timestamp=\'2019-03-02 00:00:00\''"<br>
> PROCESSING "BANDS=1"<br>
> END<br>
> END<br>
> ---<br>
> <br>
> Any help is appreciated!<br>
> <br>
> Thanks,<br>
> Thomas<br>
> <br>
> <br>
_______________________________________________<br>
mapserver-users mailing list<br>
<a href="mailto:mapserver-users@lists.osgeo.org" target="_blank">mapserver-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/mapserver-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/mapserver-users</a></blockquote></div>