Re: SQL module using JNDI resource

> 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;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
>>>>
>>>
>>>
>>>
>>
>>
>>
>> --
>> Adam Retter
>>
>> eXist Developer
>> { England }
>> adam@...
>> irc://irc.freenode.net/existdb
>>
>
>
>