Open mdlarussa opened 5 years ago
mdlarussa
commented
5 years ago Trying to pull in the SQL Server information_schema in order to enumerate all columns within an instance. Command on SQL Server side: "select * from INFORMATION_SCHEMA.COLUMNS"
If I use dbo as schema name, import works, but of course the query fails.
juliogonzalez
commented
5 years ago I am pretty sure the problem is table_name '') (no value for table_name param).
Problem seems to be at https://github.com/tds-fdw/tds_fdw/blob/master/src/tds_fdw.c#L3155 (prev_table is null).
Wild guess: are you sure the user you configured for the USER MAPPING has access to INFORMATION_SCHEMA to list tables (and to access each of the tables)? Please verify it.
Of course, if that is the problem this would be still a bug.
On normal conditions, IMPORT SCHEMA works, we even have a test for it: https://github.com/tds-fdw/tds_fdw/blob/master/tests/tests/postgresql/003_import_schema.sql
jcarnu
commented
5 years ago This issue is the same than #219 :
Sorry for duplicating it, I'm working on it and testing the changes on my branch/repo.
It seems to work.
The problem is that INFORMATION_SCHEMAdoes not document itself with its views.
The query you use does return any table. The current source tries to list the columns of a null table. Leading to a mysterious error message with SERVER.
This issue will be soon closed.
version 2.0.0-alpha.3 There appears to be a trailing ")" in the query ....
postgres=# IMPORT FOREIGN SCHEMA information_schema FROM SERVER abcd1234 INTO public; ERROR: syntax error at or near ")" LINE 2: ) SERVER abcd1234 ^ QUERY: SELECT t.table_name, c.column_name, c.data_type, c.column_default, c.is_nullable, c.character_maximum_length, c.numeric_precision, c.numeric_precision_radix, c.numeric_scale, c.datetime_precision FROM INFORMATION_SCHEMA.TABLES t LEFT JOIN INFORMATION_SCHEMA.COLUMNS c ON t.table_schema = c.table_schema AND t.table_name = c.table_name WHERE t.table_type = 'BASE TABLE' AND t.table_schema = 'information_schema' ORDER BY t.table_name, c.ordinal_position ) SERVER abcd1234 OPTIONS (schema_name 'information_schema', table_name '');