Fw: PgOleDb / PostGis / PostgreSql interface problem

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">The following is a message is one I originally posted on the pgFoundary list.  At the bottom of the
noteis a reply from </span></font>Shachar Shemesh.  <font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">I am cross posting it here because there seems to be some concern as to where the issue
originates.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">If anyone has any suggestions I would be happy to experiment to find a valid
solution.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">The gist of what I am trying to do is use PostgreSql as a geospatial database server that can be
accessedthough a WEB page, a WEB service, ArcMap or any other application that I am called on to write.  If this is not
theright combination of libraries, please let me know.  If this is not the right forum for this question, plaee let me
knowwhere I should post this.  It seems like it is an interface issue that is why I am posting it here.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Thank you for your help.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Bruce</span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span
style="font-size:
12.0pt"> </span></font><pre><font face="Courier New" size="2"><span style="font-size:10.0pt">Clay, Bruce
wrote:</span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt"> </span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> I am getting the same message that Uwe Seher mentioned
backon 11 May.</span></i></span></font></pre><pre><font face="Courier New" size="2"><span
style="font-size:10.0pt">></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> I am using a combination of PostgreSql 8.0.3, postgis
(version</span></i></span></font></pre><pre><font face="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> provided in the PostgreSql 8.0.3 installer) and what I
believeto be </span></i></span></font></pre><pre><font face="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> the current PgOleDb
dll.</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> I have loaded a few databases in PostgreSql and I can
seethe table </span></i></span></font></pre><pre><font face="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> names in ArcCatalog after I make an OLE DB
connection.</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> Two problems show up but I can not say at which level
ofthe process </span></i></span></font></pre><pre><font face="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> they occur.</span></i></span></font></pre><pre><font
face="CourierNew" size="2"><span style="font-size:10.0pt">></span></font></pre><pre><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt">><i><span style="font-style:italic"> The first is as the title says one that
says:</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> Failed to edit the selected
object(s)</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> General Function
failure</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> ERROR: no binary output function available for type
geometry</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> Fatal error in
query.</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> The standard tables used by template1 all seem to open
okbut the </span></i></span></font></pre><pre><font face="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> table that has the actual data fails with the above
errormessage</span></i></span></font></pre><pre><font face="Courier New" size="2"><span
style="font-size:10.0pt">></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">I'lloffer you a deal. You forward the message I tried to send to the
</span></font></pre><pre><fontface="Courier New" size="2"><span style="font-size:10.0pt">postgis mailing list and
couldn't(I'm not subscribed, and I don't have </span></font></pre><pre><font face="Courier New" size="2"><span
style="font-size:10.0pt">thebandwidth to subscribe to yet another mailing list), and to carry
</span></font></pre><pre><fontface="Courier New" size="2"><span style="font-size:10.0pt">out the discussion on my
behalfthere, and I'll try to solve this one, ok?</span></font></pre><pre><font face="Courier New" size="2"><span
style="font-size:10.0pt"> </span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> The second problem I encountered seems to be one of
casesensitivity. </span></i></span></font></pre><pre><font face="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> If a table has uppercase letters such as VE_SubProv I
getthe </span></i></span></font></pre><pre><font face="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> following error
message</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> Failed to edit the selected
object(s)</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> General Function
failure</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> ERROR: relation “public.ve_subprov” does not
exist.</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> Fatal error on
query</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">Whatusage type? What commands were you using?</span></font></pre><pre><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt"> </span></font></pre><pre><font face="Courier New" size="2"><span
style="font-size:10.0pt">Pleaseforward the following email to the postgis list.</span></font></pre><pre><font
face="CourierNew" size="2"><span style="font-size:10.0pt"> </span></font></pre><pre><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt">><i><span style="font-style:italic"> Hi
all,</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> Please include me in the CC of all replies, as I'm not
subscribedto </span></i></span></font></pre><pre><font face="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> the list.</span></i></span></font></pre><pre><font
face="CourierNew" size="2"><span style="font-size:10.0pt">></span></font></pre><pre><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt">><i><span style="font-style:italic"> I am the maintainer of PgOleDb (<a
href="http://pgfoundry.org/projects/oledb">http://pgfoundry.org/projects/oledb</a>),
</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span style="font-size:10.0pt">><i><span
style="font-style:italic">the OLE DB provider for PostgreSQL. This driver is beginning to
</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span style="font-size:10.0pt">><i><span
style="font-style:italic">mature, and we are starting to get questions on the PgOleDb list about
</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span style="font-size:10.0pt">><i><span
style="font-style:italic">PostGIS support. As I know nothing about it, I'm asking
here.</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> Personally, I don't really care one way or the other,
soif anyone </span></i></span></font></pre><pre><font face="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> here wants PostGIS support in PgOleDb, you will have to
doat least </span></i></span></font></pre><pre><font face="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> some of the work. Most important is giving a concise
listof all data </span></i></span></font></pre><pre><font face="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> types that PostGIS adds to PG, and how Windows program
typically</span></i></span></font></pre><pre><font face="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> expect to receive them (when expecting binary returns).
Ifa </span></i></span></font></pre><pre><font face="Courier New" size="2"><span style="font-size:10.0pt">><i><span
style="font-style:italic">translation is required between the way PG usually exports it, and the
</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span style="font-size:10.0pt">><i><span
style="font-style:italic">way OLE DB is expected to return it, small code doing the actual
</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span style="font-size:10.0pt">><i><span
style="font-style:italic">translation would be GREATLY appreciated (PgOleDb is under the LGPL,
</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span style="font-size:10.0pt">><i><span
style="font-style:italic">so whatever code you provide must be under a LGPL compatible
license).</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> More worrying, I have a question on the list
</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span style="font-size:10.0pt">><i><span
style="font-style:italic">(<a
href="http://lists.pgfoundry.org/pipermail/oledb-devel/2005-May/000301.html">http://lists.pgfoundry.org/pipermail/oledb-devel/2005-May/000301.html</a>)
</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span style="font-size:10.0pt">><i><span
style="font-style:italic">which suggest that some of the GIS types don't have binary exports
</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span style="font-size:10.0pt">><i><span
style="font-style:italic">functions. This is a bug in the type, and PgOleDb cannot be expected
</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span style="font-size:10.0pt">><i><span
style="font-style:italic">to solve it. Like I said above, I am not a GIS user myself, and I
</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span style="font-size:10.0pt">><i><span
style="font-style:italic">therefor don't know anything about it. I tried to find the SQL
</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span style="font-size:10.0pt">><i><span
style="font-style:italic">instructions for installing support for the GIS type, and couldn't
</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span style="font-size:10.0pt">><i><span
style="font-style:italic">even locate them (I seem to need to grab lwpostgis.sql from somewhere,
</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span style="font-size:10.0pt">><i><span
style="font-style:italic">but it doesn't seem to be in neither PostGIS nor PostgreSQL source
</span></i></span></font></pre><pre><fontface="Courier New" size="2"><span style="font-size:10.0pt">><i><span
style="font-style:italic">distributions).</span></i></span></font></pre><pre><font face="Courier New" size="2"><span
style="font-size:10.0pt">></span></font></pre><pre><fontface="Courier New" size="2"><span
style="font-size:10.0pt">><i><spanstyle="font-style:italic"> Shachar</span></i></span></font></pre><pre><font
face="CourierNew" size="2"><span style="font-size:10.0pt"> </span></font></pre><pre><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt"> </span></font></pre><pre><font face="Courier New" size="2"><span
style="font-size:10.0pt">--</span></font></pre><pre><font face="Courier New" size="2"><span
style="font-size:10.0pt">ShacharShemesh</span></font></pre><p class="MsoNormal"><font face="Times New Roman"
size="3"><spanstyle="font-size: 
12.0pt">Lingnu Open Source Consulting ltd</span></font></div>