ODBC Connecton to VDB

[romagnolli]

Hi guys,

I'm relatively new Teiid user and this is my first post in this forum, so please excuse me if I'm asking stupid questions

I'm trying to create an ODBC connection to a VDB (located on my local JBoss Server) and after that to start using this ODBC in Excel (for example). I found this article:

Chapter 7. ODBC Support

but when I tested the connection it gave me the following error: "Is the server running on host "my IP address here" and accepting TCP/IP connections on port 5432?"

Do you know how can I fix this...?

Cheers,

romagnolli

[rareddy]

Welcome to Teiid forums.

Try the port 35432 instead of 5432.

Ramesh..

[romagnolli]

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

[rareddy]

Do you have "AdventureWorks2016" vdb in Teiid? The database name is == vdb name in Teiid. You also need to make sure the VDB is deployed, valid and active before you can connect.

[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)......

[rareddy]

I would think Excel uses same DSN to query the Teiid

I recommend gathering the debug log file and provide us with that error to see what is wrong.

[romagnolli]

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

[rareddy]

The issue is driver is sending a statement like

SET DateStyle=

without the value, where the value is expected. I am not sure what sets this value on ODBC client. What version of driver are you using?

One option is Teiid supporting without valid value, but not sure yet.

[romagnolli]

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

[rareddy]

Can you also post the server side exception perhaps?

[romagnolli]

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..

[rareddy]

Were there any "." in your table/column names or any other non [a-z] chars? What is the exact error?

[romagnolli]

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

[romagnolli]

Hi Ramesh,

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

Cheers,

Ivan

[rhn-engineering-rareddy]

I have not had time to take look yet. Did you see any errors in the sever side (may be run in debug)? Typically we saw issues with Excel when there are quotes("") and dot (.) in schema. i.e your table/column names. For your picture that seems to be not the case. Also any different information in the ODBC log other than what provided in previous comments?