Getting DB2 SQL Error while firing a select query using Worklight 6.1.0?

Question

I am trying to connect to DB2 in my local LAN using worklight 6.1.0 and firing a Select Query for lookup of data if exist. But i am getting below error:

{
       "errors": [
          "Runtime: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=DATABASE_NAME.REGISTRATION, DRIVER=3.58.82.\nPerformed query:\nSELECT * FROM DATABASE_NAME.registration where DATABASE_NAME.registration.Mob_No = ?"
       ],
       "info": [
       ],
       "isSuccessful": false,
       "warnings": [
       ]
    }

My SQL adapter configuration looks like below:

<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.ibm.db2.jcc.DB2Driver</driverClass>
                <url>jdbc:db2://172.21.11.129:50000/MOBILEDB</url>
                <user>db2admin</user>
                <password>Newuser123</password>
            </dataSourceDefinition>
        </connectionPolicy>

And js file which has procedure looks like:

var selectStatement1 = "SELECT * FROM DATABASE_NAME.registration where DATABASE_NAME.registration.Mob_No = ?";
var procStmt1 = WL.Server.createSQLStatement(selectStatement1);

function registrationLookup(mobile){

WL.Logger.debug("Inside registrationLookup");

return WL.Server.invokeSQLStatement(
        {
            preparedStatement : procStmt1,
            parameters : [mobile]
        }
);
}

I did some Research about connecting DB2 with Worklight and came to know that i need to put below data in worklight.properties file.

wl.db.username=db2admin
wl.db.type=DB2
wl.db.password=Newuser123
wl.db.driver=com.ibm.db2.jcc.DB2Driver

But after adding it, i am not able to deploy Adapter and error says 'db2admin' does not exist. So i have skipped this step in the context of current question. But after going through error which i am getting without adding this worklight.properties data it seems to me that 'Object doesn't exist' as per http://www-01.ibm.com/support/docview.wss?uid=swg21613531 or user table does not exist. Any suggestion would be helpful. NOTE:

1. My IP address is 172.21.11.125 from where i am invoking Adapter for DB2.
2. DB2 instance is running on 172.21.11.129 @ 50000.
3. Already Added db2jcc_license_cu_9.5.jar & db2jcc_9.5.jar in server/lib. It had name appended with '_9.5' which i have removed from both jar and kept only db2jcc_license_cu.jar and db2jcc.jar.

Answer 1

Got Answer to my own Question and its really interesting one. Thanks to https://stackoverflow.com/users/2260967/glen-misquith [Glen Misquith]

Problem was with SQL Query framing which is Different what i did with MYSQL.

Adapter's Java Script file:

var selectStatement4 = "UPDATE \"LARSEN\".\"registration\" SET \"LARSEN\".\"registration\".\"Pass\"=?, \"LARSEN\".\"registration\".\"Re_Pass\"=? WHERE \"User_Name\" = ?";
var procStmt5 = WL.Server.createSQLStatement(selectStatement4);

function updatePassword(username,pass,repass){

WL.Logger.debug("Inside updatePassword "+username+" "+pass+" "+repass);

return WL.Server.invokeSQLStatement(
        {
            preparedStatement : procStmt5,
            parameters : [pass,repass,username]
        }
);
}

This is quite a Strange thing Slashes need to be used in SQL Statement while Preparing it. I would really like to understand this behavior of DB2. And also i cant directly write 'Select * from schema.table_name' and precisely write column name from which data needs to be fetched.

Answer 2

The error message is saying that your SQL statement is invalid, and I therefore infer that your connection to the DB is fine.

To diagnose this first run the SQL using DB2 command line or other tools. My guess is you mean

  LARSEN.registration

whereas you are saying DATABASE_NAME.registration