|
»
»
»
SQL module using JNDI resource
|
View:
New views
7 Messages
—
Rating Filter:
Alert me
|
 |
SQL module using JNDI resource
by Danny Kruitbosch
::
Rate this Message:
Hi,
We're currently in a project in which we're going to use exist and xquery to aggregate data from several sources like external web services, data stored in exist and data stored in RDBMS-es. We're using the httpclient module to call the external web services and are going to use the sqlmodule for getting the data from the RDBMS-es. eXist will be run from within tomcat or jetty. For the SQL connections, it would be nice if I was able to reference a jndi resource that is managed by tomcat/jetty. This way tomcat/jetty will be responsible for maintaining and cleaning up the connections to the databases and it will result in a more scalable solution as compared to opening and closing a connection for each query that is run. Since I'm no programmer, I would like to know if someone has done this already or what needs to be changed/added to the sql module to implement this. Any feedback on this idea will be much appreciated. Kind regard, Danny ------------------------------------------------------------------------- This SF.net email is sponsored by the 2008 JavaOne(SM) Conference Don't miss this year's exciting event. There's still time to save $100. Use priority code J8TL2D2. http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone _______________________________________________ Exist-open mailing list Exist-open@... https://lists.sourceforge.net/lists/listinfo/exist-open |
|
|
Re: SQL module using JNDI resource
by Adam Retter-3
::
Rate this Message:
Not much is required, really you need an extra function (perhaps
called get-jndi-connection()) in the SQLModule which would be very similar to the existing sql:get-connection() method. For a function such as sql:get-jndi-connection($jndiName, $username, $password), All it would really need to do is something like this - String dbJNDI = args[0].getStringValue(); String dbUser = args[1].getStringValue(); String dbPassword = args[2].getStringValue(); Context ctx = new InitialContext(); DataSource ds = (DataSource) ctx.lookup(dbJNDI); Connection con = ds.getConnection(dbUser, dbPassword); //get the existing connections map from the context HashMap connections = (HashMap)context.getXQueryContextVar(SQLModule.CONNECTIONS_CONTEXTVAR); if(connections == null) { //if there is no connections map, create a new one connections = new HashMap(); } //get an id for the connection long conID = getUID(); //place the connection in the connections map connections.put(new Long(conID), con); //store the updated connections map back in the context context.setXQueryContextVar(SQLModule.CONNECTIONS_CONTEXTVAR, connections); //return the uid handle of the connection return new IntegerValue(conID); 2008/5/8 <exist@...>: > > Hi, > > We're currently in a project in which we're going to use exist and > xquery to aggregate data from several sources like external web > services, data stored in exist and data stored in RDBMS-es. > > We're using the httpclient module to call the external web services > and are going to use the sqlmodule for getting the data from the > RDBMS-es. > > eXist will be run from within tomcat or jetty. > For the SQL connections, it would be nice if I was able to reference a > jndi resource that is managed by tomcat/jetty. This way tomcat/jetty > will be responsible for maintaining and cleaning up the connections to > the databases and it will result in a more scalable solution as > compared to opening and closing a connection for each query that is run. > > Since I'm no programmer, I would like to know if someone has done this > already or what needs to be changed/added to the sql module to > implement this. > > Any feedback on this idea will be much appreciated. > > > Kind regard, > > Danny > > ------------------------------------------------------------------------- > This SF.net email is sponsored by the 2008 JavaOne(SM) Conference > Don't miss this year's exciting event. There's still time to save $100. > Use priority code J8TL2D2. > http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone > _______________________________________________ > Exist-open mailing list > Exist-open@... > https://lists.sourceforge.net/lists/listinfo/exist-open -- Adam Retter eXist Developer { England } adam@... irc://irc.freenode.net/existdb ------------------------------------------------------------------------- This SF.net email is sponsored by the 2008 JavaOne(SM) Conference Don't miss this year's exciting event. There's still time to save $100. Use priority code J8TL2D2. http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone _______________________________________________ Exist-open mailing list Exist-open@... https://lists.sourceforge.net/lists/listinfo/exist-open |
|
|
Re: SQL module using JNDI resource
by Adam Retter-3
::
Rate this Message:
Danny, during some spare time at lunch today I have added in two
additional functions to the SQL module for JNDI support - sql:get-jndi-connection($jndiName) sql:get-jndi-connection($jndiName, $username, $password) Hopefully that will help you, unfortunately I have not had the time to test this as I have no easy access to JNDI sources. Anyways the code is in eXits's trunk. If you can check that out and build it and give it a test then that would be great. Thanks Adam. 2008/5/8 Adam Retter <adam@...>: > Not much is required, really you need an extra function (perhaps > called get-jndi-connection()) in the SQLModule which would be very > similar to the existing sql:get-connection() method. > > For a function such as sql:get-jndi-connection($jndiName, $username, > $password), All it would really need to do is something like this - > > String dbJNDI = args[0].getStringValue(); > String dbUser = args[1].getStringValue(); > String dbPassword = args[2].getStringValue(); > > Context ctx = new InitialContext(); > DataSource ds = (DataSource) ctx.lookup(dbJNDI); > Connection con = ds.getConnection(dbUser, dbPassword); > > //get the existing connections map from the context > HashMap connections = > (HashMap)context.getXQueryContextVar(SQLModule.CONNECTIONS_CONTEXTVAR); > if(connections == null) > { > //if there is no connections map, create a new one > connections = new HashMap(); > } > > //get an id for the connection > long conID = getUID(); > > //place the connection in the connections map > connections.put(new Long(conID), con); > > //store the updated connections map back in the context > context.setXQueryContextVar(SQLModule.CONNECTIONS_CONTEXTVAR, connections); > > //return the uid handle of the connection > return new IntegerValue(conID); > > > 2008/5/8 <exist@...>: >> >> Hi, >> >> We're currently in a project in which we're going to use exist and >> xquery to aggregate data from several sources like external web >> services, data stored in exist and data stored in RDBMS-es. >> >> We're using the httpclient module to call the external web services >> and are going to use the sqlmodule for getting the data from the >> RDBMS-es. >> >> eXist will be run from within tomcat or jetty. >> For the SQL connections, it would be nice if I was able to reference a >> jndi resource that is managed by tomcat/jetty. This way tomcat/jetty >> will be responsible for maintaining and cleaning up the connections to >> the databases and it will result in a more scalable solution as >> compared to opening and closing a connection for each query that is run. >> >> Since I'm no programmer, I would like to know if someone has done this >> already or what needs to be changed/added to the sql module to >> implement this. >> >> Any feedback on this idea will be much appreciated. >> >> >> Kind regard, >> >> Danny >> >> ------------------------------------------------------------------------- >> This SF.net email is sponsored by the 2008 JavaOne(SM) Conference >> Don't miss this year's exciting event. There's still time to save $100. >> Use priority code J8TL2D2. >> http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone >> _______________________________________________ >> Exist-open mailing list >> Exist-open@... >> https://lists.sourceforge.net/lists/listinfo/exist-open > > > > -- > Adam Retter > > eXist Developer > { England } > adam@... > irc://irc.freenode.net/existdb > -- Adam Retter eXist Developer { England } adam@... irc://irc.freenode.net/existdb ------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ Exist-open mailing list Exist-open@... https://lists.sourceforge.net/lists/listinfo/exist-open |
|
|
Re: SQL module using JNDI resource
by Danny Kruitbosch
::
Rate this Message:
Adam,
Thanks a lot! I'll check it out and let you know how it goes. Kind regards, Danny Quoting Adam Retter <adam@...>: > Danny, during some spare time at lunch today I have added in two > additional functions to the SQL module for JNDI support - > > sql:get-jndi-connection($jndiName) > sql:get-jndi-connection($jndiName, $username, $password) > > Hopefully that will help you, unfortunately I have not had the time to > test this as I have no easy access to JNDI sources. Anyways the code > is in eXits's trunk. If you can check that out and build it and give > it a test then that would be great. > > Thanks Adam. > > > 2008/5/8 Adam Retter <adam@...>: >> Not much is required, really you need an extra function (perhaps >> called get-jndi-connection()) in the SQLModule which would be very >> similar to the existing sql:get-connection() method. >> >> For a function such as sql:get-jndi-connection($jndiName, $username, >> $password), All it would really need to do is something like this - >> >> String dbJNDI = args[0].getStringValue(); >> String dbUser = args[1].getStringValue(); >> String dbPassword = args[2].getStringValue(); >> >> Context ctx = new InitialContext(); >> DataSource ds = (DataSource) ctx.lookup(dbJNDI); >> Connection con = ds.getConnection(dbUser, dbPassword); >> >> //get the existing connections map from the context >> HashMap connections = >> (HashMap)context.getXQueryContextVar(SQLModule.CONNECTIONS_CONTEXTVAR); >> if(connections == null) >> { >> //if there is no connections map, create a new one >> connections = new HashMap(); >> } >> >> //get an id for the connection >> long conID = getUID(); >> >> //place the connection in the connections map >> connections.put(new Long(conID), con); >> >> //store the updated connections map back in the context >> context.setXQueryContextVar(SQLModule.CONNECTIONS_CONTEXTVAR, connections); >> >> //return the uid handle of the connection >> return new IntegerValue(conID); >> >> >> 2008/5/8 <exist@...>: >>> >>> Hi, >>> >>> We're currently in a project in which we're going to use exist and >>> xquery to aggregate data from several sources like external web >>> services, data stored in exist and data stored in RDBMS-es. >>> >>> We're using the httpclient module to call the external web services >>> and are going to use the sqlmodule for getting the data from the >>> RDBMS-es. >>> >>> eXist will be run from within tomcat or jetty. >>> For the SQL connections, it would be nice if I was able to reference a >>> jndi resource that is managed by tomcat/jetty. This way tomcat/jetty >>> will be responsible for maintaining and cleaning up the connections to >>> the databases and it will result in a more scalable solution as >>> compared to opening and closing a connection for each query that is run. >>> >>> Since I'm no programmer, I would like to know if someone has done this >>> already or what needs to be changed/added to the sql module to >>> implement this. >>> >>> Any feedback on this idea will be much appreciated. >>> >>> >>> Kind regard, >>> >>> Danny >>> >>> ------------------------------------------------------------------------- >>> This SF.net email is sponsored by the 2008 JavaOne(SM) Conference >>> Don't miss this year's exciting event. There's still time to save $100. >>> Use priority code J8TL2D2. >>> http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone >>> _______________________________________________ >>> Exist-open mailing list >>> Exist-open@... >>> https://lists.sourceforge.net/lists/listinfo/exist-open >> >> >> >> -- >> Adam Retter >> >> eXist Developer >> { England } >> adam@... >> irc://irc.freenode.net/existdb >> > > > > -- > Adam Retter > > eXist Developer > { England } > adam@... > irc://irc.freenode.net/existdb > ------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ Exist-open mailing list Exist-open@... https://lists.sourceforge.net/lists/listinfo/exist-open |
|
|
Re: SQL module using JNDI resource
by Danny Kruitbosch
::
Rate this Message:
Hi Adam,
I've been testing and it doesn't work yet. I've setup tomcat to provide a jndi resource. Tested it with a simple jsp to see if the resource was available (put it into the exist context dir) and the jsp works OK. Next I made the following xquery: xquery version "1.0"; declare namespace sql="http://exist-db.org/xquery/sql"; let $conn := sql:get-jndi-connection("jdbc\/KLNTDB") return sql:execute($conn, "select * from RDCINST1.RDCCUSTOMER where INASINT=913737", true()) stored it into the xquery dir as dbtest.xql and called http://localhost:8080/exist/xquery/dbtest.xql Then I got the following error: Error found Name jdbc/KLNTDB is not bound in this Context [at line 5, column 14] Java Stack Trace:Class Name Method Name File Name Line org.exist.xquery.modules.sql.GetJNDIConnectionFunction eval GetJNDIConnectionFunction.java 131 org.exist.xquery.BasicFunction eval BasicFunction.java 68 org.exist.xquery.InternalFunctionCall eval InternalFunctionCall.java 49 org.exist.xquery.AbstractExpression eval AbstractExpression.java 59 org.exist.xquery.PathExpr eval PathExpr.java 248 org.exist.xquery.LetExpr eval LetExpr.java 150 org.exist.xquery.BindingExpression eval BindingExpression.java 158 org.exist.xquery.AbstractExpression eval AbstractExpression.java 59 org.exist.xquery.PathExpr eval PathExpr.java 248 org.exist.xquery.AbstractExpression eval AbstractExpression.java 59 org.exist.xquery.XQuery execute XQuery.java 217 org.exist.xquery.XQuery execute XQuery.java 184 org.exist.xmldb.LocalXPathQueryService execute LocalXPathQueryService.java 178 org.exist.http.servlets.XQueryServlet process XQueryServlet.java 360 org.exist.http.servlets.XQueryServlet doGet XQueryServlet.java 164 javax.servlet.http.HttpServlet service HttpServlet.java 690 javax.servlet.http.HttpServlet service HttpServlet.java 803 org.apache.catalina.core.ApplicationFilterChain internalDoFilter ApplicationFilterChain.java 290 org.apache.catalina.core.ApplicationFilterChain doFilter ApplicationFilterChain.java 206 org.apache.catalina.core.StandardWrapperValve invoke StandardWrapperValve.java 233 So apparently it does "see" the jndi resource in de context of the xquery? I also made a change to the GetJNDIConnectionFunction.java The signature of the get-jndi-connection($a as xs:string) requested 2 input args and you only require 1 (only the JNDI name). I've attached the changed file. Quoting Danny Kruitbosch <exist@...>: > Adam, > > Thanks a lot! I'll check it out and let you know how it goes. > > Kind regards, > > Danny > > Quoting Adam Retter <adam@...>: > >> Danny, during some spare time at lunch today I have added in two >> additional functions to the SQL module for JNDI support - >> >> sql:get-jndi-connection($jndiName) >> sql:get-jndi-connection($jndiName, $username, $password) >> >> Hopefully that will help you, unfortunately I have not had the time to >> test this as I have no easy access to JNDI sources. Anyways the code >> is in eXits's trunk. If you can check that out and build it and give >> it a test then that would be great. >> >> Thanks Adam. >> >> >> 2008/5/8 Adam Retter <adam@...>: >>> Not much is required, really you need an extra function (perhaps >>> called get-jndi-connection()) in the SQLModule which would be very >>> similar to the existing sql:get-connection() method. >>> >>> For a function such as sql:get-jndi-connection($jndiName, $username, >>> $password), All it would really need to do is something like this - >>> >>> String dbJNDI = args[0].getStringValue(); >>> String dbUser = args[1].getStringValue(); >>> String dbPassword = args[2].getStringValue(); >>> >>> Context ctx = new InitialContext(); >>> DataSource ds = (DataSource) ctx.lookup(dbJNDI); >>> Connection con = ds.getConnection(dbUser, dbPassword); >>> >>> //get the existing connections map from the context >>> HashMap connections = >>> (HashMap)context.getXQueryContextVar(SQLModule.CONNECTIONS_CONTEXTVAR); >>> if(connections == null) >>> { >>> //if there is no connections map, create a new one >>> connections = new HashMap(); >>> } >>> >>> //get an id for the connection >>> long conID = getUID(); >>> >>> //place the connection in the connections map >>> connections.put(new Long(conID), con); >>> >>> //store the updated connections map back in the context >>> context.setXQueryContextVar(SQLModule.CONNECTIONS_CONTEXTVAR, connections); >>> >>> //return the uid handle of the connection >>> return new IntegerValue(conID); >>> >>> >>> 2008/5/8 <exist@...>: >>>> >>>> Hi, >>>> >>>> We're currently in a project in which we're going to use exist and >>>> xquery to aggregate data from several sources like external web >>>> services, data stored in exist and data stored in RDBMS-es. >>>> >>>> We're using the httpclient module to call the external web services >>>> and are going to use the sqlmodule for getting the data from the >>>> RDBMS-es. >>>> >>>> eXist will be run from within tomcat or jetty. >>>> For the SQL connections, it would be nice if I was able to reference a >>>> jndi resource that is managed by tomcat/jetty. This way tomcat/jetty >>>> will be responsible for maintaining and cleaning up the connections to >>>> the databases and it will result in a more scalable solution as >>>> compared to opening and closing a connection for each query that is run. >>>> >>>> Since I'm no programmer, I would like to know if someone has done this >>>> already or what needs to be changed/added to the sql module to >>>> implement this. >>>> >>>> Any feedback on this idea will be much appreciated. >>>> >>>> >>>> Kind regard, >>>> >>>> Danny >>>> >>>> ------------------------------------------------------------------------- >>>> This SF.net email is sponsored by the 2008 JavaOne(SM) Conference >>>> Don't miss this year's exciting event. There's still time to save $100. >>>> Use priority code J8TL2D2. >>>> http://ad.doubleclick.net/clk;198757673;13503038;p?http://java.sun.com/javaone >>>> _______________________________________________ >>>> Exist-open mailing list >>>> Exist-open@... >>>> https://lists.sourceforge.net/lists/listinfo/exist-open >>> >>> >>> >>> -- >>> Adam Retter >>> >>> eXist Developer >>> { England } >>> adam@... >>> irc://irc.freenode.net/existdb >>> >> >> >> >> -- >> Adam Retter >> >> eXist Developer >> { England } >> adam@... >> irc://irc.freenode.net/existdb >> > > > > ------------------------------------------------------------------------- > This SF.net email is sponsored by: Microsoft > Defy all challenges. Microsoft(R) Visual Studio 2008. > http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ > _______________________________________________ > Exist-open mailing list > Exist-open@... > https://lists.sourceforge.net/lists/listinfo/exist-open > [GetJNDIConnectionFunction.java] /* * eXist SQL Module Extension GetJNDIConnectionFunction * Copyright (C) 2008 Adam Retter <adam@...> * www.adamretter.co.uk * * This program is free software; you can redistribute it and/or * modify it under the terms of the GNU Lesser General Public License * as published by the Free Software Foundation; either version 2 * of the License, or (at your option) any later version. * * This program is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the * GNU Lesser General Public License for more details. * * You should have received a copy of the GNU Lesser General Public License * along with this program; if not, write to the Free Software * Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA. * * $Id: GetConnectionFunction.java 4126 2006-09-18 21:20:17 +0000 (Mon, 18 Sep 2006) deliriumsky $ */ package org.exist.xquery.modules.sql; import java.sql.Connection; import javax.naming.Context; import javax.naming.InitialContext; import javax.sql.DataSource; import org.exist.dom.QName; import org.exist.xquery.BasicFunction; import org.exist.xquery.Cardinality; import org.exist.xquery.FunctionSignature; import org.exist.xquery.XPathException; import org.exist.xquery.XQueryContext; import org.exist.xquery.value.IntegerValue; import org.exist.xquery.value.Sequence; import org.exist.xquery.value.SequenceType; import org.exist.xquery.value.Type; /** * eXist SQL Module Extension GetJNDIConnectionFunction * * Get a connection to a SQL Database via JNDI * * @author Adam Retter <adam@...> * @serial 2008-05-19 * @version 1.2 * * @see org.exist.xquery.BasicFunction#BasicFunction(org.exist.xquery.XQueryContext, * org.exist.xquery.FunctionSignature) */ public class GetJNDIConnectionFunction extends BasicFunction { public final static FunctionSignature[] signatures = { new FunctionSignature( new QName("get-jndi-connection", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), "Open's a connection to a SQL Database. Expects a JNDI name in $a. Returns an xs:long representing the connection handle.", new SequenceType[] { new SequenceType(Type.STRING, Cardinality.EXACTLY_ONE) }, new SequenceType(Type.LONG, Cardinality.ZERO_OR_ONE)), new FunctionSignature( new QName("get-jndi-connection", SQLModule.NAMESPACE_URI, SQLModule.PREFIX), "Open's a connection to a SQL Database. Expects a JNDI name in $a, a username in $b and a password in $c. Returns an xs:long representing the connection handle.", new SequenceType[] { new SequenceType(Type.STRING, Cardinality.EXACTLY_ONE), new SequenceType(Type.STRING, Cardinality.EXACTLY_ONE), new SequenceType(Type.STRING, Cardinality.EXACTLY_ONE) }, new SequenceType(Type.LONG, Cardinality.ZERO_OR_ONE)) }; /** * GetJNDIConnectionFunction Constructor * * @param context * The Context of the calling XQuery */ public GetJNDIConnectionFunction(XQueryContext context, FunctionSignature signature) { super(context, signature); } /** * evaluate the call to the xquery get-jndi-connection() function, it is * really the main entry point of this class * * @param args * arguments from the get-jndi-connection() function call * @param contextSequence * the Context Sequence to operate on (not used here internally!) * @return A xs:long representing a handle to the connection * * @see org.exist.xquery.BasicFunction#eval(org.exist.xquery.value.Sequence[], * org.exist.xquery.value.Sequence) */ public Sequence eval(Sequence[] args, Sequence contextSequence) throws XPathException { // was a JNDI name specified? if (args[0].isEmpty()) return Sequence.EMPTY_SEQUENCE; try { Connection con = null; // get the JNDI source String jndiName = args[0].getStringValue(); Context ctx = new InitialContext(); DataSource ds = (DataSource) ctx.lookup(jndiName); // try and get the connection if (args.length == 1) { con = ds.getConnection(); } if (args.length == 3) { String jndiUser = args[1].getStringValue(); String jndiPassword = args[2].getStringValue(); con = ds.getConnection(jndiUser, jndiPassword); } // store the connection and return the uid handle of the connection return new IntegerValue(SQLModule.storeConnection(context, con)); } catch (Exception e) { throw new XPathException(e.getMessage()); } } } ------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2008. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ Exist-open mailing list Exist-open@... https://lists.sourceforge.net/lists/listinfo/exist-open |
|
|
|
|
|
Re: SQL module using JNDI resource
by Adam Retter-3
::
Rate this Message:
Okay I also just came to the same conclusion it seems Tomcat places
all of its JNDI recources in the JNDI base location of "java:comp/env" So what happens if using my original code you prefix your jndi name with "java:comp/env/" e.g. giving you - "java:comp/env/jdbc/KLNTDB" ? I suspect this is a Tomcat specific peculiarity - http://tomcat.apache.org/tomcat-5.5-doc/jndi-resources-howto.html Thanks Adam. 2008/5/20 Danny Kruitbosch <exist@...>: > Adam, > > When I used jdbc/KLNTDB, I ended up with a message stating that name "jdbc" > was not bound in the context. So I figured that the slash has to be escaped. > > Just now when I was checking the jndi name and was looking through the > tomcat docs about jndi resources I noticed a difference between their > examples and the code for the sql module. > > The GetJNDIConnectionFunction.java: > // get the JNDI source > String jndiName = args[0].getStringValue(); > Context ctx = new InitialContext(); > DataSource ds = (DataSource) ctx.lookup(jndiName); > > The tomcat docs example: > // get the JNDI source > String jndiName = args[0].getStringValue(); > Context ctx = new InitialContext(); > Context evctx = (Context) ctx.lookup("java:comp/env"); > DataSource ds = (DataSource) evctx.lookup(jndiName); > > So I changed the GetJNDIConnection to match the tomcat example, and I > recompiled and tested the same query again with using > sql:get-jndi-connection("jdbc/KLNTDB") > > and it works OK now. Although I've got no clue as to what makes the > difference. I'm no programmer, and my java knowledge is limited. I'm just > good at cutting and pasting ;) > > I've attached my version of the GetJNDIConnection.java > I'll do some further testing and I will also try to set up some sort of > test to see if using JNDI and container managed db pools will benefit > performance of the sql module. But it'll take me some time to set this up. > > Regards and thanks for the help so far. > > Danny > > Quoting Adam Retter <adam@...>: > >> Little bit confused as the query and the error message dont seem to match? >> >> The query states the jndi name as "jdbc\/KLNTDB" whereas the error >> message states the jndi name as "jdbc/KLNTDB" ?!? >> >> Thanks Adam. >> >> 2008/5/20 Danny Kruitbosch <exist@...>: >>> >>> Hi Adam, >>> >>> I've been testing and it doesn't work yet. I've setup tomcat to provide a >>> jndi resource. Tested it with a simple jsp to see if the resource was >>> available (put it into the exist context dir) and the jsp works OK. >>> >>> Next I made the following xquery: >>> xquery version "1.0"; >>> >>> declare namespace sql="http://exist-db.org/xquery/sql"; >>> >>> let $conn := sql:get-jndi-connection("jdbc\/KLNTDB") >>> return sql:execute($conn, "select * from RDCINST1.RDCCUSTOMER where >>> INASINT=913737", true()) >>> >>> stored it into the xquery dir as dbtest.xql and called >>> http://localhost:8080/exist/xquery/dbtest.xql >>> >>> Then I got the following error: >>> Error found >>> Name jdbc/KLNTDB is not bound in this Context [at line 5, column 14] >>> Java Stack Trace:Class Name Method Name File Name Line >>> org.exist.xquery.modules.sql.GetJNDIConnectionFunction eval >>> GetJNDIConnectionFunction.java 131 >>> org.exist.xquery.BasicFunction eval BasicFunction.java 68 >>> org.exist.xquery.InternalFunctionCall eval InternalFunctionCall.java >>> 49 >>> org.exist.xquery.AbstractExpression eval AbstractExpression.java >>> 59 >>> org.exist.xquery.PathExpr eval PathExpr.java 248 >>> org.exist.xquery.LetExpr eval LetExpr.java 150 >>> org.exist.xquery.BindingExpression eval BindingExpression.java >>> 158 >>> org.exist.xquery.AbstractExpression eval AbstractExpression.java >>> 59 >>> org.exist.xquery.PathExpr eval PathExpr.java 248 >>> org.exist.xquery.AbstractExpression eval AbstractExpression.java >>> 59 >>> org.exist.xquery.XQuery execute XQuery.java 217 >>> org.exist.xquery.XQuery execute XQuery.java 184 >>> org.exist.xmldb.LocalXPathQueryService execute >>> LocalXPathQueryService.java >>> 178 >>> org.exist.http.servlets.XQueryServlet process XQueryServlet.java >>> 360 >>> org.exist.http.servlets.XQueryServlet doGet XQueryServlet.java >>> 164 >>> javax.servlet.http.HttpServlet service HttpServlet.java 690 >>> javax.servlet.http.HttpServlet service HttpServlet.java 803 >>> org.apache.catalina.core.ApplicationFilterChain internalDoFilter >>> ApplicationFilterChain.java 290 >>> org.apache.catalina.core.ApplicationFilterChain doFilter >>> ApplicationFilterChain.java 206 >>> org.apache.catalina.core.StandardWrapperValve invoke >>> StandardWrapperValve.java 233 >>> >>> So apparently it does "see" the jndi resource in de context of the >>> xquery? >>> I also made a change to the GetJNDIConnectionFunction.java >>> The signature of the get-jndi-connection($a as xs:string) requested 2 >>> input >>> args and you only require 1 (only the JNDI name). I've attached the >>> changed >>> file. >>> >>> >>> >>> >>> >>> Quoting Danny Kruitbosch <exist@...>: >>> >>>> Adam, >>>> >>>> Thanks a lot! I'll check it out and let you know how it goes. >>>> >>>> Kind regards, >>>> >>>> Danny >>>> >>>> Quoting Adam Retter <adam@...>: >>>> >>>>> Danny, during some spare time at lunch today I have added in two >>>>> additional functions to the SQL module for JNDI support - >>>>> >>>>> sql:get-jndi-connection($jndiName) >>>>> sql:get-jndi-connection($jndiName, $username, $password) >>>>> >>>>> Hopefully that will help you, unfortunately I have not had the time to >>>>> test this as I have no easy access to JNDI sources. Anyways the code >>>>> is in eXits's trunk. If you can check that out and build it and give >>>>> it a test then that would be great. >>>>> >>>>> Thanks Adam. >>>>> >>>>> >>>>> 2008/5/8 Adam Retter <adam@...>: >>>>>> >>>>>> Not much is required, really you need an extra function (perhaps >>>>>> called get-jndi-connection()) in the SQLModule which would be very >>>>>> similar to the existing sql:get-connection() method. >>>>>> >>>>>> For a function such as sql:get-jndi-connection($jndiName, $username, >>>>>> $password), All it would really need to do is something like this - >>>>>> >>>>>> String dbJNDI = args[0].getStringValue(); >>>>>> String dbUser = args[1].getStringValue(); >>>>>> String dbPassword = args[2].getStringValue(); >>>>>> >>>>>> Context ctx = new InitialContext(); >>>>>> DataSource ds = (DataSource) ctx.lookup(dbJNDI); >>>>>> Connection con = ds.getConnection(dbUser, dbPassword); >>>>>> >>>>>> //get the existing connections map from the context >>>>>> HashMap connections = >>>>>> >>>>>> (HashMap)context.getXQueryContextVar(SQLModule.CONNECTIONS_CONTEXTVAR); >>>>>> if(connections == null) >>>>>> { >>>>>> //if there is no connections map, create a new one >>>>>> connections = new HashMap(); >>>>>> } >>>>>> >>>>>> //get an id for the connection >>>>>> long conID = getUID(); >>>>>> >>>>>> //place the connection in the connections map >>>>>> connections.put(new Long(conID), con); >>>>>> >>>>>> //store the updated connections map back in the context >>>>>> context.setXQueryContextVar(SQLModule.CONNECTIONS_CONTEXTVAR, >>>>>> connections); >>>>>> >>>>>> //return the uid handle of the connection >>>>>> return new IntegerValue(conID); >>>>>> >>>>>> >>>>>> 2008/5/8 <exist@...>: >>>>>>> >>>>>>> Hi, >>>>>>> >>>>>>> We're currently in a project in which we're going to use exist and >>>>>>> xquery to aggregate data from several sources like external web >>>>>>> services, data stored in exist and data stored in RDBMS-es. >>>>>>> >>>>>>> We're using the httpclient module to call the external web services >>>>>>> and are going to use the sqlmodule for getting the data from the >>>>>>> RDBMS-es. >>>>>>> >>>>>>> eXist will be run from within tomcat or jetty. >>>>>>> For the SQL connections, it would be nice if I was able to reference >>>>>>> a >>>>>>> jndi resource that is managed by tomcat/jetty. This way tomcat/jetty >>>>>>> will be responsible for maintaining and cleaning up the connections >>>>>>> to >>>>>>> the databases and it will result in a more scalable solution as >>>>>>> compared to opening and closing a connection for each query that is >>>>>>> run. >>>>>>> >>>>>>> Since I'm no programmer, I would like to know if someone has done >>>>>>> this >>>>>>> already or what needs to be changed/added to the sql module to >>>>>>> implement this. >>>>>>> >>>>>>> Any feedback on this idea will be much appreciated. >>>>>>> >>>>>>> >>>>>>> Kind regard, >>>>>>> >>>>>>> Danny >>>>>>> >>>>>>> >>>>>>> >>>>>>> ------------------------------------------------------------------------- >>>>>>> This SF.net email is sponsored by the 2008 JavaOne(SM) Conference >>>>>>> Don't miss this year's exciting event. There's still time to save >>>>>>> $100. >>>>>>> Use priority code J8TL2D2. >>>>>>> >>>>>>> >>>>>>> http://ad.doubleclick.net/clk;1987576 |
