Hello Laura,
SQL statements are a possible and very good way to add any external data to a form as form data.
That is obviously your intention.
In order to be able to display non-printable characters in the SQL statement, MySQL has so-called:
'Special Character Escape Sequences'.
And that is exactly what you can see in your SQL example.
String Literals:
dev.mysql.com/doc/refman/8.4/en/string-literals.html
Re: since \0 isn't a printable character
No, \0 is a printable string literal, which you can read here.
\0 stands for the 'binary zero', and the 'binary zero' is the non-printable character.
\0 stands for a non-printable character.
An example:
For the listbox with multiple selection with field ID=636, the multiple selection of 'value2' and 'value3' is set using an SQL statement.
The following SQL statement is correct and is the only way it is possible.
In the database field, there is then a 'binary zero' directly after 'value2'.
A 'binary zero' in a database field is a normal case that occurs quite frequently.
Code:
update jx_visforms_60 set F636 = "value2\0, value3" where id = 1;
As I said, \0 is readable/writable and a 'binary zero', on the other hand, is the non-printable character.
If you work with readable, editable SQL statements (e.g. in a file), then there is no other option.
Working with a tool:
If your tool cannot handle non-printable characters (export and import with the same tool), you should
- Ask the tool provider.
- Use a different tool.
Other options 1:
All other options for developers lead more or less to the same thing.
A file is created from the existing data, in whatever way, which consists of a series of individual SQL statements.
Each individual SQL statement adds a record with the form data to the data table.
In the SQL statement, all non-printable characters are in a 'special character escape sequence'.
This file can then be read in and processed using common database editors or on the command line (see below).
Creating the readable file with the SQL statements, based on any external data table, is then an individual development task.
Other options 2:
Assuming the optimal case that the table structure of the source table and the form data table match, you can do the following.
- Export the table with
mysqldump.exe
General:
mysqldump.exe [database-name] --result-file=[path-to-file]\[file-name].sql [source-tablename]
My example:
mysqldump.exe developj5 --result-file=C:\tmp\dump.sql jx_visforms_6
- Delete all lines in dump.sql that do not start with 'INSERT INTO'.
- Import the file with
mysql.exe
General:
mysql -u [database-user] -p[password] [database-name] < I:\SandBox-IV\Java\mis\mis\sql\mis.sql
My example:
mysql -u devuser -p236598452XX developj5 < C:\tmp\dump.sql
The contents of dump.sql:
Code:
INSERT INTO `jx_visforms_60` VALUES (1,1,'2024-09-06 06:39:03',0,NULL,NULL,'172.20.0.1','0000-00-00 00:00:00',0,0,'value2\0, value3',NULL,NULL,'value-1\0, value-2 ');
INSERT INTO `jx_visforms_60` VALUES (2,1,'2024-09-06 06:43:09',0,NULL,NULL,'172.20.0.1','0000-00-00 00:00:00',0,0,'value1\0, value3',NULL,NULL,'value-1\0, value-3');
The two new additional form data records are then read in correctly.
The multiple selection of the two list boxes is retained.
Best regards, Ingmar
I recommend you the new and up-to-date documentation for Joomla 4:
.