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. 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 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.

Engine: Mimer

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

ryb_sf_getelapsedtimeutc ()

CREATE FUNCTION 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;

define.ryb_sf_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!

dbo.ryb_sf_getelapsedtimeutc()

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

define.ryb_sf_makeguid

-- Drop if exists
IF OBJECT_ID('define.ryb_sf_makeguid', 'FN') IS NOT NULL
DROP FUNCTION define.ryb_sf_makeguid;
GO

-- Create the schema if needed
IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'define')
EXEC('CREATE SCHEMA define');
GO

-- Create the function
CREATE FUNCTION define.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!

ryb_sf_getelapsedtimeutc()

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

ryb_sf_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.

define.ryb_sf_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;

define.ryb_sf_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;

define.ryb_sf_random_string(integer)

-- 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.

define.sql_sf_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;

define.sql_sf_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;

define.sql_sf_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