Solved

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

Posted on 2004-04-07
6
942 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

740 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