Performing Database Administrator tasks
Creating the SQL data source
JBuilder is an application development environment in which you can create applications that access database data, but it does not include menu options for features that create SQL server tables. Typically, this is an operation reserved for a Database Administrator (DBA). However, creating tables can easily be done using SQL and JBuilder visual design tools.
This topic is not intended to be a SQL language tutorial but to show you how you can use SQL statements in JBuilder. For more information about the SQL syntax, refer to any book on the subject. One commonly
used reference is A Guide to the SQL Standard by C.J.Date.
Note: On many systems, the DBA restricts table create rights to authorized users only. If you have any difficulties with creating a table, contact your DBA to verify whether your access rights are sufficient to perform such an operation.
To create a simple table:
- Follow the steps in Connecting to a database using JDBC, using the suggested values for the connection properties. By default, the Database component is added as database1.
- Add a QueryDataSet component.
- Edit the Query property of the QueryDataSet, by default, queryDataSet1.
- From the Database drop down list, choose database1.
Now we are ready to specify and run a query in the SQL statement field. For example, in the Query property editor,
- Enter the following in the SQL statement field:
create table mytable (
lastName char(20),
firstName char(20),
salary numeric(10,2) );
- Click the Test Query button. The query executes and generates an error dialog, warning you that the query did not return a result set. Ignore the warning and close the dialog.
Note: JBuilder issues this warning because it expects the SQL server to return a result set from the query execution. JBuilder then stores the result set in the StorageDataSet. Since this SQL statement did not return a result set, JBuilder displays the warning message. We're not expecting data returned from this query so we can ignore the warning message in this case.
You've completed creating an empty table which can be used in a query.
Click the Browse Tables button to verify that the table was created correctly. In the dialog that displays, you see
- a list of tables in the DataSet Tutorial data source, including the new table (MYTABLE) we just created.
- a list of columns for the selected table. Select MYTABLE and the columns list displays FIRSTNAME, LASTNAME and SALARY.
Close the Browse tables dialog to return to the Query property editor dialog. In the SQL statement field, you can change the table name and the list of columns then use the Test Query button repeatedly until you have created all your tables or satisfied your curiosity about creating tables using SQL.
Populating a SQL table with data using JBuilder
Once you've created an empty table, you can easily fill it with data. The following example uses a GridControl to present a UI for data entry. The new data is then saved to the database.
- Follow the steps for Creating the SQL data source, but substitute the SQL statement with the following:
select * from mytable;
- Add a GridControl and set it to the QueryDataSet component (by default, queryDataSet1)
- Follow Steps 4, 5, and 6 in the Basic resolving tutorial. These steps describe adding a Save changes button which, when pressed, saves changes made to the data to the database.
Now, run the application. In the running application,
- Enter data into the grid.
- Click the Save Changes button when you've completed data entry.
When you press the button, all your changes are written to the table. The next time you run this application, the data you just entered will display in the grid and you can use this application to modify and save data again.
Deleting tables in JBuilder
Now that you've created one or more test tables, you'll need to know how to clean up and remove all the test tables. Follow the steps for Creating the SQL data source but substitute the SQL statement with the following:
drop table mytable;
You can verify the success of this operation by using the Browse Tables button of the Query property editor to
see that the table does not appear in the tables list.