Using User-Defined Variables in Teiid-SQL Engine

[kisienya]

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?

[rareddy]

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

[kisienya]

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

[shawkins]

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.