• Parametrized query in jdbcRowset datasource

    By Sachin Kulkarni 10 years ago

    I want to write a parametrized query while using jdbcRowset as a datasource in panel. When I hardcode the value, it works as shown below it works. SELECT * FROM AB.CDEF WHERE USER_ID='abcdef'

    How do I pass parameters to the query ?

    SELECT * FROM AB.CDEF WHERE USER_ID=? while using in jdbcRowSet datasource

    • By B Gleeson 10 years ago

      In the jdbcRowSet property panel, set the sqlQuery property to your query “SELECT * FROM AB.CDEF WHERE USER_ID=?”

      Then, in the sqlParameters property add the 'abcdef'. The abcdef sqlParameter will replace the ? in the sqlQuery.

      You can add as many ?'s to your sqlQuery string as you like, as long as you have the corresponding number of values added to the sqlParameters property to replace them. The order of the sqlParameters is the order that they will be added to the sqlQuery string.

      • By Sachin Kulkarni 10 years ago

        Hi Gleeson,

        I have tried the same but it doesnt work. Throwing error.

        Error while reading the relational data

        DB2 SQL Error: SQLCODE=-313, SQLSTATE=07001, SQLERRMC=null, DRIVER=3.57.82

        I am subclassing the connection manager as discussed in the topic “Dynamic username password in jdbc file”


        Below is the code i am using for this



        <xe:jdbcRowSet var=“jdbcRowSet1”

        sqlQuery="select * from comp.users where user_id='?'"
        connectionManager="heerosConnectionManager1">
        <xe:this.sqlParameters>
         <xe:sqlParameter loaded="true" value="A6B234"></xe:sqlParameter>
        </xe:this.sqlParameters>
        


        <xp:this.value><![CDATA[#{javascript:var count=jdbcRowSet1.getCount();
        var list=new java.util.ArrayList();
        for(i=0;i<count; i++){
         list.add(jdbcRowSet1.getRow(i).USER_ID)
        }
        list}]]></xp:this.value>
        



        I have tried ? with and without quotes. but doesnt work.

        This works fine when I hardcode the user_id instead of passing it as parameter. Any ideas please ?

        • By B Gleeson 10 years ago

          Sachin, you've found a bug in the jdbcRowSet data source when using parameters. I tried the example from your last comment, got the same problem as you when using jdbcRowset, but it works fine if I use a jdbcQuery data source instead.

          I found this had previously been reported here, but hadn't been reported/seen by IBM: http://stackoverflow.com/questions/14833445/how-do-sql-parameters-in-xpages-work-when-leveraging-jdbc

          Some googling tells me the error code indicates: SQLCODE = -313, ERROR: THE NUMBER OF HOST
          VARIABLES SPECIFIED IS NOT EQUAL TO THE NUMBER OF
          PARAMETER
          MARKERS

          The issue is now being tracked as SPR#BGLN9N4CZU. If a fix is found for this, I will update here and in the stackoverflow link, to let you know what build of the ExtLib the fix will be in.

          Thanks.

          • By Sachin Kulkarni 10 years ago

            Thank you for the information ! I tried using jdbcQuery also.. but looks like am doing something wrong.. below is the code.. which just reads the query from jdbcQuery datasource.. and instead of giving full query with the parameter replaced, it just displays with question mark.

            ---------

            Below is a little peice of code using jdbcQuery




            <xe:jdbcQuery var=“jdbcQuery1”

            connectionManager="heerosConnectionManager1"
            sqlQuery="select USER_ID from cd.vusers where user_id=?">
            <xe:this.sqlParameters>
             <xe:sqlParameter loaded="true" value="S6X687"></xe:sqlParameter>
            </xe:this.sqlParameters>
            




            <xp:this.value><![CDATA[#{javascript:jdbcQuery1.getQuery();}]]></xp:this.value>
            



            • By B Gleeson 10 years ago

              The getQuery method will simply return the stored sqlQuery property. The same String as it was entered in Designer, so it is working correctly in your example. In the current implementation, the sqlParameters & sqlQuery properties are always stored separately, so there is no way to retrieve the completed query, with parameters injected, with a simple method call like you've described above.

              The way it works is that whenever the jdbcQuery data source is asked to retrieve data, it constructs a java.sql.PreparedStatement object. This is where the sqlParameters are injected into the sqlQuery. The PreparedStatement is executed and the resulting ResultSet returned. See com.ibm.xsp.extlib.relational.jdbc.model.JdbcDataBlockAccessor.loadBlock() source code.

              If you use a jdbcQuery data source with sqlQuery + sqlParameters with a view control, it will work correctly. But as I said, there is a bug in jdbcRowSet that is causing an error.

              • By Sachin Kulkarni 10 years ago

                Thank you for the answer Gleeson. I tried using it as below--jdbcQuery1.getColumnValue("USER_ID"); But it throws error saying--Unknown member 'getColumnValue' in Java class 'com.ibm.xsp.extlib.jdbc.model.JdbcDataBlockAccessor'

                • By B Gleeson 10 years ago

                  You'll need to access it in the following way:
                  jdbcQuery1.get(0).getColumnValue(“USER_ID”);

                  The jdbcQuery object contains 0 or more JDBCRow objects depending on how many results are found using the sqlQuery. The JDBCRow object is what provides the getColumnValue() method.

                  So first, get the JDBCRow using jdbcQuery1.get(int index), then call getColumnValue(String name) on the returned row.

                  This is handled for you in a view. For example, in a dataView, specify the var property on the view, e.g. var=“row”, then you can reference that in the view like so, row.getColumnValue(“USER_ID”);

                  I'd recommend reading the relational chapter (Ch. 12, P. 377) in the XPages Extension Library book, if you haven't already, as that will cover this kind of thing

                  • By Sachin Kulkarni 10 years ago

                    Thank you so much :) !! worked perfectly !

                    • By B Gleeson 10 years ago

                      The issue reported and logged above has now been fixed in Release 9 of v901 XPages Extension Library

                      The issue is now being tracked as SPR#BGLN9N4CZU - RDBMS | jdbcRowSet throws exception when using sqlParameters & sqlQuery properties “

                      • By Sachin Kulkarni 10 years ago

                        Thank you for the udpate !