cancel
Showing results for 
Search instead for 
Did you mean: 

Not able to use update query in data sink- ReadyAPI

SOLVED
Occasional Contributor

Not able to use update query in data sink- ReadyAPI

Not able to use update query in data sink. It always throws error "java.lang.exceptionSmiley FrustratedQL error occured".

Below is the query i am using(MySql DB):

 

update table_out set status="Trans" where destination="12568"

 

Currently i am blocked due to this. Please help!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Hero

Re: Not able to use update query in data sink- ReadyAPI

Hi @Ready4API 

 

ok - I've just been reading the help on this datasink - as I mentioned before - I've never used a datasink (JDBC or DataConnection) step to update a database before - I've just used the JDBC test step instead.  The help instructions for a JDBC or DataConnection datasink (link) and they state as follows:

"Specifies the query to the database. You can specify the desired query manually or by using the Build SQL 
Query or Create Query dialog. To insert test step property values into the query, use a question mark (?). The values will be used in the
order they are specified on the property list on the left. Property expansion does not work in the query. Use a question mark (?) to insert values instead. You can read more about working with SQL queries below."

 

It states you can't use property expansions in there - which is I assume why you need to specify those properties.  Also - you need to specify the properties in order in which they should be used in the query (which are represented in the query using '?' marks).

 

so if I understand correctly if your query is as follows:

 

update test_out set status=statusValue where destination=destValue

 

you need to create 2 properties (status = ${DataSink#status} and destination = {DataSink#destination}) and alter your query so it reads as follows:

 

update test_out set status=? where destination=?

 

n.b. - the properties need to be specified in the correct order according to the help.

 

Is there a reason why you need to use the datasink step to update the database?  The reason I ask is that the normal JDBC step (not the Data Sink step) allows property expansions (cos I use them all the time) and the JDBC test step still supports all DML command (update, insert, merge, delete) - if I were you - I'd use the JDBC test step and save all the hassle.

 

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 Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta

View solution in original post

7 REPLIES 7
Community Hero

Re: Not able to use update query in data sink- ReadyAPI

Hey @Ready4API  (nice name! Smiley Wink)

 

Firstly single quotes are used for ANSI standard SQL to indicate the beginning and end of a string in SQL.  I'm not saying some RDBMSs might handle the use of double quotes to denote this - I remember MIMER considers double and single quotes interchangeable - but I'd stick with the ANSI rules if I were you.  With this in mind I'd make a couple of alterations:

 

I'd replace "Trans" with 'Trans' and I'd replace "12568" with '12568'

 

Secondly - can you confirm that the destination attribute's datatype is string/text rather than numeric?  Cos if numeric you need to remove the quote marks from around 12568.

 

Thirdly - I'd double check if my query works via a typical DB interrogation tool like DBVisualiser or whatever you use to execute queries external to ReadyAPI!.  I've never used mySQL - but I've used Oracle/MIMER/SQLServer, so I'm assuming a well known RDBMS like mySQL would be the same.

 

Fourthly - i'd double check if my query works via a JDBC test step first, this will at least prove if the problem is peculiar to the datasink step itself or not.

 

Fifthly - I've never used the DataSink step for anything other than writing to a flat file - but there are two options to write to a database - the DataConnection option and the JDBC option.  Which one are you using?  I was just wondering - but I'd try both - but according to what I've just read in the help I did notice that the DataConnection data sink step uses the preconfigured database connection whereas the JDBC data sink step uses a connection that is setup purely for this particular test step.  I was wondering if you've used the DataConnection option but you haven't already got a pre-existing connection?   I should highlight however - that it appears that despite what it says in the help, you can create a new connection in the DataConnection data sink step - so this could be a red herring.

 

Might be helpful if you provided a screenshot of your datasink's config - just in case anything wrong jumps out at anyone??

 

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 Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta
Occasional Contributor

Re: Not able to use update query in data sink- ReadyAPI

Hi @richie , 

Thanks or your time. I got the actual issue but not sure how to fix it. Will need your help. Whenever i am defining parameter to pass in query it is throwing me error. if i remove the property vale reference and delete parameters created it doesn't throw me error either with single quote or double quote. 

 

Please find my query below which is throwing error:

update test_out set status=${DataSink#status} where destination={DataSink#destination}

 

Once the properties are there in the window even if i pass hard coded values it throws me java.lang error but after deleting properties created it works fine for hard coded values.

 

Should i be using quotes to pass these parameterized values? Your help is relly appreciated.

 

Attaching what i created as properties.

 

Thanks!

RK

Community Hero

Re: Not able to use update query in data sink- ReadyAPI

Hi,

The use of quotes is required if the attribute value is a string/text type (CHAR, VARCHAR etc.) rather than numerics, so whether you use quote marks is determined by that and that alone.
Whatever quote marks you used when you hardcoded the values (and the query executed successfully) just do the same for the paramaterized values.

Im travelling (so typing this on my phone) but when i get to work on my laptop i can look properly, but the first thing that pops out after reading your post is the properties values e.g. '${Datasink#status}'
Isnt this a circular reference? I.e youre self referencing a property set in the datasink step from within the datasink step.
Rather than just type it in and guess, right click in the sql window and select 'Get Data' ...the result of this is ReadyAPI! will generate the correct syntax.

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 Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta
Highlighted
Community Hero

Re: Not able to use update query in data sink- ReadyAPI

Hi @Ready4API 

 

ok - I've just been reading the help on this datasink - as I mentioned before - I've never used a datasink (JDBC or DataConnection) step to update a database before - I've just used the JDBC test step instead.  The help instructions for a JDBC or DataConnection datasink (link) and they state as follows:

"Specifies the query to the database. You can specify the desired query manually or by using the Build SQL 
Query or Create Query dialog. To insert test step property values into the query, use a question mark (?). The values will be used in the
order they are specified on the property list on the left. Property expansion does not work in the query. Use a question mark (?) to insert values instead. You can read more about working with SQL queries below."

 

It states you can't use property expansions in there - which is I assume why you need to specify those properties.  Also - you need to specify the properties in order in which they should be used in the query (which are represented in the query using '?' marks).

 

so if I understand correctly if your query is as follows:

 

update test_out set status=statusValue where destination=destValue

 

you need to create 2 properties (status = ${DataSink#status} and destination = {DataSink#destination}) and alter your query so it reads as follows:

 

update test_out set status=? where destination=?

 

n.b. - the properties need to be specified in the correct order according to the help.

 

Is there a reason why you need to use the datasink step to update the database?  The reason I ask is that the normal JDBC step (not the Data Sink step) allows property expansions (cos I use them all the time) and the JDBC test step still supports all DML command (update, insert, merge, delete) - if I were you - I'd use the JDBC test step and save all the hassle.

 

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 Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta

View solution in original post

Moderator

Re: Not able to use update query in data sink- ReadyAPI

Thanks for the investigation, @richie !

 

@Ready4API could you please let us know whether your question was answered? What stage are you on now?

 


Olga Terentieva
SmartBear Assistant Community Manager

Occasional Contributor

Re: Not able to use update query in data sink- ReadyAPI

Thanks @richie  JDBC update query worked fine with property expansion. Will try data sink with what you suggested but as of now i am able to move forward. 

 

Thanks again for your time.

Occasional Contributor

Re: Not able to use update query in data sink- ReadyAPI

I tried using JDBC step instead Data sink and it worked as expected for me. Thanks @Olga_T  for follow up. Sorry for late reply , got busy experimenting.