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

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

    Re: DB2 Adapter ?

    ‏2012-11-20T05:55:09Z  
    HI,

    Please check the worklight console in eclipse.
  • Somasundaramrk
    Somasundaramrk
    49 Posts

    Re: DB2 Adapter ?

    ‏2012-11-20T06:08:40Z  
    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": [ ] 
    }
    
    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

    Re: DB2 Adapter ?

    ‏2012-11-20T06:13:17Z  
    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": [ ] 
    }
    
    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
  • SystemAdmin
    SystemAdmin
    2327 Posts

    Re: DB2 Adapter ?

    ‏2012-11-20T06:28:31Z  
    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?
    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=
    
  • SystemAdmin
    SystemAdmin
    2327 Posts

    Re: DB2 Adapter ?

    ‏2012-11-20T06:28:55Z  
    HI,

    Please check the worklight console in eclipse.
    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

    Re: DB2 Adapter ?

    ‏2012-11-20T06:40:18Z  
    this is my .js file which contains..
    <pre class="jive-pre"> var procedure1Statement = WL.Server.createSQLStatement( "select EMPLOYEEID, EMPLOYEENAME from EMP"); function procedure1(param) { return WL.Server.invokeSQLStatement( { preparedStatement : procedure1Statement, parameters : [param] }); } </pre>

    and .xml file is..
    <pre class="jive-pre"> <?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> </pre>

    and worklight property file is..
    <pre class="jive-pre"> ####################################################################################################################### # 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= </pre>
    <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

    Re: DB2 Adapter ?

    ‏2012-11-20T06:46:14Z  
    this is my .js file which contains..
    <pre class="jive-pre"> var procedure1Statement = WL.Server.createSQLStatement( "select EMPLOYEEID, EMPLOYEENAME from EMP"); function procedure1(param) { return WL.Server.invokeSQLStatement( { preparedStatement : procedure1Statement, parameters : [param] }); } </pre>

    and .xml file is..
    <pre class="jive-pre"> <?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> </pre>

    and worklight property file is..
    <pre class="jive-pre"> ####################################################################################################################### # 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= </pre>
    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

    Re: DB2 Adapter ?

    ‏2012-11-20T07:12:15Z  
    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
    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

    Re: DB2 Adapter ?

    ‏2012-11-20T07:22:08Z  
    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.
    Did you redeployed it?
  • SystemAdmin
    SystemAdmin
    2327 Posts

    Re: DB2 Adapter ?

    ‏2012-11-20T08:43:31Z  
    Did you redeployed it?
    yes i redeployed it, still same issue.. :(
  • SystemAdmin
    SystemAdmin
    2327 Posts

    Re: DB2 Adapter ?

    ‏2012-11-20T13:19:02Z  
    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
    getting the following error while deploying Adapter...

    2012-11-20 18:50:26 Activation done.
    2012-11-20 18:50:26 Tue Nov 20 18:50:26 IST 2012
    2012-11-20 18:50:26 Starting adapter deployment on Worklight Server
    2012-11-20 18:50:26 Deploying adapter: mySQLAdapter
    2012-11-20 18:50:26 Server host: localhost
    2012-11-20 18:50:26 Server port: 8080
    2012-11-20 18:50:26 null
    2012-11-20 18:50:26 Adapter deployment failed
    2012-11-20 18:50:26 ERROR