Orbeon Forms community mailing list

dynamic sql datasource

Classic

List

Threaded

3 messages Options

Michiel Roos [netcreators]

dynamic sql datasource

Dear list,

I am trying to figure out how to dynamically allocate a username to a datasource. The main idea is this:

- pass in an URI like http://localhost/ops/app?name=jack
- get the username from the uri
- set the username to a page with an sql model
- create a connection using username
- select stuff from a table

The example works when a username is entered in the datasource-sql.xml document. But I cannot seem to pass the username (which I can successfully pass to the second page) into the datasource model.

I have also tried to integrate the datasource into the sql.xpl document and then set the username value using <xsl:value-of
select="/datasource/username"/>. But then orbeon tells me: Error element "xsl:value-of" was found where no element may occur(schema:
http://www.orbeon.org/oxf/sql-datasource)

When I visit http://www.orbeon.org/oxf/sql-datasource it keeps loading forever and never show a page.

Can anyone push me in the right direction?

datasource-sql.xml:
<datasource>
<driver-class-name>org.postgresql.Driver</driver-class-name>
<uri>jdbc:postgresql://localhost:5432/dbname?autoReconnect=true</uri> <username></username>
<password>secret</password>
</datasource>

page-flow.xml:
<config xmlns="http://www.orbeon.com/oxf/controller"
xmlns:oxf="http://www.orbeon.com/oxf/processors"
xmlns:xu="http://www.xmldb.org/xupdate">

<page id="paramtest" path-info="/app"
default-submission="datasource-sql.xml">
<setvalue ref="/datasource/username" parameter="name"/>
<action>
<result page="my-redirect-page">
<xu:update select="/datasource/username">
<xu:value-of
select="document('input:instance')/datasource/username"/>
</xu:update>
</result>
</action>
</page>

<page id="my-redirect-page" path-info="/app/redirect" model="sql.xpl"
view="view.xsl">
</page>

<epilogue url="oxf:/config/epilogue.xpl"/>
</config>

view.xsl:


<xhtml:html xmlns:xhtml="http://www.w3.org/1999/xhtml"
xmlns:xforms="http://www.w3.org/2002/xforms"
xmlns:xi="http://www.w3.org/2001/XInclude"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xsl:version="2.0">
<xhtml:head>
<xforms:model id="main-model">


<xforms:instance id="instance">
<xi:include href="input:instance"/>
</xforms:instance>

</xforms:model>
</xhtml:head>
<xhtml:body>
<xforms:input ref="/datasource/username">
</xforms:input>
<table class="gridtable">
<tr>
<th>username</th>
</tr>
<xsl:for-each select="/person">
<tr>
<td>
<xsl:value-of select="username"/>
</td>
</tr>
</xsl:for-each>
</table>
</xhtml:body>
</xhtml:html>

sql.xpl:
<p:config xmlns:p="http://www.orbeon.com/oxf/pipeline"
xmlns:sql="http://orbeon.org/oxf/xml/sql"
xmlns:oxf="http://www.orbeon.com/oxf/processors"
xmlns:xs="http://www.w3.org/2001/XMLSchema">
<p:param type="output" name="data"/>
<p:processor name="oxf:sql">
<p:input name="datasource" href="datasource-sql.xml"/>
<p:input name="config">
<sql:config>
<person>
<sql:connection>
<sql:execute>
<sql:query>SELECT username FROM users</sql:query>
<sql:result-set result-sets="unbounded">
<sql:row-iterator>
<username>
<sql:get-column-value type="xs:string"
column="username"/>
</username>
</sql:row-iterator>
</sql:result-set>
<sql:no-results>
<text>No result</text>
</sql:no-results>
</sql:execute>
</sql:connection>
</person>
</sql:config>
</p:input>
<p:output name="data" ref="data"/>
</p:processor>
</p:config>

Kind regards,

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: dynamic sql datasource

Administrator

Michiel,

> I am trying to figure out how to dynamically allocate a username to
> a datasource. The main idea is this:
>
> - pass in an URI like http://localhost/ops/app?name=jack
> - get the username from the uri
> - set the username to a page with an sql model
> - create a connection using username
> - select stuff from a table
>
> The example works when a username is entered in the
> datasource-sql.xml document. But I cannot seem to pass the username
> (which I can successfully pass to the second page) into the
> datasource model.
>
> I have also tried to integrate the datasource into the sql.xpl
> document and then set the username value using <xsl:value-of
> select="/datasource/username"/>. But then orbeon tells me: Error
> element "xsl:value-of" was found where no element may occur(schema:
> http://www.orbeon.org/oxf/sql-datasource)
>
> When I visit http://www.orbeon.org/oxf/sql-datasource it keeps
> loading forever and never show a page.

That XML namespace URI is just that, an XML namespace URI. It does not
correspond to an existing web page.

> Can anyone push me in the right direction?

You should use an XSLT transformation (oxf:xslt) to produce the SQL
processor's "datasource" input from information in the request,
instead of using a static XML file for that purpose.

(Note that the "datasource" input of the SQL processor will in fact
cause the SQL processor *not* to use an actual system datasource,
therefore not using any connection pooling. It will create a new
connection everytime you call the SQL processor, which will probably
be fairly slow.)

-Erik

--
Orbeon Forms - XForms Everywhere
http://www.orbeon.com/blog/

--
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: dynamic sql datasource

Erik Bruchez wrote:
> > When I visit http://www.orbeon.org/oxf/sql-datasource it keeps
> > loading forever and never show a page.
>
> That XML namespace URI is just that, an XML namespace URI. It does not
> correspond to an existing web page.
Ok, clear

> > Can anyone push me in the right direction?
>
> You should use an XSLT transformation (oxf:xslt) to produce the SQL
> processor's "datasource" input from information in the request,
> instead of using a static XML file for that purpose.
>
> (Note that the "datasource" input of the SQL processor will in fact
> cause the SQL processor *not* to use an actual system datasource,
> therefore not using any connection pooling. It will create a new
> connection everytime you call the SQL processor, which will probably
> be fairly slow.)

Ok, the @#%@#$% mailinglist was down, but I came to the same conclusion

in the end and implemented it thusly (pitty about the connection
pooling though):

<p:config xmlns:p="http://www.orbeon.com/oxf/pipeline"
xmlns:sql="http://orbeon.org/oxf/xml/sql"
xmlns:oxf="http://www.orbeon.com/oxf/processors"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
<p:param type="input" name="instance"/>
<p:param type="output" name="data"/>


<p:processor name="oxf:xslt">
<p:input name="config" href="datasource-config.xsl"/>
<p:input name="data" href="#instance"/>
<p:output name="data" id="datasource"/>
</p:processor>


<p:processor name="oxf:sql">
<p:input name="datasource" href="#datasource"/>
<p:input name="config">
<sql:config>
<form>
<sql:connection>
<sql:execute>

</sql:execute>
</sql:connection>
</form>
</sql:config>
</p:input>
<p:output name="data" ref="data"/>
</p:processor>
</p:config>

Thanks for the reply Erik,

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