Printing QR codes out of database
Printing QR codes out of database
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
Thanks for any advise, Hannu
OpenOffice 4.1.8 on Windows 10
Re: Printing QR codes out of database
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Printing QR codes out of database
I was a programmer long time ago. Not up to date with JavaScript.
OpenOffice 4.1.8 on Windows 10
Re: Printing QR codes out of database
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:
it would be possible to write all the pictures as 123.svg and fetch them with a relatively simple SQL statement:
and build a report based on this statement with a picture control linked to the concatenated "QR" field.
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/
Code: Select all
SELECT *, 'file:///path/to/pics/' || "ID" || '.svg' AS "QR" FROM "My Table"
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Printing QR codes out of database
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:
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.
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"
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 753 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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 884
- Joined: Mon May 30, 2011 4:02 am
Re: Printing QR codes out of database
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
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 (95.47 KiB) Viewed 8120 times
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
Re: Printing QR codes out of database
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 884
- Joined: Mon May 30, 2011 4:02 am
Re: Printing QR codes out of database
Thank you so much
Dream
Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
-
- Posts: 884
- Joined: Mon May 30, 2011 4:02 am
Re: Printing QR codes out of database
@Villeroy,
Am I mistaken, or can your example only be used as an embedded DataBase?
Trying to split it, but to no avail.
Dream
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.
-
- Posts: 318
- Joined: Sun Sep 06, 2020 8:27 am
Re: Printing QR codes out of database
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
Re: Printing QR codes out of database
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
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 405 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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 884
- Joined: Mon May 30, 2011 4:02 am
Re: Printing QR codes out of database
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: .
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 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.
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: .
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 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.
Re: Printing QR codes out of database
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
-
- Posts: 884
- Joined: Mon May 30, 2011 4:02 am
Re: Printing QR codes out of database
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
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 (38.5 KiB) Viewed 7764 times
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.
-
- Posts: 884
- Joined: Mon May 30, 2011 4:02 am
Re: Printing QR codes out of database
OK,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.
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.
-
- Posts: 318
- Joined: Sun Sep 06, 2020 8:27 am
Re: Printing QR codes out of database
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/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
OpenOffice 3.1 on Windows Vista
-
- Posts: 884
- Joined: Mon May 30, 2011 4:02 am
Re: Printing QR codes out of database
@Mountaineer,
Thanks
Dream
Thanks
Dream
LO 7.x, HSQLDB 2.7.x & Ubuntu 22.04 LTS.