# Administrator Guide

# Managing Studio Database Tables

Studio centralizes database management in the Studio Database Manager portlet. It displays only database tables to which users have access privileges and lets them import database tables, as well as modify, update, and delete them. Administrators can view and mange all Studio database tables. Users can restrict access to the database tables that they created. For information on controlling access to database tables, see Securing the Database Tables.

Figure 6-1 Studio Database Manager Portlet

This chapter includes the following sections:

- Importing a New Database Table
- Adding Entries to An Existing Database Table
- Fixing Import Errors
- Editing Database Tables
- Deleting a Database Table

## Importing a New Database Table

If you have a data file that you created outside of Studio or one that you exported from Studio then edited, you can use the Studio Database Manager to convert it into a Studio database table. Studio Database Manager includes the Import Wizard, which will guide you through the entire process of importing an existing file and converting it into a format that Studio portlets can use.

To create a new database table by importing a file:

1. On the toolbar of the Studio Database Manager, click New Database From Import.

This launches the Import Wizard.

Note: | If the New Database From Import button is grayed out, clear the check boxes to the left of the Database Name column.

1. Upload the file.

1. Click Browse.

The Choose file dialog box displays.

2. Select the file you want to upload, then click Open.
3. Select the import file type.
4. Type a unique database name.
5. If you are not importing a text file, skip to the next step; otherwise, from the File Character Encoding drop-down list, select the character encoding of the file.

Character encoding is a method of displaying characters. For example, the ISO-8859-1 encoding is best for documents in English and most other Western European languages.

Caution: | Selecting the wrong encoding might corrupt the data.

6. When you are finished, click Next.

2. If you are uploading a Microsoft Excel file, specify the range of data to import.

1. From the Import Worksheet drop-down list, select the worksheet that you want to import.
2. If you want to import only a portion of the Excel work sheet, click , select a range of cells, then click when you are done.

Note: | If you want to import all the cells, do not enter or select a cell range.

3. When you are finished, click Next.

3. If you are uploading an XML document, specify the elements to import:

1. In the Specify XML Tags page, enter the XML object tag.

The XML object tag is the wrapper tag that surrounds a unit of data or a set of property tags. Property tags are tags for elements and identify the nature of the data.

2. In the XML Object Tag text box, type the object tag.
3. In the Object Property Tags text box, type each property tag used in your XML file.

The expected XML tag structure appears on the preview pane.

Note: | The wizard flattens the XML structure.

4. When you are finished, click Next.

4. Associate the data with Studio data columns.

1. If you do not want to import some data columns, clear the Import This Column check boxes directly under the columns that you do not want to import.
2. To preview the contents of a row in the database, select the row number in the Show Imported Data In drop-down list.
3. In the Column Name text box for the database table column that you want to import, either type the column names or set the first row of the imported database table as the column name by selecting the Use first row check box.
4. In the Data Type drop-down list, select the appropriate data types for the data column.

5. When you are finished, click Import.

If you have a lot of data to import, you can stop the import process while it is in progress. Once Studio imports all data, the Import Data page of the wizard is no longer available.

- To return the database to the last committed record, click Rollback. This does not undo the entire import process. It only reverses the uploading of records that have not been committed to the database.
- To stop but not cancel the import process, click Stop Import. You do not lose the records that have already been imported. If you want to import the database table again, delete the partially imported database table and start a new import process. This ensures that you do not have double entries.

6. Click Finish.

## Adding Entries to An Existing Database Table

You can use the Import Wizard to add entries to an existing database. For example, rather than manually entering contact information in a Phone Directory portlet each time your company hires a new employee, you can upload an Excel spreadsheet to the Phone Directory database table periodically. Studio adds the data in the spreadsheet as new rows in the existing database table.

To add data to an existing Studio database:

1. On the right side of the Studio Database Manager, click .

This launches the Import Wizard. For detailed instructions on the Import Wizard, see Importing a New Database Table.

2. Upload the file.
3. If you are uploading a Microsoft Excel file or an XML file, specify the range of data to import or define the elements; if not, skip to the next step.
4. Associate the data with Studio data columns.
5. When you are finished, click Import.
6. Click Finish.

## Fixing Import Errors

If the database table you are importing has problems, the Fix Import Error page in the Import Wizard displays. This page flags data errors such as data type mismatch, column discrepancies, misplaced delineators, and invalid data values. It also lets you fix data errors.

Data type mismatch errors occur when you try to add data of a particular data type to a database column with a different data type (such as inserting text into a number column). Column discrepancy errors occur when the number of columns in an imported file does not match the number of columns in the existing database table. Misplaced delineator errors occur when data separators (such as tab spaces and commas) are not at their expected places. An example of this error is a missing comma in between two data fields. Invalid data value errors occur when the data value exceeds the maximum length, which is 1,000 characters, or the required fields have missing data.

You cannot import or update the database table if you exceed the maximum number of bad records allowed in the configuration file in Studio. Either fix the bad records or change PTStudioConfig.xml. The configuration file is installed in C:\bea\alui\ptstudio\2.2\settings\config\ by default.

You can do the following in the Fix Import Errors page:

- To fix errors, enter the correct data in the highlighted text box, then click Save.
- If you do not want to correct a particular data error, you can skip the row by clicking Skip. If you want to skip the error correction altogether, click Skip All.

## Editing Database Tables

To make changes to database tables, either click the database name or at the end of the portlet row in Studio Database Manager. You can change the database description, database columns and their default values and number formats, as well as database access privileges. For more information on database columns, see Defining the Columns in the Database Table; for more information on database access privileges, see Securing the Database Tables.

## Deleting a Database Table

To delete a database table, select the check box next to the database table that you want to delete, then click Delete on the action bar. You cannot delete a database table that is being used by a portlet. You must delete the portlet from portal and Studio Administrator first. For more information on deleting portlets, see Deleting Portlets.

If you want to remove just a data column and not an entire database table, see Removing Database Columns.

## Changing Studio Database Settings

You can change the database or database driver that you are using through the Studio Administrator portlet. When you click Edit Studio Config, the Studio Administration Editor displays with two pages: one page sets the portal database settings, and the other sets the Studio database settings. The portal database stores user data such as portal user names, passwords, and locale settings of each user; while the Studio database stores data collected from Studio portlets or data uploaded to the Studio Import wizard.

Figure 6-2 Studio Administration: Portal Database Settings Page

To change the settings of your database:

1. On the action bar of the Studio Administrator portlet, click Edit Studio Config.

The Studio Administration Editor displays.

2. Click Portal Database Settings or Studio Database Settings, depending on which database setting you want to configure.

Note: | Changing the portal database settings only affects Studio, not the portal.

1. If you want to change the driver for your database, type the new driver name in the Driver Name text box.
2. If you want to change your database, type the URL of the new database in the Database URL text box.
3. In the User Name text box, type the user name for the database.
4. In the Password text box, type the password for the database.
5. Click Save Settings.