EODBC data type issues in migration to new database

Support
,
1

I am in the process of migrating an older script that originally ran on Windows and now runs on Linux. The problem I am experiencing is the part that saves results to a databse. In this migration, it is moving from a MSSQL database to a Postgres database.

The original script was using the ODBC table handler. I have moved to use the eodbc table handler. The problem is that when the write table operation occurs - it should be writing to an existing table, but it cannot find it. It seems that it is finding datatype mismatches in the columns. The table in the database has mostly integer columns, but the driver is using float.

Here is a snippet of the errors when using a verbose log for the table handler:

DEBUG: <parse_arguments>
INFO: table: SupplyOutTable
INFO: inout: OUT
INFO: parse_arguments: 'DRIVER' set to '{PostgreSQL ANSI};SERVER=......''
DEBUG: <is_handler_extensions>
WARNING: using alias: least_cost.tmp_solution_solver_fob_item_dc_assignment
INFO: verbose: 3
DEBUG: <validate_arguments>
DEBUG: <get_ampl_col_types>
DEBUG: amplcoltypes: [0, 0, 0, 0, 0, 0]
DEBUG: <alloc_and_connect>
INFO: connection string: Driver={PostgreSQL ANSI};SERVER=.....'
DEBUG: <table_exists>
INFO: Cannot find table least_cost.tmp_solution_solver_fob_item_dc_assignment
DEBUG: <table_create>
DEBUG: SQL_DOUBLE -> float8
INFO: SQL: CREATE TABLE least_cost.tmp_solution_solver_fob_item_dc_assignment (solver_run_id float8, item_id float8, facility_id float8, distribution_center_id float8, cases float8, cost float8, PRIMARY KEY (solver_run_id, item_id, facility_id, distribution_center_id));
Error at _cmdno 61 executing "write_table" command
(file RSIRUN.RUN, line 826, offset 70696):

        Error writing table SupplyOutTable with table handler eodbc:
        The driver reported the following error SQLExecDirect(SQL_HANDLE_ENV)
42P07:1:1:ERROR: relation "tmp_solution_solver_fob_item_dc_assignment" already exists;
Error while executing the query

It does not detect that the table exists and therefore tries to create the table. However, it does exist and causes the issue. I can see that it wants to create the table with float for all the column types. I’d prefer to avoid having to re-do all the table schemas, as this worked fine on prior database.

I can’t seem to force it to use specific data types for the write. Any ideas on how to resolve this? Thanks!!

2

Hi @andrewb ,
Thank you for the report!
We’ll take a look at it and let you know when we have a solution.

3

Hi @andrewb ,
An updated version of eodbc is available at

Scroll down the page, select the plugins module compatible with your machine and download the module. Afterwards extract the content and replace eodbc.dll in your AMPL folder accordingly.
Note that the updated eodbc.dll is not yet available in the installers. You need to update it manually.
Please let us know if everything is working as expected!

4

Hi @Nicolau_Santos ,

Unfortunately, that did not seem to resolve the problem. I was able to download the updated file. I still receive the same issue in which it does not evaluate the data types correctly. One detail I did not include, was that I am using the UnixODBC package in the debian install.

I have just dropped my tables and let AMPL create them with the float8 data type instead and will do the appropriate casts when utilizing the data.

5

Hi @andrewb ,

Thank you for the reply!

The main issue was eodbc failing to detect the table name.

The data conversion should be handled by the ODBC implementation (in this case unixODBC). For example, if the original table has a column of type INTEGER, when you write data from AMPL into that table the AMPL doubles should be converted to integers.

Are you getting any particular error message or facing issues with the data conversion?