search

atris / JDBC_FDW

FDW that wraps JDBC for PostgreSQL.It can be used to connect and fetch data from any data source that supports JDBC
Other
66 stars 38 forks source link

Error mapping datetime from SQLServer. #4

Closed brunosimioni closed 11 years ago

brunosimioni commented 11 years ago

There is an error mapping datetime type from SQLServer to PostgreSQL via JDBC_FDW.

Environment: jtds-1.2.4.jar postgresql-9.1.5 (compiled from source). jdbc_fdw compiled from source.

SQLServer table columns (at SQLServer): IDINDICE int VALORDE numeric ANOMESINICIO int DATALANCAMENTO datetime VALORATE numeric VALORRETORNO numeric ANOMESFINAL int IDUSUARIO int

Row from table at SQLServer (same order that is declared above) 1001 0 200401 2004-01-01 00:00:00.0 999,999,999 240 200505 5

Foreign Table (at PostgreSQL) CREATE FOREIGN TABLE oris_fdw_sm ( valorretorno numeric, anomesinicio numeric, anomesfinal numeric, idindice int ) SERVER oris_fdw_server OPTIONS ( table 'oris.oris.eveinddet' );

SQL Statement (at PostgreSQL) select * from oris_fdw_sm;

Error (at PostgreSQL) ERROR: invalid input syntax for integer: "2004-01-01 00:00:00.0"

\ Error **

ERROR: invalid input syntax for integer: "2004-01-01 00:00:00.0" SQL state: 22P02

Notice that I haven't mapped DATALANCAMENTO column, although the error raised when getting this content.

atris commented 11 years ago

Table oris_fdw_sm has 4 columns--numeric,numeric,numeric,int and SQLServer table columns are int,numeric,int,datetime.The 4th column in oris_fdw_sm is being mapped to the 4th column in SQLServer table(as it should be,since there is no explicit mapping).Hence,there is conflict between the datatypes of the columns(int in oris_fdw_sm and datatime in SQLServer table) and hence the error.

brunosimioni commented 11 years ago

Right, I get it now.

I do suggest that it must have at least one of this three behaviors:

  1. Should be able to map only specified columns, and ignore the ones that has not been mapped. Only for performance issues (not getting all column data that will never be used).
  2. If not, throw any error for not specify all columns names and types, at the CREATE FOREIGN TABLE moment.
  3. If not, if it remains with this same behavior, advise the user that some columns are missing at the moment of CREATE FOREIGN TABLE.

That are only some suggestions for improve user experience.