<br><br><div class="gmail_quote">---------- Forwarded message ----------<br>From: <b class="gmail_sendername">Thomas Joseph</b> <span dir="ltr"><<a href="mailto:tom.joseph@awtwater.com">tom.joseph@awtwater.com</a>></span><br>
Date: Sat, Jul 10, 2010 at 10:30 AM<br>Subject: RE: [gdal-dev] MYSQL Spatial Stored Procedure<br>To: Chaitanya kumar CH <<a href="http://chaitanya.ch">chaitanya.ch</a>@<a href="http://gmail.com">gmail.com</a>><br><br>
<br>
<div link="blue" vlink="blue" lang="EN-US">
<div>
<div>
<div>
<p class="MsoNormal"><font color="navy" face="Times New Roman" size="3"><span style="font-size: 12pt; color: navy;">Thanks for the
rapid response. </span></font></p>
<p class="MsoNormal"><font color="navy" face="Times New Roman" size="3"><span style="font-size: 12pt; color: navy;">The exact
command I am using to test this:</span></font></p>
<p class="MsoNormal"><font color="navy" face="Times New Roman" size="3"><span style="font-size: 12pt; color: navy;">ogrinfo.exe
MySQL:spatial,host=awtak1s3,user=root,password=pass,port=3306 -sql "CALL
sp_test(24);" –so</span></font></p>
<p class="MsoNormal"><font color="navy" face="Times New Roman" size="3"><span style="font-size: 12pt; color: navy;">the stored
procedure is very simple at the moment and looks like</span></font></p>
<p class="MsoNormal"><font color="navy" face="Times New Roman" size="3"><span style="font-size: 12pt; color: navy;">DELIMITER $$</span></font></p>
<p class="MsoNormal"><font color="navy" face="Times New Roman" size="3"><span style="font-size: 12pt; color: navy;">DROP PROCEDURE
IF EXISTS `sp_test` $$</span></font></p>
<p class="MsoNormal"><font color="navy" face="Times New Roman" size="3"><span style="font-size: 12pt; color: navy;">CREATE
DEFINER=`root`@`%` PROCEDURE `sp_test`(iworkID int)</span></font></p>
<p class="MsoNormal"><font color="navy" face="Times New Roman" size="3"><span style="font-size: 12pt; color: navy;">BEGIN</span></font></p>
<p class="MsoNormal"><font color="navy" face="Times New Roman" size="3"><span style="font-size: 12pt; color: navy;">select shape
from GIS_Nodes_WGS84 where workID = iworkID;</span></font></p>
<p class="MsoNormal"><font color="navy" face="Times New Roman" size="3"><span style="font-size: 12pt; color: navy;">END $$</span></font></p>
<p class="MsoNormal"><font color="navy" face="Times New Roman" size="3"><span style="font-size: 12pt; color: navy;">DELIMITER ;</span></font></p>
<p class="MsoNormal"><font color="navy" face="Times New Roman" size="3"><span style="font-size: 12pt; color: navy;">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.</span></font></p>
<p class="MsoNormal"><font color="navy" face="Times New Roman" size="3"><span style="font-size: 12pt; color: navy;">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</span></font></p>
<p class="MsoNormal"><font color="navy" face="Times New Roman" size="3"><span style="font-size: 12pt; color: navy;">It works as
expected and returns info about the spatial data from the select statement.</span></font></p>
<p class="MsoNormal"><font color="navy" face="Times New Roman" size="3"><span style="font-size: 12pt; color: navy;">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.</span></font></p>
<p class="MsoNormal"><font color="navy" face="Times New Roman" size="3"><span style="font-size: 12pt; color: navy;">I am running
windows XP with gdal 1.6.0.3.</span></font></p>
</div>
</div>
<p class="MsoNormal"><font color="navy" face="Times New Roman" size="3"><span style="font-size: 12pt; color: navy;">I am suppose
to post this back to the list ?</span></font></p>
<p class="MsoNormal"><font color="navy" face="Times New Roman" size="3"><span style="font-size: 12pt; color: navy;">Let me know if
you require any further information.</span></font></p>
<p class="MsoNormal"><font color="navy" face="Times New Roman" size="3"><span style="font-size: 12pt; color: navy;"> </span></font></p>
<p class="MsoNormal"><font color="navy" face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial; color: navy;"> </span></font></p>
<div>
<div class="MsoNormal" style="text-align: center;" align="center"><font face="Times New Roman" size="3"><span style="font-size: 12pt;">
<hr align="center" size="2" width="100%">
</span></font></div>
<p class="MsoNormal"><b><font face="Tahoma" size="2"><span style="font-size: 10pt; font-family: Tahoma; font-weight: bold;">From:</span></font></b><font face="Tahoma" size="2"><span style="font-size: 10pt; font-family: Tahoma;"> Chaitanya kumar
CH [mailto:<a href="mailto:chaitanya.ch@gmail.com" target="_blank">chaitanya.ch@gmail.com</a>] <br>
<b><span style="font-weight: bold;">Sent:</span></b> Saturday, 10 July 2010 3:44
p.m.<br>
<b><span style="font-weight: bold;">To:</span></b> Thomas
Joseph<br>
<b><span style="font-weight: bold;">Subject:</span></b> Re: [gdal-dev] MYSQL
Spatial Stored Procedure</span></font></p>
</div><div class="im">
<p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size: 12pt;"> </span></font></p>
<p class="MsoNormal" style="margin-bottom: 12pt;"><font face="Times New Roman" size="3"><span style="font-size: 12pt;">Thomas,<br>
<br>
Can you give the exact command you used and if possible, a description of the
procedure and tables?</span></font></p>
<div>
<p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size: 12pt;">On Sat, Jul 10, 2010 at 1:55 AM, Thomas
Joseph <<a href="mailto:tom.joseph@awtwater.com" target="_blank">tom.joseph@awtwater.com</a>>
wrote:</span></font></p>
<div link="blue" vlink="purple">
<div>
<p class="MsoNormal"><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">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.</span></font></p>
<p class="MsoNormal"><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> </span></font></p>
<p class="MsoNormal"><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">MySQL error
message:PROCEDURE spatial.sp_test can't return a result setin the given context
Description: CALL sp_test(24);</span></font></p>
<p class="MsoNormal"><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;"> </span></font></p>
<p class="MsoNormal"><font face="Arial" size="2"><span style="font-size: 10pt; font-family: Arial;">Any
suggestions would be greatly appreciated ?</span></font></p>
<div>
<p class="MsoNormal"><font color="#1f497d" face="Times New Roman" size="3"><span style="font-size: 12pt; color: rgb(31, 73, 125);"> </span></font></p>
<p class="MsoNormal"><font color="#1f497d" face="Times New Roman" size="3"><span style="font-size: 12pt; color: rgb(31, 73, 125);"> </span></font></p>
<p class="MsoNormal"><font color="#1f497d" face="Times New Roman" size="3"><span style="font-size: 12pt; color: rgb(31, 73, 125);"> </span></font></p>
<p class="MsoNormal"><font color="#1f497d" face="Times New Roman" size="3"><span style="font-size: 12pt; color: rgb(31, 73, 125);"> </span></font></p>
<p class="MsoNormal"><font color="#1f497d" face="Times New Roman" size="3"><span style="font-size: 12pt; color: rgb(31, 73, 125);"> </span></font></p>
<p class="MsoNormal"><font color="#1f497d" face="Times New Roman" size="3"><span style="font-size: 12pt; color: rgb(31, 73, 125);"> </span></font></p>
<p class="MsoNormal"><font color="#1f497d" face="Times New Roman" size="3"><span style="font-size: 12pt; color: rgb(31, 73, 125);"> </span></font></p>
<p class="MsoNormal"><font color="#1f497d" face="Times New Roman" size="3"><span style="font-size: 12pt; color: rgb(31, 73, 125);"> </span></font></p>
</div>
<p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size: 12pt;"> </span></font></p>
</div>
<p><font face="Times New Roman" size="3"><span style="font-size: 12pt;"> </span></font></p>
<div class="MsoNormal" style="text-align: center;" align="center"><font face="Times New Roman" size="3"><span style="font-size: 12pt;">
<hr align="center" size="2" width="100%">
</span></font></div>
<p><font face="Times New Roman" size="3"><span style="font-size: 12pt;">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.</span></font></p>
<div class="MsoNormal" style="text-align: center;" align="center"><font face="Times New Roman" size="3"><span style="font-size: 12pt;">
<hr align="center" size="2" width="100%">
</span></font></div>
</div>
<p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size: 12pt;"><br>
_______________________________________________<br>
gdal-dev mailing list<br>
<a href="mailto:gdal-dev@lists.osgeo.org" target="_blank">gdal-dev@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/gdal-dev" target="_blank">http://lists.osgeo.org/mailman/listinfo/gdal-dev</a></span></font></p>
</div>
<p class="MsoNormal"><font face="Times New Roman" size="3"><span style="font-size: 12pt;"><br>
<br clear="all">
<br>
-- <br>
Best regards,<br>
Chaitanya kumar CH.<br>
/t</span></font><font face="Lucida Sans Unicode"><span style="font-family: "Lucida Sans Unicode";">ʃ</span></font>a<font face="Lucida Sans Unicode"><span style="font-family: "Lucida Sans Unicode";">ɪ</span></font>θənjə/
/k<font face="Lucida Sans Unicode"><span style="font-family: "Lucida Sans Unicode";">ʊ</span></font>m<font face="Lucida Sans Unicode"><span style="font-family: "Lucida Sans Unicode";">ɑ</span></font>r/
<br>
+91-9494447584<br>
17.2416N 80.1426E</p>
</div></div><div class="im">
<p align="left"> </p>
<p align="left">
</p><hr>
<p></p>
<p align="left">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.</p>
<p align="left">
</p><hr>
<p></p></div></div>
</div><br><br clear="all"><br>-- <br>Best regards,<br>Chaitanya kumar CH.<br>/tʃaɪθənjə/ /kʊmɑr/ <br>+91-9494447584<br>17.2416N 80.1426E<br>