Layer7 API Management

Hi Team,

We are using LAC Version 3.3

We have a use case in which we need to send CSV file as request and insert the data in the CSV file into the database Table using LAC.

Is it feasible ?

Thanks,

Iran

Hi Irfan,

I must admit I'm not as familiar with the Live API Creator myself yet, so I'm going to have a colleague chime in when they have a free moment.

I did some quick review of the documentation and I found that you can create tables from a CSV file, but it seems that it wouldn't be sent as a request specifically, so if you're needing it as part of a request then this may not be possible. CSV is a proper data source, however. Ref: CSV Data Source - CA Live API Creator - 4.1 - CA Technologies Documentation

Hi Dustin,

Thank you for your assistance.

I guess this can be done using Insert JDBC DATA in Bulk ASSERTION on the gateway.

Need to find a way to use that assertion.

Thanks,

Irfan

The CSV Datasource is a 'read-only' driver.  That is, it cannot persist back to the source file.  Your question is - how to persist the CSV data.  One trick is to write a function to read the CSV data and then POST to an SQL table.  The transformation may be a little tricky since the CSV datasource does not really distinguish the datatypes of the columns (e.g. Dates, Booleans, Strings, Numbers). 

1) Create a Resource (normal table) for your CSV table.

2) Create a new Managed Server Datasource

   In the Data Sources menu - click add and select Managed Data Server

3) Once you have a new Data Source - go to Schema and Add a new Table

   Add Columns for each of your CSV columns (Note: The names must match the JSON exactly)

4)  Create a new Function.

  //This will return a JSON object view of your data

var myCSVData = SysUtility.getResource("MyCSVTableResource", null);
var resourceURL = req.getBaseUrl() + "v1/MyPersistentCSVTable";
var parms = {};
var apikey = "demo_full";// or req.apiKey.getApiKey();
var settings = { "headers": {"Authorization" : "CALiveAPICreator "+apikey+":1"}};
var postResponse = SysUtility.restPost(resourceURL, parms, settings, myCSVData);
log.debug(postResponse);
var jsonPost = JSON.parse(postResponse);
return jsonPost;

5) Go to rest lab and execute your Function

Your data is now in a persistent SQL Table. 

Thank you so much, Tyler it worked for me.

Really appreciated.

Only one thing to ask, how can we do a batch processing in this scenario. Because my CSV data file will contain more than 100Thousand records. Currently, it is only updating 20 data rows in the one-time execution of a function.

I have seen in API Properties in which we can change the page size. But How to achieve batch processing in this scenario.

I want to ADD every 100 records in batch.

Once again thank you so much for the help.

Thanks,

Irfan

var settings = {pagesize:100000,offset:0}
var myCSVData = SysUtility.getResource("MyCSVTableResource", settings);

see documentation page for details on getResource for filtering, pagesize, offset, etc.

To do a batch (scheduled) job - use the Timer function to run scheduled jobs.  Simply call your function on a schedule (timerUtil.restGet).  

You could setup a loop and change the pagesize and offset.  If you examine the response, (myCSVData['@metadata'].next_batch will have an HREF that will tell you the next batch to read.

Thanks a lot.

Regards,

Irfan