Forum Discussion

amarnath1234's avatar
amarnath1234
Contributor
6 years ago

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

  • amarnath1234's avatar
    amarnath1234
    6 years ago

    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'

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

      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"

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