Solved

sp_OAMethod fails when calling ADODB functions in runtime from a VB6 DLL

Posted on 2004-04-07
6
941 Views
Last Modified: 2007-12-19
The following T-SQL code works when the VB6 ActiveX DLL is running in Debug mode within the VB6 IDE, but not when running from the compiled DLL. I have tried various things most recently changing to late-binding (clutching at straws) so please dont add a comment like 'shouldnt you be using early-binding because its much better'. Please also dont add comments like 'isnt that a bit overcomplicatyed for what you want to do?'. I Know its overcomplicated - But if I can get this working it will allow me to then go on to do what I really want to do, which is retrieve an >8000 character XML document from a text column, manipulate it then put it back into the text column.

I have two other related questions posted on this; Answer this one and I'll close all three in your favour giving you an instant  1500 points. HELP!

T-SQL Code:

begin
      DECLARE @errorSource VARCHAR(8000)
      DECLARE @errorDescription VARCHAR(8000)
      declare @oSolveweb INT, @hr INT, @oResult VARCHAR(1000), @INstanceId VARCHAR(36), @FieldName VARCHAR(30)
      EXEC @hr = sp_OACreate 'SolvewebTest.Test', @oSolveweb OUTPUT, 5
      PRINT @hr
      IF @hr <> 0 BEGIN
            EXEC sp_displayoaerrorinfo @oSolveweb, @hr
      END
      PRINT @oSolveweb
      EXEC @hr = sp_OAMethod @oSolveweb, 'Test', @oResult OUTPUT, '5C078696-C0D6-4F8D-9F97-0056EB62721C'
      PRINT @hr
      IF @hr <> 0 BEGIN
            EXEC sp_displayoaerrorinfo @oSolveweb, @hr
      END
      PRINT @oResult
      EXEC @hr = sp_OADestroy @oSolveweb
      PRINT @hr
end

VB6 Class:

Option Explicit

Private Sub OpenDB(ByRef oDBConn As Object)
    If oDBConn Is Nothing Then
        Set oDBConn = CreateObject("ADODB.Connection")
        oDBConn.Open "Provider=sqloledb;Data Source=localhost;Initial Catalog=mydb;User Id=sa;Password=password;"
        'oDBConn.Open "Provider=sqloledb;Data Source=localhost;Initial Catalog=mydb;Integrated Security=SSPI;"
    End If
End Sub
Private Sub CloseDB(ByRef oDBConn As Object)
    If Not oDBConn Is Nothing Then
        oDBConn.Close
        Set oDBConn = Nothing
    End If
End Sub

Private Sub GetContext(ByVal objectId As String, ByRef context As String)

    Dim oDBConn As Object
    Dim oDBCmd As Object
    Dim oCmdStream As Object
    Dim oOutStream As Object
    Dim strQuery As String
    Dim strXml As String
   
    Set oDBCmd = CreateObject("ADODB.Command")
   
    OpenDB oDBConn
    oDBCmd.ActiveConnection = oDBConn
   
    strQuery = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'>"
    strQuery = strQuery & "<sql:header nullvalue='NULL'>"
    strQuery = strQuery & "     <sql:param name='ObjectId'>NULL</sql:param>"
    strQuery = strQuery & "</sql:header>"
    strQuery = strQuery & "<sql:query>EXEC object_internal_identify_forCom @ObjectId</sql:query>"
    strQuery = strQuery & "</ROOT>"
   
    Set oCmdStream = CreateObject("ADODB.Stream")
    oCmdStream.Open
    oCmdStream.WriteText strQuery, adWriteChar
    oCmdStream.Position = 0
   
    oDBCmd.NamedParameters = True
    'oDBCmd.CommandType = adCmdText
    Set oDBCmd.CommandStream = oCmdStream
    oDBCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
    oDBCmd.Parameters.Append oDBCmd.CreateParameter("@ObjectId", adVarChar, adParamInput, 36, objectId)
    Set oOutStream = CreateObject("ADODB.Stream")
    oOutStream.Open
    oDBCmd.Properties("Output Stream") = oOutStream
    oDBCmd.Execute , , adExecuteStream
    'oOutStream.Position = 0
    strXml = oOutStream.ReadText '(-1)
    oOutStream.Close
    Set oOutStream = Nothing
    oCmdStream.Close
    Set oCmdStream = Nothing
    Set oDBCmd = Nothing
    CloseDB oDBConn
   
    context = strXml
   
End Sub

Public Function Test(strInput As String) As String
   
    Dim strReturn As String
    GetContext strInput, strReturn
    Test = strReturn
   
End Function
0
Comment
Question by:Solveweb
6 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 10774339
What messages do you get when you try and use the sp_OA procs--return codes?
0
 

Author Comment

by:Solveweb
ID: 10774536
sp_OACreate: Success (0)
sp_OAMethod: Rather unhelpful 'One or more errors occured while processing command'

Thanks Brett
0
 

Author Comment

by:Solveweb
ID: 10775878
I have solved this myself.

If anyone is interested - As soon as you start trying to execute a SQLXML dialect ADODB command the DLL can no longer be called by sp_OACreate, even though the call is legal and by the book, and works fine when called externally. This must be a bug in SQLXML.

By changing the ADODB call to a straight oDBCmd.CommandType = adCmdStoredProc I was able to get the DLL working from within an sp_OACreate call.

Can I award the points to myself?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10776171
>>Can I award the points to myself?<<
Sure.  It is called a refund :)  See here:
I answered my question myself. What do I do?
http://www.experts-exchange.com/help.jsp#hi70
0
 
LVL 1

Accepted Solution

by:
GhostMod earned 0 total points
ID: 11579467
PAQd and 500 points refunded

GhostMod
CS Moderator
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question