Use MySQL variables in SQL Executor and view Output

[rwhite923]

SET block_encryption_mode = ‘aes-256-cbc’;
SET @key_str = FROM_BASE64(‘yyyyyyyyyyyyyyyyyyyyyyyyyy’);
SET @init_vector = UNHEX(‘cccccccccccccccccccccc’);
select party_id, AES_DECRYPT(FROM_BASE64(party_id), @key_str, @init_vector) as id_decrypted from db.tablename;

[bruno29a]

Hi @rwhite923 and welcome to the Knime Community.

It would help if you explicitly explained what help you need instead of us having to guess and make assumptions - because if we get the guess and assumption wrong, we’d basically have wasted our time.

It also does not allow us to jump directly to and focus on the solution - and in my case, I’m still writing all this and the assumption below before I can enjoy diving into the solution.

Assuming you are asking how to see the result of the select statement, you can still set the variables in the DB SQL Executor, and then run the select via the DB Query Reader.

Something like this should do:

You can execute these in the DB SQL Executor:

SET block_encryption_mode = 'aes-256-cbc';
SET @key_str = FROM_BASE64('yyyyyyyyyyyyyyyyyyyyyyyyyy');
SET @init_vector = UNHEX('cccccccccccccccccccccc');

Just make sure that you use the option “Support multiple SQL statements” with “;” as separator

You can then run your query (select) in the DB Query Reader:

The DB Query Reader should be able to access the variables as it’s in the same session.