This should also work on your home system as
soon as Java is working
Home machine tunnel setups needed for using JDBC from home. connecting to databases at cs.umb.edu.
To access the database-hosting systems dbs3.cs.umb.edu and pe07.cs.umb.edu, 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 on users.cs.umb.edu (SSH’s port, which is not blocked by the firewall for users.cs.umb.edu) 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 older Windows machine
See PuttyTunnels.html for instructions on using putty to set up tunnels from home to our database hosts.Needed for older Windows systems that don't have ssh/scp.On your home Linux or MacOSX system (in Terminal) or recent Windows 10 system:
First get a Terminal window for Mac or a shell window for Linux or a CMD window in Windows 10 to do the following commands. Use ssh to set up the needed tunnels to dbs3, port 1521, and pe07, port 3306: These just hang, logged in to users.cs.umb.edu, and doing their work shuttling data around.
Replace ‘user’ here with your cs.umb.edu Linux username and answer the password prompt with your cs.umb.edu Linux password. This commands will “hang”, so open another shell/Terminal/CMD window to continue working.
ssh -N -L1521:dbs3.cs.umb.edu:1521 -L3333:pe07.cs.umb.edu:3306 user@users.cs.umb.edu
These work for Linux, Windows 10, and Mac OSX v 10.9.5+. If you are still having trouble, please report your OS type and version number.
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 curl
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:
- Windows: telnet is usually there, but if not, see Install telnet client. Recent Windows 10 has curl instead.
- Linux: telnet and curl should be available in the shell window.
- Mac: telnet and curl should be there in Terminal’s command line.
Test mysql tunnel: telnet localhost 3333, or ssh localhost:3333, or curl localhost:3333 and compare to localhost 30000, say.
Test Oracle tunnel: telnet localhost 1531, or ssh localhost:1531, or curl localhost, port 1531
If successful, telnet 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.
curl may complain about binary data being sent back from a successful connection to a JDBC server, rather than try to display it. For example, on recent Windows 10:
F:\cs\cs636>curl localhost:3333
Warning: Binary output can mess up your terminal. Use "--output -" to
tell
Warning: curl to output it to your terminal anyway, or consider
"--output
Warning: <FILE>" to save to a file.
This indicates a good tunnel connection. Try another port to see the failure case:
F:\cs\cs636>curl localhost:3334
curl: (7) Failed to connect to localhost port 3334: Connection refused
Testing the tunnels with JdbcCheckup: from Windows, Linux or
MacOSX, i.e., see them actually working
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 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.43-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 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.
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--
export ORACLE_USER=xxxx
export ORACLE_PW=xxxx
export ORACLE_SITE=dbs3.cs.umb.edu:1521:dbs3
export MYSQL_USER=xxxx
export MYSQL_PW=xxxx
export MYSQL_SITE=pe07.cs.umb.edu
export cs636=/courses/cs636/public_html
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 maven (mvn command) 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 Maven's pom.xml in the database subdirectory of projects 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:
Note we are using the local end of the tunnel (localhost:1521) for host and port.
The little triangle in the upper right corner is for editing the driver properties, as you can see by mousing over it.
2. Now click Next, select Oracle version 11 or 12 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 (but version number here is old, replace by current one in
jdbc) Properties
tab
H2 (the "h2file" file-based embedded
database)
H2 is not listed as a Connection Profile type. Choose "Generic JDBC",
then circled +, then select (click) Generic JDBC Driver template. Then
click the "JAR list" tab and browse to h2.jar in your jdbc directory and
select it. Then click the "Properties" tab and fill in:
Connection URL: jdbc:h2:~/test
Database Name: test
Driver Class: org.h2.Driver
User ID: test
Click OK and leave the password blank on summary form, and leave Save
Password selected.
Finally, click Apply and Save.
Later, you can get to this view by right-clicking the connection icon and selecting Properties.
Warning: H2 can handle only one connection at a time, so don't leave this database tool connected to H2 when you aren't using it. It will prevent you from running any other application on H2, including apps run from eclipse. Error: "Database may already be in use", or equivalent.
Sqlplus on your home machine (optional, only for the adventurous)
You can download and install the same "instant client" software we are using on pe07 to access dbs3's Oracle database right from your own system.
- 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)
- Find the list of downloads, and download and unzip one of the first two, plus the third (jdbc) and fourth (sqlplus).
- Follow the instructions on the above-linked page.
- Make sure your tunnels are working, so Oracle's port appears at localhost:1521.
- In a new shell/command window, try
sqlplus scott/tiger1@//localhost:1521/dbs3Either cd to the directory with the executable, or add it to your path.
- Since 1521 is the default, you can omit it.
Note that sqlldr is not included in client installation. We have added it to pe07 by following instructions here, modified for version 12.1. But if you load your tables while logged in on pe07, 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.