Creating PDF documents from database records

[dkelly]

Today I was avoiding real work and developed this flow for creating PDF documents from jobs stored in a database.







The "GetOrders" script periodically queries an SQL database for any new orders. When it finds a new order, it retrieves the information from the database, in this case the name and birthday of the customer, and creates an XML file for Apago's PDF Constructor for building a customized PDF birthday card with the customer's name and age.



Happy 45th Birthday!
Joe Blaney


After creating the greeting card, it marks the order in the database as completed so it doesn't get reprocessed.



Dwight Kelly

Apago, Inc.

dkelly@apago.com

[dkelly]

So here's the code for "GetOrders" script for querying and updating the database



function timerFired( s : Switch )

{

var datasource = new DataSource();

var dsn = s.getPropertyValue("dataSource");

if (dsn == null) {

job.fail("Invalid value for dataSource");

return;

}

datasource.useConnection(dsn);

if (datasource.isConnected()) {



// Fetch unprocessed jobs from database

var stmt = new Statement(datasource);

stmt.execute("SELECT job_id,name,birthdate FROM greetingcards.orders WHERE completed='0';");

if (stmt.isSuccess()) {

while (stmt.isRowAvailable()) {

stmt.fetchRow();

for (var c=0; c<stmt.getNumColumns(); c++) {

var variableName = stmt.getColumnName(c);

....store value from database....

}

}



....create the PDF Constructor XML....

....add PDF Constructor XML file to job....



var stmtCompleted = new Statement(datasource);

stmtCompleted.execute("UPDATE greetingcards.orders SET completed='1' WHERE completed='0' AND job_id='"+job.getPrivateData("job_id")+"' ;");

if (stmtCompleted.isSuccess()) {

var theDataset = job.createDataset("XML");

var theXMLFilename = theDataset.getPath();

theXML.save(theXMLFilename);

job.sendToSingle(theXMLFilename);

} else {

job.log(cLogError, stmtCompleted.getMessage());

}

}

}

}





Enjoy!

[Clive Andrews]

Nice, like it!