I have this MySql query:
SELECT IDno, Company, des_decrypt(AccountNo, “myPassWord”) AS ‘AccountNo’ FROM account;
How do i do this in LibreOffice Base with its HSQLDB engine?
Thanks, R
Your question doesn’t seem clear. Are you using MySQL from Base or are you switching to HSQLDB? HSQLDB doesn’t appear to have des_decrypt function.
Hello,
While waiting for comment response, ran some simple tests using Mint 18.3, LO v6.2.0.3 and MySQL connection. Only problem encountered was returned item was an object. Cleared this problem by casting the to a Char field then worked OK.
Data entered without password:
SELECT cast(DES_DECRYPT(testField) AS CHAR(100)) AS "DeCrypted" FROM test;
Data entered with password:
SELECT cast(DES_DECRYPT(testField, 'XXXX') AS CHAR(100)) AS "DeCrypted" FROM test;
If you have Base connected to MySQL, then HSQLDB is not involved.
@kasloman Yes the error is because you have indicated a column name instead of a literal. If you look at the example in my answer, the field name is surrounded by quotes and the password is surrounded by apostrophes. DES_DECRYPT is looking for the field to decrypt & if originally password protected, the password literal used in the encrypt function.
I am connected to a MySql database.
SELECT “IDno”, “Company”, cast(DES_DECRYPT(“AccountNo”, “myPW”)AS char(50)) AS “AccountNo” FROM “account”;
Returns - "The data content could not be loaded.
Unknown Column myPW in field list.
@kasloman Please do not respond by using an answer unless it is an answer to the original question. Instead use add a comment which is below each answer. If clarity is to be added to question, then simply edit the question & note the edited portion.
Many thanks. Worked like a charm changing “myPW” to ‘myPW’ in single quotes.
R