B4J Programming Press on the image to return to the main documentation page.

Jackcess

Written by Andrew Graham

This library is based on Jackcess, a pure Java library for reading from and writing to MS Access databases.
Presently Access database versions 2000-2010 are supported and Access 97 read-only.
It will obviously help to have some knowledge of MS Access in order to understand it.
Jackcess does not support SQL queries so all database manipulations are done procedurally.

Documentation for Jackcess is rather limited but the Javadocs for Jackcess can be found here.
http://jackcess.sourceforge.net/apidocs/index.html

This library depends on commons-lang-2.6.jar and jackcess-1.2.10.jar. It also requires several files to be present
in a myproject\res\raw folder. Those files may be copied from the demo in the archive and must be made read-only.

Jackcess and therefore also this library is licensed under the GNU Lesser General Public License.
http://www.gnu.org/licenses/lgpl.html
Copies of both the General Public License and Lesser General Public License are in the provided archive.

Copyright 2013 Anywhere Software

List of types:

JackcessCursor
JackcessDatabase
JackcessIndex
JackcessIndexCursor
JackcessTable

JackcessCursor

A cursor provides navigation of a table.

Events:

None

Members:


  DeleteCurrentRow

  FindFirstRow (column As String, value As Object) As Boolean

  FindNextRow (column As String, value As Object) As Boolean

  GetColumnValue (columnname As String) As Object

  GetCursor As com.healthmarketscience.jackcess.Cursor

  GetNextRow

  GetPreviousRow

  Initialize (table As com.healthmarketscience.jackcess.Table)

  IsAfterLast As Boolean

  IsBeforeFirst As Boolean

  Reset

  SetCurrentRowValue (column As String, value As Object)

  UpdateCurrentRow (values() As Object)

Members description:

DeleteCurrentRow
Delete the current row. Throws IllegalStateException if the current row is not valid
(at beginning or end of table), or already deleted
FindFirstRow (column As String, value As Object) As Boolean
Moves to the first row (as defined by the cursor) where the given column has the given value.
If a match is not found (or an exception is thrown), the cursor is restored to its previous state.
Warning, this method always starts searching from the beginning of the Table (you cannot use it to find successive matches).
FindNextRow (column As String, value As Object) As Boolean
Moves to the next row (as defined by the cursor) where the given column has the given value.
If a match is not found (or an exception is thrown), the cursor is restored to its previous state.
GetColumnValue (columnname As String) As Object
Most Access data types map to types directly supported by Basic4android and can be returned as is.
Only dates need special treatment to turn them into ticks for the Basic4android DateTime object.
Rather than put a check for a Date in every call to this method it is incumbent on the programmer
to pass the Date object to the JackcessDatabase.DateToTicks helper method.
GetCursor As com.healthmarketscience.jackcess.Cursor
Returns the internal wrapped Cursor object.
GetNextRow
Reads the next row into an internal Map containing a set of Column name (String) and value (Object) pairs.
The values of individual columns in the row may be obtained by GetColumnValue or GetColumnValueAndDate
GetPreviousRow
Reads the previous row into an internal Map containing a set of Column name (String) and value (Object) pairs.
The values of individual columns in the row may be obtained by GetColumnValue or GetColumnValueAndDate
Initialize (table As com.healthmarketscience.jackcess.Table)
Initialises this object with a table provided by JackcessDatabase.GetTable or JackcessTable.GetTable.
IsAfterLast As Boolean
Returns true if the cursor is currently positioned after the last row, false otherwise
IsBeforeFirst As Boolean
Returns true if the cursor is currently positioned before the first row, false otherwise.
Reset
After calling this method, GetNextRow will return the first row in the table,
SetCurrentRowValue (column As String, value As Object)
Updates a single value in the current row.
Any Date values should be passed as ticks to the Jackcess.DateFromTicks helper method
and the returned Date object used here.
UpdateCurrentRow (values() As Object)
Update the current row with the provided values.
Any Date values should be passed as ticks to the Jackcess.DateFromTicks helper method
and the returned Date object used here.

JackcessDatabase

The JackcessDatabase object represents an Access database.
Once opened the tables in the database may be passed to the Initialize method of a JackcessTable object.

Events:

None

Members:


  Close

  DateFromTicks (ticks As Long) As java.util.Date

  DateToTicks (date As java.util.Date) As Long

  Details As String

  Flush

  GetFileFormat As String

  GetSystemTable (systemtablename As String) As com.healthmarketscience.jackcess.Table

  GetSystemTableNames As String()

  GetTable (table As String) As com.healthmarketscience.jackcess.Table

  GetTableNames As String()

  IsOpen As Boolean

  Open (filename As String)

  Version As Double [read only]

Members description:

Close
Closes the presently opened Access database.
DateFromTicks (ticks As Long) As java.util.Date
Helper method to turn a ticks value into the Java Date type required for a database date field.
DateToTicks (date As java.util.Date) As Long
Helper method to turn the Java Date type returned from a database date field into a ticks value.
Details As String
Returns a multiline String containing potentially useful details of the Database.
Flush
Flushes any current changes to the database file (and any linked databases) to disk.
GetFileFormat As String
Returns the file format of this database as one of MSISAM, V1997, V2000, V2003, V2007 or V2010.
GetSystemTable (systemtablename As String) As com.healthmarketscience.jackcess.Table
Returns a reference to any available table in this access database, including system tables.
Warning, this method is not designed for common use, only for the occassional time when access to
a system table is necessary.
GetSystemTableNames As String()
Returns a String array containing the names of all the system tables in the database.
GetTable (table As String) As com.healthmarketscience.jackcess.Table
Returns a Table for the database for use in Table.Initialize.
GetTableNames As String()
returns a String array containing the names of all the user tables in the database.
IsOpen As Boolean
Returns True if a database is open.
Open (filename As String)
Opens the specified Access database.
Version As Double [read only]
Returns the version of the library.

JackcessIndex


Events:

None

Members:


  Details As String

  GetColumnNames As String()

  GetIndex As com.healthmarketscience.jackcess.Index

  Initialize (index As com.healthmarketscience.jackcess.Index)

  IsForeignKey As Boolean

  IsPrimaryKey As Boolean

  IsUnique As Boolean

Members description:

Details As String
Returns a string containing potentially useful details of the Index.
GetColumnNames As String()
Returns the names of each of the columns in the index as a String array.
GetIndex As com.healthmarketscience.jackcess.Index
Returns the internal wrapped Index object.
Initialize (index As com.healthmarketscience.jackcess.Index)
Initialises this object with a database index provided by JackcessTable.GetIndex.
IsForeignKey As Boolean
Returns true if the index is a foreign key.
i.e. it is a field in the table that is a primary key in another table
IsPrimaryKey As Boolean
Returns True if the index is a primary key.
i.e. it is a field whose values uniquely identify each record in that table
IsUnique As Boolean
Returns True if the index entries must be unique values.
Some notes about uniqueness:
Access does not seem to consider multiple null entries invalid for a unique index.
Text indexes collapse case, and Access seems to compare only the index entry bytes,
therefore two strings which differ only in case will violate the unique constraint.

JackcessIndexCursor

An index cursor provides efficient navigation of a table using a predefined index column or columns.

Events:

None

Members:


  DeleteCurrentRow

  FindClosestRowByEntry (entryvalues() As Object)

  FindFirstRow (column As String, value As Object) As Boolean

  FindFirstRowByEntry (entryvalues() As Object) As Boolean

  FindNextRow (column As String, value As Object) As Boolean

  GetColumnValue (columnname As String) As Object

  GetCursor As com.healthmarketscience.jackcess.Cursor

  GetNextRow

  GetPreviousRow

  Initialize (table As com.healthmarketscience.jackcess.Table, index As com.healthmarketscience.jackcess.Index)

  IsAfterLast As Boolean

  IsBeforeFirst As Boolean

  Reset

  SetCurrentRowValue (column As String, value As Object)

  UpdateCurrentRow (values() As Object)

Members description:

DeleteCurrentRow
Delete the current row. Throws IllegalStateException if the current row is not valid
(at beginning or end of table), or already deleted
FindClosestRowByEntry (entryvalues() As Object)
Moves to the first row (as defined by the cursor) where the index entries are >= the given values.
If an exception is thrown, the cursor is restored to its previous state.

entryValues is an array with the column values for the index's columns.
FindFirstRow (column As String, value As Object) As Boolean
Moves to the first row (as defined by the cursor) where the given column has the given value.
If a match is not found (or an exception is thrown), the cursor is restored to its previous state.
Warning, this method always starts searching from the beginning of the Table (you cannot use it to find successive matches).
FindFirstRowByEntry (entryvalues() As Object) As Boolean
Moves to the first row (as defined by the cursor) where the index entries match the given values.
If a match is not found (or an exception is thrown), the cursor is restored to its previous state.
Warning, this method always starts searching from the beginning of the Table (you cannot use it to find successive matches).
If the entry is found the row is read into an internal Map containing a set of Column name (String) and value (Object) pairs.
The values of individual columns in the row may be obtained by GetColumnValue or GetColumnValueAndDate
entryValues is an array with the column values for the index's columns.
FindNextRow (column As String, value As Object) As Boolean
Moves to the next row (as defined by the cursor) where the given column has the given value.
If a match is not found (or an exception is thrown), the cursor is restored to its previous state.
GetColumnValue (columnname As String) As Object
Most Access data types map to types directly supported by Basic4android and can be returned as is.
Only dates need special treatment to turn them into ticks for the Basic4android DateTime object.
Rather than put a check for a Date in every call to this method it is incumbent on the programmer
to pass the Date object to the JackcessDatabase.DateToTicks helper method.
GetCursor As com.healthmarketscience.jackcess.Cursor
Returns the internal wrapped Cursor object.
GetNextRow
Reads the next row into an internal Map containing a set of Column name (String) and value (Object) pairs.
The values of individual columns in the row may be obtained by GetColumnValue or GetColumnValueAndDate
GetPreviousRow
Reads the previous row into an internal Map containing a set of Column name (String) and value (Object) pairs.
The values of individual columns in the row may be obtained by GetColumnValue or GetColumnValueAndDate
Initialize (table As com.healthmarketscience.jackcess.Table, index As com.healthmarketscience.jackcess.Index)
Initialises this object with a table provided by JackcessDatabase.GetTable or JackcessTable.GetTable
and a database index provided by JackcessTable.GetIndex or JackcessIndex.GetIndex.
IsAfterLast As Boolean
Returns true if the cursor is currently positioned after the last row, false otherwise
IsBeforeFirst As Boolean
Returns true if the cursor is currently positioned before the first row, false otherwise.
Reset
After calling this method, GetNextRow will return the first row in the table,
SetCurrentRowValue (column As String, value As Object)
Updates a single value in the current row.
Any Date values should be passed as ticks to the Jackcess.DateFromTicks helper method
and the returned Date object used here.
UpdateCurrentRow (values() As Object)
Update the current row with the provided values.
Any Date values should be passed as ticks to the Jackcess.DateFromTicks helper method
and the returned Date object used here.

JackcessTable

The JackcessTable object represent a table within the open database.

Events:

None

Members:


  AddRow (values() As Object)

  AddRows (values As java.util.List)

  DeleteCurrentRow

  Details As String

  Display As String

  GetColumnDataTypes As String()

  GetColumnJavaTypes As String()

  GetColumnNames As String()

  GetColumnValue (columnname As String) As Object

  GetIndex (indexname As String) As com.healthmarketscience.jackcess.Index

  GetIndexNames As String()

  GetNextRow

  GetRowCount As Int

  GetTable As com.healthmarketscience.jackcess.Table

  Initialize (table As com.healthmarketscience.jackcess.Table)

  Reset

  UpdateCurrentRow (values() As Object)

Members description:

AddRow (values() As Object)
Adds a single row to this table and writes it to disk.
The values are expected to be given in the order that the Columns are listed by getColumns() method.
values are the row values for a single row. The given row array will be modified if this table contains
an auto-number column, otherwise it will not be modified.
Any Date values should be passed as ticks to the Jackcess.DateFromTicks helper method
and the returned Date object used here.
AddRows (values As java.util.List)
Add multiple rows to this table, only writing to disk after all rows have been written,
and every time a data page is filled. This is much more efficient than calling addRow multiple times.
Note, if this table has an auto-number column, the values written will be put back into the given row arrays.
Any Date values should be passed as ticks to the Jackcess.DateFromTicks helper method
and the returned Date object used here.
DeleteCurrentRow
Delete the current row (retrieved by a call to GetNextRow).
Details As String
Returns a string containing potentially useful details of the Table.
Display As String
Returns a string representation of the entire table in tab-delimited format.
GetColumnDataTypes As String()
Returns the Access data type of each of the columns in the table as a String array.
GetColumnJavaTypes As String()
Returns the Java data type of each of the columns in the table as a String array.
Note that the Date type needs special treatment when read from the database.
There are two helper methods, DateToTicks and DatefromTicks in JackcessDatabase to amp
database Date type values to and from ticks for use in Basic4android.
Access types map to Java types as follows.

BINARY: Byte[] of max length 255 bytes
BOOLEAN: Boolean
BYTE: Byte
COMPLEX_TYPE: Integer - an autonumber field which is the key for a secondary table which holds the data
DOUBLE: Double
FLOAT: Float
GUID: String with the pattern {xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}
INT: Short
LONG: Integer
MEMO: String of max length 8388607 chars
MONEY: BigDecimal with at most 4 decimal places.
NUMERIC: BigDecimal
OLE: Byte[] of max length 16777215 bytes.
SHORT_DATE_TIME: Date - basic4android does not directly support this type
TEXT: String of max length 255 chars
GetColumnNames As String()
Returns the names of each of the columns in the table as a String array.
GetColumnValue (columnname As String) As Object
Most Access data types map to types directly supported by Basic4android and can be returned as is.
Only dates need special treatment to turn them into ticks for the Basic4android DateTime object.
Rather than put a check for a Date in every call to this method it is incumbent on the programmer
to pass the Date object to the JackcessDatabase.DateToTicks helper method.
GetIndex (indexname As String) As com.healthmarketscience.jackcess.Index
Returns an Index for the table for use in JackcessIndex.Initialize.
GetIndexNames As String()
Returns the names of each of the indexes in the table as a String array.
GetNextRow
Reads the next row into an internal Map containing a set of Column name (String) and value (Object) pairs.
The values of individual columns in the row may be obtained by GetColumnValue or GetColumnValueAndDate
GetRowCount As Int
Returns the number of rows in the table.
GetTable As com.healthmarketscience.jackcess.Table
Returns the internal wrapped Table object.
Initialize (table As com.healthmarketscience.jackcess.Table)
Initialises this object with a database table provided by JackcessDatabase,GetTable.
Reset
After calling this method, GetNextRow will return the first row in the table,
UpdateCurrentRow (values() As Object)
Update the current row with the provided values.
Any Date values should be passed as ticks to the Jackcess.DateFromTicks helper method
and the returned Date object used here.

Note the after invoking this method GetColumnValue will return the old values not the new ones.
This is unlike the behaviour of Cursor.UpdateCurrentRow and Cursor.SetCurrentRowValue after which
the new values ARE available. The reason for this is that Cursor rereads the current row to update
its stored values but a Table cannot do this as it lacks the necessary method.
Top