9 Sep
2012
9 Sep
'12
5:50 a.m.
Hi all,
I am trying to get a wiki page to display the tables in my DB2 database
(on an AIX system, not an OS/400). I'm confused about how to configure
Groovy to make the connection, though. I went here:
http://extensions.xwiki.org/xwiki/bin/view/Extension/Execute+SQL
So far, I have come up with this syntax:
{{groovy}}
import groovy.sql.Sql
sql = Sql.newInstance( 'jdbc:db2://AAA:BBB/CCC', 'DDD', 'EEE',
'com.ibm.db2.jcc.DB2Driver' )
sql.eachRow( 'list tables' )
{{/groovy}}
AAA = server name
BBB = port
CCC = database name
DDD = user
EEE = password
This syntax doesn't work - it results in "ClassNotFoundException" errors.
First question: do I have the syntax right?
Second question: I assume I have to provide Groovy some DB2 zip/jar file,
but where should that file be placed in my Tomcat/MySQL installation?
Third question: I know I will have to manipulate the query result so that
the output will have wiki elements (like clickable links). Does anyone
have any examples of how they displayed their results, preferably with
tables and links?
Thanks!
9 Sep
9 Sep
9:13 a.m.
Hi Matt,
On Sep 9, 2012, at 5:50 AM, Matt Lamoureux <mmlmrx(a)gmail.com> wrote:
Hi all,
I am trying to get a wiki page to display the tables in my DB2 database
(on an AIX system, not an OS/400). I'm confused about how to configure
Groovy to make the connection, though. I went here:
http://extensions.xwiki.org/xwiki/bin/view/Extension/Execute+SQL
So far, I have come up with this syntax:
{{groovy}}
import groovy.sql.Sql
sql = Sql.newInstance( 'jdbc:db2://AAA:BBB/CCC', 'DDD', 'EEE',
'com.ibm.db2.jcc.DB2Driver' )
sql.eachRow( 'list tables' )
{{/groovy}}
AAA = server name
BBB = port
CCC = database name
DDD = user
EEE = password
This syntax doesn't work - it results in "ClassNotFoundException" errors.
First question: do I have the syntax right?
No idea, I've never used this. I have just copied the information from
http://groovy.codehaus.org/Tutorial+6+-+Groovy+SQL and put it on
http://extensions.xwiki.org/xwiki/bin/view/Extension/Execute+SQL
Second question: I assume I have to provide Groovy
some DB2 zip/jar file,
but where should that file be placed in my Tomcat/MySQL installation?
You need to put the db2 jar In your webapp's WEB-INF/lib directory. This is probably
why you get the CNFE.
Third question: I know I will have to manipulate the
query result so that
the output will have wiki elements (like clickable links). Does anyone
have any examples of how they displayed their results, preferably with
tables and links?
You just need to use wiki syntax…
To do that from Groovy you'd use: print and println.
For ex: println "Some **bold**"
See the syntax guide to learn how to use tables/links.
Hope it helps,
-Vincent
15 Sep
15 Sep
5:53 p.m.
That did help, Vincent - thank you!
After putting the right JAR files into the WEB-INF\lib directory, I have
managed to make a connection to a remote DB2 database and even format the
results into a proper wiki table (even with links):
{{groovy}}
import groovy.sql.Sql
sql = Sql.newInstance( 'jdbc:db2://<database>/<database>',
'<user>',
'<password>', 'com.ibm.db2.jcc.DB2Driver' )
println "|=Name|=Type|=Length|=Nullable";
sql.eachRow("SELECT colname, typename, length, nulls FROM SYSCAT.COLUMNS
WHERE tabname = '<table name>' and tabschema = '<schema>'
")
{ row ->
println "|[[$row.colname]]|$row.typename|$row.length|$row.nulls";
}
{{/groovy}}
Now my question is - how can I convert that plaintext URL into using a
datasource? I saw the link on your "Execute SQL" page which referred to a
page on StackExchange, but I can't seem to figure it out. It can't be as
simple as using "ConfigurationHolder" and putting the values in xwiki.cfg
or something, is it?
Thanks!
On Sun, Sep 9, 2012 at 3:13 AM, Vincent Massol <vincent(a)massol.net> wrote:
Hi Matt,
On Sep 9, 2012, at 5:50 AM, Matt Lamoureux <mmlmrx(a)gmail.com> wrote:
_______________________________________________
users mailing list
users(a)xwiki.org
http://lists.xwiki.org/mailman/listinfo/users
Hi all,
I am trying to get a wiki page to display the tables in my DB2 database
(on an AIX system, not an OS/400). I'm confused about how to configure
Groovy to make the connection, though. I went here:
http://extensions.xwiki.org/xwiki/bin/view/Extension/Execute+SQL
So far, I have come up with this syntax:
{{groovy}}
import groovy.sql.Sql
sql = Sql.newInstance( 'jdbc:db2://AAA:BBB/CCC', 'DDD', 'EEE',
'com.ibm.db2.jcc.DB2Driver' )
sql.eachRow( 'list tables' )
{{/groovy}}
AAA = server name
BBB = port
CCC = database name
DDD = user
EEE = password
This syntax doesn't work - it results in "ClassNotFoundException" errors.
First question: do I have the syntax right?
No idea, I've never used this. I have just copied the information from
http://groovy.codehaus.org/Tutorial+6+-+Groovy+SQL and put it on
http://extensions.xwiki.org/xwiki/bin/view/Extension/Execute+SQL
Second question: I assume I have to provide
Groovy some DB2 zip/jar file,
but where should that file be placed in my Tomcat/MySQL installation?
You need to put the db2 jar In your webapp's WEB-INF/lib directory. This
is probably why you get the CNFE.
Third question: I know I will have to manipulate
the query result so that
the output will have wiki elements (like clickable links). Does anyone
have any examples of how they displayed their results, preferably with
tables and links?
You just need to use wiki syntax…
To do that from Groovy you'd use: print and println.
For ex: println "Some **bold**"
See the syntax guide to learn how to use tables/links.
Hope it helps,
-Vincent
4481
days inactive
4487
days old
2 comments
2 participants
participants (2)
-
Matt Lamoureux -
Vincent Massol