Help needed on Repository database change

[spsa]

I have downloaded the EBASE XI V5 community version for evaluation and installed on a Win 2012 Platform. As part of our requirement, I would like to use SQL 2012 Std as our database of choice for both repository and also to interact with the workflow engine to fulfill our goal.

As stated in the help files I used the 3 SQL scripts - 01-create_DB_sqlserver.sql, 02-repository_tables_sqlserver.sql and 03-sample_forms_tables_sqlserver.sql (found C:\ebaseXi_5.0\UfsServer\databaseSchemas\sqlserver) to generate the databases and the tables and changed the config file ebase.xml in the folder C:\ebaseXi_5.0\UfsServer\tomcat\conf\Catalina\localhost.

However, on starting up things didnot work and when I looked at the 3 SQL scripts, I found that the 1st script generates the 2 databases UFS and ebase_samples.

However the 2nd script which I expected to be using the UFS database to create the table and the 3rd script which in my view should create the tables in the ebase_samples were not doing their job. Instead they were writing direct to the masterDB in SQL server as these scripts do not have the directive USE UFS or USE ebase_samples at the start of the scripts.

My question to the forum are

1) why is this and in that case what is the purpose of creating 2 empty databases. When I checked the MySQL scripts, they appeared OK.

In addition, the SQL script does not drop the tables workspace_snapshot_actions and workspace_snapshot_files but simply creates them. Is this also a bug?

2) I created an additional user (in addition to admin) with the same privileges as admin in the original derby database. However, when the SQL database was created, it only created the admin database and didnot create the the 2nd user or any other user.

Thus, if I want to create something in derby and then move it to SQL server is that not possible?

3) When the repository with SQL did not work, I changed back the config file ebase.xml to point to the derby and restarted all the services including the computer. However after starting back, when I tried to start the Server Admin console I keep getting the error

Logon failed, reason:
description: com.ebasetech.ws.core.cxf.client.CXFExceptionWrapper: Could not invoke service. java.lang.NullPointerException

Any idea why this is and what is the remedy.


We wanted to use this product as part of our project but seem to have hit the wall straight away. Any help would be appreciated.

Regards

[Hovik]

I think you have come across couple of issues in the sql scripts. I will look into these today.

In the meantime, to get you working with the Derby repository, before switching to SQL server did you export the existing content as suggested by the documentation? If you did, please import that file, restart the client and try starting the admin console. Otherwise, it may be quickest to re-install and stay with the Derby repository until I fix the issues with the Sql Server scripts.

Note that you will not lose anything you create while using the Derby repository, when you later switch to using Sql server.

Hovik
Ebase support

[Hovik]

Hi again,

1) Both issues you reported have been fixed. You can download the new versions of the sql files here:

http://www.ebaseftp.com/download/forum/ ... server.sql

http://www.ebaseftp.com/download/forum/ ... server.sql

2) I'm afraid you're right. Currently you can't move users, roles etc and you will need to manually re-create them following a switch of repository. This should not normally be a problem as you would switch repository at the outset. However we do see that this may not be the case at all times.

In this version of ebase Xi (5.0.1), whatever you create or configure using the admin console can not be moved to a new system. The exception to this are Database connections and Email accounts which can be deployed to a target system.
We plan to address these in a future release.

3) Do you still have the version of Ebase.xml which prevented you from using the admin console? if so please email it to support@ebasetech.com.
Could you start the admin colsole and then get the error when logging in, or did you get the error as soon you clicked "Start Server Admin App"?

[spsa]

Dear Hovik

Thanks for the reply. I fixed up the SQL scripts myself and when I ran them , it populated the tables as it should. However, I could not import the workspace entities as stated in the manual.

In my Designer workspace, I created a project called AlloyPlating with two folders named UserForms and Database.

When I exported the project it created a zip file with meta data inside etc at the export location I chose. However, after changing the repository when I tried to import the project using the exported zip file, the import screen came up with a message saying 0 items to import.

Since this was happening, I decided to go back to the derby by commenting out the SQL JDBC connection string and reenabling the derby. Once this was done, I stopped and started the EBASE service and restarted the machine.

Once I tried to open the server admin console I started getting the error message , I mentioned earlier. However, I could get the Designer to run OK and it showed the server to be not running. (BTW, I am using a separate server and client configuration).

Since then I reinstalled the environment and would try again and let you know the outcome.


Also, I have another question in relation to the change of repository. At the time of installation, the system asks for a password for the admin user which I provided. However, in the SQL script, it appears that you have a fixed password which has been created using the encryption key specified in the SQL script. Does this mean that the password created during the install is also lost as a result of change of repository? Please clarify.

Also what is the default password for the admin user created by the SQL script?

Regards

Atish

Hi again,

1) Both issues you reported have been fixed. You can download the new versions of the sql files here:

http://www.ebaseftp.com/download/forum/ ... server.sql

http://www.ebaseftp.com/download/forum/ ... server.sql

2) I'm afraid you're right. Currently you can't move users, roles etc and you will need to manually re-create them following a switch of repository. This should not normally be a problem as you would switch repository at the outset. However we do see that this may not be the case at all times.

In this version of Ebase Xi (5.0.1), whatever you create or configure using the admin console can not be moved to a new system. The exception to this are Database connections and Email accounts which can be deployed to a target system.
We plan to address these in a future release.

3) Do you still have the version of Ebase.xml which prevented you from using the admin console? if so please email it to support@ebasetech.com.
Could you start the admin colsole and then get the error when logging in, or did you get the error as soon you clicked "Start Server Admin App"?

[spsa]

Hi Hovik

I have observed some interesting quacks during this repository change. May be you can test it in your lab.

Initial State: I made a fresh install of the EBASE XI 5 community edition as a server mode installation and both designer and server were runnig fine.

I have two drives in the computer and the workspace was defined in Drive D :\Ebase\Workspace. Both Server and Designer were configured to work off this workspace. Also in the same folder D:\EBase I created a folder called EXIM for exporting and importing my project.

Steps:
a) I create a base project called MyProject where I had two folders Forms and DatabaseConnections. The Forms had 1 form called MainForm.

b) Using your updated SQL scripts, I ran them to create the two databases UFS and EBASE_SAMPLES and also created the user 'ufs' as dbo for these two databases in SQL server.

c) I changed the ebase.xml file and uncommented the SQL JDBC connection parts (replacing the ebt999 server name with my server name ) and commented out the derby database.

d) I exported the minimal project using the Designer Export tool to the directory D:\Ebase\EXIM. BTW, the export options shown in the documentation is not up to date and differs significantly from that of version 4.


Here again I have a question. Unlike version 4 where the project was stored in a the UFS repository, as per changes in Ver 5, it is mentioned that the project is now stored in a separate location which is accessed by both the designer and the server. This path is also setup during the initial stages. Thus could you please explain why we need to do the export and import as it is not going from one database to the other?


5) Anyway, after exporting, I stopped the server, restarted the server and rebooted the machine.

6) Upon reboot, I expected that the server would now see the SQL server for UFS and Ebase samples since I commented out the derby in the ebase.xml. I logged on to the server OK and found under the database connections that the old derby is still connected.

7) I used the designer to import the same project although it did not make sense to me since it was not a DB to DB transfer. Again in ver 5 the menus are totally different and not what is shown in the manual.

Since the old derby was still showing as connected and not the SQL server, I created two database connections for UFS and EBASE_Samples to the SQL using the database wizard. Both connections were successful.

9) I deleted the derby connections UFS and EBASE_SAMPLES from the database connections, stopped the server and restarted. The server started up OK with connections I created in Step 8.

Since the ebase.xml was not being used, I decided to delete the database connections to SQL from the DATABASE Connections under server admin and restarted the EBASE service. When it booted up, I could logon to the Server Admin console and under the database connections, it did not show any connections .

Thus my question to you is:

i) Given that the workspace is shared between the designer and server in this instance what is the purpose of having this export and Import.

ii) Why is it that by changing the connection string in the ebase.xml, I am not able to switch the repository.

iii) Even when no database connection is defined how is the system still running? Is there a cache that internally looks at derby?

[spsa]

The mystery of changing the database repository is resolved. It is much simpler and there is no need for certain procedures as in Ver 4.X.

If a change of repository is needed at the start of a project the following needs to be done:

a) To prevent corruption, close the Designer and preferably top the EBASE server.

Replace the connection string for derby (comment it out and uncomment the SQL Server connection string and provide correct username/password servername etc. The default user name ufs if used, needs to be created in the SQL server and given appropriate data read write permissions in the SQL level - make them part of dbo schema for these two databases UFS and EBASE_Samples.

b) Using the updated SQL script, create the two default databases ufs and EBASE_Samples. I would suggest that in your updated script you also incorporate the creation of the two SQL users as well.

c) Once this is done, start the EBASE server using the the command start_ebaseserver.exe. The Ebase server should start OK as per the command prompt.

d) try to open the Server admin console. The browser might report as in my case that no connection could be made. If this be the case, Stop the Ebase Server and Restart. Also, one might actually use the Ebase Service Ebase_xi_V5w.exe located in the folder C:\ebaseXi_5.0\UfsServer\tomcat\bin.

Once the Ebase comes up, try loading the server admin page. At this stage the default admin user will go back to the default password 'admin'.

e) Once logged in, go to the Database Connections. In this screen the connection type will still show as 'Apache derby' . This I believe is a bug. manually change the connection type to SQL Server and test the connection.

f) As a test, create a new user. This user will now appear in the SQL table sec_users in the SQL UFS database.


There is no need to perform any export and import as the server and designer can be programmed to point to the same location where project files are kept.

Its all good after that.

Cheers

[Hovik]

Thank you for the effort you put in identifying the problems and for your comprehensive feedback.

I totally agree with most of the points in your last post. And of course you are right in saying that there is no need to export/import anything.

The documentation was for V4 and that was the main cause of confusion.
I have now changed this to reflect the steps required to change repository in a V5 system.

Some comments in reply to your points:

b)

I would suggest that in your updated script you also incorporate the creation of the two SQL users as well.

We have considered this in the past, but as the syntax for creation of logins and users has changed in later versions of Sql Server, there doesn't seem to be any way of doing this in a script which will work in all version of Sql Server. So we will include this step in the instructions.

d)

The browser might report as in my case that no connection could be made.

I suspect this was caused by the browser caching. You should not need to start the server twice.

e)

Once logged in, go to the Database Connections. In this screen the connection type will still show as 'Apache derby' . This I believe is a bug. manually change the connection type to SQL Server and test the connection.

Leaving the connection type as 'Apache Derby' will not cause any problems at this stage. The system will behave perfectly well and you can start the Server Admin App.
The ONLY time the connection type is used is when using Ebase to CREATE a DB table based on the database resource.

So yes, it needs to be set correctly, but only for the reason above.