Topic overview

This article gives an overview about the access to Comarch ERP Enterprise databases via an Comarch ERP Enterprise ODBC data source.

Target group

The article is aimed at developers and consultants who generate reports. Its focus is set on the ODBC interface of Comarch ERP Enterprise and its specifics. The document’s content is the basis for creating reports in the system landscape of Comarch ERP Enterprise.

Definitions

ERP system ODBC driver
The ERP system’s ODBC driver makes it possible to read data residing in various databases of the Comarch ERP Enterprise system. For example, reporting tools or Office applications can use it to access this data. Permissions defined in the Comarch ERP Enterprise system are also taken into account for queries.

JDBC driver for ERP system
The ERP system’s JDBC driver provides read access to data in various databases of the Comarch ERP Enterprise system. For example, reporting tools or Office applications can use it to access this data. Permissions defined in the Comarch ERP Enterprise system are also taken into account for queries.

Preconditions

The ODBC driver must have been installed on the client computer and a Comarch ERP Enterprise ODBC data source needs to have been configured. To use the JDBC interface, you need to install the JDBC driver. Besides it is required that the reader:

• Is familar with Comarch ERP Enterprise from the user’s perspective,
• Is familiar with the basics of data modeling in Comarch ERP Enterprise,
• Is familiar with creating reports and
• Has knowledge in the data modeling with relational databases.

Supported third-party systems for creating reports

The 32-Bit ODBC driver has been successfully tested with the following external systems:
– Crystal Reports 2013
– Microsoft Query from Microsoft Office 2013

The following third-party systems should be compatible with the 32-Bit ODBC driver, but are no longer tested and are not supported by Comarch:
– Crystal Reports 9, 10, 11, 2008 and 2011
– Cognos Impromptu 7 and Powerplay
– Cognos 8 Data Manager
– Microsoft Query from Microsoft Office 2007 or later

The 64-Bit ODBC driver has been successfully tested with the following external systems:
– Crystal Reports 2020

Database interface data model

The database interface offers a prepared data model for the report application, which follows the modeling model of Comarch ERP Enterprise. The relational schema existing on the database is hidden in large part. Thus, the report creation is significantly simplified since known names of business objects and their attributes will be displayed, and some modeled relationships between business objects will be analyzed automatically. This considerably reduces the number of joins to be modeled in the report.

The preparation of the relational schema described below requires that the reader of this article is familiar with Comarch ERP Enterprise, its functional data model, and the most important modeling terms such as:

• business object, business entity, dependent
• relations
• data types, special parts
• primary key and business key

The access to a database interface is always effected in the context of an organization that is referred to as active organization.

Table names

The database interface provides the tables organized in catalogs for the business objects and views saved on the database. The CISAG main catalog contains all tables that are organized in subcatalogs.

A table of a business object is classed with one of the DEPENDENT or ENTITY subcatalogs according to its category. The tables of the views and OQL views defined on the database are to be found in the VIEW and VIEWOQL subcatalogs. The VIRTUAL subcatalog contains the virtual tables, and the FUNCTION subcatalog contains the virtual functions.

If a partner made its own developments, new tables will be included in the subcatalogs according to their category.

The table below illustrates the structure:

A table name is derived from the name of the corresponding business object or view. In such case the name of the namespace will be reduced by the fixed parts, i.e. .obj and com., or com.cisag. in case of CISAG objects will be left out. Points will be replaced by underscores.

A table can have a label. The description specified in the business object and/or view definition is displayed as label text. The columns of a table are the attributes of a business object; the column name is taken from the respective attribute name. A column may have a label that is defined in the data description of the logical data type belonging to the attribute. The set display language of the data source determines the language of the labels. The report application formulates SQL statements on these tables in order to generate the desired report.

Column names

The attribute name is used as column name. There exist particular column names and/or suffixes that indicate a special meaning of the column. Subsequently, special column names are listed from which the report developer can derive category or content information without knowing the details about the concrete category or content of the column.

Marking GUID attributes

GUIDs (Global Unique Identifiers) are used to form unique keys for business objects and refer to them via these keys. Thus, the columns based on the Comarch ERP Enterprise data type, GUID, are the most important candidates in order to link tables in the data modeling. These columns can be highlighted if the column names are marked with an appended underscore.

Resolving array attributes

With respect to an array attribute of a business object, the corresponding table showing the cardinality of the attribute contains a respective number of columns. In this case, the index is added as a suffix to the attribute name.

Special BusinessKey_text column

If certain conditions are met by a business object, the BusinessKey_text column will be added to its table. This column contains the instance string of the business object instance. When analyzing the request, the application server loads the related business object instance via the persistence service. This may lead to longer response times in case of requests with a great quantity of results.

The column will only be added if the following conditions are met:

• The business object has a business key
• The primary key of the business object consists of an attribute

This column may only be used for displaying.

Resolving relationships

In case of certain relationships of a business object, the business key and the description of the destination object are automatically inserted in the table as columns. The columns will only be inserted into the table if the relationship meets certain conditions:

• The cardinality of the relationship needs to be 1:1
• The destination business object has exactly one attribute of the GUID data type as primary key
• The relationship is exactly defined with one attribute; the target attribute is the primary key attribute
• The source and destination objects are defined on the same database
• The destination business object has defined a business key

Restrictions in case of several relationships per attribute

If several of these relationships are defined for the business object with the same source attribute, but with a different target attribute, then the special _BK and _text columns generated for the column of the source attribute may not be used in the report. In this case, the generation of the table metadata is not deterministic. One of the relationships is used for generating special columns, the others are ignored. Thus, it is possible that these special columns refer to another destination business object than, for example, during the report development.

In such a case, the required relationship must be manually added to the report in order to get the business key and instance string needed. Open the business object definition of the source business object using the Development objects application. The Relationships tab shows the defined relationships towards other business objects. Identify the required relationship which you have to add manually in the report in order to get the business key and instance string needed. Link the required table of the destination business object via the column of the source attribute and the column of the target attribute with the table of the source business object. Therefore, please use a LEFT OUTER JOIN. The added join needs to have the following structure:

source_table LEFT OUTER JOIN target_table ON source_column = target_column
Do not use the _BK column within the report, but the corresponding column in the table of the destination business object, which contains the business key.

Do not use the _text column within the report, but the Business_Key_text column in the table of the destination business object.

Instance string column with _text suffix

If the above-mentioned conditions have been met, this column will be added. The name of such column is derived from the name of the source attribute and the _text suffix. It is given the label from the data description of the source attribute. The content of the column is the instance string of the business object instances. When analyzing the request, the application server loads the related business object instance via the persistence service. This may lead to longer response times in case of requests with a great quantity of results.

This column may only be used for displaying in the report, but not for selecting (JOIN / WHERE clause), grouping (GROUP BY) and sorting (ORDER BY clause) on the database side.

Business key column with _BK suffix (BK column)

If the business key of the destination object is to be accepted, the following condition needs to be met in addition: The business key of the destination object consists of exactly one attribute from the string category.

The name of the column is derived from the name of the source attribute and the _BK suffix. The label is determined on the basis of the data description of the logical data type belonging to the business key attribute.

This column may only be used for formulating join conditions. If a BK column is not used for displaying only, none of the subsequently described statement optimizations will be performed.

Optimizing the database statements

Consider that _BK columns are resolved in different ways depending on the setting of the ODBC access option on the application server. If _BK columns of master data are accessed, the contents are determined either by the persistence service or the database. Provided that the cache of the application server is big enough, the calculation of the content by the persistence service is more favorable than the resolution by the database.

If the ODBC access is restricted, only master data of the sizes S and M will be resolved by the persistence service. Master data of the L, XL and XXL data sizes will be determined by the database. Thus it is avoided that any ODBC access blocks out other business objects from the cache of the application server when the access is restricted.

If the ODBC access is not restricted, all master data will be resolved via the persistence service. This way, other processes running on the application server might be constrained under certain conditions since data required by such processes will be blocked out from the cache. This setting is recommended for an application server that primarily processes any ODBC access.

You can set the ODBC access option in the System cockpit application for an application server.

Virtual relationship column

It is possible to extend a table of a business object by a virtual column whose content is calculated at runtime of the query. This virtual column is based on a relationship of the business object to a concrete business object.

The relationship has to meet the following conditions:

• The cardinality of the relationship needs to be 1:1
• The destination business object has exactly one attribute of the GUID data type as primary key
• The relationship is exactly defined with one attribute; the target attribute is the primary key attribute
• The source and destination objects are defined on the same database

The destination business object does not need to have any business key, and/or the business key can have several parts or another category as string.

If such a relationship exists with a business object and if a virtual column definition has been specified for the destination business object, the virtual column will be added to the table of the business object.

A class is to be implemented for the virtual column, which calculates the column content for each table row. The primary key for the respective instance of the destination business object is passed as input parameter for the calculation.

The name of the virtual column is derived from the name of the source attribute with an arbitrary suffix. Within the extended table, the name has to be unambiguous.

This virtual column may only be used for displaying.

Such a special solution can be used to reproduce a BK column if the destination business object does not meet the conditions for an automatically added BK column with respect to the business key.

Resolving value set attributes

An attribute of a business object, whose logical data type is based on a value set, is represented by the data source via two columns. One of the columns is described with the attribute name and contains the value set value that corresponds to the constant name. The name of the other column is composed of the attribute name and the _text suffix. This column contains the respective description for the value set value in the display language set for the data source. Both columns get assigned the label specified in the data description belonging to the logical data type of the attribute.

If a report is programmed for a value set attribute and/or if selections or conditions are specified on this, the constant name has always to be used. Thus, the report remains independent of changes in the description of the value set entries and independent of the selected display language. Report parameters derived from value sets are passed by Comarch ERP Enterprise in this form to the report application.

The _text column may only be used for displaying.

Selections may only be performed with valid value set constants in the constant column.

Undefined value

Columns based on value sets can also be undefined. This can be found out by checking the report for the empty character string with regard to the constant name. In this case, the related value set description is also the empty character string.

Invalid value

The <invalid> value for the value set description is a special case. This value is always returned by the data source if a value not contained in the value set is found in the records. This usually indicates a possible problem with underlying legacy data transfers. A selection for invalid values is not possible.

Displaying special ERP data types (special parts)

Comarch ERP Enterprise offers special data types that have complex structures driven by business needs. Amounts and quantities are the most important types. These are specially treated in ODBC.

Resolving attributes of the foreign amount type

An attribute which is based on the com.cisag.general.obj.ForeignAmount special part is represented by several columns in the table corresponding to the business object. These are in accordance with the part attributes and further attributes added by the data source. The names of the columns are derived from the attribute name and the corresponding suffix. An overview is given in the following table.

Resolving attributes of the domestic amount type

An attribute which is based on the com.cisag.general.obj.DomesticAmount special part is represented by several columns in the table corresponding to the business object. The names of the columns are derived from the attribute name and the corresponding suffix.

An overview is given in the following table:

All other currencies were converted from the exact amount entered according to the exchange rates in effect at the time of entry.

A value of 1 means that _amount1 is accurate, 2 means that _amount2 is accurate, and 3 means that _amount3 is accurate. A value of 0 means that none of the three currency amounts is an accurate value.

The following columns will be generated in the table for the totalValues.discountValueDomestic attribute of the
com.cisag.sales.obj.SalesOrder business object:

• totalValues_discountValueDomestic_amount1
• totalValues_discountValueDomestic_amount2
• totalValues_discountValueDomestic_amount3
• totalValues_discountValueDomestic_amountCorporate
• totalValues_discountValueDomestic_amountOrganization
• totalValues_discountValueDomestic_amount2_text
• totalValues_discountValueDomestic_amount3_text
• totalValues_discountValueDomestic_exact

Using columns with _amountCorporate and _amountOrganization suffixes

When using an attribute of the Domestic amounts type in a report, the following has to be considered in connection with multi-site and the output of the amount in the primary currency of the organization:

Single site: Creating a report

The _amountCorporate column contains the amount of currency 1, 2, or 3 of the domestic amount in accordance with the primary currency index of the client. The _amountOrganization column has the same content as the _amountCorporate column.

Multi-site: Creating a report

The _amountCorporate column contains the amount of currency 1, 2, or 3 of the domestic amount in accordance with the primary currency index of the active organization. This, however, can be an amount concerning another currency if the domestic amount to be output does not originate from the active organization.

The _amountOrganization column contains the currency amount of the active organization’s primary currency. It has been calculated based on currency 1 (corporate currency) of the domestic amount using a conversion factor. This value is calculated and does not have the quality of an exact value.

If you know that you prepare an intra-organizational report, i.e. you only have domestic amounts with the same currency allocations, the amountCorporate field is always to be preferred to the amountOrganization field as it contains the value from the domestic amount and is not calculated based on the corporate currency.

Columns across the table

If a business object contains at least one attribute of the Domestic amount type, the following columns will be added to the respective table:

Resolving attributes of the quantity type

An attribute which is based on the com.cisag.general.obj.Quantity special part is represented by several columns in the table corresponding to the business object. These are in accordance with the part attributes and further attributes added by the data source. The names of the columns are derived from the attribute name and the corresponding suffix.

The table below provides an overview.

Special link_text column

If a table is one of a business entity that has exactly one GUID as primary key, an extra column will be added to the table with link_text as name. This column contains a URL through which the respective business object instance can be opened by means of the default application for displaying/editing.

In a report, the value for the field of such a column can be specified as a hyperlink. Thus, the business entity addressed by the hyperlink can be opened from the report by means of the browser in Comarch ERP Enterprise.

This column may only be used for displaying.

Supporting blob attributes

A Comarch ERP Enterprise data source does not support any attributes of business objects, which are based on the Comarch ERP Enterprise Blob, SBlob or Text data types. Such attributes do not have any corresponding column in the table of the business object.

Exceptions

However, the Blob attributes of the com.cisag.app.general.obj.Text and com.cisag.app.general.obj.LongText business objects are supported by default. They are provided for collecting texts which will be saved as compressed files in the database. This way, a text module can be assigned to a business object via a relationship.

The Blob attributes of the virtual com.cisag.app.general.ItemImage and com.cisag.app.general.PartnerImage tables are supported. They include the binary data for the corresponding images.

The support of a Blob attribute for a business object and/or a virtual table can only be added if this has been registered before. The com.cisag.pgm.appserver.hook.ODBCRegistryHook hook, which is defined in the com.cisag.pgm.appserver.Server hook contract, serves this purpose. You can register Blob attributes in a conflict-free manner via a particular implementation of this hook.

A Blob attribute may only be used for displaying. The maximum Blob size currently amounts to 16 MB.

Resolving date and time stamp attributes

Comarch ERP Enterprise provides various time stamp and date types. All date and time stamp values are generally saved on the database as a time stamp with respect to the GMT time zone. If the data type also includes time zone information, the GUID of the time zone will be saved additionally on the database. For more information on the time stamp and date types in Comarch ERP Enterprise, please refer to the Data types of the type system article.

A time stamp or date value will be output allowing for its time zone information. If the value to be output does not have any own time zone information, the time zone will be used from the ODBC context. In the interactive ODBC operation, the time zone of the organization indicated with the creation of the data source determines which time zone is used as context. In a single site environment, the time zone of the client is used automatically. When reports are output via the SOM, such time zone is used which corresponds to the organization of the user logged on. If no time zone could be found for an organization, the time zone of the Comarch ERP Enterprise system will be used as fallback.

The database interface driver provides all time stamp and date values in the GMT time zone and in the time zone of the ODBC context. Therefore, an attribute which is based on a time stamp or date type is resolved in several columns.

Time stamp without time zone information

The following two columns are added to the table of the business object for an attribute which is based on the primitive TimeStamp data type:

Time stamp or date with time zone information

The following three columns are added to the table of the business object for an attribute which is based on a special logical data type for the time stamp or date:

Value area of time stamps

The following symbolic values are used in ODBC for time stamp and date values:

• undefined value – the 01.01.1000 00:00.000 value with respect to the GMT time zone represents a non-defined time stamp. This value, for example, is provided by the updateInfo_deleteTime_gmt column if the business object has not been marked for deletion.
• minimum value – the 31.12.1000 00:00.000 value with respect to the GMT time zone represents the smallest time stamp that can be processed in ODBC. All time stamps falling below this one will be mapped on this time stamp, exempt from the value for the undefined time stamp.
• maximum value – the 31.12.4712 00:00.000 value with respect to the GMT time zone represents the greatest time stamp that can be processed in ODBC. Any greater time stamp will be mapped on this time stamp when transferred to the ODBC server. If a greater time stamp than this one is to be output by the ODBC server, the 31.12.9999 00:00.000 value with respect to the GMT time zone will be returned.

Time data

In order to be able to calculate absolute time data as, for example, 02:15 independently of the time zone, these data will be saved in Comarch ERP Enterprise under the date 01/01/1970 with respect to the GMT time zone. The ODBC driver transfers such time data together with the date to Crystal Reports. If you want to use a time specification in a report template, you should either deduct this date from the value transferred or hide the date in the output field.

Resolving the change information of a business object

If, with respect to a business object, it has been specified that the system maintains change information (Log user and time point option in the Development objects application), special columns will be added for this purpose to the table of the business object.

Supporting user-defined fields

If the user-defined fields are based on an object extension, i.e. they are saved in their own database table and not in a Blob data structure, they can be queried via ODBC. The database table of the respective business object is extended automatically by the columns for the user-defined fields.

The user-defined fields for business objects can be queried and/or used over the Comarch ERP Enterprise data source like other attributes. The name of the corresponding column for the attribute in the table is composed of the attribute name and the _ext suffix. A user-defined field is resolved in several columns within the table depending on the data type. The resolution is performed analogously to a normal business object attribute. The label specified during the creation of the user-defined field is displayed as the column label.

Data type selection field

The Selection field data type of a user-defined field is resolved in a similar way as the Valueset data type. The following table shows the corresponding columns.

It can only be used for display.

Time-dependent business objects

Some business objects can be time-dependent (e.g., com.cisag.app.general.obj.Partner business object). When querying data of time-dependent business objects, only the versions valid at the current time point are taken into account – as customary in Comarch ERP Enterprise.

Time-dependent business objects have the validFrom and validUntil attributes. If these attributes are explicitly included in the query, i.e., if they are used as return column, selection and sorting criterion or for groupings, all versions of the business object instances will be taken into account.

Virtual tables

A virtual table does not exist on the database, but is emulated by SAS. In principle, such a table can be accessed like a database table. The content of a virtual table is calculated at runtime of the query with regard to the input parameters. The result can consist of 0 to n rows. Additional columns can be added to this result by means of joins to tables of other business objects. The number of result rows is solely determined by the virtual table. The results for columns from the joined tables are determined via the persistence service of SAS and not directly via a database statement.

Considerable restrictions apply to the complexity of the query:

Only LEFT OUTER and RIGHT OUTER are allowed as join categories. For this purpose, the virtual table must always represent the main side of the join, i.e., the left side with a LEFT join and the right side with a RIGHT join. The condition for the join must be formulated on the primary key or business key of the business object that belongs to the joined table. Only = is permitted as an operator and all partial conditions must be linked by AND. These restrictions also apply to joins that are based on a table joined to a virtual table. Columns of the joined tables may only be used for displaying. As a consequence, a virtual table may not be joined to another virtual table.

A table of a view or OQL view may not be joined to a virtual table or depend on it since its content has to be specified on the database.

The columns of the virtual table may only be used for displaying, sorting, and formulating join conditions. Additionally, columns that are input parameters of the virtual table may also be used in the WHERE clause for parameter transfer.

The use of aggregate functions as well as other SQL functions, GROUP BY or HAVING clauses is not allowed

Sorting the result

The support of the sort order specified in the ORDER BY clause depends on the developer of the concrete virtual table. Normally, there will be a sort order defined by the developer; the specified sort order will be ignored then. For more details on the support of sort orders, please refer to the article about the concrete virtual table.

Assigning input parameters

The values for possible input parameters of the virtual table can be set via the WHERE clause. The name of a column that can be used as input parameter starts with the in_ prefix. Accordingly, the column names are to be chosen when programming the virtual table. Several input parameters to be set must be linked via AND. As operation for setting the parameter value, only = is allowed.

Other conditions may not be included in the WHERE clause.

In order to support Crystal Reports and its specific selection feature in TimeStamp columns (missing support of milliseconds), an exception exists for input parameters that are based on the Comarch ERP Enterprise TimeStamp data type. In such a case, an interval of the following form is also permitted as condition:
in_timeStampAttr >= dateValue1 AND timeStampAttr < dateValue2The dateValue1 value is transferred to the virtual table for the in_timeStampAttr input parameter; the other partial condition is ignored.

Virtual functions

A virtual function is a special table that returns a result row for a number of input values, which is calculated at runtime of the query. It does not exist on the database, but is emulated by SAS. It typically serves to extend the query result by additional columns.

A virtual function is usually linked to another database table via a join of the LEFT OUTER category. The join condition specifies how the values for the input parameters of the virtual function will be assigned. If a query contains a join, the query result will be determined first without considering the virtual function of the database. Afterwards, the values in the columns of the virtual function will be calculated for each result row. In this process, SAS calculates the values in the output columns of the virtual function for the value allocation of the input parameters, which has been specified via the join condition. A column that can be used as input parameter starts with the in_ prefix. The join condition may not contain any further partial conditions.

A virtual function may only be used in joins as shown hereinafter:

Database table A LEFT OUTER JOIN virtual function VF ON
A:columnName1 = VF:columnName1 [AND A:columnName2 = VF:columnName2]*

virtual function VF RIGHT OUTER JOIN database table A ON
A:columnName1 = VF:columnName1 [AND A:columnName2 = VF:columnName2]*

It is allowed to join several virtual functions to a database table. A virtual function can also be joined to another virtual function.

A virtual function may also represent the main side of a join. In this case, the same restrictions apply as to joins with virtual tables.

In addition, it is possible to specify values for input parameters of a virtual function via the WHERE clause (analogous to the virtual table). A value set in such a manner overwrites the value allocation specified by a join condition. For the parameter assignment, the same rules apply as to the virtual table.

In a join with a virtual function, a table of a view or OQL view may only be used on the main side of the join since its content has to be specified on the database.

A virtual function can be used on its own analogously to the virtual table. However, it will only produce a maximum of one result row.

The columns of the virtual function may only be used for displaying and formulating join conditions. Additionally, columns that are input parameters of the virtual function may also be used in the WHERE clause for parameter transfer.

Automatic optimization of database statements

The database statements sent via the ODBC interface of the application server will be optimized (if possible) before they are executed on the database. The optimization aims to avoid reading data from the database that are supposed to have been cached by the application server, but rather to load them from the cache of the application server. For that reason, it is intended to remove tables of master data business objects from the database statement since these will be maintained in the cache. The simplified database statement then will be executed on the database. Afterwards, the required master data will be loaded via the persistence service using the shared cache and added to the result. By observing certain rules when defining the joins between database tables, the speed of the report execution can be increased and the database load can be reduced.

A table of a business object will be removed from the database statement if the following conditions have been met:

• The data type of the business object is Master data, Configuration master data or Configuration master data (display).
• The expected data size of the business object is Small or Medium. If the application server works in the Unrestricted ODBC access mode, the data size will not be considered.
• The join condition has been exclusively formulated via all attributes of the primary key or the business key of the business object.
• The columns of the table are only used for displaying.
• In case of a time-dependent business object, only the current version is accessed.
• The com.cisag.sys.odbc.DisableJoinOptimization property is not enabled. This property serves to disable the optimization and is not set by default.

Configuring the application server

In the configuration of an application server within the system cockpit, relevant settings can be carried out for the ODBC access. Since access to the database interface via JDBC is newer, the configuration options are labeled ODBC. However, they also apply to JDBC access.

ODBC access setting option

Via the ODBC access field it can be set whether an application server should enable an ODBC access and, if so, in which ODBC access mode it should work. The unrestricted and restricted ODBC accesses are available as modes. These modes affect the behavior of the application server with respect to the allocation of database connections and the main memory usage during the automatic optimization of ODBC database statements.

An application server carrying out ODBC accesses as the main task should be operated in the unrestricted mode.

Limiting the usable database connections

It is possible to limit the number of the database connections used for ODBC accesses at the same time.

In the restricted mode, a maximum of 50% of the possible database connections is used simultaneously for ODBC accesses. Thus, it is ensured that other users can work interactively with the application server. In the unrestricted mode, all available database connections are used, if necessary, for ODBC accesses. This way it may occur that users will not be able anymore to work interactively.

Hyperlink configuration for business entities

For business entities, the special link_text column is added to the respective table. This one contains the URL in order to display the business entity in Comarch ERP Enterprise. For generating the URL, the application server that processes the ODBC request needs to be configured accordingly.

In the Target server for link attributes field of the application server settings within the system cockpit, the application server needs to be specified, on which the default application is to be opened with the business object instance as parameter. This can be another application server than the one that processes the ODBC request. However, it must be able to access the same database.

Authorizations

It is possible to set authorizations for the ODBC access at user and business entity levels.

Authorization at user level

To permit or prohibit the use of ODBC for a user, the following capabilities are available:

Authorization at business entity level

In addition to the capabilities, Comarch ERP Enterprise authorizations are analyzed at the business entity level with the ODBC access. Tables of business entities and their dependents, on which the logged-on user does not have any ODBC access authorization, are not visible to this user. Virtual tables and functions are mostly assigned to a business entity each and thus inherit its authorizations.

Modeling tips

In the following, the developer of reports receives tips for reproducing data models in Comarch ERP Enterprise and for avoiding modeling errors.

Naming conventions of data sources

The standard reports are connected with the standard data sources specified in the table:

The respective data sources have to be set up accordingly on the client computer. Alternatively, the data source to be used for a report can be adapted in the report application. For more detailed information on the Reports application, please refer to the Reports article.

Restrictions for the query of several OLTP databases

For technical reasons, it is not possible to switch a Comarch ERP Enterprise data source once chosen for an OLTP database with the interactive ODBC access, i.e. within an external application as, for example, Crystal Reports. This means if another OLTP database is to be queried, the report application needs to be stopped and restarted. Alternatively, of course, a second application instance may be opened.

Thus, a report cannot query several OLTP databases when it is executed via the interactive Comarch ERP Enterprise ODBC driver. However, a report can query an OLTP, the repository or configuration database as well as the OLAP database related to the OLTP database at the same time. In order to avoid this problem (which, however, is little feasible), a report could access two OLTP data sources at the same time, which refer to different application servers.

This restriction only applies to the interactive access, but not to the output of reports by means of the Comarch ERP Enterprise Output Manager (SOM).

Visualizing the Comarch ERP Enterprise data model

In order to be informed about the data model of a business object, it is helpful to consult the metadata of the business object. The Development objects application in the Software development framework serves this purpose. Another possibility for displaying the metadata of a business object is offered by the Development object menu item of an entity field in the context menu.

The metadata basically contain information on attributes, data types, indexes, relationships, and further properties of a business object.

The Data model diagrams application can be used in order to get a graphical representation of the data model on a quantity of business objects. You can find detailed information in the Data model diagrams article.

Data model of the report

The Comarch ERP Enterprise data model is complex and based on object-relational design principles. When creating reports, this consequently means that the developer is quickly faced with a great number of tables which are accessed by a report. Apart from the complexity, this may result in performance problems since a join operation between tables is an expensive database operation. Therefore, the complexity of a report should be as low as possible. Besides, it must be taken into account that the joins between the tables are formulated in such a way that the automatic join optimization can simplify the resulting SQL statement and the cache of the application server can be used.

Using special columns

If all required tables were explicitly consulted in order to formulate a query, this could quickly result in a complex model of 10-15 tables. For example, a sales order header (CISAG@ENTITY.app_sales_SalesOrder) maintains approximately 90 references to other tables. A large part of these references points to generally used business objects such as currencies, order types, units, etc. Therefore, it should always be checked whether columns added to the table by the ODBC driver can be used for descriptions (_txt and/or _BK suffixes) in the report instead of formulating a join to the corresponding table.

Denormalized business objects

Comarch ERP Enterprise denormalizes the data model in some places, i.e., it maintains copies. Reasons for this are to be found in the performance concerning the access to data and in the archivability (e.g. customer master data in order headers) of transaction data.

Error messages

Errors that occur during the report output will be logged by means of error messages. This is possible at diverse places and happens in the SOM operation as well as with the interactive use of ODBC.

Errors that have occurred in the ODBC server will be written in the system log of the application server executing the ODBC accesses and belong to the ODS message class. This log can be viewed by means of the Message logs application.

Furthermore, the ODBC server will send all error messages occurred to the ODBC driver. The driver will enter messages received by the ODBC server as well as messages about errors that happened in the driver itself into the Windows Event Log.

Errors occurring in the driver are usually messages that point to communication errors with the ODBC server. The JDBC driver generates errors in the form of SQL exceptions.

In the SOM operation, the error messages will be additionally entered in the status messages of the related output job so that information on the error cause can be found directly on the output job. This should be the first contact point to determine an error cause.

In the interactive operation, error messages will be provided to the ODBC application (e.g., Crystal Reports) via the ODBC API so that these messages will be displayed directly in the application by means of dialog windows.

Frequent error causes

Many errors occur due to a communication that was not achieved or discontinued between the servers involved. Reasons for this frequently are a wrong system configuration, that the ODBC server does not run and/or was restarted during the report’s output, or a certificate is not valid. In the interactive operation, outdated ODBC data sources can also be a possible cause. Communication error messages are not recorded in message logs of ODBC SAS since no connection exists at the time when the error occurs.

Other errors originate from missing ODBC authorizations of the user applied to the ODBC access, or if no ODBC access is allowed on the application server which is used for the ODBC access.

Many error messages that occur during the report development refer to the restrictions with respect to the use of special columns for business objects and Comarch ERP Enterprise data types as well as the virtual tables/functions described in this article.

JDBC driver

In addition to the ODBC driver, the JDBC driver is another interface to Comarch ERP Enterprise system databases. The JDBC driver offers the same data model as the ODBC driver, which is also intended to be read-only. All settings configured for ODBC access in the System cockpit application also apply to JDBC access.

Installing JDBC driver installation

The JDBC driver is supplied and updated as a com.cisag.sys.delivery.Install-JDBC-Driver zip file. The files/install/jdbc/jdbc-driver.zip file should be unzipped with a suitable archiving program from the Comarch ERP Enterprise system file server folder. All unzipped jar files must be in the class path. The implementation of the JDBC driver can be found in the semiramisjdbc.jar file.

Configuring JDBC connection

The JDBC driver communicates with the application server via HTTPS. In order for the JDBC driver to establish a connection, you must create a key store file containing the following information:

• Comarch ERP Enterprise public key (root certification authority)
• User certificate

The key store file can be created using the Java command line tool keytool or using a wizard application such as KeyStore Explorer, and must be of type JKS.

The Java interface for configuring a JDBC connection (ja-va.sql.Connection) expects two details: a connection URL and a property specification.

The connection URL must start with the protocol prefix jdbc:semiramis: followed by the URL to SAS. The URL is the same as the one used to configure the ODBC data source. It can therefore be copied from the ODBC data source creation dialog.

Properties are key-value pairs for the required configuration settings. The JDBC driver recognizes the following properties: