public JbirdDB(String path,
    String uname,
    String password,
    ResourceBundle pr,
    String driver) throws SQLException 
{
                 
       nformatter.setGroupingUsed(false);
       if (uname == null) {
       		jbirdconn = DriverManager.getConnection(path);
	} else {
       		jbirdconn = DriverManager.getConnection(path,
			uname, password);
	}
       progres = pr;
       driver_name = driver;
	//SchemaUpdate updater = new SchemaUpdate(this, pr); // WORK HERE - SchemaUpdate OFF
	//updater.update(); // WORK HERE - SchemaUpdate OFF
	//updater.nullify(); // WORK HERE - SchemaUpdate OFF
}

Throws SQLException if fails to open connection to the data base

 public final int addNewNote(String text) 
{
	System.out.println("addNewNote: enter");
	int maxnoteno = -1;
				// get new note number
	ResultSet rs = null;
	try {
	   rs = sqlQuery("SELECT MAX(NoteNo) FROM notes");
	   rs.first();
	   maxnoteno = rs.getInt(1);
	   if (rs.wasNull()) {
		maxnoteno = 1;
	   } else {
		maxnoteno ++;
	   }
	}
	catch (SQLException e) {
		unanticipatedError("addNewNote: get new number " + e);
		log.trace("addNewNote: " +
			progres.getString("add_notes_failed"));
		return -1;
	}
	finally {
		closeSqlQuery(rs);
	}
					// commit 
	PreparedStatement pstmt = null;
	try {
	   pstmt = jbirdconn.prepareStatement(
	   			"INSERT INTO notes (NoteNo, Notes) " +  
		   		"VALUES (?, ?)");
	   pstmt.setInt(1, maxnoteno);
	   pstmt.setString(2, text);
	   if (pstmt.executeUpdate() < = 0) {
		maxnoteno = -1;
	   }
	}
        catch (SQLException e) {
		unanticipatedError("addNewNote: " + e);
		log.trace("addNewNote: " +
			progres.getString("add_notes_failed"));
		return -1;
        }
	finally {
		if (pstmt != null) {
		   try {
			pstmt.close();
		   }
		   catch (SQLException e) {
		   }
		}
	}
	return maxnoteno;
}

Add a new note and return the value of NoteNo or -1 upon failure

 public int addNewObserver(String proposedname) 
{
 
	PreparedStatement pstmt = null;
	ResultSet queryresult = null;
	int rowcount = -1;
	int maxobservernumber = 0;
	proposedname = clipToMax(proposedname, OBSERVER_NAME_MAX_LEN);
				// check against existing observers
	try{
		pstmt = jbirdconn.prepareStatement(
	   			"SELECT COUNT(ObsNo) FROM observers " +
				"WHERE ObsName = ?");
		pstmt.setString(1, proposedname);
                queryresult = pstmt.executeQuery();
		queryresult.first();
                rowcount = queryresult.getInt(1);
                queryresult.close();
        }
	catch(SQLException e) {
		unanticipatedError("addNewObserver: check existing " +
					e.getMessage());
		return -2;
	}
	finally {
		try {
			if (queryresult != null) {
				queryresult.close();
			}
		}
		catch (SQLException e) {
		}
		try {
			if (pstmt != null) {
				pstmt.close();
			}
		}
		catch (SQLException e) {
		}
	}
	if (rowcount  > 0) {
		return -1;
	}
				// get new observer number
	try {
	   queryresult = sqlQuery("SELECT MAX(ObsNo) FROM observers");
	   queryresult.first();	
	   maxobservernumber = queryresult.getInt(1);
	}
	catch (SQLException e) {
		unanticipatedError("addNewObserver: get number " +
					e.getMessage());
		return -2;
	}
	finally {
		closeSqlQuery(queryresult);
	}
				// add new observer
	maxobservernumber ++;
	try {
	   pstmt = jbirdconn.prepareStatement(
			"INSERT INTO observers (ObsNo, ObsName) " + 
			"VALUES (?, ?)");
	   pstmt.setInt(1, maxobservernumber);
	   pstmt.setString(2, proposedname);
	   if (pstmt.executeUpdate() < = 0) {
		maxobservernumber = -2;
	   }
	   return maxobservernumber;
	}
	catch (SQLException e) {
		unanticipatedError("addNewObserver: commit " +
					e.getMessage());
		return -2;
	}
	finally {
	   try {
		if (pstmt != null) {
			pstmt.close();
	  	}
	   }
	   catch (SQLException e) {
	   }
	}
}

 public int addNewRegion(String proposedname) 
{
 
	ResultSet queryresult = null;
	int rowcount = -1;
	int maxregionnumber = 0;
	proposedname = clipToMax(proposedname, REGION_NAME_MAX_LEN);
			// check for region name
	PreparedStatement pstmt = null;
	try {
		pstmt =jbirdconn.prepareStatement(
			"SELECT COUNT(RegNo) FROM regions WHERE RegName = ?");
		pstmt.setString(1, proposedname);
		queryresult = pstmt.executeQuery();
		queryresult.first();
		rowcount = queryresult.getInt(1);
		queryresult.close();
	}
	catch (SQLException e) {
		unanticipatedError("addNewRegion: check existing " + 
					e.getMessage());
		return -2;
	}
	finally {
		try {
			if (queryresult != null) {
				queryresult.close();
			}
		}
		catch (SQLException e) {
		}
		try {
			if (pstmt != null) {
				pstmt.close();
			}
		}
		catch (SQLException e) {
		}
	}
	if (rowcount  > 0) {
		return -1;
	}
			// obtain new region number
	try {
	   queryresult = sqlQuery("SELECT MAX(RegNo) FROM regions");
	   queryresult.first();
	   maxregionnumber = queryresult.getInt(1);
	}
	catch (SQLException e) {
		unanticipatedError("addNewRegion: get number " + e.getMessage());
		return -2;
	}
	finally {
		closeSqlQuery(queryresult);
	}
			// add new region
	maxregionnumber ++;
	try {
		pstmt = jbirdconn.prepareStatement(
				"INSERT INTO regions " +
				"(RegNo, RegName) " +
				"VALUES (?, ?)");
	   	pstmt.setInt(1, maxregionnumber);
	   	pstmt.setString(2, proposedname);
		if(pstmt.executeUpdate() < = 0) {
			maxregionnumber = -2;
		}
		return maxregionnumber;
	}
	catch (SQLException e) {
		unanticipatedError("addNewRegion: commit " +
			e.getMessage());
	   return -2;
	}
	finally {
	  try {
	    if (pstmt != null) {
		pstmt.close();
	    }
	  }
	  catch (SQLException e) {
	  }
	}
}

Return region number if successful; Return -1 if region already exists; Return -2 on internal error;

 public int addNewTrip(int region,
    Date date,
    String locality,
    String keywords,
    String notes) 
{
	ResultSet queryresult = null;
	int rowcount = -1;
	int maxtripnumber = 0;
	locality = clipToMax(locality, LOCALITY_MAX_LEN);
	keywords = clipToMax(keywords, KEYWORDS_MAX_LEN);
				// check against existing trips
	PreparedStatement pstmt = null;
	try {
	   pstmt = jbirdconn.prepareStatement(
		"SELECT COUNT(TripNo) FROM trips WHERE " +
		"Region = ? AND Locality = ? AND ObsDate = ?");
	   pstmt.setInt(1, region);
	   pstmt.setString(2, locality);
	   pstmt.setDate(3, date);
	   queryresult = pstmt.executeQuery();
	   queryresult.first();
	   rowcount = queryresult.getInt(1);
	}
	catch (SQLException e) {
                unanticipatedError("addNewTrip: check existing " + 
                                        e.getMessage());
        }
	finally {
		try {
			if (queryresult != null) {
				queryresult.close();
			}
		}
		catch(SQLException e) {
		}
		try {
			if (pstmt != null) {
				pstmt.close();
			}
		}
		catch(SQLException e) {
		}
	}
	if (rowcount  > 0) {
		return -1;
	}
					// get new trip number
	try {
		queryresult = sqlQuery("SELECT MAX(TripNo) from trips");
		queryresult.first();	
		maxtripnumber = queryresult.getInt(1);
	}
	catch (SQLException e) {
                unanticipatedError("addNewTrip: trip number " + 
                                        e.getMessage());
		return -2;
	}
	finally {
		closeSqlQuery(queryresult);
	}
	maxtripnumber ++;
					// add the note
	try {
		jbirdconn.setAutoCommit(false);
	}
	catch (SQLException e) {
                unanticipatedError("addNewTrip: setAutoCommit(false) " + 
                                        e.getMessage());
		return -2;
	}
	int noteno = 0;
	if (notes != null) {
		noteno = addNewNote(notes);
		if (noteno <  1) {
			log.trace("addNewTrip: add note " + 
				progres.getString("add_trip_failed"));
			return -2;
		}
	}
					// add the trip record
	try {
		pstmt = jbirdconn.prepareStatement(
			"INSERT INTO trips (TripNo, Region, " +
			"ObsDate, Locality, KeyWords, NoteNo) " +
			"VALUES (?, ?, ?, ?, ?, ?)");
	   	pstmt.setInt(1, maxtripnumber);
	   	pstmt.setInt(2, region);
	   	pstmt.setDate(3, date);
	   	pstmt.setString(4, locality);
		if (keywords.length()  > 0) {
	   	   pstmt.setString(5, keywords);
		} else {
		   pstmt.setNull(5, java.sql.Types.VARCHAR);
		}
		if (noteno  > 0) {
	   	   pstmt.setInt(6, noteno);
		} else {
		   pstmt.setNull(6, java.sql.Types.INTEGER);
		}
	   	if (pstmt.executeUpdate() <  1) {
			maxtripnumber = -2;
		}
		jbirdconn.commit();
	}
	catch (SQLException e) {
                unanticipatedError("addNewTrip: add trip " + 
                                        e.getMessage());
		try {
			jbirdconn.rollback();
		}
		catch (SQLException e1) {
                	unanticipatedError("addNewTrip: rollback " + 
                                        e1.getMessage());
		}
	}
	finally {		// both of these should always run
		try {
			jbirdconn.setAutoCommit(true);
		}
		catch(SQLException e) {
                	unanticipatedError("addNewTrip: " +
					"setAutoCommit(true) "+ 
                                        e.getMessage());
		}
		try {
			if (pstmt != null) {
				pstmt.close();
			}
		}
		catch(SQLException e) {
		}
	}
	//if (maxtripnumber <  1 && noteno  > 0) {
		//deleteNote(noteno);
	//}
	return maxtripnumber;
}

 public int attachNoteToTrip(int noteno,
    int tripno) 
{
	String upd = new StringBuffer()
			.append("UPDATE trips SET NoteNo = ")
			.append(nformatter.format(noteno))
			.append(" WHERE TripNo = ")
			.append(nformatter.format(tripno))
			.toString();
	int mods;
	try {
	 	mods = sqlUpdate(upd);
	}
	catch (SQLException e) {
		unanticipatedError("attachNoteToTrip: " + e.getMessage());
		return -1;
	}
	return mods;
}

For a trip, set the value of NoteNo to that given Intended only for emergeny purposes such as when a the system fails to delete a note after that note has already been disassociated from the trip.

 public int changeIndexedString(String dbname,
    String idxvar,
    int idx,
    String stringvar,
    String newvalue) throws SQLException 
{
	String mysql = new StringBuffer()
			.append("UPDATE ")
			.append(dbname)
			.append(" SET ")
			.append(stringvar)
			.append(" = \'")
			.append(escapeQuotes(newvalue))
			.append("\' WHERE ")
			.append(idxvar)
			.append(" = ")
			.append(nformatter.format(idx))
			.toString();
	log.trace(mysql);
	Statement sqlstatement;
	sqlstatement = jbirdconn.createStatement();
	int result;
	try {
	   result = sqlstatement.executeUpdate(mysql);
	}
	catch (SQLException e) {
		unanticipatedError("changeIndexedString update:" +
			e.getMessage());
	   result = 0;
	}
	finally {
	   try {
	   	sqlstatement.close();
	   }
	   catch (SQLException e) {
		unanticipatedError("changeIndexedString close:" +
					e.getMessage());
	   }
	}
	return result;
}

 public static final String clipToMax(String value,
    int maxlen) 
{
      int valuelen = value.length();
      String answer = null;
      if (valuelen  > maxlen) {
	answer = value.substring(0, (maxlen - 1));
      } else {
	answer = value;
      }
      return answer;
}

 public  void close() 
{
      closePreparedStatements();
      try {
      jbirdconn.close();
      }
      catch (SQLException e1) {
       String msg = new StringBuffer() 
		.append(progres.getString("DB_close_fail"))
		.append(" ")
		.append(e1.getMessage())
		.toString();
       if (log == null) {
           System.out.println(msg);
       } else {
	   log.error(msg);
       }
      }
}

 public final  void closePreparedStatement(PreparedStatement ps) 
{
	if (ps != null) {
	   try {
		ps.close();
	   }
	   catch (Exception e) {
	   }
	}
}

Close an individual prepared statement without concern for exceptions thrown in the process. null values ok

 public final  void closePreparedStatements() 
{
	closePreparedStatement(pticktypestmt);
	pticktypestmt = null;
	closePreparedStatement(ptickspeciesstmt);
	ptickspeciesstmt = null;
	closePreparedStatement(pistickedstmt);
	pistickedstmt = null;
	// closePreparedStatement(paddtochecklist);
	// paddtochecklist = null;
	// closePreparedStatement(pchecklistcontains);
	// pchecklistcontains = null;
	closePreparedStatement(p_get_tick);
	p_get_tick = null;
	closePreparedStatement(p_update_tickcount);
	p_update_tickcount = null;
}

Close all open prepared statements JbirdDB author responsible for making sure that all prepared statements are processed here

 public final  void closeSqlQuery(ResultSet rs) 
{
	try {
		Statement stmt = rs.getStatement();
		rs.close();
		stmt.close();
	}
	catch (SQLException e3) {
		unanticipatedError("closeSqlQuery:" + 
			e3.getMessage());
	}
}

A method to close both a result set and the statement from which it was built.

Intended for use in methods that make use of the ResultSets that are returned by the sqlQuery method of this class.

The proper thing to do is probably to make a subclass of ResultSet that has a modified close method. Maybe someday.

 public final  void commit() throws SQLException 
{
	jbirdconn.commit();
}

 public final IntStringArrays commonNamesNums(int listno,
    boolean alphabetic) 
{
	return commonNamesNums(nformatter.format(listno), alphabetic);
}

Return a list of common names and species numbers.

 public final IntStringArrays commonNamesNums(String listno,
    boolean alphabetic) 
{
	IntStringArrays answer = null;
	if (alphabetic) {
		answer = commonNamesNumsAlphO(listno);
	} else {
		answer = commonNamesNumsTaxO(listno);
	}
	return answer;
}

Return a list of common names and species numbers. Note that listno is the list number represented as a string.

 public final IntStringArrays commonNamesNumsAlphO(String listno) 
{
	String bquery = new StringBuffer()
	   .append("SELECT commonnames.SpecNo, commonnames.Common," +
	           " commonnames.ListNo," +
		   " lower(commonnames.Common)" +
	           " FROM commonnames" +
	           " WHERE commonnames.ListNo = ") 
	   .append(listno)
	   .append(" ORDER BY lower(commonnames.Common)")
	   .toString();
	return queryForIntString(bquery, 1, 2);
}

Get list of species numbers and common names in alphabetic order

 public final IntStringArrays commonNamesNumsTaxO(String listno) 
{
		// WORK HERE.
		// It might be possible to keep temporary results here.
		// The idea is to build comquery on first go and
		// thereafter only when the selected common name
		// list changes.  (Note that this could be problematic
		// when it's possible to edit taxonomy. Possibly have
		// changes in taxonomy while out the state variable
		//
	String sqlquery = new StringBuffer()
		.append(
		      "SELECT commonnames.SpecNo, " +
			      "commonnames.Common, " +
			      "species.GlobalSort, " +
			      "species.GenNo, " +
			      "species.SpecNo " +
 	   	      "FROM commonnames, species " +
	   	      "WHERE commonnames.ListNo = ")
		.append(listno)
		.append(" AND commonnames.SpecNo = species.SpecNo " +
			"ORDER BY species.GlobalSort")
		.toString();
	return queryForIntString(sqlquery, 1, 2);
}

Get list of species numbers and common names in tradtional taxonomic order

 public final int countNoteRefs(int notenum) 
{
	int counta = countNoteRefs(notenum, "ticks");
	if (counta <  0) {
		return -1;
	}
	int countb = countNoteRefs(notenum, "trips");
	if (countb <  0) {
		return -1;
	}
	return counta + countb;
}

Count number of instances of a note in both tables trips and ticks.

Returns number of instances or -1 on failure.

 public final int countNoteRefs(int notenum,
    String table) 
{
	   String notequery = new StringBuffer()
				.append("SELECT COUNT(NoteNo) FROM ")
				.append(table) 
				.append(" WHERE NoteNo = ")
			        .append(nformatter.format(notenum))
				.toString();
	   int count = 0;
	   ResultSet queryresult = null;
	   try {
	   	queryresult = sqlQuery(notequery);
	   	queryresult.first();
	   	count = queryresult.getInt("COUNT(NoteNo)");
	    }
	    catch (SQLException e) {
		unanticipatedError("countNoteRefs: table " +
				   table + " note " +
				   notenum +
				   " - " + e.getMessage());
		return -1;
	    }
	    finally {
		if (queryresult != null) {
			closeSqlQuery(queryresult);
		}
	    }
	    return count;
}

Count references to a note in the given table (table may be trips, ticks or notes).

Returns number of instances or -1 on failure.

 public final ResultSet dateQuery(String query,
    Date[] dates) throws SQLException 
{
	//Debug.printStringScaled(query);
	if (dates == null || dates.length == 0) {
		return sqlQuery(query);
	} else {
		ResultSet answer = null;
		PreparedStatement pstmt =
			jbirdconn.prepareStatement(query);
		try {
			int max = dates.length;
			for (int idx = 0; idx <  max; idx ++ ) {
				pstmt.setDate((idx + 1), dates[idx]);
			}
			answer = pstmt.executeQuery();
		} 
		catch (SQLException e) {
			try {
				pstmt.close();
			}
			catch (SQLException e1) {
			}
			throw (e);
		}
		return answer;
	}
}

A method executing queries that contain date literals, which is an enormous headache. Why format a Date object into a variety of formats for different SQL engines so that the engines can convert them back to binary?

The query should be a PreparedStatement query in which all question marks represent dates. The number of dates in the array must match the number of question marks in the query. (No checking is done.

 public final int deleteNote(int[] notenums) 
{
	int tally = 0;
	int i;
	if (notenums != null) {
		for (i = 0; i <  notenums.length; i++) {
			tally += deleteNote(notenums[i]);
		}
	}
	return tally;
}

Delete an array of notes from the notes table if they are not referenced in the ticks table.

 public final int deleteNote(int notenum) 
{
			// query ticks table for presence of note
	   String whereclause = new StringBuffer()
			.append("where NoteNo = ")
			.append(nformatter.format(notenum))
			.toString();
	   int count = countNoteRefs(notenum);
	   if (count  >= 1) {	// exit if note is referenced
		return 0;
	   }
	   if (count <  0) {
		Object[] oba = new Object[1];
		oba[0] = new Integer(notenum);
		log.error(MessageFormat.format
			(progres.getString("note_count_failed"), oba));
		log.warning(MessageFormat.format
			(progres.getString("note_delete_failed"), oba));
		return -1;
	   }
				// really delete note
	   String deleter = "DELETE FROM notes " + whereclause;
	   int retcode = 0;
	   try {
		 retcode = sqlUpdate(deleter);
	   }
	   catch (SQLException e1) {
		unanticipatedError("deleteNote: " +
				e1.getMessage());
		Object[] oba = new Object[1];
		oba[0] = new Integer(notenum);
		log.warning(MessageFormat.format
			(progres.getString("note_delete_failed"), oba));
		return -1;
	   }
	   return retcode;
}

Delete a note from the notes table if it is not referenced by any observation in the ticks or trips tables

 public final int dumpTable(String tabname) throws IOException, SQLException 
{
	String filename = new StringBuffer().append(tabname)
				    .append(".csv")
				    .toString();
	int nrecs = dumpToFile(tabname, filename, ",", "\"");
	Object[] oba = { tabname, filename, nformatter.format(nrecs) };
	log.trace(MessageFormat.format(progres.getString("Dump_table"),
		oba));
	oba = null;
	return nrecs;
}

Dump table to file of comma separated values. Values containg commas will be quoted. Returns number of records written

 public final  void dumpTables() throws IOException, SQLException 
{
	//int rc = sqlUpdate("UPDATE ticks SET NoteNo = NULL WHERE NoteNo <  1");
	//System.out.println("ticks notes modified " + rc);
	//rc = sqlUpdate("UPDATE ticks SET BirdCount = NULL WHERE BirdCount <  1");
	//System.out.println("ticks birdcount modified " + rc);
	//rc = sqlUpdate("UPDATE trips SET NoteNo = NULL WHERE NoteNo <  1");
	//System.out.println("trips notes modified " + rc);
	dumpTable("checklistinfo");
	dumpTable("checklist");
	dumpTable("commonlists");
	dumpTable("regions");
	dumpTable("trips");
	dumpTable("observers");
	dumpTable("ticks");
	dumpTable("notes");
	dumpTable("commonnames");
	dumpTable("species");
	dumpTable("genera");
	dumpTable("families");
	//dumpTable("metadata");// WORK HERE - SchemaUpdate OFF
	//dumpTable("synonyms");// WORK HERE - SchemaUpdate OFF
	log.trace(progres.getString("Dump_complete"));
}

Dump all tables.

WORK HERE - this should report numbers of lines written to the log. dumpTable now returns those numbers.

 public final int dumpToFile(String tabname,
    String filename,
    String delim,
    String quote) throws IOException, SQLException 
{
	ResultSet rs = sqlQuery(new StringBuffer()
			   .append("SELECT * FROM ")
			   .append(tabname)
			   .toString());
	int rc = MyJDBC.dumpToFile(rs, filename, delim, quote);
	closeSqlQuery(rs);
	return rc;
}

Dump table to file using the specified delimiter. Values containg the delimiter will be quoted using the quote argument if the argument is not null. File io is buffered. Returns number of records written

 public final int extractInt(ResultSet rs) throws SQLException 
{
	rs.first();
	return rs.getInt(1);
}

Extract a single integer from column 1 row 1 of the result set. The ResultSet is not closed.

Useful for COUNT, MAX queries where the statement can be closed after the query.

 public final int extractIntClose(ResultSet rs) throws SQLException 
{
	int answer = -1;
	try {
		answer = extractInt(rs);
	} catch (SQLException sqle) {
		throw (sqle);
	}
	finally {
		closeSqlQuery(rs);
	}
	return answer;
}

Extract a single integer from column 1 row 1 of the result set, and close both the result set and its parent statement.

Useful for COUNT, MAX queries where the statement can be closed after the query.

 public final int extractSpeciesSet(ResultSet rs,
    SpeciesSet set) throws SQLException 
{
	return extractSpeciesSet(rs, set, true);
}

Extract species from a ResultSet and add them to the species set that is provided. The locations of items in the ResultSet is pretty picky.

Called by getChecklists and getTicked Species, which have been written to provide fields in identical order.

 public final int extractSpeciesSet(ResultSet rs,
    SpeciesSet set,
    boolean closequery) throws SQLException 
{
	int answer = 0;
	int[] indices = new int[8];
	indices[Species.SPECNO_IDX] = 3;
	indices[Species.SORTORDER_IDX] = 8;
	indices[Species.GENNO_IDX] = 4;
	indices[Species.FAMNO_IDX] = 1;
	indices[Species.EPITHET_IDX] = 6;
	indices[Species.GENUS_IDX] = 5;
	indices[Species.FAMILY_IDX] = 2;
	indices[Species.COMMON_IDX] = 7;
	String[] current = new String[2];
	current[0] = "";
	current[1] = "";
	while (rs.next()) {
		set.add(new Species(rs, indices, current));
		answer ++;
	}
	if (closequery) {
		closeSqlQuery(rs);
	}
	return answer;
}

 protected final ValueNLabel extractValueNLabel(ResultSet rs) throws SQLException 
{
	ValueNLabel data = null;
	ResultSetMetaData md = rs.getMetaData();
	int ncol = md.getColumnCount();
	md = null;
	rs.last();
	int nrows = rs.getRow();
	rs.beforeFirst();
	while(rs.next()) {
		Object value = rs.getObject(1);
		if (data == null) {
			Class value_class = value.getClass();
			if (value_class.equals(Integer.class)) {
				data = new IntegerValueNLabel(nrows);
			} else if (value_class.equals(String.class)) {
				data = new StringValueNLabel(nrows);
			} else if (value_class.equals(Date.class)) {
				data = new DateValueNLabel(nrows);
			}
		}
		int count = rs.getInt(ncol);
		if (ncol == 3) {
			String label = rs.getString(2);
			data.add(value, label, count);
		} else {
			data.add(value, count);
		}
	}
	return data;
}

This method is used to collect information on column and row headers from a preliminary query that extracts that information from a table. The idea is to run a query for column headers, use this method, run a query for row headers, use this method, run the cross tabulation query, and use buildTableData to harvest the results.

 public final ResultSet getAllSpecies(int commonlist) throws SQLException 
{
	String sclist = nformatter.format(commonlist);
	StringBuffer sqlprep = new StringBuffer(200)
		 .append("SELECT DISTINCT families.FamNo AS fFamNo,"
		 + " families.FamScientific AS fFamScientific,"
		 + " species.SpecNo AS sSpecNo,"
		 + " genera.GenNo AS gGenNo,"
		 + " genera.Genus AS gGenus,"
	         + " species.Species AS sSpecies,"
		 + " commonnames.Common AS cCommon,"
		 + " species.GlobalSort AS sGlobalSort"
		 + " FROM families, genera, species, commonnames"
	         + " WHERE "
		 + " genera.GenNo = species.GenNo"
		 + " AND families.FamNo = genera.FamNo"
		 + " AND commonnames.SpecNo = species.SpecNo"
		 + " AND commonnames.ListNo = ")
		 .append(sclist);
	String sql = sqlprep.toString();
	Debug.printStringScaled(sql);
	return sqlQuery(sql);
}

 public final int getAllSpecies(int commonlist,
    SpeciesSet set) throws SQLException 
{
	/* time spent
		sql query	94 %
		SpeciesSet	 5 %
	*/
	/*
	long[] times = new long[3];					// DEBUG
	String[] timenames = new String[] {	"BEGIN      ",		// DEBUG
						"sql query",		// DEBUG
						"Species Set" };	// DEBUG
	times[0] = System.currentTimeMillis();				// DEBUG
	ResultSet rs = getAllSpecies(commonlist);			// DEBUG
	times [1] = System.currentTimeMillis();				// DEBUG
	int answer = extractSpeciesSet(rs, set);			// DEBUG
	times [2] = System.currentTimeMillis();				// DEBUG
	System.out.println("JbirdDB.getAllSpecies");			// DEBUG
	Debug.dumpTimes(times, timenames);				// DEBUG
	return answer;							// DEBUG
	*/
	return extractSpeciesSet(getAllSpecies(commonlist), set);
}

 public IntStringArrays getAlphabeticalIntvarStringvar(String dbname,
    String idxvar,
    String stringvar,
    String extra) 
{
	try {
	   IntStringArrays answer = getIntvarStringvar(dbname,
						    idxvar,
						    stringvar,
						    extra);
	   // WORK HERE - do the sort!
	   if (answer == null) {
		answer = new IntStringArrays(null, null, false);
	   }
	   return answer;
	}
	catch (SQLException e) {
	   unanticipatedError("getAlphabeticalIntvarStringvar " +
			e.getMessage());
	   return null;
	}
}

Returns null if there was an error.
Returns an IntStringArrays object that contains nulls if the query returned no observations.

 public String[] getChecklistInfo(int listno) throws SQLException 
{
	return getChecklistInfo(nformatter.format(listno));
}

Return array of information about a checklist when the list number is given as an integer. See string version for details on the String array.

 public String[] getChecklistInfo(String strlistno) throws SQLException 
{
	String query = new StringBuffer()
			.append("SELECT ListNo, ListName, " +
				"Description, Source, Contributor, " +
				"Version FROM checklistinfo WHERE " +
				"ListNo = ")
			.append(strlistno)
			.toString();
	String[] answer = null;
	ResultSet rs = sqlQuery(query);
	try {
	   rs.beforeFirst();
	   if(rs.next()) {
		answer = new String[5];
		answer[ChecklistDB.LIST_NAME] = rs.getString("ListName");
		answer[ChecklistDB.LIST_DESCRIPTION] = rs.getString("Description");
		answer[ChecklistDB.LIST_SOURCE] = rs.getString("Source");
		answer[ChecklistDB.LIST_CONTRIBUTOR] = rs.getString("Contributor");
		answer[ChecklistDB.LIST_VERSION] = rs.getString("Version");
	   } else {		// no info on 
		Object[] oba = new Object[1];
		oba[0] = strlistno;
		log.warning(MessageFormat.format(
			progres.getString("no_checklist"), oba));
	   }
        }
        catch(SQLException e) {
		unanticipatedError("getCommonListInfo: " + 
				  e.getMessage());
		throw(e);
	}
	finally {
	   if (rs != null) {
		closeSqlQuery(rs);
	   }
	}
	return answer;
}

Return array of array of information about a commmon name list, when the list number is given as a string.

The string array returned contains in the following order Listname, Description, Source, Contributor, Version.

 public final IntStringArrays getChecklists() throws SQLException 
{
	String sql = "SELECT ListNo, ListName FROM checklistinfo" 
			+ " WHERE ListNo  > 0"
			+ " ORDER BY ListName";
	ResultSet rs = sqlQuery(sql);
	rs.last();
	int nrows = rs.getRow();
	rs.beforeFirst();
	IntStringArrays answer = new IntStringArrays(nrows);
	int idx = 0;
	while (rs.next()) {
		answer.inta[idx] = rs.getInt(1);
		if (rs.wasNull()) {
			answer.inta[idx] = -1;
		}
		answer.stringa[idx] = rs.getString(2);
		if (rs.wasNull()) {
			answer.stringa[idx] = "";
		}
		idx ++;
	}
	closeSqlQuery(rs);
	return answer;
}

Return an IntStringArray object that contains the checklists in alphabetical order and their primary keys.

 public final int getChecklists(int[] lists,
    int commonlist,
    SpeciesSet set) throws SQLException 
{
	/* Time spent
	   build sql	 0 %
	   execute sql	94 %
	   SpeciesSet	 8 %
	*/
	/*
	long[] times = new long[4];					// DEBUG
	String[] timenames = new String[] { "BEGIN      ",		// DEBUG
					    "build sql  ",		// DEBUG
					    "execute sql",		// DEBUG
					    "SpeciesSet " };		// DEBUG
	times[0] = System.currentTimeMillis();				// DEBUG
	*/
	String sclist = nformatter.format(commonlist);
	StringBuffer sqlprep = new StringBuffer(200)
		 .append("SELECT DISTINCT families.FamNo AS fFamNo,"
		 + " families.FamScientific AS fFamScientific,"
		 + " species.SpecNo AS sSpecNo,"
		 + " genera.GenNo AS gGenNo,"
		 + " genera.Genus AS gGenus,"
	         + " species.Species AS sSpecies,"
		 + " commonnames.Common AS cCommon,"
		 + " species.GlobalSort AS sGlobalSort"
		 + " FROM families, genera, species,"
		 + " commonnames,"
		 + " checklist"
	         + " WHERE checklist.ListNo IN ( ")
		 .append(joinIntArray(", ", lists))
		 .append(" ) AND checklist.SpecNo = species.SpecNo"
		 + " AND genera.GenNo = species.GenNo"
		 + " AND families.FamNo = genera.FamNo"
		 + " AND commonnames.SpecNo = checklist.SpecNo"
		 + " AND commonnames.ListNo = ")
		 .append(sclist);
	String sql = sqlprep.toString();
	Debug.printStringScaled(sql);					// DEBUG
	/*
	times[1] = System.currentTimeMillis();				// DEBUG
	ResultSet rs = sqlQuery(sql);					// DEBUG
	times[2] = System.currentTimeMillis();				// DEBUG
	int answer = extractSpeciesSet(rs, set);			// DEBUG
	times[3] = System.currentTimeMillis();				// DEBUG
	System.out.println("JbirdDB.getChecklists");			// DEBUG
	Debug.dumpTimes(times, timenames);				// DEBUG
	return answer;							// DEBUG
	*/
	return extractSpeciesSet(sqlQuery(sql), set);
}

Add species in the checklist to the set of species.

 public String[] getCommonListInfo(int listno) 
{
	return getCommonListInfo(nformatter.format(listno));
}

Return array of information about a common name list when the list number is given as an integer. See string version for details on the String array.

 public String[] getCommonListInfo(String strlistno) 
{
	String query = new StringBuffer()
			.append("SELECT ListNo, Encoding, ListName, "+
				"Description, Source, Contributor, " +
				"Version FROM commonlists WHERE " +
				"ListNo = ")
			.append(strlistno)
			.toString();
	String[] answer = null;
	ResultSet rs = null;
	try {
	   rs = sqlQuery(query);
	   rs.beforeFirst();
	   if(rs.next()) {
		answer = new String[6];
		answer[0] = rs.getString("Encoding");
		answer[1] = rs.getString("ListName");
		answer[2] = rs.getString("Description");
		answer[3] = rs.getString("Source");
		answer[4] = rs.getString("Contributor");
		answer[5] = rs.getString("Version");
	   } else {		// no info on 
		Object[] oba = new Object[1];
		oba[0] = strlistno;
		log.warning(MessageFormat.format(
			progres.getString("no_common_list"), oba));
	   }
        }
        catch(SQLException e) {
		unanticipatedError("getCommonListInfo: " + 
				  e.getMessage());
	}
	finally {
	   if (rs != null) {
	   	closeSqlQuery(rs);
	   }
	}
	return answer;
}

Return array of array of information about a commmon name list, when the list number is given as a string.

The string array returned contains in the following order Encoding, Listname, Description, Source, Contributor, Version

 public Object[] getCreditsInfo(int commonlist,
    int checklist) 
{
      Object[] answer = new Object[10];
      try {
	   String[] master = getChecklistInfo("0");
	   if (master == null) {
		answer = null;
		return null;
	   }
	   answer[0] = master[1];
	   answer[1] = master[2];
	   answer[2] = master[3];
	   master = null;
	   String[] common = getCommonListInfo(commonlist);
	   if (common == null) {
		answer = null;
		return null;
	   }
	   answer[3] = common[2];
	   answer[4] = common[3];
	   answer[5] = common[4];
	   common = null;
	   if (checklist  > 0) {
		String[] check = getChecklistInfo(checklist);
	   	if (check == null) {
			answer = null;
			return null;
	   	}
	   	answer[6] = check[1];
	   	answer[7] = check[2];
	   	answer[8] = check[3];
		check = null;
	   } else {
		answer[6] = answer[0];
		answer[7] = answer[1];
		answer[8] = answer[2];
           }
	 } catch (SQLException sqle) {
		// exception should be logged by getChecklistInfo.
		// this capture added J-Bird 0.3.0 may need to revisit
		// and revise.
		return null;
	 }
	return answer;
}

 public final int[] getFamilyGenus(int species) throws SQLException 
{
		int answer[] = new int[2];
		Object parm[] = new Object[1];
		parm[0] = new Integer(species);
		ResultSet rs = sqlQuery(
			"SELECT genera.FamNo, genera.GenNo FROM genera, species"
				+ " WHERE species.SpecNo = ?"
				+ " AND species.GenNo = genera.GenNo", 
				parm, 
				new int[] { java.sql.Types.INTEGER });
		try {
			rs.first();
			answer[0] = rs.getInt(1);
			if (rs.wasNull()) {
				answer[0] = -1;
			}
			answer[1] = rs.getInt(2);
			if (rs.wasNull()) {
				answer[1] = -1;
			}
		} catch (SQLException se) {
			throw(se);
		} finally {
			closeSqlQuery(rs);
		}
		return answer;
}

Return the family and genus numbers of a species.

 public String getIndexedString(String dbname,
    String idxvar,
    int idx,
    String stringvar) throws SQLException 
{
	ResultSet queryresult;
	String answer;
	if (idx <  0) {
		return "";
	}
	String mysql = new StringBuffer()
			.append("SELECT ")
			.append(stringvar)
			.append(" FROM ")
			.append(dbname)
			.append(" WHERE ")
			.append(idxvar)
			.append(" IN (")
			.append(nformatter.format(idx))
			.append(")")
			.toString();
	queryresult = sqlQuery(mysql);
	queryresult.beforeFirst();
	if (queryresult.next()) {
		answer = queryresult.getString(stringvar);
	} else {
		answer = "";
	}
	closeSqlQuery(queryresult);
	return answer;
}

 public IntStringArrays getIntvarStringvar(String dbname,
    String idxvar,
    String stringvar) throws SQLException 
{
	return getIntvarStringvar(dbname, idxvar, stringvar, "");
}

 public IntStringArrays getIntvarStringvar(String dbname,
    String idxvar,
    String stringvar,
    String extra) throws SQLException 
{
	int[] intresult = null;
	String[] stringresult = null;
	ResultSet queryresult;
	int rowcount;
	String mysql = new StringBuffer()
			.append("SELECT ")
		        .append(idxvar)
			.append(", ")
		        .append(stringvar)
		        .append(" FROM ")
		        .append(dbname)
			.append(" ")
		        .append(extra)
			.toString();
	log.trace("Query - " + mysql);
	queryresult = sqlQuery(mysql);
        queryresult.last();
        rowcount = queryresult.getRow();
	if (rowcount <  1) {	// nothing matched query
		return null;
	}
        intresult = new int[rowcount];
        stringresult= new String[rowcount];
        queryresult.beforeFirst();
        int i = 0;
        while (queryresult.next()) {
                intresult[i] = queryresult.getInt(idxvar);
                stringresult[i] = queryresult.getString(stringvar);
                i ++;
        }
	closeSqlQuery(queryresult);
	IntStringArrays results = new IntStringArrays(intresult, 
						     stringresult,
						     false);
	return results;
}

Returns null if nothing matches query.

 public final LogListener getLogListener() 
{
		return log;
}

 public ResultSet getNoteInfo(int trip,
    int[] observers,
    int species) 
{
	String[] obsa = MyStringArray.formatIntArray(observers, null);
	String obss = MyStringArray.join(", ", obsa);
	obsa = null;
	String query = new StringBuffer()
		.append("SELECT SpecNo, Observer, NoteNo FROM ")
		.append("ticks WHERE ")
		.append("SpecNo = ")
		.append(nformatter.format(species))
		.append(" AND TripNo = ")
		.append(nformatter.format(trip))
		.append(" AND Observer IN (")
		.append(obss)
		.append(")")
		.toString();
	ResultSet answer = null;
	try {
		answer = sqlQuery(query);
	}
	catch (SQLException e) {
		unanticipatedError("getNoteInfo: " + e.getMessage());
	}
	return answer;
}

Return a result set that includes the note index for each observer that observed a species on a trip.

 public final String getNoteText(int noteno) 
{
	String query = new StringBuffer()
		.append("SELECT Notes FROM notes where NoteNo = ")
		.append(nformatter.format(noteno))
		.toString();
	ResultSet rs = null;
	String answer = null;
	try {
		rs = sqlQuery(query);
		rs.first();
		answer = rs.getString("Notes");
        }
	catch (SQLException e) {
		unanticipatedError("getNoteText: " + e.getMessage());
		try {
			rs.close();
		}
		catch (SQLException e1) {
		}
		return null;
	}	
	finally {
		if (rs != null) {
			closeSqlQuery(rs);
		}
	}
	return answer;
}

Return the text of the indicated note, or null on failure

 public String[] getObserverNames(int[] obsnos) 
{
 
	System.out.println("getObserverNames: enter - undebugged function!");
				// generate comma delimited set of observers
	String sobsnos = MyStringArray.join(", ", 
			    MyStringArray.formatIntArray(obsnos, nformatter));
				// build query and get result set
	String query = new StringBuffer()
			.append("SELECT ObsNo, ObsName FROM observers where " +
			        "ObsNo IN ( ")
			.append(sobsnos)
			.append(" )")
			.toString();
	ResultSet resultset = null;
	try {
		resultset = sqlQuery(query);
	}
	catch(SQLException e) {
		unanticipatedError("getObserverNames: sqlQuery" + 
				e.getMessage());
		return null;
	}
				// extract answer
	String[] answer = new String[obsnos.length];
	int i;
	try {
		resultset.beforeFirst();
		while (resultset.next()) {
			int thisno = resultset.getInt(1);
			i = 0;
			while (i <  obsnos.length && thisno != obsnos[i] ) {
				i++;
			}
			if (i <  obsnos.length) {
				answer[i] = resultset.getString(2);
			}
		}
	}
	catch (SQLException e) {
		unanticipatedError("getObserverNames: process ResultSet " + 
				e.getMessage());
	}
	finally {
		try {
		resultset.close();
		}
		catch (SQLException e1) {
		    unanticipatedError("getObserverNames: closing ResultSet " + 
				e1.getMessage());
		}
	}
			// fill missing values with empty strings
	for(i = 0; i <  obsnos.length; i++) {
		if (answer[i] == null) {
			answer[i] = "";
		}
	}
	return answer;
}

Return the names of the observers in the same order

 public final ArrayList getObserverNotes(int obsno) throws SQLException 
{
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	ArrayList al = new ArrayList(20);
	try {
		pstmt = jbirdconn.prepareStatement(
			"SELECT NoteNo FROM ticks WHERE NoteNo  > 0 AND " +
				"Observer = ?");
		pstmt.setInt(1, obsno);
		rs = pstmt.executeQuery();
		while(rs.next()) {
			int noteno = rs.getInt(1);
			if (! rs.wasNull()) {
				al.add(new Integer(noteno));
			}
		}
	}
	finally {
		if (rs != null) {
			try {
				rs.close();
			}
			catch (SQLException e) {
			}
		}
		if (pstmt != null) {
			try {
				pstmt.close();
			}
			catch (SQLException e) {
			}
		}
	}
	return al;
}

 public int getObserverNumber(String observer) throws SQLException 
{
	ResultSet queryresult;
	int answer = -1;
	if (observer == null || observer.length() == 0) {
		return answer;
	}
	String mysql = "SELECT ObsNo FROM observers WHERE ObsName = ?";
	Object[] parms = new Object[1];
	parms[0] = observer;
	int[] parmtypes = new int[1];
	parmtypes[0] = java.sql.Types.VARCHAR;
	queryresult = sqlQuery(mysql, parms, parmtypes);
	queryresult.beforeFirst();
	if (queryresult.next()) {
		answer = queryresult.getInt("ObsNo");
	}
	closeSqlQuery(queryresult);
	return answer;
}

Return number that corresponds to observer. Returns -1 if observer not found.

 public final int[] getObservers(int tripno) 
{
	String tripnumber = nformatter.format(tripno);
	return getObservers(tripnumber);
}

 public final int[] getObservers(String tripno) 
{
	String query = new StringBuffer()
		.append("SELECT DISTINCT Observer FROM ticks WHERE TripNo = ")
		.append(tripno)
		.toString();
	ResultSet resultset;
	try {
		resultset = sqlQuery(query);
	}
	catch (SQLException e) {
		unanticipatedError(e.getMessage());
		return null;
	}
	int[] ids = null;
	try {
		resultset.last();
		int rowcount = resultset.getRow();
		resultset.beforeFirst();
		ids = new int[rowcount];
		int i = 0;
		while (resultset.next()) {
			ids[i] = resultset.getInt("Observer");
			i ++;
		}
	}
	catch (SQLException e) {
		unanticipatedError(e.getMessage());
		ids = null;
	}
	finally {
		closeSqlQuery(resultset);
	}
	return ids;
}

 public int getStringIndex(String dbname,
    String idxvar,
    String stringvar,
    String stringval) 
{
	ResultSet queryresult = null;
	int answer;
	if (stringval.compareTo("") == 0) {
		return -2;
	}
	String mysql = new StringBuffer()
			.append("SELECT ")
			.append(idxvar)
			.append(" FROM ")
			.append(dbname)
			.append(" WHERE ")
			.append(stringvar)
			.append(" = \'")
			.append(escapeQuotes(stringval))
			.append("\'")
			.toString();
	try {
	   queryresult = sqlQuery(mysql);
	   queryresult.beforeFirst();
	   if (queryresult.next()) {
		   answer = queryresult.getInt(idxvar);
	   } else {
		   answer = -1;
	   }
	}
	catch (SQLException e) {
	   String message = new StringBuffer()
			.append("getStringIndex: failed on table ")
			.append(dbname)
			.append(" looking up value of ")
			.append(idxvar)
			.append(" for ")
			.append(stringvar)
			.append(" = ")
			.append(stringval)
			.append(" ")
			.append(e.getMessage())
			.toString();
	   unanticipatedError(message);
	   return -2;
	}
	finally {
		if (queryresult != null) {
			closeSqlQuery(queryresult);
		}
	}
	return answer;
}

 public final Tick getTick(Tick info) throws SQLException 
{
	int tripno = info.tick_info[Tick.TRIP_IDX];
	int observerno = info.tick_info[Tick.OBSERVER_IDX];
	int speciesno = info.tick_info[Tick.SPECIES_IDX];
	if (tripno <  0 || observerno <  0 || speciesno <  0) {
		return null;
	}
	if (p_get_tick == null) {
		p_get_tick = jbirdconn.prepareStatement(
			"SELECT TripNo, Observer, SpecNo, BirdCount, NoteNo "
			   + "FROM ticks "
			   + "WHERE TripNo = ? AND Observer = ? AND SpecNo = ?");
	}
	p_get_tick.clearParameters();
	p_get_tick.setInt(1, tripno);
	p_get_tick.setInt(2, observerno);
	p_get_tick.setInt(3, speciesno);
	ResultSet rs = p_get_tick.executeQuery();
	try {
		if (rs.next()) {
			info.tick_info[Tick.COUNT_IDX] = rs.getInt("BirdCount");
			if (rs.wasNull()) {
				info.tick_info[Tick.COUNT_IDX] = Tick.MISSING;
			}
			info.tick_info[Tick.NOTE_IDX] = rs.getInt("NoteNo");
			if (rs.wasNull()) {
				info.tick_info[Tick.NOTE_IDX] = Tick.MISSING;
			}
		} else {
			info = null;
		}
	} catch (SQLException sqle) {
			throw(sqle);
	} finally {
		closeSqlQuery(rs);
	}
	return info;
}

Fill in the count and note fields of the tick provided. Returns null if the the tick is not completely specified (trip, observer, or species < 0) or if the species has not been ticked by the observer on the given trip. Throws all SQLExceptions. The bird count and note fields will have the value Tick.MISSING if they are null in the returned record.

No effort is made to check for duplicate observations. The first returned by the database query is returned by the method.

 public Date getTripDate(int tripno) 
{
	String querystring = new StringBuffer()
		.append("SELECT TripNo, ObsDate FROM trips " +
			"WHERE TripNo = ")
		.append(nformatter
		.format(tripno))
		.toString();
	ResultSet resultset = null;
	Date answer = null;
	try {
	  	resultset = sqlQuery(querystring);
	  	while(resultset.next()) {
			answer = resultset.getDate(2);
		}
	}
	catch (SQLException e) {
	  unanticipatedError("getTripDate: " + e.getMessage());
	  return null;
	}
	finally {
		if (resultset != null) {
			closeSqlQuery(resultset);
		}
	}
	return answer;
}

 public TripInfo getTripInfo(int tripno) 
{
	String querystring = new StringBuffer()
		.append("SELECT TripNo, Region, ObsDate, Locality, KeyWords, " +
			"NoteNo FROM trips WHERE TripNo = ")
		.append(nformatter
		.format(tripno))
		.toString();
	ResultSet resultset = null;
	try {
	  resultset = sqlQuery(querystring);
	}
	catch (SQLException e) {
	  unanticipatedError("getTripInfo " + e.getMessage());
	  return null;
	}
	TripInfo tripinfo = new TripInfo();
	try {
	   resultset.last();
	   int rowcount = resultset.getRow();
	   if (rowcount <  1) {
	   	return null;
	   }
				// assume OK to bypass methods of TripInfo
	   resultset.first();
	   tripinfo.tripno = resultset.getInt("TripNo");
	   tripinfo.region = resultset.getInt("Region");
	   tripinfo.date = resultset.getDate("ObsDate");
	   tripinfo.locality = resultset.getString("Locality");
	   tripinfo.keywords = resultset.getString("KeyWords");
	   if (tripinfo.keywords == null) {
		tripinfo.keywords = "";
	   }
	   tripinfo.noteno = resultset.getInt("NoteNo");
	   if (resultset.wasNull()) {
		tripinfo.noteno = 0;
	   }
	}
	catch (SQLException e) {
	   unanticipatedError("getTripInfo: "+ e.getMessage());
	}
	finally {
		closeSqlQuery(resultset);
	}
	if (tripinfo.noteno  > 0) {
		tripinfo.notes = getNoteText(tripinfo.noteno);
		//if (tripinfo.notes == null) {
			// WORK HERE - what to do in case of missing
			// 		notes
		//}
	}
	return tripinfo;
}

 public final ArrayList getTripNoteNums(String triplist) throws SQLException 
{
	ArrayList listar = new ArrayList(20);
	int notecount = 0;
			// get trip notes
	ResultSet rs = null;
	String sql;
	try {
		sql = new StringBuffer()
			.append("SELECT NoteNo from trips WHERE TripNo in (")
			.append(triplist)
			.append(") AND NoteNo  > 0")
			.toString();
		rs = sqlQuery(sql);
		notecount = intFieldIntoArrayList(rs, 1, listar);
	}
	finally {
		closeSqlQuery(rs);
	}
	rs = null;
	try {
	   sql = new StringBuffer()
			.append("SELECT DISTINCT NoteNo from ticks ")
			.append("WHERE TripNo in (")
			.append(triplist)
			.append(") AND NoteNo  > 0")
			.toString();
	   rs = sqlQuery(sql);
	   notecount += intFieldIntoArrayList(rs, 1, listar);
	}
	finally {
		closeSqlQuery(rs);
	}
	return listar;
}

 public IntStringArrays getTripsInRegion(int region) 
{
	int rowcount;
	int[] tripids = null;
	String[] availablechoices = null;
        String querystring = 
	    "SELECT TripNo, ObsDate, Locality FROM trips WHERE Region = " +
                nformatter.format(region) + " ORDER BY ObsDate, Locality";
	ResultSet resultset = null;
	try {
           resultset = sqlQuery(querystring);
           resultset.last();
           rowcount = resultset.getRow();
           tripids = new int[rowcount];
           availablechoices = new String[rowcount];
	   resultset.beforeFirst();
           int i = 0;
           while (resultset.next()) {
              tripids[i] = resultset.getInt("TripNo");
              String tripname = resultset.getString("Locality");
              Date numericdate = resultset.getDate("ObsDate");
              availablechoices[i] = numericdate.toString() + " " + 
					tripname;
              i++;
           }
	}
	catch(SQLException e) {
		unanticipatedError("getTripsInRegion " + 
				   e.getMessage());
		return null;
	}
	finally {
	   try {
	       resultset.close();
	   }
	   catch (SQLException e) {
	   }
	}
	return new IntStringArrays(tripids, availablechoices, false);
}

 public final String getUniqueSQL(String key) 
{
	UniqueSQL hash = null;
	if (unique_sql_ref != null) {
		hash = (UniqueSQL)unique_sql_ref.get();
	}
	if (hash == null) {
		hash = UniqueSQL.load(driver_name, true);
		unique_sql_ref = new WeakReference(hash);
	}
	String answer = (String)hash.get(key);
	return answer;
}

Retrieve an entry from the store of sql that is unique to the driver in use.

Such sql is assumed to be used infrequently. Only a weak reference is maintained to it by JbirdDB.

 public final ArrayList getUnsharedObsNoteNums(int obsno) throws SQLException 
{
				// get Observer's notes
	ArrayList alnotes = getObserverNotes(obsno);
	if (alnotes == null) {
		System.out.println("getUnsharedObsNoteNums: alnotes is null");
	}
	String stnotes = joinIntArrayList(", ", alnotes);
	if (stnotes == null) {
		return null;
	}
	alnotes.clear();
				// Determine which notes are unique by
				// counting references to each note. Unique
				// notes should be referenced only once.
	String sql = new StringBuffer()
			.append("SELECT NoteNo, COUNT(NoteNo) FROM ticks ")
			.append("WHERE NoteNo IN (")
			.append(stnotes)
			.append(") GROUP BY NoteNo")
			.toString();
	ResultSet rs = null;
	try {
		rs = sqlQuery(sql);
		while(rs.next()) {
			int count = rs.getInt(2);
			if (count == 1) {
				int noteno = rs.getInt(1);
				alnotes.add(new Integer(noteno));
			}
		}
	}
	catch (SQLException e) {
		System.out.println("WORKHERE EXCEPTION: " + e.getMessage());
	}
	finally {
		if (rs != null) {
			closeSqlQuery(rs);
		}
	}
	return alnotes;
}

 public static int intFieldIntoArrayList(ResultSet rs,
    int fieldno,
    ArrayList la) throws SQLException 
{
	int answer = 0;
	while (rs.next()) {
		int aval = rs.getInt(fieldno);
		if (!rs.wasNull()) {
			la.add(new Integer(aval));
			answer ++;
		}
	}
	return answer;
}

Extract integer field from a result set and store values in a ArrayList that is provided in the function call.

Return the number of values added to the ArrayList.

This method does not close either the ResultSet.

 public boolean isTicked(int species,
    int trip,
    int observer) 
{
	boolean answer = false;
	ResultSet rs = null;
	try {
		if (pistickedstmt == null) {
			 pistickedstmt = jbirdconn.prepareStatement(
				"SELECT COUNT(SpecNo) AS count FROM ticks WHERE "
					+ " TripNo = ?"
					+ " AND Observer = ?"
					+ " AND SpecNo = ?");
		}
		pistickedstmt.clearParameters();
		pistickedstmt.setInt(1, trip);
		pistickedstmt.setInt(2, observer);
		pistickedstmt.setInt(3, species);
		rs = pistickedstmt.executeQuery();
	   	rs.beforeFirst();
	   	while(rs.next()) {
			int acount = rs.getInt("count");
			if (acount  > 0) {
				answer = true;
			}
		}
		rs.close();
		rs = null;
	}
	catch (SQLException e) {
		unanticipatedError("itTicked - " + e.getMessage());
		if (rs != null) {
			try {
				rs.close();
			}
			catch (SQLException e1) {
			}
		}
	}
	return answer;
}

Determine whether a species has been ticked by an observer on a trip.

In the event of an exception, prints to log and returns false without further incident.

 public final String joinIntArray(String delim,
    int[] ia) 
{
 
	if (ia == null || ia.length == 0) {
		return null;
	}
	int max = ia.length;
	StringBuffer sb = new StringBuffer(max * 4);
	sb.append(nformatter.format(ia[0]));
	for (int i = 1; i <  max; i++) {
		sb.append(delim);
		sb.append(nformatter.format(ia[i]));
	}
	return sb.toString();
}

Format integer array as a string with elements delimited as indicated.

 public final String joinIntArrayList(String delim,
    ArrayList al) 
{
 
	if (al == null || al.isEmpty()) {
		return null;
	}
	int max = al.size();
	StringBuffer sb = new StringBuffer(max * 4);
	Integer intob = (Integer)al.get(0);
	sb.append(nformatter.format(intob.intValue()));
	for (int i = 1; i <  max; i++) {
		sb.append(delim);
		intob = (Integer)al.get(i);
		sb.append(nformatter.format(intob.intValue()));
	}
	return sb.toString();
}

 public int linkSpeciesNote(int tripno,
    int specno,
    int[] observers,
    int noteno) 
{
	String[] obsa = MyStringArray.formatIntArray(observers, null);
	String obss = MyStringArray.join(", ", obsa);
	String mysql = new StringBuffer()
			.append("UPDATE ticks SET NoteNo = ")
			.append(nformatter.format(noteno))
			.append(" WHERE TripNo = ")
			.append(nformatter.format(tripno))
			.append(" AND SpecNo = ")
			.append(nformatter.format(specno))
			.append(" AND Observer IN (")
			.append(obss)
			.append(")")
			.toString();
	int retcode = 0;
	try {
		retcode = sqlUpdate(mysql);
	}
	catch (SQLException e1) {
		unanticipatedError("linkNote: " + e1.getMessage());
	}
	return retcode;
}

Associate a note with observers on a trip.

 public int moveAllTripsToRegion(int srcregion,
    int destregion) 
{
	String srcstring = nformatter.format(srcregion);
	String deststring = nformatter.format(destregion);
	String sqlstmt = new StringBuffer()
		.append("UPDATE trips SET Region = ")
		.append(deststring)
		.append(" WHERE Region = ")
		.append(srcstring)
		.toString();
	try {
		return sqlUpdate(sqlstmt);
	}
	catch (SQLException e) {
		unanticipatedError("moveAllTripsToRegion " + 
			e.getMessage());
		return -1;
	}
}

 public int moveTripTicksToTrip(int srctrip,
    int desttrip) 
{
	String source = nformatter.format(srctrip);
	String dest = nformatter.format(desttrip);
		// Locate ticks shared in common by two trips and
		// remove them from the source trip
	int[] observers = getObservers(source);
	if (observers == null) {
		return -1;
	}
	int max = observers.length;
	int i;
	String update;
	int rc = -1;
	int changed = 0;
	try {
		jbirdconn.setAutoCommit(false);
	}
	catch (SQLException e) {
		 unanticipatedError("moveTripTicksToTrip: " +
			"setAutoCommit(false) "+ e.getMessage());
		 return -1;
	}
			// delete duplicates
	for (i = 0; i <  max; i++) {
		String observer = nformatter.format(observers[i]);
		update = new StringBuffer()
			.append("DELETE FROM ticks WHERE TripNo = ")
			.append(source)
			.append(" AND Observer = ")
			.append(observer)
			.append(" AND SpecNo IN (SELECT DISTINCT SpecNo FROM ticks WHERE TripNo = ")
			.append(dest)
			.append(" AND Observer = ")
			.append(observer)
			.append(")")
			.toString();
		try {
		 rc = sqlUpdate(update);
		}
		catch (SQLException e) {
		 unanticipatedError("moveTripTicksToTrip: observers "+
			e.getMessage());
		 try {
			jbirdconn.rollback();
		 }
		 catch (SQLException e1) {
		    unanticipatedError("moveTripTicksToTrip: rollback 1 "+
			e1.getMessage());
		 }
		 try {
			jbirdconn.setAutoCommit(true);
		 }
		 catch (SQLException e2) {
		    unanticipatedError("moveTripTicksToTrip: " +
			"setAutCommit(true) 1 " +
			e2.getMessage());
		 }
		 return -1;
		}
		changed += rc;
	}
	if (rc  > 0) {
	   Object[] oba = new Object[1];
	   oba[0] = new Integer(rc);
	   log.trace(MessageFormat.format
		(progres.getString("Remove_duplicate_ticks"), oba));
	   rc = 0;
	}
	// Reassign ticks in source trip to dest trip
	update = new StringBuffer()
		.append("UPDATE ticks SET TripNo = ")
		.append(dest)
		.append(" WHERE TripNo = ")
		.append(source)
		.toString();
	try {
		rc = sqlUpdate(update);
		rc += removeTripsNTicks(srctrip);
		jbirdconn.commit();
	}
	catch (SQLException e) {
		unanticipatedError("moveTripTicksToTrip: moving " +
		e.getMessage());
		try { 
			jbirdconn.rollback();
		}
		catch(SQLException e1) {
			unanticipatedError("moveTripTicksToTrip: " +
				"rollback 2 " +
				e.getMessage());
		}
		return -1;
	}
	finally {
		try {
			jbirdconn.setAutoCommit(true);
		}
		catch (SQLException e) {
			unanticipatedError("moveTripTicksToTrip: " +
				"setAutoCommit(true) 2 " +
				e.getMessage());
		}
	}
	return rc;
}

ALSO REMOVES srctrip.

 public int moveTripsToRegion(String region,
    String triplist) 
{
	String sqlstmt = new StringBuffer()
			  .append("UPDATE trips SET Region = ")
			  .append(region)
			  .append(" WHERE TripNo IN (")
			  .append(triplist)
			  .append(")")
			  .toString();
	try {
		return sqlUpdate(sqlstmt);
	}
	catch (SQLException e) {
		unanticipatedError("moveTripsToRegion " +
				e.getMessage());
		return -1;
	}
}

 public  void oneParmMsg(int level,
    String resname,
    String message) 
{
	Object[] oba = new Object[1];
	oba[0] = message;
	log.level(level, 
	      MessageFormat.format(progres.getString(resname), 
	      oba));
}

 public final IntStringArrays queryForIntString(String sqlquery,
    int intcol,
    int stringcol) 
{
	ResultSet rs = null;
				// try to open result set
	try {
		rs = sqlQuery(sqlquery);
	}
	catch (SQLException e) {
		unanticipatedError(e.getMessage());
		return null;
	}
	System.out.println("queryForIntString: GOT RESULTSET");
				// opened ResultSet closed by finally
	try {
		return MyJDBC.getIntString(rs, intcol, stringcol);
	}
	catch (SQLException e) {
		unanticipatedError(e.getMessage());
		return null;
	}
				// close result set before returning
	finally {
	  if (rs != null) {
		closeSqlQuery(rs);
	  }
	}
}

Perform the sql query indicated and return integer and string arrays of the columns indicated by intcol and stringcol.

Returns null on error or empty ResultSet.

 public String regionName(int regionnumber) throws SQLException 
{
	ResultSet queryresult;
	String answer = null;
	if (regionnumber <  0) {
		return "";
	}
	String mysql = "SELECT RegName FROM regions WHERE RegNo = " +
			nformatter.format(regionnumber);
	queryresult = sqlQuery(mysql);
	queryresult.beforeFirst();
	if (queryresult.next()) {
		answer = queryresult.getString("RegName");
	} else {
		answer = "";
	}
	closeSqlQuery(queryresult);
	return answer;
}

 public int regionNumber(String regionname) throws SQLException 
{
	ResultSet queryresult;
	int answer = -1;
	if (regionname == null || regionname.length() == 0) {
		return answer;
	}
	String mysql = "SELECT RegNo FROM regions WHERE RegName = ?";
	Object[] parms = new Object[1];
	parms[0] = regionname;
	int[] parmtypes = new int[1];
	parmtypes[0] = java.sql.Types.VARCHAR;
	System.out.println("regionName " + regionname + " "  + java.sql.Types.VARCHAR);
	queryresult = sqlQuery(mysql, parms, parmtypes);
	queryresult.beforeFirst();
	if (queryresult.next()) {
		answer = queryresult.getInt("RegNo");
	}
	closeSqlQuery(queryresult);
	return answer;
}

Return number that corresponds to region name. Returns -1 if region not found.

 public int removeObserver(int observer) 
{
	String formatted = nformatter.format(observer);
	ArrayList alnotes = null;
	try {
		alnotes = getUnsharedObsNoteNums(observer);
	}
	catch (SQLException e) {
		unanticipatedError("removeObserver: getUnsharedObsNoteNums" + 
			e.getMessage());
		return -1;
	}
	String stnotes = joinIntArrayList(", ", alnotes);
	System.out.println("removeObserver notes to go " + stnotes);
	alnotes = null;
	int arraylen;
	if (stnotes != null) {
		arraylen = 3;
	} else {
		arraylen = 2;
	}
	String[] sqlstmt = new String[arraylen];
 	sqlstmt[0] = new StringBuffer()
		.append("DELETE FROM ticks WHERE Observer = ")
		.append(formatted)
		.toString();
 	sqlstmt[1] = new StringBuffer()
		.append("DELETE FROM observers WHERE ObsNo = ")
		.append(formatted)
		.toString();
	if (arraylen == 3) {
		sqlstmt[2] = new StringBuffer()
		.append("DELETE FROM notes WHERE NoteNo IN (")
		.append(stnotes)
		.append(")")
		.toString();
	}
	try {
		return sqlUpdateAsOne(sqlstmt);
	}
	catch (SQLException e) {
		unanticipatedError("removeObserver: sqlUpdate " + 
			e.getMessage());
		return -1;
	}
}

 public int removeRegion(int region) 
{
	IntStringArrays tripisa = getTripsInRegion(region);
	if (tripisa == null) {
	   return -1;
	}
	int changed = 0;
	if (tripisa.inta != null) {
           if (tripisa.inta.length  > 0) {
		changed = removeTripsNTicks(tripisa.inta);
		if (changed <  0) {
			return -1;
		}
	   }
	}
 	String sqlstmt = new StringBuffer()
			.append("DELETE FROM regions WHERE RegNo = ")
			.append(nformatter.format(region))
			.toString();
	int rc = 0;
	try {
	    rc = sqlUpdate(sqlstmt);
	}
	catch (SQLException e) {
	    unanticipatedError("removeRegion " + 
				e.getMessage());
	    return -1;
	}
	if (rc  > 0) {
		changed += rc;
	}
	return changed;
}

 public int removeTripsNTicks(String triplist) 
{
	ArrayList la = null;
	try {
		la = getTripNoteNums(triplist);
	}
	catch(SQLException e) {
		unanticipatedError("removeTripsNTicks: getTripNoteNums " +
			e.getMessage());
		return -1;
	}
	String notelist = joinIntArrayList(", ", la);
	la = null;
	String whereclause = " WHERE TripNo IN (" +
			     triplist +
			     ")";
	int arraylen;
	if (notelist != null) {
		arraylen = 3;
	} else {
		arraylen = 2;
	}
        String[] stmts = new String[arraylen];
	stmts[0] = "DELETE FROM ticks " + whereclause;
	stmts[1] = "DELETE FROM trips " + whereclause;
	int rc;
	if (arraylen == 3) {
		stmts[2] = "DELETE FROM notes WHERE NoteNo IN (" +
			notelist  + 
			")";
	}
	try {
		if (jbirdconn.getAutoCommit()) {
			rc = sqlUpdateAsOne(stmts);
		} else {
			rc = sqlUpdate(stmts);
		}
	}
	catch(SQLException e) {
		unanticipatedError("removeTripsNTicks: sqlUpdate " +
			e.getMessage());
		rc = -1;
	}
	return rc;
}

Remove trips listed, including ticks and notes.

Triplist should be a ", " delimited list of trips to delete.

If auto commit is on, changes effected by this methods will be performed as a single transaction, (i.e., auto commit will be turned off, the transactions will be committed, and autocommit will be turned back on.

 public int removeTripsNTicks(int[] triplist) 
{
	String[] tripa = MyStringArray.formatIntArray(triplist, null);
	String trips = MyStringArray.join(", ", tripa);
	return removeTripsNTicks(trips);
}

 public int removeTripsNTicks(int tripno) 
{
	String trip = nformatter.format(tripno);
	return removeTripsNTicks(trip);
}

 public int renameObserver(int lobsnum,
    String lnewname) 
{
	lnewname = clipToMax(lnewname, OBSERVER_NAME_MAX_LEN);
	log.trace("renameObserver: renaming to " + lnewname);
	int check = getStringIndex("observers", "ObsNo", "ObsName", lnewname);
	if (check  > -1) {		// already exists
   		twoParmMsg(5, "Already_exists", 
			progres.getString("Observer"), 
			lnewname);
		return -1;
	}
	if (check <  -2) {		// problem in initial check
		log.trace("renameObserver: internal error");
		return -2;
	}
	int result;
	try {
	    result = changeIndexedString("observers", "ObsNo", lobsnum, 
						    "ObsName", lnewname);
	}
	catch (SQLException e) {
		unanticipatedError("renameObserver " +
			e.getMessage());
	   result = -2;
	}
	if (result <  0) {
	   result = -2;
	}
        return result;
}

 public int renameRegion(int lregnum,
    String lnewname) 
{
	lnewname = clipToMax(lnewname, REGION_NAME_MAX_LEN);
	int check = getStringIndex("regions", "RegNo", "RegName", lnewname);
	if (check  > -1) {		// already exists
		return -1;
	}
	if (check <  -2) {		// problem in initial check
		return -2;
	}
	int result;
	try {
	    result = changeIndexedString("regions", "RegNo", lregnum, 
						    "RegName", lnewname);
	}
	catch (SQLException e) {
	   unanticipatedError("renameRegion " + e.getMessage());
	   result = -2;
	}
	if (result <  0) {
	   result = -2;
	}
        return result;
}

 public final  void rollback() throws SQLException 
{
	jbirdconn.rollback();
}

 public final  void setAutoCommit(boolean to) throws SQLException 
{
	jbirdconn.setAutoCommit(to);
}

 public final  void setLogListener(LogListener logger) 
{
	log = logger;
}

 public final int singleIntQuery(String query,
    Object[] objects,
    int[] types) throws SQLException 
{
	int answer = -1;
	return extractIntClose(sqlQuery(query, objects, types));
}

Return the integer result of query that returns an a result set that contains a single observation with a single integer field. This method can be used for more complex result sets, but will return the integer value in the first column of the first row of the result set.

Closes ResultSet AND parent Statement!

 public final ResultSet sqlQuery(String query) throws SQLException 
{
	ResultSet queryresult;
	Statement sqlstatement;
	sqlstatement = jbirdconn.createStatement();
	long before;		// DEBUG
	int dur;		// DEBUG;
	before = System.currentTimeMillis();
	try {
		queryresult = sqlstatement.executeQuery(query);
			 	// DEBUG
		dur = (int) ((System.currentTimeMillis() - before) / 1000);
		System.out.println(nformatter.format(dur) + " sec " + query);
	} catch (SQLException sqle) {	// close Statement and rethrow
		try {
			sqlstatement.close();
		}
		catch (SQLException sqleb) {
		}
		throw (sqle);
	}
	return queryresult;
}

 public final ResultSet sqlQuery(String[] querya) throws SQLException 
{
	long before;		// DEBUG
	int dur;		// DEBUG;
	ResultSet queryresult;
	Statement sqlstatement;
	sqlstatement = jbirdconn.createStatement();
	int lastq = querya.length - 1;
	int i;
	for (i = 0; i <  lastq; i++) {
	    before = System.currentTimeMillis();
	    sqlstatement.executeUpdate(querya[i]);
	    dur = (int) ((System.currentTimeMillis() - before) / 
			1000);  // DEBUG
	    System.out.println(nformatter.format(dur) +
			" sec " + querya[i]); // DEBUG
	}
	    before = System.currentTimeMillis();
	queryresult = sqlstatement.executeQuery(querya[i]);
	    dur = (int) ((System.currentTimeMillis() - before) / 
			1000);  // DEBUG
	    System.out.println(nformatter.format(dur) +
			" sec " + querya[i]);  // DEBUG
	return queryresult;
}

 public final ResultSet sqlQuery(String query,
    Object[] objects,
    int[] types) throws SQLException 
{
	if (objects == null || objects.length == 0) {
		return sqlQuery(query);
	} else {
		ResultSet answer = null;
		PreparedStatement pstmt =
			jbirdconn.prepareStatement(query);
		try {
			int max = objects.length;
			for (int idx = 0; idx <  max; idx ++ ) {
				pstmt.setObject((idx + 1), objects[idx],
						types[idx]);
			}
			answer = pstmt.executeQuery();
		} 
		catch (SQLException e) {
			try {
				pstmt.close();
			}
			catch (SQLException e1) {
			}
			throw (e);
		}
		return answer;
	}
}

A query for avoiding all formatting of literals for the query. Pass a query suitable for a preparedStatement, an array of objects along with an array of java.sql.Types that describes the nature of the objects for substitution. Array lengths must match the number question marks in the query.

 public int sqlUpdate(String query) throws SQLException 
{
	Statement sqlstatement = jbirdconn.createStatement();
	long before;		// DEBUG
	int dur;		// DEBUG;
	    before = System.currentTimeMillis();
	int answer;
	try {
		answer = sqlstatement.executeUpdate(query);
	    	dur = (int) ((System.currentTimeMillis() - before) / 
			1000);  // DEBUG
	    	System.out.println(nformatter.format(dur) +
			" sec " + query); // DEBUG
	} catch (SQLException sqle) {
		throw(sqle);
	} finally {
		try {
			sqlstatement.close();
		} catch (SQLException sqleb) {
		}
	}
	return answer;
}

 public int sqlUpdate(String[] querya) throws SQLException 
{
	long before;		// DEBUG
	int dur;		// DEBUG;
	Statement sqlstatement = null;
        int mods = 0;
	try {
	   sqlstatement = jbirdconn.createStatement();
	   int lastq = querya.length;
	   int i;
	   for (i = 0; i <  lastq; i++) {
	       if (log != null ) {
	       log.trace("sqlUpdate: " + querya[i]);
	       }
	       before = System.currentTimeMillis(); // DEBUG
	       mods += sqlstatement.executeUpdate(querya[i]);
	       dur = (int) ((System.currentTimeMillis() - before) / 
			   1000);  // DEBUG
	       System.out.println(nformatter.format(dur) +
			   " sec " + querya[i]);  // DEBUG
	   }
	}
	catch (SQLException e) {
		throw(e);
	}
	finally {
		if (sqlstatement != null) {
	  		sqlstatement.close();
		}
	}
	return mods;
}

 public final int sqlUpdate(String query,
    Object[] objects,
    int[] types) throws SQLException 
{
	if (objects == null || objects.length == 0) {
		return sqlUpdate(query);
	} else {
		int answer = 0;
		PreparedStatement pstmt =
			jbirdconn.prepareStatement(query);
		try {
			int max = objects.length;
			for (int idx = 0; idx <  max; idx ++ ) {
				System.out.println(idx + " " +
					 objects[idx] + " " +
					 types[idx]);
				pstmt.setObject((idx + 1), objects[idx],
						types[idx]);
			}
			answer = pstmt.executeUpdate();
		} 
		catch (SQLException e) {
			throw (e);
		}
		finally {
			pstmt.close();
		}
		return answer;
	}
}

An update query for avoiding all formatting of literals for the query. Pass a query suitable for a preparedStatement, an array of objects along with an array of java.sql.Types that describes the nature of the objects for substitution. Array lengths must match the number question marks in the query.

 public int sqlUpdateAsOne(String[] querya) throws SQLException 
{
	long before;		// DEBUG
	int dur;		// DEBUG;
	Statement sqlstatement = null;
        int mods = 0;
	try {
	   jbirdconn.setAutoCommit(false);
	   sqlstatement = jbirdconn.createStatement();
	   int lastq = querya.length;
	   int i;
	   for (i = 0; i <  lastq; i++) {
	       if (log != null ) {
	       log.trace("sqlUpdate: " + querya[i]);
	       }
	       before = System.currentTimeMillis(); // DEBUG
	       mods += sqlstatement.executeUpdate(querya[i]);
	       dur = (int) ((System.currentTimeMillis() - before) / 
			   1000);  // DEBUG
	       System.out.println(nformatter.format(dur) +
			   " sec " + querya[i]);  // DEBUG
	   }
	   jbirdconn.commit();
	}
	catch (SQLException e) {
		jbirdconn.rollback();
		throw(e);
	}
	finally {
		if (sqlstatement != null) {
	  		sqlstatement.close();
		}
		jbirdconn.setAutoCommit(true);
	}
	return mods;
}

Like sqlUpdate, but uses transaction processing. Sets auto commit to false before processing the argument. Commits, rolls back and sets to auto commit to true after processing.

 public final int tickSpecies(int trip,
    int observer,
    int species) 
{
	return tickSpecies(trip, observer, species, 0);
}

 public final int tickSpecies(int trip,
    int observer,
    int species,
    int count) 
{
	int retcode = -1;
	try {
	   if (ptickspeciesstmt == null) {
		ptickspeciesstmt = jbirdconn.prepareStatement(
	   		"INSERT INTO ticks (SpecNo, TripNo, Observer, " +
			"NoteNo, BirdCount) " +
	   		"VALUES ( ?, ?, ?, ?, ?)");
			// parm 1 integer species number
			// parm 2 integer trip number
			// parm 3 integer observer number
			// parm 4 integer note number (always null)
			// parm 5 integer bird count
	   }
	   ptickspeciesstmt.clearParameters();
	   ptickspeciesstmt.setInt(1, species);
	   ptickspeciesstmt.setInt(2, trip);
	   ptickspeciesstmt.setInt(3, observer);
	   ptickspeciesstmt.setNull(4, java.sql.Types.INTEGER);
	   if (count  > 0) {
	   	ptickspeciesstmt.setInt(5, count);
	   } else {
	   	ptickspeciesstmt.setNull(5, java.sql.Types.INTEGER);
	   }
	   retcode = ptickspeciesstmt.executeUpdate();
	}
	catch (SQLException e1) {
		unanticipatedError("tickSpecies " +
				e1.getMessage());
	}
	return retcode;
}

 public int tickType(int region,
    int trip,
    Date tripdate,
    int observer,
    int species) 
{
	int answer = TICK_LIFE;
	ResultSet resultset = null;
	try {
	   if (pticktypestmt == null) {
			pticktypestmt = jbirdconn.prepareStatement(
			"SELECT ticks.TripNo, trips.Region, trips.ObsDate " +
		        "FROM ticks, trips WHERE ticks.TripNo = trips.TripNo " +
		        "AND ticks.Observer = ?  AND ticks.SpecNo = ? " +
			"AND trips.ObsDate < = ?");
			// parm 1 integer observer number
			// parm 2 integer species number
			// parm 3 Date    trip date
	   }
	   pticktypestmt.clearParameters();
	   pticktypestmt.setInt(1, observer);
	   pticktypestmt.setInt(2, species);
	   pticktypestmt.setDate(3, tripdate);
	   resultset = pticktypestmt.executeQuery();
	   resultset.beforeFirst();
	   while(resultset.next()) {
		if (answer == TICK_LIFE) {		//  seen somewhere
		   answer = TICK_REGION;
		}
		int qtrip = resultset.getInt("ticks.TripNo");
		int qregion = resultset.getInt("trips.Region");
		if (answer == TICK_REGION && qregion == region) {// seen in region
		   answer = TICK_TRIP;
		}
		if (qtrip == trip) {			// seen on trip
		   answer = TICK_ALREADY;
		}
	   }
	}
	catch (SQLException e1) {
		unanticipatedError("tickType - " + e1.getMessage());
		answer = INTERNAL_ERROR;
	}
	finally {
	   if (resultset != null) {
		try {
		   resultset.close();
		}
		catch(Exception e2) {
		}
	   }
	}
	return answer;
}

Returns one of INTERNAL_ERROR, TICK_ALREADY, TICK_TRIP, TICK_REGION, TICK_LIFE.

 public final String toCondition(int[] ia) 
{
	String answer = null;
	if (ia.length == 1) {
		answer = new StringBuffer(10)
				.append("= ")
		   		.append(nformatter.format(ia[0]))
				.toString();
	} else {
		answer = new StringBuffer(120)
			.append("IN ( ")
			.append(joinIntArray(", ", ia))
			.append(" )")
			.toString();
	}
	return answer;
}

Return a string that contains an SQL condition operator and what follows it for a condition. If the length of ia is 1, return "= value_of_ia[0]". If the length of ia is greater than 1 return an IN condition such as "IN ( value_of_ia[0], ... )"

 public final String toCondition(String[] sa) 
{
	String answer = null;
	sa = MyStringArray.quoteArray(sa, "'");
	if (sa.length == 1) {
		answer = new StringBuffer(10)
				.append("= ")
		   		.append(sa[0])
				.toString();
	} else {
		answer = new StringBuffer(120)
			.append("IN ( ")
			.append(MyStringArray.join(", ", sa))
			.append(" )")
			.toString();
	}
	return answer;
}

Return a string that contains an SQL condition operator and what follows it for a condition. If the length of ia is 1, return "= value_of_ia[0]". If the length of ia is greater than 1 return an IN condition such as "IN ( value_of_ia[0], ... )" Note that this method quotes non-destructively quotes the elements of sa for inclusion in the condition.

 public  void unanticipatedError(String msg) 
{
	Object[] oba = new Object[1];
	oba[0] = msg;
	log.error(MessageFormat.format(
		progres.getString("Unanticipated_error"), 
		oba));
}

 public final int unlinkNDeleteSpeciesNote(int tripno,
    int specno,
    int[] observers) 
{
	int[] notelist = unlinkSpeciesNote(tripno, specno, observers);
	int answer = deleteNote(notelist);
	return answer;
}

Unlink and delete species notes for the observers, species, and trip indicated.

Returns the number of notes actually deleted.

 public final int[] unlinkSpeciesNote(int tripno,
    int specno,
    int[] observers) 
{
	String[] obsa = MyStringArray.formatIntArray(observers, null);
	String obss = MyStringArray.join(", ", obsa);
					// build array of note numbers
	String mysql = new StringBuffer()
			.append("SELECT NoteNo from ticks ")
			.append(" WHERE TripNo = ")
			.append(nformatter.format(tripno))
			.append(" AND SpecNo = ")
			.append(nformatter.format(specno))
			.append(" AND Observer IN (")
			.append(obss)
			.append(")")
			.toString();
	int retcode = 0;
	ResultSet rs = null;
	try {
		rs = sqlQuery(mysql);
	}
	catch (SQLException e1) {
		unanticipatedError("unlinkNote query: " + e1.getMessage());
		return null;
	}
		// too much hassle to build an AbstractSet and doo
		// all the conversions.  Just build an array, check
		// against it, then build an array of the correct
		// size to return.
	int[] checknumbers = new int[observers.length];
	int at = -1;
	int i;
	try {
	   while (rs.next()) {
		int noteno = rs.getInt(1);
		if (! rs.wasNull()) {
					// add to list of notes
			boolean found = false;
			for (i = 0; i < = at; i++) {
			    if (noteno == checknumbers[i]) {
				found = true;
			    }
			}
			if (! found) {
				at ++;
				checknumbers[at] = noteno;
			}
		}			// end - add to list of notes
	   }
	}
	catch (SQLException e2) {
		unanticipatedError("unlinkNote build list: " + e2.getMessage());
		return null;
	}
	finally {
		closeSqlQuery(rs);
	}
	int[] answer = null;
	if (at  >= 0) {
		answer = new int[(at+1)];
		for (i = 0; i < = at; i++) {
			answer[i] = checknumbers[i];
		}
	}
	checknumbers = null;
						// wipe out notes entries
	mysql = new StringBuffer()
			.append("UPDATE ticks SET NoteNo = NULL ")
			.append(" WHERE TripNo = ")
			.append(nformatter.format(tripno))
			.append(" AND SpecNo = ")
			.append(nformatter.format(specno))
			.append(" AND Observer IN (")
			.append(obss)
			.append(") AND NoteNo  > 0")
			.toString();
	try {
	   sqlUpdate(mysql);
	}
	catch (SQLException e) {
		unanticipatedError("unlinkNote update notes: " + e.getMessage());
		return null;
	}
	return answer;
}

Disassociate a note from the indicated observers in the indicated trip.

Returns an integer array that contains identification numbers of notes that were unlinked.

The note will not be deleted from the notes table. Use deleteNote to do that after all associations have been broken.

 public final int untickSpecies(int trip,
    int observer,
    int species) 
{
	int[] observers = new int[1];
	observers[0] = observer;
	try {
		jbirdconn.setAutoCommit(false);
	}
	catch (SQLException e) {
		unanticipatedError("untickSpecies: setAutoCommit(false) " +
				e.getMessage());
		return -1;
	}
   	unlinkNDeleteSpeciesNote(trip, species, observers);
	observers = null;
	String sqlcommand = new StringBuffer()
			.append("DELETE FROM ticks where TripNo = ")
			.append(nformatter.format(trip))
			.append(" AND SpecNo = ")
			.append(nformatter.format(species))
			.append(" AND Observer = ")
			.append(nformatter.format(observer))
			.toString();
	Statement stmt =