Setup needed for access to our database servers on dbs3.cs.umb.edu and topcat.cs.umb.edu, for CS636 and CS630, fall 2017

For security reasons, you cannot login to dbs3. Thus you will need to ssh to topcat.cs.umb.edu and then use sqlplus to access Oracle on dbs3.
For example, the sample user scott has password tiger1, and a table named emp. Here we use sqlplus running on topcat.cs.umb.edu to access the database on dbs3 by adding "@//dbs3.cs.umb.edu/dbs3" to the usual sqlplus command:

topcat$ sqlplus scott/tiger1@//dbs3.cs.umb.edu/dbs3

SQL*Plus: Release 12.1.0.2.0 Production on Mon Aug 22 15:45:19 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Fri Aug 19 2016 14:19:03 -04:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opions

SQL> select count(*) from emp;

  COUNT(*)
----------
        14

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 6bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opions
topcat$

To avoid showing your login information on the command line (which is also shown by the "ps al" command to others), do the following:

topcat$ sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Fri Sep 30 13:54:13 2016
 
Copyright (c) 1982, 2014, Oracle. All rights reserved.

SQL> connect scott/tiger1@//dbs3.cs.umb.edu/dbs3
Connected.
SQL> select count(*) from emp;

  COUNT(*)
----------
        14

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 6bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opions
topcat$

Example with student user jsmith: Note how the UNIX/Linux username is also the password here:

topcat$ sqlplus jsmith/jsmith@//dbs3.cs.umb.edu/dbs3

Oracle Accounts

Once you have run apply for a cs630 or cs636 account (even if you are in cs430 or cs436 officially), you are listed for an Oracle account. I will create the accounts and will be the (one and only) Oracle DBA for dbs3. Thus if you believe the Oracle system is not working properly, please email me (eoneil@cs.umb.edu) right away. If you forget your Oracle password, you should be able to get it reset by the UNIX operators in S-3-158 using a special Oracle account set up for that purpose. This is a new functionality for operators, however, and may not be available at the start of the term.

JDBC access to Oracle and mysql from topcat and other Linux hosts at cs.umb.edu

JDBC for Oracle is available at port 1521 on dbs3.cs.umb.edu, and its "sid" is dbs3. You can test a JDBC connection to your own Oracle account by running JdbcCheckup after copying the jdbc directory from your class home page to your own homework directory. Note that when logged in on topcat or other host at cs.umb.edu, you are inside the firewall, so you can contact Oracle's JDBC service directly at dbs3.cs.umb.edu, port 1521, and don't need a tunnel as you will at home, discussed later in this file.

for cs630: cp -r /data/htdocs/cs630/jdbc cs630
for cs636: cp -r /data/htdocs/cs636/jdbc cs636

Then cd to your own jdbc directory, and:

topcat$ javac JdbcCheckup.java
topcat$ java -cp ojdbc6.jar:. JdbcCheckup
Picked up _JAVA_OPTIONS: -Djava.security.egd=file:/dev/../dev/urandom
Please enter information to test connection to the database
Using Oracle (o), MySql (m) or HSQLDB (h)? o
user: xxxxx
password: yyyyy (same as xxxxx unless you changed it)
use canned Oracle connection string (y/n): y
host: dbs3.cs.umb.edu
port (often 1521): 1521
sid (site id): dbs3
using connection string: jdbc:oracle:thin:@dbs3.cs.umb.edu:1521:dbs3
Connecting to the database...connected.
Hello World!
Your JDBC installation is correct.

Similarly, for mysql:

topcat$ java -cp  mysql-connector-java-5.1.39-bin.jar:. JdbcCheckup
Picked up _JAVA_OPTIONS: -Djava.security.egd=file:/dev/../dev/urandom
Please enter information to test connection to the database
Using Oracle (o), MySql (m) or HSQLDB (h)? m
user: xxxxx
password: yyyyy
use canned MySql connection string (y/n): y
host: localhost
port (often 3306): 3306
using connection string: jdbc:mysql://localhost:3306/eoneil1db
Connecting to the database...connected.
Hello World!
Your JDBC installation is correct.


Home machine tunnel setups needed for using JDBC from home

To access the database systems dbs3.cs.umb.edu and topcat and pe07, themselves inside the firewall, from a system outside the firewall, we need a “tunnel” that uses the SSH protocol’s ability to provide a secure connection to a port that is not directly accessible for security reasons. For example, we want to access port 1521 on dbs3 to talk to Oracle using JDBC, but this port is blocked by the firewall.  So, instead we connect to port 22 (SSH’s port, which is not blocked by the firewall for users.cs.umb.edu or topcat or pe07) on topcat.cs.umb.edu (for example) and arrange that SSH make a connection for us inside the firewall to port 1521 on dbs3, and then move the data back and forth.  For more info, see IBM article on tunneling.

For your home Windows machine

See PuttyTunnels.html for instructions on using putty to set up tunnels from home to our database hosts.

On your home Linux or MacOSX machine (in Terminal):

First get a Terminal window for Mac or a shell window for Linux to do the following commands. Use ssh to set up the needed tunnels to dbs3, port 1521, and topcat, port 3306:  These just hang, logged in to topcat.

Replace ‘user’ here with your cs.umb.edu Linux username and answer the password prompt with your cs.umb.edu Linux password.  These commands will “hang”, so open another shell/Terminal window to continue working.

      ssh -N -L1521:dbs3.cs.umb.edu:1521 user@topcat.cs.umb.edu
      ssh -N -L3333:topcat.cs.umb.edu:3306 user@topcat.cs.umb.edu
      or both at once:
      ssh -N -L1521:dbs3.cs.umb.edu:1521 -L3333:topcat.cs.umb.edu:3306 user@topcat.cs.umb.edu

These work on Mac OSX v 10.9.5 on a desktop system. If you are still having trouble, please report your OSX version number and type of Mac.

Testing the tunnels with a browser

Browse to localhost:1521 with Chrome and see "No Data Received". This means it did connect, just didn't get any data from the server.

Try localhost:12345 or another random port to see "This webpage is not available", meaning it couldn't connect.

Try localhost:3333 with Chrome and see a small download happen. It had to connect to do that.

Testing the tunnels with telnet (or similarly, putty or ssh)

telnet is an classic tool that was used before ssh was needed: it just connects your keyboard and screen to a TCP stream connection, plain and simple. It can be used to test to see if a port on a host is open, among other things.  Try “telnet” at the command line to see if it’s available on your system, and if not, optionally install it as follows:

Test mysql tunnel: telnet localhost 3333, or ssh localhost:3333, or putty to host localhost, port 3333

Test Oracle tunnel: telnet localhost 1531, or ssh localhost:1531, or putty to host localhost, port 1531

If successful, telnet or ssh or putty will take over the window and show you anything sent by the server. Anything you type will be sent to the server, except for the telnet escape sequence control-]. We don’t want to confuse the server with garbage, so type the escape sequence immediately, followed by quit, or kill the window.  To see what happens with a port that’s not in active use, try “telnet localhost 12345” (or ssh localhost:12345 or putty ...) or some other random number.

Testing the tunnels with JdbcCheckup:  from Windows, Linux or MacOSX

Once we have the tunnels working, the database ports show up as localhost:1521 and localhost:3333. We are using a local port 3333 to avoid any locally running mysql databases, which would normally use the local 3306 port.  Similarly, if you have a local Oracle server running, you will need to use a different local port for tunneled Oracle.

java -classpath ojdbc6.jar;. JdbcCheckup    (use colon instead of semicolon on Linux/MacOSX)
Please enter information to test connection to the database
Using Oracle (o), MySql (m) or HSQLDB (h)? o
user: xxxxxx
password: xxxxxx
use canned Oracle connection string (y/n): y
host: localhost
port (often 1521): 1521
sid (site id): dbs3
using connection string:
jdbc:oracle:thin:@localhost:1521:dbs3
Connecting to the database...connected.
Hello World!
Your JDBC installation is correct.
java -classpath mysql-connector-java-5.1.39-bin.jar;.
JdbcCheckup
Please enter information to test connection to the database
Using Oracle (o), MySql (m) or HSQLDB (h)? m
user: xxxxx 
password: xxxx
use canned MySql connection string (y/n): y
host: localhost
port (often 3306): 3333
using connection string:
jdbc:mysql://localhost:3333/xxxxxdb
Connecting to the database...connected.
Hello World!
Your JDBC installation is correct.

Sqlplus on your home machine (optional, only for the adventurous)

You can download and install the same "instant client" software we are using on topcat to access dbs3's Oracle database right from your own system.

  1. Visit http://www.oracle.com/technetwork/database/features/instant-client/index-097480.htm and choose your platform, and obtain an Oracle web account if necessary (following its prompt to do so)
  2. Find the list of downloads, and download and unzip one of the first two, plus the third (jdbc) and fourth (sqlplus).
  3. Follow the instructions on the above-linked page.
  4. Make sure your tunnels are working, so Oracle's port appears at localhost:1521.
  5. In a new shell/command window, try sqlplus scott/tiger1@//localhost:1521/dbs3 Either cd to the directory with the executable, or add it to your path.
  6. Since 1521 is the default, you can omit it.

Note that sqlldr is not included in client installation. We have added it to topcat by following instructions here, modified for version 12.1. But if you load your tables while logged in on topcat, you shouldn't need sqlldr on your development system.

Note that once you are using port 1521 for a local Oracle, you can't use it for tunneling to dbs3's port, so use another local port for the tunnel, or bring down your local Oracle when you want to tunnel to dbs3's.

Oracle SQL Developer on your home machine (optional) after getting sqlplus to work

1. Visit http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html and choose your platform. For 64-bit Windows, use the version with the JDK embedded. For other platforms, get Java 8 working first.

2. Download and unzip. Run from the resulting directory or add to your path.

3. Create a new Connection, for example for user scott, password tiger1, hostname localhost, port 1521, sid dbs3, and leave the boxes below the sid blank.

4. Double click the resulting Connection icon, and explore the resulting catalog tree.


Note: the rest of this file is relevant to CS436/636 only.

Environment Variable Setup for Database access from pizza1 and other cs636 projects

We need to use different strings to provide to JDBC to access our databases from outside our departmental firewall than from within it, as we saw when using JdbcCheckup. For our projects such as pizza1, we will use environment variables to hold these strings, and set up their definitions differently based on location. 

For Oracle database, we will set up env variables ORACLE_USER, ORACLE_PW, ORACLE_SITE
For MySQL use, we will set up env vars MYSQL_USER, MYSQL_PW, MYSQL_SITE.

For Linux systems at cs.umb.edu, inside the firewall, we can directly address the database hosts by their real names. Put these lines in your .profile in your login directory, creating a new file if necessary:

        # change these to fit your accounts--
        ORACLE_USER=xxxx; export ORACLE_USER
        ORACLE_PW=xxxx; export ORACLE_PW
        ORACLE_SITE=dbs3.cs.umb.edu:1521:dbs3; export ORACLE_SITE
        MYSQL_USER=xxxx; export MYSQL_USER
        MYSQL_PW=xxxx; export MYSQL_PW
        MYSQL_SITE=topcat.cs.umb.edu; export MYSQL_SITE
        cs636=/courses/cs636/public_html; export cs636

Note: don’t put spaces around the equals signs in the above.

On your home Windows machine:

Use the System control panel to set these environment variables. Note that we tell our app to connect to a certain port on “localhost’, i.e., this host, but our prearrangement with putty means that this will be one end of a putty tunnel to the database port.

There is a shortcut way to get to this panel, as follows. Click the "Start" button and fill in "env" in the resulting text box. Then choose the popup destination "Edit environment variables for your account".

ORACLE_USER=xxxx
ORACLE_PW=xxxx
ORACLE_SITE=localhost:1521:dbs3
MYSQL_USER=xxxx
MYSQL_PW=xxxx
MYSQL_SITE=localhost:3333

On your home Linux machine:

Set up the needed environment variables by editing file .profile or .bashrc in your home directory:
export ORACLE_USER=xxxx
export ORACLE_PW=xxxx
export ORACLE_SITE=localhost:1521:dbs3
export MYSQL_USER=xxxx
export MYSQL_PW=xxxx
export MYSQL_SITE=localhost:3333

Note:don’t put spaces around the equals signs in the above.

This should be executed when you open a new shell window, but if that doesn’t work, you can execute it explicitly by “source .bashrc”.

On your Mac OSX machine:

To set up the needed environment variables, put the above 6 export commands in .bash_profile in your home directory, and log out and in again or use "source .bash_profile".

With just this setup, java, javac, and ant used at the command line will work fine, but you have to run eclipse from the Terminal shell to allow it access to the defined environment variables.  To run eclipse normally, from the dock, you need to wrap it in a little shell script, as explained in DevelopmentSetup.html.

One GUI over all our databases: the eclipse Data Source Explorer view

This is just for your convenience, if you like GUI access to your database data. Alternatively, you can use "ant show-oradb" or "ant show-mysqldb" to display the whole database when you need to look at it.

In eclipse, open the Data Source Explorer view (Window>Show View>Data Source Explorer), and set up a Database Connection for Oracle  Alternatively, use the Database Development perspective (Window>Open Perspective…), which shows this view by default.

1. In Data Source Explorer, right-click Database Connections, select New ..., then Oracle, then fill in a name such as dbs3, click Next.

Now you should see "Specify a Driver and Connection Details". Fill it in like this, where xxxx stands for your UNIX username:

2. Now click Next, select Oracle version 11 thin driver and see:

3. Next select the JAR list tab, replace ojdbc14.jar with ojdbc6.jar found in your jdbc directory (browse to it).

4. Make sure your tunnels are working, and try "Test Connection".

Mysql Replace eoneil with your own username: New Connection window: use the little circled-star and triangle icons in the upper right corner to get jar files attached properly. JARs tab Properties tab

HSQLDB 

The little triangle in the upper right corner is for editing the driver properties, as you can see by mousing over it.

Later, you can get to this view by right-clicking the connection icon and selecting Properties.

The following image shows the result of clicking on the triangle and setting the jar file location (wherever it is.)

db_explorer_hsqldb_jar_setup.jpg

 

Showing loaded tables:

 

 Note for JPA projects (pizza2, etc.): to use this HSQLDB connection with Project Properties>JPA for pizza2/music2, you will need to override the default catalog with PUBLIC.