Sign In

PDF Generation

PDF files are widely used as email attachments for use cases like invoicing and purchasing. Often the PDF files need to be generated automatically from database data without user intervention.

AppSynergy allows you to dynamically generate HTML and then convert that HTML into a PDF document stored in your database. You generate the HTML and request the PDF document generation via your SQL code (e.g. trigger, stored procedure).

Hello World Example

The following example calls AppSynergy's built-in HTML2PDF web service. Before you can call the web service you will need to create an API Key to allow access to your account (see Tools > API Keys... for details).

Once you have an API Key you can call the HTML2PDF web service. The process has two steps:

  1. generate the HTML and request the conversion to PDF, and
  2. process the response to store the generated PDF file in a document field.

Make the Request

BEGIN DECLARE v_req_url VARCHAR(2048) DEFAULT ''; DECLARE v_post_data MEDIUMTEXT; DECLARE v_html MEDIUMTEXT; -- generate the HTML SET v_html = '<html><body><h1>Hello World</h1></body></html>'; -- create the request object SET v_post_data = JSON_OBJECT( 'filename','HelloWorld.pdf', 'html', v_html ); -- make the request INSERT INTO parasql_http_request (request_method, request_url, post_data, post_data_encoding, callback_procedure, opt_metadata) VALUES ('POST', v_req_url, v_post_data, 'application/json', 'HelloWorld_SavePDF', JSON_OBJECT('invoiceNumber', 12345) ); END

Process the Response

An example of a callback_procedure to process the response is shown below. In this example the stored procedure is called HelloWorld_SavePDF (as seen above) and it takes a single parameter of type BIGINT called param_request_id (as seen below).

BEGIN DECLARE v_pdf_doc_field VARCHAR(255); -- Document Fields are declared as VARCHAR(255) DECLARE v_invoice_number BIGINT; -- get the response (including opt_metadata where we have stored the related invoice number) SELECT JSON_VALUE(response_body, '$.data.documentField'), JSON_VALUE(opt_metadata, '$.invoiceNumber') INTO v_pdf_doc_field, v_invoice_number FROM parasql_http_request WHERE id = param_request_id; -- param_request_id is the procedure's parameter -- update the Invoices table to save the PDF UPDATE Invoices SET Invoice_PDF = v_pdf_doc_field WHERE Invoice_ID = v_invoice_number; END

Additional Documentation

See the SQL/REST documentation for details on how to make RESTful API calls. See the Sending Email documentation for details on how to send an email with a document field attachment.

Supported HTML/CSS

The HTML to be used for PDF generation purposes must be in XHTML format. XHTML requires that all elements are closed (i.e. <br> will NOT work but <br/> will).

Most of CSS 2.1 is supported. In addition there are additional directives you can use for things like page numbers in footers or repeating the header of an HTML table across the top of multiple page.

CSS for page numbering:

@page { @bottom-center { content: "Page " counter(page) " of " counter(pages); } }

CSS for making a table header repeat at the top of each printed page:

table { -fs-table-paginate: paginate; -fs-page-break-min-height: 50px; }

Invoice_GetHTML() Function Example

If you are generating complex HTML (e.g. for an invoice) it is usually best to create a dedicated stored function to do so. The function can take a parameter like an invoice number and return the generated HTML for that invoice.

The following pseduo example (it doesn't read from any tables) provides an otherwise complete example.

BEGIN DECLARE logoURL VARCHAR(2048) DEFAULT ''; DECLARE html MEDIUMTEXT; DECLARE v_invoice_terms VARCHAR(50) DEFAULT 'NET 30'; -- start HTML SET html = '<html><head>'; -- style sheet - non-standard CSS noted with comments below SET html = concat(html, '<style>'); SET html = concat(html, 'body {font-family: sans-serif; font-size: 12px; line-height: 120%;}'); SET html = concat(html, '@page { @bottom-center { content: "Page " counter(page) " of " counter(pages); } }'); -- page numbers SET html = concat(html, 'table {border-collapse: collapse; width: 650px; margin: 10px; font-size:inherit; font-family: inherit;}'); SET html = concat(html, 'table { -fs-table-paginate: paginate; -fs-page-break-min-height: 50px; }' ); -- repeat table header on each page SET html = concat(html, 'table, th, td {border: 1px solid black;}'); SET html = concat(html, 'tr {page-break-inside: avoid;}'); -- makes rows break cleanly SET html = concat(html, 'th {font-weight: bold; padding: 5px; background-color:rgb(245,245,245);}'); SET html = concat(html, 'td {padding: 5px;}'); SET html = concat(html, 'td.summary {text-align: right; border: none;}'); SET html = concat(html, 'td.summary-data {text-align: right;}'); SET html = concat(html, 'td:nth-child(4), td:nth-child(5) {text-align: right;}'); SET html = concat(html, ' {display:inline-block; vertical-align:top; width:300px; height:auto; margin:5px; border:0px solid black; padding:5px;}'); SET html = concat(html, '</style>'); -- open body SET html = concat(html, '</head><body>'); -- box 1 - logo SET html = concat(html, '<div class="info-box"><img src="', logoURL, '" style="max-width:200px" /></div>'); -- box 2 - INVOICE # SET html = concat(html, '<div class="info-box">'); SET html = concat(html, '<b>INVOICE #:</b> ', 12345, '<br/>'); SET html = concat(html, 'Invoice Date: ', DATE_FORMAT('2020-09-04', '%m/%d/%Y'), '<br/>'); SET html = concat(html, 'Terms: ', parasql_escape_html(v_invoice_terms), '<br/>'); -- ALWAYS escape variables with parasql_escape_html() SET html = concat(html, '</div>'); SET html = concat(html, '<br/>'); -- the trailing / character is REQUIRED by XHTML -- box 3 - REMIT TO SET html = concat(html, '<div class="info-box">'); SET html = concat(html, '<b>REMIT TO:</b>', '<br/>'); SET html = concat(html, 'My Company LLC','<br/>'); SET html = concat(html, '145 Main St.','<br/>'); SET html = concat(html, 'New York, NY 10023','<br/>'); SET html = concat(html, '</div>'); -- box 4 - BILL TO SET html = concat(html, '<div class="info-box">'); SET html = concat(html, '<b>BILL TO:</b>', '<br/>'); SET html = concat(html, 'ACME LLC', '<br/>'); SET html = concat(html, '601 Heritage Dr', '<br/>'); SET html = concat(html, 'Jupiter, FL 33458', '<br/>'); SET html = concat(html, '</div>'); SET html = concat(html, '<br/>'); -- start table SET html = concat(html, '<table>'); -- table header SET html = concat(html, '<thead><tr><th>Item #</th><th>Description</th><th>Qty</th><th>Unit Price</th><th>Total</th></tr></thead>'); -- FOR EACH ROW - this would be in a loop SET html = concat(html, '<tr><td>4567</td><td>XYZ Product Description</td><td>10</td><td>$25.00</td><td>$250.00</td></tr>'); SET html = concat(html, '<tr><td>4567</td><td>XYZ Product Description</td><td>10</td><td>$25.00</td><td>$250.00</td></tr>'); SET html = concat(html, '<tr><td>4567</td><td>XYZ Product Description</td><td>10</td><td>$25.00</td><td>$250.00</td></tr>'); -- table footer SET html = concat(html, '<tr><td class="summary" colspan="4">Subtotal:</td><td class="summary-data">$', 750.00, '</td></tr>'); SET html = concat(html, '<tr><td class="summary" colspan="4">Shipping:</td><td class="summary-data">$', 0.00, '</td></tr>'); SET html = concat(html, '<tr><td class="summary" colspan="4">Total:</td><td class="summary-data">$', 750.00, '</td></tr>'); -- end table SET html = concat(html, '</table>'); -- close body SET html = concat(html, '</body></html>'); -- return the HTML RETURN html; END

Diagnosing Problems

If you run into problems, try issuing a command like the following via Tools > SQL Console... to diagnose what went wrong:

SELECT * FROM parasql_http_request ORDER BY id DESC LIMIT 1