Opened 19 years ago
Last modified 15 years ago
#1101 new defect
[OGR - WFS] SQL VRT with WFS
| Reported by: | jlacroix | Owned by: | mapserverbugs |
|---|---|---|---|
| Priority: | high | Milestone: | |
| Component: | WFS Server | Version: | 4.4 |
| Severity: | normal | Keywords: | |
| Cc: |
Description
A VRT data source containing a SQL Query may have problem with WFS. If the SQL query returns a binary field, OGR will return a gml file containing illegal characters (Binary data). For example, I have a MySQL query on a table (SELECT * FROM table1) that contains a blob with binary data in it. When I try to query those data via WFS, the gml returned by MapServer contains a tag <geom>[insert_fuzzy_characters_here]</geom>. This make the WFS client (MapServer again) to fail. A ogrinfo on the GML file returns: FAILURE: Unable to open datasource `/opt/fgs/tmp/ms_tmp//64b2afb384715e6ca9a49d6c393713e7.tmp.gml' with the following drivers. A proposed solution is to use CDATA in the XML. See: http://www.w3schools.com/xml/xml_cdata.asp Paul Spencer wrote: AFAIK you can use CDATA in any XML document to indicate 'character data' inside an element, it is used as follows: <element><![CDATA[this is my data to be treated exactly as is, you can use invalid characters like & < and > ...]]></element> The only rules appears to be: * the delimiter ']]>' must be continuous, no spaces, no linebreaks inside this delimiter * you can't use ]]> inside a cdata
Attachments (1)
Change History (6)
comment:2 by , 19 years ago
Originally the geom field was a geometry field, butmy installation of MySQL does
not support this type so I changed it to blob.
CREATE TABLE `cimtLocations` (
`locationID` int(10) unsigned NOT NULL auto_increment,
`time` datetime NOT NULL default '0000-00-00 00:00:00',
`lat` float NOT NULL default '0',
`longitude` float NOT NULL default '0',
`geom` blob,
PRIMARY KEY (`locationID`)
);
INSERT INTO `cimtLocations` VALUES (1, '2004-11-14 00:00:00', '40', '-99',
0xe610000001010000000000000000388fc00000000000388fc0);
INSERT INTO `cimtLocations` VALUES (2, '2004-11-14 00:00:00', '-37.9275',
'-123', 0xe61000000101000000931804560e855ec01f85eb51b8764240);
INSERT INTO `cimtLocations` VALUES (3, '2004-11-14 00:00:00', '38.9273',
'-112.078', 0xe61000000101000000a245b6f3fd845ec058ca32c4b1764240);
INSERT INTO `cimtLocations` VALUES (4, '2004-11-14 00:00:00', '43.9272',
'-122.078', 0xe61000000101000000a245b6f3fd845ec0f46c567dae764240);
INSERT INTO `cimtLocations` VALUES (5, '2003-11-19 00:00:00', '56.9282',
'-64.082', 0xe610000001010000006891ed7c3f855ec0d712f241cf764240);
INSERT INTO `cimtLocations` VALUES (6, '2003-11-19 00:00:00', '76.928',
'-83.081', 0xe6100000010100000077be9f1a2f855ec0105839b4c8764240);
INSERT INTO `cimtLocations` VALUES (7, '2003-11-19 00:00:00', '26.9278',
'-102.08', 0xe6100000010100000085eb51b81e855ec0499d8026c2764240);
INSERT INTO `cimtLocations` VALUES (8, '2003-11-19 00:00:00', '26.9275',
'-92.08', 0xe6100000010100000085eb51b81e855ec01f85eb51b8764240);
INSERT INTO `cimtLocations` VALUES (9, '2003-11-19 00:00:00', '27.9273',
'-72.079', 0xe61000000101000000931804560e855ec058ca32c4b1764240);
INSERT INTO `cimtLocations` VALUES (10, '2003-11-19 00:00:00', '41.9272',
'-67.078', 0xe61000000101000000a245b6f3fd845ec0f46c567dae764240);
comment:4 by , 15 years ago
<?xml version='1.0' encoding="ISO-8859-1" ?>
<wfs:FeatureCollection
xmlns:ms="http://mapserver.gis.umn.edu/mapserver"
xmlns:wfs="http://www.opengis.net/wfs"
xmlns:gml="http://www.opengis.net/gml"
xmlns:ogc="http://www.opengis.net/ogc"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.opengis.net/wfs http://ogc.dmsolutions.ca/wfs/1.0.0/WFS-basic.xsd
http://mapserver.gis.umn.edu/mapserver http://localhost:9090/cgi-bin/mswfs_ticket1101?SERVICE=WFS&VERSION=1.0.0&REQUEST=DescribeFeatureType&TYPENAME=cimtlocations&OUTPUTFORMAT=XMLSCHEMA">
<gml:boundedBy>
<gml:Box srsName="EPSG:4326">
<gml:coordinates>-122.078000,26.927500 -67.078000,43.927200</gml:coordinates>
</gml:Box>
</gml:boundedBy>
<gml:featureMember>
<ms:cimtlocations>
<gml:boundedBy>
<gml:Box srsName="EPSG:4326">
<gml:coordinates>-99.000000,40.000000 -99.000000,40.000000</gml:coordinates>
</gml:Box>
</gml:boundedBy>
<ms:msGeometry>
<gml:Point srsName="EPSG:4326">
<gml:coordinates>-99.000000,40.000000</gml:coordinates>
</gml:Point>
</ms:msGeometry>
<ms:locationID>1</ms:locationID>
<ms:time>2004/11/14 0:00:00</ms:time>
<ms:lat> 40</ms:lat>
<ms:longitude> -99</ms:longitude>
<ms:geom>E610000001010000000000000000388FC00000000000388FC0</ms:geom>
</ms:cimtlocations>
</gml:featureMember>
<gml:featureMember>
<ms:cimtlocations>
<gml:boundedBy>
<gml:Box srsName="EPSG:4326">
<gml:coordinates>-112.078000,38.927300 -112.078000,38.927300</gml:coordinates>
</gml:Box>
</gml:boundedBy>
<ms:msGeometry>
<gml:Point srsName="EPSG:4326">
<gml:coordinates>-112.078000,38.927300</gml:coordinates>
</gml:Point>
</ms:msGeometry>
<ms:locationID>3</ms:locationID>
<ms:time>2004/11/14 0:00:00</ms:time>
<ms:lat> 39</ms:lat>
<ms:longitude> -112</ms:longitude>
<ms:geom>E61000000101000000A245B6F3FD845EC058CA32C4B1764240</ms:geom>
</ms:cimtlocations>
</gml:featureMember>
<gml:featureMember>
<ms:cimtlocations>
<gml:boundedBy>
<gml:Box srsName="EPSG:4326">
<gml:coordinates>-122.078000,43.927200 -122.078000,43.927200</gml:coordinates>
</gml:Box>
</gml:boundedBy>
<ms:msGeometry>
<gml:Point srsName="EPSG:4326">
<gml:coordinates>-122.078000,43.927200</gml:coordinates>
</gml:Point>
</ms:msGeometry>
<ms:locationID>4</ms:locationID>
<ms:time>2004/11/14 0:00:00</ms:time>
<ms:lat> 44</ms:lat>
<ms:longitude> -122</ms:longitude>
<ms:geom>E61000000101000000A245B6F3FD845EC0F46C567DAE764240</ms:geom>
</ms:cimtlocations>
</gml:featureMember>
<gml:featureMember>
<ms:cimtlocations>
<gml:boundedBy>
<gml:Box srsName="EPSG:4326">
<gml:coordinates>-102.080000,26.927800 -102.080000,26.927800</gml:coordinates>
</gml:Box>
</gml:boundedBy>
<ms:msGeometry>
<gml:Point srsName="EPSG:4326">
<gml:coordinates>-102.080000,26.927800</gml:coordinates>
</gml:Point>
</ms:msGeometry>
<ms:locationID>7</ms:locationID>
<ms:time>2003/11/19 0:00:00</ms:time>
<ms:lat> 27</ms:lat>
<ms:longitude> -102</ms:longitude>
<ms:geom>E6100000010100000085EB51B81E855EC0499D8026C2764240</ms:geom>
</ms:cimtlocations>
</gml:featureMember>
<gml:featureMember>
<ms:cimtlocations>
<gml:boundedBy>
<gml:Box srsName="EPSG:4326">
<gml:coordinates>-92.080000,26.927500 -92.080000,26.927500</gml:coordinates>
</gml:Box>
</gml:boundedBy>
<ms:msGeometry>
<gml:Point srsName="EPSG:4326">
<gml:coordinates>-92.080000,26.927500</gml:coordinates>
</gml:Point>
</ms:msGeometry>
<ms:locationID>8</ms:locationID>
<ms:time>2003/11/19 0:00:00</ms:time>
<ms:lat> 27</ms:lat>
<ms:longitude> -92</ms:longitude>
<ms:geom>E6100000010100000085EB51B81E855EC01F85EB51B8764240</ms:geom>
</ms:cimtlocations>
</gml:featureMember>
<gml:featureMember>
<ms:cimtlocations>
<gml:boundedBy>
<gml:Box srsName="EPSG:4326">
<gml:coordinates>-72.079000,27.927300 -72.079000,27.927300</gml:coordinates>
</gml:Box>
</gml:boundedBy>
<ms:msGeometry>
<gml:Point srsName="EPSG:4326">
<gml:coordinates>-72.079000,27.927300</gml:coordinates>
</gml:Point>
</ms:msGeometry>
<ms:locationID>9</ms:locationID>
<ms:time>2003/11/19 0:00:00</ms:time>
<ms:lat> 28</ms:lat>
<ms:longitude> -72</ms:longitude>
<ms:geom>E61000000101000000931804560E855EC058CA32C4B1764240</ms:geom>
</ms:cimtlocations>
</gml:featureMember>
<gml:featureMember>
<ms:cimtlocations>
<gml:boundedBy>
<gml:Box srsName="EPSG:4326">
<gml:coordinates>-67.078000,41.927200 -67.078000,41.927200</gml:coordinates>
</gml:Box>
</gml:boundedBy>
<ms:msGeometry>
<gml:Point srsName="EPSG:4326">
<gml:coordinates>-67.078000,41.927200</gml:coordinates>
</gml:Point>
</ms:msGeometry>
<ms:locationID>10</ms:locationID>
<ms:time>2003/11/19 0:00:00</ms:time>
<ms:lat> 42</ms:lat>
<ms:longitude> -67</ms:longitude>
<ms:geom>E61000000101000000A245B6F3FD845EC0F46C567DAE764240</ms:geom>
</ms:cimtlocations>
</gml:featureMember>
</wfs:FeatureCollection>
<?xml version='1.0' encoding="ISO-8859-1" standalone="no" ?> <!DOCTYPE ServiceExceptionReport SYSTEM "http://ogc.dmsolutions.ca/wms/1.1.0/exception_1_1_0.dtd"> <ServiceExceptionReport version="1.1.0"> <ServiceException> msDrawMap(): Image handling error. Failed to draw layer named 'cimtlocations'. msWFSLayerWhichShapes(): WFS connection error. WFS request produced unexpected output (junk?) for layer cimtlocations. msBuildWFSLayerPostRequest(): WFS connection error. MapServer supports only WFS 1.0.0 or 0.0.14 (please verify the version metadata wfs_version). </ServiceException> </ServiceExceptionReport>
comment:5 by , 15 years ago
Ref comment:1
The gml in the first part of the previous comment produces the service exception report of the second part. The request that generated the gml is: http://127.0.0.1:9090/cgi-bin/mswfs_ticket1101?version=1.0.0&service=wfs&request=getfeature&typename=cimtlocations and the request that generated the service exception is: http://localhost:9090/cgi-bin/mswfs_client_ticket1101?service=wms&version=1.1.0&request=getmap&layers=cimtlocations&srs=EPSG:4326&bbox=-180.0,-90.0,180.0,90.0&format=image/png&width=400&height=300 .
The sql database does not have a password. The command line to verify the mysql database is: ogrinfo MySQL:ticket1101,user=nsavard,host=localhost,port=3306 tables=cimtLocations -al. The command line to access the database is: mysql -D ticket1101 -u nsavard -h localhost .
Add the following symbolic links in the /path/to/fgs/www/cgi-bin directory:
mswfs_client_ticket1101 -> /home/nsavard/products/mapserver/bugs_testing/mswfs_client_ticket1101 mswfs_ticket1101 -> /home/nsavard/products/mapserver/bugs_testing/mswfs_ticket1101
If you get the following error when accessing mysql database, add a symbolic link from /tmp/mysql.sock to /var/run/mysqld/mysqld.sock ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysqld.sock' (2)
This error occurs because mysql server is listening on /var/run/mysqld/mysqld.sock but mysql client is sending the data on /tmp/mysql.sock. It happens within fgs environment.
I'll attach the map files, the sql dump and the virtual data file.
Note:
See TracTickets
for help on using tickets.
