Printing QR codes out of database

Getting your data onto paper - or the web - Discussing the reports features of Base
Post Reply
saarenmaa
Posts: 2
Joined: Thu Dec 17, 2020 4:22 pm

Printing QR codes out of database

Post 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
OpenOffice 4.1.8 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Printing QR codes out of database

Post 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?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
saarenmaa
Posts: 2
Joined: Thu Dec 17, 2020 4:22 pm

Re: Printing QR codes out of database

Post by saarenmaa »

I was a programmer long time ago. :cry: Not up to date with JavaScript.
OpenOffice 4.1.8 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Printing QR codes out of database

Post 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

ID	URL
123	https://foo.com/
it would be possible to write all the pictures as 123.svg and fetch them with a relatively simple SQL statement:

Code: Select all

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Printing QR codes out of database

Post by Villeroy »

2021-02-09: Fixed some irritating bug in the form design, added button to save record
2021-12-06: Limited URL lenght in form to 62 characters which seems to be the maximum. Removed references to MriLib.
You still have that QR/JavaScript extension installed.
If you are still using OpenOffice, install the report builder extension. I'd prefer https://libreoffice.org/ 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

SELECT "TXT" AS "URL", 
"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.
Attachments
QR.odb
Write QR codes.
(37.31 KiB) Downloaded 492 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Printing QR codes out of database

Post by dreamquartz »

Hi Villeroy,

There appears to be an issue under LO 7.3.2.2
Please see attached.
Where did you get the "QR Code Generator JS"?
There is something via Synaptic, but not sure if that is what you used.

Dream
Attachments
QR Code Error message.png
QR Code Error message.png (95.47 KiB) Viewed 7400 times
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Printing QR codes out of database

Post by Villeroy »

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Printing QR codes out of database

Post by dreamquartz »

Thank you so much
Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Printing QR codes out of database

Post by dreamquartz »

@Villeroy,

Am I mistaken, or can your example only be used as an embedded DataBase?
Trying to split it, but to no avail.

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Mountaineer
Posts: 310
Joined: Sun Sep 06, 2020 8:27 am

Re: Printing QR codes out of database

Post by Mountaineer »

dreamquartz wrote: Thu Jun 02, 2022 5:15 am ...
Trying to split it, but to no avail.
...
So you have now a working split-db without qr-function,
or do you imply the qr-macros are preventing the split?
Or why do you think your problem is related to the macros?

It is unlikely the macros by villeroy are limited to a special type of db, if you read the text...
OpenOffice 3.1 on Windows Vista
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Printing QR codes out of database

Post by Villeroy »

QR.odb with split database directory, auto-install macro and the QR generating macro which depends on https://extensions.openoffice.org/en/pr ... -generator

1) Extract zip to trusted directory.
2) Open QR.odb, confirm installation by macro
3) Open form
4) Click button
5) Open report

The split database has been created with https://forum.openoffice.org/en/forum/v ... 21&t=86071
The auto-installer is taken from https://forum.openoffice.org/en/forum/v ... 21&t=77543
Attachments
QR.zip
(68.29 KiB) Downloaded 185 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Printing QR codes out of database

Post by dreamquartz »

Thank you so much.
See you did a re-write on FreeHSQLDB.
There appears to be an issue, though.....
Selecting the latest jar (HSQLDB 2.6.1 hsqldb.jar) I am getting the message:
Screenshot from 2022-06-02 09-33-53.png
Screenshot from 2022-06-02 09-33-53.png (24.45 KiB) Viewed 7083 times
.
This happens when I do: Tools->Macros->Organize Macros->Basic->FreeHSQDBD->FreeHSQLBD->Main (double click)->2. Specify database drive hsqldb.jar.
I can select the driver.
I try to open the form: QR; This is when the popup shows.

This is what the connection shows
Screenshot from 2022-06-02 09-52-58.png
Screenshot from 2022-06-02 09-52-58.png (13.41 KiB) Viewed 7083 times
It appears that the connection is correct.

Clearing the popup, trying to open the form again, no change; popup shows again.
Clearing popup, closing DataBase, opening DataBase again; able to open form.
Everything appears to be working fine when following these 2 steps.
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Printing QR codes out of database

Post by Villeroy »

HSQL 2.5 and 2.6 are not compatible with 1.8. They can not convert HSQL 1.8 on the fly. The uploaded stand-alone database uses the 1.8 version of your Open/LibreOffice. You can use a 2.4 driver to convert the stand-alone database. Then you may use 2.6 to convert that one. However, there are other issues when using Base with the most recent HSQL versions. I'm still happy with version 2.4. which plays very well with Base.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Printing QR codes out of database

Post by dreamquartz »

My FreeHSQLDB is not part of a DataBase.
Have it under My Macros.
The version with QR.odb FreeHSQLDB always defaults to: file:///opt/libreoffice7.3/program/../program/classes/hsqldb.jar
I have it stored somewhere else.
The reason for that is that I have different DataBases, and so I can select which jar to use.

I seem not to be able to remove it and use the one under My Macros. An Error is produced.

Am I to understand that the table structure (Table1) is fixed?
I want to extend and/or create different tables for different situations.

I also would like to know if control characters can be added to "TXT".
Working on this one for a while already, but no success so far.
I am trying to create a situation where Tabs can de added, so that I can 'Save Record' and 'Write QR Codes'.

Dream
Attachments
Screenshot from 2022-06-03 13-34-19.png
Screenshot from 2022-06-03 13-34-19.png (38.5 KiB) Viewed 7044 times
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Printing QR codes out of database

Post by dreamquartz »

Villeroy wrote: Thu Jun 02, 2022 7:21 pm HSQL 2.5 and 2.6 are not compatible with 1.8. They can not convert HSQL 1.8 on the fly. The uploaded stand-alone database uses the 1.8 version of your Open/LibreOffice. You can use a 2.4 driver to convert the stand-alone database. Then you may use 2.6 to convert that one. However, there are other issues when using Base with the most recent HSQL versions. I'm still happy with version 2.4. which plays very well with Base.
OK,

That is then I am getting stuck....
I am therefore looking for all the drivers for all previous versions of HSQLDB.
Do you have any idea where to locate them?

Seems to be difficult....

Kind regards,

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Mountaineer
Posts: 310
Joined: Sun Sep 06, 2020 8:27 am

Re: Printing QR codes out of database

Post by Mountaineer »

dreamquartz wrote: Mon Jun 13, 2022 11:16 pm...
I am therefore looking for all the drivers for all previous versions of HSQLDB.
Do you have any idea where to locate them?

Seems to be difficult....

Kind regards,

Dream
Villeroys instructions on the split db can guide you to hsqldb, they link a download page, and while they advertise their newest version, there are also some suspicios named subdirectories at https://sourceforge.net/projects/hsqldb/files/hsqldb/
OpenOffice 3.1 on Windows Vista
dreamquartz
Posts: 881
Joined: Mon May 30, 2011 4:02 am

Re: Printing QR codes out of database

Post by dreamquartz »

@Mountaineer,

Thanks

Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Post Reply