MiraMon databases

Access to databases with MiraMon

MiraMon allows the access to any database in DBF III, III+ and IV format, as well as tabular data (physical or the result of query or calculation expressions) contained in other file formats (such as .xls, .mdb, etc) and in databases of data (such as MS-Access, Oracle, SQL Server, DB2, Excel, etc); in these last cases it will be necessary that the corresponding ODBC drivers exist and are installed, with the exception of the MDB (which allows direct access from MiraMon), to generate a DSN file to access the data. In versions prior to version 4 of MiraMon, the DBF format was the only format used for databases.

Unlike other programs, MiraMon layers can be built with simultaneous queries in different databases in different formats, locally (on the computer) or remotely (databases on corporate servers, on the local network, the intranet or the Internet).

In order to read from different databases, MiraMon uses the Microsoft ODBC (Open Database Connectivity) technology, which allows the access to different relational and non-relational databases, as much for reading as for modifying information. ODBC offers a unique and integrated environment so that any application, like MiraMon, can have access to a large number of different database formats.

The ODBC technology configuration tool is located in the configuration options of the computer, under the name of ODBC Data Sources, in the Control Panel (Start menu | Configuration).

ODBC components are automatically installed on the computer when most Microsoft products are installed, for example: Office, SQL Server, etc. Since the first version of Win2000, this technology is part of the operating system and, therefore, its installation is not necessary.

Verify on the control panel (or in the administrative tools on Win2000), that the ODBC Data Sources entry appears. If it does not appear, the required components must be installed. If the computer already has ODBC, it is strongly recommended updating with the latest available versions since Microsoft constantly updates this technology with new features and connections to new databases.

How to install and update ODBC

The latest versions of the ODBC components can be downloaded from https://docs.microsoft.com/en-us/sql/odbc/microsoft/microsoft-supplied-odbc-drivers?view=sql-server-2017 for Access, Excel, Dbase, text, Oracle, etc, or from https://docs.microsoft.com/ca-es/sql/connect/odbc/microsoft-odbc-driver-for-sql-server?view=sql-server-2017 for SQL Server. In general, these components can also be found under the name of MDAC (Microsoft Data Access Components).

Notice that on this page there are several tools available to download. Only the MDAC (NOT the MDAC SDK) should be downloaded.

Which parts integrate ODBC

ODBC technology is integrated by several pieces:

• Driver Manager: application founded between the own application and the databases. It is responsible for the interactions and transmits the data between the application and the database.
• Drivers: specific connections for each one of the different databases. The Driver Manager is responsible for controlling all the drivers.
  To access data in MS-Access format, a driver for MS-Access is needed; likewise, to access data from Oracle databases, a specific driver for Oracle databases is needed.

Through the installation of ODBC technology, Microsoft provides some drivers to access the most common databases in the market (for example, MS-Access, SQL-Server, Oracle,...) and also some drivers to access data in not strictly formats of data bases (for example Excel, Text,...).

Other ODBC drivers can also be obtained from other commercial brands to access their own products, for example the Oracle brand ODBC driver to access the databases in Oracle (for example https://www.oracle.com/technetwork/database/windows/downloads/utilsoft-098155.html).

How to set up a connection between MiraMon and a database

MiraMon has been prepared to use the power of ODBC technology. With this fact, many future doors are opened to be able to perform very complex spatial queries, using data from large databases.

• Databases in DBF format: they can be accessed as usual.
• Databases in MS-Access format (MDB): go directly to the MDB file, MiraMon is already responsible for establishing the protocols to access this type of file via ODBC (obviously, it is necessary to have the ODBC drivers installed).
• Any other database format, relational or not: it is necessary to follow the following steps.

1.- Open the ODBC manager from the configuration options of the computer. Where is ODBC located? WIN NT/2000, WIN95/98). NOTE: The appearance of this window may vary depending on the version and the installed language. This image corresponds to the version 3.520.7326.0 in English. At this point it is necessary to define what a DSN is: Data Source Name, it allows us to define the access to the database, which driver should be used and how this connection should be. Types of DSN: USER DSN (user data source): The own DSN of each user. In Windows NT, Windows 2000 and ulterior environments, with the safety options enabled, this DSN can only be used by the user who created it or by the system administrator. It is useful to increase security (only the user in question will have access, without passwords). SYSTEM DSN (system data source): DSN that can be used by anyone with access to the computer. FILE DSN (data source in a file): DSN saved in a text file with a DSN extension. This text file can be opened with any text editor and its content can be manipulated. As a text file, the security parameters of any other file are applied. Most of the users, at least at the beginning, will find it easier to access the databases via DSN of file (File DSN), which is the reason why we will continue under this case.

2.- Add a file DSN (File DSN). Click on the Add button.

3.- Choose the appropriate driver to access to the wished database. In this example, to access a database in Oracle format. NOTE: Make sure that the chosen driver is the latest available version. It is possible to find more than one version of different drivers in the list.

4.- Indicate the name and directory where the DSN file should be saved. It is important to remember the place where this file is kept and that it has an identifying name with the database to which it accesses.

5.- Indicate which database should be accessed and the properties of the connection. The connection configuration window is specific to each driver, in this case only the driver window for databases in Oracle format is displayed. NOTE: This dialog box can be skipped by typing the user name in the USER= key and the password in the PWD= key in the corresponding DSN file. The privacy level of this file must be kept in mind if this decision is taken.

6.- Once the DSN file has been created, MiraMon (and all its support applications) will have access to the database through this file.

The DBF format is, perhaps, the most popular of the alphanumeric data table formats (except for the tabular forms in plain text), both for its simplicity and its speed, as well as for not depending on third parties, or simply because the certainty that the information can be opened on a computer with unknown installed drivers is wanted (so it is the default option for the creation of MMZX or MMZ files allocated for wide dissemination via Internet), despite the great potential presented by access to the different tabular sources explained in detail in the previous sections.

However, the DBF format, due to its age, has a series of limitations, extensively explained in section 1. Background and motivation of the technical document "Extended DBF" format specification (length of the file name or with strange characters -accented, diaeresis, spaces, etc-, length of the name of each field of maximum 10 characters or with strange characters, among a long list).

MiraMon solves the most important of these limitations by establishing a variation of the DBF format called "Extended DBF", which is detailed in sections 2. Characteristics and use of the "Extended DBF" format and 3. Specification of the "Extended DBF" format from the same technical document.

However, it should be considered that if a table does not need to overcome the limitations of the classic DBF, it is preferable to write it in this format to make it readable by other software that do not support the "extended DBF" since the format does not keep downward compatibility (an "extended DBF" cannot be read, not even partially, by a software that reads classic DBF). This causes that if an "extended DBF" table must be taken to a software that can only read classic DBF, it must be necessary to apply some operations on the table to transform it into "classic DBF" again (reduction of the number of fields, etc), as will be explained later in Transformation from an "extended DBF" to a classic DBF.

MiraDades currently informs, in the option "Information | Table information", if the table is extended or not, and it is possible to see how many fields it has (and therefore, if one of the reasons for not being a "classic DBF" is having exceeded the 254 fields) as well as the characteristics of the fields in the corresponding list, so it can also be noticed which are those that do not comply with the "classic DBF" specification (field names longer than 10 characters or with strange -accented characters, spaces, text fields longer than 254 characters). However, this analysis is too arduous if it has to be done with a large volume of tables or with some frequency, so it is advisable to have a mechanism that makes the transformation easier from an "extended DBF" table to a "classic DBF" table.

IMPORTANT: The transformation from an "extended DBF" to a "classic DBF" almost always entails loss of information, so it is very convenient to have a backup of the table that is going to be "simplified".

The characteristics of the "extended DBF" format are explained in section 3. Specification of the "Extended DBF" format of the technical document "Extended DBF" format specification.

The main requirement to transform an "extended DBF" to a "classic DBF" is to streamline the way to convert extended fields into classic fields, as well as to control the number of total fields.

What has to be done to move from "extended DBF" to the classic one (user level)?:

• Convert the names of the fields in capital letters, without extended characters, limiting the maximum length to 10 characters and making sure that there are no name repetitions with the other existing fields.
• Monitor that the size of a text field (type 'C', of characters) does not exceed the 254 bytes.
• Reduce the total number of fields, without exceeding the 255 allowed in the classic DBF.

Through the GestBD tool it is possible to automatically convert an "extended DBF" into classic, although if it is prefered to do it manually for having more control, it can be done through MiraDades, in the "Modify structure of table" window.

MiraDades assigns the following features to facilitate the conversion of an "extended DBF" into a classic:

1. In the field list, appears next to each extended field name:
   • The proposed new name with less than 10 characters and without extended characters.
   • The necessary size that the field must have to avoid the cut of information.

   This causes the resource to have a horizontal scroll bar, if it is necessary. If the field is extended but the reason is not the name, it will also be written to provide the user with a greater visual ease.

   

2. A buttonnext to the "Change" button with the name "To classical": when the button is pressed, the field becomes classic adopting the proposed name and size. If the size in the "classic DBF" exceeds 255 characters, it is notified that if it is cut, information will be lost and the opportunity to continue or cancel is given.

3. Below the list of field names appears the reason why the DBF is extended, if it is. Examples that may appear:
   • There are extended field names, there are text field sizes of more than 254 characters and there are more than 255 fields.
   • There are text field sizes of more than 254 characters and there are more than 255 fields.
   • There are extended field names and there are text field sizes of more than 254 characters.

4. If the extended information next to the fields is a nuisance, a check mark that hides the extended information of the fields can be selected.

The GestBD (database manager) incorporates the following features to facilitate the conversion of an "extended DBF" into "classic DBF":

1. A button next to the "ODBC-> DBF" button with the name "Extended DBF": pressing the button offers the possibility of naming the "classic DBF" file resulting from the transformation.


If the size in the "classic DBF" exceeds 255 characters, it is notified that cutting it will cause loss of information and the opportunity to continue or cancel is given. In case of continuing and cutting information, a backup copy of the original "extended DBF" table is automatically generated.