Archived Forum Post

Index of archived forum posts

Question:

SQL Server Methods that Return Long Strings

Dec 02 '15 at 11:39

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

---

Answer

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_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