External Databases and Database URI

How to tell onCourse where your data lives

In order to connect to external databases such as MySQL, MS SQL and Oracle, onCourse Server needs to know where the external database lives. This is done by starting the server with special arguments (in the cases of headless configurations commonly found on Unix-derivative systems such as FreeBSD, Linux and Solaris), or choosing the external database option through GUI when first starting onCourse Server.

Note that connecting to an external database is a paid for feature and will require that you contact us to enable it.

Skipping the actual configuration of the database for the moment, we have some different URIs that connect to various different database types:

1. no file protocol specified (uses Derby DB internal to onCourse) :
   • -Ddburi=/Users/matthias/Desktop/onCourseServer/onCourse.iocdata
   • -Ddburi=“C:\Documents and settings\onCourse\onCourse.iocdata”
   • -Ddburi=“onCourse\onCourse.iocdata”
2. file protocol specified (uses Derby DB internal to onCourse):
   • -Ddburi=file:///Users/matthias/Desktop/onCourseServer/onCourse.iocdata
   • -Ddburi=“file:///C:/Documents and settings/onCourse/onCourse.iocdata”
     • use 3 slashes after the protocol name “file” in both cases
     • use /, not \ also under Windows
     • the application replaces all white spaces with “%20”
     • If you use windows or there are white spaces in the URI path you must surround the URI with “.
3. network databases: an URI contains the following information:
   • host, e.g. 203.29.62.146 or delish.ish.com.au
   • port, e.g. 1376, 3306 or 1521
   • database name, e.g. onCourse
   • user name, e.g. oncourse
   • password, e.g. oncourse
   • every vendor has its own URI (URL) format which has to be used (remind quotations)
   • MS SQL Server:
     • -Ddburi=jdbc:sqlserver://<host>:<port>;property=value[;property=value]]
     • -Ddburi=‘jdbc:sqlserver://203.29.62.146:1376;database=onCourse;user=oncourse;password=oncourse’
   • MySQL:
     • -Ddburi=jdbc:mysql://<host>:<port>/<dbname>?user=username&password=password
     • -Ddburi=‘jdbc:mysql://delish.ish.com.au:3306/onCourse?user=oncourse&password=oncourse’
   • Oracle:
     • -Ddburi=jdbc:oracle:thin:<username/password>@//<host>:<port>/<dbname>
     • -Ddburi=‘jdbc:oracle:thin:oncourse/oncourse@//203.29.62.146:1521/onCourse’
   • It is not necessary to specify a port, if the default port is used.

Tweaks

See here for tweaking databases for better performance with onCourse.

More information about URIs at http://blogs.msdn.com/ie/archive/2006/12/06/file-uris-in-windows.aspx