Category: Uncategorized

Group Concatenation for Informix

While on other databases available by standard, Informix does not provide such a feature. But it can be implemented easily:


CREATE FUNCTION gc_init(dummy VARCHAR(255)) RETURNING LVARCHAR;
    RETURN '';
END FUNCTION;

CREATE FUNCTION gc_iter(result LVARCHAR, value VARCHAR(255))
    RETURNING LVARCHAR;
    IF result = '' THEN
        RETURN TRIM(value);
    ELSE
        RETURN result || '  <OR>  ' || TRIM(value);
    END IF;
END FUNCTION;

CREATE FUNCTION gc_combine(partial1 LVARCHAR, partial2 LVARCHAR)
    RETURNING LVARCHAR;
    IF partial1 IS NULL OR partial1 = '' THEN
        RETURN partial2;
    ELIF partial2 IS NULL OR partial2 = '' THEN
        RETURN partial1;
    ELSE
        RETURN partial1 || '  <OR>  ' || partial2;
    END IF;
END FUNCTION;

CREATE FUNCTION gc_final(final LVARCHAR) RETURNING LVARCHAR;
    RETURN final;
END FUNCTION;

CREATE AGGREGATE group_concat
    WITH (INIT = gc_init, ITER = gc_iter,
          COMBINE = gc_combine, FINAL = gc_final);

Encode from int to char…

…and decode back from char to int.

This is an implemenation I once made to run on an informix database. It is probably used not very often, but I somehow liked the code, therefore I want to share it to everyone:


CREATE FUNCTION EncodeIntToChar( a INT, len INT )
	RETURNING VARCHAR(100);

	-- Function encodes a numeric value (int) to an alphanumeric representation
	-- with a specific length (len)

	DEFINE Result VARCHAR(100);
	DEFINE i INT;

	LET i = 0;
	LET Result = '';

	FOR i = (len - 1) TO 0
		LET Result = TRIM(Result)||CHR( MOD( a / POW( 36, i ), 36 ) + ( 48 + ( CASE WHEN MOD( a / POW( 36, i ), 36 ) > 9 THEN 7 ELSE 0 END ) ) );
	END FOR;

	RETURN Result;
END FUNCTION;

CREATE FUNCTION CharDecodeToInt( a VARCHAR(100), len INT )
	RETURNING INT;

	-- Function decodes an alphanumeric value to its integral representation
	-- Specification of length is also required
	
	DEFINE Factor, Result, Power, i INT;

	LET a = LPAD( RIGHT( a, len ), len, '0' );
	LET Result = 0;

	FOR i = len TO 1
		LET Power = ABS( i - len );
		LET Factor = ( ASCII( SUBSTR( a, i, 1 ) ) - 48 - ( CASE WHEN ( ASCII( SUBSTR( a, i, 1 ) )) > 57 THEN 7 ELSE 0 END ) );
		LET Result = Result + ( Factor * POW( 36, Power ) );
	END FOR;

	RETURN Result;
END FUNCTION;

CREATE FUNCTION IncreaseEncoded( AEncoded VARCHAR(100) )
	RETURNING VARCHAR(100);
	RETURN EncodeIntToChar( CharDecodeToInt( AEncoded, LENGTH( AEncoded ) ) + 1, LENGTH( AEncoded ) );
END FUNCTION;

Informix Random Sh*t

Dass man bei Informix für das wenige Geld nicht mal eine Random Funktion bekommt, das ist wohl klar. Wie auch immer: Hab lange danach gegoogelt, eine gefunden, geklaut und hier gespeichert, damit ich sie nicht vergesse.

DROP PROCEDURE IF EXISTS dup_test_sp_random;
CREATE PROCEDURE dup_test_sp_random() RETURNING INTEGER;
DEFINE GLOBAL seed DECIMAL(10) DEFAULT 1;
DEFINE d DECIMAL(20,0);
LET d = (seed * 1103515245) + 12345;
LET seed = d - 4294967296 * TRUNC(d / 4294967296);
RETURN MOD(TRUNC(seed / 65536), 32768);
END PROCEDURE;

Wie auch immer: wenn Unternehmen sich PostgreSQL oder andere Gratisprodukte nicht leisten können,…

Embarcadero C++ Builder

 

__dbk_fcall_wrapper

Dem wird am ehesten so entgegnet:

  • Select C++ Compiler->Use classic Borland compiler and tick (TRUE).
  • Select project options->C++ Linker and untick (FALSE) “Link with Dynamic RTL”
  • Select project options->C++ Linker and untick (FALSE) “Link withthe delphi Runtime Library”
  • Select project options->Runtime Packages and untick (FALSE) “Link with runtime packages”
  • C++ Linker / Erweitert / DLLs verzögert laden: BORLNDMM.DLL

ss