CREATE ALIAS and local temporary files

[Tho Eb]

Hi all, 

I've developed a Java program that uses an H2 DB for data processing. Data is imported into the H2 DB, processed, and eventually exported. 

After creating the DB file, an ALIAS is created (I need an MD5 function which is not among the standard functionalities of H2). 

According to the documentation of alias (https://www.h2database.com/html/commands.html#create_alias): 

Creates a new function alias. If this is a ResultSet returning function, by default the return value is cached in a local temporary file.

1) Is there any way to prevent the local temporary file? 

Why I am asking: If I'd install the program on a server, and two different users run the program at the same time, both users have their own H2 DB, but I'd assume they would "share" the same local temporary file, e.g. /tmp/org/h2/dynamic/. This would lead to a problem, if user A starts the program first and the local temporary file belongs to this user, then user B starts the program and is not allowed to overwrite the local temporary file which belongs to user A.

2) If 1) is not possible, is there any option to "personalize" the local temporary file, or to specify the location to be able to keep them separated? 

Thanks, 

Tho

[Evgenij Ryazanov]

Hello

 

According to the documentation of alias (https://www.h2database.com/html/commands.html#create_alias): 

Creates a new function alias. If this is a ResultSet returning function, by default the return value is cached in a local temporary file.

This line of documentation seems to be outdated, the related setting for table value functions was removed from H2.

And your function should be a regular function, I don't see any reasons to return a table from MD5 function. It should simply return a result as a string or array of bytes.

 

but I'd assume they would "share" the same local temporary file

No, they wouldn't.

[Tho Eb]

Hello Evgenij, 

Thank you for your reply.

Evgenij Ryazanov schrieb am Montag, 26. April 2021 um 15:08:44 UTC+2:

Hello

 

According to the documentation of alias (https://www.h2database.com/html/commands.html#create_alias): 

Creates a new function alias. If this is a ResultSet returning function, by default the return value is cached in a local temporary file.

This line of documentation seems to be outdated, the related setting for table value functions was removed from H2.

And your function should be a regular function, I don't see any reasons to return a table from MD5 function. It should simply return a result as a string or array of bytes.

The function does return a string, not a table. 

CREATE ALIAS MD5 AS $$

import java.security.MessageDigest;

@CODE

  

String md5(String baseString)

{

    try

    {

        MessageDigest md    = MessageDigest.getInstance("MD5");

        byte[]        array = md.digest(baseString.getBytes());

        StringBuffer  sb    = new StringBuffer();

  

        for (int i = 0; i < array.length; ++i)

        {

            sb.append(Integer.toHexString((array[i] & 0xFF) | 0x100).substring(1, 3));

        }

        return sb.toString();

    }

    catch (java.security.NoSuchAlgorithmException e)

    {

    }

  

    return "00000000000000000000000000000000";

}

$$;

 

but I'd assume they would "share" the same local temporary file

No, they wouldn't.

That's interesting. Why's that, how are the local temporary files handled?  

It brings me back to my question: Is it possible to prevent local temporary files? 

Thanks, Tho