Jackcess / News: Recent posts

Jackcess 5.0.4 Release

• the sql generated for several kinds of saved queries so that it matches what ms access writes: query rows sorted by their order column, TOP with DISTINCT/DISTINCTROW, DISTINCTROW in update and delete queries, bracketed parameter names, subqueries in the FROM clause, the placement of repeated join conditions, "*" in delete queries without columns, no select type in crosstab queries, and the trailing NUL of ddl and pass-through text.
• Parse date/time values the way ms access does, both in date literals and in strings converted to dates (CDate, IsDate, etc.), including times without seconds, year first dates, dates without a year, the other field orders ms access tries, and month names.
• Name the arctangent function Atn, which is what ms access calls it. It was registered as Atan, so an expression which ms access evaluates failed to find the function.
• Build the right text index key for the characters the weight tables handle specially: marks which stand for the character before them, characters weighed as more than one, weights which do not split into equal parts or which are shorter than a whole entry, marks which modify the weight before them, and values long enough that ms access writes past the room it keeps for the weights. The offsets the diacritic and unprintable weights carry are counted the way ms access counts them, a collation which moves a few characters takes the rest from the weight table it tailors, and the tertiary weight ms access keeps for each character is carried in a new table. Checked against indexes ms access built over every combination of character classes the encoder can meet, 219100 values in each of the two weight tables. The 1997 table counts a character weighed as more than one the same way.
• Read a compressed text value whose uncompressed run holds a character below 0x0100, such as "d", which is written 64 00. The run was walked one byte at a time, so the 00 of such a character was taken for the byte which marks the change back to compressed and the rest of the value came out wrong. An uncompressed character is two bytes wide, and stepping over the whole character is what tells the two apart.
• Read a jet 3 text value which starts with the two bytes that mark a compressed value in a later format. Jet 3 holds text in a code page and has no compressed form, so those bytes are two characters of the code page, and reading the value as compressed put a NUL after each character. The compressed form is now taken only where text is unicode.
• Find a column whose name starts with the byte order mark. A name keeps that character, while the same name read out of a text value loses it, because the two bytes which mark a compressed value are also how it is written. Ms access matches the two, so a column lookup now passes over the mark. A database holding such names could not be read for its relationships at all.
• Let a table be related to itself, which is how a tree is held: a row carries the key of its parent row in the same table. The two table lookup, Database.getRelationships(Table,Table), refused the same table twice, although the other two lookups already returned such a relationship. Creating one wrote the wrong index number into one of the two foreign key indexes, so the file could not be read back, and a row which named itself as its parent could neither be added nor removed although ms access does both. A relationship row which names a column the table does not hold is now skipped, the same as one which names a table which is not there, rather than throwing and taking every other relationship of that database with it. A column can be matched against itself as well, which ms access allows without referential integrity and refuses to enforce.
• Read a short year in DateSerial by the same windows rule a date literal takes, 00 through 49 in the 2000s and 50 through 99 in the 1900s. The turnover was at 30, so DateSerial(30,1,1) gave 1930 where ms access gives 2030.
• Parse a function call written with a space before its bracket, such as "Now ()", which ms access accepts and writes itself. The parser threw an UnsupportedOperationException for one, which a caller could not even catch as a parse problem.
• Parse a date literal whose year is below 100, such as #1/1/99#, #1/1/9# or #1/1/0009#, by the windows rule: 00 through 49 are in the 2000s and 50 through 99 in the 1900s. The rule is the value of the year rather than the number of digits it is written with, which is how ms access reads each of them.
• Allow an expression for the pattern of a Like, such as a comparison of one column against another, which ms access accepts. Only a literal pattern was taken before, and an operator which binds tighter than the Like, as in Like "a" & "b", is now part of the pattern.

Jackcess 5.0.3 Release

• Create complex columns, which jackcess could read but not write. ColumnBuilder.setMultiValue() with setValueList() or setRowSource() makes a multi-value column, setAttachment() an attachment column, and setAppendOnly() on a memo column keeps the history of its values. Jackcess builds the table which holds the values, the row which names it and the properties ms access reads, through either TableBuilder or ColumnBuilder.addToTable(). A complex column added to a table which holds rows gives every row a value id, as ms access does.
• Hand out a free page again, with Database.setReuseFreePages(true) or the com.healthmarketscience.jackcess.reuseFreePages system property. A write then takes the lowest free page in the file before it grows the file. It is off by default, because it changes the page every write lands on.
• Database.reclaimFreeSpace() shortens the file to drop the run of free pages at the end of it, and gives back the number of pages it dropped. The free pages inside the file stay where they are, and setReuseFreePages() is what puts those to use.
• Take an index page out of the index's owned pages when the page is freed. Deleting rows can empty an index page, which jackcess gives back to the database, and the index went on claiming a page which ms access can hand to another object.
• Keep a long value where it is when an update keeps its value with Column.KEEP_VALUE. Jackcess wrote a second copy of the value, and left the first one behind, whenever the row had been read before the update.
• Write the new value of a calculated MEMO or TEXT column when a row is updated. Jackcess re-used the bytes already written for the column, which kept the value computed before the update. A calculated MEMO now takes new pages on every update, because the value is written again and jackcess does not free the pages the old one held.
• Read a table whose column usage map is named by the table definition but cannot be read. Writing a long value to such a column threw a NullPointerException, where the warning says the table is still usable.
• Evaluate the GenGUID() and GenUniqueID() functions, which the jet expression service provides and ms access gives as the default value of the columns it adds to make a database replicable. GenGUID() returns a new random guid in the braced, upper case form ms access writes, and GenUniqueID() a random value over the whole range of a LONG. Adding a row to such a table threw "Could not find function".
• Read a database whose system catalog holds a row with nothing in it, which a damaged file can have. The row names no object and is now stepped over, where reading its flags threw NullPointerException and lost every table in the file. A query row with no flags is skipped with a warning for the same reason.
• Generate an auto number GUID in upper case. Column.readGUIDValue gives back upper case, which is what ms access writes, so a value jackcess generated did not match the one it read back for the same row.
• Evaluate the default value of a column which holds no free text. A default value which does not start with "=" is taken as a literal in a string context, which is right for a TEXT or MEMO column, where ms access lets a literal be written without quotes. A GUID column also reads as a string and took the same path, so the default GenGUID() which ms access gives a replication column was stored as that text rather than evaluated.
• Find a row in a Jet 3 index which is not on its first leaf page. The index page's compressed byte count is one byte on Jet 3, and the byte after it is the page's level in the index tree.
• Read Jet 3 text with the charset the database's own code page names, which is windows-1252 for most files. Jackcess used the platform default charset, which has been UTF-8 since Java 18, so text outside ascii decoded to the replacement character and an index lookup for it then found nothing.
• Find a row by a date value read out of it, with the default DateTimeType.LOCAL_DATE_TIME. The fractional second was rounded to milliseconds, so the value no longer held what the stored double held and the index key built from it matched no entry. A date now keeps the coarsest fraction of a second which converts back to the double it was read from, so a value this library wrote is unchanged and a value ms access wrote keeps its low bits.

Jackcess 5.0.2 Release

• Treat a text index in a jet 3 file as read only unless its locale is english. 5.0.1 wrote and looked up such an index with the english order, which a jet 3 file in another locale does not use.
• Make the case conversions which compare names and keywords independent of the default locale.
• Read, change and write the name map of a table through Table.getNameMap(). The name map is the data behind the ms access Name AutoCorrect option, and a column rename has to update it. NameMap.findMismatches() lists where the name map and the table disagree.
• Read the properties of a query through Query.getProperties(), and its name map through Query.getNameMap(). The name map of a query cannot be changed, and its findMismatches() lists the tables and columns the query uses which were renamed since it was saved, which ms access repairs when it next opens the query.
• Add PropertyMap constants and default types for more of the common database, table, column and query properties.
• Write a memo property value longer than 255 characters, such as a long default value expression, which ms access saves. A property value which is too long now fails with an InvalidValueException which names the property, instead of a NullPointerException or a corrupt property block.
• Keep the ddl flag of a property as it is stored in the file. A property whose name has a default ddl flag was read with the flag on, and a save then wrote the flag into the file.

Jackcess 5.0.1 Release

• Write text indexes in the collations which weigh a sequence of characters as a unit: czech and slovak, spanish traditional, hungarian in both its orders, norwegian and danish, croatian, serbian, bosnian and vietnamese.
• Test what happens to a text index in a collation jackcess cannot write, which no test covered once the collations of this release supported every database in the test data.
• Write text indexes in the french sublanguages, belgium, canada, switzerland, luxembourg and monaco, which ms access sends to the french collation and which were refused although french itself was written.
• Write the upper case forms of the characters the general order collations move. Croatian, serbian, bosnian, romanian and indic in that family moved the lower case form alone.
• Write text indexes in the collations which a whole sort id names rather than a locale: german phone book, which expands the umlauts, georgian modern, and romanian and indic in the access 2010 order.
• Write text indexes in twelve more collations, which move a few characters against the general legacy order: spanish modern, swedish and finnish, polish, romanian, turkish, slovenian, estonian, latvian, lithuanian, icelandic, ukrainian and macedonian.
• Find a row whose index entry it shares with another row. One entry can hold several rows, since the entry of a text value folds case, and a lookup read only the first of them.
  Add Write text indexes in the french collation. French weights every character the way the general legacy order does and orders the diacritics of a value from its end.
  Add Write text indexes for the 58 locales which the ms access locale map sends to 1033, which is most of europe: german, greek, italian, dutch, portuguese, russian, arabic, georgian, hindi and their sublanguages. None of them tailors a single character, so the weight table jackcess already has is the whole of the collation.
• Read and write the collation variant, the third byte of a column's sort order field. The four bytes are the ms access sort id, and the variant separates the collations of one locale, so german phone book used to read as plain german and hungarian technical as plain hungarian.
• Add Column.getColumnId, the id ms access gave a column when it was created. An id is never renumbered, so a table which has had a column deleted has gaps in its ids.
• Add Column.isHidden and Column.isSecurityIdentifier for the two column flag bits which were previously unnamed.
• Add Index.getProperties. An index has a property block of its own, separate from the block of the column access named it after.
• Add PropertyMap.Property.isSkipHandler for the property flag bit which tells ms access to store the value without running the handler bound to the property name, and a PropertyMap.put which sets both flag bits.

Jackcess 5.0.0 Release

• Update minimum java requirement to java 11. Some code base cleanup and modernization.
• Removed RuntimeIOException in favor of built in UncheckedIOException.
• Removed DataType.UNKNOWN_0D. It is not a data type.
• Renamed DataType.UNKNOWN_11 to DataType.BIG_BINARY, which is what the engine calls it. It is a fixed length binary column longer than the 255 byte BINARY, used only by the Data column of MSysAccessObjects.

Jackcess 4.0.12 Release

• Determine the type of a complex column from the name of its "type" table, which ms access reserves, rather than by counting the data types of that table's columns. one.
• Find the foreign key column of a complex column's flat table from the flag ms access sets on it, rather than by looking for the long column which is not the autonumber
• Check the column name in MSysComplexColumns against the column being loaded, which catches a bad complex id in a column definition
• Truncate long text index entries the way ms access does, keeping the leading 508 bytes of the key and ending it with a digest of the bytes discarded. Keys longer than 510 bytes were previously written in full, so ms access could not use the index and jackcess could not find the affected rows again.
• Fix the index code suffix written for "international ext" characters (which include hiragana and katakana). The suffix length grows with the number of characters using those codes, but a fixed length one was always written, so index entries for 8 or more such characters were wrong.
• Some code base modernization. Note a minor exception widening in OleBlob.
• Fix database corruption when adding column to table with deleted variable length columns.
• Add toString to ColumnBuilder, TableBuilder and IndexBuilder.
• Check that the column counts and sizes in a table definition cover the columns which are actually defined when the table is loaded. A damaged table definition now becomes read-only.
• Fix the index entry written for a surrogate pair. In the general sort order the weight table gives the high surrogates four separate ranges, one of which has no weight at all, and only the first was implemented, so entries for characters above plane 1 did not match ms access. In the general legacy sort order ms access gives a surrogate no weight at all, so every entry for a supplementary character was wrong there.
• Write the level of an index page into its page header. The level is the depth of the page below the leaves of the index tree, 0 on a leaf page and one more than its children on a node page. Zero was written on every page, so an index deep enough to need node pages claimed they were all at the level of the leaves.
• Identify a property map by its name and its block type together. Ms access keeps index properties in their own block and names an index after its column by default, so a table commonly holds a column block and an index block with the same name. Keying on the name alone reported the index properties as the column's, and rewriting the map wrote one block instead of two, which discarded the index properties.
• Keep the whole property flag byte when a property map is rewritten. Ms access uses it as a bit field: bit 0 is the ddl flag and bit 7 tells the engine to store the value without running the handler bound to the property name. The byte was read as a boolean and written back as 0 or 1, which invented the ddl flag on properties which did not have it and dropped bit 7 from those which did. PropertyMap.Property isDdl now reports bit 0 rather than the whole byte.
• Give a column added to an existing table an unused column id. Ms access assigns each column an id which it never reuses and never renumbers, so a table which has had a column deleted has gaps in its ids. The physical column number was written as the id, which could repeat an id already in use.
• Mark an index over a complex column the way ms access does, with a 2 in the byte after the index flags.
• Use the file formats which ms access stores uncompressed when writing an attachment (jpg, jpeg, gif, png, zip, cab, docx, xlsx, xlsb and pptx), and deflate everything else. The previous list compressed png and office files which ms access leaves alone, and left archives raw which ms access deflates.
  Update Linked databases are no longer resolved automatically. The default LinkResolver now refuses to open linked databases. Applications which use linked databases must configure LinkResolver.UNRESTRICTED (the previous behavior) or a resolver enforcing their own policy, or enable the allowLinkResolution system property. Note that this also affects CustomLinkResolver, whose fallback for non-custom files now denies.

Jackcess 4.0.11 Release

• Promote the global usage map to a reference map once a database grows beyond what an inline global usage map can represent (~2mb). Large databases previously retained an inline global usage map (a sliding window which no longer described the whole database), which ms access would report as invalid/corrupt during operations such as sorting a large table. Fixes 160
• Fix possible edge-case corruption when updating indexes.
• Make text functions locale sensitive.
• Add optional support via system property for writing "broken" indexes for unsupported sort orders. This enables building the db using jackcess and then later making the db indexes valid using ms access compact and repair.

Jackcess 4.0.10 Release

• Update commons lang3 dependency version
• Update apache poi dependency version

Note, 4.0.9 release was skipped while fixing issues due to updating to new sonatype repository.

Jackcess 4.0.8 Release

• Fix adding fixed length columns to an existing table.

Jackcess 4.0.7 Release

• Add support for surrogate pairs in text indexes (e.g. emoticons)

Jackcess 4.0.6 Release

• Make NumberFormatter locale aware, using the currently configured LocaleContext
• Account for character sizes when copying column lengths

Jackcess 4.0.5 Release

• Add option to DatabaseBuilder for ignoring broken system catalog indexes. This is a workaround to allow jackcess to read tables from the database even if the index is non-functional

Jackcess 4.0.4 (and 4.0.3) Release

• Fix edge case which can cause table to be considered corrupt
• Update parent pom to fix release process

Jackcess 4.0.2 Release

• Add Table methods to get the creation and last modified dates.
• Add support for linked odbc tables. TableMetaData provides access to connection string and local TableDefinition if available

Jackcess 4.0.1 Release

• Ignore trailing spaces when creating text index entries.
• Add basic support for access 2019+ dbs.
• Add support for extended date/time type in access 2019+ dbs.

Jackcess 4.0.0 Release

• Add Automatic-Module-Name in order to make Jackcess safe to use in the module path in Java9+ environments.
• Make a few classes and constants public in order to facilitate use in different packages.
• Alter access to java.sql classes so that usage is dependent on whether or not the module is loaded in Java 9+ environments. Tweak a few methods to no longer throw SQLException.

Jackcess 3.5.1 Release

• Add more static utility methods to DatabaseBuilder to enable more succinct object construction code using static imports

Jackcess 3.5.0 Release

• Add support for Access 97 general collation order, enabling indexes to be used with Access 97 dbs
• Add Stream support to all interfaces which are Iterable
• Enabled expression evaluation by default
• Change the default DateTimeType to LOCAL_DATE_TIME
• Add support for Predicate value patterns in cursor find methods. Add PatternColumnPredicate for creating Predicate instances which can match values using various pattern syntaxes

Jackcess 3.0.1 Release

• Add ColumnFormatter utility which can apply Column "Format" property for display of column values
• Add Database.newTableMetaDataIterable() which enables iterating through table names without loading the entire collection into memory at once

Jackcess 3.0.0 Release

• Jackcess now requires a Java 8+ runtime. As part of this update, all dependencies have been updated to their latest versions
• Add support for Java 8 Temporal types. Date/time fields will now accept as input most Temporal types (e.g. LocalDate, LocalTime, LocalDateTime, etc). Additionally, date/time fields can optionally be changed to output LocalDateTime instead of Date. This behavior is configurable on a per-Database basis by setting the DateTimeType for the database. All Date based APIs now have a parallel LocalDateTime API. Note that only one of those will work depending on how the Database is configured. The legacy Date support will be the default initially, but is deprecated and may be removed in the future
• Add support for Path. Existing File based APIs now have parallel versions which utilize Path
• The Apache commons-lang dependency has been replaced with commons-lang3

Jackcess 2.2.3 Release

• Fix parsing of escaped double quotes in expressions
• Use column label from ResultSetMetaData when importing

Jackcess 2.2.2 Release

• Fix length in units calculation for Access 97 text fields

Jackcess 2.2.1 Release

• Implement the majority of the missing standard functions: FormatCurrency, FormatDateTime, FormatNumber, FormatPercent, Val, DateAdd, DateDiff, DatePart, MonthName, WeekdayName, DDB, IPmt, PPmt, Rate, SLN, SYD, Format, Replace, StrConv. (Note that the internal API for the expr package has changed in an incompatible way. However, since the API is still experimental, this was deemed acceptable for a minor version.). Note that many of the financial functions were copied and adapated from the Apache POI and UCanAccess projects (which are both under the Apache License 2.0)
• Implement more type coercion methods for expressions. Add support for hex/oct integer strings. Add support for number strings with commas. Add support for coercing numeric String to a date/time value. Add support for date/time values with implicit (current) year
• Ignore column validators for read-only dbs. This will avoid irrelevant failures when reading databases which have invalid column properties

Jackcess 2.2.0 Release

• Implement support for optional expression evaluation. When enabled, expressions will be evaluated for default values, calculated fields, field validators, and record validators.
• Default values will now be respected when expression evaluation is enabled (currently disabled by default).

Jackcess 2.1.12 Release

• Add some additional property keys and relevant enums for values
• Create new usage map correctly when adding an index to an existing table