about using the ODBC connection Teiid

[wanglei]

I use Jboss as 7.1.1 and teiid 8.2 create a vdbs in linux server. The VDBs configure is:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>   
<vdb name="MyVDB" version="1">   
<description>My sybiq VDB</description>   
<property name="UseConnectorMetadata" value="cache" />   
<model visible="true" type="PHYSICAL" name="sybiq">   
     <property name="importer.useFullSchemaName" value="true"/>   
     <property name="supports-multi-source-bindings" value="true"/>   
     <source name="sybiq21" translator-name="sybase" connection-jndi-name="java:sybiq21DS"/>   
     <source name="sybiq22" translator-name="sybase" connection-jndi-name="java:sybiq22DS"/>   
</model>   
</vdb>

I want to use the ODBC connection VDBS in the Linux server.

I have installed the unixODBC(unixODBC-2.3.2.tar.gz) and postgresql (postgresql-9.0.3.tar.gz).

I  set up the file "/etc/odbc. Ini" like "http://docs.jboss.org/teiid/7.1.0.Final/client-developers-guide/en-US/html/ODBC.html" . The file "/etc/odbc.ini" content is:

[ConnTeiid]
Driver = /usr/lib/psqlodbc.so
Description = PostgreSQL Data Source
Servername = 192.168.7.85
Port = 35432 
Protocol = 7.4
UserName = user
Password = user
Database = MyVDB
ReadOnly = no
ServerType = Postgres
ConnSettings = 
UseServerSidePrepare=1
ByteaAsLongVarBinary=1
Optimizer=0
Ksqo=0
Trace = No
TraceFile = /var/log/trace.log
Debug = No
DebugFile = /var/log/debug.log

But, I don't know what am I doing this right, then how to validate the ODBC Settings, Who can teach me how to use the ODBC connection Teiid should how to configure.

[rareddy]

UnixODBC comes with sample program called "isql", you can use it to connect to VDB and issue SQL queries.

./isql -dsn ConnTeiid -u {user} -p {password}

if that works, then you can connect whatever client program that can communicate over ODBC.

Ramesh..

[wanglei]

Thanks for your help.

The jboss and teiid is running with -b 192.168.7.85.

But I use command "sql -dsn ConnTeiid -u {user} -p {password} " can't connect to VDB.

[root@dp1 data]# isql  -dsn ConnTeiid -p user -u user 

**********************************************
* unixODBC - isql                            *
**********************************************
* Syntax                                     *
*                                            *
*      isql DSN [UID [PWD]] [options]        *
*                                            *
* Options                                    *
*                                            *
* -b         batch.(no prompting etc)        *
* -dx        delimit columns with x          *
* -x0xXX     delimit columns with XX, where  *
*            x is in hex, ie 0x09 is tab     *
* -w         wrap results in an HTML table   *
* -c         column names on first row.      *
*            (only used when -d)             *
* -mn        limit column display width to n *
* -v         verbose.                        *
* -lx        set locale to x                 *
* -q         wrap char fields in dquotes     *
* -3         Use ODBC 3 calls                *
* -n         Use new line processing         *
* -e         Use SQLExecDirect not Prepare   *
* -k         Use SQLDriverConnect            *
* --version  version                         *
*                                            *
* Commands                                   *
*                                            *
* help - list tables                         *
* help table - list columns in table         *
* help help - list all help options          *
*                                            *
* Examples                                   *
*                                            *
*      isql WebDB MyID MyPWD -w < My.sql     *
*                                            *
*      Each line in My.sql must contain      *
*      exactly 1 SQL command except for the  *
*      last line which must be blank (unless *
*      -n option specified).                 *
*                                            *
* Please visit;                              *
*                                            *
*      http://www.unixodbc.org               *
*      [e-mail]                      *
*      [e-mail]              *
**********************************************

[root@dp1 data]#
[root@dp1 data]# isql  ConnTeiid  user  user         
[ISQL]ERROR: Could not SQLConnect
[root@dp1 data]#

Where is my wrong Settings?

[rareddy]

try "isql -v ConnTeiid user user" for detailed error

[wanglei]

The detailed error is:

[root@dp1 ~]# isql -v ConnTeiid user user
[28000][unixODBC]Unknown authentication type
[ISQL]ERROR: Could not SQLConnect
[root@dp1 ~]#

Which is caused by this problem?

[rareddy]

Does your JDBC connection works? Are there any errors on server log?

[wanglei]

The jboss and teiid is running , no error log.

JDBC connection ?  not is the jboss and teiid ?

I can connect to teiid with Java code use the JDBC .Because I want to use  "set partialResultsMode true" in  code . 

On the Windows 7 operating system, I can use the ODBC( psqlodbc) connection to this teiid.

[rareddy]

So it has to be your installation of driver on the Linux then. Which version of Linux and how did you install the postgresql driver?

[wanglei]

Thanks for your help, I know where is the problem.

I need to install the unixODBC, postgresql, psqlodbc, but I only installed the unixODBC psqlodbc, postgresql without installation,  I am  installed psqlodbc now .

After I install the psqlodbc,  use the command "isql -v ConnTeiid user user" is ok:

[root@dp1 ~]# isql -v ConnTeiid user user
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> quit
[root@dp1 ~]#

Thank you very much for your guidance

[ravindra_singh]

Hi Lie,

Can you share your latest odbc.ini setup