| This page contains instructions on the use of databases on the Database application projects at the department of Computer Science A form to order Oracle user accounts is included. |
Two of departments servers bodbacka.cs.helsinki.fi and users.cs.helsinki.fi are dedicated for use as database servers for student projects. Bodbacka server runs the Oracle databases (Oracle 11.2.0) and users.cs.helsinki.fi runs the postsgreSQL databases. Both database environments are centrally managed.
You need an Oracle account to be able to use the Oracle database. When you have the account you are able to create your own tables and access these tables. To create a user account, go to the Oracle account management page. To enter the page you must provide your CS departments main account name and password. You may use this page also to change the password, even if you have forgotten your current one. The account name in Oracle will be the same as your account name.
A relational database is operated with SQL commands and scripts. These sripts and command can be used in the Application project, for example, to create tables and to enter and query test data.
Setup
To execute SQL commands and scripts you need an SQL-client software. Users.cs.helsinki.fi provides a textual interface (sqlplus. To start the program you must set up some environment variables. Use the command setup oracle to do that.
Textual interface
The textual interface (sqlplus) is started with the command sqlplus /nolog. The prompt changes to 'sql>'. A database session is then started using the connect-command:
"connect <USER>/<PASSWORD>@bodbacka/test".
Replace <USER> and <PASSWORD> with your Oracle account name and password, for example, a session on database test as user scott (password tiger) is started as"connect scott/tiger@bodbacka/test".
Some useful commands in the textual interface are listed below.
- exit ends the session and stops the interface
- You may write the query directly into the command buffer. In writing the text you may change lines as you wish. Semicolon as the last character of the query will cause the query to be executed. The same effect is achieved by entering forward slash as the last line of the query.
- edit opens your default editor (defined by the environment variable EDITOR) to edit the query. To open the editor you need to have some text in the command buffer (for example 'select'). You must end your command with a semicolon or a forward slash as a separate line of text. Normal exit from the editor changes the contents of the command buffer.
- The command buffer may contain only one command at a time
- If the query is written directly into the command buffer, then the return after the semicolon will activate the execution. If you have used an editor to build up the query, you need the command run (or r) to execute it.
- list (or l) causes the contents of the command editor to be listed
- There are commands available for direct manipulation of the command buffer. The following example shows how to replace text.
SQL> list 3 SQL> This is line 3 and is to be changed SQL> c/is to be/has been/ SQL> This is line 3 and has been changed SQL>The first command selects the line number 3 as the target. The second command defines the change. The change is verified by listing the changed line.- save filename.sql saves the contents of the command buffer as file filename.sql
The queries or query scripts may also be made externally outside the textual interface. A file that contains a sequence of sql commands may be executed using the command start filename.sql or @ filename.sql. Each command in the sequence must end with a semicolon or forward slash as a separate line, for example:
insert into table1 values(1,2); insert into table1 values(2,3); commit;or
insert into table1 values(1,2) / insert into table1 values(2,3) / commit /You should keep in mind that update, insert and delete operations need to be committed to take effect.
Graphical interface
The graphical TOra database tool is started with the command /opt/tora/bin/tora. You must enter your Oracle account name and password, schema name = account name, and the database name (bodbacka.test) to connect to the database.
Some Oracle manuals in pdf-format (about 2-10M each):
- SQL Reference Manual
- SQLplus Quick Reference
- SQLplus User's Guide and Reference
- Error Messages
- JDBC Developers Quide and Reference
- PL/SQL User's Guide and Reference
There is a script wanna-postgres to make an account on the postgreSQL database. This script is available only on users.cs.helsinki.fi.
Porsgres provides a textual interface psql.
Postgres may be used in servlets and in php. The driver needed for database connections is /usr/local/pgsql-7.4/jdbc.jar. The name of the driver class is org.postgresql.Driver. Connection data needed in establihing database connections is jdbc:postgresql://localhost/username.
Harri Laine 3.1.2011,