Orbeon Forms community mailing list

howto UPDATE some.postgresTable SET someval= . . . .?

Classic

List

Threaded

4 messages Options

Michiel Roos [netcreators]

howto UPDATE some.postgresTable SET someval= . . . .?

Dear list,

I can sucessfully read a xmlfragment from a postgres database by using:

<sql:get-column-value type="odt:xmlFragment" column="xsl"/>

But now I would like to write an xml fragment to the database using:

<sql:update>
UPDATE forms.someTable SET xml='<sql:param select="/data/somebranch"
type="xs:string"/>' where xmlid=<sql:param type="xs:int"
select="/data/state/xmlid"/>
</sql:update>

This however fails telling me:
The column index is out of range: 2, number of columns: 1.

I can successfully run:

<sql:update>
UPDATE forms.someTable SET xml='blip' where xmlid=<sql:param
type="xs:int" select="/data/state/xmlid"/>
</sql:update>

But I don't want a blip!

;-)

Next stop is to try and regex out all the < and > before the inserts.

There must be a cleaner way. Any pointers?

--

Met vriendelijke groet,

Michiel Roos
netcreators.com

--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws

Erik Bruchez

Re: howto UPDATE some.postgresTable SET someval= . . . .?

Administrator

Michiel,

> I can sucessfully read a xmlfragment from a postgres database by using:
>
> <sql:get-column-value type="odt:xmlFragment" column="xsl"/>
>
> But now I would like to write an xml fragment to the database using:
>
> <sql:update>
> UPDATE forms.someTable SET xml='<sql:param select="/data/somebranch"
> type="xs:string"/>' where xmlid=<sql:param type="xs:int"
> select="/data/state/xmlid"/>
> </sql:update>

I doubt the single quotes are needed above. Your update statement will
in fact be changed by oxf:sql to a prepared statement of the form:

UPDATE forms.someTable SET xml='?' where xmlid=?

I suspect that the single quotes will make the JDBC driver miss the
first parameter.

-Erik

--
Orbeon Forms - Web Forms for the Enterprise Done the Right Way
http://www.orbeon.com/

--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws

Michiel Roos [netcreators]

Re: howto UPDATE some.postgresTable SET someval= . . . .?

Erik Bruchez wrote:

> Michiel,
>
> > I can sucessfully read a xmlfragment from a postgres database by using:
> >
> > <sql:get-column-value type="odt:xmlFragment" column="xsl"/>
> >
> > But now I would like to write an xml fragment to the database using:
> >
> > <sql:update>
> > UPDATE forms.someTable SET xml='<sql:param select="/data/somebranch"
> > type="xs:string"/>' where xmlid=<sql:param type="xs:int"
> > select="/data/state/xmlid"/>
> > </sql:update>
>
> I doubt the single quotes are needed above. Your update statement will
> in fact be changed by oxf:sql to a prepared statement of the form:
>
> UPDATE forms.someTable SET xml='?' where xmlid=?
>
> I suspect that the single quotes will make the JDBC driver miss the
> first parameter.

Eric,

Acknowleged.

However, even without the quotes, I would like to insert a piece of xml
into the db without orbeon stripping all the tags from the xml fragment.

Who strips these tags and why?

Is it the postgres driver? Orbeon?

Is there a way I can debug this further?

I currently have the following turned on:
<category name="org.orbeon.oxf.xforms.XFormsModelSubmission">
<priority value="info"/>
</category>

<category name="org.orbeon.oxf.processor.sql.SQLProcessor">
<priority value="debug"/>
</category>

Currently I work around the problem using saxon:serialize. But there
must be a more elegant solution.

Cheers,

Michiel Roos

--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws

Erik Bruchez

Re: howto UPDATE some.postgresTable SET someval= . . . .?

Administrator

Michiel Roos wrote:

> Erik Bruchez wrote:
>> Michiel,
>>
>> > I can sucessfully read a xmlfragment from a postgres database by using:
>> >
>> > <sql:get-column-value type="odt:xmlFragment" column="xsl"/>
>> >
>> > But now I would like to write an xml fragment to the database using:
>> >
>> > <sql:update>
>> > UPDATE forms.someTable SET xml='<sql:param select="/data/somebranch"
>> > type="xs:string"/>' where xmlid=<sql:param type="xs:int"
>> > select="/data/state/xmlid"/>
>> > </sql:update>
>>
>> I doubt the single quotes are needed above. Your update statement will
>> in fact be changed by oxf:sql to a prepared statement of the form:
>>
>> UPDATE forms.someTable SET xml='?' where xmlid=?
>>
>> I suspect that the single quotes will make the JDBC driver miss the
>> first parameter.
> Eric,
>
> Acknowleged.
>
> However, even without the quotes, I would like to insert a piece of xml
> into the db without orbeon stripping all the tags from the xml fragment.
>
> Who strips these tags and why?
>
> Is it the postgres driver? Orbeon?

I don't think you explain what the problem actually is. What do you mean
by "stripping all the tags from the xml fragment"?

-Erik

--
Orbeon Forms - Web Forms for the Enterprise Done the Right Way
http://www.orbeon.com/

--
You receive this message as a subscriber of the [hidden email] mailing list.
To unsubscribe: mailto:[hidden email]
For general help: mailto:[hidden email]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws