[teiid-issues] [JBoss JIRA] (TEIID-5403) Support the use of variables in client SQL query

[Steven Hawkins (JIRA)]

     [ https://issues.jboss.org/browse/TEIID-5403?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Steven Hawkins resolved TEIID-5403.
-----------------------------------
    Resolution: Explained


If you want to issue this as a single statement, it can be enclosed as a anonymous procedure block which can also be directly sent from the client:

{code}
BEGIN
  DECLARE string PARAM1 = cast(? as string);
  SELECT ... 
    WHERE COLUMN1 = PARAM1
    ) T1 ON T1.COLUMN2 = TABLEA.COLUMN3
   WHERE TABLEA.COLUMN4 = PARAM1
  ...;
{code}

Note the cast of the bind variable, which is due to a small issue with the resolver that isn't inferring the type from the variable declaration.

You may also use the postgresql like feature (which is undocumented for our jdbc interface) of using $n bindings - where is the parameter number:

{code}
...
WHERE COLUMN1 = $1
) T1 ON T1.COLUMN2 = TABLEA.COLUMN3
WHERE TABLEA.COLUMN4 = $1
...
{code}

> Support the use of variables in client SQL query
> ------------------------------------------------
>
>                 Key: TEIID-5403
>                 URL: https://issues.jboss.org/browse/TEIID-5403
>             Project: Teiid
>          Issue Type: Enhancement
>            Reporter: SHI HONG CHIN
>            Assignee: Steven Hawkins
>
> In Microsoft SQL Server, when writing SQL query, I can directly declare and set local variables, and then use it as much as I like in the rest of SQL query.
> Example:
> {code:java}
> DECLARE @PARAM1 VARCHAR(100);
> SET @PARAM1 = ?;
> SELECT
> 	<SOME COLUMNS>
> FROM TABLEA LEFT OUTER JOIN (
> 	SELECT
> 		<SOME COLUMNS>
> 	FROM TABLEB
> 	WHERE COLUMN1 = @PARAM1
> ) T1 ON T1.COLUMN2 = TABLEA.COLUMN3
> WHERE TABLEA.COLUMN4 = @PARAM1
> UNION ALL
> SELECT
> 	<SOME COLUMNS>
> FROM TABLEC
> WHERE TABLEC.COLUMN5 = @PARAM1;
> {code}
> In above example, "@PARAM1" is the local variable that been used multiple times in the query.
> The benefits:
> 1. In programming, I only need to set the parameter for one time. For example, when using Java PreparedStatement's setString(int parameterIndex, String x) function, I only need to write "statement.setString(1, param1);" instead of writing "statement.setString(1, param1); statement.setString(2, param1); statement.setString(3, param1); ....." to set the same value.
> 2. I no need to create a procedure.
> However, in Teiid, I cannot find anyway to achieve this.
> In Teiid, please implement the support of local variables in client SQL query.



--
This message was sent by Atlassian JIRA
(v7.5.0#75005)