Hacking J-Bird (Version 0.5.2): Import from relational databases

It is possible to import the master list or species checklists from any relational database for which you have a Java JDBC driver. To do so, you will provide information that is necessary for Java to connect to the database and an SQL query that returns the names of the species to be imported. You will store the connection information and query in a file and then select the file when importing data into J-Bird. J-Bird will connect to the database, run the query, and then present the results to you just as it does when importing from a file of comma-separated values or from an Excel spreadsheet.

Connection information and query

• ClassPath - describes the locations of the driver classes that are required to access the database. It is needed only if the classes are not already available on the class path. But, drivers generally will not be available on the class path, and most users will have to specify this parameter. ClassPath will usually be the name of a jar archive file (e.g., somedriver.jar) or a zip archive file. It may be expressed as an absolute path from the beginning of the drive (or file system) or as a path relative to the directory in which J-Bird is installed. Less commonly driver classes will exist as class files in a directory tree. In this case the value of ClassPath is the name of the directory that is the base of the directory tree, followed by a slash (e.g., C:\tmp\otherdriver\classes\). Rarely, two or more sets of drivers will be needed to access a database. Multiple locations can be specified by separating names of locations by colons or semicolons (e.g., downloads/drivers/zonga/driver.jar:/home/me/miscjar/library.zip).
• URLClassPath - Specifies the location of driver classes as a URL. This parameter is useful in the really unlikely event that driver classes are to be loaded from some other computer on the network. Chances are that nobody will ever use this parameter.
• DriverClassname - is the name of the driver that is used to access the database. The name to use should be provided by documentation that accompanies the JDBC driver.
• DatabaseURL - is the name of the database that will be accessed. It consists of a prefix that Java uses to figure out which driver to use and the name of the database as it is stored by the database software. Java JDBC drivers are usually packaged with instructions that describe how to specify the URLs for them.
• DatabaseUser - is the user name that one usually uses to access the database. Do not specify this parameter if you do not usually specify a user name when accessing the database.
• DatabasePassword - is the password that one usually uses to access the database. Do not specify this parameter if you don't use a password to access the database.
• SQLQuery - is the query that produces a list of species to import. The query must produce common names or scientific names. Both are OK. Scientific names may appear in one column or be split into a column of genera and a column of specific epithets. Other columns are unnecessary and they will be ignored by J-Bird. You will tell J-Bird which columns contain which information after the query has been executed.
  
  A very simple example is one in which species names and counties are in a single table.
  

  	SELECT * FROM countylists WHERE county = "Lorain"
  		

  
  More often names will be stored in a different table than regional checklists.
  

  	SELECT CommonName FROM common, checklists WHERE checklists.Region = 22 AND checklists.SpeciesID = common.SpeciesID
  		

Format of parameter file

Parameters are stored in a plain text file according to the following formatting.

• One parameter and value pair per line.
• The general format is "parameter_name = value".
• Any amount of white space can appear before the name of the parameter, between the parameter name and the equal sign and between the equal sign and the beginning of the value.
• A parameter-value pair can span more than one line. To continue a pair across lines, place a backslash as the last character at the end of each line to be continued onto the next. This is really useful for large SQL queries.
• Blank lines are OK and ignored.
• Entire lines are treated as comments if the first non-whitespace character is #.
• The file is really a Java properties file, and any format that is OK in a property file but not mentioned here is OK.

Example parameter file:

Name of parameter file

The name of the property file must begin with "JDBC-import" and end with ".jpf". This is a convention that J-Bird uses to identify parameter files that will be used to import from relational database. An example of a name might be: JDBC-import-Oklahoma.jpf. If when you attempt to import data, J-Bird appears to be trying to import the contents of your configuration file rather than data extracted from the database, the cause is probably that the name of the configuration file has not been recognized as that of a configuration file.

Importing

To import the master species list from a database, construct the configuration file; run the master list builder and select the option for building the master list from table types that include relational database.

To import a checklist from a database, follow the first steps that you would for any import. When asked to select a file that contains the data (or a web page), select the parameter file. J-Bird will detect the name and import from the relational database.

Example: Context for both examples

In the examples below, the goal is import a checklist from a relational database of checklists. The database is named BirdLists, and it contains three tables.

• Species - a table that contains names of species in the database. It has two columns.
• SpecNo - a unique numerical identifier for each species - the primary key of the table.
• CommonName - a common name for each species.
• ListInfo - a table that contains names of checklists in the database. It has two columns.
• ListNo - a unique numerical identifier for each checklist - the primary key of the table.
• ListName - a name for each checklist.
• ListMembers - a table that indicates which species are found on which checklists. It has two columns.
• SpecNo - a species identifier.
• ListNo - a checklist to which the species belongs.

In the examples, names of species on one of the checklists will be imported. To keep the examples simple, checklist names will be ignored, and species names will be imported from checklist number 4. An SQL query for extracting species from checklist 4 is:

	SELECT Species.CommonName FROM Species, ListMembers
		WHERE ListMembers.ListNo = 4
			AND ListMembers.SpecNo = Species.SpecNo

Example: Importing from Microsoft Access

To import data from Access databases an extra step is required: the database must be available as an ODBC Data Source Name (DSN). This is discussed first because the Data Source Name that you give the database will be used in the configuration parameters that are used by J-Bird. If you have not already set up an ODBC Data Source Name, follow the steps below.

• From the control panel, double click on "Data Sources (ODBC)" on Windows 95/98/ME. On Windows XP, click on "Administrative Tools" and then on "Data Sources (ODBC)".
• Look at the tabs, and select "User DSN" if it's available. Otherwise select "System DSN". User DSN limits the connection to you and makes it available only to your computer (rather than to other computers on the network).
• Select "MS Access Database - Microsoft Access Driver (*.mdb)" and then click on the "Add ..." button on the right.
• On the next panel, select "Microsoft Access Driver (*.mdb)", and click the "Finish" button at the bottom of the dialog.
• On the panel that appears, provide a Data Source Name in the blank provided. The name that you provide will be the name that you reference in the import configuration file. It does not need to be the same as the name of the Access database, but it is much simpler if the names are the same. However, it is best to void spaces in the Data Source Name.
• Click on the "Select ..." button in the Database square, and use the file browser to select the .mdb file that is the Microsoft Access database that you wish to use.
• Click OK, and OK again.

The Access database in this example is named BirdLists, and for simplicity the ODBC DSN has also been named BirdLists. The parameters are as follows.

• ClassPath, URLClassPath - are not needed for Microsoft Access because the driver is provided as part of the Java software.
• DriverClassname - sun.jdbc.odbc.JdbcOdbcDriver will always be the name of the driver that is used to access Microsoft Access databases.
• DatabaseURL - jdbc:odbc:BirdLists. The prefix jdbc:odbc: is used by Java to select the appropriate driver (used for all MS Access databases), and BirdLists is the ODBC Data Source Name that was given to the database.
• DatabaseUser, DatabasePassword - are not needed with the Access database.
• SQLQuery - is the query given above and below in the text of the file.

The exact text of the configuration file follows.

	DriverClassname = sun.jdbc.odbc.JdbcOdbcDriver
	DatabaseURL = jdbc:odbc:BirdLists
	SQLQuery = SELECT Species.CommonName \
			FROM Species, ListMembers \
			WHERE ListMembers.ListNo = 4 \
				AND ListMembers.SpecNo = Species.SpecNo

Note that the use of backslashes permits the query to be nicely formatted by spreading it out over several lines.

The configuration is complete.

Importing from MySQL

In this example, the database BirdLists resides in a MySQL database that is running on the same computer that is running J-Bird. A location of the driver must be specified along with a database username and password.

• ClassPath - /usr/share/jdbc-mysql/lib/mysql-connector-java-3.0.11-stable-bin.jar. The driver happens to be installed in a system directory and is referenced with a full path name.
• URLClassPath - not needed in this example.
• DriverClassname - com.mysql.jdbc.Driver will always be the name of the driver that is used to access MySQL databases.
• DatabaseURL - jdbc:mysql:///BirdLists. The prefix jdbc:mysql: is used by Java to select the appropriate driver (used for all MySQL databases), and ///BirdLists specifies the location (on this server) and name of the database.
• DatabaseUser - Twitcher happens to be the user name that is associated with the database.
• DatabasePassword - tiktiktik is an extremely insecure password, but it was used in this database.
• SQLQuery - is the query given above and below in the text of the file.

The exact text of the configuration file follows.

	ClassPath = /usr/share/jdbc-mysql/lib/mysql-connector-java-3.0.11-stable-bin.jar
	DriverClassname = com.mysql.jdbc.Driver
	DatabaseURL = jdbc:mysql:///BirdLists
	DatabaseUser = Twitcher
	DatabasePassword = tiktiktik
	SQLQuery = SELECT Species.CommonName \
			FROM Species, ListMembers \
			WHERE ListMembers.ListNo = 4 \
				AND ListMembers.SpecNo = Species.SpecNo

The configuration is complete.