• Changing XPagesJDBC demo from Derby to MS SQL

    By Jack Wilson 3 years ago

    What would be the best practice to convert the XPagesJDBC demo (2011-08-30 release) which uses Derby to another db backend (say MS SQL)? The "XPages Goes Relational" slide presentation "Highly Recommends" creating an OSGi plugin--should we expect that OSGi plugins for the major DBMS drivers would be available in 8.5.3 gold?

    What I'm after is the functionality of the demo database hitting MS SQL instead of Derby. Maybe someone has already written an OSGi plugin for the MS SQL sqljdbc4.jar driver?

    Thanks...jack

    • Sample MSSQL plugin

      By Andrejus Chaliapinas 3 years ago

      Hi Jack,

      Could you try this attached OSGI plugin, which actually wraps sqljdbc4.jar file with MS SQL JDBC driver and see if it works for you?



      Corresponding mssql_test.jdbc file could have such content:





      com.microsoft.sqlserver.jdbc.SQLServerDriver

      jdbc:sqlserver://testvm:1097;databaseName=pubs

      sa

      test

    • re: Sample MSSQL plugin

      By Jack Wilson 3 years ago

      Andrejus,

      That works great! I have only tried it on my local notes client, but I'll try it on the server later.

      I had already setup a copy of the JDBC demo db to hit our test MSSQL server by putting the sqljdbc4.jar file in the
      [notes client root]\jvm\lib\ext\ folder,
      so all I had to do was move the sqljdbc4.jar file out of that folder, put your file in my
      [notes client root]\framework\extlib\eclipse\plugins folder, and then start designer with the recommended parameters:

      designer -RPARAMS -clean

      Then I did a "Help-About-Plug-in details" in designer, sorting by provider to see the plugin installed:
      MS SQL driver plugin     1.0.0.201109061401 extlib.driver.mssql

      That was the only change I had to make; I got a connection the first try, and can see the data using the other samples.

      Thank you so much for providing this.

      ...jack

      • Works fine on server too

        By Jack Wilson 3 years ago

        For server:

        1. copied extlib.driver.mssql_1.0.0.201109061401.jar to:

        [domino data root]\domino\workspace\applications\eclipse\plugins

        2. Shut down domino server (stopping http wasn't sufficient to allow removal of the existing jar file in the next step).

        3. Removed sqljdbc4.jar from

        [domino program root]\jvm\lib\ext

        4. Start Domino server.

        5. Tested connection--successful!

        ...jack

        • Good,please report about all bugs found

          By Andrejus Chaliapinas 3 years ago

          I'm glad you are able now to test your application(s) with MS SQL server. Please report any bug you may find during such tests.

          Also, please let us know what do you think would be the best place on Domino server (names.nsf, other nsf or file) to define JDBC connections globally and not just per application.

          • Got a bug for you..

            By Johan k Carlsson 3 years ago

            Connection seems to work great, but when I change some things in the application and do a ‘build’ the class xpages.DBUtil can’t find the following two classes:

             

             

            import com.ibm.xsp.extlib.jdbc.dbhelper.DatabaseHelper;

            import com.ibm.xsp.extlib.util.JdbcUtil;

             

            And so it can’t be built. I have looked through my client and server file system and can’t find the files in any lib or directory? Is it so that the application was ‘built’ before deployment with the files and now they are not present?

             

            How can I get a hold of these files?

            • Those classes belong to a plugin

              By Andrejus Chaliapinas 3 years ago

              Those 2 mentioned classes:

              import com.ibm.xsp.extlib.jdbc.dbhelper.DatabaseHelper;
              import com.ibm.xsp.extlib.util.JdbcUtil;

              are located inside Extlib relational plugin, which is something like this com.ibm.xsp.extlib.relational_8.5.2.201109181457.jar

              That plugin usually resides either on your Domino server in:
              \domino\workspace\applications\eclipse\plugins

              or on Notes client in:
              \workspace\applications\eclipse\plugins

              Now, when you are saying than changing something in your application and building it cannot find them - could probably mean only if those plugins are not properly provisioned on your client side and DDE can't access them properly.

              Did you follow Extlib update site installation steps? With each release of Extlib it's usually the best to do that via File->Application->Install menu and not manually.

              In DDE you could also try Project->Clean step to rebuild whole application and see if that problem still exists.

              • Problem solved.. questions..

                By Johan k Carlsson 3 years ago

                Thanks for the tips! Unfortunately I had already tried all that. But I tried to install the latest release of extension library (14 sep 2011). And my problems went away. Maybe something went wrong in my previous installation of ext lib, but of cause both times I followed the Extlib update site installation steps. I’m running on 853 beta server and designer btw.

                 

                So now I can use the Dynamic SQL Query part of XPagesJDBC.nsf. But I have some questions that maybe you can answer.

                 

                Is there a limit of how long the result set can be? I only get the first 20 entries in any query..

                 

                And if my credentials only have reader access, what impact will it have in the result set, sorting and such?

                • update,questions

                  By Johan k Carlsson 3 years ago

                  I have found the parameter for the result set in the xpage control :).. But still, could you explain if my credentials only have reader access, what impact will it have in the result set, sorting and such?

                   

                  And another question is how can I get the pager to work. Got the following error messege when trying to go to the second page in the result set (our SQL Server is 2000):

                  Unexpected runtime error

                  The runtime has encountered an unexpected error.

                  Error source

                  Page Name:/JDBC_StaticViewPanelFile.xsp

                  Exception

                  com.ibm.xsp.FacesExceptionEx: Error while reading the relational data
                  Error while reading the relational data
                  The requested operationis not supported for forward-looking result sets.
                   

                  • Could you send more information?

                    By Andrejus Chaliapinas 3 years ago

                    Hi,
                    Could you send (attach to your response in this forum) any diagnostics file you may find either in \IBM_TECHNICAL_SUPPORT which starts with xpages or any other log file, which usually found in \domino\workspace\log which get generated during your error?

                    Also, please let us know if you are using JdbcQuery or JdbcRowSet data source while working with SQL Server 2000 and when you are getting that error - i.e. pressing Next button while on initial page or pressing Back while on some other page?

                    In regards to reader credentials implication on particular JDBC datasource operations - this is still work in progress. Right now you are getting your error not related to those credentials.

    • Could we have a sample mySQL plugin?

      By Leif Lagebrand 3 years ago

      Could we have a sample mySQL plugin? We have tested the one for SM SQL and it works fine.

      • Sample MySQL plugin

        By Andrejus Chaliapinas 3 years ago

        Hi Leif,

        Please find attached sample MySQL plugin which wraps MySQL 5.1.7 Connector's driver.

        Sample mysql.jdbc file content could be as such:

        com.mysql.jdbc.Driver

        jdbc:mysql://localhost:3306/test

        root

        test

        Hope that helps.

    • mySQL works!

      By Leif Lagebrand 3 years ago

      It works. Thank you.


      When Ext.Lib is released as 1.0. Will these plugins be released in some way? Feels like a waist of tiem if everybody would have to make them when they can be made centrally. mySQL ís released as 5.5.16. Why didn't you make a plugin of that version?

      How long does it take to updatethe plugin  from mySQL driver 5.1 to 5.5?

      Wonderful to have this easy connection to relational databases in Domino.

      • In regards to mySQL version

        By Andrejus Chaliapinas 3 years ago

        Hi Leif,

        It's great it works for you. It's still TBD (To Be Determined) in which form those plugins could be provided later if they will be made as part of some future ExtLib release. So far we are collecting all responses in this area of ExtLib functionality to understand what is the demand in regards to various databases (usage, configuration, etc).

        Now, you've asked about mySQL version 5.5.16. But it's only database version, not JDBC driver's version, which could work with that database. So far I could see that the latest one (listed on this link - http://dev.mysql.com/downloads/connector/j/) is of version 5.1.17. In my previous post I've mentioned that mySQL connector's version, not mySQL database version I think.

        If you may have any other comments/feedback - we'll appreciate them.

  • OK,here's another one,plugin for DB2?

    By Leif Lagebrand 3 years ago

    We use MS SQL, mySQL and DB2.

    • Sample DB2 plugin

      By Andrejus Chaliapinas 3 years ago

      Hi Leif,

      Please find attached sample DB2 plugin which wraps DB2 v9.7 JDBC driver.

      Sample db2.jdbc file content could be as such:

      com.ibm.db2.jcc.DB2Driver
      jdbc:db2://testvm:50000/SAMPLE
      db2admin
      test

      Hope that helps.

    • DB2 Sample plugin

      By Martin Vereecken 3 years ago

      Hi Andrejus,

      I'm also looking for your sample DB2 plugin, but I must be overlooking it, because I can't find the attachment on the page? Where can I find it? Thanks!

      Kind regards,

      Martin

      • db2/mssql/mysql updates sites reattachments

        By Jack Wilson 3 years ago

        I noticed the other day attachments in this discussion seem to disappear after so many months.

        I saved the ones that Andrejus gave us back in October 2011. I'm reattaching them here, but I wonder if they could be put in a more permanent place for others?

        I'll put each one in as a response to this.

        ...jack

        • dates reattached were 2012-04-21,not 2012-04-12 <eom>

          By Jack Wilson 3 years ago
        • We need to remove them

          By Andrejus Chaliapinas 3 years ago

          Hi Jack,

          We need to remove those attachments from this web site, because they contain JDBC drivers' jar files and thus could violate such drivers' licensing issues even for testing purposes. If you still want to reattach update sites - you need to remove such drivers' jars from them.

          I'm thinking about preparing special wizard-like Eclipse based plugin, which will allow developer/administrator to pick their JDBC driver's jar(s) and preparing required by Extension library update site. It will take some time to make such plugin, but then it will be much easier to work with.

          • Oops--sorry

            By Jack Wilson 3 years ago

            Andrejus,

            Thanks for the clarification. I saw your discussion earlier on the Oracle driver, and thought this issue only pertained to it.

            Hopefully you or someone else has the rights to delete these attachments, since I don't seem to.

            Yes, a wizard would be great. I found an article about creating them but it had quite a few steps:

            http://www-10.lotus.com/ldd/ddwiki.nsf/dx/creating_an_xpages_library

            Thanks...jack

  • How to install on Designer Client?

    By Steve Smillie 3 years ago

    How to install this MSsql.jar onto on the designer client?
    I first tried to use the File, Application, Install menu, but apparently this Jar is not setup to be installed that way
    In another post Jack suggested:
      [notes client root]\framework\extlib\eclipse\plugins folder
      Then start designer with the recommended parameters:  designer -RPARAMS -clean
    But I don't have extlib folder under framework? I tried to create one but wasn't able to make it work.

    Any suggestions?

    • You need to install ExtLib first

      By Andrejus Chaliapinas 3 years ago

      Hi,

      For provided sample mySQL/MS SQL plugins to work properly you need first to install ExtLib plugins. After installation they are usually found under \workspace\applications\eclipse\plugins folder. Into that same folder you need to copy those sample JDBC plugins.

      • Thanks,I do have the ext lib installed

        By Steve Smillie 3 years ago

        I do have the ext lib installed, using it for other things as well.

        Will this show as an installed plugin?
        Not seeing anything on the About, Plugins list.

        • You should see it then

          By Andrejus Chaliapinas 3 years ago

          If it was correctly installed and provisioned in your Lotus Notes, then via Help/About/Plug-in Details you should see column named

          Plug-in Id and if sorted by that column you should find one with Id as com.ibm.xsp.extlib.

          See image attached.

          • I know the ext lib is installed

            By Steve Smillie 3 years ago

            Checking the ext lib isn't a problem, it ios clearly there.   I was trying to see if the MsSQL and/or MySql plugins were installed. I don't see them as a plugin after placing them in the \workspace\applications\eclipse\plugins folder.  Am I missing something?

            • Cannot see any plugins in Notes 8.5.3 Gold

              By Jack Wilson 3 years ago

              I've got the same problem. I tried all 3 of the db plugins that Andrejus has provided (MSSQL, MYSQL, DB/2), and cannot get them to show in Help - About IBM Lotus Domino Designer - Plug-in Details.

              I've installed them into the directory Andrejus recommended (\data\workspace\applications\eclipse\plugins) and run the "designer - RPARAMS -clean". I also tried putting them into the directory that worked for me before with 8.5.3 cd5 (\framework\extlib\eclipse\plugins).

              I'm using the 8.5.3.201110131854NTF version, and I can confirm they are installed correctly.

              Any ideas?

              Thanks...jack

              • Sample DB2 driver update site

                By Andrejus Chaliapinas 3 years ago

                Hi,

                Could you try this update site to install in your DDE and see if you get DB2 Sample plugin listed? If so - I'll post today also update sites for sample mySQL and MS SQL drivers. Need one test box for Oracle to prepare sample update site for some driver as well.

                I believe that copy/paste approach for plugins into some directory is supposed to work mostly for server sde and for proper install on client side - it should be done mostly via update sites.

    • update site zip worked for me for db2

      By Jack Wilson 3 years ago

      Andrejus,

      Worked ok for me. I can now see it listed as a plugin (image attached).

      Looking forward to the other drivers :-)

      Thanks again!

      ...jack

  • Sample MS SQL driver update site <eom>

    By Andrejus Chaliapinas 3 years ago