?
Solved

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

Posted on 2004-04-07
6
Medium Priority
?
953 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
5 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

601 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