I want to update Database through Datasource-JDBC in ReadyAPI,Is it possible ?If yes please suggest how to update.
update ODM.PROMO set PROMO_END_TMSTMP ='2018-11-01' where PROMO_CATEGORY_TYPE='Loyalty_Offers' and PROMO_STATUS='1';
Solved! Go to Solution.
You use a JDBC test step to execute your update query.
You need tocreate the db connection before though.
You will need to id the following:
What RDBMS are you connecting to?
What thin driver .jar files do you need to create the db connection?
What version of java hace you installed and update if the driver .jar files need a different version of java to what is installed.
After youve id'd all above you will need to ensure the correct java version is in your windows env. Classpath, copy the driver .jar files to ext/lib and configure your db connection in the jdbc connection details form in the preferences.
Once youve configured your connection you can then run your update via thr sql you specified in the jdbc test step.
Hope this was clear,
Thanks for your response.
I already did this all connection(check SS-1).my question is am able to update or not because when i tried to update i got below error.
"Can't get the Connection for specified properties; com.ibm.db2.jcc.am.SqlException: [jcc][3.61.65] Method executeQuery cannot be used for update. ERRORCODE=-4476, SQLSTATE=null"
Youre using DB2 and ive updated a DB2 record via soapui before now.
However. Youre getting a standard error response passed by your RDBMS.
Just because you can SELECT on a particular table doesnt mean that you have update privileges on the table for the useraccount you are using in your db connection.
You need to confirm if the useraccount you are using to connect to your RDBMS also includes a grant to update on the table you are trying to update. Without any other info, Im guessing "no" is the answer. I.e. the screenshots you provide are for a successful SELECT and an unsuccessful UPDATE.
Easiest way to check is to run the update via a db interrogation tool, but ensure to use the connection details you are using to connect in soapui.
ok - I'm guessing it likely to be a problem with the syntax then.
Have you confirmed that you can run a different query from the same JDBC step?
e.g. SELECT DATE(CURRENT TIMESTAMP)
I'm suggesting the above - just to ensure there's nothing wrong with the step object itself.
Have you tried running a different UPDATE query on the same JDBC step (something simpler that doesn't actually update anything)?
Possible changes I would make to the query are as follows:
1. Remove the semi colon at the end of your query.....yes I agree a semi colon is used to end a clause in SQL but I stopped using semi colons in SoapUI when they were causing me problems.
2. Secondly - do you need to specify the ODM schema in front of the PROMO table? if you're already connecting to the correct schema, this isn't necessary. the only reason I'm guessing you are using the schema alias would be if the schema/user you are using to connect is not the ODM schema....is this correct? If you can dump it - get rid of the schema alias.
3. Thirdly I'd to_date the PROMO_END_TMSTMP value - I never really had problems with dates in SoapUI in DB2, but I do with Oracle and so I just got into the habit of using to_date- adds a little more control
4. Fourthly - can you confirm PROMO_STATUS attribute is a varchar/char type rather than a numeric? Currently looks like a text value considering you've wrapped the value in quote marks.
set PROMO_END_TMSTMP = to_date('2018-11-01', '%Y-%m-%d')
I had a quick google and the error code returned occurs when the resultset is empty - can you confirm you have records where PROMO_CATEGORY_TYPE='Loyalty_Offers' and PROMO_STATUS='1'????
If you can lay out what the problem was as well as the solution you found (including any mentions to datasources/datasinks which werent mentioned at all in the original postings) this could help other people should they face this issue in the future.