Page 1 of 1

Printing QR codes out of database

PostPosted: Thu Dec 17, 2020 4:40 pm
by saarenmaa
I need to print a very large number of small size QR codes out of database. There exists an OO Extension "QR Code Generator JS". It works in Calc and Writer. Any suggestions how to do this in Base? I think it is best to use Base since there are Reports and page layout can be controlled.

Thanks for any advise, Hannu

Re: Printing QR codes out of database

PostPosted: Thu Dec 17, 2020 6:30 pm
by Villeroy
The extension converts text to QR codes. I would think that it could be possible to convert text that is stored in a database into QR codes during report generation. Are you a programmer?

Re: Printing QR codes out of database

PostPosted: Thu Dec 17, 2020 7:51 pm
by saarenmaa
I was a programmer long time ago. :cry: Not up to date with JavaScript.

Re: Printing QR codes out of database

PostPosted: Thu Dec 17, 2020 8:55 pm
by Villeroy
The extension author uses a JS library with a little bit of wrapper code in StarBasic.
Function QrCode(sText, sPath, sColor, nSize) is the routine which takes 4 arguments and prepares the appropriate call to the JavaScript code.

A simple solution would read the database field in question, call that routine and store the resulting pictures permanently in a dedicated directory. The original Basic code deletes the picture after it has been embedded in your document.
The directory of pictures can be reported either with the LibreOffice suite or with OpenOffice plus report builder extension. OpenOffice's 20 year old report generator can not have any pictures.

Having a table with a unique identifier and some URL to be QRed:
Code: Select all   Expand viewCollapse view

it would be possible to write all the pictures as 123.svg and fetch them with a relatively simple SQL statement:
Code: Select all   Expand viewCollapse view
SELECT *, 'file:///path/to/pics/' || "ID" || '.svg' AS "QR" FROM "My Table"

and build a report based on this statement with a picture control linked to the concatenated "QR" field.

Re: Printing QR codes out of database

PostPosted: Sun Dec 20, 2020 1:12 am
by Villeroy
2021-02-09: Fixed some irritating bug in the form design, added button to save record
You still have that QR/JavaScript extension installed.
If you are still using OpenOffice, install the report builder extension. I'd prefer where this thing is built-in.
menu:Tools>Options... Security, [Macro Security...], set highest level and add a trusted directory for documents with embedded macro code. Do NOT use your Downloads folder. Your Documents folder is a better place.
Save the attached document to that directory or any subdirectory therein.
Create a subdirectory "qr" in the directory where you saved the document.
Open the document and the form therein.
Push the big button. It triggers an embedded macro which itself calls the extension macro which itself calls the JavaScript that produces QR codes.
Navigate the form records and try the codes with your smart phone.
Open the report.
A report is something printable, a form is for interactive data input, filtering, sorting, viewing.
How it works:
The form, the report and the macro rely on a query named "QR". The query must have a text column "URL" to generate the pictures from and a column "FN" of unique file names.
Query "QR" in the attached document goes like this:
Code: Select all   Expand viewCollapse view
"PK" AS "ID",
'qr/' || RIGHT( '000000' || "PK", 7 ) || '.svg' AS "FN"
FROM "Table1"

I have a table "Table1" from where I select the column "TXT" renamed to "URL", a unique primary key "PK" renamed to "ID" and a concatenation of subdirectory 'qr/' with an expression that creates a 7-digit number from the unique primary key plus the '.svg' file name suffix. The concatenation of the unique file names in subdirectory "qr" is named "FN".
The order of columns does not matter, the order of rows does not matter. All that matters is that some query "QR" has a column "URL" and a column "FN".
The macro loops through all records of that query and writes the QR code for "URL" into the file specified by "FN" by simply calling the code you already have installed.
The image controls in both elements, the form and the report, are linked to the "URL" column of the "QR" query which point to a relative file URL. The database document has no images stored.
P.S. any filtering applied to the form will affect the count of generated QR codes. Only visible form records get a new QR.
P.P.S. menu:Tools>Macros>Organize>Basic... tab "Libraries" allows to import the QR library from my document into yours if both documents are loaded. Create your own "QR" query in your database and that's it.