MapInfo Pro Developers User Group

Hi all,

I'm hoping to revive this issue that I previously reported on MapInfo-L (https://groups.google.com/forum/#!searchin/mapinfo-l/alex$20eastwood%7Csort:date/mapinfo-l/nG0kIGQ4iLA/7lOak0YWAwAJ). I'm developing a PostgreSQL/PostGIS backend for our MapInfo application, the database backend will be optional for our clients so we need code that will work with data in the database as well as in regular tab files.

In short, when you upload a MapInfo table to the database any logical-type columns are converted to Character(1) and the value is lost. You can prevent data loss by converting the column to char(1) with MapInfo/MapBasic before uploading, but it means that I'll have to check for a database setup before every Select statement involving a logical column and change the query accordingly.

I'm not sure if the issue is with EasyLoader or perhaps the ODBC driver? Or maybe I'm doing something wrong but I've played around with this quite a bit and not been able to resolve it. Would really like to get this working because searching through ~200.000 lines of code for the affected select statements is not something I'm particularly keen on.

Cheers,

Alex

Hi Alex. There are a couple of things going on here.

MapInfoEasyLoader *can* create a 'bool' type field when uploading a LOGICAL field, but the ODBC driver needs to be configured so that "Bools as Char" is deselected. Here are some instructions around this:

The ODBC Data Source for PostGIS has a data type option called Bools as Char that is checked by default. To download a Boolean field as a Logical field in MapInfo Professional, uncheck Bools as Char in the PostGIS ODBC dialog called Advanced Options (PostGIS).

To do this:

1. From the Start menu, select Control Panel > Administrative Tools > Data Sources (ODBC).

2. In the ODBC Data Source Administrator dialog, under the System DSN tab select your PostGIS data

source and click Configure.

3. In the PostgresSQL ANSI ODBC Driver (psqlODBC) Setup dialog, in the Options pane click

Datasource.

4. In the Advanced Options (PostGIS) 1/2 dialog, uncheck the Bools as Char checkbox and click OK.

------------------------

After doing this, MapInfoEasyloader with create a bool for a logical, and MapInfoPro will interpret the bool as a logical when opening the table.

However, and this is a big however, there is a bug in MIPro that does not allow for saving a logical column as a PostGIS bool. So, the logical column effectively becomes read-only in MIPro (yes, you can modified other columns in the table and save back to the DB, but you won't be able to update the logical/bool column). This bug also prevents saving a copy of a table with logical data type to PostGIS.

I am curious however, you mention the possibility of working around the issue by changing the data type from logical to char(1) prior to uploading it to PostGIS. If you did so, you would retain the 'T'/'F' rather than having them converted to 1/0, but would that help? MapBasic can convert integer 1/0 to logical T/F.

Hi John,

many thanks for this, I'd only looked at the driver settings within MapInfo/EasyLoader and not the control panel.

The thing I said about the workaround was just that if you simply upload a table to the database, the logical column is converted to char(1), but the actual values are lost, whereas they will be retained if the column is converted from logical to char(1) before uploading.

Last but not least...this seems to be a pretty major bug if the logical/bool columns can't be updated or saved. So while I would much prefer to retain logical columns as bool in PostGIS I need to be able to update these fields as and when necessary. Can this bug be fixed!?

Hi Alex. Sure, we'll take another look at the bug, and re-evaluate it, since it's an impediment for you.

But, I want to try to ensure that there isn't any confusion about the effect of the bug. I am guessing that it wasn't treated as a higher priority because if you configure the driver to treat Chars as Bool then there exists an implicit conversion which treats '1' and '0' as true and false, respectively.

So it was regarded as a bug which did not limit functionality, since there was a workaround for supporting boolean values in PostGIS: treat as chars and then allow MIPro/MapBasic to interpret the 1/0 as T/F.

So, I'll bring it back up for consideration and re-prioritization, but hopefully the conversion is usable, if awkward, now, without waiting for the fix.

Thanks,

-john

gah. Sorry...typo in my attempt at "clarification"...should read "treat Bools as Char"

Hi John,

I appreciate you looking in to this and re-evaluating the bug priority. I've been pretty busy working on other stuff but I will take a closer look at this soon and get back to you with a more detailed response!

Many thanks,

Alex

Hi John,

just spent a bit of time playing around with this. In order to replicate the working environment, I have performed these tests via MapBasic scripts compiled to .mbx and then run, as opposed to doing everything from the MapBasic window in MapInfo.

If I leave my driver settings as they are (i.e. treating Bools as Char) then the problem remains that I will need to distinguish between a database and regular setup:

In a regular native-type setup, I can do a Select query on a table based on a logical column being True or False. In most cases we'll have something like

dim lLogical as logical

lLogical = 'determined in the code

Select * From some_table Where logical_column = lLogical

In a database setup, I open the same table but the logical column is now Char(1). The table has 11 rows, all but 1 are "F".

Dim lLogical as Logical

lLogical = True

Select * From some_table Where logical_column = lLogical selects all records, not just 1.

If I change it to Select * From some_table Where logical_column = "T" then it gives me the correct result. Alternatively I could have Where logical_column = Str$(lLogical), and this also gives the correct result. So with this last method I could get around having a 'If DBMS_Setup Else'-block before each query on logical columns, but I'd still need to go and do this in our code.

So then I changed my driver settings to not treat Bools as Char. I re-uploaded my test table to the database with EasyLoader, and the logical column is now stored as Boolean and the correct values have been retained for each row. Browsing the table (opened as a live table) the values are displayed as 0 for False, 1 for true, as expected. But the same Select query, with the lLogical variable set to True now selects all records when it should only select 1. This seems weird given that True is defined as 1 in the MapBasic.def file, the values in the table are listed as 1 or 0 in the table, so I would have expected this to work. Weirder still is that if I change it to ‘select where logical_column = 1’ (or ‘select where logical_column = True’) I get a data type mismatch error.

Then, given the bug you described, I changed the value from 1 to 0 in the MapInfo browser window for 1 record, and committed this change back to the db. But when browsing the table in pgAdmin it shows the recently changed value correctly so the logical column can’t have been read-only.

So to sum this up, leaving treat Bools as Char would mean that I either have 2 different select queries for databse and regular setups, or use the Str$() function (which would still require trailing through the existing code). If I change my driver settings, the problem is that I also can’t query a logical column based on the value of a variable or even simply 1 or True. In both cases I think it is fair to say that it doesn’t work as one would expect!

Interested to hear what you make of this...

Cheers,

Alex

Hi Alex. Thanks for the information. I'll get back with you after I spend some time stepping through your examples in code. Interesting find with saving an updated record back to PG; I thought I'd tried this, or similar, and it didn't work for me. We have increased the priority on the bug for v17 and we'll incorporate your test scenario into it. I think the problems with querying against a pg table with logical columns is probably very closely related to the problem(s) defined in the bug.

-john

Hi, sounds good to me, thanks. If you want me to run any more tests let me know. Here some version information: MapInfo Pro v15.0.1 (though we will only ship the database-enabled application with v17) and PostgreSQL v9.5. PostgreSQL 32-bit ANSI v8.04.02.00 driver.

Alex