9 Replies Latest reply on Mar 2, 2016 1:15 AM by Ravindra Singh

    about using the ODBC connection Teiid

    lei wang Newbie

      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.

        • 1. Re: about using the ODBC connection Teiid
          Ramesh Reddy Master

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

          • 2. Re: Re: about using the ODBC connection Teiid
            lei wang Newbie

            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               *
            *      nick@lurcher.org                      *
            *      pharvey@codebydesign.com              *
            **********************************************
            
            [root@dp1 data]#
            [root@dp1 data]# isql  ConnTeiid  user  user         
            [ISQL]ERROR: Could not SQLConnect
            [root@dp1 data]#
            
            

            Where is my wrong Settings?

            • 3. Re: Re: about using the ODBC connection Teiid
              Ramesh Reddy Master

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

              1 of 1 people found this helpful
              • 4. Re: Re: Re: about using the ODBC connection Teiid
                lei wang Newbie

                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?

                • 5. Re: Re: Re: about using the ODBC connection Teiid
                  Ramesh Reddy Master

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

                  • 6. Re: about using the ODBC connection Teiid
                    lei wang Newbie

                    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.

                    • 7. Re: about using the ODBC connection Teiid
                      Ramesh Reddy Master

                      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?

                      • 8. Re: Re: about using the ODBC connection Teiid
                        lei wang Newbie

                        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

                        • 9. Re: about using the ODBC connection Teiid
                          Ravindra Singh Newbie

                          Hi Lie,

                           

                          Can you share your latest odbc.ini setup