Subscribe to this thread
Home - General / All posts - ODBC issues
rk
265 post(s)
#28-Apr-18 17:00

Does anyone have similar or different experience?

I tried to connect to m9 .map file from MS Access 365 64bit Build 9226.2114 and Tableau 2018.1 using Manifold ODBC drivers 9.00.166.00 and 9.00.166.05 (Experimental).

166.0 is installed with installer. 166.05 is of course portable.

In MS Access I see list of tables but then get error -7748

In Tableau I get:

An error occurred while communicating with Other Databases (ODBC).

Unable to connect to the server. Check that the server is running and that you have access privileges to the requested database.

[Microsoft][ODBC Driver Manager] Driver's SQLAllocHandle on SQL_HANDLE_ENV failed

Generic ODBC requires additional configuration. The driver and DSN (data source name) must be installed and configured to match the connection.

Unable to connect using the DSN named "mfd9". Check that the DSN exists and is a valid connection.

I also tried Manifold Project Driver 8.00.30.00

MS Access can read the tables from m8 .map

In Tableau

An error occurred while communicating with Other Databases (ODBC).

The connection to the data source might have been lost.

The connection to the data source might have been lost.

ExternalProtocol::PipeMessageSource::ReadBytes: Communication with the Tableau Protocol Server process was lost.

Generic ODBC requires additional configuration. The driver and DSN (data source name) must be installed and configured to match the connection.

Unable to connect using the DSN named "mfd8". Check that the DSN exists and is a valid connection.

Attachments:
error-7748.PNG
odbc_table_listing.PNG

adamw


7,903 post(s)
#05-May-18 13:26

We are aware of issues connecting from Tableau (it's something strange, the connection fails before our driver even loads). We will investigate further.

We will check what's up specifically with the version of Access that you mention as well. We have been testing against different versions of Access and they have been working fine.

Forest
566 post(s)
#07-May-18 23:47

On the same day, I had imported ms-access data into Manifold but could not use OBDC to see it in QGIS. I have followed the example in the help file closely and QGIS can successfully test the connection. A non-spatial table is shown in the QGIS 3.0.2 table of contents and as it is not possible to open a non-spatial table the trail stops there. I have tried by GEOMETRY and POLYGON in the GEOMETRY_COLUMNS table.

This is not a show stopper as geopackages provide an alternative. I have edited geopackages in QGIS and the results are reflected in Manifold and have also done the reverse.

adamw


7,903 post(s)
#08-May-18 12:30

A follow-up:

The issue with Tableau is still in the works.

The issue with Access was specific to newer versions of Access and was on our side (we went a little overboard with Unicode).

The issue with QGIS is specific to newer versions of QGIS and is, to put it bluntly, QGIS (or, more specifically, perhaps OGR) trading one bug for another. We can work around the new bug relatively easily and safely, so we will just do that.

(They used to be putting table names into queries verbatim: SELECT * FROM <whatever the table name is>. This is a big no-no, because the table could be named something like: 'geometry_columns; DELETE FROM data;', this is SQL injection. Someone noticed and they now started quoting table names. But they don't ask the driver what quotes to use and are blindly using the double quote character. Which breaks if that's not a legal quote for the database - and it frequently isn't, eg, this wasn't a legal quote for us. We will make the double quote character a legal quote for ODBC connections. This will have no effect on well-behaving clients and will simply make non-well-behaving clients fail in other places than where they fail now - and on balance, they will fail rarer.)

rk
265 post(s)
#09-May-18 15:59

Great.

I can import tables into Access, but linked tables show records as #Deleted

Pressing F5 shows the first record.

Attachments:
access_link_mfd_meta.PNG

Lucius1 post(s)
#09-May-18 21:30

I was having similar issues linking tables to Access from a Manifold 9 Map file. I am using Access 365 64bit, and also have the Access Database Engine 2016 installed.

It seemed to be partially resolved with Build 9.0.167.0. I can link tables from M9 project into Access if the primary key is an integer datatype. If the primary key consists of an integer & VARCHAR field the linked table shows all field values as '#deleted'.

There seems to be an issue with VARCHAR in an index and its transfer to its "equivalent" LONG TEXT datatype in Access.

tjhb
8,009 post(s)
#09-May-18 22:49

Interesting! Great observations.

There is not a 1:1 mapping for Unicode characters in a conversion to a narrower text type. 1:N. So a BTREE involving Unicode may not survive?

I wonder what Manifold does or tries to do in such a case.

tjhb
8,009 post(s)
#10-May-18 21:35

It doesn’t matter at all, especially after Adam’s explanation below, but yes I did mean N:1 not 1:N. (Narrowing.)

adamw


7,903 post(s)
#10-May-18 15:08

There is an important detail for Access that I've been meaning to post, you all beat me to it.

Many of our tables use 64-bit integer fields (MFD_ID, for one thing, is always 64-bit). Access only recently started supporting 64-bit integer fields and it does so on an opt-in basis, because opting in makes the format incompatible with older versions of Access / other applications.

Here is how to link tables with 64-bit integer fields from Manifold to Access:

Create a new blank database. Invoke File - Options, Current Database. Scroll all the way down and check 'Support BigInt data Type for Linked/Imported Tables'. *Now* link the tables from the Manifold data source.

You can also repair existing links: turn the same option on, then invoke External Data - Linked Table Manager, select the tables linked from Manifold and click OK to refresh them.

We'll check what happens with indexes on text fields.

adamw


7,903 post(s)
#10-May-18 17:15

A follow-up on Access and #deleted on tables with primary key on text fields:

If the text field is non-Unicode (VARCHAR in 9), everything works fine.

If the text field is Unicode (NVARCHAR in 9), Access tries to use it as non-Unicode and fails.

The specifics: Access asks us to return data for the field in its native format, we return Unicode, but it expected the data to be ANSI and so things fail. This only happens with key fields, when accessing non-key fields Access either asks specifically for Unicode - we return that and it then treats it as Unicode correctly - or it asks specifically for ANSI - we convert to ANSI and return ANSI and Access then treats it as ANSI correctly. We cannot really patch this. Other products also experience the issue, some 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, but this just loses data and ruins uniqueness, because the conversion from Unicode to ANSI is lossy. What we would suggest is creating a computed VARCHAR field, building an index on that field and dropping the index on the NVARCHAR field. Maybe there is something easier. The 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 to update.

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.