Connecting Derby database with RAD 7.0

Creating a database connection

In the Database Explorer view, connections can be made to a list of supported relational databases. Some of the supported databases are IBM Cloudscape, IBM DB2 Universal Database, Derby, MySql, Microsoft SQL Server, Sybase Enterprise Systems, and Oracle Database. Refer to the product help for a complete list of the supported databases.

In this step, create a database connection to a Derby database that is shipped with Application Developer. A Derby database can use any file system folder as a database. You do not need to create the database before trying to establish a connection. If you select Create the database if required, the Derby database is created before the connection is established.

Derby accepts only one database connection at a time. For example, if a WebSphere Application Server is already connected to the Derby database, an attempt to make a connection from the Database Explorer will fail.

To create a Derby connection:

1. If the server is running, stop it now or you cannot establish a database connection.
   In the Servers view, right-click on WebSphere Application Server v6.1 and select Stop.
2. Switch to the Data perspective. Select Window > Open Perspective > Other. Select the Show all check box and select Data. Click OK. Select OK if asked to enable the Core Database Development capability.
3. Right-click Connections in the Database Explorer view, in the bottom left of the perspective, and click New Connection.
4. Select Derby 10.1 as shown in Figure 16.
5. Enter any file system directory in the Database location, for example C:\HelloWorldSeries\AddressDB. You do not have to physically create the folder in the file system; just type in a new folder name.
6. Click Browse to select the class location. Browse to \runtimes\base_v61\derby\lib\derby.jar as the class location.
7. Make sure the Create the database if required box is checked. If it is, the tool sets up the Derby database at that location. Because Derby is a test database, you do not need a user ID and password to connect to it. Enter any user ID in the user information section.
8. Click Test Connection to see if a connection can be established. You should see a window saying the connection is successful. Click Finish to create the connection. If it returns an error stating the user name is invalid, leave both user ID and password blank.
   
   Figure 16. Creating a Derby database connection 
   
   
9. The connection AddressDB is displayed in the Database Explorer view. Expand the connection to see AddressDB, as shown below.
   
   
   Figure 17. Database Explorer AddressDB connection 
   
   

Back to top

Creating a data model

Right now, the database is empty. Use the Data Project Explorer view to create a data model that contains schema and tables. A data model represents relational data objects that may or may not exist in the database. Later in this exercise, the data model will be deployed to an actual database.

1. In the Data perspective's Data Project Explorer view, right-click New and click Project.
2. Select Data > Data Design Project > Next.
3. Type MyDataProject as the project name and click Finish.
4. Right-click MyDataProject and click New > Physical Data Model.
   
   
   Figure 18. Creating a physical data model 
   
   
5. Select Derby as the Database and 10.1 as the version. Click Finish.
6. Expand MyDataProject > Data Models > Database Model.dbm > Schema. 
7. Create a table named AddressTable. Right-click on the Schema > Add Data Object > Table. A default table named Table1 is created.
   
   Figure 19. Creating a table 
   
   
8. Select Table1 and go to the Properties view. Change the following:
   
   
   Figure 20. Modifying table name 
   
   
9. Switch to the Columns tab in the Properties view.
10. Click New to add a column. Enter id as the column name. Click the Primary Key check box and select INTEGER as the column type.
11. Click New. Enter name as the column name. Select VARCHAR as the column type. Change the string length to 100.
12. Click Add Another. Enter address as the column name. Select VARCHAR as the column type. Change the string length to 200.
13. Save the file.
    
    
    Figure 21. Table columns for AddressTable 
    
    
14. In the Data Project Explorer, right-click the newly created AddressTable and click Generate DDL. Click Next three times, accepting all the default values. This generates a DDL file named script.sql that can be executed to create the AddressTable in the database.
15. Right-click script.sql under the SQL Scripts folder and select Run SQL. This will create the AddressTable in the Derby database.
    
    
    Figure 22. Run SQL 
    
    
16. Select the Use an existing connection option. Select AddressDB as the database connection. Click Finish.
    
    Figure 23. Run SQL wizard 
    
    
17. Expand your connection until you see the Tables folder. You'll see the AddressTable you just created.
    
    
    Figure 24. After the schema definition is deployed 
     

source:http://www.ibm.com