SqlServer 2000 sp_OAMethod - WeOnlyDo Discussion board

SqlServer 2000 sp_OAMethod (wodCrypt)

by beto21971, Friday, August 21, 2009, 05:38 (5333 days ago)

hI, i was trying to use wodcrypt from inside SqlServer 2000, with sp_OAxxx storeprocedures, but i couldn't. Can anybody helpme? Thats the code in transact sqlserver 2000, and i allways get error.

DECLARE @TXT_TO_CRYPT VARCHAR(4000)

SET @TXT_TO_CRYPT='HOLA MUND0'

DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
/*---------------------------------------------------------------
/ CREA EL OBJETO CRYPT
----------------------------------------------------------------*/
DECLARE @Crypt int
EXEC @hr = sp_OACreate 'WeOnlyDo.wodCryptCom.1', @Crypt OUT,1
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @Crypt, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END


/*---------------------------------------------------------------
/ CREA EL OBJETO DECBLOB
----------------------------------------------------------------*/
DECLARE @DecBlob int
/*EXEC @hr = sp_OACreate 'WeOnlyDo.MemBlob.1', @DecBlob OUT,1
*/
EXEC @hr = sp_OACreate 'WeOnlyDo.MemBlob.1', @DecBlob OUT,1
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @DecBlob, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

/*---------------------------------------------------------------
/ CREA EL OBJETO ENCBLOB
----------------------------------------------------------------*/
DECLARE @EncBlob int
/*EXEC @hr = sp_OACreate 'WeOnlyDo.MemBlob.1', @EncBlob OUT,1 */
EXEC @hr = sp_OACreate 'WeOnlyDo.MemBlob.1', @EncBlob OUT,1

IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @EncBlob, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

/*------------------------------------------------
/ SETEA PARAMETROS DE LOS OBJETOS
-------------------------------------------------*/
EXEC sp_OASetProperty @DecBlob, 'Text', 'PRUEBA'
EXEC sp_OASetProperty @Crypt , 'Type', 2 /* DES */
EXEC sp_OASetProperty @Crypt , 'SecretKey', 'WeOnlyDo!'

EXEC @hr =sp_OAMethod @Crypt, 'Encrypt', @DecBlob, @EncBlob out
IF @hr <> 0
BEGIN
PRINT 'ERROR METODO ENCRYPT: '+Convert(varchar,@hr)
EXEC sp_OAGetErrorInfo @Crypt, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

DECLARE @property varchar(4000)
EXEC @hr = sp_OAGetProperty @EncBlob, 'ToBase64', @property OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @EncBlob
RETURN
END


PRINT 'Cifrado: '+@property

exec sp_OADestroy @EncBlob
exec sp_OADestroy @DecBlob
exec sp_OADestroy @Crypt

Re: SqlServer 2000 sp_OAMethod

by wodDamir, Friday, August 21, 2009, 09:13 (5333 days ago) @ beto21971

Hi,

I'm not really familiar with SqlServer, but can you be more specific?

What error do you receive and where?

Regards,
Damba

Re: SqlServer 2000 sp_OAMethod

by beto21971, Friday, August 21, 2009, 23:01 (5333 days ago) @ wodDamir

well, the error is this:

0x80042725 ODSOLE Extended Procedure
without anykind of description

the line of code is this:

EXEC @hr =sp_OAMethod @Crypt, Encrypt , @DecBlob, @EncBlob out


the problem seems to be with the Encrypt method. It seems to be a kind of parameters problems...but i don t know. May be, because, like in C# and Delphi examples, where the memblog is CAST to BLOB until pass it as parameters...but i don t know how to do that with SQLSERVER 2000.

could you help me?...

hI, i was trying to use wodcrypt from inside SqlServer 2000, with sp_OAxxx storeprocedures, but i couldn't. Can anybody helpme? Thats the code in transact sqlserver 2000, and i allways get error.

DECLARE @TXT_TO_CRYPT VARCHAR(4000)

SET @TXT_TO_CRYPT='HOLA MUND0'

DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
/*---------------------------------------------------------------
/ CREA EL OBJETO CRYPT
----------------------------------------------------------------*/
DECLARE @Crypt int
EXEC @hr = sp_OACreate 'WeOnlyDo.wodCryptCom.1', @Crypt OUT,1
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @Crypt, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END


/*---------------------------------------------------------------
/ CREA EL OBJETO DECBLOB
----------------------------------------------------------------*/
DECLARE @DecBlob int
/*EXEC @hr = sp_OACreate 'WeOnlyDo.MemBlob.1', @DecBlob OUT,1
*/
EXEC @hr = sp_OACreate 'WeOnlyDo.MemBlob.1', @DecBlob OUT,1
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @DecBlob, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

/*---------------------------------------------------------------
/ CREA EL OBJETO ENCBLOB
----------------------------------------------------------------*/
DECLARE @EncBlob int
/*EXEC @hr = sp_OACreate 'WeOnlyDo.MemBlob.1', @EncBlob OUT,1 */
EXEC @hr = sp_OACreate 'WeOnlyDo.MemBlob.1', @EncBlob OUT,1

IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @EncBlob, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

/*------------------------------------------------
/ SETEA PARAMETROS DE LOS OBJETOS
-------------------------------------------------*/
EXEC sp_OASetProperty @DecBlob, 'Text', 'PRUEBA'
EXEC sp_OASetProperty @Crypt , 'Type', 2 /* DES */
EXEC sp_OASetProperty @Crypt , 'SecretKey', 'WeOnlyDo!'

EXEC @hr =sp_OAMethod @Crypt, 'Encrypt', @DecBlob, @EncBlob out
IF @hr <> 0
BEGIN
PRINT 'ERROR METODO ENCRYPT: '+Convert(varchar,@hr)
EXEC sp_OAGetErrorInfo @Crypt, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

DECLARE @property varchar(4000)
EXEC @hr = sp_OAGetProperty @EncBlob, 'ToBase64', @property OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @EncBlob
RETURN
END


PRINT 'Cifrado: '+@property

exec sp_OADestroy @EncBlob
exec sp_OADestroy @DecBlob
exec sp_OADestroy @Crypt

Re: SqlServer 2000 sp_OAMethod

by beto21971, Saturday, August 22, 2009, 01:23 (5333 days ago) @ beto21971

Finally, Fixed:

Correct Line Code:

EXEC @hr =sp_OAMethod @Crypt, 'Encrypt', null,@DecBlob,@EncBlob output

I let you the full code that I proved with 10 encryption algoritms and it work just fine!!!.Enjoy it.

DECLARE @TXT_TO_CRYPT VARCHAR(4000)

SET @TXT_TO_CRYPT='HOLA MUND0'

DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
DECLARE @Largo int
DECLARE @CIFRADO VARCHAR(4000)
/*---------------------------------------------------------------
/ CREA EL OBJETO CRYPT
----------------------------------------------------------------*/
DECLARE @Crypt int
EXEC @hr = sp_OACreate 'WeOnlyDo.wodCryptCom.1', @Crypt OUT,1
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @Crypt, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END


/*---------------------------------------------------------------
/ CREA EL OBJETO DECBLOB
----------------------------------------------------------------*/
DECLARE @DecBlob int
EXEC @hr = sp_OACreate 'WeOnlyDo.MemBlob.1', @DecBlob OUT,1
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @DecBlob, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

/*---------------------------------------------------------------
/ CREA EL OBJETO ENCBLOB
----------------------------------------------------------------*/
DECLARE @EncBlob int
EXEC @hr = sp_OACreate 'WeOnlyDo.MemBlob.1', @EncBlob OUT,1
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @EncBlob, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

/*------------------------------------------------
/ SETEA PARAMETROS DE LOS OBJETOS
-------------------------------------------------*/
EXEC sp_OASetProperty @DecBlob, 'Text', 'PRUEBA'
EXEC sp_OASetProperty @Crypt , 'Type', 0 /* DES */
EXEC sp_OASetProperty @Crypt , 'SecretKey', 'WeOnlyDo!'
EXEC sp_OASetProperty @Crypt , 'Mode', 0
EXEC sp_OASetProperty @Crypt , 'Optimized', 0
EXEC @hr =sp_OAMethod @Crypt, 'Encrypt', null,@DecBlob,@EncBlob output
IF @hr <> 0
BEGIN
PRINT 'ERROR METODO ENCRYPT: '+Convert(varchar,@hr)
EXEC sp_OAGetErrorInfo @Crypt
RETURN
END

EXEC @hr = sp_OAGetProperty @EncBlob, 'ToHex', @CIFRADO OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @EncBlob
RETURN
END

PRINT 'Cifrado: '+@CIFRADO

exec sp_OADestroy @EncBlob
exec sp_OADestroy @DecBlob
exec sp_OADestroy @Crypt