DB Datasource is not getting refreshed

sujaldalia

I'm using MySQL DB as the datasource and doing Data Source Correlation on it. When new data is getting inserted in the DB it does not do correlation properly until you open the data source and do show column.

Is there a setting or something that needs to be turned on so it always pulls latest info from the DB?

OmarR

Good afternoon Sujaldalia,

You will have to re-deploy the Virtual Asset to read in the new data from the DB. The "Show Column" button saves the data source settings and then re-deploys the virtual asset automatically. Notice that when you hit the "Show Columns" button, the console shows the redeployment of the asset. To ensure that you are retrieving the latest data, redeploy the asset under the virtualize server view or click on the "Show Column" button under the data source.

sujaldalia

Is there a work around for this to make it work? If I have multiple PVAs that is working against single DB / table and 1 inserting the value, it will be hard to know when the data is inserted and when to redeploy.

OmarR

One solution could be to make a call to the REST API to redeploy the specific Assets that are utilizing the DB as a datasource. You could add the call at the end of the PVA that is inserting values into the DB. This way, every time you add new values to the DB, the corresponding PVAs will be updated automatically.

sujaldalia

Redeploying the asset can interrupt the usage if it is being accessed / used so that will not work

OmarR

That is an excellent point Sujal
The alternative solution does not have this issue, however, it will require a bit more effort to implement, but if done correctly, it should be a one-time set-up.
The alternative would be to create a GATEWAY PVA to write out the DB values into a file in CSV format and have a secondary PVA do correlation using a csv-datasource. The GATEWAY PVA would utilize the DB tool (chained to Responder) to make a call to the DB to retrieve the latest values. We would then pass the results as XML through an XML transformer and finally through a write file tool.

In addition to the DB tool, we will also need to chain a Message Forward Tool to the GATEWAY responder in order to pass the request to the secondary PVA. The configuration would look like the following:

If you choose to go this route, I recommend looking at the Xpath found here. The Xpath provided by @Ireneusz Szmigiel simplifies the entire process beautifully.
Overall, this is worthy of a feature request. I will contact our support team to create a case on your behalf and begin the FR process.

sujaldalia

My initial request that I'm working with is JSON so I'm extracting info from that, saving it in DB & forwarding out to the endpoint. For some reason when I run select statement I'm not able to write the output or see it in traffic viewer. I can write sql query to file but that is it.

My colleague is using Virtualize 9.8 and he is able to see the result in XML format using traffic viewer but I have tried both 9.9 and 9.10.3 but no luck

sujaldalia

I was able to fix the issue. I use DB Tool as part of the SOATest and it allows you to see traffic viewer and show the data. It would be great for traffic viewer to show data in virtualize as well

OmarR

Yes, the traffic viewer will only display the traffic for SOAtest clients or clients in a ProvisionAction. Traffic Viewers chained to a responder will not populate.

Admittedly, I found a flaw with the set-up above where the csv datasource will also cache the data stored in the file. Therefore, you may not always retrieve the latest data from the DB. Fortunately, there is a different approach you may take if the desire is to return the DB data for the requested parameter.
Set-up:
1) Chain a JSON Databank to your Responder (Incoming Request output) and extract/store the data being requested
2) Chain a DB Tool to your Responder (Incoming Request output) and add the SQL query to retrieve the requested data (extracted by JSON databank).

Example:
SELECT * FROM CUSTOMER WHERE ID = '${Dynamic_id}'

3) Chain an XML DataBank to the DB Tool (Results as XML) and extract/store the root element to extract everything.
4) Parameterize ${ } the Responder payload with the data extracted from the XML databank.
The configuration will look like the following:

sujaldalia

Thanks Omar