8 Replies Latest reply on May 29, 2014 7:01 AM by Steven Hawkins

    Teiid sql query with chinese character

    yang liu Newbie

      I want to ask a question about chinese character, When I create a query like this:

      SELECT teiid2602.KC.{**some chinese character**} FROM teiid2602.KC WHERE teiid2602.KC.id is not null;

      the teiid system returns the message:

      odbc_exec(): SQL error: [unixODBC]ERROR: TEIID30504 KK: 1054 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.`mobile`, g_0.`{**some chinese character**}` FROM `teiid2602`.`KC` AS g_0]
      org
      .teiid.jdbc.TeiidSQLException: TEIID30504 KK: 1054 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.`mobile`, g_0.`{**some chinese character**}` FROM `teiid2602`.`KC` AS g_0];
      Error while executing the query, SQL state S0022 in SQLExecDirect

       

      but if I query like this:

      SELECT teiid2602.KC.abc as {some chinese character} FROM teiid2602.KC WHERE teiid2602.KC.id is not null;


      the query can return result, so I wonder how can I fix this encoding problem, thanks.

        • 1. Re: Teiid sql query with chinese character
          Steven Hawkins Master

          What is the target database?  And how is the table defined there?

           

          > but if I query like this:

           

          That appears like at least the Teiid metadata has been changed so that the column name is abc?  Is the source column name still the chinese character or has it also been changed to abc?

           

          In general here you can turn up logging to debug to see the difference in the source sql generated each way.

          • 2. Re: Teiid sql query with chinese character
            yang liu Newbie

            Hi Steven,

             

            my target db is mysql, and the table defined like this

             

            CREATE TABLE `KC` (

            `id` int(11) NOT NULL AUTO_INCREMENT,

            `invoiceno` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

            `買家會員名` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

            `买家支付宝账号` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

            `买家应付货款` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

            `mobile` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,

            `invoicedate` date DEFAULT NULL,

            `paymentdate` datetime DEFAULT NULL,

            PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=239553 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci$$

             

            > but if I query like this:

             

            this part I didnot change the chinese metadata field to english, it is just a alias, the source column is still chinese

            • 3. Re: Teiid sql query with chinese character
              yang liu Newbie

              Hi Steven,


              And I've logged the sql which teiid processing, the chinese char will become ???, like this:


              SELECT teiid2602.KC.??? FROM teiid2602.KC WHERE teiid2602.KC.id is not null;


              and if I user select *:


              select * from teiid2602.KC where teiid2602.KC.id si not null;


              it also get errors like this

              20:06:40,883 WARN [org.teiid.PROCESSOR] (Worker32_QueryProcessorQueue413) clVpk8Tk122/ TEIID30020 Processing exception for request clVpk8Tk122/.3 'TEIID30504 KK: 1054 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.`id`, g_0.`invoiceno`, g_0.`?????`, g_0.`???????`, g_0.`??????`, g_0.`??????`, g_0.`??????`, g_0.`totalamount`, g_0.`????`, g_0.`????????`, g_0.`????????`, g_0.`????`, g_0.`name`, g_0.`????`, g_0.`????`, g_0.`mobile`, g_0.`invoicedate`, g_0.`paymentdate`, g_0.`????`, g_0.`????`, g_0.`????`, g_0.`quantity`, g_0.`??Id`, g_0.`????`, g_0.`status`, g_0.`unknown`, g_0.`unknown2`, g_0.`unknown3`, g_0.`mobileorder`, g_0.`??????tmp`, g_0.`??????tmp` FROM `teiid2602`.`KC` AS g_0 WHERE g_0.`id` IS NOT NULL]'. Originally TeiidProcessingException 'Unknown column 'g_0.?????' in 'field list'' sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method). Enable more detailed logging to see the entire stacktrace.

              database:0417DB_1

              [unixODBC]�时间tmp`, g_0.`订单付款时间tmp` FROM `teiid2602`.`KC` AS g_0 WHERE g_0.`id` IS NOT NULL]; Error while executing the query

              connectionResource id #4

              S0022

              Error execute SQL: select * from teiid2602.KC where teiid2602.KC.id is not null;

              :

               

              • 4. Re: Teiid sql query with chinese character
                Steven Hawkins Master

                > this part I didnot change the chinese metadata field to english, it is just a alias, the source column is still chinese

                 

                Are you showing that if you use a Chinese alias with an English column name, then it works as expected?  That abc is just an example and not meant to be the same query as the original one.

                 

                > it also get errors like this


                Let's start with the single column case.

                 

                SQL state S0022

                 

                Indicates that there is an invalid column name.

                 

                If you issue the query from the Teiid error message:

                 

                SELECT g_0.`mobile`, g_0.`{**some chinese character**}` FROM `teiid2602`.`KC` AS g_0

                 

                Directly against the source using the same ODBC client that the server is using, do you get the same error?  And in general does this source SQL seem appropriate or is it not the correct chinese name?

                • 5. Re: Teiid sql query with chinese character
                  yang liu Newbie

                  Hi Steven,

                   

                  thanks for your reply. about this one

                  ->Directly against the source using the same ODBC client that the server is using, do you get the same error?  And in general does this source SQL seem appropriate or is it not the correct chinese name?

                   

                  do you mean maybe the error may caused by odbc connection?

                  1.I've tried to write the sql in the program directly instead of fetch the query statement in db, but it still not wok

                  2. Actually I am using PHP to write program and I tried odbc extension and pgsql extention to query teiid vdb, if I query this one: 

                  select * from teiid2602.KC where teiid2602.KC.id si not null;  ( there are some chinese column in the table);

                  both of them are failed, and I check the server.log in the teiid instance, the error sql is like this:

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

                  20:06:40,883 WARN [org.teiid.PROCESSOR] (Worker32_QueryProcessorQueue413) clVpk8Tk122/ TEIID30020 Processing exception for request clVpk8Tk122/.3 'TEIID30504 KK: 1054 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.`id`, g_0.`invoiceno`, g_0.`?????`, g_0.`???????`, g_0.`??????`, g_0.`??????`, g_0.`??????`, g_0.`totalamount`, g_0.`????`, g_0.`????????`, g_0.`????????`, g_0.`????`, g_0.`name`, g_0.`????`, g_0.`????`, g_0.`mobile`, g_0.`invoicedate`, g_0.`paymentdate`, g_0.`????`, g_0.`????`, g_0.`????`, g_0.`quantity`, g_0.`??Id`, g_0.`????`, g_0.`status`, g_0.`unknown`, g_0.`unknown2`, g_0.`unknown3`, g_0.`mobileorder`, g_0.`??????tmp`, g_0.`??????tmp` FROM `teiid2602`.`KC` AS g_0 WHERE g_0.`id` IS NOT NULL]'. Originally TeiidProcessingException 'Unknown column 'g_0.?????' in 'field list'' sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method). Enable more detailed logging to see the entire stacktrace.

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

                   

                  and when I using php odbc extension, the error message in webpage like this:

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

                  [unixODBC]�时间tmp`, g_0.`订单付款时间tmp` FROM `teiid2602`.`KC` AS g_0 WHERE g_0.`id` IS NOT NULL];

                  Error while executing the query

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

                   

                  and when I using php pgsql(postgre) extension, the error message in webpage like this:

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

                  pg_exec(): Query failed: ERROR: TEIID30504 KK: 1054 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.`id`, g_0.`invoiceno`, g_0.`買家會員名`, g_0.`买家支付宝账号`, g_0.`买家应付货款`, g_0.`买家应付邮费`, g_0.`买家支付积分`, g_0.`totalamount`, g_0.`返点积分`, g_0.`买家实际支付金额`, g_0.`买家实际支付积分`, g_0.`订单状态`, g_0.`name`, g_0.`运送方式`, g_0.`联系电话`, g_0.`mobile`, g_0.`invoicedate`, g_0.`paymentdate`, g_0.`宝贝种类`, g_0.`物流单号`, g_0.`物流公司`, g_0.`quantity`, g_0.`店铺Id`, g_0.`店铺名称`, g_0.`status`, g_0.`unknown`, g_0.`unknown2`, g_0.`unknown3`, g_0.`mobileorder`, g_0.`订单创建时间tmp`, g_0.`订单付款时间tmp` FROM `teiid2602`.`KC` AS g_0 WHERE g_0.`id` IS NOT NULL]

                  DETAIL: org.teiid.jdbc.TeiidSQLException: TEIID30504 KK: 1054 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.`id`, g_0.`invoiceno`, g_0.`買家會員名`, g_0.`买家支付宝账号`, g_0.`买家应付货款`, g_0.`买家应付邮费`, g_0.`买家支付积分`, g_0.`totalamount`, g_0.`返点积分`, g_0.`买家实际支付金额`, g_0.`买家实际支付积分`, g_0.`订单状态`, g_0.`name`, g_0.`运送方式`, g_0.`联系电话`, g_0.`mobile`, g_0.`invoicedate`, g_0.`paymentdate`, g_0.`宝贝种类`, g_0.`物流单号`, g_0.`物流公司`, g_0.`quantity`, g_0.`店铺Id`, g_0.`店铺名称`, g_0.`status`, g_0.`unknown`, g_0.`unknown2`, g_0.`unknown3`, g_0.`mobileorder`, g_0.`订单创建时间tmp`, g_0.`订单付款时间tmp` FROM `teiid2602`.`KC` AS g_0 WHERE g_0.`id` IS NOT NULL]

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

                   

                   

                  actually I think the 2 return error are the same meaning:--the query can not find the field in teiid whild the query executing.

                   

                  So I wonder whether teiid query support non-english field search, if it support, why my query has errors, thanks.

                  • 6. Re: Teiid sql query with chinese character
                    Steven Hawkins Master

                    > do you mean maybe the error may caused by odbc connection

                     

                    Yes.  At least to me it appears that the source sql should be valid based upon what you see in the error message - in that we are generating a source query whose column names seem to match the source table.  However in the select * case there are columns in the source query that aren't shown in your source table, like unknown, unknown2, `订单创建时间tmp`, etc.  That's why I want to concentrate initially on selecting just a single column. 

                     

                    > 1.I've tried to write the sql in the program directly instead of fetch the query statement in db, but it still not wok

                     

                    Can you elaborate here?  Do you mean that you couldn't make things work even with Teiid removed from the flow?  If that's the case, what exactly was failing?

                     

                    > So I wonder whether teiid query support non-english field search, if it support, why my query has errors, thanks.

                     

                    Yes it is supported.  Note that our parser has accepted the chinese characters and that we've formed a source query with chinese characters.  So the next question is to find out if  the source driver and/or mysql itself.

                     

                    Note that you are getting ???? replacement characters in the log probably due to either what app you are opening it with or if for some reason log4j is set to write in ascii or other non-UTF charset.

                    • 7. Re: Teiid sql query with chinese character
                      yang liu Newbie

                      Hi Steven,

                       

                      Thanks for your reply,

                      1.) I tried to work the flow with Teiid removed, I use php mysql connect the raw db sourse, and I can get the data successfully by the sql with chinese char:

                      select teiid2602.KC.买家货款 from teiid2602.KC where teiid2602.KC.id;

                      2.) so I think the bug may caused by my teiid server encoding setting, I try this sql to fetch the encoding of teiid

                      SHOW SERVER_ENCODING;

                      but the result reutrn like this

                      Array ( [0] => [server_encoding] => )

                      so I'm not sure whether the sql can run in teiid, if the sql is incorrect, I wonder

                       

                      a.) is there any sql in teiid that can return the db instance create schema
                      b.) is there any sql in teiid that can return server encoding of the teiid
                      c.)is there any sql in teiid that can set the server encoding

                       

                      Thanks.

                      • 8. Re: Teiid sql query with chinese character
                        Steven Hawkins Master

                        > I tried to work the flow with Teiid removed, I use php mysql connect the raw db sourse, and I can get the data successfully by the sql with chinese char:

                         

                        Was this issued from the same connection pool on the application server that Teiid would be using?

                         

                        If so, the only difference I see in the query is the lack of quoting.  If not, then what is the difference in how this connection was configured and the data source that you defined in the application server.

                         

                        On 2 and all of the encoding questions, Teiid always uses UTF-8 from the Teiid client to the Teiid server.  There is no option to change that encoding.  Each of the sources Teiid connects to (the data sources you configure on the application server) though must have their encoding and other options set appropriately.