How to Use the Database Explorer in the Oracle Solaris Studio IDE

by Nik Krasilnikov

This article describes how to use the Database Explorer in the Oracle Solaris Studio integrated development environment (IDE).

Published September 2012

Oracle Solaris Studio includes the Database Explorer, which enables you to observe and manage databases in IDE. This article demonstrates the main features of the Database Explorer.

Setting Up a Database Connection

To work with a database, you must first set up a connection to the database.

1. In the IDE, open the Services window by choosing Window -> Services.
   
   
   

   Figure 1. Menu Options for Opening the Services Window

2. In the Services window, open the Databases node and then open the Drivers node.
   
   
   

   Figure 2. Database Drivers in Services Window

   The Oracle Solaris Studio IDE includes drivers for MySQL, PostgreSQL, and Oracle Thin, which you use to connect to the associated databases, as shown in Figure 2.

   Tip: If you want to connect to another type of database that is not in the Drivers list, you could register a driver by right-clicking the Drivers node and selecting New Driver. You would need to specify the path to the driver and its name and class. See the IDE's online help for more information about adding a driver.

3. Right-click the driver for the type of database you want to connect to, and select Connect Using.
   
   

   In Figure 3, the Oracle Thin driver is used to create a connection.

   
   

   Figure 3. Option on Context Menu for Oracle Thin Driver

   A wizard opens to help you configure the connection.

4. In the Customize Connection dialog box in the wizard, enter the host name or IP address of the database server and the username and password to be used to access the database.
   
   
   

   Figure 4. Default Values for Connecting to Oracle Database Express Edition

   The Customize Connection dialog box of the New Connection wizard contains default values for Oracle Database Express Edition, which is shown as "XE."

5. Change the settings to make them appropriate for your database and click Next.
6. In the Choose Database Schema dialog box, the default schema for the specified username is displayed. Select a different schema, if appropriate, and click Finish.
   
   
   

   Figure 5. Choose Database Schema Dialog Box

7. Look in the Services window to see the new connection in the list.
   
   
   

   Figure 6. Services Window with a Connection to the XE Database

Exploring and Modifying Databases

After you are connected to the database, you can use the Database Explorer of the IDE to work with the database.

1. Open the new jdbc:oracle:thin connection in the Services window.
2. Use the Database Explorer to view your database tables and columns.
   
   

   For each action you perform, the IDE runs an SQL command on the database.

   In Figure 7, a database table FRIENDS in the TUXDEMO schema was expanded. The SURNAME column was selected, and the IDE ran the "select SURNAME from TUXEDO.FRIENDS" command to show items.

   
   

   Figure 7. Viewing Database Tables and Columns

3. Right-click items in the Database Explorer to create, modify, and delete tables, columns, indices, and so on.
   
   

   In Figure 8, the user right-clicked the Table node and selected Create Table. This opened a Create Table dialog box to help create a new table.

   
   

   Figure 8. Creating a Table

Editing and Running SQL Scripts in the SQL Editor

You can open, edit, and run SQL scripts in the SQL Editor. The editor provides syntax highlighting to make writing scripts easier. When you run a script that returns data, the data is also displayed in the SQL Editor.

Figure 9 shows the SQL Editor with a script for creating a table.

Figure 9. SQL Editor

To open the SQL Editor, right-click a node in the Database Explorer in the Services window and choose Execute Command, Create Table, or some other command.

The SQL statement or script is executed on the database that is selected in the Connection drop-down list in the toolbar in the SQL Command window. If the database connection is closed, the IDE opens the connection to the database. In the Connection drop-down list, you can change the database on which to run the SQL statement.

Code Completion in SQL Editor

The SQL Editor's code completion lets you insert schema, table, and column names more quickly. It also lets you browse the history of all SQL statements, filter by search terms or by connection URLs, and then insert the selected statement. You can sort query results in the SQL Editor and load large returned data sets page by page. You can directly change values of returned results and insert or delete rows.

In the Figure 10, the user pressed Ctrl-Space to see code completion suggestions.

Figure 10. Code Completion Example

See Also

See the following resources for more information:

• "How to Create an Oracle Database Project in the IDE" and other articles about the IDE are available from the Topics page of the Oracle Solaris Studio portal.
• The NetBeans article "Connecting to Oracle Database from NetBeans IDE" applies also to the Oracle Solaris Studio IDE. However, you do not need to download the ojdbc6.jar file as mentioned in the article because it is already included in the Oracle Solaris Studio IDE.
• The Help menu in the IDE provides access to extensive information about using the IDE, including how to work with databases.
• For basic information about using the Oracle Solaris Studio IDE, see the IDE Quick Start Tutorial.

About the Author

Nikolay Krasilnikov joined Sun Microsystems in 2008. Now he is a senior software engineer for Oracle in St. Petersburg, Russia developing the Oracle Solaris Studio IDE. His responsibilities include developing the C and C++ grammar, parser, and code model.