Mysql

Wed Apr 12 06:17:09 PDT 2006

Grabiel,

It appears as though there are spaces in between the parameters of 
your MySQL connection string.  I think it should be:

MySQL:test,host=hostname.somewhere,user=root,password=pw

I'm not sure this is the real issue though,

Howard

At 8:29 AM -0400 4/12/06, grabiel wrote:
>Hi
>   Thats excellent in a very short period of time (What time).
>   I am using gdal 1.5 but when i try to execute ogrinfo 'MYSQL:test, 
>host=localhost, user=root, password=mypass, tables=asmroad1' asmroad1
>   from command line it gives result like this-------->
>
>  >ogrinfo 'MYSQL:test, host=localhost.localdomain, user=root, 
>password=zxcvydas, tables=asmroad1' asmroad1
>  >Warning 1: ' host=localhost.localdomain' in MYSQL datasource 
>definition not recognised and ignored.
>  >Warning 1: ' user=root' in MYSQL datasource definition not 
>recognised and ignored.
>  >Warning 1: ' password=zxcvydas' in MYSQL datasource definition not 
>recognised and ignored.
>  >Warning 1: ' tables=asmroad1' in MYSQL datasource definition not 
>recognised and ignored.
>  >INFO: Open of `MYSQL:test, host=localhost.localdomain, user=root, 
>password=zxcvydas, tables=asmroad1'
>  >using driver `MySQL' successful.
>
>  >Layer name: asmroad1
>  >Geometry: None
>  >Feature Count: 5
>  >Layer SRS WKT:
>  >(unknown)
>  >road_name: String (0.0)
>  >road_id: Integer (0.0)
>  >roadtype: String (0.0)
>  >OGRFeature(asmroad1):0
>  >road_name (String) = NH51
>  >road_id (Integer) = 4
>  >roadtype (String) = metallic
>
>  >OGRFeature(asmroad1):1
>  >road_name (String) = NH39
>  >road_id (Integer) = 3
>  >roadtype (String) = NonMetallic
>
>  >OGRFeature(asmroad1):2
>  >road_name (String) = NH54
>  >road_id (Integer) = 2
>  >roadtype (String) = Mettalic
>
>  >OGRFeature(asmroad1):3
>   >road_name (String) = NH53
>   >road_id (Integer) = 1
>   >roadtype (String) = Metallic
>
>>OGRFeature(asmroad1):4
>   >road_name (String) = NH42
>   >road_id (Integer) = 5
>   >roadtype (String) = Semimetallic
>
>   All the datas and fields are showing in the output. But after 
>modifying my mapfile when i access the page, it gives an error "The 
>documents contain no data".
>
>
>   Now with documented map file is like this->
>   LAYER
>       NAME "asmroad"
>       TYPE LINE
>       CONNECTIONTYPE ogr
>       CONNECTION "asmroad.shp" /*This is the shape file in my data ditectory*/
>      
>       /* I am trying to connect route_na field in my asmroad.dbf 
>which is in data directory */
>       /* to the field name road_name in asmroad1 table in mysql */
>       /* So i have written my code like this */
>      
>       DATA "SELECT * FROM asmroad1 LEFT JOIN 
>'MYSQL:test,host=localhost, user=root,
>       password=mypass'.road_name ON asmroad1.road_name = route_na.roadjoin1"
>       STATUS ON
>END
>
>  Whether the code above is correct? Maybe a little bit of correction is
>  needed in the DATA part. I have used a unique name for a connection 
>by the name "roadjoin1". You can refer it to my previous mail. But 
>What is it doing here just before the line STATUS ON in my map file. 
>I think since you decipher the text, so u will know its uses here.
>
>Thanks
>Grabiel
>
>  --- On Tue 04/11, William Kyngesburye < woklist at kyngchaos.com > wrote:
>From: William Kyngesburye [mailto: woklist at kyngchaos.com]
>To: mapserver-users at lists.umn.edu
>      Cc: grabiel at MYWAY.COM
>Date: Tue, 11 Apr 2006 09:10:24 -0500
>Subject: Re: Mysql
>
>After digging around the list archives and dealing with software 
>updates and bugs, I finally figured this out yesterday (what 
>timing!).  The main key is that you use GDAL, not MapServer directly 
>or ODBC.What you need then, is GDAL 1.3.1 with MySQL table support. 
>MapServer version shouldn't matter.  There are a couple sticky 
>problems, tho.  One is that GDAL automatically detects a feature ID 
>field in the database and doesn't include it in the field list for 
>the table.  I don't know the particulars for how it decides it a 
>field is an 'FID', but it's something like, integer field as a 
>primary key.  You should be OK with string fields.You can check what 
>OGR sees by duplicating the MySQL connection  string aprt from below 
>(without the .table part, but with tables=)  with ogrinfo on the 
>commandline:ogrinfo -al -so 
>'MYSQL:database,host=whatever,user=whatever,password=whatever,tables=you 
>rtable'Another problem is that GDAL curently doesn't detect tinyints 
>or  smallints
>(thinks they're strings), so if those are used for your  relate 
>field, it may not work.Now for the MAP file stuff:CONNECTIONTYPE 
>ogrCONNECTION "asmroad.shp"DATA "SELECT * FROM asmroad LEFT JOIN 
>'MYSQL:test,host=localhost,user=username,password=userpass'.route_na 
>ON asmroad.road_name = route_na.roadjoin1"Assuming I'm deciphering 
>the connection/join statements right, they  don't seem to be 
>documented yet.  You don't need the JOIN block.Basically, 
>CONNECTIONTYPE will always be OGR.  CONNECTION can be one  of 2 
>things: either the full relative path to the shapefile as it  would 
>be in a DATA statement + .shp, it must include the full file  name, 
>unlike in DATA statements; or if the shapefile is in subfolder  you 
>can list just the subfolder, because of the way OGR handles 
>datasources and layers, but it can't be empty (CONNECTION "").The 
>OGR mysql connection string format (see also the OGR  documentation 
>for the MySQL format) is 
>'MYSQL:database,host=,user=,password=,tables='.table.  Host defaults 
>to localhost, password can be left out if there is none.  Tables 
>(inside the quote) is a semicolon-seprated list of tables to 
>restrict  the search to (can speed things up when there are many 
>tables), and  table (after the dot) is the table you want.The join 
>part, ON shapedbf.joinfield1 = mysqltable.joinfield2 should  be easy 
>to figure out.Be aware that I only tried this so far on joining by 
>integer fields.   Haven't tried string fields yet.On Apr 11, 2006, 
>at 5:08 AM, grabiel wrote:>>>>  Hi>     I am trying to connect Mysql 
>table for non spatial data. But in  > the final output in the 
>browser all the fields from the .DBF files  > are shown, no data 
>from mysql table are added. I also manipulated  > the To and FROM 
>field in the map file but its all the same. If i  > change     the 
>table name in the mapfile the result is same. I  > guess its not 
>working at all.>>      My mapfile is something like>>      
>...................>     LAYER>     NAME "asmroad">     TYPE LINE> 
>STATUS Off>     DATA "asmroad">       CLASS>>	NAME "Road"> 
>TEMPLATE "road.html"		>	COLOR 200 50 100>       END>> 
>HEADER "road_header.html">       FOOTER "road_footer.html">> 
>JOIN>        CONNECTIONTYPE mysql>        CONNECTION 
>"localhost:mysql:zxmnt:test">        TABLE "amroad">        TO 
>"route_na">        NAME "roadjoin1">        FROM "road_name"> 
>END>  END>>   I am using the PHP/mapscript. Whether any DSN should 
>be created  > for these type of data access?>   Hoping to get 
>suggestion from the list members.>   Thanks>>   Grabri>>>> 
>_______________________________________________> No banners. No 
>pop-ups. No kidding.> Make My Way  your home on the Web - 
>http://www.myway.com-----William Kyngesburye 
><kyngchaos at kyngchaos.com>http://www.kyngchaos.com/"Oh, look, I seem 
>to have fallen down a deep, dark hole.  Now what  does that remind 
>me of?  Ah, yes - life."- Marvin
>
>_______________________________________________
>No banners. No pop-ups. No kidding.
>Make My Way  your home on the Web - http://www.myway.com