Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

721 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