anypoint studio database issue for expression

Question

When I use this query it works fine in anypoint studio in mule. But now in Anypoint studio it says that it is deprecated, so I used new db details config as given below, but it gives error message as given below with new query details.

<jdbc-ee:outbound-endpoint exchange-pattern="one-way" queryKey="syncdb" queryTimeout="-1" connector-ref="auth-quickbooks_companies" doc:name="Database">
     <jdbc-ee:query key="syncdb" value="MERGE INTO SIAS_ACCESS dest USING(SELECT '${companyName}' ACCT_SYSTEM_COMPANY_NAME, 
       #[message.payload.accessToken] access_token, #[message.payload.accessTokenSecret]  
       access_token_secret,#[message.payload.realmId] ACCT_SYSTEM_COMPANY_ID FROM dual ) 
       src ON( dest.ACCT_SYSTEM_COMPANY_ID = src.ACCT_SYSTEM_COMPANY_ID ) 
       WHEN MATCHED THEN UPDATE SET dest.access_token = src.access_token, 
       dest.access_token_secret = src.access_token_secret, dest.ACCT_SYSTEM_COMPANY_NAME = src.ACCT_SYSTEM_COMPANY_NAME,
       ACCESS_TOKEN_LIFETIME = SYSDATE
       WHEN NOT MATCHED THEN 
       INSERT( ACCESS_ID, ACCT_SYSTEM_COMPANY_NAME, ACCESS_TOKEN, ACCESS_TOKEN_SECRET, ACCT_SYSTEM_COMPANY_ID, ACCESS_TOKEN_LIFETIME) VALUES( SIAS_ACCESS_SEQ.nextval, src.ACCT_SYSTEM_COMPANY_NAME, src.access_token, src.access_token_secret, src.ACCT_SYSTEM_COMPANY_ID, SYSDATE )"/>
</jdbc-ee:outbound-endpoint>

New db query: with Operation as Execute DDL

<db:execute-ddl config-ref="Oracle_Configuration" doc:name="Database">
<db:dynamic-query><![CDATA[MERGE INTO SIAS_ACCESS dest USING(SELECT '${companyName}' ACCT_SYSTEM_COMPANY_NAME, 
    #[message.payload.accessToken] access_token, #[message.payload.accessTokenSecret]  
    access_token_secret,#[message.payload.realmId] ACCT_SYSTEM_COMPANY_ID FROM dual ) 
    src ON( dest.ACCT_SYSTEM_COMPANY_ID = src.ACCT_SYSTEM_COMPANY_ID ) 
    WHEN MATCHED THEN UPDATE SET dest.access_token = src.access_token, 
    dest.access_token_secret = src.access_token_secret, dest.ACCT_SYSTEM_COMPANY_NAME = src.ACCT_SYSTEM_COMPANY_NAME,
    ACCESS_TOKEN_LIFETIME = SYSDATE
    WHEN NOT MATCHED THEN 
    INSERT( ACCESS_ID, ACCT_SYSTEM_COMPANY_NAME, access_token, access_token_secret, ACCT_SYSTEM_COMPANY_ID, ACCESS_TOKEN_LIFETIME) VALUES( SIAS_ACCESS_SEQ.nextval, src.ACCT_SYSTEM_COMPANY_NAME, src.access_token, src.access_token_secret, src.ACCT_SYSTEM_COMPANY_ID, SYSDATE )]]></db:dynamic-query>
 </db:execute-ddl>

ERROR:

Root Exception stack trace:
java.sql.SQLSyntaxErrorException: ORA-00972: identifier is too long    
    at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)

Instead of Operation DDL I have selected Insert(tried with select/update) with Query Type as Parameterized, it gives me error as:

Query type must me '[INSERT, STORE_PROCEDURE_CALL]' but was 'DDL'

I think you should remove <db:execute-ddl and try with <db:select and Parameterized-query — Anirban Sen Chowdhary

David Dossot David Dossot · Accepted Answer · 2014-09-20T15:45:42

MERGE is wrongly recognized as a DDL operation by the new DB connector.

See: https://github.com/mulesoft/mule/blob/mule-3.x/modules/db/src/main/java/org/mule/module/db/internal/parser/SimpleQueryTemplateParser.java#L48-L53

And the fallback to DDL here: https://github.com/mulesoft/mule/blob/mule-3.x/modules/db/src/main/java/org/mule/module/db/internal/parser/SimpleQueryTemplateParser.java#L97

I suggest you open a JIRA: https://www.mulesoft.org/jira/browse/MULE/ and keep using the jdbc connector for now.

anypoint studio database issue for expression

1 Answers