MS SQL Server Identity Column
Hello Screen Scraper pros
I am working on a project right now where I am using screen-scraper to log in and download a report. I would like to save every line from the report in a database. I am doing that currently using an extractor pattern to pull out the datarecord and then manually applying 2 other patterns to the record to extract transactions and adjustments using a script.
I have included the relavent sections of my script that runs on each pattern match (the pattern being a section of transactions and adjustments).
I am wondering what the best way to store the records in a MS SQL Server database. I can use some of the data fields as the primary key but doing it that way results in missing some transactions due to the data in my pk fields being repeated occasionally and the unique constraint for primary keys.
I also tried using an auto increment identity column but nothing is being added. This would be my preferred approach if I could figure out how to get it to work.
Another idea I had was to use a static variable in my java script but I am not sure that is the right approach.
Any feedback and ideas are appreciated.
DataSet transactions = scrapeableFile.extractData(allTransactions, "Transaction Extractor Pattern");
for (i=0; i < transactions.getNumDataRecords(); i++) {
transactionRecord = transactions.getDataRecord(i);
//resident ID, facility ID
transactionRecord.put("RESIDENTID", dataRecord.get("RESIDENTID"));
transactionRecord.put("FACILITYID", session.getVariable("FACILITYID"));
transactionRecord.put("timestamp_add", addDate);
dm.addData("transactions", transactionRecord);
dm.commit( "transactions" );
}
dm.flush();
allTransactions = dataRecord.get("TRANSACTION_DATA");
DataSet adjustments = scrapeableFile.extractData(allTransactions, "Adjustment Extractor Pattern");
for (i=0; i < adjustments.getNumDataRecords(); i++) {
adjustmentRecord = adjustments.getDataRecord(i);
adjustmentRecord.put("RESIDENTID", dataRecord.get("RESIDENTID"));
adjustmentRecord.put("FACILITYID", session.getVariable("FACILITYID"));
adjustmentRecord.put("timestamp_add", addDate);
dm.addData("transactions", adjustmentRecord);
dm.commit( "transactions" );
}
dm.flush();
I eventually got it to work
I eventually got it to work with a generated primary key stored in the session. In my initial script to set up parameters I added a variable to the session:
in my script that runs on each report section pattern match I added the following to grab the next transactionID and store it in the dataRecord, increment the counter and set a new value for the session variable.
transactionID++;
session.setVariable("transactionID", transactionID);
Feedback is still welcome, I am curious to know how the pros would have solved this problem.
I always try to scrape what
I always try to scrape what the site uses as a unique key and use that too, but I can't tell what that would be based on this.
unfortunately the unique key
unfortunately the unique key is not displayed in the report I am trying to scrape. I thought I had enough unique columns to just make a combination of the data for my primary key but now I have realized I am missing some duplicate rows because of this.
It was working the way I had it but I realize now I was missing some rows of data due to the unique constraint on primary keys.