Topic
11 replies Latest Post - ‏2012-11-20T13:19:02Z by SystemAdmin
SystemAdmin
SystemAdmin
2327 Posts
ACCEPTED ANSWER

Pinned topic DB2 Adapter ?

‏2012-11-20T05:47:03Z |
Hi,

I am trying to connect with Db2 by selecting SQL Adapter option and makes suitable changes in .xml and .js file by changing driver class name and var " procedure1Statement = WL.Server.createSQLStatement("select EMPLOYEEID, EMPLOYEENAME from EMP");" in JS file. when i Invoke Procedure by right click on Adapter nothing appears on eclipse.

could you please tell how can i connect and fetch data from DB2 in Worklight.
  • Somasundaramrk
    Somasundaramrk
    49 Posts
    ACCEPTED ANSWER

    Re: DB2 Adapter ?

    ‏2012-11-20T05:55:09Z  in response to SystemAdmin
    HI,

    Please check the worklight console in eclipse.
    • SystemAdmin
      SystemAdmin
      2327 Posts
      ACCEPTED ANSWER

      Re: DB2 Adapter ?

      ‏2012-11-20T06:28:55Z  in response to Somasundaramrk
      Hi,
      
      getting the following error now... 
      { 
      "errors": [ 
      "Runtime: org.apache.commons.dbcp.SQLNestedException: Cannot create JDBC driver of class 'com.mysql.jdbc.Driver' for connect URL 'jdbc:db2:TEST'" ], 
      "info": [ ], 
      "isSuccessful": false, 
      "warnings": [ ] 
      }
      
      • Somasundaramrk
        Somasundaramrk
        49 Posts
        ACCEPTED ANSWER

        Re: DB2 Adapter ?

        ‏2012-11-20T06:08:40Z  in response to SystemAdmin
        Can you share adapter folder.

        Check the following 2 things,
        1. Did you add db2 jar in server->lib folder?
        2. Did you configure correctly in the adapter xml and worklight.properties file?
        • SystemAdmin
          SystemAdmin
          2327 Posts
          ACCEPTED ANSWER

          Re: DB2 Adapter ?

          ‏2012-11-20T06:28:31Z  in response to Somasundaramrk
          this is my .js file which contains..
          
          var procedure1Statement = WL.Server.createSQLStatement(
          "select EMPLOYEEID, EMPLOYEENAME from EMP"); function procedure1(param) 
          { 
          
          return WL.Server.invokeSQLStatement(
          { preparedStatement : procedure1Statement, parameters : [param] 
          }); 
          }
          


          and .xml file is..
          
          <?xml version=
          "1.0" encoding=
          "UTF-8"?>   <wl:adapter name=
          "mySQLAdapter" xmlns:xsi=
          "http://www.w3.org/2001/XMLSchema-instance" xmlns:wl=
          "http://www.worklight.com/integration" xmlns:sql=
          "http://www.worklight.com/integration/sql">   <displayName>mySQLAdapter</displayName> <description>mySQLAdapter</description> <connectivity> <connectionPolicy xsi:type=
          "sql:SQLConnectionPolicy"> <!-- Example 
          
          for using a JNDI data source, replace with actual data source name --> <!-- <dataSourceJNDIName>java:/data-source-jndi-name</dataSourceJNDIName> --> <!-- Example 
          
          for using MySQL connector, 
          
          do not forget to put the MySQL connector library in the project
          's lib folder --> <dataSourceDefinition> <driverClass>com.mysql.jdbc.Driver</driverClass> <!--<driverClass>COM.ibm.db2.jdbc.app.DB2Driver</driverClass>--> <url>jdbc:db2:TEST</url> <user>root</user> <password>root</password> </dataSourceDefinition> </connectionPolicy> <loadConstraints maxConcurrentConnectionsPerNode=
          "5" /> </connectivity>   <!-- Replace 
          
          this with appropriate procedures --> <procedure name=
          "procedure1"/> <procedure name=
          "procedure2"/>   </wl:adapter>
          


          and worklight property file is..
          
          ####################################################################################################################### #    Worklight properties file # #   NOTICE! #       Keys and values in 
          
          this file are CASE-SENSITIVE! # #   Values in 
          
          this template are the 
          
          default values. #######################################################################################################################     ####################################################################################################################### #       Idle session timeout in minutes ####################################################################################################################### #serverSessionTimeout=30   ####################################################################################################################### #       Public Worklight Server address (In a clustered environment, set to the load balancer
          's address) ####################################################################################################################### #publicWorkLightHostname=localhost # http or https #publicWorkLightProtocol=http # For 
          
          default port leave empty #publicWorkLightPort=8080 #publicWorkLightContext=/    ####################################################################################################################### #  DB Settings ####################################################################################################################### # jndi name; empty value means Apache DBCP data source #wl.db.jndi.name=   # For MySQL #wl.db.type=MYSQL #wl.db.url=jdbc:mysql:
          //localhost:3306/Worklight # For Derby #wl.db.type=DERBY #wl.db.url=jdbc:derby:$
          {worklight.home
          }/derby/WorklightDB;create=
          
          true #wl.reports.db.url=jdbc:derby:$
          {worklight.home
          }/derby/WorklightReportsDB;create=
          
          true # For HSQL #wl.db.type=HSQL #wl.db.url=jdbc:hsqldb:file:$
          {worklight.home
          }/hsqldb/WorklightDB #wl.reports.db.url=jdbc:hsqldb:file:$
          {worklight.home
          }/hsqldb/WorklightReportsDB # For DB2 #wl.db.type=DB2 #wl.db.url=jdbc:db2:TEST # For Oracle #wl.db.type=ORACLE #wl.db.url=jdbc:oracle:thin:@localhost:1521:SID   #wl.db.username=root #wl.db.password=root   # Default values 
          
          for DBCP connection pool #wl.db.initialSize=10 #wl.db.maxActive=10 #wl.db.maxIdle=10 #wl.db.testOnBorrow=
          
          true   #wl.db.autoddl=
          
          true   ####################################################################################################################### #   Raw reports ####################################################################################################################### #reports.exportRawData=
          
          false # # jndi name; empty value means Apache DBCP data source #wl.reports.db.jndi.name=$
          {wl.db.jndi.name
          } # Default values 
          
          for DBCP connection pool #wl.reports.db.initialSize=$
          {wl.db.initialSize
          } #wl.reports.db.maxActive=$
          {wl.db.maxActive
          } #wl.reports.db.maxIdle=$
          {wl.db.maxIdle
          } #wl.reports.db.testOnBorrow=$
          {wl.db.testOnBorrow
          } #wl.reports.db.type= #wl.reports.db.url= #wl.reports.db.username= #wl.reports.db.password=   ####################################################################################################################### #   bit.ly credentials (
          
          for console) ####################################################################################################################### #bitly.username= #bitly.apikey=   ####################################################################################################################### #      Push C2DM proxy settings ####################################################################################################################### #push.c2dm.proxy.enabled=
          
          false # protocol may be either http or https #push.c2dm.proxy.protocol= #push.c2dm.proxy.host= # negative value means 
          
          default port #push.c2dm.proxy.port=-1 # empty user means no authentication #push.c2dm.proxy.user= #push.c2dm.proxy.password=   ####################################################################################################################### # Push APNS proxy settings ####################################################################################################################### #push.apns.proxy.enabled=
          
          false # only SOCKS proxy is supported at the moment #push.apns.proxy.type=SOCKS #push.apns.proxy.host= #push.apns.proxy.port=   ####################################################################################################################### #    WS-Security ####################################################################################################################### #ws-security.keystore.path= #ws-security.keystore.type=PKCS12 #ws-security.keystore.password=worklight     ####################################################################################################################### # Protecting the Worklight Console ####################################################################################################################### # You can protect the Worklight Console by defining user credentials required to access it. # These settings can also be encrypted as described in Storing Properties in Encrypted Format. # In addition to defining these two properties, you should also configure the authenticationConfig.xml file, # located under <Worklight Root Directory>\server\conf, as described in the Developer
          's Reference Guide. #console.username= #console.password=
          
          • Somasundaramrk
            Somasundaramrk
            49 Posts
            ACCEPTED ANSWER

            Re: DB2 Adapter ?

            ‏2012-11-20T06:40:18Z  in response to SystemAdmin
            <driverClass>com.mysql.jdbc.Driver</driverClass>

            Instead of the above line add the db2 driver. (COM.ibm.db2.jdbc.app.DB2Driver : DB2 JDBC Type 2 Driver)

            db2jcc.jar and db2jcc_license_cu.jar (Both of these jars must be included in the server->lib folder)
          • SystemAdmin
            SystemAdmin
            2327 Posts
            ACCEPTED ANSWER

            Re: DB2 Adapter ?

            ‏2012-11-20T06:46:14Z  in response to SystemAdmin
            First off, you are not passing any params, so change your call:

            var procedure1Statement = WL.Server.createSQLStatement("select EMPLOYEEID, EMPLOYEENAME from EMP");
            function procedure1() {
            return WL.Server.invokeSQLStatement({
            preparedStatement : procedure1Statement,
            parameters : []
            });
            }

            Second, why did you comment out the DB2 driver? You are using the MySQL driver:

            <driverClass>com.mysql.jdbc.Driver</driverClass>
            <!--<driverClass>COM.ibm.db2.jdbc.app.DB2Driver</driverClass>-->

            Third, this is not an appropriate db url, that I know:

            <url>jdbc:db2:TEST</url>

            Should be like this:

            <url>jdbc:mysql://localhost:3306/TEST</url>

            I am assuming you would be using port 3306, which is the standard for MySQL, I image that DB2 is the same. I am assuming the server is running in your machine, otherwise, you need an actual IP address like 199.XXX.XXX.XXX

            Lastly, you don't need anything in worklight.properties for the SQL adapter, so leave everything commented it out like you have it.

            Ralph Pina
            • SystemAdmin
              SystemAdmin
              2327 Posts
              ACCEPTED ANSWER

              Re: DB2 Adapter ?

              ‏2012-11-20T07:12:15Z  in response to SystemAdmin
              i have made the above changes said(added the jar and all others.).. but now when i am Invoking the procedure by right click on Adapter no message of error/Successful appears on the eclipse console.
              • SystemAdmin
                SystemAdmin
                2327 Posts
                ACCEPTED ANSWER

                Re: DB2 Adapter ?

                ‏2012-11-20T07:22:08Z  in response to SystemAdmin
                Did you redeployed it?
                • SystemAdmin
                  SystemAdmin
                  2327 Posts
                  ACCEPTED ANSWER

                  Re: DB2 Adapter ?

                  ‏2012-11-20T08:43:31Z  in response to SystemAdmin
                  yes i redeployed it, still same issue.. :(
      • SystemAdmin
        SystemAdmin
        2327 Posts
        ACCEPTED ANSWER

        Re: DB2 Adapter ?

        ‏2012-11-20T06:13:17Z  in response to SystemAdmin
        Apart from the implementation question above:

        Is DB2 in the same machine, or a remote machine?

        I would start by trying to login to DB2 and execute the command with the username and password.

        If that works, make sure DB2 can be access from outside your localhost. I had a similar error in MySQL, had to change the my.conf file. DB2 may have something similar.

        Ralph Pina