[resolved] Problem with session variables in tMysqlRow

One Star

[resolved] Problem with session variables in tMysqlRow

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. 

Accepted Solutions
One Star

Re: [resolved] Problem with session variables in tMysqlRow

I completely re wrote the job using tFlowToIterate and tMysqlrow. 

All Replies
Seventeen Stars

Re: [resolved] Problem with session variables in tMysqlRow

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?
One Star

Re: [resolved] Problem with session variables in tMysqlRow

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

Re: [resolved] Problem with session variables in tMysqlRow

I completely re wrote the job using tFlowToIterate and tMysqlrow. 
Moderator

Re: [resolved] Problem with session variables in tMysqlRow

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
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.