Help converting a SHA1 hash to Oracle

[Jonathan Farina <jo...@yahoo.co.uk.INVALID>]

Hi
I am trying to write some code that will take a MySQL SQL statement that includes a SHA1 hash statement and have it translate successfully to Oracle and in doing so add a cast function.

Eg:

select sha1(address) from locations limit 10

Should translate to:

SELECT CAST(STANDARD_HASH(ADDRESS) AS VARCHAR(200)) FROM LOCATIONS FETCH FIRST 10 ROWS ONLY

I have 90% done but am struggling with the last part. I have via creating my own SqlNodes done the following:

SELECT CAST(STANDARD_HASH("LOCATIONS"."ADDRESS") AS "VARCHAR(200)") "ADDRESS" FROM “LOCATIONS"

This has been achieved by the following code:

…skipping setup code…

//create Hash function
SqlNode[] HashFunctionOperandList = new SqlNode[1];
HashFunctionOperandList[0] = sqlIdentifier;
//ORACLE_HASH is a UDF defined elsewhere
SqlBasicCall innerFunction = new SqlBasicCall(ORACLE_HASH, HashFunctionOperandList, new SqlParserPos(0, 0));

//Create the Cast function
SqlNode[] functionOperandList = new SqlNode[2];
functionOperandList[0] = innerFunction;
functionOperandList[1] = new SqlIdentifier("VARCHAR(200)", SqlParserPos.ZERO);
compliantFunction =  new SqlBasicCall(new SqlCastFunction(),functionOperandList, new SqlParserPos(0, 0));
…skipping to output code…
SqlDialect translationDialect;
translationDialect = OracleSqlDialect.DEFAULT;;
String Query = compliantFunction.toSqlString(translationDialect).toString();
My key issue seems to be how I get VARCHAR(200) in my cast function, secondly everything included the CAST AS has been quoted.
I’ve tried SO for support and according to the Calcite website, It recommended I try here.  So any help would be appreciated?

Thanks

Re: Help converting a SHA1 hash to Oracle

[Benchao Li <li...@apache.org>]

Hi Jonathan,

# For the first question: "how I get VARCHAR(200) in my cast function"

You passed wrong operand now (new SqlIdentifier("VARCHAR(200)",
SqlParserPos.ZERO);), it should be a SqlDataTypeSpec, not a SqlIdentifier
for the cast's second operand.

# For the second question: "everything included the CAST AS has been quoted"

SqlWriterConfig#quoteAllIdentifiers controls whether to quote all
identifiers while translating SqlNode to string. You can pass your own
config to SqlNode#toSqlString.

Jonathan Farina <jo...@yahoo.co.uk.invalid> 于2023年1月19日周四 07:12写道：

> Hi
> I am trying to write some code that will take a MySQL SQL statement that
> includes a SHA1 hash statement and have it translate successfully to Oracle
> and in doing so add a cast function.
>
> Eg:
>
> select sha1(address) from locations limit 10
>
> Should translate to:
>
> SELECT CAST(STANDARD_HASH(ADDRESS) AS VARCHAR(200)) FROM LOCATIONS FETCH
> FIRST 10 ROWS ONLY
>
> I have 90% done but am struggling with the last part. I have via creating
> my own SqlNodes done the following:
>
> SELECT CAST(STANDARD_HASH("LOCATIONS"."ADDRESS") AS "VARCHAR(200)")
> "ADDRESS" FROM “LOCATIONS"
>
> This has been achieved by the following code:
>
> …skipping setup code…
>
> //create Hash function
> SqlNode[] HashFunctionOperandList = new SqlNode[1];
> HashFunctionOperandList[0] = sqlIdentifier;
> //ORACLE_HASH is a UDF defined elsewhere
> SqlBasicCall innerFunction = new SqlBasicCall(ORACLE_HASH,
> HashFunctionOperandList, new SqlParserPos(0, 0));
>
> //Create the Cast function
> SqlNode[] functionOperandList = new SqlNode[2];
> functionOperandList[0] = innerFunction;
> functionOperandList[1] = new SqlIdentifier("VARCHAR(200)",
> SqlParserPos.ZERO);
> compliantFunction =  new SqlBasicCall(new
> SqlCastFunction(),functionOperandList, new SqlParserPos(0, 0));
> …skipping to output code…
> SqlDialect translationDialect;
> translationDialect = OracleSqlDialect.DEFAULT;;
> String Query =
> compliantFunction.toSqlString(translationDialect).toString();
> My key issue seems to be how I get VARCHAR(200) in my cast function,
> secondly everything included the CAST AS has been quoted.
> I’ve tried SO for support and according to the Calcite website, It
> recommended I try here.  So any help would be appreciated?
>
> Thanks
>


-- 

Best,
Benchao Li