Hi Ramesh,

Thank you very much for your quick reply. I tried with port 35432 (also created an outbound rule in Windows Firewall to open this port) , but now I get the following error:

Any idea about that..??

Cheers,

romagnolli

Yes, "AdventureWorks2016" is already deployed on the server and is active - I'm able to connect (and extract data) to this VDB using SQuireL SQL client (using JDBC Teiid driver). I'm also able to connect to the VDB using Excel (menu Data --> From Other Sources --> From Microsoft Query), but I cannot create DSN in ODBC Data Source Administrator (64-bit)......

Not sure whether this is what you need, but that's what I found...:

[7652-0.000]ci=0000000000000000 globals.extra_systable_prefixes = 'dd_'

[7652-0.000]exe name=odbcad32 plaformId=2

[7652-0.000]CC_conninfo_init opt=2

[7652-0.000]copy_globals driver=PostgreSQL Unicode(x64)

[7652-0.031]copy_globals driver=PostgreSQL Unicode(x64)

[7652-0.031]getDSNinfo: DSN= overwrite=0

[7652-0.031]getCommonDefaults:setting ODBC.INI position of 000001A4485DF010

[7652-0.031]ci=000001A4485DF010 globals.extra_systable_prefixes = 'dd_'

[7652-0.031]calling getDSNdefaults

[7652-22.331]EN_add_connection: self = 000001A4485CE140, conn = 000001A4485F9320

[7652-22.332]       added at 0, conn->henv = 000001A4485CE140, conns[0]->henv = 000001A4485CE140

[7652-22.334]copy_globals driver=

[7652-22.334]CC_connect: entering...

[7652-22.334]sslmode=disable

[7652-22.334]LIBPQ_CC_connect: entering...

[7652-22.334]Driver Version='09.05.0400,Jul 30 2016' linking 1800 dynamic Multithread library

[7652-22.350]LIBPQ_CC_connect: DSN = 'PostgreSQL35W', server = '192.168.11.120', port = '35432', database = 'AdventureWorks2016', username = 'teiid', password='xxxxx'

[7652-22.350]connecting to the database using 192.168.11.120 as the server

[7652-22.350]libpq connection to the database established.

[7652-22.350]protocol=3

[7652-22.350]Server version=8.1.4

[7652-22.350]LIBPQ_connect: retuning 1

[7652-22.350]CC_send_settings: entering...

[7652-22.350]PGAPI_AllocStmt: entering...

[7652-22.350]**** PGAPI_AllocStmt: hdbc = 000001A4485F9320, stmt = 000001A448604DA0

[7652-22.350]CC_add_statement: self=000001A4485F9320, stmt=000001A448604DA0

[7652-22.350]PGAPI_ExecDirect: entering...0

[7652-22.350]SC_recycle_statement: self= 000001A448604DA0

[7652-22.350]**** PGAPI_ExecDirect: hstmt=000001A448604DA0, statement='SET DateStyle = 'ISO''

[7652-22.350]PGAPI_ExecDirect: calling PGAPI_Execute...

[7652-22.350]PGAPI_Execute: entering...0

[7652-22.350]PGAPI_Execute: clear errors...

[7652-22.350]PGAPI_NumParams: entering...

[7652-22.350]SC_scanQueryAndCountParams: entering...

[7652-22.350]prepareParameters was not called, prepare state:8

[7652-22.350]SC_recycle_statement: self= 000001A448604DA0

[7652-22.350]Exec_with_parameters_resolved: copying statement params: trans_status=1, len=21, stmt='SET DateStyle = 'ISO''

[7652-22.350]   stmt_with_params = 'SET DateStyle = 'ISO''

[7652-22.350]about to begin SC_execute

[7652-22.350]      it's NOT a select statement: stmt=000001A448604DA0

[7652-22.350]CC_send_query: conn=000001A4485F9320, query='SET DateStyle = 'ISO''

[7652-22.350]in QR_Constructor

[7652-22.350]exit QR_Constructor

[7652-22.365]notice/error message len=208

[7652-22.365]SC_set_Result(48604da0, 486061d0)[7652-22.365]QResult: enter DESTRUCTOR

[7652-22.365]STATEMENT ERROR: func=SC_execute, desc='(null)', errnum=7, errmsg='Error while executing the query'

[7652-22.381]CONN ERROR: func=SC_execute, desc='(null)', errnum=110, errmsg='ERROR: TEIID31100 Parsing error: Encountered "[*]SET[*] DateStyle =" at line 1, column 1.

Was expecting: "alter" | "begin" | "call" | "create" | "delete" | "drop" | "exec" | "execute" | "insert" | "merge" ...'

[7652-22.381]retval=-1

[7652-22.381]PGAPI_ExecDirect: returned -1 from PGAPI_Execute

[7652-22.381]CC_send_settings: result -1, status 0 from 'SET DateStyle = 'ISO''

[7652-22.381]PGAPI_ExecDirect: entering...0

[7652-22.381]SC_recycle_statement: self= 000001A448604DA0

[7652-22.381]SC_set_Result(48604da0, 0)[7652-22.381]QResult: enter DESTRUCTOR

[7652-22.381]QResult: in QR_close_result

[7652-22.381]QResult: free memory in, fcount=0

[7652-22.381]QResult: free memory out

[7652-22.381]QResult: exit close_result

[7652-22.381]QResult: exit DESTRUCTOR

[7652-22.381]PDATA_free_params:  ENTER, self=000001A4486050D0

[7652-22.381]**** PGAPI_ExecDirect: hstmt=000001A448604DA0, statement='SET extra_float_digits = 2'

[7652-22.381]PGAPI_ExecDirect: calling PGAPI_Execute...

[7652-22.381]PGAPI_Execute: entering...0

[7652-22.381]PGAPI_Execute: clear errors...

[7652-22.381]PGAPI_NumParams: entering...

[7652-22.381]SC_scanQueryAndCountParams: entering...

[7652-22.381]prepareParameters was not called, prepare state:8

[7652-22.381]SC_recycle_statement: self= 000001A448604DA0

[7652-22.381]PDATA_free_params:  ENTER, self=000001A4486050D0

[7652-22.381]Exec_with_parameters_resolved: copying statement params: trans_status=1, len=26, stmt='SET extra_float_digits = 2'

[7652-22.381]   stmt_with_params = 'SET extra_float_digits = 2'

[7652-22.381]about to begin SC_execute

[7652-22.381]      it's NOT a select statement: stmt=000001A448604DA0

[7652-22.381]CC_send_query: conn=000001A4485F9320, query='SET extra_float_digits = 2'

[7652-22.381]in QR_Constructor

[7652-22.381]exit QR_Constructor

[7652-22.381]notice/error message len=217

[7652-22.381]SC_set_Result(48604da0, 486061d0)[7652-22.381]QResult: enter DESTRUCTOR

[7652-22.381]STATEMENT ERROR: func=SC_execute, desc='(null)', errnum=7, errmsg='Error while executing the query'

[7652-22.397]CONN ERROR: func=SC_execute, desc='(null)', errnum=110, errmsg='ERROR: TEIID31100 Parsing error: Encountered "[*]SET[*] DateStyle =" at line 1, column 1.

Was expecting: "alter" | "begin" | "call" | "create" | "delete" | "drop" | "exec" | "execute" | "insert" | "merge" ...'

[7652-22.397]retval=-1

[7652-22.397]PGAPI_ExecDirect: returned -1 from PGAPI_Execute

[7652-22.397]CC_send_settings: result -1, status 0 from 'SET extra_float_digits = 2'

[7652-22.397]PGAPI_FreeStmt: entering...hstmt=000001A448604DA0, fOption=1

[7652-22.397]QResult: enter DESTRUCTOR

[7652-22.397]QResult: in QR_close_result

[7652-22.397]QResult: free memory in, fcount=0

[7652-22.397]QResult: free memory out

[7652-22.397]QResult: exit close_result

[7652-22.397]QResult: exit DESTRUCTOR

[7652-22.397]SC_init_Result(48604da0)[7652-22.397]SC_Destructor: self=000001A448604DA0, self->result=0000000000000000, self->hdbc=000001A4485F9320

[7652-22.397]APD_free_params:  ENTER, self=000001A448604F70

[7652-22.397]IPD_free_params:  ENTER, self=000001A448604FE0

[7652-22.397]PDATA_free_params:  ENTER, self=000001A4486050D0

[7652-22.397]SC_Destructor: EXIT

[7652-22.397]CC_send_settings: entering...

[7652-22.397]CC_send_settings: entering...

[7652-22.397]CC_lookup_lo: entering...

[7652-22.397]CC_send_query: conn=000001A4485F9320, query='select oid, typbasetype from pg_type where typname = 'lo''

[7652-22.397]in QR_Constructor

[7652-22.397]exit QR_Constructor

[7652-22.412]num_fields = 2

[7652-22.412]in QR_set_num_fields

[7652-22.412]exit QR_set_num_fields

[7652-22.412]QR_from_PGResult: fieldname='oid', adtid=23, adtsize=15, atttypmod=11 (rel,att)=(806,1)

[7652-22.412]QR_from_PGResult: fieldname='typbasetype', adtid=23, adtsize=15, atttypmod=11 (rel,att)=(806,7)

[7652-22.412]REALLOC: old_count = 0, size = 0

[7652-22.412]qresult: len=5, buffer='14939'

[7652-22.412]qresult: len=1, buffer='0'

[7652-22.412]QResult: enter DESTRUCTOR

[7652-22.412]QResult: in QR_close_result

[7652-22.412]QResult: free memory in, fcount=1

[7652-22.412]QResult: free memory out

[7652-22.412]QResult: exit close_result

[7652-22.412]QResult: exit DESTRUCTOR

[7652-22.412]Got the large object oid: 14939

[7652-22.412]CC_lookup_characterset: entering...

[7652-22.412]conn->unicode=1

[7652-22.412]CC_connect: returning...1

[7652-22.412]CC_error_statements: self=000001A4485F9320

[7652-22.412]CONN ERROR: func=CC_connect, desc='', errnum=-1, errmsg='ERROR: TEIID31100 Parsing error: Encountered "[*]SET[*] DateStyle =" at line 1, column 1.

Was expecting: "alter" | "begin" | "call" | "create" | "delete" | "drop" | "exec" | "execute" | "insert" | "merge" ...'

[7652-22.428]enter CC_get_error

[7652-22.428]exit CC_get_error

[7652-23.277]enter CC_Destructor, self=000001A4485F9320

[7652-23.278]in CC_Cleanup, self=000001A4485F9320

[7652-23.278]after PQfinish

[7652-23.278]CC_conninfo_init opt=1

[7652-23.278]exit CC_Cleanup

[7652-23.279]after CC_Cleanup

[7652-23.279]after free statement holders

[7652-23.279]exit CC_Destructor

[7652-23.279]in EN_Destructor, self=000001A4485CE140

[7652-23.279]clearing conns count=128

[7652-23.280]exit EN_Destructor: rv = 1

[7652-66.798]DETACHING PROCESS

---------------------------------------------------------------------------------------------

[0.031]DSN info: DSN='PostgreSQL35W',server='',port='',dbase='',user='',passwd=''

[0.031]          onlyread='',showoid='',fakeoidindex='',showsystable=''

[0.031]          conn_settings='(null)', conn_encoding='(null)'

[0.031]          translation_dll='',translation_option=''

[22.334]Driver Version='09.05.0400,Jul 30 2016' linking 1800 dynamic Multithread library

[22.334]Global Options: fetch=100, unknown_sizes=0, max_varchar_size=255, max_longvarchar_size=8190

[22.334]                unique_index=1, use_declarefetch=0

[22.334]                text_as_longvarchar=1, unknowns_as_longvarchar=0, bools_as_char=1 NAMEDATALEN=64

[22.350]                extra_systable_prefixes='dd_', conn_settings='(null)' conn_encoding=''

[22.350]conn=000001A4485F9320, query='SET DateStyle = 'ISO''

[22.365]STATEMENT ERROR: func=SC_execute, desc='(null)', errnum=7, errmsg='Error while executing the query'

[22.365]                 ------------------------------------------------------------

[22.365]                 hdbc=000001A4485F9320, stmt=000001A448604DA0, result=000001A4486061D0

[22.365]                 prepare=8, internal=1

[22.365]                 bindings=0000000000000000, bindings_allocated=0

[22.365]                 parameters=0000000000000000, parameters_allocated=0

[22.365]                 statement_type=21, statement='SET DateStyle = 'ISO''

[22.365]                 stmt_with_params='SET DateStyle = 'ISO''

[22.365]                 data_at_exec=-1, current_exec_param=-1, put_data=0

[22.365]                 currTuple=-1, current_col=-1, lobj_fd=-1

[22.365]                 maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0, scroll_concurrency=1

[22.365]                 cursor_name='SQL_CUR000001A448604DA0'

[22.365]                 ----------------QResult Info -------------------------------

[22.365]                 fields=000001A4485CB3D0, backend_tuples=0000000000000000, tupleField=0, conn=0000000000000000

[22.365]                 fetch_count=0, num_total_rows=0, num_fields=0, cursor='(NULL)'

[22.365]                 message='ERROR: TEIID31100 Parsing error: Encountered "[*]SET[*] DateStyle =" at line 1, column 1.

Was expecting: "alter" | "begin" | "call" | "create" | "delete" | "drop" | "exec" | "execute" | "insert" | "merge" ...', command='(NULL)', notice='(NULL)'

[22.365]                 status=7

[22.365]CONN ERROR: func=SC_execute, desc='(null)', errnum=110, errmsg='ERROR: TEIID31100 Parsing error: Encountered "[*]SET[*] DateStyle =" at line 1, column 1.

Was expecting: "alter" | "begin" | "call" | "create" | "delete" | "drop" | "exec" | "execute" | "insert" | "merge" ...'

[22.381]            ------------------------------------------------------------

[22.381]            henv=000001A4485CE140, conn=000001A4485F9320, status=0, num_stmts=16

[22.381]            pqconn=000001A4485FB2A0, stmts=000001A4485EA530, lobj_type=-999

[22.381]conn=000001A4485F9320, query='SET extra_float_digits = 2'

[22.381]STATEMENT ERROR: func=SC_execute, desc='(null)', errnum=7, errmsg='Error while executing the query'

[22.381]                 ------------------------------------------------------------

[22.381]                 hdbc=000001A4485F9320, stmt=000001A448604DA0, result=000001A4486061D0

[22.397]                 prepare=8, internal=1

[22.397]                 bindings=0000000000000000, bindings_allocated=0

[22.397]                 parameters=0000000000000000, parameters_allocated=0

[22.397]                 statement_type=21, statement='SET extra_float_digits = 2'

[22.397]                 stmt_with_params='SET extra_float_digits = 2'

[22.397]                 data_at_exec=-1, current_exec_param=-1, put_data=0

[22.397]                 currTuple=-1, current_col=-1, lobj_fd=-1

[22.397]                 maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0, scroll_concurrency=1

[22.397]                 cursor_name='SQL_CUR000001A448604DA0'

[22.397]                 ----------------QResult Info -------------------------------

[22.397]                 fields=000001A4485CB1D0, backend_tuples=0000000000000000, tupleField=0, conn=0000000000000000

[22.397]                 fetch_count=0, num_total_rows=0, num_fields=0, cursor='(NULL)'

[22.397]                 message='ERROR: TEIID31100 Parsing error: Encountered "[*]SET[*] extra_float_digits =" at line 1, column 1.

Was expecting: "alter" | "begin" | "call" | "create" | "delete" | "drop" | "exec" | "execute" | "insert" | "merge" ...', command='(NULL)', notice='(NULL)'

[22.397]                 status=7

[22.397]CONN ERROR: func=SC_execute, desc='(null)', errnum=110, errmsg='ERROR: TEIID31100 Parsing error: Encountered "[*]SET[*] DateStyle =" at line 1, column 1.

Was expecting: "alter" | "begin" | "call" | "create" | "delete" | "drop" | "exec" | "execute" | "insert" | "merge" ...'

[22.397]            ------------------------------------------------------------

[22.397]            henv=000001A4485CE140, conn=000001A4485F9320, status=0, num_stmts=16

[22.397]            pqconn=000001A4485FB2A0, stmts=000001A4485EA530, lobj_type=-999

[22.397]conn=000001A4485F9320, query='select oid, typbasetype from pg_type where typname = 'lo''

[22.412]    [ Large Object oid = 14939 ]

[22.412]    [ Client encoding = 'UTF8' (code = 6) ]

[22.412]CONN ERROR: func=CC_connect, desc='', errnum=-1, errmsg='ERROR: TEIID31100 Parsing error: Encountered "[*]SET[*] DateStyle =" at line 1, column 1.

Was expecting: "alter" | "begin" | "call" | "create" | "delete" | "drop" | "exec" | "execute" | "insert" | "merge" ...'

[22.428]            ------------------------------------------------------------

[22.428]            henv=000001A4485CE140, conn=000001A4485F9320, status=1, num_stmts=16

[22.428]            pqconn=000001A4485FB2A0, stmts=000001A4485EA530, lobj_type=14939

I'm using this version of the driver: "psqlodbc_09_05_0400-x64" downloaded from here: PostgreSQL: File Browser

Looks like it's really a driver issue - I've installed and older version of the PostgreSQL diver (psqlodbc_09_05_0200-x64) and I managed successfully to create the DSN.

Then I tried to use this ODBC in MS Access and everything worked well and I managed to import data from VDB.....But when I tried to use the ODBC in Excel I got an error - the connection to the VDB was successful, I see all the table in the database, but the error message says that these tables don't have columns...!! very strange..

This is the error I got when I tried to create an ODBC query in MS Excel:

Hi Ramesh,

Do you have any idea what is causing this error and probably how to fix it or workaround it...?

Cheers,

Ivan