• Can't Execute Query in MSSQL using JDBC in XPages

    By Thomas Adrian 1 decade ago

    I am using a JDBC Microsoft SQL driver in an xpage to get data from MS SQL server The driver is installed using an update site and it is loaded successfully into the OSGI

    I have also added a sqlserver.jdbc containing credentials to the webcontent\webinf\jdbc folder in the application

    When I try to access the sql tables using @JdbcDbColumn it is working fine

    var con=@JdbcGetConnection("sqlserver");

    @JdbcDbColumn(con,“PrTr”,“Descr”)

    But when I try to execute a query

    var con=@JdbcGetConnection(“sqlserver”);

    var query=“SELECT * FROM PrTr”;

    var rs=@JdbcExecuteQuery(con,query);

    ….

    I get this

    java.lang.ArrayIndexOutOfBoundsException

    • By Thomas Adrian 1 decade ago

      This is the xpages not working

      <?xml version="1.0" encoding="UTF-8"?>


      <xp:text escape="true" id="computedField1">
    <xp:this.value><![CDATA[#{javascript:var con=@JdbcGetConnection("sql2");

      var query=“SELECT * FROM ProdTr Where TrNo=10”;
      var rs = @JdbcExecuteQuery(con,query);
      var resultArray=new Array();
      resultArray[0]= ““;
      var i=1;
      while (rs.next()) {
      resultArray[i]=rs.getString(“ColumnName”);

      i++;

      }
      return resultArray;

      }]]>

      </xp:text></xp:view>
      • By B Gleeson 1 decade ago

        Hi Thomas. Could you try fully scoping your SQL statement for the relevant schema?

        For example, if the schema is called 'foo' and the table is 'bar', then use this:
        “SELECT * FROM foo.bar”

        Let us know if that has any effect.

    • By Thomas Adrian 1 decade ago

      I have also tried to add the sql driver directly to the nsf,
      same result @jdbcDbColumn is working but not @JdbcExecuteQuery

    • By Thomas Adrian 1 decade ago