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!!