DOCUMENT:Q178445  23-DEC-1997  [vbwin]
TITLE   :PRB: Stored Procedure with Text (BLOB) Input Returns Null Output
PRODUCT :Microsoft Visual Basic for Windows
PROD/VER:WINDOWS:5.0
OPER/SYS:WINDOWS
KEYWORDS:vb5all

======================================================================
---------------------------------------------------------------------
The information in this article applies to:

 - Microsoft Visual Basic Enterprise Edition for Windows, version 5.0
---------------------------------------------------------------------

SYMPTOMS
========

When a stored procedure has a input text parameter (known as BLOB, Binary
Large Object), all the output parameters get set to Null by the SQL Server
on the way back to the client.

CAUSE
=====

This is a known issue with SQL Server driver.

RESOLUTION
==========

Instead of using output parameters, prepare the returned information in a
resultset.

STATUS
======

Microsoft has confirmed this to be a bug in the Microsoft products
listed at the beginning of this article. We are researching this
bug and will post new information here in the Microsoft Knowledge
Base as it becomes available.

MORE INFORMATION
================

Besides steps to reproduce the behavior, the following sample code provides
a workaround by returning to the Visual Basic client the identity column of
the newly-created record as a resultset. Test table and stored procedures
are created in Pubs database.

Command1 inserts a new record to table, BLOBTest, by passing the contents
in Text1 as an input parameter to the stored procedure, InputBLOBTest, and
returns the Identity column as an output parameter. The return value is
shown in the debug window as Null. Command2 provides a workaround by
executing a modified version of the original stored procedure, which passes
back the identity column in a resultset.

1. Start a new project in Visual Basic and choose "Standard EXE." Form1 is
   created by default.

2. From the Project menu, select References, and then place a check next
   to Microsoft Remote Data Object 2.0.

3. Add 2 CommandButtons, Command1 and Command2, to Form1.

4. Add a TextBox, Text1, to Form1.

5. Paste the following code in the General Declarations section of Form1:

   Dim cn As New rdoConnection

   Private Sub Command1_Click()
      Dim qy As New rdoQuery
      With qy
         Set .ActiveConnection = cn
         .SQL = "{? = CALL InputBLOBTest(?, ?, ?) }"
      End With
      qy(0).Direction = rdParamReturnValue
      qy(1).Direction = rdParamInput
      qy(2).Direction = rdParamInput
      qy(3).Direction = rdParamOutput
      qy(1) = Now
      qy(2) = Text1.Text
      qy.Execute
      Debug.Print qy(3)
   End Sub

   Private Sub Command2_Click()
      Dim strSQL As String
      Dim rs As rdoResultset

      strSQL = "{CALL InputBLOBTest2('" & Now & "','" & Text1.Text & "')}"
      Set rs = cn.OpenResultset(strSQL)
      Debug.Print rs(0)
   End Sub

   Private Sub Form_Load()

      Dim strSQL As String

      cn.CursorDriver = rdUseOdbc
      cn.Connect = "Driver={SQL server};" _
        &  "Server=MyServer;Database=Pubs;Uid=sa;Pwd="
      cn.EstablishConnection rdDriverNoPrompt

      On Error Resume Next
      strSQL = "DROP TABLE dbo.BLOBTest"
      cn.Execute strSQL, rdExecDirect
      On Error GoTo 0

      strSQL = "CREATE TABLE dbo.BLOBTest "
      strSQL = strSQL & "(ID int IDENTITY NOT NULL PRIMARY KEY, "
      strSQL = strSQL & "WhenEntered Varchar(30), Comment TEXT)"
      cn.Execute strSQL, rdExecDirect

      On Error Resume Next
      strSQL = "DROP PROCEDURE dbo.InputBLOBTest"
      cn.Execute strSQL, rdExecDirect
      On Error GoTo 0

      strSQL = "CREATE PROCEDURE dbo.InputBLOBTest "
      strSQL = strSQL & "@WhenEntered Varchar(30), _
      @Comment TEXT, @id Int OUTPUT
      strSQL = strSQL & "AS Insert Into BLOBTest(WhenEntered, Comment) "
      strSQL = strSQL & "Values(@WhenEntered, @Comment) "
      strSQL = strSQL & "Select @id=@@Identity"
      cn.Execute strSQL, rdExecDirect

      On Error Resume Next
      strSQL = "DROP PROCEDURE dbo.InputBLOBTest2"
      cn.Execute strSQL, rdExecDirect
      On Error GoTo 0

      strSQL = "CREATE PROCEDURE dbo.InputBLOBTest2 "
      strSQL = strSQL & "@WhenEntered Varchar(30), @Comment TEXT "
      strSQL = strSQL & "AS Insert Into BLOBTest(WhenEntered, Comment) "
      strSQL = strSQL & "Values(@WhenEntered, @Comment) "
      strSQL = strSQL & "Select @@Identity"
      cn.Execute strSQL, rdExecDirect

      Command1.Caption = Command1.Caption & "(Repro)"
      Command2.Caption = Command2.Caption & "(Workaround)"

   End Sub

   Private Sub Form_Unload(Cancel As Integer)
      cn.Close
   End Sub

6. Note that you should change your Server, Uid, and Pwd parameters in the
   RDO connection string.

7. Start the program or press the F5 key. Look at immediate window for
   output.

(c) Microsoft Corporation 1997, All Rights Reserved.
Contributions by Adrian Chiang, Microsoft Corporation
======================================================================
Keywords          : vb5all
Version           : WINDOWS:5.0
Platform          : WINDOWS
Issue type        : kbprb

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS
PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND.  MICROSOFT DISCLAIMS
ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES
OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.  IN NO
EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR
ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL,
CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF
MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE
POSSIBILITY OF SUCH DAMAGES.  SOME STATES DO NOT ALLOW THE EXCLUSION
OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES
SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1997.