MapInfo Pro

Expand all | Collapse all

Field auto changing to LargeInt ?

  • 1.  Field auto changing to LargeInt ?

    Posted 14 days ago
    Hi all,

    a PC with MapInfo Pro v 17.2 (have also uninstalled and tried v17.1) is automatically changing one of the fields in a table when refreshing from an Oracle database:

    It only does it on one PC and not any of the other PCs with MapInfo v17.

    Original (what it should be)
    EQUIP_NO_ID Decimal (14, 0) ;

    after refresh on problem MapInfo/Win10 PC
    EQUIP_NO_ID LargeInt ;

    MapInfo will say it cannot open the table after it has been refreshed and closed.

    What could be causing this bug? In what situations does MapInfo modify table structure without confirming with the users?

    Thanks in advance

    ------------------------------
    cheers,
    Matt
    ------------------------------


  • 2.  RE: Field auto changing to LargeInt ?

    Pitney Bowes
    Posted 14 days ago
    Hi Matt.

    We made some changes in MIPro 17.0.1 to support saving a copy of a TAB with LARGEINT data types to RDB tables (including Oracle). Prior to 17.0.1 we didn't handle LARGEINT and RDB tables.

    However, I need to better understand what you are seeing, because I'm not able to reproduce an error.

    I'm just trying to think of every question to ask here, so I apologize for the length of the list. If we can't resolve the question here, in this forum, I'd suggest that you open a ticket with Technical Support where it can be tracked better.

    - Are you opening the Oracle table as Live w/ cache; Live w/out cache; or Linked?
    - How are you creating the table? Is it an existing table on Oracle, or are you using MIPro to save a copy of a Native table to Oracle?
    - Are you able to query the Oracle table directly (e.g., with Oracle SQL Developer)? If so, hat is the data type of the column on the db table?
    - Are you losing data; does the data value change?
    - You say it only happens on one installation of MIPro, running 17.0.2 (and 17.0.1?), but not other instances of MIPro 17.0.x? What version of MIPro is running on the other instances where you don't see a problem?
    - When you try to re-open the table, what version of MIPro are you using to open the table? I assume you are attempting to re-open the Oracle table at this point.
    - What version of Oracle server?
    - What version of the Oracle client? Is the Oracle client version the same on your different installation PCs?

    Here is what I've done so far to try to reproduce your issue:
    - Using MIPro 17.0.2:
    - - Created a NativeX table with a DECIMAL(14,0) column
    - - Saved a copy of this table to Oracle 11gR2
    - Checked the table structure on Oracle, and verified it was Number(14,0)
    -- Opened the table as Live/Cache
    -- Refreshed the server table and noted that MIPro reports the column type as LARGEINT.
    --- Part of the changes in 17.0.1 is that MIPro interprets Number columns with a precision between 10 and 20 as LARGEINT, in an effort to better support LARGEINT in MIPro and RDB tables. However, this shouldn't have any impact on the supported values or precision of the data; in fact these changes address a number of issues with lost precision.
    --Edited the column values and saved the table back to Oracle
    --- Save successful. Table schema remains unchanged.
    -- Closed table and reopened as live/cache
    -- Edited column values and saved back to Oracle

    - In MIPro 16.0.2
    -- Opened Oracle table as Live/Cache
    -- Data type reported is DECIMAL(16,0) (note..."16" not "14")
    -- Edited column values and saved back to Oracle

    - In MIPro 17.0.2
    -- Refreshed live table; saw values saved from 16.0.2

    - Repeated update/save/refresh in both directions a couple more times
    - Closed and reopened table from each instance a couple more times
    - In MIPro 17.0.2
    -- Saved a copy of the Live/Cache Oracle table back to Oracle (different name)
    -- Note, this creates a column of type Number(20,0) in Oracle for the LARGEINT type
    - In MIPro 16.0.2
    -- Opened this new copy of the table
    -- Note, this will display one of the problems addressed in 17.0.1 wherein we treat a Number(20,0) ("LargeInt") type as a floating point and lose precision

    Regards,
    -john

    ------------------------------
    John Teague
    PITNEY BOWES SOFTWARE, INC
    Troy NY
    ------------------------------



  • 3.  RE: Field auto changing to LargeInt ?

    Posted 13 days ago
    Hi John,

    Thanks for that detailed response, really appreciate the support and timely response!

    • Opening the Oracle table as linked (read only), it's an existing table on Oracle. Open Spatial Munsys is the application (tacks on database to AutoCAD for management of as constructed information).
    • I don't have access to Oracle SQL Developer, however it's defined as number (12 wide) in the application (Munsys) config.
    Example record with the largest value currently for EQUIP_NO_ID:
    GID                       EQUIP_NO_ID
    3,888,602            198,761

    Interestingly GID is Decimal (12, 0) and there are other fields in the table that are also EQUIP_NO_ID Decimal (14, 0) yet they do not auto convert to LargeInt after a refresh. This must relate to the precision you describe and its bumped to LargeInt.

    • The table is not being constantly re-created, it was created once years ago (running since MapInfo 12 32bit) and is refreshed by script every night. No changes are posted back to Oracle, it's read-only consumption only.
    • Not losing data, it's still there after refresh, but after closing the table and re-opening the error comes up - MapInfo unable to open.
    • This issue is encountered on MapInfo v17.1 & v17.2 however is not a problem on straight v17.0
    • Attempting to open the table in MapInfo versions above, from the locally stored data (not refreshing from Oracle) and error unable to open.

    Easy fix might be to recreate the table with a cast on the EQUIP_NO_ID field to another data type, I'll see how it goes.




    ------------------------------
    cheers
    ------------------------------



  • 4.  RE: Field auto changing to LargeInt ?

    Pitney Bowes
    Posted 9 days ago
    Matt,
    I tried to recreate the open-table error using a Linked table, but I've not been able to do so. I created a native table, with Decimal(14,0) using MIPro 16.0.2 and saved that to Oracle 11gr2. I then created a linked Native table from this, also using 16.0.2...so at this point I should have a baseline pre-17.0.1 table similar to yours. I used 17.0.3 to open and refresh this linked table; closed the table; re-opened in 17.0.3 and in 16.0.2...No error.

    This nightly refresh by script...I assume this is a MapBasic script that does a Server Refresh? Does it do anything else that might affect the state of the table?
    What is the exact text of the error you get when you try to open the table?
    Are you able to open the table in pre-17.0.1 versions of Pro?

    If there is something you are doing that I'm not, please let me know, and we can continue to try to resolve it here.
    Otherwise, I suggest that you will need to open a ticket with customer support so that the issue can be worked more formally.

    -john

    ------------------------------
    John Teague
    PITNEY BOWES SOFTWARE, INC
    Troy NY
    ------------------------------