Forum Discussion

Ready4API's avatar
Ready4API
Occasional Contributor
5 years ago
Solved

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.exception:SQL error occured". Below is the query i am using(MySql DB):   update table_out set status="Trans" where ...
  • richie's avatar
    richie
    5 years ago

    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

     

richie's avatar
richie
Community Hero
5 years ago

Hey Ready4API  (nice name! ;))

 

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

 

  • Ready4API's avatar
    Ready4API
    Occasional Contributor
    5 years ago

    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

    • richie's avatar
      richie
      Community Hero
      5 years ago
      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
      • richie's avatar
        richie
        Community Hero
        5 years ago

        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