Thematic map using MySQL

[Frank Warmerdam]

On Wed, 9 Feb 2005 10:57:16 +0000, joÿffffffffffe3o Antunes
<bluetuna_goes_skying at yahoo.co.uk> wrote:
> This example works perfectly with an ODBC for an Access Database. I instaled
> and configured ODBC for my SQL, but still it does not work. I get this
> message: 
>   
> Warning: [MapServer Error]: msDrawMap(): Failed to draw layer named
> 'comuni'. in c:\programmi\apache group\apache\htdocs\abruzzo\_test_odbc.php
> on line 6
> 
> Warning: [MapServer Error]: msOGRFileOpen(): ExecuteSQL(SELECT * FROM
> comuni_abruzzo_gb LEFT JOIN 'ODBC:prova'.zzz ON comuni_abruzzo_gb.Id =
> zzz.ID) failed. Unable to initialize ODBC connection to DSN prova,
> [MySQL][ODBC 3.51 Driver]Access denied for user 'ODBC'@'localhost' (using
> password: NO) in c:\programmi\apache
> group\apache\htdocs\abruzzo\_test_odbc.php on line 6

Joao,

The problem in the OGR based approach is that the ODBC
connection is failing.  The truth is that I don't know that 
much about the internals of ODBC and how system and user
DSNs work.  But basically the OGR code just uses the DSN
name to try and open the ODBC connection and if it isn't
setup properly in advance you will crash and burn - as you
have.

My understanding is that you need to use a System DSN,
not a user dsn and that there may be permissions issues that
apply to the user that cgi's are run under.  

If you are interested in narrow down the issue, try:

 o Using "ogrinfo.exe -ro 'ODBC:prova' -al -so" and see if
    you are able to connect.  This is the first level verification
    of ODBC under OGR and if this works but the cgi use
    does not then you have a permissions error. If this does
    not work then you likely have a DSN issue. 

 o Try "shp2img.exe -m <yourmapfile> -o out.png" to see
    if the layer definition is renderable.  This will test the
    join and other configuration while not suffering
    from permissions issues. 

Note, I have not had much success specifying my userid
and password as part of the DSN.   I have generally had to
fallback on specifying them in the OGR connection string.
Something like 'ODBC:userid/password at prova'.

Good luck,
-- 
---------------------------------------+--------------------------------------
I set the clouds in motion - turn up   | Frank Warmerdam, warmerdam at pobox.com
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | Geospatial Programmer for Rent