Orbeon Forms community mailing list

Beginning Orbeon MySQL Troubles

Classic

List

Threaded

4 messages Options

dcrensha

Beginning Orbeon MySQL Troubles

I have tried all of the documentation and mailing list topics on the subject I could find. However, I was unable to get a connection. My goal in all of this was to just make a simple query like 'show tables' or something analogous. I have added the latest mysql connector jar to both tomcat and orbeon's lib directories.

Next step was to create the datasource-sql.xml file with the content:

<datasource>
<driver-class-name>com.mysql.jdbc.Driver</driver-class-name>
<uri>jdbc:mysql://myserver:3306:mydb</uri>
<username>myuser</username>
<password>mypassword</password>
</datasource>

My next step was uncommenting these lines from orbeon's web.xml:

<resource-ref>
<description>DataSource</description>
<res-ref-name>jdbc/db</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

This, however caused all sorts of strange errors with orbeon's pages. This could all be very simple and attributed to ignorance on my part. I'm just looking for a simple, beginner style step-by-step for how to connect to a mysql database and put together a simple query.

Thanks,
Dennis Crenshaw

fl.schmitt(ops-users)

Re: Beginning Orbeon MySQL Troubles

hi dennis,

> Next step was to create the datasource-sql.xml file with the content:
>
> <datasource>
> <driver-class-name>com.mysql.jdbc.Driver</driver-class-name>
> <uri>jdbc:mysql://myserver:3306:mydb</uri>
> <username>myuser</username>
> <password>mypassword</password>
> </datasource>

is the uri correct? In my datasource.xml to access a postgresql server,
i use jdbc:postgresql://myserver/mydb (db name separated from server
name by a slash, not a ':' ) - this may be database specific?

> My next step was uncommenting these lines from orbeon's web.xml:
>
> <resource-ref>
> <description>DataSource</description>
> <res-ref-name>jdbc/db</res-ref-name>
> <res-type>javax.sql.DataSource</res-type>
> <res-auth>Container</res-auth>
> </resource-ref>

Hmm - i think, at this point you're mixing up the two different ways to
configure the datasource. Here's the documentation:

http://www.orbeon.com/ops/doc/processors-sql#configuration

If you use a datasource.xml config, you don't need to add a resource
reference to web.xml. So it should work even without uncommenting the
resource-ref fragment from web.xml.

Did you uncomment all the fragments in web.xml that are marked as
required for the SQL examples? If you just uncommented the xml fragment
cited above, i suppose the Datasource Definition is missing - there are
some more fragments to uncomment, but this is only required to get the
SQL sample working. If you don'Ät want this, you can leave it commented out.

So, i think it would be the best to comment out the resource-ref
fragment again to check if the simple datasurce.xml file is sufficient.

HTH
florian

--
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
OW2 mailing lists service home page: http://www.ow2.org/wws

dcrensha

Re: Beginning Orbeon MySQL Troubles

First of all, thanks for the reply.

> is the uri correct? In my datasource.xml to access a postgresql server,
> i use jdbc:postgresql://myserver/mydb (db name separated from server
> name by a slash, not a ':' ) - this may be database specific?

Pardon, that was a typo, you were indeed correct about the uri formatting error, however that was unfortunately not the source of my problem. I think I'm confused by the documentation. If I put my datasource-sql.xml in the ../webapps/orbeon/WEB-INF/resources/config/ directory then how do I go about referencing the datasource defined there in my xform? And then how do I put the completed query it into something I can see with HTML, say, using the XForms Sandbox?

This is what I have so far, though I'm unsure of how to look into the query after it's executed:

<html xmlns="http://www.w3.org/1999/xhtml"
xmlns:xforms="http://www.w3.org/2002/xforms"
xmlns:sql="http://orbeon.org/oxf/xml/sql">

<sql:config>
<results>
<sql:connection>
<sql:datasource>DataSource</sql:datasource>
<sql:execute>
<sql:query>
show tables
</sql:query>
</sql:execute>
</sql:connection>
</results>
</sql:config>

<head>
<title>XForms Hello SQL</title>
</head>
<body/>
</html>

I left the web.xml commented out so I would be able to figure out whether that was my problem or not, though it didn't seem to adffect anything.

- Dennis

fl.schmitt(ops-users)

Re: Re: Beginning Orbeon MySQL Troubles

hi dennis,

> If I put my datasource-sql.xml in the
> ../webapps/orbeon/WEB-INF/resources/config/ directory then how do I
> go about referencing the datasource defined there in my xform? And
> then how do I put the completed query it into something I can see
> with HTML, say, using the XForms Sandbox?

in my own app, i use XPL files to access the databases (eXist and
PostgreSQL). Such a XPL pipeline contains one or more "processors" to
generate or manipulate data or to access the database layer:

http://www.orbeon.com/ops/doc/reference-xpl-pipelines

To generate XML data from a SQL database, you will have to use the SQL
processor:

http://www.orbeon.com/ops/doc/processors-sql

For example, one of the calls in my own app to access a sql database
looks like this:

<p:processor name="oxf:sql">
<p:input
name="datasource"
href="datasource-verfdb.xml"
debug="datasource-in" />
<p:input
name="config"
href="#aindex-sqlquery"
debug="query-in" />
<p:output
name="output"
id="aindex"
debug="query-out" />
</p:processor>

The datasource input has a href to the file datasource-verfdb.xml that's
situated in the same directory as the XPL file (i use the
webapps\[context]\WEB-INF\resources\data-access\sql directory as place
where all sqldatabase-access-related files are stored). For testing, you
can also nest the configuration directly into the datasource input element.

The config input in the example above references the output of another
processor in the same XPL file (that has an id attribute with
'aindex-sqlquery' as value). This way, the sql query can be created
dynamically using XSLT. Again, the SQL search can also be nested into
the config input.

There's a special XPL sandbox where you can test your XPL file:

http://www.orbeon.com/ops/sandbox-transformations/xpl/

If you want to test the database access with a server not publicly
reachable, you could deploy orbeon.war in your own servlet container so
it can access the database. But IMHO it's easier to create a little test
app and use debugging attributes (as in my example) together with the
orbeon logfiles to check the in- and outputs of the processor.

To pass the result of the sql processor / xpl to your xforms page, you
can use the "usual" techniques to access a submission:

http://www.orbeon.com/ops/doc/reference-xforms-ng#xforms-instance-initialization

This way, you can initialize a XForms instance with the output of your
query.

HTH
florian

--
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
OW2 mailing lists service home page: http://www.ow2.org/wws