SQL backend engine scripts

There might be some additional backend engine SQL specific that you might need for initializing fields from the SQL side.

Something that returns a UTC timestamp and something that returns a GUID/UUID in 16 bytes, are very basic scripts.

The GUID/UUID implementations being shown here are Capersoft's implementation of a printable GUID/UUIDs. Most SQL backend engine have traditional GUID/UUID implementations.

I mostly work with PostgreSQL so I can show the ones that I use. You can use them as examples to build your own for your backend using ChatGPT, Claude, or any other AI coder. The internet might have additional examples available.

Over time, more scripts should make it to these Wiki pages. If you want to contribute something to the scripts of the supported DCT2SQL backends, your name will be listed as the contributor. It will be appreciated, by the folks getting started on their particular SQL engine backend.

Please verify and test these scripts in your own SQL engine backend implementation. Some of these scripts have been rewriten by ChatGPT into new scripts.

Engine: Mimer

Created with ChatGPT by Roberto Renz from PostgreSQL example. NOT TESTED!

GetElapsedTimeUTC

CREATE FUNCTION define.ryb_sf_getelapsedtimeutc ()
RETURNS BIGINT
AS
BEGIN
    RETURN SECS_BETWEEN(TIMESTAMP '1970-01-01 00:00:00', CURRENT_TIMESTAMP(0) AT TIME ZONE UTC) * 1000;
END;

MakeGUID

-- Drop existing function if it exists
DROP FUNCTION IF EXISTS define.ryb_sf_makeguid;
-- Optionally, create the schema if needed (depending on your permissions)
-- CREATE SCHEMA define;

CREATE FUNCTION define.ryb_sf_makeguid()
    RETURNS VARCHAR(16)>
    -- You may specify DETERMINISTIC or NOT DETERMINISTIC as needed
    MODIFIES SQL DATA
BEGIN
    DECLARE chars VARCHAR(36) := '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    DECLARE result VARCHAR(16) := '';
    DECLARE i INT := 1;
    DECLARE pos INT;
    REPEAT
        -- Generate a random float between 0 and 1
        -- Multiply by length (36), use CEILING to get a position 1..36
        SET pos = CEILING(RAND() * 36);
        SET result = result || SUBSTRING(chars FROM pos FOR 1);
        SET i = i + 1;
    UNTIL i > 16 END REPEAT;
    RETURN result;
END;

Engine: MSSQL

Created with ChatGPT by Roberto Renz from PostgreSQL example. NOT TESTED!

GetElapsedTimeUTC

CREATE FUNCTION dbo.ryb_sf_getelapsedtimeutc()
RETURNS BIGINT
AS
BEGIN
    RETURN DATEDIFF_BIG(MILLISECOND, '1970-01-01 00:00:00', SYSUTCDATETIME());
END;
GO

MakeGUID

-- Drop if exists
IF OBJECT_ID('dbo.ryb_sf_makeguid', 'FN') IS NOT NULL 
    DROP FUNCTION dbo.ryb_sf_makeguid; 
GO 
 
-- Create the function >
CREATE FUNCTION dbo.ryb_sf_makeguid() 
RETURNS NVARCHAR(50) 
AS 
BEGIN
    DECLARE @chars NVARCHAR(36) = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'; 
    DECLARE @result NVARCHAR(50) = ''; 
    DECLARE @i INT = 1; 
    WHILE @i <= 16 
    BEGIN 
        -- Random number 1–36 
        DECLARE @pos INT = CAST(RAND(CHECKSUM(NEWID())) * 36 + 1 AS INT); 
        SET @result = @result + SUBSTRING(@chars, @pos, 1); 
        SET @i += 1; 
    END 
    RETURN @result; 
END; 
GO 

Engine: MYSQL

Created with ChatGPT by Roberto Renz from PostgreSQL example. NOT TESTED!

GetElapsedTimeUTC

DELIMITER //
CREATE FUNCTION ryb_sf_getelapsedtimeutc()
RETURNS BIGINT
DETERMINISTIC
BEGIN
    RETURN UNIX_TIMESTAMP(UTC_TIMESTAMP(6)) * 1000;
END//
DELIMITER ;

MakeGUID

-- Drop if exists
DROP FUNCTION IF EXISTS ryb_sf_makeguid;

DELIMITER $$

CREATE FUNCTION ryb_sf_makeguid()
RETURNS VARCHAR(16)
DETERMINISTIC
BEGIN
    DECLARE chars VARCHAR(36) DEFAULT '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    DECLARE result VARCHAR(16) DEFAULT '';
    DECLARE i INT DEFAULT 1;
    DECLARE pos INT;
    WHILE i <= 16 DO
        -- RAND() returns 0 ≤ x < 1, multiply by 36 → 0–35
        SET pos = FLOOR(RAND() * 36) + 1;
        SET result = CONCAT(result, SUBSTRING(chars, pos, 1));
        SET i = i + 1;
    END WHILE;
    RETURN result;
END$$

DELIMITER ;

Engine: PostgreSQL

These script examples are from one of my PostgreSQL application databases.

GetElapsedTimeUTC

-- FUNCTION: define.ryb_sf_getelapsedtimeutc()
-- DROP FUNCTION define.ryb_sf_getelapsedtimeutc();
CREATE OR REPLACE FUNCTION define.ryb_sf_getelapsedtimeutc()
    RETURNS bigint
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
AS $BODY$
begin
  RETURN (   SELECT (EXTRACT(EPOCH FROM (SELECT NOW() AT TIME ZONE 'UTC')) * 1000)   );
end
$BODY$;
ALTER FUNCTION define.ryb_sf_getelapsedtimeutc() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION define.ryb_sf_getelapsedtimeutc() TO res_admin;
GRANT EXECUTE ON FUNCTION define.ryb_sf_getelapsedtimeutc() TO postgres;
GRANT EXECUTE ON FUNCTION define.ryb_sf_getelapsedtimeutc() TO PUBLIC;
GRANT EXECUTE ON FUNCTION define.ryb_sf_getelapsedtimeutc() TO res_dba WITH GRANT OPTION;

MakeGUID

-- FUNCTION: define.ryb_sf_makeguid()
-- DROP FUNCTION define.ryb_sf_makeguid();
CREATE OR REPLACE FUNCTION define.ryb_sf_makeguid()
    RETURNS text
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE
AS $BODY$
declare
  chars text[] := '{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z}';
  result text := '';
  i integer := 0;
begin
  for i in 1..16 loop
    result := result || chars[ceil(35 * random())];
  end loop;
  return result;
end;
$BODY$;
ALTER FUNCTION define.ryb_sf_makeguid() OWNER TO postgres;
GRANT EXECUTE ON FUNCTION define.ryb_sf_makeguid() TO res_admin;
GRANT EXECUTE ON FUNCTION define.ryb_sf_makeguid() TO postgres;
GRANT EXECUTE ON FUNCTION define.ryb_sf_makeguid() TO PUBLIC;
GRANT EXECUTE ON FUNCTION define.ryb_sf_makeguid() TO res_dba WITH GRANT OPTION;

Random_String

-- FUNCTION: define.ryb_sf_random_string(integer)
-- DROP FUNCTION define.ryb_sf_random_string(integer);
CREATE OR REPLACE FUNCTION define.ryb_sf_random_string(integer)
    RETURNS text
    LANGUAGE 'sql'
    COST 100
    VOLATILE
AS $BODY$
SELECT array_to_string( ARRAY (
        SELECT substring( '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
          FROM ( ceil(random()*62))::int FOR 1 )
        FROM generate_series(1, $1)
    ), '' ) 
$BODY$;
ALTER FUNCTION define.ryb_sf_random_string(integer) OWNER TO postgres;
GRANT EXECUTE ON FUNCTION define.ryb_sf_random_string(integer) TO res_admin;
GRANT EXECUTE ON FUNCTION define.ryb_sf_random_string(integer) TO postgres;
GRANT EXECUTE ON FUNCTION define.ryb_sf_random_string(integer) TO PUBLIC;
GRANT EXECUTE ON FUNCTION define.ryb_sf_random_string(integer) TO res_dba WITH GRANT OPTION;

Engine: SQLAnywhere

These have been tested out in an SQLAnywhere database and rewriten by ChatGPT.

GetElapsedTimeUTC

CREATE OR REPLACE FUNCTION "define"."sql_sf_GetElapsedTimeUTC"()
RETURNS BIGINT  -- more natural than REAL for milliseconds
NOT DETERMINISTIC
BEGIN
    DECLARE retval BIGINT;
    -- DATEDIFF in SQL Anywhere returns an integer value directly
    SET retval = DATEDIFF(
        millisecond,
        TIMESTAMP '1970-01-01 00:00:00',   -- Unix epoch
        CURRENT UTC TIMESTAMP
    );
    RETURN retval;
END;

MakeGUID

CREATE OR REPLACE FUNCTION "define"."sql_sf_MakeGUID"()
RETURNS CHAR(16)
NOT DETERMINISTIC
BEGIN
    DECLARE retval  CHAR(16) DEFAULT '';
    DECLARE i       INTEGER  DEFAULT 1;
    -- Candidate characters: A–Z then 0–9
    DECLARE chars   LONG VARCHAR DEFAULT 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
    DECLARE pick    INTEGER;
    WHILE i <= 16 LOOP
        -- RAND() ∈ [0,1); pick ∈ [1,36]
        SET pick  = FLOOR( RAND() * 36 ) + 1;
        SET retval = retval || SUBSTR( chars, pick, 1 );
        SET i = i + 1;
    END LOOP;
    RETURN retval;
END;

GetCurrentTimeInElapsedUTC

CREATE OR REPLACE FUNCTION "define"."sql_sf_GetCurrentTimeInElapsedUTC"( IN "xDate" TIMESTAMP )
RETURNS BIGINT
NOT DETERMINISTIC
BEGIN
    -- Convert the supplied local timestamp to UTC by applying the current local→UTC offset (in minutes),
    -- then return milliseconds since the Unix epoch.
    DECLARE epoch    TIMESTAMP DEFAULT TIMESTAMP '1970-01-01 00:00:00';
    DECLARE utc_ts   TIMESTAMP;
    -- Offset = DATEDIFF(minute, local, UTC)  (typically negative in US time zones)
    SET utc_ts = DATEADD(
        minute,
        DATEDIFF( minute, CURRENT TIMESTAMP, CURRENT UTC TIMESTAMP ),
        "xDate"
    );
    RETURN DATEDIFF( millisecond, epoch, utc_ts );
END;

Code Wiki Main Repositories

Valhalla Calling - Miracle of Sound

[Verse 1]
Ships on vigor of the waves are skimming
Barren summits to the verdant plains
Each horizon is a new beginning
Rise and reign

Far from the Fjords and the ice cold currents
Ravens soar over new frontiers
Songs and sagas of a fate determined
Shields and spears

Vows of favour or the thrill of plunder
Pull together for the clan and kin
Clank of hammers and the crash of thunder
Pound within

[Chorus]
Oh-ho-oh
The echoes of eternity
Oh-ho-oh
Valhalla calling me
Oh-ho-oh
To pluck the strings of destiny
Oh-ho-oh
Valhalla calling me
Valhalla calling me

[Verse 2]
Sails a' swaying on the crimson rivers
Blood and glory in the fighting fields
Shields a' shatter into splintered timbers
Iron and steel

Fires are rising and the bells are ringing
Glory take us into Odin's halls
Golden glimmer and the sound of singing
Asgard's call

[Chorus]
Oh-ho-oh
The echoes of eternity
Oh-ho-oh
Valhalla calling me
Oh-ho-oh
To pluck the strings of destiny
Oh-ho-oh
Valhalla calling me
Valhalla calling me

[Bridge]
Wind and the waves will carry me
Wind and the waves will set me free
Wind and the waves will carry me
Wind and the waves will set me free

[Chorus]
Oh-ho-oh
The echoes of eternity
Oh-ho-oh
Valhalla calling me
Oh-ho-oh
To pluck the strings of destiny
Oh-ho-oh
Valhalla calling me
Valhalla calling me