Connecting to databases with ConceptDraw PRO

ConceptDraw PRO Pro combines a powerful graphic engine and the possibility to work with ODBC- compatible databases (henceforth DB). If you are a developer, you should consider ConceptDraw as a powerful and flexible tool for finding solutions connected with visualization of information, stored in DB. However, even if you do not do any programming, ConceptDraw gives you an advantage to work with DB with the help of two wizards: Organization Chart Wizard and Database Model Diagram. The first one builds up Organization Chart on information from an arbitrary DB, and the other creates a scheme which models the structure of the given DB. The only thing that you should know when using the wizards is the principles of connection to DB. And it is those principles the article is dedicated to.

Thus, no matter whether your interests in work with DB are restricted to using the Wizards, or you intend to apply ConceptDraw as a developer's platform, but this is the first time you meet ODBC, this article is for you.

What is ODBC?

ODBC technology (Open Data Base Connectivity) is intended for uniform access to any type database. A standard set of functions is defined to be used. These functions are normally addressed to a common center (the so-called driver manager). The center in its turn reallocates the addresses by calling a specific corresponding function, understood by the Database Management System (henceforth DBMS). Where does the driver manager take these specific functions from? They are provided by a driver of corresponding DBMS. I.e. any developer wanting a DBMS to support ODBC standard, can create a driver able to "interpret" standard commands into commands understood by a given DBMS. If such a driver for the DBMS you are interested in has been already written, the DBMS is called ODBC-compatible. There is another way of interaction, the case when a client-program (for instance ConceptDraw) has direct access to a specific ODBC-driver, passing the driver manager.

ConceptDraw uses ODBC for work with DB. Which is why before using ConceptDraw for work with DB, you need to be sure in two things:

1. the DBMS, your database was created in, is ODBC-compatible (i.e. there exists an ODBC-driver for it);
2. the necessary ODBC- driver has been installed in your computer.

Let's say, you are not sure you have the necessary ODBC-driver. It can be checked out with the help of a corresponding ODBC administrator program. In Windows 9x it's normally called from the Control panel ("ODBC Data Sources (32-bytes)" icon), in Windows 2000 and XP, open Control Panel, then Administration and ODBC Data Sources (what are ODBC sources further in this article). In MacOS X try to open an administrator by the path /Applications/Utilities/ODBC Administrator (it is installed together with the operation system, starting from version 10.2). Open the tab Drivers. There you can find the list of all preinstalled ODBC-drivers. Usually, by the driver's name it can be defined which DBMS it is intended for.

Note. If you are running an earlier version of MacOS X and you do not have ODBC Administrator, or on the tab Drivers no appropriate line to be seen, it does not necessarily mean there is no such a driver. It might have been installed, but it can be operated only by the "native" administrator, which as well as the driver manager is installed together with the ODBC driver. Try looking for a file with a similar name in /Library/CFM Support. ODBC-drivers are usually installed there.

So, you have detected or installed the needed ODBC-driver. Now we know that we can access DB created by a specific DBMS. How do we connect to this specific DB? For the driver manager or for a client program functioning instead DB looks like a data source (DSN). DSN is a set of data which defines DB format and its physical location as well as work parameters of the ODBC-driver with the DB. It is DSN for creation and management of which ODBC Administration is intended. On its various tabs you can create, delete, and reconfigure DSN of three types: user's type, system type or file type. User DSN is in the scope of just one user in whose session this DSN has been created, the system type is the one in all the computer users' scope, and the file type is saved in a file with dsn extension and can be transferred to another computer. For the moment ConceptDraw supports work with user and system DSN. To create such a DSN, click Add on the corresponding tab. A Wizard will appear to ask you to choose the ODBC-driver and enter the DSN name, for further settings it will call a dialog, the kind of which depends on a specific ODBC-driver.

Note. To learn more about ODBC go to: http://www.iodbc.org and http://www.odbc.org.

So, DSN has been created. Now for ConceptDraw to get connected to your DB, you should specify the DSN and ODBC-driver being used (the latter - in MacOS X only). Let's see how it happens in ConceptDraw Wizards.

Connecting to databases in ConceptDraw wizards

Open ConceptDraw, and choose Organization Chart Wizard in Template Gallery. In the dialog that appears two ways are offered: to build an Organization Chart on the data stored in DB or in the text file. Right now, we are more interested in the first way. It has been chosen by default. To call the dialog of DSN choices click Select. If DSN has not been created yet, you can create a new one right here by pressing Add. The Wizard will read the data and enter the connection line corresponding to the DSN in the text field.

Note. In MacOS X before pressing Select you should choose an ODBC-driver. Use Change button for it. The given driver may happen to be unable to call the dialog of DSN choices. Then you'll have to write down the connection string manually in the text field. It at least should say the DSN name.

DSN=my_dsn

Via semi column other parameters (for instance, login and password) can be enumerated.

DSN=my_dsn;UID=my_login;PWD=my_password

This is it. That's enough to get connected to DB. In further steps the Wizard will be collecting all necessary information specific for Organization Charts. It will offer the list of tables, found in this DB, where you can choose the one containing the data; it'll require comparing names of fields (the table columns) with the concepts used in building the diagram, and the names of fields values of which will be represented in the object user's properties. And at the end it'll find out whether the diagram should be built on one or on several pages.

Note that for the Wizard's work to be successful the table should meet some requirements. The name of the employee and his boss's must be presented in the columns. The rest data is voluntary. The approximate choice of the table columns can be seen in the file <path to ConceptDraw>/Libraries/Organization Charts/OrgChartExample.txt.

Connection to the database in Database Model Diagram Wizard looks similar. Opening the document if it's empty and also on choosing the menu item Tools->Database Chart->Build Database Structure in Windows the dialog of DSN choice will appear at once, and in MacOS X ODBC-driver will be requested first. That's enough for the Wizard to get on touch with the database and make up its Chart.

Connecting to the databases from CDBasic

ConceptDraw Wizards are just ConceptDraw documents, with built-in script in ConceptDraw Basic. Sometimes they additionally use external libraries created with the help of other programming languages (for instance, Organization Chart Wizard). But the very process of interaction with DB is carried out by means of ConceptDraw Basic.

For work with DB ConceptDraw Basic provides Database access objects model. All addresses to the database are made by calling of certain object methods of the given model.

To start work with DB, you need to create an instance of object DBEngine. This is the object of the upper level in CDBasic-model of access to DB. It operates the Drivers Manager and allows to reach other objects necessary in work with DB.

Work with DB runs within workspaces, which operate connections with databases and with transactions. A new workspace is created by choosing CreateWorkspace() method of DBEngine object. It allows to specify the workspace name as well as login and password for access to the database.

For connection with DB either of the two objects can be used: Database or Connection. (Discussing differences between them is beyond this article).

On opening the connection DSN is supposed to have been created for DB. It's specified in the connection string in one of the parameters of OpenDatabase() and OpenConnection() methods.

At the end of the work the connection and the workspace should be closed by Close() method.

Dim eng as DBEngine, ws as Workspace, db as Database

set eng=new DBEngine
set ws=eng.CreateWorkspace("University","admin","")
set db=ws.OpenDatabase("University",TRUE,FALSE,"DSN=University_DSN")

' addresses to the database

db.Close()
ws.Close()
or
Dim eng as DBEngine, ws as Workspace, cn as Connection

set eng=new DBEngine
set ws=eng.CreateWorkspace("University","admin","")
set cn=ws.OpenConnection("University",TRUE,FALSE,"DSN=University_DSN")

' addresses to the database

cn.Close()

Of course such a short introduction is not enough to work with DB from ConceptDraw Basic. More detailed information is available from the article "The Review of Database Access Objects Model". If you work with FileMaker, it's worth referring to the article "Visualization of FileMaker database in ConceptDraw". As examples, the same ConceptDraw Wizards can be considered. To open the editor window with the script source code, launch the corresponding Wizard and choose the menu item Tools -> ConceptDraw Basic -> Document Script -> Edit.

Download ConceptDraw PRO Free 30-day trial, , English

Price:

ConceptDraw PRO Professional

$249