1

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.
Pawankumar Dubey
  • 387
  • 1
  • 6
  • 21
  • Is `DATABASE_NAME` really the name of your db/schema? That doesn't seem like a very clear/useful name. The db is complaining that the `registration` table doesn't exist (or it can't find it, anyways), so it's probably not a user-table issue. I don't know enough about Worklight - is the js distributed to clients? Doing so would seem to be a recipe for disaster if you let clients run arbitrary SQL statements. This is otherwise a clearly asked question - I wish more people would provide the full text of error encountered, much less steps they tried... – Clockwork-Muse Jul 22 '14 at 06:54
  • I think, i did mistake with question a bit let me clarify that Database_Name=MOBILEDB, SCHEMA_NAME=LARSEN(Dummy Name), Table_NAME=registration. Here in Woklight Adapter.xml and its corresponding js file resides on server and client invokes these adapter via procedure(Callback Function). Well Eclipse(Worklight) provide feature to directly invoke this methods with parameter and result is displayed on html filem, so whole big story to test adapters could be minimized. – Pawankumar Dubey Jul 22 '14 at 07:27

2 Answers2

1

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

djna
  • 54,992
  • 14
  • 74
  • 117
  • Yeas, Its LARSEN.registration; I directly ran the Select statement and same error i am getting on Data Studio console. Tried all possible Permo-Combo like Schema only: SELECT * FROM LARSEN.registration where LARSEN.registration.Mob_No = ? | DB name+Schema only: SELECT * FROM MOBILEDB.LARSEN.registration where MOBILEDB.LARSEN.registration.Mob_No = ? | DBNAME only: SELECT * FROM MOBILEDB.registration where MOBILEDB.registration.Mob_No = ? Direct table: SELECT * FROM registration where registration.Mob_No = ? – Pawankumar Dubey Jul 22 '14 at 08:40
  • So now we are clear. This is not a worklight issue, this is a DB/SQL issue. Use the db2 command center to build your query. Try the simplest query: SELECT * FROM SCHEMA.TABLE - in your case this should be SELECT * FROM LARSEN.registration - but I wonder whether this is case sensitive? Hence my suggestion - use command centre to build the query. – djna Jul 22 '14 at 09:15
  • Yeas, it is related to DB2. Finally i was able to run the same on DB2 using "SELECT * FROM LARSEN."registration" where LARSEN."registration"."Mob_No" = 9930667660;" but i am stuck in framing the same in my JS file. I tried with var selectStatement = 'SELECT * FROM LARSEN."registration" where LARSEN."registration"."Mob_No" = ?'; but in return i am getting the same error with slashes added "Runtime: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=LARSEN.registration, DRIVER=3.58.82.\nPerformed query:\nSELECT * FROM LARSEN.\"registration\" where LARSEN.\"registration\".\"Mob_No\" = ?" – Pawankumar Dubey Jul 22 '14 at 10:14
  • I'm going to research further. We've shown that registration is lower case and as such it seems we need to put it in quotes. My practice when creating databases is always to use UPPER_CASE for all tables and columns. If you have any control over the DB you might try that, you would then not need quotes. However I think what you're trying should be possible. We're getting close to needing a new question: How do you enter worklight adapter database queries for tables with lower case names? – djna Jul 22 '14 at 11:59
  • Please try one thing: SELECT * FROM LARSEN.REGISTRATION as your query; we'll deal with the where clause if that works – djna Jul 22 '14 at 12:02
0

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.

Community
  • 1
  • 1
Pawankumar Dubey
  • 387
  • 1
  • 6
  • 21