PostgreSQL and SOAP, version 7.4/8.0

First, a SOAP query should be posted in SOAP message format, not using the
query string as you do. Second, I like the idea of calling external SOAP
services, but consider creating a language 'soap' you could do with a CREATE
FUNCTION type thing. e.g.

CREATE FUNCTION "foo" (TEXT) RETURNS INTEGER AS
'http://somewhere.com/path/to/.wsdl', 'foo'
LANGUAGE 'soap';

(hmm, it is unclear if this is what you are suggesting or not...)

Second, I hate SOAP because it is too bloated (have you read the spec(s)?).
If you can support xmlrpc instead, you'll save yourself a lot of headaches.
If you got SOAP working, though, I'd use it. It's more an implementation
thing.

On Fri, Mar 28, 2003 at 09:01:08AM -0500, mlw wrote:

I have been working on moving some of my software to a more SOAP
compatible interface. As I was doing it, it occured to me that a generic
function could be written, in PostgreSQL's new function manager that
allows multiple columns to be returned, that is a generic SOAP interface.

All one would need do is define what is expected from the SOAP call in
the "CREATE FUNCTION" statement. Then the generic SOAP function could
then read what is expected and return the XML/SOAP data as a set of
results as if it were a subquery.

What is needed is an efficient way to find the data types and names from
the functions definition. Does anyone know how to do that?

A small program could also parse a WSDL file and write a "CREATE
FUNCTION" script for the XML as well.

On the flip side, I am also working on a PostgreSQL SOAP interface,
where one does this:

http://somehost/postgresql?query="select * from table"

And a SOAP compatible resultset is returned.

On a more advanced horizon, one should be able to do this:

select * from localtable,
mysoap('http://remotehost/postgresql?query=select * from foo') as soap
where soap.field = localtable.field;

If we can do that, PostgreSQL could fit into almost ANY service
environment. What do you guys think? Anyone want to help out?

I have no time to volunteer for projects, but what the hell...! It's too
cool. I can't spend much time on it but bounce things off me and I'll
do whatever hacking I can squeeze in. What soap implementation would you
use for the PostgreSQL plugin? libsoap, last I checked, is a wee bit
out of date. And not documented.

-Jason

Jason wrote:

If you can support xmlrpc instead, you'll save yourself a lot of headaches.

XML-RPC has three merits over SOAP:

1. It's a simple specification, and thus readily implemented.

2. Microsoft and IBM aren't fighting over control over it, so it's
not suffering from the "we keep adding pseudo-standards to it"
problem. (Which further complicates the specifications.)
You can have a /complete/ implementation of XML-RPC, whereas,
for SOAP, you can hold ghastly long arguments as to what SOAP
means, anyways.

3. There's a (perhaps not "standard", but definitely widely
implemented) scheme for bundling multiple XML-RPC requests into
one message, which improves latency a LOT for small messages.

Of course, CORBA has actually been quite formally standardized, suffers
from many fairly interoperable implementations, and is rather a lot less
bloated than any of the XML-based schemes. It might be worth trying,
too...
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne&gt; rate me
http://cbbrowne.com/info/soap.html
I just got skylights put in my place. The people who live above me are
furious.

On Fri, Mar 28, 2003 at 01:36:43PM -0500, cbbrowne@cbbrowne.com wrote:

Of course, CORBA has actually been quite formally standardized, suffers
from many fairly interoperable implementations, and is rather a lot less
bloated than any of the XML-based schemes. It might be worth trying,
too...

The ability to use the HTTP transport has it's advantages with web services--
You can throw something together with a few lines of PHP, you don't have to
worry about how to activate objects, I've never been able to figure out how
to handle transport-layer security and authentication with CORBA (of course,
this was all fairly new stuff when I was working with it), all this stuff
comes for free with the HTTP transport.

I like CORBA, though, and I'd probably find a CORBA module useful, but it
doesn't solve all the same problems.

Hrm, I wonder if the overhead of XML-RPC wouldn't be too bad for the new
PostgreSQL protocol... it probably would, but it would be entirely useful.
You can make XML-RPC calls from mozilla javascript, so you could do some
pretty sweet tweaking to keep your addresses in a pgsql database.

As an "additional" protocol which postmaster can listen to it would rule.
I'm making a habit of putting all the business logic into stored procedures,
and this would basically publish the business logic in a very useful way.

-Jason

Jason M. Felice wrote:

First, a SOAP query should be posted in SOAP message format, not using the
query string as you do. Second, I like the idea of calling external SOAP
services, but consider creating a language 'soap' you could do with a CREATE
FUNCTION type thing. e.g.

CREATE FUNCTION "foo" (TEXT) RETURNS INTEGER AS
'http://somewhere.com/path/to/.wsdl&#39;, 'foo'
LANGUAGE 'soap';

(hmm, it is unclear if this is what you are suggesting or not...)

Second, I hate SOAP because it is too bloated (have you read the spec(s)?).
If you can support xmlrpc instead, you'll save yourself a lot of headaches.
If you got SOAP working, though, I'd use it. It's more an implementation
thing.

Here's the thing, yes I know there are a "lot" of alternatives to SOAP,
all with varying levels of "being better than SOAP." It still stands
that a SOAP interface would be useful for people.

On Fri, Mar 28, 2003 at 09:01:08AM -0500, mlw wrote:

I have been working on moving some of my software to a more SOAP
compatible interface. As I was doing it, it occured to me that a generic
function could be written, in PostgreSQL's new function manager that
allows multiple columns to be returned, that is a generic SOAP interface.

All one would need do is define what is expected from the SOAP call in
the "CREATE FUNCTION" statement. Then the generic SOAP function could
then read what is expected and return the XML/SOAP data as a set of
results as if it were a subquery.

What is needed is an efficient way to find the data types and names from
the functions definition. Does anyone know how to do that?

A small program could also parse a WSDL file and write a "CREATE
FUNCTION" script for the XML as well.

On the flip side, I am also working on a PostgreSQL SOAP interface,
where one does this:

http://somehost/postgresql?query=&quot;select * from table"

And a SOAP compatible resultset is returned.

On a more advanced horizon, one should be able to do this:

select * from localtable,
mysoap('http://remotehost/postgresql?query=select * from foo') as soap
where soap.field = localtable.field;

If we can do that, PostgreSQL could fit into almost ANY service
environment. What do you guys think? Anyone want to help out?

I have no time to volunteer for projects, but what the hell...! It's too
cool. I can't spend much time on it but bounce things off me and I'll
do whatever hacking I can squeeze in. What soap implementation would you
use for the PostgreSQL plugin? libsoap, last I checked, is a wee bit
out of date. And not documented.

I was thinking of using SOAP over HTTP as the protocol, and a minimalist
version at best. If the people want "more" let them add it.

I have an HTTP service class in my open source library. It would br
trivial to accept a SQL query formatted as a GET request, and then
execute the query and, using libpq, format the result as XML. It should
be simple enough to do.

On Fri, 2003-03-28 at 14:39, mlw wrote:

I was thinking of using SOAP over HTTP as the protocol, and a
minimalist version at best. If the people want "more" let them add it.

I have an HTTP service class in my open source library. It would br
trivial to accept a SQL query formatted as a GET request, and then
execute the query and, using libpq, format the result as XML. It
should be simple enough to do.

It would be easy. I've done something similar (using ODBC to
get to PostgreSQL) - but using a language none of the rest of
you are likely to be interested in (Unicon). Works just fine,
though the implementation (deliberately, by personal preference)
avoids accepting arbitrary SQL statements from SOAP clients,
instead forcing the clients to use an RPC interface so I can
do sanity checking in the Unicon [which I know better than I know
PostgreSQL...] SOAP servers.

I, too, opted for a 'minimal-SOAP' implementation. A 'real'
implementation boggles the mind.

--
Steve Wampler <swampler@noao.edu>
National Solar Observatory

mlw writes:

On the flip side, I am also working on a PostgreSQL SOAP interface,
where one does this:

http://somehost/postgresql?query=&quot;select * from table"

And a SOAP compatible resultset is returned.

That looks quite similar to the planned XML functionality. While that
plan doesn't contain the word "SOAP", one could of course create a small
layer to convert the output format to any other XML format. If you're
interested, please look up the XML discussion of the last few weeks.

--
Peter Eisentraut peter_e@gmx.net

Given a HTTP formatted query:
GET "http://localhost:8181/pgmuze?query=select+*+from+zsong+limit+2&quot;

The output is entered below.

Questions:
Is there a way, without spcifying a binary cursor, to get the data types
associated with columns? Right now I am just using undefined, as the
ODBC version works.

Anyone see any basic improvements needed?

<?xml version = "1.0"?>
<soap:Envelope xmlns:MWSSQL="http://www.mohawksoft.com/MWSSQL/envelope&quot;&gt;
<soap:Header>
<!-- Fields in set -->
<Columns count="9">
<muzenbr>undefined</muzenbr>
<disc>undefined</disc>
<trk>undefined</trk>
<song>undefined</song>
<artistid>undefined</artistid>
<acd>undefined</acd>
<trackid>undefined</trackid>
<datasrc>undefined</datasrc>
<extid>undefined</extid>
</Columns>
</soap:Header>
<soap:Body>
<ROWSET columns="9" rows="2">
<ROW ROWID="0">
<muzenbr>424965</muzenbr>
<disc>1</disc>
<trk>5</trk>
<song>Write My Name In The Groove</song>
<artistid>100021391</artistid>
<acd>A</acd>
<trackid>203429573</trackid>
<datasrc>1</datasrc>
<extid>203429573</extid>
</ROW>
<ROW ROWID="1">
<muzenbr>177516</muzenbr>
<disc>1</disc>
<trk>1</trk>
<song>Papa Was A Rolling Stone</song>
<artistid>100000411</artistid>
<acd>P</acd>
<trackid>200000000</trackid>
<datasrc>1</datasrc>
<extid>200000000</extid>
</ROW>
</ROWSET>
</soap:Body>
</soap:Envelope>

Steve Wampler wrote:

mlw kirjutas E, 31.03.2003 kell 03:43:

Given a HTTP formatted query:
GET "http://localhost:8181/pgmuze?query=select+*+from+zsong+limit+2&quot;

The output is entered below.

Questions:
Is there a way, without spcifying a binary cursor, to get the data types
associated with columns? Right now I am just using undefined, as the
ODBC version works.

Anyone see any basic improvements needed?

<?xml version = "1.0"?>
<soap:Envelope xmlns:MWSSQL="http://www.mohawksoft.com/MWSSQL/envelope&quot;&gt;
<soap:Header>
<!-- Fields in set -->
<Columns count="9">

The SOAP 1.1 spec specifies (p4.2) the following about SOAP Header:

The encoding rules for header entries are as follows:

1. A header entry is identified by its fully qualified element
name, which consists of the namespace URI and the local name.
All immediate child elements of the SOAP Header element MUST be
namespace-qualified.

I'm not sure that SOAP Header is the right place for Query header info,
as the header is meant for:

SOAP provides a flexible mechanism for extending a message in a
decentralized and modular way without prior knowledge between the
communicating parties. Typical examples of extensions that can be
implemented as header entries are authentication, transaction
management, payment etc.

So the definition of structure should probably be inside SOAP:Body .

---------------
Hannu

Actually, as far as I am aware, the header is for metadata, i.e. it is the
place to describe the data being returned. The description of the fields
isn't the actual data retrieved, so it doesn't belong in the body, so it
should go into the header.

pgsql@mohawksoft.com kirjutas E, 31.03.2003 kell 19:52:

Actually, as far as I am aware, the header is for metadata, i.e. it is the
place to describe the data being returned.

Did you read the SOAP spec ?

The description of the fields
isn't the actual data retrieved, so it doesn't belong in the body, so it
should go into the header.

That is logical, but this is not what the spec tells.

Also the spec requires immediate child elements of SOAP:Header to have
full namespace URI's.

And another question - why do you have the namespace MWSSQL defined but
never used ?

-------------
Hannu

Hannu Krosing wrote:

pgsql@mohawksoft.com kirjutas E, 31.03.2003 kell 19:52:

Actually, as far as I am aware, the header is for metadata, i.e. it is the
place to describe the data being returned.

Did you read the SOAP spec ?

yes

The description of the fields
isn't the actual data retrieved, so it doesn't belong in the body, so it
should go into the header.

That is logical, but this is not what the spec tells.

This is exactly what the spec calles for. The spec, at least 1.1, says
very little about what should not be in the header. For an XML request,
it should carry. It is very particular about soap header attributes, but
header contents is very flexable.

Also the spec requires immediate child elements of SOAP:Header to have
full namespace URI's.

Yup, that was a bug.

And another question - why do you have the namespace MWSSQL defined but
never used ?

That was part of the same bug as above, it now outputs this:

<?xml version = "1.0"?>
<mwssql:Envelope xmlns:mwssql="http://www.mohawksoft.com/mwssql/envelope&quot;&gt;
<mwssql:Header>
<exec:sql>update cgrpairs set ratio=0 where srcitem=100098670</exec:sql>
<exec:affected>2657</exec:affected>
<qry:sql>select * from ztitles limit 2</qry:sql>
<qry:ROWSET>
<qry:ROW columns="28">
<t:acd>undefined</t:acd>
<t:muzenbr>undefined</t:muzenbr>
<t:cat2>undefined</t:cat2>
<t:cat3>undefined</t:cat3>
<t:cat4>undefined</t:cat4>
<t:performer>undefined</t:performer>
<t:performer2>undefined</t:performer2>
<t:title>undefined</t:title>
<t:artist1>undefined</t:artist1>
<t:engineer>undefined</t:engineer>
<t:producer>undefined</t:producer>
<t:labelname>undefined</t:labelname>
<t:catalog>undefined</t:catalog>
<t:distribut>undefined</t:distribut>
<t:released>undefined</t:released>
<t:origrel>undefined</t:origrel>
<t:nbrdiscs>undefined</t:nbrdiscs>
<t:spar>undefined</t:spar>
<t:minutes>undefined</t:minutes>
<t:seconds>undefined</t:seconds>
<t:monostereo>undefined</t:monostereo>
<t:studiolive>undefined</t:studiolive>
<t:available>undefined</t:available>
<t:previews>undefined</t:previews>
<t:pnotes>undefined</t:pnotes>
<t:artistid>undefined</t:artistid>
<t:datasrc>undefined</t:datasrc>
<t:extid>undefined</t:extid>
</qry:ROW>
</qry:ROWSET>
</mwssql:Header>
<mwssql:Body>
<ROWSET columns="28" rows="2">
<ROW ROWID="0">
<acd>P</acd>
<muzenbr>68291</muzenbr>
<cat2>Performer</cat2>
<cat3>Jazz Instrument</cat3>
<cat4>Guitar</cat4>
<performer>Steve Khan</performer>
<performer2>Khan, Steve</performer2>
<title>Evidence</title>
<artist1></artist1>
<engineer></engineer>
<producer></producer>
<labelname>Novus</labelname>
<catalog>3074</catalog>
<distribut>BMG</distribut>
<released>02/13/1990</released>
<origrel>n/a</origrel>
<nbrdiscs>1</nbrdiscs>
<spar>n/a</spar>
<minutes></minutes>
<seconds></seconds>
<monostereo>Stereo</monostereo>
<studiolive>Studio</studiolive>
<available>N</available>
<previews></previews>
<pnotes></pnotes>
<artistid>100025343</artistid>
<datasrc>1</datasrc>
<extid>68291</extid>
</ROW>
<ROW ROWID="1">
<acd>P</acd>
<muzenbr>67655</muzenbr>
<cat2>Collection</cat2>
<cat3>Jazz Instrument</cat3>
<cat4></cat4>
<performer>Various Artists</performer>
<performer2>Various Artists</performer2>
<title>Metropolitan Opera House Jam Session</title>
<artist1></artist1>
<engineer></engineer>
<producer></producer>
<labelname>Jazz Anthology</labelname>
<catalog>550212</catalog>
<distribut>n/a</distribut>
<released>1992</released>
<origrel>n/a</origrel>
<nbrdiscs>1</nbrdiscs>
<spar>n/a</spar>
<minutes></minutes>
<seconds></seconds>
<monostereo>Mono</monostereo>
<studiolive>Live</studiolive>
<available>N</available>
<previews></previews>
<pnotes></pnotes>
<artistid>100050450</artistid>
<datasrc>1</datasrc>
<extid>67655</extid>
</ROW>
</ROWSET>
</mwssql:Body>
</mwssql:Envelope>

Out of curiousity, what is the purpose of putting the qry:ROWSET
description into the message at all (header or not)? Isn't it a
perfectly valid SOAP message (and just as parseable) with that removed?

I freely admit to not being a soap expert, but similar SOAP
messages I generate from queries seem to work fine without this
metadata. Is having it required by some part of the SOAP spec
I don't understand?

Thanks!

On Tue, 2003-04-01 at 05:29, mlw wrote:

That was part of the same bug as above, it now outputs this:

<?xml version = "1.0"?>
<mwssql:Envelope xmlns:mwssql="http://www.mohawksoft.com/mwssql/envelope&quot;&gt;
<mwssql:Header>
<exec:sql>update cgrpairs set ratio=0 where srcitem=100098670</exec:sql>
<exec:affected>2657</exec:affected>
<qry:sql>select * from ztitles limit 2</qry:sql>
<qry:ROWSET>
<qry:ROW columns="28">
<t:acd>undefined</t:acd>
<t:muzenbr>undefined</t:muzenbr>
<t:cat2>undefined</t:cat2>
<t:cat3>undefined</t:cat3>
<t:cat4>undefined</t:cat4>
<t:performer>undefined</t:performer>
<t:performer2>undefined</t:performer2>
<t:title>undefined</t:title>
<t:artist1>undefined</t:artist1>
<t:engineer>undefined</t:engineer>
<t:producer>undefined</t:producer>
<t:labelname>undefined</t:labelname>
<t:catalog>undefined</t:catalog>
<t:distribut>undefined</t:distribut>
<t:released>undefined</t:released>
<t:origrel>undefined</t:origrel>
<t:nbrdiscs>undefined</t:nbrdiscs>
<t:spar>undefined</t:spar>
<t:minutes>undefined</t:minutes>
<t:seconds>undefined</t:seconds>
<t:monostereo>undefined</t:monostereo>
<t:studiolive>undefined</t:studiolive>
<t:available>undefined</t:available>
<t:previews>undefined</t:previews>
<t:pnotes>undefined</t:pnotes>
<t:artistid>undefined</t:artistid>
<t:datasrc>undefined</t:datasrc>
<t:extid>undefined</t:extid>
</qry:ROW>
</qry:ROWSET>
</mwssql:Header>
<mwssql:Body>
<ROWSET columns="28" rows="2">
<ROW ROWID="0">
<acd>P</acd>
<muzenbr>68291</muzenbr>
<cat2>Performer</cat2>
<cat3>Jazz Instrument</cat3>
<cat4>Guitar</cat4>
<performer>Steve Khan</performer>
<performer2>Khan, Steve</performer2>
<title>Evidence</title>
<artist1></artist1>
<engineer></engineer>
<producer></producer>
<labelname>Novus</labelname>
<catalog>3074</catalog>
<distribut>BMG</distribut>
<released>02/13/1990</released>
<origrel>n/a</origrel>
<nbrdiscs>1</nbrdiscs>
<spar>n/a</spar>
<minutes></minutes>
<seconds></seconds>
<monostereo>Stereo</monostereo>
<studiolive>Studio</studiolive>
<available>N</available>
<previews></previews>
<pnotes></pnotes>
<artistid>100025343</artistid>
<datasrc>1</datasrc>
<extid>68291</extid>
</ROW>
<ROW ROWID="1">
<acd>P</acd>
<muzenbr>67655</muzenbr>
<cat2>Collection</cat2>
<cat3>Jazz Instrument</cat3>
<cat4></cat4>
<performer>Various Artists</performer>
<performer2>Various Artists</performer2>
<title>Metropolitan Opera House Jam Session</title>
<artist1></artist1>
<engineer></engineer>
<producer></producer>
<labelname>Jazz Anthology</labelname>
<catalog>550212</catalog>
<distribut>n/a</distribut>
<released>1992</released>
<origrel>n/a</origrel>
<nbrdiscs>1</nbrdiscs>
<spar>n/a</spar>
<minutes></minutes>
<seconds></seconds>
<monostereo>Mono</monostereo>
<studiolive>Live</studiolive>
<available>N</available>
<previews></previews>
<pnotes></pnotes>
<artistid>100050450</artistid>
<datasrc>1</datasrc>
<extid>67655</extid>
</ROW>
</ROWSET>
</mwssql:Body>
</mwssql:Envelope>

--
Steve Wampler <swampler@noao.edu>
National Solar Observatory

I can certainly imagine cases for processing where having the field names
and other metadata up front (maybe add type info, nullable, etc instead of
just "undefined") would be useful.

here's another question:

If the intention is to use field names as (local) tag names, how will you
handle the case where the field name isn't a valid XML name? Of course, one
could do some sort of mapping (replace illegal chars with "_", for example)
but then you can't be 100% certain that you haven't generated a collision,
I should think.

andrew

----- Original Message -----
From: "Steve Wampler" <swampler@noao.edu>
To: "mlw" <pgsql@mohawksoft.com>
Cc: "Hannu Krosing" <hannu@tm.ee>; <jfelice@cronosys.com>;
"Postgres-hackers" <pgsql-hackers@postgresql.org>
Sent: Tuesday, April 01, 2003 12:40 PM
Subject: Re: [HACKERS] PostgreSQL and SOAP, suggestions?

Out of curiousity, what is the purpose of putting the qry:ROWSET
description into the message at all (header or not)? Isn't it a
perfectly valid SOAP message (and just as parseable) with that removed?

I freely admit to not being a soap expert, but similar SOAP
messages I generate from queries seem to work fine without this
metadata. Is having it required by some part of the SOAP spec
I don't understand?

Thanks!

On Tue, 2003-04-01 at 05:29, mlw wrote:

That was part of the same bug as above, it now outputs this:

<?xml version = "1.0"?>
<mwssql:Envelope

xmlns:mwssql="http://www.mohawksoft.com/mwssql/envelope&quot;&gt;

<mwssql:Header>
<exec:sql>update cgrpairs set ratio=0 where

srcitem=100098670</exec:sql>

I can certainly imagine cases for processing where having the field
names and other metadata up front (maybe add type info, nullable, etc
instead of just "undefined") would be useful.

here's another question:

If the intention is to use field names as (local) tag names, how will
you handle the case where the field name isn't a valid XML name? Of
course, one could do some sort of mapping (replace illegal chars with
"_", for example) but then you can't be 100% certain that you haven't
generated a collision, I should think.

I'm not sure, I have to really research how to handle that case. I have been
simply doing a %hex translation on characters that do not conform to XML,
that may actually be "good enough(tm)."

As for the field names being undefined, if you can find a way to get the
field types without having to specify a binary cursor I'd like that.
Admitedly, I have not looked very hard. This is a small part of a bigger
project. The SQL/XML provider currently supports PG and ODBC.

The web services project, which contains the SQL/XML provider, has a bunch
of other services.

mlw writes:

Given a HTTP formatted query:
GET "http://localhost:8181/pgmuze?query=select+*+from+zsong+limit+2&quot;

The output is entered below.

That looks a lot like the SQL/XML-style output plus a SOAP header. Below
is the output that I get from the SQL/XML function that I wrote. A simple
XSLT stylesheet should do the trick for you.

Btw., I also have an XSLT stylesheet that can make an HTML table out of
this output and I have a table function that can generate a virtual table
from this output.

=> select table2xml('select * from products');

<?xml version='1.0'?>
<table
xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance&#39;
xsi:noNamespaceSchemaLocation='#'> <!-- XXX this needs to be fixed -->

<xsd:schema
xmlns:xsd='http://www.w3.org/2001/XMLSchema&#39;
xmlns:sqlxml='http://www.iso-standards.org/mra/9075/2001/12/sqlxml&#39;&gt;

<xsd:import
namespace='http://www.iso-standards.org/mra/9075/2001/12/sqlxml&#39;
schemaLocation='http://www.iso-standards.org/mra/9075/2001/12/sqlxml.xsd&#39; />

<xsd:simpleType name='peter.pg_catalog.text'>
<xsd:restriction base='xsd:string'>
<xsd:maxLength value='MLIT' /> <!-- XXX needs actual value -->
</xsd:restriction>
</xsd:simpleType>

<xsd:simpleType name='INTEGER'>
<xsd:restriction base='xsd:integer'>
<xsd:maxInclusive value='2147483647'/>
<xsd:minInclusive value='-2147483648'/>
</xsd:restriction>
</xsd:simpleType>

<xsd:simpleType name='NUMERIC'>
<xsd:restriction base='xsd:decimal'>
<xsd:totalDigits value='PLIT'/> <!-- XXX needs actual values -->
<xsd:fractionDigits value='SLIT'/>
</xsd:restriction>
</xsd:simpleType>

<xsd:complexType name='RowType'>
<xsd:sequence>
<xsd:element name='name' type='peter.pg_catalog.text' nillable='true'></xsd:element>
<xsd:element name='category' type='INTEGER' nillable='true'></xsd:element>
<xsd:element name='price' type='NUMERIC' nillable='true'></xsd:element>
</xsd:sequence>
</xsd:complexType>

<xsd:complexType name='TableType'>
<xsd:sequence>
<xsd:element name='row' type='RowType' minOccurs='0' maxOccurs='unbounded' />
</xsd:sequence>
</xsd:complexType>

<xsd:element name='table' type='TableType' />

</xsd:schema>

<row>
<name>screwdriver</name>
<category>3</category>
<price>7.99</price>
</row>

<row>
<name>drill</name>
<category>9</category>
<price>12.49</price>
</row>

</table>

--
Peter Eisentraut peter_e@gmx.net

That function looks great, but what happens if you need to return 1
million records? Wouldn't you exhaust all the memory in the server? Or
can you stream it somehow?

I have an actual libpq program which performs a query against a server,
and will stream out the XML, so the number of records has very little
affect on efficiency. I think the table2xml function is great for 99% of
all the queries, but for those huge resultsets, I think it may be
problematic.

What do you think?

BTW, I routinely have queries that return millions of rows.

Peter Eisentraut wrote:

mlw kirjutas T, 01.04.2003 kell 15:29:

Hannu Krosing wrote:

pgsql@mohawksoft.com kirjutas E, 31.03.2003 kell 19:52:

Actually, as far as I am aware, the header is for metadata, i.e. it is the
place to describe the data being returned.

Did you read the SOAP spec ?

yes

???

What you have come up with _is_not_ a SOAP v1.1 message at all. It does
use some elements with similar names but from different namespace.

the SOAP Envelope, Header and Body elemants must be from namespace
http://schemas.xmlsoap.org/soap/envelope/

Per section 3 paragraph 2 of SOAP spec a conforming SOAP processor MUST
discard a message that has incorrect namespace.

<?xml version = "1.0"?>
<mwssql:Envelope xmlns:mwssql="http://www.mohawksoft.com/mwssql/envelope&quot;&gt;
<mwssql:Header>

The <SOAP-ENV:Header> "is a generic mechanism for adding features to a
SOAP message in a decentralized manner without prior agreement between
the communicating parties. SOAP defines a few attributes that can be
used to indicate who should deal with a feature and whether it is
optional or mandatory (see section 4.2)".

The <SOAP-ENV:Body> "is a container for mandatory information intended
for the ultimate recipient of the message (see section 4.3). SOAP
defines one element for the body, which is the Fault element used for
reporting errors."

The Header element is encoded as the first immediate child element of
the SOAP Envelope XML element. All immediate child elements of the
Header element are called header entries.

The encoding rules for header entries are as follows:

1. A header entry is identified by its fully qualified element
name, which consists of the namespace URI and the local name.
All immediate child elements of the SOAP Header element MUST be
namespace-qualified.

...

An example is a header with an element identifier of "Transaction", a
"mustUnderstand" value of "1", and a value of 5. This would be encoded
as follows:

<SOAP-ENV:Header>
<t:Transaction
xmlns:t="some-URI" SOAP-ENV:mustUnderstand="1">
5
</t:Transaction>
</SOAP-ENV:Header>

<exec:sql>update cgrpairs set ratio=0 where srcitem=100098670</exec:sql>
<exec:affected>2657</exec:affected>
<qry:sql>select * from ztitles limit 2</qry:sql>
<qry:ROWSET>
<qry:ROW columns="28">

where are namespaces exec:, qry: abd t: defined ?

----------------
Hannu

Hannu Krosing wrote:

mlw kirjutas T, 01.04.2003 kell 15:29:

Hannu Krosing wrote:

pgsql@mohawksoft.com kirjutas E, 31.03.2003 kell 19:52:

Actually, as far as I am aware, the header is for metadata, i.e. it is the
place to describe the data being returned.

Did you read the SOAP spec ?

yes

???

What you have come up with _is_not_ a SOAP v1.1 message at all. It does
use some elements with similar names but from different namespace.

the SOAP Envelope, Header and Body elemants must be from namespace
http://schemas.xmlsoap.org/soap/envelope/

[snip]
Hmm, I read "SHOULD" and "MAY" in the spec, assuming that it was not
"MUST" are you saying it is invalid if I do not use the SOAP URIs for
the name spaces? If so, no big deal, I'll change them.

As for defining the namespaces, yea that's easy enough, just tack on an
attribute.

I still don't see where putting the field definitions in the soap header
is an invalid use of that space.

Andrew Dunstan writes:

If the intention is to use field names as (local) tag names, how will you
handle the case where the field name isn't a valid XML name? Of course, one
could do some sort of mapping (replace illegal chars with "_", for example)
but then you can't be 100% certain that you haven't generated a collision,
I should think.

The SQL/XML draft specifies an reversible escape mechanism. Basically,
when mapping an SQL identifier to an XML name you replace problematic
characters with an escape sequence based on the Unicode code point, like
_x2A3B_.

--
Peter Eisentraut peter_e@gmx.net

mlw writes:

That function looks great, but what happens if you need to return 1
million records?

The same thing that happens with any set-returning function: memory
exhaustion.

I have an actual libpq program which performs a query against a server,
and will stream out the XML, so the number of records has very little
affect on efficiency. I think the table2xml function is great for 99% of
all the queries, but for those huge resultsets, I think it may be
problematic.

What do you think?

Clearly, my approach is not sufficient if you need to handle big result
sets. But perhaps a compromise based on cursors could be designed so that
large parts of the format can be managed centrally. Such as:

DECLARE foo CURSOR FOR SELECT ... ;

-- gives you the XML Schema for the result set
SELECT xmlschema_from_cursor(foo);

-- gives you ones row (<row>...</row>)
SELECT xmldata_from_cursor(foo);

--
Peter Eisentraut peter_e@gmx.net

mlw kirjutas K, 02.04.2003 kell 15:56:

Hannu Krosing wrote:

What you have come up with _is_not_ a SOAP v1.1 message at all. It does
use some elements with similar names but from different namespace.

the SOAP Envelope, Header and Body elemants must be from namespace
http://schemas.xmlsoap.org/soap/envelope/

[snip]
Hmm, I read "SHOULD" and "MAY" in the spec, assuming that it was not
"MUST" are you saying it is invalid if I do not use the SOAP URIs for
the name spaces? If so, no big deal, I'll change them.

AFAICS you can _leave_out_ the namespace, but not put in another,
nonconforming namespace.

As for defining the namespaces, yea that's easy enough, just tack on an
attribute.

I still don't see where putting the field definitions in the soap header
is an invalid use of that space.

It is not strictly nonconforming, just not the intended use of
"transparently adding" new info:

4.2 SOAP Header

SOAP provides a flexible mechanism for extending a message in a
decentralized and modular way without prior knowledge between the
communicating parties. Typical examples of extensions that can be
implemented as header entries are authentication, transaction
management, payment etc.

I.e. the intended use of *SOAP* Header is *not* defining the structure
of the message but is rather something similar to e-mail (rfc822)
Headers.

The XML way of defining a message is using a DTD, XML-schema, Relax NG
schema or somesuch, either embedded (forbidden for DTD's in SOAP) or
referenced.

Also for me the following:

The Header element is encoded as the first immediate child element of
the SOAP Envelope XML element. All immediate child elements of the
Header element are called header entries.

The encoding rules for header entries are as follows:

1. A header entry is identified by its fully qualified element
name, which consists of the namespace URI and the local name.
All immediate child elements of the SOAP Header element MUST be
namespace-qualified.

describes an element with a full embedded URI, not just
namespace-qualified tagname, but I may be reading it wrong and the
namespace could be defined at outer level. But defining namespace at the
outer level is counterintuitive for cases where the header element is to
be processed and removed by some "SOAP intermediary".

Also this seems to support *not* using Header for essensial structure
definitions:

4.3.1 Relationship between SOAP Header and Body

While the Header and Body are defined as independent elements, they
are in fact related. The relationship between a body entry and a
header entry is as follows: A body entry is semantically equivalent to
a header entry intended for the default actor and with a SOAP
mustUnderstand attribute with a value of "1". The default actor is
indicated by not using the actor attribute (see section 4.2.2).

This suggests that putting the structure definition as 1-st Body element
and data as second would be equivalent to putting structure in Header

-----------------
Hannu

Hannu Krosing wrote:

mlw kirjutas K, 02.04.2003 kell 15:56:

Hannu Krosing wrote:

What you have come up with _is_not_ a SOAP v1.1 message at all. It does
use some elements with similar names but from different namespace.

the SOAP Envelope, Header and Body elemants must be from namespace
http://schemas.xmlsoap.org/soap/envelope/

[snip]
Hmm, I read "SHOULD" and "MAY" in the spec, assuming that it was not
"MUST" are you saying it is invalid if I do not use the SOAP URIs for
the name spaces? If so, no big deal, I'll change them.

AFAICS you can _leave_out_ the namespace, but not put in another,
nonconforming namespace.

[snip]

I think you are interpreting the spec a bit too restrictively. The
syntax is fairly rigid, but the spec has a great degree of flexibility.
I agree that, syntactically, it must work through a parser, but there is
lots of room to be flexible.

mlw wrote:

I think you are interpreting the spec a bit too restrictively. The
syntax is fairly rigid, but the spec has a great degree of flexibility.
I agree that, syntactically, it must work through a parser, but there is
lots of room to be flexible.

This is /exactly/ the standard problem with SOAP.

There is enough "flexibility" that there are differing approaches
associated, generally speaking, with "IBM versus Microsoft" whereby it's
easy to generate SOAP requests that work fine with one that break with
the other.

For a pretty simple example of a longstanding bug that has never been
fixed, see:
<http://sourceforge.net/tracker/index.php?func=detail&amp;aid=559324&amp;group_id=26590&amp;atid=387667&gt;

The precis:

The SOAP implementation used by the XMethods folks to publish stock
prices is buggy, rejecting perfectly legitimate messages submitted using
ZSI (a Python SOAP implementation).

The bug isn't with ZSI; it is quite clearly with the server, apparently
implemented in Java using one of the EJB frameworks.

In practice, what happens is that since that service is fairly popular,
particularly for sample applications, the implementors of SOAP libraries
wind up coding around the bugs.

The problem is that it gets difficult to tell the difference between
bugs and variations in interpretations of the standards.

If the specs were more strictly defined, it would be a lot easier to use
SOAP, because you wouldn't be left puzzling over whether the
interoperability problems you're having are:

a) Problems with the client;
b) Problems with the server;
c) Problems with interpretation of specs;
d) ...

The vast degree to which messages can get rewritten behind your back
adds to the fun.

Of course, it's only fun if you *enjoy* having interoperability
problems...
--
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne&gt; rate me
http://www.ntlug.org/~cbbrowne/soap.html
He who laughs last thinks slowest.

cbbrowne@cbbrowne.com kirjutas N, 03.04.2003 kell 02:01:

mlw wrote:

I think you are interpreting the spec a bit too restrictively. The
syntax is fairly rigid, but the spec has a great degree of flexibility.
I agree that, syntactically, it must work through a parser, but there is
lots of room to be flexible.

This is /exactly/ the standard problem with SOAP.

There is enough "flexibility" that there are differing approaches
associated, generally speaking, with "IBM versus Microsoft" whereby it's
easy to generate SOAP requests that work fine with one that break with
the other.

Do you know of some:

a) standard conformance tests

b) recommended best practices for being compatible with all mainstream
implementations (I'd guess a good approach would be to generate very
strictly conformant code but accept all that you can, even if against
pedantic reading of the spec)

-----------------
Hannu

Hannu Krosing wrote:

cbbrowne@cbbrowne.com kirjutas N, 03.04.2003 kell 02:01:

mlw wrote:

I think you are interpreting the spec a bit too restrictively. The
syntax is fairly rigid, but the spec has a great degree of flexibility.
I agree that, syntactically, it must work through a parser, but there is
lots of room to be flexible.

This is /exactly/ the standard problem with SOAP.

There is enough "flexibility" that there are differing approaches
associated, generally speaking, with "IBM versus Microsoft" whereby it's
easy to generate SOAP requests that work fine with one that break with
the other.

Do you know of some:

a) standard conformance tests

Off the top of my head, no, but I bet it is a goole away. If you know
any good links, I'd love to know. I have been working off the W3C spec.

b) recommended best practices for being compatible with all mainstream
implementations (I'd guess a good approach would be to generate very
strictly conformant code but accept all that you can, even if against
pedantic reading of the spec)

I have been planning to "test" the whole thing with a few .NET
applications. I am currently using expat to parse the output to ensure
that it all works correcty.

cbbrowne@cbbrowne.com kirjutas N, 03.04.2003 kell 02:01:

mlw wrote:

I think you are interpreting the spec a bit too restrictively. The
syntax is fairly rigid, but the spec has a great degree of flexibility.
I agree that, syntactically, it must work through a parser, but there is
lots of room to be flexible.

This is /exactly/ the standard problem with SOAP.

There is enough "flexibility" that there are differing approaches
associated, generally speaking, with "IBM versus Microsoft" whereby it's
easy to generate SOAP requests that work fine with one that break with
the other.

Do you know of some:

a) standard conformance tests

b) recommended best practices for being compatible with all mainstream
implementations (I'd guess a good approach would be to generate very
strictly conformant code but accept all that you can, even if against
pedantic reading of the spec)

The problem with a) is that SOAP, unlike CORBA, doesn't have the notion of
standardized language bindings. That makes it tough to be sure that your
implementation is "standard" in any meaningful way in the first place.

The "best practices" have involved scripting up interoperability tests where
they construct sets of functions with varying data types and verify that "my
client implementation can talk to your server implementation," and vice-versa.

And when you run into problems, you chip off bits of code until the block of
stone starts looking like an elephant.

In order to have confidence of interoperability, you have to test your client
library against all the servers you care about, or vice-versa. That's
definitely not the same thing as being a "conformance" test.

Trying to be "really strict" doesn't seem to be a viable strategy, as far as I
can see...
--
(concatenate 'string "cbbrowne" "@ntlug.org")
http://www3.sympatico.ca/cbbrowne/wp.html
"The cost of living has just gone up another dollar a quart."
-- W.C. Fields

I have been planning to "test" the whole thing with a few .NET
applications. I am currently using expat to parse the output to ensure
that it all works correcty.

That, unfortunately, probably implies that your implementation is almost
totally non-interoperable.

You should put out of your mind the notion of being "correct." Being
"correct" is pretty irrelevant if 80% of the requests that come from a VB.NET
client fail because Microsoft implemented part of their request differently
than what you interpreted as "correct."

The point is that "correctness" isn't the thing you need to aim for; what you
should aim for is interoperability with the important client implementations.

SOAP::Lite, .NET, probably some Java ones, C++ ones, and such.

Nobody does "correctness" testing; they do interoperability tests where they
try to submit requests to Apache AXIS, .NET, WebSphere, and the lot of other
important implementations. If you're testing a server (as is the case here),
then the point is to run tests with a bunch of clients.

Head to the SOAP::Lite and Axis projects; you'll see matrices describing this
sort of thing...
--
(reverse (concatenate 'string "ac.notelrac.teneerf@" "454aa"))
http://www.ntlug.org/~cbbrowne/advocacy.html
"Fear leads to anger. Anger leads to hate. Hate leads to using Windows
NT for mission-critical applications." --- What Yoda *meant* to say

On Thu, Apr 03, 2003 at 07:54:13AM -0500, cbbrowne@cbbrowne.com wrote:

I have been planning to "test" the whole thing with a few .NET
applications. I am currently using expat to parse the output to ensure
that it all works correcty.

That, unfortunately, probably implies that your implementation is almost
totally non-interoperable.

You should put out of your mind the notion of being "correct." Being
"correct" is pretty irrelevant if 80% of the requests that come from a VB.NET
client fail because Microsoft implemented part of their request differently
than what you interpreted as "correct."

The point is that "correctness" isn't the thing you need to aim for; what you
should aim for is interoperability with the important client implementations.

SOAP::Lite, .NET, probably some Java ones, C++ ones, and such.

Nobody does "correctness" testing; they do interoperability tests where they
try to submit requests to Apache AXIS, .NET, WebSphere, and the lot of other
important implementations. If you're testing a server (as is the case here),
then the point is to run tests with a bunch of clients.

Head to the SOAP::Lite and Axis projects; you'll see matrices describing this
sort of thing...

Hmmm. Can I reiterate my support of XML-RPC here? <g>

-Jay 'Eraserhead' Felice