#372 How to encrypt esisting hsqldb database?

[Abid Maqbool]

Hi! I have a very complex and large HSQLdb I can connect my it to file baized or embded database using:

db_url: C:\My Database\My-Database;crypt_key=6C3773BE334A8D343006DD588D33F780;crypt_type=AES;shutdown=true
user: ms_db_user
pass: MyPaSs

Do to some technical reson, I have export my Database to file e.g.

--<export script statement> ::= PERFORM EXPORT SCRIPT FOR DATABASE [ { STRUCTURE | DATA } ] [WITH COLUMN NAMES] TO <single-quoted file path>

PERFORM EXPORT SCRIPT FOR DATABASE TO 'C:\My Database\backup.sql'

I can simply run my databse by just renaming backup.sql to My-Database.script, until now all is okey, i can now connect using

db_url: C:\My Database\My-Database;shutdown=true
user: ms_db_user
pass: MyPaSs

But problem is that the exported script e.g. backup.sqlis in decrypted formate (or plain formate), But I want to encrypt it same as like before it was, how can I?

If I simply try to connect using orignal url then i will see

Not in GZIP format

So How can I enc my existing database (not new one)?

Also I can't simply import database using

--<import script statement> ::= PERFORM IMPORT SCRIPT DATA FROM <single-quoted file path> { CONTINUE | STOP | CHECK } ON ERROR

PERFORM IMPORT SCRIPT DATA FROM 'C:\My Database\backup.sql' CHECK ON ERROR

[Fred Toussi]

Try:

PERFORM IMPORT SCRIPT DATA FROM 'C:\My Database\backup.sql' CONTINUE ON ERROR

[Abid Maqbool]

@fredt Still it's not working by using your provided solution!

I have exported HSQLdb v.2.7.3 database using:

PERFORM EXPORT SCRIPT FOR DATABASE TO 'C:\My Database\backup.sql';

And try to import using:

PERFORM IMPORT SCRIPT DATA FROM 'C:\My Database\backup.sql' CONTINUE ON ERROR;

The error:

SQL Error [S1000]: error in script file  line: 1 org.hsqldb.HsqlException: General error: SET DATABASE UNIQUE NAME HSQLDB73E23237C2

And stacktrace exception:

org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [S1000]: error in script file  line: 1 org.hsqldb.HsqlException: General error: SET DATABASE UNIQUE NAME HSQLDB73E23237C2
    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:130)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeStatement(SQLQueryJob.java:631)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.lambda$2(SQLQueryJob.java:522)
    at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:192)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.executeSingleQuery(SQLQueryJob.java:541)
    at org.jkiss.dbeaver.ui.editors.sql.execute.SQLQueryJob.extractData(SQLQueryJob.java:1001)
    at org.jkiss.dbeaver.ui.editors.sql.SQLEditor$QueryResultsContainer.readData(SQLEditor.java:4239)
    at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.lambda$0(ResultSetJobDataRead.java:128)
    at org.jkiss.dbeaver.model.exec.DBExecUtils.tryExecuteRecover(DBExecUtils.java:192)
    at org.jkiss.dbeaver.ui.controls.resultset.ResultSetJobDataRead.run(ResultSetJobDataRead.java:126)
    at org.jkiss.dbeaver.ui.controls.resultset.ResultSetViewer$ResultSetDataPumpJob.run(ResultSetViewer.java:5166)
    at org.jkiss.dbeaver.model.runtime.AbstractJob.run(AbstractJob.java:117)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:63)
Caused by: java.sql.SQLException: error in script file  line: 1 org.hsqldb.HsqlException: General error: SET DATABASE UNIQUE NAME HSQLDB73E23237C2
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source)
    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:335)
    at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:128)
    ... 12 more
Caused by: org.hsqldb.HsqlException: error in script file  line: 1 org.hsqldb.HsqlException: General error: SET DATABASE UNIQUE NAME HSQLDB73E23237C2
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.result.Result.getException(Unknown Source)
    ... 17 more

Any more help, what is going wrong!

[Fred Toussi]

The Guide states: "The file to be imported must be a file exported with the EXPORT SCRIPT statementslisted above with DATA qualifier."

1. Export the STRUCTURE and DATA of the database to two separate files.
2. Edit the file for the structure of the database and keep just the essential settings and CREATE commands.
3. Use SqlTool to load the edited file.
4. Use the PERFORM IMPORT SCRIPT command as I mentioned before to import the file containing the exported DATA.