Nested CALL

[Thotheolh]

Hi,

I am trying to run nested CALL functions but could not get it to work. Below is my SQL statement.

insert into EmailSubscription (
    Email,
    IsSubscribed,
    DateSubscribed,
    SubCode
) 
values (
    "te...@test.com",
    true,
    current_timestamp(),
    call rawtohex(call hash('SHA256', call concat(Email, DateSubscribed), 1))
);

I want to SHA256 hash the concatenation of the email address with the timestamp of subscription and then convert the raw SHA256 binary output into a hexadecimal representation to be inserted as the 'SubCode' for the table.

How should I go about doing that in SQL ?

[Noel Grandin]

something like this:

insert into EmailSubscription (
    Email,
    IsSubscribed,
    DateSubscribed,
    SubCode
) 
values (
    "te...@test.com",
    true,
    current_timestamp(),

    rawtohex(hash('SHA256', concat("te...@test.com", current_timestamp()), 1))
);

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscribe@googlegroups.com.
To post to this group, send email to h2-da...@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

[Thotheolh]

I have modified and re-arranged the SQL statement as below:

insert into EmailSubscription (
    EMAIL,
    ISSUBSCRIBED,
    DATESUBSCRIBED,
    SUBCODE

)
values (
    'te...@test.com',
    true,
    current_timestamp(),

    rawtohex(hash('SHA256', stringtoutf8(concat('te...@test.com', current_timestamp())), 1))
);

It works with the modification but the outcome when I select the SubCode is now shown below:

te...@test.com

TRUE

2017-08-19 15:12:18.156

null

0032006600660033006600360030006600630061003300610035003100350064003000380065003800320061006500620039006300340039003200350037003500390039003400640064003700320063003700390061003300640061003600380031006400370035003100610062003000310034006100390062003600610061

What I am expecting is the concat as the input for the hash which is 'te...@test.com2017-08-19 15:12:18.156' and after hashing it should be '2ff3f60fca3a515d08e82aeb9c492575994dd72c79a3da681d751ab014a9b6aa'. 

How do I format my result to get the hash as shown above ?

To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

[Thotheolh]

I noticed that the format problem is fixed by not using the rawtohex() call which solves the problem. The rawtohex is somewhat weird as I was expecting a raw byte array to be converted to hex data but it takes a string and puts it to 4 character hex. Maybe a definition of what this 'raw' refers to in the documentation and some samples in the document would help.

On Saturday, August 19, 2017 at 3:20:20 PM UTC+8, Thotheolh wrote:

I have modified and re-arranged the SQL statement as below:

insert into EmailSubscription (
    EMAIL,
    ISSUBSCRIBED,
    DATESUBSCRIBED,
    SUBCODE
)
values (
    'te...@test.com',
    true,
    current_timestamp(),

    rawtohex(hash('SHA256', stringtoutf8(concat('test@test.com', current_timestamp())), 1))
);

[Noel Grandin]

glad to here you sorted it out - feel free to submit a pull request, the relevant file is 

  src/docsrc/help/help.csv

:-)

​