login about faq

Some ActiveX methods return strings that are too long for local string variables. How can these be returned to the calling stored procedure?

asked Dec 02 '15 at 11:17

chilkat's gravatar image

chilkat ♦♦
11.8k316358420


The Chilkat Global object has a property named KeepStringResult which can be set to 1. (The default value is 0.) When set to 1, then each method that returns a string will also save the return value in the object's LastStringResult property. String properties can be accessed via temp tables, and this is the means for accessing strings too long to fit in local string variables. (For some reason, the SQL Server ActiveX/COM layer imposes some sort of limitation on the lengths of strings it can return to a local variable. I suspect it is 4K, but I can't remember or maybe I never knew in the first place...)

The first step is to set Global.KeepStringResult = 1. The Global object can be discarded immediately after the property is set.

       DECLARE @hr int
       DECLARE @global int
       EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Global', @global OUT
       IF @hr <> 0
       BEGIN
              PRINT 'Failed to create Global ActiveX component'
              RETURN
       END

   EXEC sp_OASetProperty @global, 'KeepStringResult', 1
   EXEC @hr = sp_OADestroy @global

Next, I'll demonstrate how to use LastStringResult. The LastMethodSuccess property can be used instead of looking at the string return value to determine if the method call succeeded. A typical method that would return a very long string is Mht.GetEML. Here's how to do it:

    DECLARE @mht int
    EXEC @hr = sp_OACreate 'Chilkat_9_5_0.Mht', @mht OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

DECLARE @success int

EXEC sp_OAMethod @mht, 'UnlockComponent', @success OUT, 'Anything for 30-day trial'
    IF @success <> 1
      BEGIN
        EXEC sp_OAGetProperty @mht, 'LastErrorText', @sTmp0 OUT
        PRINT @sTmp0
        EXEC @hr = sp_OADestroy @mht
        RETURN
      END

EXEC sp_OASetProperty @mht, 'UseCids', 1

DECLARE @emlStr nvarchar(4000)
    EXEC sp_OAMethod @mht, 'GetEML', @emlStr OUT, 'http://www.bonairefishing.com/'

-- Ignore the return value because we'll check LastMethodSuccess and then get the result
    -- from LastStringResult.
    EXEC sp_OAGetProperty @mht, 'LastMethodSuccess', @success OUT
    IF @success <> 1
      BEGIN
      -- The LastErrorText may be very large, so we should access it through a temp table..
       DECLARE @tmp2 TABLE (lastErrText ntext)
       INSERT INTO @tmp2 EXEC sp_OAGetProperty @mht, 'LastErrorText'
       SELECT * from @tmp2

EXEC @hr = sp_OADestroy @mht
        RETURN
      END

-- The GetEML method succeeded.  Now get the return string via a temp table (because it could be too
     -- large for a variable..
     DECLARE @tmp3 TABLE (emlStr ntext)
     INSERT INTO @tmp3 EXEC sp_OAGetProperty @mht, 'LastStringResult'
     SELECT * from @tmp3

link

answered Dec 02 '15 at 11:38

chilkat's gravatar image

chilkat ♦♦
11.8k316358420

edited Dec 02 '15 at 11:39

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or __italic__
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×9
×1
×1

Asked: Dec 02 '15 at 11:17

Seen: 1,509 times

Last updated: Dec 02 '15 at 11:39

powered by OSQA