Creating object from an object w/sp_OACreate

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Creating object from an object w/sp_OACreate

Author

Topic

KHeon
Posting Yak Master

135 Posts

Posted - 2002-08-20 : 11:53:06

Help!

I've done this before, but somehow misplaced the code. I'm creating a stored procedure that takes a user password, generates a seed_value, concatenates the two and then hashes them using ASPEncrypt (www.aspencrypt.com).

For some reason I'm getting a Type Mismatch error when I try to call an objects method which returns another object. Here is the ASP code that I would use to do this outside of SQL Server:


Set CM = Server.CreateObject("Persits.CryptoManager")
Set Context = CM.OpenContext("mycontainer", True)
Set Hash = Context.CreateHash
Hash.AddText "some text"
Response.Write Hash.Value.Hex

Here is the code I currently have in my stored proc:


DECLARE @user_password VARCHAR(15)
DECLARE @seed_value CHAR(10)
DECLARE @cm INT
DECLARE @context INT
DECLARE @hash INT

DECLARE @hr int
DECLARE @src varchar(255)
DECLARE @desc varchar(255)

SET @user_password = 'jessica'
SET @seed_value = 'aB1cD2Ef3z'

-- Create CryptoManager object.
EXEC @hr = sp_OACreate 'Persits.CryptoManager', @cm OUT

-- Check object creation status (success/failed).
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @cm, @src OUT, @desc OUT 
   SELECT hr = CONVERT(VARBINARY(4), @hr), Source = @src, Description = @desc
    RETURN
END

-- Create Context object.
/* Set Context = CM.OpenContext("mycontainer", True) */
EXEC @hr = sp_OAMethod @cm, 'OpenContext', @context
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @cm, @src OUT, @desc OUT 
   SELECT hr = CONVERT(VARBINARY(4), @hr), Source = @src, Description = @desc
   RETURN
END

The error I'm getting is this:


0x80042725	ODSOLE Extended Procedure	sp_OAMethod usage:  ObjPointer int IN, MethodName varchar IN [, @returnval <any> OUT [, additional IN, OUT, or BOTH params]]

Can anyone see what I'm doing wrong? It's been awhile since I played with sp_OA sprocs, and everytime I do I have to re-learn what I've forgotten.

Thanks in advance!

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-08-20 : 12:48:15

Shouldn't it be something like

EXEC @hr = sp_OAMethod @cm, 'OpenContext', @context OUT

or

EXEC @hr = sp_OAMethod @cm, 'OpenContext', @context OUT, 'mycontainer','True'

HTH
Jasper Smith

KHeon
Posting Yak Master

135 Posts

Posted - 2002-08-20 : 13:13:48

Ah, nice catch, actually I'm just playing around with stuff and apparently copied the wrong example.


-- Check object creation status (success/failed).
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @cm, @src OUT, @desc OUT 
   SELECT hr = CONVERT(VARBINARY(4), @hr), Source = @src, Description = @desc
    RETURN
END

-- Create Context object.
/* Set Context = CM.OpenContext("mycontainer", True) */
EXEC @hr = sp_OAMethod @cm, 'OpenContext', @context OUT, 'mycontainer', True
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @cm, @src OUT, @desc OUT 
   SELECT hr = CONVERT(VARBINARY(4), @hr), Source = @src, Description = @desc
   RETURN
END

I've tried using 'True' and True w/out any luck.

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

KHeon
Posting Yak Master

135 Posts

Posted - 2002-08-20 : 14:04:35

Figured this one out. You can't pass True or False as is to SQL, their is no equivalent operator (no boolean), only bit. I changed True to 0 and it worked (just hope that 0 is the same as True)...

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-20 : 14:06:43

Zero is false. True is either 1 or -1, depending on which language the COM object was written in.

KHeon
Posting Yak Master

135 Posts

Posted - 2002-08-20 : 15:04:08

Thanks rob! I can, for some reason, never remember which is which. I know it's sad, such a simple thing to remember.

Let me guess, Visual Basic is 1, C++ would be -1?

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources