Oracle to SqlServer Code conversion

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;
 
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;
 
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
 
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?
 
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
########################################################################################################
 
--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
 
########################################################################################################
--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
########################################################################################################
 
--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
########################################################################################################
 
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
 
Back
Top