Answers to: How can I assign a MS SQL-based XpertIvySystemDataBase as the Designer's SystemDb

Comment by Reguel Wermelinger on Reguel Wermelinger's answer

Reguel Wermelinger — Mon, 12 Sep 2016 08:44:28 -0400

I'd try to work with a delta insert.

Start a Designer with an empty workspace
Export the SystemDb at the current state
Import your project and start the engine
Create your tasks that you wan't to re-use
Export the SystemDB again
Work with a diff tool like WinMerge or similar to find the differences between the first and the second DB export. Try to only apply these insert statements which we're added by your engine start + task creations after a re-boot of the engine

Comment by Dieter Staudt on Reguel Wermelinger's answer

Dieter Staudt — Fri, 09 Sep 2016 13:06:05 -0400

Dear Reguel,sorry. It's me again. All tries restoring the saved database don't work.

DROP... and COPY the complete saved file into the db window the error message "invalid schema name in statement (CRATE SCHEMA PUBLIC AUTHORIZATION DBA ) apperas.
Only COPY of all INSERT statements: following error message "violation of unique constraint SYS_PK_48: duplicate value(s) for column(s) ... of the first table form the instert statements" appears.
and DELETE and INSERT do not work because there are problems with missing forreign keys and so on. Do you have any idea? Best regards Dieter

Comment by Dieter Staudt on Reguel Wermelinger's answer

Dieter Staudt — Thu, 08 Sep 2016 15:44:26 -0400

Dear Reguel, thank you. the first step works fine - I have all "my" case- and task-data etc. No I must try to restore this sql-file.

Comment by Reguel Wermelinger on Reguel Wermelinger's answer

Reguel Wermelinger — Thu, 08 Sep 2016 15:14:39 -0400

Reguel is ok :)

the export script will be written to the path you specify as parameter. In the example above the file c:/temp/myHsqlDbExport.sql will be written when the command is executed...

Comment by Dieter Staudt on Reguel Wermelinger's answer

Dieter Staudt — Thu, 08 Sep 2016 15:12:11 -0400

Dear Sir,

thank you for your answer. Sorry, where can I found the Export-SQL-Script? There was no attachment to the e-mail or our virus detection has deleted it.

Best regards Dieter Staudt

Answer by Reguel Wermelinger

Reguel Wermelinger — Thu, 08 Sep 2016 14:57:01 -0400

Create a Database Configuration:

database = Hypersonic SQL Db
driver = HSQL Db Memory
DatabaseName = AxonIvySystemDatabase

Open the SQL query window and execute:

SCRIPT 'c:/temp/myHsqlDbExport.sql' to export the full system db schema plus its content
DROP SCHEMA PUBLIC CASCADE removes the default system db after a restart
Paste the content of the previously exported SQL file to re-create the old version

Normally you will run into escaping and encoding problems in the last step. A practical solution could be that you only re-import the INSERT statements instead of the whole SCHEMA+DATA to avoid these problems.

Comment by Dieter Staudt on Reguel Wermelinger's answer

Dieter Staudt — Wed, 07 Sep 2016 14:00:30 -0400

Dear Reguel (is it ok?)

Is there no possibility to store the content of the Hypersonic MEMORY Database for developing and testing functions like ITask, TaskQuery etc.? Now I must create every day new cases and tasks in the Designer.

Best regards Dieter

Answer by Reguel Wermelinger

Reguel Wermelinger — Wed, 07 Sep 2016 12:47:43 -0400

You can setup a standalone DB (like MsSql) with an Axon.ivy Engine Configuration UI. To run this DB in a Designer simply copy the /configuration/serverconfig.xml of the engine into your designer. With this approach you will need a license as well for the designer.