Forum Discussion

richie's avatar
richie
Community Hero
9 months ago

Parameterising JDBC Connection Settings..Can you do it?

Hi,

I'm trying to create a project that I can lift and shift between environments no matter the data in the underlying database - so I need to parameterise anything that is environment specific but i'm struggling with the JDBC steps.

The summary of my question is -->  how are people parameterising their JDBC Test Step connection details so they don't have to edit the content of EVERY SINGLE JDBC TEST STEP when they switch to a different environment?

Please see below for what I've tried.

 

Ages back nmrao showed me a way of storing all environment specific variables at project level using an environment specific properties file which works except for my JDBC connection settings.

i.e. create a 'QA_properties' file that looks like the following:

EnvironmentDomainHost=https://platform.www-whatever.net
Authorization_HeaderParm=eyJhbGciOiJIUzI1NiIsInR5cC
X-Auth-Key_AdminHeaderParm=8b0d7bf2-9aa3-4e29-8b4e-79a4c7bde384
X-Auth-Key_HeaderParm=8683f790-18de-4906-a65b-67565d18c676
JDBC_Driver=com.mysql.jdbc.Driver
JDBC_Host=104.211.13.14
JDBC_Port=3306
JDBC_DB=mab_user
JDBC_User=ulo_user
JDBC_Password=thisIsNotTheRealPassword
JDBC_ConnectionURL=jdbc:mysql://whatever:3306/db?user=xxxUser

 

So database I need to connect to is mySQL v8.  I've downloaded JDK17, I've installed the mySQL v8 driver file and copied it into the correct /Program Files/ReadyAPI!/bin/ext directory and confirmed the connection works.

First thing - when I select the correct PREVIOUSLY created connection my JDBC test step

I was expecting the 'Driver' the 'Connection String' and the 'Password' to be auto populated with the details already set in my Database Connections function - didn't this auto populate before?

Because those fields aren't populated - I decided to use the 'Get Data' function that is available and pick up the relevant values from my custom project properties (sourced from the 'QA_properties' file - see above) - so it appears as follows:

 

The 'Get Data' function appears to let me point to a parameter value stored elsewhere - however - if I try running the query or testing the DB connection - it fails.

I think there might be a couple of defects that is hindering me being able to do this - I'm pretty sure one earlier versions of ReadyAPI a couple of years ago auto populated the 'Driver' 'ConnectionString' and the 'Password' fields once the pre-existing DB connection was set.

I don't remember trying to parameterise the DB Connection details before now - HOWEVER - why include functionality that supports parameterisation with the Get Data function and allow me to point to a custom project property if it's not allowed?  

 

Can anyone advise?  How would any one else here parameterise a JDBC Test Step in a project so that when lifting and shifting their ReadyAPI! project to a different environment they don't have to edit EVERY Single JDBC test step?

I'm gonna have hundreds and hundreds of tests and virtually every single one of those will require a JDBC test step.

Yes I would like to use REST as an alternative - but there are no native REST APIs supporting querying mySQL (unlike stuff like Azure DB, etc.).  I'd need a developer to code a REST API to support this.

 

Cheers,

Rich

  • nmrao's avatar
    nmrao
    Champion Level 3
    9 months ago

    What is the issue being faced?

    Connection error? or details from log otherwise?

    • richie's avatar
      richie
      Community Hero
      9 months ago

      Hey nmrao 

      An error is reported "SQLException: Access denied for user 'DB_user' @ 'IP Address' (using password: NO)" - so it's ignoring the password value populating the password field (the password field doesn't include the 'Get Data' capability - when I right click in the password field - the context menu that launches doesn't include the 'Get Data' option).

       

      At this point, if I click on the 'Configure' button immediately below the 'Password' field' - the following 'no matching template' dialogue is launched 

      which once the 'OK' button is selected to close the 'Error' dialogue - the 'Database Configuration' form is automatically launched.

      I'm guessing 'template' is the RDBMS option (SQLServer? Oracle? PostGres, MySQL) - but it's not even recognising the previously connected mySQL connection (that was set in the 'Configuration' section of the JDBC test step form).  If I click 'OK' on the 'No matching template' dialogue - it then launches the 'Database Configuration' form (see below)

       

       

      Cheers!

      Rich

       

    • richie's avatar
      richie
      Community Hero
      9 months ago

      Hey man,

      The password was never parameterised - the right click context menu doesn't include a 'Get Data' option on the password field - which further raises the question for me - what's the point of being able to parameterise the other details (Connection String, Driver fields) if you can't parameterise the Password field value?


      Surely that means - you can't parameterise any JDBC test step?

      As I said in original post - I'm using the approach you showed me to handle multiple environments - specify values in an environment specific custom project level properties file - and then just load in whichever file corresponds to the environment you're testing in - but if you can't parameterise the DB user's password - it kinda invalidates parameterising the other DB connection details.

      I might raise a ticket with SmartBear and see what they say.

      Thanks man - appreciate you looking at this for me,

      rich

  • nmrao's avatar
    nmrao
    Champion Level 3
    9 months ago

    richie 

    I do have only SoapUI Open Source edition of specific version and that does not have a separate field for password as shown below. 

    In this case, credentials  can be part of Connection String itself.  such as:

    jdbc:postgresql:dbserver:5432:ProductDB&user=root&password=secret

    May be you can try including the password in the connection string and leave Password blank to see if that helps.

    Of course, use parameterized values for each of the above in the connection string, assign below string to JDBC_CONNECTION_STRING project property and use it in JDBC Step's connection string and see what happens, leave password blank

    jdbc:postgresql:${#Project#DB_HOST}:${#Project#DB_PORT}:${#Project#DB_NAME}&user=${#Project#DB_USER}&password=${#Project#DB_PASSWORD}

  • troyyerJP's avatar
    troyyerJP
    Contributor
    9 months ago

    hi  richie 

    I was a bit confused with which Test Step was being used but I had trialed creating a 'JDBC Request' Test Step out on my end with my current SQL JDBC setup and it works as I expect so hopefully the same would work on your end. 

    So I use the Environments feature of ReadyAPI to change the DB connection configuration of a PostgreSQL DB connection (and prior I had done it for a MS-SQL DB connection) for various Data Source Test steps (which was what I have always used my DB connections within ReadyAPI)

    Here is a screenshot of my JDBC connection section in ReadyAPI Environments section

     In the gif (ReadyAPI Environment DB Connection.gif) you can see that I have the configuration for 2 separate DB environments and then within the Data Source Test Step using JDBC, when I change the environment it changes the connection but the greyed out section does not change to the new values. My hypothesis is that the greyed out section is from the Default No Environment  connection. But this way of configuring allows us to change the config per environment under test (the team using MS-SQL DB has 7 environment configs for their DB connection used in the Data source Test Step)

     I also created a dummy JDBC Test Request step and I could see that it was working correctly with the JDBC connection I had created in my project. Although I note that the connection string does change to the string for the environment in the JDBC request, so not sure why the behaviour is different to the Data Source Test Step. (gif attached ReadyAPI JDBC Test Step using Environment.gif)

    Try and see if this setup with Environments works for you.