Bad variable type (0x80020008) SQL Server sp_OAMethod

Windows 7 64bit
MS SQL Server 64bit
Delphi 2007

I am trying to pass a string value (lpstr) to a com object I created and I am getting the that Bad Variable error when I run the following code:

  DECLARE @DeductionAmount money
  DECLARE @HResult int
  DECLARE @DLLObject int
  DECLARE @ErrorSource varchar (255)
  DECLARE @ErrorDesc varchar (255)
  EXEC @HResult = sp_OACreate 'TaxLibrary.CalculateTax', @DLLObject OUT
     
  EXEC @HResult = sp_OAMethod @DLLObject, 'CalculateTax', @DeductionAmount OUT, 1, '00-000-0000'
  IF @HResult <> 0
  BEGIN
    EXEC sp_OAGetErrorInfo @DLLObject, @ErrorSource OUT, @ErrorDesc OUT  
    RAISERROR('COM Error 0x%x, %s, %s', 16, 1, @HResult, @ErrorSource, @ErrorDesc)
    RETURN
  END


The definition for that error message is the following:
Bad variable type (0x80020008)
Data type of a Transact-SQL value passed as a method parameter did not match the Microsoft Visual Basic data type of the method parameter, or a NULL value was passed as a method parameter.
 
I did not use VB to create my com object, but I do not think that matters.  I have the parameter defined as of type lpstr in my com object.  I am not sure how I am to format the SQL any differently.

Thanks,
carv99Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cyberkiwiCommented:
Can you please show the Delphi signature for the function?
I'll give it a go when I have time.
carv99Author Commented:
Here is the code for the function with in the com object editor
HRESULT _stdcall CalculateTax([in] byte CalcType, [in] LPSTR CalcCode, [out, retval] CURRENCY * TaxAmount );

Here is the signature for the delphi function:
function TCalculateTax.CalculateTax(CalcType: Shortint; CalcCode: PChar): Currency;
carv99Author Commented:
Even in the example seen here: http://msdn.microsoft.com/en-us/library/ms174984.aspx they are passing in string values with no problem.

EXEC @hr = sp_OAMethod @object, 'Connect', NULL, 'my_server',
    'my_login', 'my_password'
IF @hr <> 0
BEGIN
   EXEC sp_OAGetErrorInfo @object
    RETURN
END

I cannot seem to find much about this online, but documentation dealing with this functionality seems to be using VB.  That leads me to believe that this issue is unique to delphi.
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

carv99Author Commented:
Just to clarify, the com object was written in Delphi
cyberkiwiCommented:
Okay I just got time to test this and try as I might, and search as I might, there seems to be no solution.
The only thing that worked for me was to change the signature from LPSTR to BSTR.
If you do not have access to the COM object, then create a wrapper COM object, that has a signature of

HRESULT _stdcall CalculateTax2([in] byte CalcType, [in] LPSTR CalcCode, [out, retval] CURRENCY * TaxAmount );

which becomes

function TCalculateTaxWrapper.CalculateTax2(CalcType: Shortint; CalcCode: PChar;
  out TaxAmount: Currency): HResult;

Hope that helps.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
carv99Author Commented:
I ended up using Variants opposed to bstr i have not tried that.

Thanks for investigating though.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.