Mysql

[grabiel]

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