When I try to link to an external object I always get the following error (tested of a couple of different machines):
(vd.gabmo_plz 22005 SR130: Integer 139715286138880 does not fit in column COL_SCALE. in DB.DBA.vd_int_attach_table, signal:(BIF), DB.DBA.vd_attach_view)
Could you please help debug this?
Which ODBC Driver are you using and what do you mean by tested of a couple of different machines?
At what point in the link external objects/tables process is the failure occurring?
The Virtuoso trace_on() can be used to get more information written to the virtuoso.log file on the Virtuoso server activities when attempting to link the tables.
Assuming the ODBC connection to the remote databases is successful an iODBC Driver Manager trace would also be useful to see …
Which ODBC Driver are you using
I’m using the official PostgreSQL driver compiled against iodbc. I can connect also via iodbctest and can sql select. I also see the DSNs in the conductor and can create a connection to them.
what do you mean by tested of a couple of different machines?
I’m testing on the AWS AMI, but also on my personal Linux Box with Virtuoso 8.3 evaluation license. I get the same error on both machines.
The virtuoso.log and the driver log are available here:
https://github.com/p1d1d1/p1d1d1.github.io/raw/master/logs.zip
I’d really appreciate if you can help debug this!
The Virtuoso
trace_on()
Here I’m not sure I did it correctly. I run trace_on() in isql. Should I use some specific parameter?
I run
trace_on ('dns', sql_send', 'errors')
I get:
16:16:22 DSNL_7 0 connecting to remote 'PostgreSQLdevUnicode' as user : postgres
16:16:29 DSNL_7 0 connecting to remote 'PostgreSQLdevUnicode' as user : postgres
16:16:34 DSNL_0 PostgreSQLdevUnicode select * from "public"."spatial_ref_sys" where 0 = 1
16:16:34 ERRS_0 22005 SR130 Integer 140595754434560 does not fit in column COL_SCALE.I don’t see the ERRS_0 22005 SR130 Integer 140595754434560 does not fit in column COL_SCALE. error in the trace_on() output provided in the zip file?
From the snippet you provided above it appear the spatial_ref_sys table is being queried, what is the schema of this table? As I assume it is values in the COL_SCALE column of the table that do not fit into the integer type Virtuoso has mapped COL_SCALE to, thus what is its datatype in Postgres?
I don’t see any column COL_SCALE in the source tables. I get the same error independently of the table.
Example 1:
With the following table definition:
Example 2:
With the following table definition:
PS: does Virtuoso has a max number of columns defined somewhere?
I am going to confirm with development but I think COL_SCALE is actually defined in Virtuoso and probably stored the scale of a column, which it seems the PostgreSQL ODBC driver you are using is returning as these enormous integer values, i.e., 140595754434560, 139715286138880, etc., which as indicate do not “fit”, i.e., are too large for a scale value.
Could this be an issue in the PostgreSQL ODBC driver itself or with building the PostgreSQL ODBC Driver with iODBC?
As suggested previously, I would recommend using the OpenLink UDA PostgreSQL ODBC Driver, which has been tested with Virtuoso, and is available for free evaluation …
In the ODBC trace provided previously the table being attached is "vd"."gemeinde", can you please provide the schema for that table and also a few sample rows of data for analysis. Please also provide this is text form so it can be copy and paste to create table locally.
Also, what is the version of PostgreSQL being used (SELECT version()) and a list of all installed extensions (SELECT * FROM pg_available_extensions).
Please post the SQL DDL in text form. That will simplify schema recreation effort.
Just do:
SQL Text
As suggested previously I would recommend using the OpenLink UDA PostgreSQL ODBC Driver , which has been tested with Virtuoso, and is available for free evaluation …
I’m giving it a try. Get this error:
VD052: Remote DSN PostgreSQLU: [OpenLink][ODBC][PostgreSQL Server]Connection refused in vdd_dsn_info:(BIF), DB.DBA.vd_remote_data_source
iodbctest returns me:
SQLDriverConnect = [OpenLink] [ODBC] [PostgreSQL Server] Connection refused (111) SQLSTATE=HY000
ODBC_Connect = [OpenLink] [ODBC] [PostgreSQL Server] Connection refused (111) SQLSTATE=HY000
As suggested previously I would recommend using the OpenLink UDA PostgreSQL ODBC Driver , which has been tested with Virtuoso, and is available for free evaluation …
Assuming I am on PostgreSQL 9.4.22, could you please tell me which is the right/needed version of you drivers that will work within the AWS PAGO AMI?
Thanks
Got it with your drivers. Not really easy to find the right configuration parameters for the odbc.ini
The documentation on configuring the PostgreSQL Lite Unix ODBC driver is here, and all you need specify are the hostname and port # of the PostgreSQL database server instance as connection options (-H -P) in the odbc.ini file for the DSN (which takes precedence) or as environment variables (PGHOST, PGPORT) in the openlink.ini file …
Do you now have the OpenLink PostgreSQL Unicode ODBC Driver working with Virtuoso and can attach your tables?
I have now working connections and can attach tables.
Could you please tell me which version of you driver should we use for the AWS PAGO AMI?
What is the OpenLink PostgreSQL ODBC driver you downloaded and works on presumably your local Linux test machine ?
We would need to know the Linux glibc version with the command:
rpm -qa | grep glibc
to know which driver to use on Virtuoso AWS PAGO AMI, but I suspect it will be a glibc2.17 system, i.e.,
[hwilliams@ip-10-228-66-119 ~]$ rpm -qa | grep glibc
glibc-headers-2.17-222.el7.x86_64
glibc-2.17-222.el7.x86_64
glibc-devel-2.17-222.el7.x86_64
glibc-common-2.17-222.el7.x86_64
[hwilliams@ip-10-228-66-119 ~]$
In which case you can use our PostgreSQL 9 Linux glibc2.17 ODBC driver from that link … or the glibc2.5 or 2.12 driver would work as it is compatible with later glibc versions …
The question was more: which license?
What do you mean which license, as that is dependant on the actual ODBC Driver you choose to use, which I presume to be our Postgres Lite Single Tier ODBC Driver for whatever Linux glibc version you are using as per my previous post.
Or are you interested more in price of licenses for which available offers can be viewed here …