Page 2 of 2 FirstFirst 1 2
Results 21 to 32 of 32

Thread: Oracle to SqlServer Code conversion

  1. #21
    Rem -----------------------------------------------------------------------------------
    Rem
    Rem
    Rem Name : Encrypt_PassWord.Sql
    Rem
    Rem Purpose : This script helps create package for Hint Product in DWH Schema
    Rem
    Rem
    Rem
    Rem DD/MM/YYYY Created by Reason
    Rem ---------- ----------- ------
    Rem 11/8/2010 HINT team This script helps create package for Hint Product in DWH Schema
    Rem ---------------------------------------------------------------------------------
    Spool Encrypt_PassWord.Log;
    Create or Replace PACKAGE APP_PASSWORD as


    FUNCTION ENCRYPT (P_string IN VARCHAR2) RETURN VARCHAR2;
    FUNCTION DECRYPT (P_string IN VARCHAR2) RETURN VARCHAR2;
    FUNCTION ADDHASH(P_keystring IN VARCHAR2, P_datastring IN VARCHAR2) RETURN VARCHAR2;
    FUNCTION SUBHASH(P_keystring IN VARCHAR2, P_datahex IN VARCHAR2) RETURN VARCHAR2;
    FUNCTION SETHASHLIMIT(P_newlimit IN NUMBER) RETURN NUMBER;


    END APP_PASSWORD;
    /


    PROMPT Creating PACKAGE BODY 'APP_PASSWORD'
    Create or Replace PACKAGE BODY APP_PASSWORD AS
    TYPE INTTAB IS TABLE OF BINARY_INTEGER INDEX BY BINARY_INTEGER;
    TYPE NUMTAB IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;


    X_hextable VARCHAR2(256) := '0123456789ABCDEF'||
    '1032547698BADCFE'||
    '23016745AB89EFCD'||
    '32107654BA98FEDC'||
    '45670123CDEF89AB'||
    '54761032DCFE98BA'||
    '67452301EFCDAB89'||
    '76543210FEDCBA98'||
    '89ABCDEF01234567'||
    '98BADCFE10325476'||
    'AB89EFCD23016745'||
    'BA98FEDC32107654'||
    'CDEF89AB45670123'||
    'DCFE98BA54761032'||
    'EFCDAB8967452301'||
    'FEDCBA9876543210';


    X_hashlimit NUMBER := 32;
    X_savekey VARCHAR2(256);
    X_savetable INTTAB;
    X_keylimit NUMBER := 10;
    X_crcarray NUMTAB;


    FUNCTION ENCRYPT (P_string VARCHAR2) RETURN VARCHAR2 IS
    X_key VARCHAR2(100);
    X_return VARCHAR2(2000);
    X_length NUMBER;
    X_counter NUMBER;
    BEGIN
    X_return := '';


    IF P_string IS NOT NULL THEN
    X_length := length(P_string);
    X_counter := 1;

    X_key := '1gf:dUR(DS=29q0+';


    WHILE X_counter <= X_length LOOP
    X_return := X_return||addhash(X_key,substr(P_string,X_counter, 20));
    X_counter := X_counter + 20;
    END LOOP;
    END IF;

    RETURN X_return;

    END ENCRYPT;


    FUNCTION DECRYPT(P_string VARCHAR2) RETURN VARCHAR2
    IS


    X_key VARCHAR2(100);
    X_return VARCHAR2(2000);
    X_length NUMBER;
    X_counter NUMBER;
    BEGIN
    X_return := '';


    IF P_string IS NOT NULL THEN
    X_length := length(P_string);
    X_counter := 1;


    X_key := '1gf:dUR(DS=29q0+';


    WHILE X_counter <= X_length LOOP
    X_return := X_return||subhash(X_key,substr(P_string,X_counter, 64));
    X_counter := X_counter + 64;
    END LOOP;
    END IF;
    RETURN X_return;
    END DECRYPT;




    FUNCTION XORTOHEX(X_b1 IN BINARY_INTEGER, X_b2 IN BINARY_INTEGER) RETURN VARCHAR2
    IS
    /*
    ** Compute the XOR of two byte values and return a 2-character
    ** hexadecimal string result.
    */


    X_unibble BINARY_INTEGER;
    X_lnibble BINARY_INTEGER;
    X_x1 BINARY_INTEGER;
    X_x2 BINARY_INTEGER;
    BEGIN
    X_x1 := MOD(X_b1, 16);
    X_x2 := MOD(X_b2, 16);
    X_lnibble := X_x1 * 16 + X_x2 + 1;
    X_unibble := (X_b1 - X_x1) + (X_b2 - X_x2)/16 + 1;


    RETURN(substr(X_hextable, X_unibble, 1)||substr(X_hextable, X_lnibble, 1));

    END XORTOHEX;



    FUNCTION XORTOCHR(X_b1 IN BINARY_INTEGER, X_b2 IN BINARY_INTEGER) RETURN VARCHAR2
    IS
    /*
    ** Compute the XOR of two byte values and return a 1-byte (character)
    ** result. (Note: this might be invalid for multibyte character
    ** sets, since this function will THEN be returning only a portion
    ** of a full character in the CHR result -- needs to be checked.)
    */

    X_unibble BINARY_INTEGER;
    X_lnibble BINARY_INTEGER;
    X_x1 BINARY_INTEGER;
    X_x2 BINARY_INTEGER;
    BEGIN
    X_x1 := MOD(X_b1, 16);
    X_x2 := MOD(X_b2, 16);
    X_lnibble := X_x1 * 16 + X_x2 + 1;
    X_unibble := (X_b1 - X_x1) + (X_b2 - X_x2)/16 + 1;


    RETURN(chr(instr('0123456789ABCDEF', substr(X_hextable, X_unibble, 1)) * 16 +
    instr('0123456789ABCDEF', substr(X_hextable, X_lnibble, 1)) - 17));
    END XORTOCHR;

    PROCEDURE RC4INIT(P_keystring IN VARCHAR2, P_rctable IN OUT INTTAB)
    IS
    /*
    ** Initialize RC4 from the KEYSTRING, common to both ENCRYPT and DECRYPT.
    */

    X_dataint INTTAB;
    X_rccount BINARY_INTEGER;
    X_rcswap BINARY_INTEGER;
    X_slen BINARY_INTEGER;
    X_dlen BINARY_INTEGER;
    X_kloc BINARY_INTEGER;
    X_ktemp BINARY_INTEGER;
    BEGIN
    -- Check for key match with previous initialization
    IF (substr(P_keystring,1,X_keylimit) = X_savekey) THEN
    P_rctable := X_savetable;
    ELSE
    -- Turn the key string into a series of binary integer byte values
    X_dlen := 0;
    X_slen := length(P_keystring);

    IF (X_slen > X_keylimit) THEN
    X_slen := X_keylimit;
    END IF;

    FOR X_kloc IN 1..X_slen LOOP
    X_ktemp := ascii(substr(P_keystring,X_kloc,1));
    IF (X_ktemp < 256) THEN
    X_dlen := X_dlen + 1;
    X_dataint(X_dlen) := X_ktemp;
    ELSIF (X_ktemp < 256 * 256) THEN
    X_dataint(X_dlen + 2) := MOD(X_ktemp, 256);
    X_ktemp := (X_ktemp - MOD(X_ktemp, 256))/256;
    X_dataint(X_dlen + 1) := MOD(X_ktemp, 256);
    X_dlen := X_dlen + 2;
    ELSIF (X_ktemp < 256 * 256 * 256) THEN
    X_dataint(X_dlen + 3) := MOD(X_ktemp, 256);
    X_ktemp := (X_ktemp - MOD(X_ktemp, 256))/256;
    X_dataint(X_dlen + 2) := MOD(X_ktemp, 256);
    X_ktemp := (X_ktemp - MOD(X_ktemp, 256))/256;
    X_dataint(X_dlen + 1) := MOD(X_ktemp, 256);
    X_dlen := X_dlen + 3;
    ELSE
    X_dataint(X_dlen + 4) := MOD(X_ktemp, 256);
    X_ktemp := (X_ktemp - MOD(X_ktemp, 256))/256;
    X_dataint(X_dlen + 3) := MOD(X_ktemp, 256);
    X_ktemp := (X_ktemp - MOD(X_ktemp, 256))/256;
    X_dataint(X_dlen + 2) := MOD(X_ktemp, 256);
    X_ktemp := (X_ktemp - MOD(X_ktemp, 256))/256;
    X_dataint(X_dlen + 1) := MOD(X_ktemp, 256);
    X_dlen := X_dlen + 4;
    END IF;
    END LOOP;

    -- Initialize the RC4 algorithm using the encryption key string
    FOR X_rccount IN 1..256 LOOP
    P_rctable(X_rccount) := X_rccount - 1;
    END LOOP;


    X_rcswap := 0;
    X_kloc := 1;

    FOR X_rccount IN 1..256 LOOP
    X_ktemp := P_rctable(X_rccount);
    X_rcswap := X_rcswap + X_ktemp + X_dataint(X_kloc);

    IF (X_rcswap >= 512) THEN
    X_rcswap := X_rcswap - 512;
    ELSIF (X_rcswap >= 256) THEN
    X_rcswap := X_rcswap - 256;
    END IF;

    P_rctable(X_rccount) := P_rctable(X_rcswap + 1);
    P_rctable(X_rcswap + 1) := X_ktemp;

    IF (X_kloc >= X_dlen) THEN
    X_kloc := X_kloc - X_dlen;
    END IF;
    X_kloc := X_kloc + 1;
    END LOOP;
    -- Save table for next time
    X_savetable := P_rctable;
    X_savekey := substr(P_keystring,1,X_keylimit);
    END IF;
    END RC4INIT;


    FUNCTION ADDHASH(P_keystring IN VARCHAR2, P_datastring IN VARCHAR2) RETURN VARCHAR2
    IS
    X_dataint INTTAB;
    X_rctable INTTAB;
    X_rccount BINARY_INTEGER;
    X_rcswap BINARY_INTEGER;
    The biggest adventure u can take is to live life of your dreams..
    Bidiyasar a!!!!!

  2. #22
    X_slen BINARY_INTEGER;
    X_dlen BINARY_INTEGER;
    X_kloc BINARY_INTEGER;
    X_ktemp BINARY_INTEGER;
    X_datahex VARCHAR2(2000);
    X_hlimit NUMBER;
    BEGIN
    -- Initialize RC4 from key string
    RC4INIT(P_keystring, X_rctable);


    -- Turn the data string into a series of binary integer byte values
    X_dlen := 0;
    X_slen := length(P_datastring);

    FOR X_kloc IN 1..X_slen LOOP
    X_ktemp := ascii(substr(P_datastring,X_kloc,1));
    IF (X_ktemp < 256) THEN
    X_dlen := X_dlen + 1;
    X_dataint(X_dlen) := X_ktemp;
    ELSIF (X_ktemp < 256 * 256) THEN
    X_dataint(X_dlen + 2) := MOD(X_ktemp, 256);
    X_ktemp := (X_ktemp - MOD(X_ktemp, 256))/256;
    X_dataint(X_dlen + 1) := MOD(X_ktemp, 256);
    X_dlen := X_dlen + 2;
    ELSIF (X_ktemp < 256 * 256 * 256) THEN
    X_dataint(X_dlen + 3) := MOD(X_ktemp, 256);
    X_ktemp := (X_ktemp - MOD(X_ktemp, 256))/256;
    X_dataint(X_dlen + 2) := MOD(X_ktemp, 256);
    X_ktemp := (X_ktemp - MOD(X_ktemp, 256))/256;
    X_dataint(X_dlen + 1) := MOD(X_ktemp, 256);
    X_dlen := X_dlen + 3;
    ELSE
    X_dataint(X_dlen + 4) := MOD(X_ktemp, 256);
    X_ktemp := (X_ktemp - MOD(X_ktemp, 256))/256;
    X_dataint(X_dlen + 3) := MOD(X_ktemp, 256);
    X_ktemp := (X_ktemp - MOD(X_ktemp, 256))/256;
    X_dataint(X_dlen + 2) := MOD(X_ktemp, 256);
    X_ktemp := (X_ktemp - MOD(X_ktemp, 256))/256;
    X_dataint(X_dlen + 1) := MOD(X_ktemp, 256);
    X_dlen := X_dlen + 4;
    END IF;
    END LOOP;


    -- If necessary, pad the data string with zeros
    X_hlimit := SETHASHLIMIT(NULL);

    WHILE (X_dlen < X_hlimit) LOOP
    X_dlen := X_dlen + 1;
    X_dataint(X_dlen) := 0;
    END LOOP;

    IF (X_dlen > X_hlimit) THEN
    X_dlen := X_hlimit;
    END IF;


    -- Loop through the data string and encrypt the result

    X_rccount := 1;
    X_rcswap := 0;
    X_datahex := '';

    FOR X_kloc IN 1..X_dlen LOOP
    IF (X_rccount >= 256) THEN
    X_rccount := X_rccount - 256;
    END IF;

    X_rccount := X_rccount + 1;
    X_ktemp := X_rctable(X_rccount);
    X_rcswap := X_rcswap + X_ktemp;


    IF (X_rcswap >= 256) THEN
    X_rcswap := X_rcswap - 256;
    END IF;

    X_rctable(X_rccount) := X_rctable(X_rcswap + 1);
    X_rctable(X_rcswap + 1) := X_ktemp;
    X_ktemp := X_ktemp + X_rctable(X_rccount);


    IF (X_ktemp >= 256) THEN
    X_ktemp := X_ktemp - 256;
    END IF;

    X_datahex := X_datahex || XORTOHEX(X_dataint(X_kloc), X_rctable(X_ktemp + 1));
    END LOOP;
    RETURN(X_datahex);
    END ADDHASH;


    FUNCTION SUBHASH(P_keystring IN VARCHAR2, P_datahex IN VARCHAR2) RETURN VARCHAR2
    IS
    X_rctable INTTAB;
    X_rccount BINARY_INTEGER;
    X_rcswap BINARY_INTEGER;
    X_slen BINARY_INTEGER;
    X_dlen BINARY_INTEGER;
    X_kloc BINARY_INTEGER;
    X_ktemp BINARY_INTEGER;
    X_datastring VARCHAR2(2000);
    X_dataval BINARY_INTEGER;
    X_newval VARCHAR2(1);
    BEGIN
    -- Initialize RC4 from key string
    RC4INIT(P_keystring, X_rctable);


    X_slen := length(P_datahex)/2;
    X_dlen := SETHASHLIMIT(NULL);

    IF (X_slen > X_dlen) THEN
    X_slen := X_dlen;
    END IF;


    -- Loop through the data string and decrypt the result
    X_rccount := 1;
    X_rcswap := 0;
    X_datastring := '';

    FOR X_kloc IN 1..X_slen LOOP
    IF (X_rccount >= 256) THEN
    X_rccount := X_rccount - 256;
    END IF;

    X_rccount := X_rccount + 1;
    X_ktemp := X_rctable(X_rccount);
    X_rcswap := X_rcswap + X_ktemp;

    IF (X_rcswap >= 256) THEN
    X_rcswap := X_rcswap - 256;
    END IF;

    X_rctable(X_rccount) := X_rctable(X_rcswap + 1);
    X_rctable(X_rcswap+ 1) := X_ktemp;
    X_ktemp := X_ktemp + X_rctable(X_rccount);

    IF (X_ktemp >= 256) THEN
    X_ktemp := X_ktemp - 256;
    END IF;


    -- Turn the hex string into a series of binary integer byte values
    X_dataval := instr('0123456789ABCDEF',substr(P_datahex,X_kloc*2-1,1)) * 16 +
    instr('0123456789ABCDEF',substr(P_datahex,X_kloc*2 ,1)) - 17;
    X_newval := XORTOCHR(X_dataval, X_rctable(X_ktemp + 1));

    EXIT WHEN (X_newval = CHR(0));

    X_datastring := X_datastring || X_newval;
    END LOOP;
    RETURN(X_datastring);
    END SUBHASH;


    FUNCTION SETHASHLIMIT(P_newlimit IN NUMBER) RETURN NUMBER IS
    /*
    ** Set internal hashing limit (default is 32). The old limit is returned
    */
    X_oldlimit NUMBER;
    BEGIN
    IF (X_hashlimit IS NULL) THEN
    X_hashlimit := 32;
    END IF;

    /* OLDLIMIT := HASHLIMIT;*/ -- ModIFied on 09/02/2000 - By RNK
    IF (P_newlimit IS NOT NULL) THEN
    X_hashlimit := P_newlimit;
    END IF;

    X_oldlimit := X_hashlimit;
    RETURN (X_oldlimit);
    END SETHASHLIMIT;


    END APP_PASSWORD;
    /
    Spool off;
    The biggest adventure u can take is to live life of your dreams..
    Bidiyasar a!!!!!

  3. #23
    so this is the code for passwprd encryptiona nd decryption which is in PL/SQL and i need to change it to TSQL


    What i did was i changed all the functions individually and tried creating those functions..

    but after successfull creation of 2 function in the third function xorchar there is a variable which is declared in the package.. and that cannot be used.. since.. its data type is some INTTAB and im finding out what is the equivalent for that in TSQL



    regards
    Ashe
    The biggest adventure u can take is to live life of your dreams..
    Bidiyasar a!!!!!

  4. #24
    Quote Originally Posted by ashe View Post
    so this is the code for passwprd encryptiona nd decryption which is in PL/SQL and i need to change it to TSQL


    What i did was i changed all the functions individually and tried creating those functions..

    but after successfull creation of 2 function in the third function xorchar there is a variable which is declared in the package.. and that cannot be used.. since.. its data type is some INTTAB and im finding out what is the equivalent for that in TSQL



    regards
    Ashe
    Looks like INTTAB is a table.
    Is this some SAP stuff?

  5. #25
    Here it is: - you need to assemble it into a package.

    --SWISSQL DROP SCRIPTS
    If Exists ( SELECT name
    FROM sysobjects
    WHERE name = 'ENCRYPT'
    AND type = 'FN')
    DROP FUNCTION ENCRYPT
    GO
    CREATE FUNCTION ENCRYPT
    (
    @P_string VARCHAR(4000)
    )
    RETURNS VARCHAR(4000)
    AS
    BEGIN

    DECLARE @X_key VARCHAR(100)
    DECLARE @X_return VARCHAR(2000)
    DECLARE @X_length FLOAT
    DECLARE @X_counter FLOAT
    SELECT @X_return = ''
    IF @P_string IS NOT NULL
    BEGIN
    SELECT @X_length = LEN(@P_string)
    SELECT @X_counter = 1
    SELECT @X_key = '1gf:dUR(DS=29q0+'
    WHILE @X_counter <= @X_length
    BEGIN --(

    SELECT @X_return = @X_return + DBO.addhash(@X_key, SUBSTRING(@P_string, @X_counter, 20))
    SELECT @X_counter = @X_counter + 20

    END --)

    END


    RETURN @X_return

    END


    GO
    ################################################## ################################################## ####
    --SWISSQL DROP SCRIPTS
    If Exists ( SELECT name
    FROM sysobjects
    WHERE name = 'DECRYPT'
    AND type = 'FN')
    DROP FUNCTION DECRYPT
    GO
    CREATE FUNCTION DECRYPT
    (
    @P_string VARCHAR(4000)
    )
    RETURNS VARCHAR(4000)
    AS
    BEGIN

    DECLARE @X_key VARCHAR(100)
    DECLARE @X_return VARCHAR(2000)
    DECLARE @X_length FLOAT
    DECLARE @X_counter FLOAT
    SELECT @X_return = ''
    IF @P_string IS NOT NULL
    BEGIN
    SELECT @X_length = LEN(@P_string)
    SELECT @X_counter = 1
    SELECT @X_key = '1gf:dUR(DS=29q0+'
    WHILE @X_counter <= @X_length
    BEGIN --(

    SELECT @X_return = @X_return + DBO.subhash(@X_key, SUBSTRING(@P_string, @X_counter, 64))
    SELECT @X_counter = @X_counter + 64

    END --)

    END


    RETURN @X_return

    END


    GO
    ################################################## ################################################## ####
    --SWISSQL DROP SCRIPTS
    If Exists ( SELECT name
    FROM sysobjects
    WHERE name = 'XORTOHEX'
    AND type = 'FN')
    DROP FUNCTION XORTOHEX
    GO
    CREATE FUNCTION XORTOHEX
    (
    @X_b1 INT ,
    @X_b2 INT
    )
    RETURNS VARCHAR(4000)
    AS
    BEGIN

    /*
    ** Compute the XOR of two byte values and return a 2-character
    ** hexadecimal string result.
    */

    DECLARE @X_unibble INT
    DECLARE @X_lnibble INT
    DECLARE @X_x1 INT
    DECLARE @X_x2 INT
    SELECT @X_x1 = ( @X_b1 ) % ( 16 )
    SELECT @X_x2 = ( @X_b2 ) % ( 16 )
    SELECT @X_lnibble = @X_x1 * 16 + @X_x2 + 1
    SELECT @X_unibble = ( @X_b1 - @X_x1 ) + ( @X_b2 - @X_x2 ) / 16 + 1

    RETURN (SUBSTRING(X_hextable, @X_unibble, 1) +SUBSTRING(X_hextable, @X_lnibble, 1) )

    END


    GO
    ################################################## ################################################## ####
    --SWISSQL DROP SCRIPTS
    If Exists ( SELECT name
    FROM sysobjects
    WHERE name = 'XORTOCHR'
    AND type = 'FN')
    DROP FUNCTION XORTOCHR
    GO
    CREATE FUNCTION XORTOCHR
    (
    @X_b1 INT ,
    @X_b2 INT
    )
    RETURNS VARCHAR(4000)
    AS
    BEGIN

    /*
    ** Compute the XOR of two byte values and return a 1-byte (character)
    ** result. (Note: this might be invalid for multibyte character
    ** sets, since this function will THEN be returning only a portion
    ** of a full character in the CHR result -- needs to be checked.)
    */

    DECLARE @X_unibble INT
    DECLARE @X_lnibble INT
    DECLARE @X_x1 INT
    DECLARE @X_x2 INT
    SELECT @X_x1 = ( @X_b1 ) % ( 16 )
    SELECT @X_x2 = ( @X_b2 ) % ( 16 )
    SELECT @X_lnibble = @X_x1 * 16 + @X_x2 + 1
    SELECT @X_unibble = ( @X_b1 - @X_x1 ) + ( @X_b2 - @X_x2 ) / 16 + 1

    RETURN (CHAR(CHARINDEX(SUBSTRING(X_hextable, @X_unibble, 1), '0123456789ABCDEF') * 16 + CHARINDEX(SUBSTRING(X_hextable, @X_lnibble, 1), '0123456789ABCDEF') - 17) )

    END


    GO
    ################################################## ################################################## ####

  6. #26
    --SWISSQL DROP SCRIPTS
    If Exists ( SELECT name
    FROM sysobjects
    WHERE name = 'RC4INIT'
    AND type = 'P')
    DROP PROCEDURE RC4INIT
    GO
    CREATE PROCEDURE RC4INIT
    @P_keystring VARCHAR(4000) ,
    @P_rctable INTTAB OUTPUT
    AS
    BEGIN
    SET NOCOUNT ON

    /*
    ** Initialize RC4 from the KEYSTRING, common to both ENCRYPT and DECRYPT.
    */

    DECLARE @X_dataint INTTAB
    DECLARE @X_rccount INT
    DECLARE @X_rcswap INT
    DECLARE @X_slen INT
    DECLARE @X_dlen INT
    DECLARE @X_kloc INT
    DECLARE @X_ktemp INT
    -- Check for key match with previous initialization

    IF ( SUBSTRING(@P_keystring, 1, X_keylimit)= X_savekey )
    BEGIN
    SELECT @P_rctable = X_savetable
    END
    ELSE
    BEGIN
    -- Turn the key string into a series of binary integer byte values

    SELECT @X_dlen = 0
    SELECT @X_slen = LEN(@P_keystring)
    IF ( @X_slen > X_keylimit )
    BEGIN
    SELECT @X_slen = X_keylimit
    END
    SELECT @X_kloc = 1

    WHILE @X_kloc <= @X_slen

    BEGIN

    SELECT @X_ktemp = ASCII(SUBSTRING(@P_keystring, @X_kloc, 1))
    SET @LoopProcessingFlag = 0
    IF ( @X_ktemp < 256 )
    BEGIN
    SELECT @X_dlen = @X_dlen + 1
    INSERT INTO @X_dataint( tempIdx , X_dataint_COL1 ) VALUES (@X_dlen, @X_ktemp)
    SET @LoopProcessingFlag = 1
    END
    IF ( @X_ktemp < 256 * 256 ) AND (@LoopProcessingFlag = 0)
    BEGIN
    IF EXISTS (SELECT * FROM @X_dataint WHERE tempIdx=@X_dlen+2)
    UPDATE @X_dataint SET X_dataint_COL1 = ( @X_ktemp ) % ( 256 ) WHERE tempIdx = @X_dlen+2
    ELSE
    INSERT INTO @X_dataint( tempIdx , X_dataint_COL1 ) VALUES (@X_dlen+2, ( @X_ktemp ) % ( 256 ))

    SELECT @X_ktemp = ( @X_ktemp - ( @X_ktemp ) % ( 256 )) / 256
    IF EXISTS (SELECT * FROM @X_dataint WHERE tempIdx=@X_dlen+1)
    UPDATE @X_dataint SET X_dataint_COL1 = ( @X_ktemp ) % ( 256 ) WHERE tempIdx = @X_dlen+1
    ELSE
    INSERT INTO @X_dataint( tempIdx , X_dataint_COL1 ) VALUES (@X_dlen+1, ( @X_ktemp ) % ( 256 ))

    SELECT @X_dlen = @X_dlen + 2
    SET @LoopProcessingFlag = 1
    END
    IF ( @X_ktemp < 256 * 256 * 256 ) AND (@LoopProcessingFlag = 0)
    BEGIN
    IF EXISTS (SELECT * FROM @X_dataint WHERE tempIdx=@X_dlen+3)
    UPDATE @X_dataint SET X_dataint_COL1 = ( @X_ktemp ) % ( 256 ) WHERE tempIdx = @X_dlen+3
    ELSE
    INSERT INTO @X_dataint( tempIdx , X_dataint_COL1 ) VALUES (@X_dlen+3, ( @X_ktemp ) % ( 256 ))

    SELECT @X_ktemp = ( @X_ktemp - ( @X_ktemp ) % ( 256 )) / 256
    IF EXISTS (SELECT * FROM @X_dataint WHERE tempIdx=@X_dlen+2)
    UPDATE @X_dataint SET X_dataint_COL1 = ( @X_ktemp ) % ( 256 ) WHERE tempIdx = @X_dlen+2
    ELSE
    INSERT INTO @X_dataint( tempIdx , X_dataint_COL1 ) VALUES (@X_dlen+2, ( @X_ktemp ) % ( 256 ))

    SELECT @X_ktemp = ( @X_ktemp - ( @X_ktemp ) % ( 256 )) / 256
    IF EXISTS (SELECT * FROM @X_dataint WHERE tempIdx=@X_dlen+1)
    UPDATE @X_dataint SET X_dataint_COL1 = ( @X_ktemp ) % ( 256 ) WHERE tempIdx = @X_dlen+1
    ELSE
    INSERT INTO @X_dataint( tempIdx , X_dataint_COL1 ) VALUES (@X_dlen+1, ( @X_ktemp ) % ( 256 ))

    SELECT @X_dlen = @X_dlen + 3
    SET @LoopProcessingFlag = 1
    END
    IF (@LoopProcessingFlag = 0)
    BEGIN
    IF EXISTS (SELECT * FROM @X_dataint WHERE tempIdx=@X_dlen+4)
    UPDATE @X_dataint SET X_dataint_COL1 = ( @X_ktemp ) % ( 256 ) WHERE tempIdx = @X_dlen+4
    ELSE
    INSERT INTO @X_dataint( tempIdx , X_dataint_COL1 ) VALUES (@X_dlen+4, ( @X_ktemp ) % ( 256 ))

    SELECT @X_ktemp = ( @X_ktemp - ( @X_ktemp ) % ( 256 )) / 256
    IF EXISTS (SELECT * FROM @X_dataint WHERE tempIdx=@X_dlen+3)
    UPDATE @X_dataint SET X_dataint_COL1 = ( @X_ktemp ) % ( 256 ) WHERE tempIdx = @X_dlen+3
    ELSE
    INSERT INTO @X_dataint( tempIdx , X_dataint_COL1 ) VALUES (@X_dlen+3, ( @X_ktemp ) % ( 256 ))

    SELECT @X_ktemp = ( @X_ktemp - ( @X_ktemp ) % ( 256 )) / 256
    IF EXISTS (SELECT * FROM @X_dataint WHERE tempIdx=@X_dlen+2)
    UPDATE @X_dataint SET X_dataint_COL1 = ( @X_ktemp ) % ( 256 ) WHERE tempIdx = @X_dlen+2
    ELSE
    INSERT INTO @X_dataint( tempIdx , X_dataint_COL1 ) VALUES (@X_dlen+2, ( @X_ktemp ) % ( 256 ))

    SELECT @X_ktemp = ( @X_ktemp - ( @X_ktemp ) % ( 256 )) / 256
    IF EXISTS (SELECT * FROM @X_dataint WHERE tempIdx=@X_dlen+1)
    UPDATE @X_dataint SET X_dataint_COL1 = ( @X_ktemp ) % ( 256 ) WHERE tempIdx = @X_dlen+1
    ELSE
    INSERT INTO @X_dataint( tempIdx , X_dataint_COL1 ) VALUES (@X_dlen+1, ( @X_ktemp ) % ( 256 ))

    SELECT @X_dlen = @X_dlen + 4
    END


    SELECT @X_kloc = @X_kloc + 1

    END

    -- Initialize the RC4 algorithm using the encryption key string

    SELECT @X_rccount = 1

    WHILE @X_rccount <= 256

    BEGIN

    IF EXISTS (SELECT * FROM @P_rctable WHERE tempIdx=@X_rccount)
    UPDATE @P_rctable SET P_rctable_COL1 = @X_rccount-1 WHERE tempIdx = @X_rccount
    ELSE
    INSERT INTO @P_rctable( tempIdx , P_rctable_COL1 ) VALUES (@count, @X_rccount - 1)


    SELECT @X_rccount = @X_rccount + 1

    END


    SELECT @X_rcswap = 0
    SELECT @X_kloc = 1 SELECT @X_rccount = 1

    WHILE @X_rccount <= 256

    BEGIN

    SELECT @X_ktemp = DBO.P_rctable(@X_rccount)
    SELECT @X_rcswap = @X_rcswap + @X_ktemp + DBO.X_dataint(@X_kloc)
    SET @LoopProcessingFlag = 0
    IF ( @X_rcswap >= 512 )
    BEGIN
    SELECT @X_rcswap = @X_rcswap - 512
    SET @LoopProcessingFlag = 1
    END
    IF ( @X_rcswap >= 256 ) AND (@LoopProcessingFlag = 0)
    BEGIN
    SELECT @X_rcswap = @X_rcswap - 256
    END

    IF EXISTS (SELECT * FROM @P_rctable WHERE tempIdx=@X_rccount)
    UPDATE @P_rctable SET P_rctable_COL1 = DBO.P_rctable(@X_rcswap + 1) AND b.tempIdx = @X_rccount
    ELSE
    INSERT INTO @P_rctable( tempIdx , P_rctable_COL1 ) VALUES (@count, DBO.P_rctable(@X_rcswap + 1))

    INSERT INTO @P_rctable( tempIdx , P_rctable_COL1 ) VALUES (@X_rcswap+1, @X_ktemp)
    IF ( @X_kloc >= @X_dlen )
    BEGIN
    SELECT @X_kloc = @X_kloc - @X_dlen
    END

    SELECT @X_kloc = @X_kloc + 1

    SELECT @X_rccount = @X_rccount + 1

    END


    -- Save table for next time

    SELECT @X_savetable = @P_rctable
    SELECT @X_savekey = SUBSTRING(@P_keystring, 1, X_keylimit)
    END


    SET NOCOUNT OFF

    END


    GO
    ################################################## ################################################## ####
    CREATE FUNCTION getLoginTime()
    RETURNS DATETIME
    AS
    BEGIN
    DECLARE @globalLoginTime DATETIME
    SELECT @globalLoginTime=login_time FROM master..sysprocesses WHERE spid=@@SPID
    RETURN @globalLoginTime
    END
    GO

  7. #27
    ################################################## ################################################## ####
    --SWISSQL DROP SCRIPTS
    If Exists ( SELECT name
    FROM sysobjects
    WHERE name = 'ADDHASH'
    AND type = 'P')
    DROP PROCEDURE ADDHASH
    GO
    CREATE PROCEDURE ADDHASH
    @P_keystring VARCHAR(4000) ,
    @P_datastring VARCHAR(4000) ,
    @return_val VARCHAR(4000) OUTPUT
    AS
    BEGIN
    SET NOCOUNT ON

    DECLARE @X_dataint INTTAB
    DECLARE @X_rctable INTTAB
    DECLARE @X_rccount INT
    DECLARE @X_rcswap INT
    DECLARE @X_slen INT
    DECLARE @X_dlen INT
    DECLARE @X_kloc INT
    DECLARE @X_ktemp INT
    DECLARE @X_datahex VARCHAR(2000)
    DECLARE @X_hlimit FLOAT
    -- Initialize RC4 from key string

    EXEC RC4INIT @P_keystring , @X_rctable
    -- Turn the data string into a series of binary integer byte values

    SELECT @X_dlen = 0
    SELECT @X_slen = LEN(@P_datastring)SELECT @X_kloc = 1

    WHILE @X_kloc <= @X_slen

    BEGIN

    SELECT @X_ktemp = ASCII(SUBSTRING(@P_datastring, @X_kloc, 1))
    SET @LoopProcessingFlag = 0
    IF ( @X_ktemp < 256 )
    BEGIN
    SELECT @X_dlen = @X_dlen + 1
    INSERT INTO @X_dataint( tempIdx , X_dataint_COL1 ) VALUES (@X_dlen, @X_ktemp)
    SET @LoopProcessingFlag = 1
    END
    IF ( @X_ktemp < 256 * 256 ) AND (@LoopProcessingFlag = 0)
    BEGIN
    IF EXISTS (SELECT * FROM @X_dataint WHERE tempIdx=@X_dlen+2)
    UPDATE @X_dataint SET X_dataint_COL1 = ( @X_ktemp ) % ( 256 ) WHERE tempIdx = @X_dlen+2
    ELSE
    INSERT INTO @X_dataint( tempIdx , X_dataint_COL1 ) VALUES (@X_dlen+2, ( @X_ktemp ) % ( 256 ))

    SELECT @X_ktemp = ( @X_ktemp - ( @X_ktemp ) % ( 256 )) / 256
    IF EXISTS (SELECT * FROM @X_dataint WHERE tempIdx=@X_dlen+1)
    UPDATE @X_dataint SET X_dataint_COL1 = ( @X_ktemp ) % ( 256 ) WHERE tempIdx = @X_dlen+1
    ELSE
    INSERT INTO @X_dataint( tempIdx , X_dataint_COL1 ) VALUES (@X_dlen+1, ( @X_ktemp ) % ( 256 ))

    SELECT @X_dlen = @X_dlen + 2
    SET @LoopProcessingFlag = 1
    END
    IF ( @X_ktemp < 256 * 256 * 256 ) AND (@LoopProcessingFlag = 0)
    BEGIN
    IF EXISTS (SELECT * FROM @X_dataint WHERE tempIdx=@X_dlen+3)
    UPDATE @X_dataint SET X_dataint_COL1 = ( @X_ktemp ) % ( 256 ) WHERE tempIdx = @X_dlen+3
    ELSE
    INSERT INTO @X_dataint( tempIdx , X_dataint_COL1 ) VALUES (@X_dlen+3, ( @X_ktemp ) % ( 256 ))

    SELECT @X_ktemp = ( @X_ktemp - ( @X_ktemp ) % ( 256 )) / 256
    IF EXISTS (SELECT * FROM @X_dataint WHERE tempIdx=@X_dlen+2)
    UPDATE @X_dataint SET X_dataint_COL1 = ( @X_ktemp ) % ( 256 ) WHERE tempIdx = @X_dlen+2
    ELSE
    INSERT INTO @X_dataint( tempIdx , X_dataint_COL1 ) VALUES (@X_dlen+2, ( @X_ktemp ) % ( 256 ))

    SELECT @X_ktemp = ( @X_ktemp - ( @X_ktemp ) % ( 256 )) / 256
    IF EXISTS (SELECT * FROM @X_dataint WHERE tempIdx=@X_dlen+1)
    UPDATE @X_dataint SET X_dataint_COL1 = ( @X_ktemp ) % ( 256 ) WHERE tempIdx = @X_dlen+1
    ELSE
    INSERT INTO @X_dataint( tempIdx , X_dataint_COL1 ) VALUES (@X_dlen+1, ( @X_ktemp ) % ( 256 ))

    SELECT @X_dlen = @X_dlen + 3
    SET @LoopProcessingFlag = 1
    END
    IF (@LoopProcessingFlag = 0)
    BEGIN
    IF EXISTS (SELECT * FROM @X_dataint WHERE tempIdx=@X_dlen+4)
    UPDATE @X_dataint SET X_dataint_COL1 = ( @X_ktemp ) % ( 256 ) WHERE tempIdx = @X_dlen+4
    ELSE
    INSERT INTO @X_dataint( tempIdx , X_dataint_COL1 ) VALUES (@X_dlen+4, ( @X_ktemp ) % ( 256 ))

    SELECT @X_ktemp = ( @X_ktemp - ( @X_ktemp ) % ( 256 )) / 256
    IF EXISTS (SELECT * FROM @X_dataint WHERE tempIdx=@X_dlen+3)
    UPDATE @X_dataint SET X_dataint_COL1 = ( @X_ktemp ) % ( 256 ) WHERE tempIdx = @X_dlen+3
    ELSE
    INSERT INTO @X_dataint( tempIdx , X_dataint_COL1 ) VALUES (@X_dlen+3, ( @X_ktemp ) % ( 256 ))

    SELECT @X_ktemp = ( @X_ktemp - ( @X_ktemp ) % ( 256 )) / 256
    IF EXISTS (SELECT * FROM @X_dataint WHERE tempIdx=@X_dlen+2)
    UPDATE @X_dataint SET X_dataint_COL1 = ( @X_ktemp ) % ( 256 ) WHERE tempIdx = @X_dlen+2
    ELSE
    INSERT INTO @X_dataint( tempIdx , X_dataint_COL1 ) VALUES (@X_dlen+2, ( @X_ktemp ) % ( 256 ))

    SELECT @X_ktemp = ( @X_ktemp - ( @X_ktemp ) % ( 256 )) / 256
    IF EXISTS (SELECT * FROM @X_dataint WHERE tempIdx=@X_dlen+1)
    UPDATE @X_dataint SET X_dataint_COL1 = ( @X_ktemp ) % ( 256 ) WHERE tempIdx = @X_dlen+1
    ELSE
    INSERT INTO @X_dataint( tempIdx , X_dataint_COL1 ) VALUES (@X_dlen+1, ( @X_ktemp ) % ( 256 ))

    SELECT @X_dlen = @X_dlen + 4
    END


    SELECT @X_kloc = @X_kloc + 1

    END


    -- If necessary, pad the data string with zeros

    SELECT @X_hlimit = DBO.SETHASHLIMIT(null)
    WHILE ( @X_dlen < @X_hlimit )
    BEGIN --(

    SELECT @X_dlen = @X_dlen + 1
    IF EXISTS (SELECT * FROM @X_dataint WHERE tempIdx=@X_dlen)
    UPDATE @X_dataint SET X_dataint_COL1 = 0 WHERE tempIdx = @X_dlen
    ELSE
    INSERT INTO @X_dataint( tempIdx , X_dataint_COL1 ) VALUES (@X_dlen, 0)


    END --)

    IF ( @X_dlen > @X_hlimit )
    BEGIN
    SELECT @X_dlen = @X_hlimit
    END

    -- Loop through the data string and encrypt the result

    SELECT @X_rccount = 1
    SELECT @X_rcswap = 0
    SELECT @X_datahex = '' SELECT @X_kloc = 1

    WHILE @X_kloc <= @X_dlen

    BEGIN

    IF ( @X_rccount >= 256 )
    BEGIN
    SELECT @X_rccount = @X_rccount - 256
    END

    SELECT @X_rccount = @X_rccount + 1
    SELECT @X_ktemp = DBO.X_rctable(@X_rccount)
    SELECT @X_rcswap = @X_rcswap + @X_ktemp
    IF ( @X_rcswap >= 256 )
    BEGIN
    SELECT @X_rcswap = @X_rcswap - 256
    END

    IF EXISTS (SELECT * FROM @X_rctable WHERE tempIdx=@X_rccount)
    UPDATE @X_rctable SET X_rctable_COL1 = DBO.X_rctable(@X_rcswap + 1) AND b.tempIdx = @X_rccount
    ELSE
    INSERT INTO @X_rctable( tempIdx , X_rctable_COL1 ) VALUES (@count, DBO.X_rctable(@X_rcswap + 1))

    INSERT INTO @X_rctable( tempIdx , X_rctable_COL1 ) VALUES (@X_rcswap+1, @X_ktemp)
    SELECT @X_ktemp = @X_ktemp + DBO.X_rctable(@X_rccount)
    IF ( @X_ktemp >= 256 )
    BEGIN
    SELECT @X_ktemp = @X_ktemp - 256
    END

    SELECT @X_datahex = @X_datahex + DBO.XORTOHEX(DBO.X_dataint(@X_kloc), DBO.X_rctable(@X_ktemp + 1))

    SELECT @X_kloc = @X_kloc + 1

    END



    SELECT @return_val = (@X_datahex)

    SET NOCOUNT OFF

    END


    GO
    ################################################## ################################################## ####

  8. #28
    --SWISSQL DROP SCRIPTS
    If Exists ( SELECT name
    FROM sysobjects
    WHERE name = 'SUBHASH'
    AND type = 'P')
    DROP PROCEDURE SUBHASH
    GO
    CREATE PROCEDURE SUBHASH
    @P_keystring VARCHAR(4000) ,
    @P_datahex VARCHAR(4000) ,
    @return_val VARCHAR(4000) OUTPUT
    AS
    BEGIN
    SET NOCOUNT ON

    DECLARE @X_rctable INTTAB
    DECLARE @X_rccount INT
    DECLARE @X_rcswap INT
    DECLARE @X_slen INT
    DECLARE @X_dlen INT
    DECLARE @X_kloc INT
    DECLARE @X_ktemp INT
    DECLARE @X_datastring VARCHAR(2000)
    DECLARE @X_dataval INT
    DECLARE @X_newval VARCHAR(1)
    -- Initialize RC4 from key string

    EXEC RC4INIT @P_keystring , @X_rctable
    SELECT @X_slen = LEN(@P_datahex)/ 2
    SELECT @X_dlen = DBO.SETHASHLIMIT(null)
    IF ( @X_slen > @X_dlen )
    BEGIN
    SELECT @X_slen = @X_dlen
    END

    -- Loop through the data string and decrypt the result

    SELECT @X_rccount = 1
    SELECT @X_rcswap = 0
    SELECT @X_datastring = '' SELECT @X_kloc = 1

    WHILE @X_kloc <= @X_slen

    BEGIN

    IF ( @X_rccount >= 256 )
    BEGIN
    SELECT @X_rccount = @X_rccount - 256
    END

    SELECT @X_rccount = @X_rccount + 1
    SELECT @X_ktemp = DBO.X_rctable(@X_rccount)
    SELECT @X_rcswap = @X_rcswap + @X_ktemp
    IF ( @X_rcswap >= 256 )
    BEGIN
    SELECT @X_rcswap = @X_rcswap - 256
    END

    IF EXISTS (SELECT * FROM @X_rctable WHERE tempIdx=@X_rccount)
    UPDATE @X_rctable SET X_rctable_COL1 = DBO.X_rctable(@X_rcswap + 1) AND b.tempIdx = @X_rccount
    ELSE
    INSERT INTO @X_rctable( tempIdx , X_rctable_COL1 ) VALUES (@count, DBO.X_rctable(@X_rcswap + 1))

    INSERT INTO @X_rctable( tempIdx , X_rctable_COL1 ) VALUES (@X_rcswap+1, @X_ktemp)
    SELECT @X_ktemp = @X_ktemp + DBO.X_rctable(@X_rccount)
    IF ( @X_ktemp >= 256 )
    BEGIN
    SELECT @X_ktemp = @X_ktemp - 256
    END

    -- Turn the hex string into a series of binary integer byte values

    SELECT @X_dataval = CHARINDEX(SUBSTRING(@P_datahex, @X_kloc * 2 - 1, 1), '0123456789ABCDEF')* 16 + CHARINDEX(SUBSTRING(@P_datahex, @X_kloc * 2, 1), '0123456789ABCDEF')- 17
    SELECT @X_newval = DBO.XORTOCHR(@X_dataval, DBO.X_rctable(@X_ktemp + 1))
    IF ( @X_newval = )
    BREAK

    SELECT @X_datastring = @X_datastring + @X_newval

    SELECT @X_kloc = @X_kloc + 1

    END



    SELECT @return_val = (@X_datastring)

    SET NOCOUNT OFF

    END


    GO
    ################################################## ################################################## ####
    CREATE FUNCTION getLoginTime()
    RETURNS DATETIME
    AS
    BEGIN
    DECLARE @globalLoginTime DATETIME
    SELECT @globalLoginTime=login_time FROM master..sysprocesses WHERE spid=@@SPID
    RETURN @globalLoginTime
    END
    GO
    --SWISSQL DROP SCRIPTS
    If Exists ( SELECT name
    FROM sysobjects
    WHERE name = 'SETHASHLIMIT'
    AND type = 'P')
    DROP PROCEDURE SETHASHLIMIT
    GO
    CREATE PROCEDURE SETHASHLIMIT
    @P_newlimit FLOAT ,
    @return_val FLOAT OUTPUT
    AS
    BEGIN
    SET NOCOUNT ON

    /*
    ** Set internal hashing limit (default is 32). The old limit is returned
    */

    DECLARE @X_oldlimit FLOAT
    IF ( X_hashlimit IS NULL )
    BEGIN
    SELECT @X_hashlimit = 32
    END

    /* OLDLIMIT := HASHLIMIT;*/
    -- ModIFied on 09/02/2000 - By RNK

    IF ( @P_newlimit IS NOT NULL )
    BEGIN
    SELECT @X_hashlimit = @P_newlimit
    END

    SELECT @X_oldlimit = @X_hashlimit

    SELECT @return_val = (@X_oldlimit)

    SET NOCOUNT OFF

    END


    GO
    ################################################## ################################################## ####

  9. #29
    Quote Originally Posted by arvindc View Post
    looks like inttab is a table.
    Is this some sap stuff?
    it is declared upfront at start of the package.
    TYPE INTTAB IS TABLE OF BINARY_INTEGER INDEX BY BINARY_INTEGER;
    TYPE NUMTAB IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

  10. #30
    Hi...Arvind

    it is declared upfront at start of the package.
    TYPE INTTAB IS TABLE OF BINARY_INTEGER INDEX BY BINARY_INTEGER;
    TYPE NUMTAB IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

    thanks but please read this


    What i did was i changed all the functions individually and tried creating those functions..

    but after successfull creation of 2 function in the third function xorchar there is a variable which is declared in the package.. and that cannot be used.. since.. its data type is some INTTAB and im finding out what is the equivalent for that in TSQL


    i have already done this.. but facing problem in creating objects.....


    Regards
    Ashe
    The biggest adventure u can take is to live life of your dreams..
    Bidiyasar a!!!!!

  11. #31
    try something like this.


    SQL> create type inttab as table of number 2 /

    and then execute the procedure.

  12. #32
    yepp trying to do that...


    trying to do that..


    Thanks
    Ashe
    The biggest adventure u can take is to live life of your dreams..
    Bidiyasar a!!!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •