We use the DataDirect PostgreSQL Wire Protocol ODBC Driver in connection with SAP Data Services. We're working on an upgrade, which moves us to the 7.12.0085 version of the driver. When I try to import table defintions to Data Services, if the table contains columns of data type NUMERIC that specify length and precision values, the import fails with the following error:
ODBC data source <phobos_graph_mart> error message for operation <SQLColumns>: <[SAP BusinessObjects][ODBC PostgreSQL Wire Protocol driver]Number contains an invalid character: 30.0001373291015625 [SAP BusinessObjects][ODBC PostgreSQL Wire Protocol driver]Fractional truncation. Error in column 7.
The error is not related to data in the table; it's empty. I get this error when trying to import a table with a column of data type NUMERIC(30,5). If I change the data type and remove the length and precision, making it just NUMERIC, it imports with no error.
Can anyone provide any help with this?
Using just numeric works because it defaults to 1000 precision and possible 1000 scale. You can control this unbounded numeric with these two connection options:
UnboundedNumericPrecision
UnboundedNumericScale
They default to 28 and 6. The value 30.0001373291015625 has too many scales for NUMERIC(30,5)
Also, please be aware that the latest supported PostgreSQL ODBC WP driver is version 07.15.0199 (B0229, U0152). If you are already upgrading, it is highly recommended that you download the latest hotfix installer from the ESD download center in order to obtain this version. The Unbounded* connection options to which Eugene refers are no likely implemented in the 7.12.0085 version that you are currently using.
What I don't understand is where 30.0001373291015625 comes from. Is that how the database is storing a length of 30 in the background?
Is there a way to set those two connection options within the Windows ODBC DSN definition dialog? Or are these things that have to be passed in code, in a connection string. Because I'm not interacting with this in code.
@Brian. Okay. If they are implemented in the 07.15.0199 version, does that mean that there will be ways to set those in the ODBC DSN setup?
So, I'm going to post this here in case anyone ever is searching for the same problem.
We've finally figured out what was going on. To understand it, you need to know that we are using the EnterpriseDB version of PostgreSQL here at our business. In order to get the DataDirect drivers to work with EnterpriseDB, we had to overload the version() function in our database and have it return a string that contained the "PostgreSQL" instead of "EnterpriseDB". We worked that out with the people from SAP and Progress.
EnterpriseDB, at some point, though we don't know when, overloaded their int4/div function to return a numeric value rather than in integer. The DataDirect driver determines the length and precision of a NUMBERIC datatype field via division operation. Normally, this division would return an integer and everything is fine. But, when you override the division function and return a numeric value, suddenly 30 becomes 30.0001373291015625 and the driver rejects it.
I'm not sure why one would calculate the length and precision, since I'm sure they are stored in the catalog somewhere, but in case, that's how the driver does it and that, combined with EDB's modification, horked our communication with the DB.
For now, because we can't upgrade our drivers ourselves (we are dependent on SAP to include them in their software), we're going to drop the overloaded division function and we'll be good to go.
>> But, when you override the division function and return a numeric value, suddenly 30 becomes 30.0001373291015625 and the driver rejects it.
Where did you see this and what did the driver return to reject it?
Yeah, that's why we overloaded the version() function and return the string "PostgreSQL". EnterpriseDB also helped us (I said Progress before, but now that I think about it, it was people from SAP and EDB who helped us) figure this solution out.
The error is in my original post.