Tips & tricks: Switching between data sources

In case of arcplan applications, it is quite common that the relational data source from which the framework data for navigation, user administration, comments etc. are extracted are located on one server for the development, test and/or production version. This complicates the switch between these databases, as almost all metadata apart from the server connection is placed in the arcplan repository. In particular, this includes the database name, which now complicates the simple switch between various databases on the same server.

Upon the initial access of the database window, all available metadata information is requested from the database and written in the related repository file (.apr). By default this information consists of the name of the OBDC connection, the database name, the schema, the names of tables and columns. Subsequently, this metadata is used in a query in order to generate the correct statement (... FROM dbname.schemaname.tablename).

If various servers are used, the database names, schemas and tables are named identically in all databases, and only the entry for the ODBC connection is adjusted and moved to the other server. If the databases are located on the same server (e.g. SteuerDB_Test and SteuerDB_Prod), this is not possible, as in this case, the database name deviates from the database name initially written into the repository.

One problem, different solutions

One possibility is to write separate SQL statements. To this end, the statement is compiled in an individual field and sent to the database via the SELECT(;) formula.

Using SQL statements entails various advantages and disadvantages. It is generally advantageous that the queries are very performant; and they can be designed very variably by compiling individual text components. Further, highly complex queries can be conducted via the SQL statements, and it is theoretically possible to change the databases during the run-time; however, the latter should be avoided for reasons of consistency.

A disadvantage is primarily posed by the lack of clarity and readability of the application; further, there is no way to highlight syntax for the SQL statements, which makes them harder to read. Additionally, application designers mandatorily need SQL skills, and the easy use of the connecting arrows in order to develop queries is no longer possible.

Another possibility is to remove the database name from the metadata information in combination with the respective setting of the database after the connection for the session has been established. This requires a number of steps:

Ensuring that an Entry in the Repository File (.apr File) exists for every Table

In order to ensure that an initial entry is generated for each table in the repository file, the table must be changed once in the arcplan database window and then, the repository must be saved. To this end, it is best to set up a primary key for the respective table in arcplan, save the database and then remove the key. This step enforces an explicit writing of the meta-information onto the repository file without saving a real change.

This generates an entry in the .apr file of the database which looks approximately as follows:

Removing the Database Name

The section highlighted in blue in the above screenshot is important for step two. It is the database name. In order to remove it from the metadata, the content between the tags must be removed and the file must be saved. In this context, the apr file can be edited with any text editor. This must be done for every table. In particular, it must also be done once for future/new tables after they are generated. The entry must then look as follows:

After closing and reopening the database in arcplan, the database name will no longer be displayed with the entry that has just been adjusted:

When requesting data from this table, the statement has changed, as well. The database name has now disappeared:

Setting the Database

As now, there is no longer a special set database, the connection will always be made to the standard database determined in the User/Login or in the ODBC connection (unless otherwise adjusted). As it is possible to determine a standard database in various locations, however (e.g. User/Login or ODBC connection), and the last command takes precedence, it cannot always be clearly reconstructed.

Therefore, the very first command in the application after establishing the connection should be “USE dbname” in order to correctly set the database for the further course of the session.
(As already mentioned above, it is technically possibly to change the database during the run-time by a renewed USE statement; however, this is not recommended as arcplan only updates the required objects and this may lead to inconsistencies and to an amalgamation of data from both databases).

The advantage of this method is the constant handling of database queries. As soon as the database names of the tables were removed, arcplan will behave as always. Database queries can still be done by arrows. Thus, it is not necessary to have SQL skills in order to develop reports. In addition, the query by SQL statement is possible at any time, and it may thus still be used in cases in which it is more beneficial.