cancel
Showing results for 
Search instead for 
Did you mean: 

datasource-JDBC-Query

SOLVED
Highlighted
Contributor

datasource-JDBC-Query

I want to update Database through Datasource-JDBC in ReadyAPI,Is it possible ?If yes please suggest how to update.

Ex:-

update ODM.PROMO set PROMO_END_TMSTMP ='2018-11-01' where PROMO_CATEGORY_TYPE='Loyalty_Offers' and PROMO_STATUS='1';

1 ACCEPTED SOLUTION

Accepted Solutions
Contributor

Re: datasource-JDBC-Query

data source is for sourcing data. i tried with data sink with below query-its working fine.

 

update ODM.PROMO set PROMO_END_TMSTMP ='2018-11-01' where PROMO_CATEGORY_TYPE='Loyalty_Offers' and PROMO_STATUS='1'

9 REPLIES 9
Community Hero

Re: datasource-JDBC-Query

Yep its completely straightforward.

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,

Cheers,

Richie
if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the title of the post to something more descriptive? This will help people when searching for problems.
Contributor

Re: datasource-JDBC-Query

Hi,

 

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][10103][10941][3.61.65] Method executeQuery cannot be used for update. ERRORCODE=-4476, SQLSTATE=null"

Community Hero

Re: datasource-JDBC-Query

The query type doesnt make any difference.
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.

Cheers,

Richie
if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the title of the post to something more descriptive? This will help people when searching for problems.
Contributor

Re: datasource-JDBC-Query

I have update access in DB(attached SS) and same username and password i used to connect datasource.

 

Community Hero

Re: datasource-JDBC-Query

spooky.

 

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)
FROM SYSIBM.SYSDUMMY1;

 

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.

 

update PROMO

set PROMO_END_TMSTMP = to_date('2018-11-01', '%Y-%m-%d')

where PROMO_CATEGORY_TYPE='Loyalty_Offers'

and PROMO_STATUS='1'

 

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'????

 

 

Cheers,

 

richie

 

 

if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the title of the post to something more descriptive? This will help people when searching for problems.
Contributor

Re: datasource-JDBC-Query

data source is for sourcing data. i tried with data sink with below query-its working fine.

 

update ODM.PROMO set PROMO_END_TMSTMP ='2018-11-01' where PROMO_CATEGORY_TYPE='Loyalty_Offers' and PROMO_STATUS='1'

Community Hero

Re: datasource-JDBC-Query

Im totally lost. You never mentioned datasource or datasinks before....what do they have anything to do with it?

Confused.....

Rich
if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the title of the post to something more descriptive? This will help people when searching for problems.
Contributor

Re: datasource-JDBC-Query

thanks for your time.

Community Hero

Re: datasource-JDBC-Query

Hi,

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.

Cheers,

Rich


if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the title of the post to something more descriptive? This will help people when searching for problems.
New Here?
Join us and watch the welcome video:
Watch the new Interview
Top Kudoed Authors