• How to connect to MSSQL?

    By Thomas Adrian 3 years ago

    Reading the documentation for the new relational database support is not very easy to understand

    can someone explain to me (very easily) the steps needed to connect to a MSSQL server and do a simple query in xpages

     

    1.

    2.

    3.

    ...

     

    Thanks

    Thomas

    • Steps required

      By Andrejus Chaliapinas 3 years ago

      Hi Thomas,

      Here is the story. Currently we support per NSF Application JDBC configured connections, whose definition lies inside special file with .jdbc extension. Global JDBC connections per server and additional DDE tooling support will be introduced later.

      1. To be able to connect to your MS SQL database you would need to place Sample MS SQL plugin, provided here:

      http://www.openntf.org/internal/home.nsf/response.xsp?action=openDocument&documentId=63A74E09F265E8198625790300483F8F&MainID=C3103EC4E9FE5DDA86257901006837F1

      into your \domino\workspace\applications\eclipse\plugins directory.

      If you'd like to try locally - then better approach is to use an update site, see here:

      http://www.openntf.org/internal/home.nsf/response.xsp?action=openDocument&documentId=87EBF546D95FE7808625792F004E27B8&MainID=C3103EC4E9FE5DDA86257901006837F1

      2. Have similar as described in that link mssql_test.jdbc file placed into your NSF Application under WebContent\WEB-INF\jdbc folder. For that you may need to use DDE Navigator view (via Window/Show Eclipse Views/Navigator), because standard DDE Applications view doesn't show that .

      3. Open our sample XPagesJDBC.nsf in DDE, find JDBC_StaticViewPanelTable.xsp in it, find this section in it:

      <xe:jdbcQuery connectionName="derby1"

      var="jdbcData1" defaultOrderBy="id" sqlTable="users"

      calculateCount="true">

      xe:jdbcQuery>

       

      and change connectionName to the name of your .jdbc file and sqlTable to your, for example:

      <xe:jdbcQuery connectionName="mssql_test"

      var="jdbcData1" defaultOrderBy="id" sqlTable="mssqlusers"

      calculateCount="true">

      xe:jdbcQuery>

      4. On that same XPage change viewcolumn to retrieve values from your database, by altering these places:

      <xp:viewColumn id="viewColumn3" columnName="firstName">

       

      ang changing columnName to the name of a column/field inside your mssqlusers table.

      5. Save your work and try preview via a web browser.

      Hope that will help

      • Yes

        By Thomas Adrian 3 years ago

        Thanks, that works very well

         

        /Thomas