DBMS Data Sources - Notes

This topic that captures technical notes from engineering on using DBMS packages such as Oracle, PostgreSQL, SQL Server, DB2 or other DBMS packages.   Notes provided here may be difficult to find or not yet incorporated into other topics.   They are provided here so search engines can find them.

 

 

Specifying ports when connecting to DBMS data sources

When connecting to a DBMS we can specify the port to use either in the connection string or within the name of the server after a colon.   For example, when connecting to a PostgreSQL server we can write for the name of server:

 

localhost:5433

 

The dialog will know we are connecting to PostgreSQL, a database that allows explicit specification of a port to use for the connection, and it will split out the port number from the name automatically.   Alternatively we could explicitly specify the port as part of the connection string, in the form:

 

{ "Source": "host=localhost port=5433 user=postgres password=xxxx dbname=xxxx" }

 

Identity Fields

Views on PostgreSQL and other databases allow specifying a field to use as an identity field. Doing this allows records in the view table to be selected, and, if the view supports that, edited or deleted. To specify a field to use as an identity field, right-click the field header and choose Use as Identity. The system will scan all values in the field to make sure there are no duplicates or NULLs and if the values are clean, the table will expose a BTREE index on the field. The information about the field used as an identity field is saved into the database, so attempting to use the table in future Manifold sessions will automatically expose a BTREE index on that field without further scans. The identity option can be cleared or moved to a different field. The view can have only one identity field specified in this way.

 

Using a view with the identity field specified via the Use as Identity command in the table window monitors reads via the index. If the system detects that some of the values in the identify field are duplicates or NULLs, it automatically removes the identity option from the field and reports the removal in the log window.

 

Connecting from Microsoft Access to a Manifold ODBC Data Source

Please also review the discussion in the MDB Files in 64-bit Windows topic and the Example: Create and Use New Data Source using an MDB Database example topic.

 

Many Manifold tables use 64-bit integer fields.  The mfd_id field, for example, is created by Manifold as a 64-bit integer field.  Access only recently has started supporting 64-bit integer fields.  As of this writing, Access supports 64-bit integer fields on an opt-in basis,.    Choosing to opt in support for 64-bit integer fields makes Access format incompatible with older versions of Access and incompatible with other applications that use Access format.   The need to opt-in requires extra work to link Manifold tables with 64-bit Integer fields from Manifold into Access.

 

How to link tables with 64-bit integer fields from Manifold to Access:

 

  1. In the latest version of Access, create a new blank database.

  2. Choose File - Options, Current Database.

  3. Scroll all the way down and check Support BigInt data Type for Linked/Imported Tables.

  4. We can now link the tables from the Manifold data source.

 

How to repair existing links in Access to Manifold tables:

 

  1. Choose File - Options, Current Database.

  2. Scroll all the way down and check Support BigInt data Type for Linked/Imported Tables.

  3. Invoke External Data - Linked Table Manager.

  4. Select the tables linked from Manifold.

  5. Click OK to refresh them.

 

Primary Keys on Text Fields

Access can run into trouble Linking to Manifold tables that have primary keys which are text types if the text type is Unicode, that is, NVARCHAR.  This results from a well-known issue in Access:  

 

 

What causes the problem is that Access asks Manifold to return data for the field in its native format.   Manifold returns Unicode, since that is the native format, but because Access, despite asking for native format, thinks the data should be ANSI anyway the interchange fails and field values will be shown by Access as '#deleted'.  The problem only happens with key fields.  When accessing non-key fields Access either asks specifically for Unicode, which Manifold returns and which Access treats correctly as Unicode, or Access asks specifically for ANSI, in which case Manifold converts the data to ANSI, returns ANSI and Access then correctly treats it as ANSI.

 

This particular problem with Access is not something that can be patched around by Manifold, since Manifold should reply with Unicode when a field is Unicode and Access asks it to be returned in its native format.   Other products also experience the issue with Access, with various ways of dealing with it.  Some products, for example, expose user-level options to force data in key fields to ANSI, either always or when Access asks to return their values in the format native to the field.   Such hacks may cause more problems than they purport to solve:  the forced conversion in such circumstances loses data and ruins uniqueness, because the conversion from Unicode to ANSI is lossy.

 

If our Manifold tables contain a primary key that uses an NVARCHAR text field, one possible approach, besides moving from Access to SQL Server or some other more advanced DBMS, would be in Manifold to create a computed VARCHAR field, building an index on that field and dropping the index on the NVARCHAR field.   Other approaches might be easier, given the specific nature of the interoperability project between Manifold and Access.   

 

This issue in Access has been known for a long time and it is reported to be there because there is an external component involved which is difficult for Microsoft to update.

Geometry Collections

Reading geometry collection values automatically merges individual values of the same underlying type used in Manifold geometry, such as area, line or point, with differences between subtypes such as line and multiline being ignored.   The result of the merge is returned.   This applies to all data which support geometry collection values, including WKB, GeoJSON, JSON, native geometry in database-specific formats, and so on.

 

Reading geometry collection values with individual values of mixed underlying types automatically converts areas to lines and lines to points in order to return all coordinates. Example: reading a geometry collection with an area and several points will return a multipoint containing all coordinates of all individual values.

 

Notes

Compacting Data Sources - If a data source can be compacted, right-clicking on that data source in the Project pane will show a Compact context menu command.  If the data source does not support compacting, the command will not appear.  In addition to the context menu Compact command that can be used manually, the Manifold object model includes means to compact a data source, to check whether a data source supports compacting, to check whether a data source has data to compact or has unsaved changes.

 

See Also

Changes and Additions

 

File - Link

 

File - Create - New Data Source

 

Formats and Data Sources