Frequently Asked Questions

This page will list the answers to some frequently encountered problems. If you still have issues after reading this document, feel free to contact the course staff on Piazza.

Table of Contents

VSCode / SSH

  1. The Remote SSH extension keeps loading when connecting to the remote CS servers
    The latest VS Code versions introduced a bug which prevents users from connecting using the Remote SSH extension. We can temporarily workaround this issue by using one of two solutions:
    Solution 1:
    • Downgrade to 1.100.3 version of VS Code by downloading it here. Make sure to download the one that matches your OS + Processor.
    • In your VS Code settings, search for "update" and change "Update: Mode" to "none" to prevent it from automatically updating again.
    • Try using the Remote SSH extension to connect now

      • NOTE: Any version before 1.101.X should work fine. You can choose a different version from the left sidebar on the page.
      WARNING: This may cause existing extensions to stop working. It may also be the case that once you downgrade, your extension may prompt you to upgrade your VSCode to the latest version. If those constant prompts bother you, you can look into disabling the prompts.
    Solution 2:
    If you do not want to downgrade your VS Code version, you can try this solution:
    • Run the following command in your Terminal: ssh CWL@remote.students.cs.ubc.ca and then use your CWL password to authenticate yourself.
    • Modify your environment variables on startup by modifying the .bashrc file. Run command nano .bashrc. This will open up the .bashrc file directly in the Terminal for you to edit.
    • Add export NODE_OPTIONS="--disable-wasm-trap-handler" to the bottom of the .bashrc file, navigating the file using the arrow keys.
    • Save the file using Ctrl + X followed by Y and then Enter
    • Reconnect using the Remote SSH extension
    • If using the JavaScript version of the project, add unset NODE_OPTIONS to line 2 of the remote-start.sh script.
    This fix was taken from this GitHub issue related to the Remote-SSH connection issue.

    If running Node for other projects on the CS server, you may see this error: node: --disable-wasm-trap-handler is not allowed in NODE_OPTIONS. In this case, you will need to run unset NODE_OPTIONS in your Terminal before launching the Node server.
  2. I'm running into a disk quota reached / no more space left error when trying to add a new directory or file on the remote server.
    1. Open your terminal and log into SSH. sometimes, when your disk quota is reached, you won’t be able to log into SSH through VSCode or whatever IDE you are using, so do this on your OS’s terminal.
    2. Type in du -sk * .??* | sort -nr | head -20 . This should show the top 20 files that use the most space, and usually .vscode-server and .cache are the two files taking up the most space.
    3. Type in rm -rf .vscode-server or rm -rf .cache to remove those files (these should be safe to remove).
    4. Once that’s done, type in du -sk * .??* | sort -nr | head -20 again to make sure you deleted the right files or quota -ws -f /home --hide-device --show-mntpoint to see how much space you have left.
  3. I am unable to see the .env file in VS Code
    1. Go to your VS Code settings (Cmd/Ctrl + ,) and search for the Files: Exclude setting
    2. Find the option called **/.* or something similar
    3. Remove this option from the list and you should be able to see the .env file again.

Oracle

  1. I can't log in.
    Ensure that your log in credentials are correct. Your username should be "ora_CWLusername" and your password is a(student number). For example, if your CWL username is platypus and your student number is 12345678, then your log in credentials for Oracle would be ora_platypus and a12345678.
  2. I am receiving a "ORA-28000: the account is locked" message.
    The course staff does not control the database nor do we have permissions to help you fix this issue. You will need to contact help@cs.ubc.ca with your name, CWL, and student number to ask for them to unlock the account. Please note that the help desk works normal business hours and it may take 2-3 business days to receive a reply.
  3. I'm doing things in my application, but they're not showing up in the database or I'm doing things in the database, but they're not showing up in the application
    When you write to the database, it's not actually guaranteed to be in there until after you've committed the data (more on this in 404). Try adding the command "Commit;" (no quotation marks) at the end of your script/series of actions.
  4. I'm getting a "SQL Plus: command not found" error in my terminal
    This error may result from an incomplete PATH variable setup or a PATH variable that was incorrectly modified during a file cleanup.
    Solution 1:
    Try running the following command: /cs/local/generic/bin/sqlplus
    Solution 2:
    • Open your .bashrc file
    • Check if the following directories are included in your PATH:
      /cs/local/bin:/cs/local/generic/bin
    • If these paths are missing, add them by editing the PATH variable in .bashrc
    • After editing, save the file and run

Other useful tips:

  1. Every DBMS vendor implements some variation of SQL. You will need to look at Oracle's official documentation or other resources to find out how to write SQL in Oracle. Some infrequently used functionalities, like CHECK and TRIGGER, set difference, etc., usually have slightly different syntax.
  2. Oracle does not allow assertions.
  3. Oracle does not commit every transaction immediately. If two sessions are connected to the same database, and Session 1 deletes Record A without commit. When Session 2 tries to delete Record A, it will block until Session 1 commits. Your teammates, for example, might also be using your database. So, commit often in your programming code. You should also be using one connection in your code; do not create multiple connections.
  4. INSERT tips: ON UPDATE statements are not supported in Oracle; so, you need to remove them. Also, check the formatting of your INSERT statements (you might have missed a semi-colon or a quote, or your spacing may be inconsistent).

SQL*Plus

  1. I can't seem to log in.

    When logging into the undergrad Oracle server using SQL*Plus, you need to include the @stu at the end of your username (e.g., ora_platypus@stu).

    What is the stu for? There are really two different authentication systems, one is the undergraduate server, and the other is the Oracle database, which is hosted on the undergraduate server.The stu part specifies which database you are connecting to. Each database server (like the undergrad Oracle server), hosts multiple databases for different users. As an undergraduate student, you only have access to one of the databases (the one called stu).

  2. I can't log in to SQL*Plus (and I know the username/password is correct) or I am seeing a message like "maximum simultaneous connections reached".

    You may have some outstanding connection processes that need to be killed. You can identify these processes by typing ps -u <your_userid> at the command prompt. Type kill -9 <process_pid> to kill a process with pid <process_pid>.

  3. I am running a command in SQLplus but all I get is a number as an output.

    You have probably forgotten to end your SQL command with a semicolon.

Other useful tips:

  1. You should use SQL*Plus to test your SQL statements before embedding them in a program. Many users use SQL*Plus to run ad hoc queries against their tables, and quickly see the results.
  2. To make the changes made in SQL*Plus visible to your program while you are still in SQL*Plus, type commit in SQL*Plus. The reason is that SQL*Plus doesn't commit the changes made by data manipulation language statements such as INSERT, UPDATE, and DELETE until it exits. However, data definition language statements such as CREATE and DROP automatically issue a COMMIT statement so everything before is also committed.
  3. If you run into space issues, you can quickly delete all the tables in your database by following the instructions here
  4. See Other Useful Commands" to learn about SQLplus commands that will make life a bit easier

PHP

  1. I am encountering Object not found! Error 404 when trying to access www.students.cs.ubc.ca/~<CWL username>/<filename>.php.

    You either do not have the specified *.php file in your public_html directory or the permission given to the *.php file is incorrect. Linux permissions are hierarchical; so, you need to make sure that every folder from the root path (i.e., /) allows at least "executable" permission (e.g., chmod 711). See the page on setting up PHP for instructions on how to set file permissions.

  2. I am trying to run oracle-test.php, but the data isn't showing up on my page.

    Here are some things to check:

    • Is the binding of $POST parameters done correctly?
    • Is your query correct?
    • Have you tried using executePlainSQL()?

  3. How do I stop the page from refreshing?

    Every time the page submits a POST request, the page will refresh if $success is not set to false. If you want to stop the page from refreshing, set $success to false.

  4. The database is not returning any answer nor do I get an error message.

    Try tweaking your string formatting. For example, try to remove whitespaces.

  5. When I make a database call, it returns a resource ID instead of an actual value.

    PHP returns a resource object— not the actual result. Therefore, you have to step in one level to "unpack" the desired query result. The printTable helper has a while loop that "unpacks" the returned result.

  6. How do I use session variables?

    Include the following at the beginning of your PHP script.
    session_save_path('/home/<first letter of CWL username>/<CWL username>/public_html');
    session_start()

  7. I am getting an Internal Server Error from the Apache (Red Hat) Server at www.students.cs.ubc.ca, port 80.

    Some students report that this error is due to either:

    1. A permissions error (the PHP file needs 755 permissions- if you need more information on how to do this, refer to this page) or
    2. An error in the PHP code. We strongly recommended that you only make a small number of changes each time you modify the code. In fact, test the code right off the bat to make sure it works for you before making further modifications.

  8. I'm getting the error "Oracle Connect Error ORA-01017: invalid username/password; logon denied"

    Some possible reasons for this are:

    • You have not changed the "username" and "password" line from the given files.
    • You are not entering the login and password correctly. Please check the login and password info on sqlplus to make sure that you have it right.
    • You are entering "@stu" as part of your user name. That will not work. It is listed separately at the end.
    • Somehow in the process of editing the php file, the quotation marks have been replaced with fancy quotation marks. These look like quotation marks but are entirely different characters, and PHP will not recognize them as such. Try replacing the quotation marks with actual quotation marks. You can tell the difference because real quotation marks should show as just up and down and the fancy ones will be curved and/or sideways.
  9. Apache won't start.

    Some possible reasons for this are:

    • You are not using the appropriate server (e.g., lulu.students).
    • Your environmental variables are not set up.
    • You haven't unzipped the Apache files.
    • If it says a file doesn't exist or can't load, go look at the file to ensure it either exists or isn't an empty file. If it is empty, copy it over from the original source.
    • Some file didn't get extracted correctly, try deleting your Apache folder and trying again.
    • Check that file permissions are set correctly (refer to this page for more information).

  10. When I populate or update my oracle db directly from the command line, the changes/populated tables do not appear on my front end via php.

    Run commit work; on the sqlplus terminal command line to commit your oracle db changes.

JavaScript and Node.js

  1. I'm on a Windows machine and I see Syntax error: "elif" unexpected (expecting "then") OR I see Initialization error: ORA-01017: invalid username/password; logon denied

    It may be the case that the line endings are causing an issue. Run sed -i 's/\r$//' remote-start.sh and sed -i 's/\r$//' .env to fix the issue.

  2. I'm on a Windows machine and I see Initialization error: ORA-01017: invalid username/password; logon denied after I run " sh./remote-start.sh"

    If you have ensured your username and password are properly set in the .env file, it may be the case that the line endings are causing an issue. Run sed -i 's/\r$//' remote-start.sh and sed -i 's/\r$//' .env to fix the issue.

  3. When deployed locally, I see ORA-12170 (TNS: Connect timeout occurred)

    • Ensure you are connected to UBC's secure network, either via ubcsecure or through the UBC VPN.
    • Note the port number on which your local express server is running.
    • Run ssh -L <port number from the previous step>:dbhost.students.cs.ubc.ca:1522 <CWL>@remote.students.cs.ubc.ca locally, and keep the terminal open to maintain the SSH tunnel to the CS servers.
    • In the .env file, update the following settings: ORACLE_HOST=localhost and ORACLE_PORT=<port number from the previous step>.

  4. Afer I run " sh./remote-start.sh" it says "Connection pool started", but when I go to my browser, it won't connect. What do I do?

    Make sure you open a new terminal in your local project folder as shown in the last step

  5. I'm having an issue connecting to the database locally. I am able to get the link to open the project but after it opens, the database doesn't connect and gives this error in terminal,
    Error: NJS-503: connection to host 127.0.0.1 port 50000 could not be established. (CONNECTION_ID=6P/qacPdKFVnmCgE3LRpYg==) connect ECONNREFUSED 127.0.0.1:50000

    Make sure you follow all of the instructions to deploy locally every time.

  6. I am trying to run the base JavaScript/Oracle demo project locally, but when I initiate the project with .\local-start.cmd I always get the error: Initialization error: DPI-1047: Cannot locate a 64-bit Oracle Client library: "The specified module could not be found".

    Alternatively:

    Initialization error: ORA-12262: Cannot connect to database. Could not resolve hostname localhost in Easy C/<name> connection string localhost
    Help: https://docs.oracle.com/error-help/db/ora-12262/

    Advice from a (very helpful!) student:

    After being stuck on this for a(n upsettingly) long amount of time, I decided that editing the PATH variable was simply not working, so I instead added a new line to the function initializeConnectionPool() in appService.js:

    oracledb.initOracleClient({ libDir: process.env.ORACLE_DIR })

    This allowed me to hardcode the directory of the Instant Client into a new .env variable ORACLE_DIR, rather than having oracledb try to find it in the PATH variable.

    After I solved this issue, I discovered that my computer was also having problems with the provided code for loading the variables from the .env file, which was resulting in an incorrect connectString being passed oracledb.createPool(dbConfig). I'm not sure if this is a general Windows issue or something more specific to my own computer or IDE, but simply not using the code from envUtil.js entirely fixed that issue. You can do this by using the node dotenv library to access environment variables via process.env.<variable name>.

    Overall, in the interest of saving someone else from struggling with these issues for as long as I did, here is exactly what to change from the demo project:

    1. Open a terminal, navigate to your project folder, and run npm install dotenv
    2. Add a new variable to your .env file called ORACLE_DIR. Set its value to be the absolute path to your Oracle Instant Client. ORACLE_DIR='your absolute path here'
    3. In appService.js, change the top part of the file to be: 
      const oracledb = require('oracledb');
require('dotenv').config();


// Database configuration setup. Ensure your .env file has the required database credentials.
const dbConfig = {
    user: process.env.ORACLE_USER,
    password: process.env.ORACLE_PASS,
    connectString: `${process.env.ORACLE_HOST}:${process.env.ORACLE_PORT}/${process.env.ORACLE_DBNAME}`,
    poolMin: 1,
    poolMax: 3,
    poolIncrement: 1,
    poolTimeout: 60
};


// initialize connection pool
async function initializeConnectionPool() {
    try {
        oracledb.initOracleClient({ libDir: process.env.ORACLE_DIR })
        await oracledb.createPool(dbConfig);
        console.log('Connection pool started');
    } catch (err) {
        console.error('Initialization error: ' + err.message);
    }
}
// ... and the rest of the file stays the same

Java and JDBC (no longer supported)

  1. I can't seem to log in to Oracle.

    See the first item in the Oracle section.

  2. How do I resolve Error: package oracle.jdbc.driver does not exist?

    You have not included the JDBC driver package in your CLASSPATH. The JRE needs to be able to find this package during compile and run time. You can search Google for how to configure CLASSPATH as an environment variable, or explicitly provide it as an input when executing the javac and java commands. If you are compiling your Java program on the department's server, try executing source jdbc.env first.

    If you are using Eclipse or IntelliJ, see here for instructions on how to add a JAR to your project.

    For other IDEs, you'll need to add the JDBC driver to your project dependency, and possibly configure port forwarding if you are not on a UBC network. You should read your IDE's documentation to learn how to set up the project dependency, and read the ssh documentation or Google to set up port forwarding (some of this is mentioned above).

  3. Which Oracle instance am I connecting to?

    The Oracle instance that you'll be connecting will depend on which method you use from the page on getting started:

    • Method 1: String ORACLE_URL = "jdbc:oracle:thin:@dbhost.students.cs.ubc.ca:1522:stu"
    • Method 2: String ORACLE_URL = "jdbc:oracle:thin:@localhost:1522:stu"

    If you are using Eclipse/IntelliJ on your local machine to connect to Oracle, make sure that port forwarding is on and change your connectURL string to jdbc:oracle:thin:@localhost:1522:stu.

  4. My program is hanging (freezing) when I try to login to using my application.

    This is likely due to a network timeout. Most commonly, this happens when trying to connect to the undergrad Oracle database outside of a UBC network like ubcsecure. Check https://my.cs.ubc.ca/docs/connecting-cs-resources or setup port forwarding via SSH. You want to forward to dbhost.students.cs.ubc.ca:1522 from localhost:1522.

  5. I am getting a string not terminated at end of line compilation error.

    You probably included a line break in the string, (e.g.,
    executeQuery("SELECT a, b, c
     FROM abc");    ).

    Do not line break a string. You can, however, do the following:
    executeQuery("SELECT a, b, c " +
    "FROM abc");

  6. I am running some code on the department's servers but am getting a java.awt.HeadlessException error.

    It probably relates to your windowing environment. Essentially, you are trying to run a GUI program but the server doesn't know where to display the UI. Make sure that you are using an X server and a terminal emulator like xterm, Xshell, or putty that supports X11 forwarding on your machine.

    Also, make sure that X11 Forwarding is taking place. In particular, for SSH using xManager, go to Properties -> SSH -> Tunneling, and make sure that the box "Forward X11 connections to:" is checked. For Mac/Linux users, make sure you are using ssh -X or ssh -Y when signing into the remote server.

  7. My Oracle account has been locked after a bunch of unsuccessful login attempts.

    Contact help[AT]cs.ubc.ca to get them to reset your locked account.

    If you're pretty sure that you entered your user id correctly (without the @stu) and you entered the password correctly, the problem is likely with the branch program running on SSH, where SSH (xManager) is an older version. Possible workarounds:

    • Upgrade SSH to version 6
    • Copy and paste your user id and password into a Notepad or Word document, and then copy and paste it into the pop-up box resulting from the branch application. Some students report that this workaround solved the problem.

  8. I am getting IO Error: The Network Adapter could not establish the connection

      It seems the easiest way to resolve this is to remove your data source and redo the steps below. The problem seems to occur if Bank.main() is ran before the database connection is completed.

    1. Configure SSH Tunnel (View | Tool Windows | Database -> click Data Source Properties icon -> click SSH/SSL tab -> click the ... beside "Use SSH Tunnel". Test connection should be successful. If not, check correct username and password used (regular CWL ID).
    2. Configure database connection info (ora_CWLID, a[student #]). Test connection should be successful if credentials are correct.
    3. After clicking okay, you should see the database connection in progress
    4. Once the connection is completed successfully, you should be able to see a list of existing tables in the database (only if you have completed Tutorial 5 first). Do NOT run Bank.main() until you see the following because it seems doing so can cause the connection to just stop before it's completed, causing the IO error. If the connection gets stuck, right click on the data source (I named it demo) and click refresh.
    5. Now running Bank.main() & filling in the username (ora_CWLID) & password ( a[student #] ) should result in the following

    If you are running the code through the terminal and running into the same problem, try the steps below:

    1. Open a terminal window and navigate to the jar file directory (window 1)
    2. On a new terminal window (window 2), run this (replace with your CWL):
      3. ssh -l CWL -L localhost:1522:dbhost.students.cs.ubc.ca:1522 remote.students.cs.ubc.ca
    3. Now log in with your CWL password and leave the window (window 2) open.
    4. Go back to the other terminal window (window 1) and try running the jar again. You can log in with ora_CWL and a[student #].

  9. I can't even login to the department's servers anymore.

    Send an e-mail to help[AT]cs.ubc.ca and they should be able to reset your config files. Just be sure to give them your CWL.

  10. I am getting a java.lang.NoClassDefFoundError message when running java branch on a department server.

    You probably need to set the environment variable again. Every time that you log out and log in again, the Oracle JDBC environment variable is cleared. When you log in, you can set the environment variable again by typing source jdbc.env.

  11. I am having issues with my .bashrc file when I log into the department's servers.

    You can download a fresh copy of .bashrc from the Getting Started page for Java/JDBC projects.

  12. I am getting an Invalid Oracle URL error when running my Java program locally.

    Make sure the connection string has been changed from jdbc:oracle:thin@dbhost.students.cs.ubc.ca:1522:stu to jdbc:oracle:thin:@localhost:1522:stu before running it. Also, make sure that you have your ssh port forwarding running.

  13. My SQL query doesn't seem to work when I run it in my code but it works fine when I paste it in SQL Plus.

    Are you working with a column that has been declared as a CHAR? When you have values in a column with CHAR, it adds whitespaces at the end of the value before storing it in the database. Use a VARCHAR instead of CHAR (see here for more information).

  14. I do not see any Database option in IntelliJ

    There are two different problems that could cause this, 90% of the time, number 1 is the problem:

    1. SQL only works in IntelliJ ultimate. If you have the community edition, you won’t be able to use it inside there. However, the good news is that you can apply for a year long free IntelliJ ultimate since you are a student. Simply go through this link https://www.jetbrains.com/shop/eform/students and apply for one. Make sure to give your ubc email address when they ask for an email address.
    2. If you have IntelliJ ultimate already, and you still don’t see database option in there. Follow these instructions. Hover over File -> Settings -> Plugins -> search database inside the installed tab -> click on Database Tools and SQL - > Enable the plugin > restart IDE
  15. preparedStatement() with placeholder and setString() not work as expected

    Use VARCHAR instead of CHAR for the placeholder variable in your SQL CREATE table statement

    For the CHAR type, the Oracle will add padding at the end of the value that is shorter than the column length. See here for more information

    For setString(), it converts to VARCHAR type, which is the actual length. See here for more information

Other helpful tips:

  1. You cannot create listeners with Oracle Client because the client is not allowed to create an Oracle server.
  2. A good way to find syntax errors is to comment out pieces of your code to narrow down the source of the error in a binary search-like algorithm.
  3. Commit often to avoid holding locks; otherwise, you might get delays in other sessions that access your database.