3 Replies Latest reply on Dec 9, 2014 9:28 PM by shawkins

    Using User-Defined Variables in Teiid-SQL Engine

    kisienya
    kisienya Dec 9, 2014 5:34 PM

      Teiid returns errors when the following queries are executed on VDB

       

      SELECT @latsBatchID := MAX(BatchID) FROM ltcbatch;

       

      Error

      Remote org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "SELECT @latsBatchID [*]:[*]= MAX" at line 1, column 21.

      Was expecting: <EOF> | "and" | "as" | "between" | "except" | "fetch" | "from" | "in" | "intersect" | "into" ...

       

      set @firstname = 'xxxxx;

       

      Error

      Remote org.teiid.api.exception.query.QueryParserException: TEIID31100 Parsing error: Encountered "[*]set[*] @firstname =" at line 1, column 1.

      Was expecting: "alter" | "begin" | "call" | "create" | "delete" | "drop" | "exec" | "execute" | "insert" | "merge" ...

       

      How do i get to run such queries, in essence how can i pass a value to a variable or use user-defined variables in Teiid-SQL Engine?

      • 296 Views
      • Tags:
        • 1. Re: Using User-Defined Variables in Teiid-SQL Engine
          rareddy
          rareddy Dec 9, 2014 5:52 PM (in response to kisienya)

          Which language you are trying to call these statements from? In Java you need to look into prepared statements.

           

          there is no operator in SQL like := or @, thus above parsing errors.

           

          May be you can explain what you are trying accomplish then we can give little clear answer on how to achieve it.

           

          Ramesh..

            • Actions
          • 2. Re: Using User-Defined Variables in Teiid-SQL Engine
            kisienya
            kisienya Dec 9, 2014 6:53 PM (in response to rareddy)

            Thanks Ramesh for your prompt reply

             

            I have a VDB with two data sources (CSV and MySQL), I need to insert data from CSV into MySQL.

             

            I need to select the last entry in a column BatchID and increment it by 1 every time i run the query.

             

            See below, what I mean by :=, where the value of MAX(BatchID) is assigned to variable lastBatchID. I can then  increment it as lastBatchID=lastBatchID + 1

             

            mysql> SELECT @lastBatchID := MAX(BatchID) FROM ltcbatch;

             

            +------------------------------+

             

            | @lastBatchID := MAX(BatchID) |

             

            +------------------------------+

             

            |                         3559 |

             

            +------------------------------+

             

            1 row in set (0.00 sec)

             

            Cheers

              • Actions
            • 3. Re: Using User-Defined Variables in Teiid-SQL Engine
              shawkins
              shawkins Dec 9, 2014 9:28 PM (in response to kisienya)

              There are session scoped variables accessible via the teiid_session_set and teiid_session_get functions -  System Functions - Teiid 8.10 (draft) - Project Documentation Editor

               

              Other than that if you can run your queries in the context of an anonymous procedure block, then you can use procedure logic.

                • Actions