[resolved] Problem with session variables in tMysqlRow

[Anonymous]

Hello,
I have a Top 10 ranking sql script inside a tMysqlRow component like so :
Set @Hits_rank := 0, @current_month := 0;
SELECT ProductCode, Month, CountHits
   FROM
     (SELECT ProductCode, Month, CountHits,
                  @Hits_rank := IF(@current_month = month, @Hits_rank + 1, 1) AS Hits_rank,
                  @current_month := month
       FROM StatMonth
       ORDER BY Month, CountHits DESC
     ) ranked
   WHERE Hits_rank <= 10;
When I run the job, I get an error msg saying :
'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT ProductCode, Month, CountHits     FROM       (SELECT ProductCode, Month, ' at line 1'
The thing is I've tested this script inside the Talend SQLBuilder and it works just fine. 
When the script is inside the tMysqlRow component the only difference is that " " is placed at the beginning and at the end of the script. 
I can't figure out what the problem is.  Any help is much appreciated. 
Thanks
P.S This is tagged 'oracle' by mistake, it is definately a MySQL db. 

[Anonymous]

Before release 5.4 Talend has build the SQL as ONE line String. Do you have the chance to test it with a release higher the 5.4.1?

[Anonymous]

Hello,
Thanks for ur suggestion & sorry for my late response, I was away yesterday.  I've upgraded to Talend version 5.6
an there has been an improvement.  The original sql syntax problem has gone but a new one is now present :
    Set cannot be resolved to a variable
    Syntax error on token "@", ; expected
    Hits_rank cannot be resolved to a type
    Syntax error on token ":", invalid VariableDeclaratorId
    Syntax error on token "@", ; expected
    Syntax error on token ":", Expression expected after this token
    Syntax error, insert ";" to complete LocalVariableDeclarationStatement
    Syntax error on token(s), misplaced construct(s)
    Syntax error on token "ProductCode", delete this token
    Syntax error on token(s), misplaced construct(s)
    Syntax error on token "=", delete this token
    Syntax error on token "@", delete this token
    Syntax error on token "@", delete this token
    Syntax error, insert ";" to complete Statement
    Syntax error on token ",", ; expected
    Syntax error on token(s), misplaced construct(s)
    Syntax error on token "=", { expected
    Syntax error on token "StatMonth", ; expected
    Syntax error on token(s), misplaced construct(s)
    Syntax error on token "Month", delete this token
    Syntax error on token ")", ; expected
    Syntax error on token "WHERE", = expected after this token
    Syntax error, insert "else Statement" to complete IfStatement
    Syntax error, insert "}" to complete Block
any ideas ?
Thanks in advance. 

[Anonymous · Accepted solution]

I completely re wrote the job using tFlowToIterate and tMysqlrow. 

[Anonymous]

Hi thetrad9,
Thanks for posting that you have resolved it by yourself. Could you please share your rewritten session variable in tMysqlRow with us?
Best regards
Sabrina