Capítulo 4. Conectar con una Base de Datos

There are many features within Power*Architect that involve connecting to a database, such as reverse and forward engineering. Power*Architect allows you to use any JDBC- or ODBC-accessible source database.

For more information on supported databases, see the section called “Supported Database” .

Connecting to a database with Power*Architect involves the following steps:

1. Define general settings and drivers for the database platform you plan to connect to (such as SQL Server or Oracle). For more information, see the section called “Setting up Database Types” .

2. Create a connection to a specific database server. This connection uses the general settings and drivers you have configured for the database platform. For more information, see the section called “Setting up Database Connections” .

Supported Database

Power*Architect provides full or partial support for the following database platforms.

Database Support Notes

Oracle Fully supported.

SQL Server Fully supported.

PostgreSQL Fully supported.

MySQL Fully supported.

IBM DB2 Partial support; needs more testing.

HSQLDB Works; used in samples.

Derby Preliminary support exists. Reverse engineering

databases in Derby 10.3.2 or later is possible. Derbyspecific

forward engineering is not yet available;

however, you can try using the forward engineering

support for another platform such as MySQL or

HSQLDB. Please post to our web support forum if

you are interested in forward engineering your data

models to Derby.

Setting up Database Types

You must define general settings for the database platforms you plan to work with (such as SQL Server, MySQL, Oracle, DB2, etc.). These settings will be used by Power*Architect when you set up a connection to a specific database server.

Note: Remember, at this point you are configuring general settings only and are not connecting to a specific database. For more information on connecting to a database, see the section called “Setting up Database Connections” .

• General settings for several database platforms are already pre-configured in Power*Architect. If you plan to work with one of these database platforms, all you need to do is define the location of the JDBC driver. For more information, see the section called “Defining the JDBC Driver” .

• You can also define additional database platforms in Power*Architect. For more information, see the section called “Adding a New Database Type” .

Adding a New Database Type

To add a new database type:

1. Select File » User Preferences.

Alternate method:

• Select Connections » Database Connection Manager or Window » Database Connection Manager.

On the Database Connection Manager dialog box, click JDBC Drivers.

The User Preferences dialog box appears, with the JDBC Drivers tab open. Existing database types, including the pre-defined database types included with Power*Architect, are listed on the left.

Note: You can modify an existing database type by clicking on it in the list.

2. Click + below the list of database types.

3. Enter the following information on the General Tab:

Name Name for the database type (for example,

PostgreSQL or SQL Server).

Driver Class Java class name of the driver. This is the driver

class within the JDBC driver JAR file that will be

used for database connections.

Connection String Template General format of the JDBC URL for the database platform.

In this field ... Enter the following information ...

Important: You are not creating a connection for

a specific database - you are entering a generic

connection string that applies to the database

platform. Later on, when you set up a connection

to a specific database, Power*Architect will use

this template to create the URL to connect to the

database.

The connection string template must conform to a

specific pattern that includes literals and variables.

• Literals are entered like normal text but may

not contain angle brackets (), which are

reserved for defining variables. As the name

implies, literals appear in the URL in the same

position and way they appear in the template.

• Variables are used to for values that change

often, such as the schema or database

name you wish to connect to. To define

a variable in the template, use the format

(to include a

default value) or (if you don't

want to include a default value). If you use

a default value. it is entered automatically

when you create a database connection. You

can modify the value if the database you are

connecting to is configured to use a different

value.

Each variable you define is shown below the

Connection String Template field. This provides

you with a preview of the values you will be able

to modify when creating a database connection.

For example, the connection string template to

connect to a Microsoft SQL Server database might

look like this:

jdbc:sqlserver://: 

When you create a connection to a specific

SQL Server database, Power*Architect will use

this template to create the connection URL. In

this example, the template will create the URL

"jdbc:sqlserver://:1433", where 1433 is the default

port value. Since SQL Server databases listen to

port 1433 by default, it makes sense to include

this value in the template. When you're creating

the actual database connection, you can change the

port value if the database you're connecting to is

configured differently.

4. The settings on the Kettle tab are only used when you create a Kettle job. For more information on

these settings, see the section called “Using Kettle Jobs” .

5. Click OK.

Next, you must define the location of the JDBC driver for the database type. For more information, see

the section called “Defining the JDBC Driver” .

Defining the JDBC Driver

Whether you are adding a new database platform to Power*Architect or want to use one of the preconfigured

platforms, the last step in setting up a database type is to locate the JAR file (or files) that

contain the JDBC drivers for the database platform.

Note: Remember, at this point you are just telling Power*Architect where the drivers are. You must set

up a database connection in order to connect to a specific database server (for more information, see the

section called “Setting up Database Connections” ).

Unlike most applications, which need a distinct driver program to communicate with each type of database,

Power*Architect uses Java-based drivers. These drivers normally come from the database vendor in the

form of JAR (Java Archive) files. JAR files are an extension to the file format used by PKZip/WinZip

archives.

Most database platforms provide drivers that are fully backward compatible. This means that it is best

to use the newest driver available, regardless of the software version on the specific database server you

intend to connect to. One exception to this is the Oracle database. It is important to match the major version

number of your JDBC driver with the major version number of the Oracle database server you connect to.

For example, if you are connecting to an Oracle 10g database, use the latest Oracle 10g driver. If you are

connecting to an Oracle 9i database, use the Oracle 9i driver.

To define the JDBC driver for a database type:

1. If you do not have the JDBC driver for a specific database platform, you can usually obtain one

from the database vendor. If that fails, you can find a directory of databases drivers on Sun's

web site [http://developers.sun.com/product/jdbc/drivers] . There is also a permanent thread in the

Power*Architect user support forum [http://www.sqlpower.ca/forum/posts/list/401.page] , where you

can share information with other Power*Architect users about finding and configuring drivers for a

particular database platform.

2. Decide on a permanent location to store your JDBC drivers. A good strategy is to create a JDBC folder

under your Documents folder and collect all of you JDBC driver files there.

3. Save the JDBC driver (it will usually be one or more JAR files) in the location you've chosen.

4. If the User Preferences dialog box is not already open, select File » User Preferences.

5. On the JDBC Drivers tab, select a database type.

6. Click Add JAR.

7. Locate the JAR file and click Open. If there is a valid driver class in the JAR file, a file tree will appear

showing the JDBC driver classes within the JAR file.

8. Select the driver you want to use.

9. Click OK.

Setting up Database Connections

You must set up a connection to allow Power*Architect to connect to a specific database. When you

create a connection, it is automatically added to the current Power*Architect project. You can also use

the connection in all your projects.

Before creating a connection, you must define the general settings for the database platform. For more

information, see the section called “Setting up Database Types” .

Creating a New Database Connection

To create a new database connection:

1. Select Connections » Add Source Connection » New Connection.

Alternate methods:

• Select Connections » Database Connection Manager (or Window » Database Connection Manager),

then click New.

• Right-click a blank space in the database tree, then click Add Source Connection » New Connection.

The Database Connection dialog box appears.

2. On the General tab, enter the following information:

In this field ... Do this ...

Connection Name Enter a name for the database connection.

Database Type Select the database platform you want to connect

to. Note: This list contains the database types

you defined in your user preferences. For more

information, see the section called “Setting up

Database Types” .

Connect Options and JDBC URL Enter the connection options for the database

driver. (Theses options are based on the database

type you select.)

If you are using one of the fully-supported drivers,

the connection option parameters are added into

the JDBC URL field in the order that the Java

driver expects to see them (this string is sometimes

called a "db URL" in Java terminology). In the

following example:

• The default port number from the database type

has been entered automatically in the Connect

Options.

Note: You would not usually change a

default value unless the database server you're

connecting to has been configured to use a

different value.

• The hostname and database name have been

entered manually in the Connect Options.

• The PostgreSQL driver is being used.

Username and Password If necessary, enter the username and password to

connect to the database.

3. The settings on the Kettle tab are only used when you create a Kettle job. For more information on

these settings, see the section called “Using Kettle Jobs” .

4. Click OK. The new connection is added to the current project (you can view the connection in the

database tree) and is also added to the Database Connection Manager.

Adding or Removing Database Connections for a Project

You can add a previously created database connection to a project. (When you create a new connection,

it is automatically added to the current project. For more information, see the section called “Creating a

New Database Connection” .) You can also remove a connection from a project. You cannot remove a

connection if it is being used as a source connection in the playpen.

Note: You can permanently delete connections. For more information, see the section called “ Modifying

or Deleting Database Connections ” .

To add a database connection to a project, do one of the following:

• Select Connections » Add Source Connection, then select a database connection.

• Right-click a blank space in the database tree, click Add Source Connection, then select a database

connection.

The database connection is added to the database tree.

To remove a database connection from a project, do one of the following:

• Right-click a database connection in the database tree, then click Remove Connection.

• Click a database connection in the database tree, then select Connections » Remove Connection.

Modifying or Deleting Database Connections

You can modify a database connection's properties or permanently delete it. You cannot delete a connection

if it is being used as a source connection in the playpen.

Note: You can also remove a connection from a project without permanently deleting the connection. For

more information, see the section called “ Adding or Removing Database Connections for a Project ” .

To modify a database connection:

1. Select Connections » Database Connection Manager (or Window » Database Connection Manager).

2. Select a database connection, then click Edit.

Alternate methods:

• Right-click a database connection in the database tree, then click Connection Properties.

• Select a database connection in the database tree, then select Connections » Connection Properties.

The Database Connection dialog box appears.

3. Modify the connection settings. For information on the settings, see the section called “Creating a New

Database Connection” .

4. Click OK.

To permanently delete a database connection:

1. Select Connections » Database Connection Manager (or Window » Database Connection Manager).

2. Select a database connection, then click Remove.