Docs "tip" about direct Postgres connection

[m.ardito]

hi,

i'm reading teiid 8.2 manual (client guide), and i read, at the bottom of this page :

"Tip

You can also use languages like Perl, Python, C/C++ with ODBC ports to Postgres, or if they have direct Postgres connection modules you can use them too to connect Teiid and issue queries an retrieve results."

does this mean that i have some way to connect to Teiid with postgresql JDBC too (eg: squirrel - i know i can use teiid jdbc driver and it works, it's just an example...)?  Or with standard postgres management tools like pgAdmin?

Thanks!

Marco

[rareddy]

Yes, we have tested the JDBC driver, pgAdmin in limited fashion. Let us know if you see any issues.

[m.ardito]

thanks, well i tried both and failed with both :-)

i left all the settings as default waiting for some confirmation from the forum because there are so many of them!

DB) on localhost teeid instance i have a testmix xml vdb with 2 models: testmixMS (sql server) and  testmixMY (mysql) which works (now) perfectly both with JDBC and ODBC (squirrel, msaccess, msexcel) which gives me SQL access to objects like

- TestmixMS.ApiMn.dbo.MA_Authors

or

- TestmixMY.conti_contratti

1) squirrel 3.4.0 w/Postgresql JDBC postgresql-9.2-1002.jdbc4.jar

i used the connection string: jdbc:postgresql://localhost:35432/testmix with user/user

i left the connection "properties" as default and

- the connection is established, no errors no logs

- in the squirrel connection objects tree, though i have nothing (i see all object categories but they're all empty items!)

- i see lots of "metadata" tab fields filled

- switching to "status" tab i get a few fields filled but also (only the first time i open the tab) a log like

"Error: ERROR: TEIID31100 Parsing error: Encountered "[*]SHOW[*] TRANSACTION ISOLATION" at line 1, column 1.

Was expecting: "alter" | "call" | "create" | "delete" | "drop" | "exec" | "execute" | "insert" | "select" | "table" ...

  Detail: org.teiid.jdbc.TeiidSQLException: TEIID31100 Parsing error: Encountered "[*]SHOW[*] TRANSACTION ISOLATION" at line 1, column 1.

Was expecting: "alter" | "call" | "create" | "delete" | "drop" | "exec" | "execute" | "insert" | "select" | "table" ...

SQLState:  50000

ErrorCode: 0"

- switching to "schemas" tab i get nothing.

- "table types" is filled with values, no errors

- "data types" nothing, no errors

- all other tabs, except "locks", perhaps obviously, is filled with values

2) pgAdmin III 1.16.1 (win7x64), same vdb, same user, same host, all default options

- connection seems to happen but i instantly get the same error as squirrel

"Error: ERROR: TEIID31100 Parsing error: Encountered "[*]SHOW[*] TRANSACTION ISOLATION" at line 1, column 1.

Was expecting: "alter" | "call" | "create" | "delete" | "drop" | "exec" | "execute" | "insert" | "select" | "table" ...

  Detail: org.teiid.jdbc.TeiidSQLException: TEIID31100 Parsing error: Encountered "[*]SHOW[*] TRANSACTION ISOLATION" at line 1, column 1.

Was expecting: "alter" | "call" | "create" | "delete" | "drop" | "exec" | "execute" | "insert" | "select" | "table" ...

SQLState:  50000

ErrorCode: 0"

and closing the msgbox lots of error msgboxes about other missing things like "column not found in pgset: "datalastsysoid"

so i basically think something fundamental is missing in both ways... but can't figure out what or why

i could try to configure "connection settings" to mimic the odbc config (datasource page1/page2) but there are too many possibilities...

let me know how can i help you to help me! :-D

Marco

[shawkins]

> 1) squirrel 3.4.0 w/Postgresql JDBC postgresql-9.2-1002.jdbc4.jar

Before going too far down this path, there aren't many places where it makes sense to use the pg jdbc driver instead of the Teiid jdbc driver.  We have verified basic support with the pg JDBC driver up to the pg 9.1-901 version - which is used in our unit tests to exersize the pg emulation layer.  However that doesn't guarentee usage by tools that may be expecting more pg specific SQL support than we current provide - such as SHOW TRANSACTION ISOLATION.

> and closing the msgbox lots of error msgboxes about other missing things like "column not found in pgset: "datalastsysoid"

We do not completely emulate the pg system catalog.  The tables we do provide were initially modeled after support for pg version 8.0 with the corresponding odbc client. And even then the support was mostly to satify built-in metadata queries, so not even all columns on all emulated tables were added.  Our support has been evolving for compatibilty through pg version 8.4 and only minimally for 9.0+. 

Long story short is that our focus here is on the ODBC driver and we will generally not function as a drop in replacement for tooling expecting a pg database - unless there has been a specific customer scenario that has been vetted.  Any issues you encounter specifically with ODBC emulation should be logged as we will want to ensure compatibility.  Issues with the pg JDBC driver would be considered a low priorty.

Steve

[m.ardito]

ok thanks, i understand and agree.  i just was reading that tip in the docs and wondered if some PHP project that could not support ODBC (i've tried some of those in the past here in italian, sorry, and this is the sw i tested) could be used in that way:

in that case, which is just a possibility and not a priority, there could be other ways... (php/odbc-jdbc bridge perhaps)

don't mind... i don't really need it just now...

Thanks,

Marco