Fwd: [gdal-dev] MYSQL Spatial Stored Procedure

[Chaitanya kumar CH]

---------- Forwarded message ----------
From: Thomas Joseph <tom.joseph at awtwater.com>
Date: Sat, Jul 10, 2010 at 10:30 AM
Subject: RE: [gdal-dev] MYSQL Spatial Stored Procedure
To: Chaitanya kumar CH <chaitanya.ch at gmail.com>


  Thanks for the rapid response.

The exact command I am using to test this:

ogrinfo.exe MySQL:spatial,host=awtak1s3,user=root,password=pass,port=3306
-sql "CALL sp_test(24);" –so

the stored procedure is very simple at the moment and looks like

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_test` $$

CREATE DEFINER=`root`@`%` PROCEDURE `sp_test`(iworkID int)

BEGIN

select shape from GIS_Nodes_WGS84 where workID = iworkID;

END $$

DELIMITER ;

The table GIS_Nodes_WGS84 is just a simple MyISAM table with a few columns,
OGR_FID as primary key, shape as wkb point geometry column, and workId
integer.

If I run orginfo with
MySQL:spatial,host=awtak1s3,user=root,password=pass,port=3306 -sql "select
shape from GIS_Nodes_WGS84 where workID = 24;" –so

It works as expected and returns info about the spatial data from the select
statement.

I know I could just use select statements but my application is slightly
more complicated and dynamic then the test and it would be good to be able
use a stored procedure so I could pass variables.

I am running windows XP with gdal 1.6.0.3.

I am suppose to post this back to the list ?

Let me know if you require any further information.




 ------------------------------

*From:* Chaitanya kumar CH [mailto:chaitanya.ch at gmail.com]
*Sent:* Saturday, 10 July 2010 3:44 p.m.
*To:* Thomas Joseph
*Subject:* Re: [gdal-dev] MYSQL Spatial Stored Procedure



Thomas,

Can you give the exact command you used and if possible, a description of
the procedure and tables?

On Sat, Jul 10, 2010 at 1:55 AM, Thomas Joseph <tom.joseph at awtwater.com>
wrote:

I am relatively new to gdal.  I have a few spatial tables in MYSQL and I am
currently using ORG as read access to my data for a mapsever layer.
Everything works great with standard select statements however it does not
work when I try to use a stored procedure with the spatial data.  When I use
orginfo I get the following error.



MySQL error message:PROCEDURE spatial.sp_test can't return a result setin
the given context Description: CALL sp_test(24);



Any suggestions would be greatly appreciated ?




















 ------------------------------

THIS EMAIL IS CONFIDENTIAL.  Its use or disclosure by any person other than
the addressee is unauthorised.  Anyone other than the intended recipient
must not rely on the content of this email or any attachment to it.  The
sender cannot guarantee that this email or any attachment to it is free of
computer viruses or other conditions which may damage or interfere with
other computer systems.
 ------------------------------


_______________________________________________
gdal-dev mailing list
gdal-dev at lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/gdal-dev




-- 
Best regards,
Chaitanya kumar CH.
/tʃaɪθənjə/ /kʊmɑr/
+91-9494447584
17.2416N 80.1426E



------------------------------

THIS EMAIL IS CONFIDENTIAL.  Its use or disclosure by any person other than
the addressee is unauthorised.  Anyone other than the intended recipient
must not rely on the content of this email or any attachment to it.  The
sender cannot guarantee that this email or any attachment to it is free of
computer viruses or other conditions which may damage or interfere with
other computer systems.

------------------------------




-- 
Best regards,
Chaitanya kumar CH.
/tʃaɪθənjə/ /kʊmɑr/
+91-9494447584
17.2416N 80.1426E
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/gdal-dev/attachments/20100710/cf1af09f/attachment-0001.html