• Creator
  • #4009

    Is there any way to call a stored procedure from formativ. (connect to a sequal server database)

  • Author
  • #6839
    Support 1a


    We generally do all our database access in Formativ using Microsoft’s ADO. While I haven’t personally called a stored procedure, I can’t imaging why it couldn’t be done. If you are not familiar with ADO do a search on Microsoft’s web site for the appropriate reference material.

    Here’s some example applet code that creates a ADO session and executes some SQL. This example accesses an Access database, but you only need to change the connection string in order to connect to any SQL database supported by ADO:

    sub UpdateAcceptedSupportIssue(iMsg, iEvent, iEngineer)
      dim iADOObj
      dim iRST 
      ' create a new instance of an ADO Connection object 
      set iADOObj = CreateObject("ADODB.Connection")
      ' Did we get a handle to the ADO connection  
      if not isobject(iADOObj) then
        call msgbox("Failed to create ADODB.Connection object.", vbCritical, "Support Example")
        ' open the test data source with the Connection object  
        iConnnectString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & SUPPORT_DATABASE  
        iADOObj.Open iConnnectString
        ' Are we connected to the database?
        if err.number = 0 then
          ' Connected to the database - what do we want to do?  We currently support
          ' accepting a new support incident, and completing (closing) an incident.
          select case iEvent
            ' Accept - Add a new record the SQL database
            case "GW#C#ACCEPT"
              iSQLString = "INSERT INTO Support(Incident,RequestedBy,Subject,ResponsibleEngineer,RequestedDate) VALUES ('"& iMsg.MessageID &"','"& iMsg.FromText& "','"& iMsg.Subject&"','" & iEngineer & "','"&SQLDate(Date)&"')"
            ' Complete (close) - Update the existing record in the SQL database        
            case "GW#C#COMPLETE"
              iSQLString = "UPDATE Support SET CompletedDate='" & SQLDate(Date + 5) & "' WHERE Incident='" & iMsg.MessageID &"'"
          end select 
          ' close and remove the Recordset object from memory  
          set iRST = Nothing  
          msgbox err.description, vbCritical, IDS_CAPTION
        end if
        ' Close and remove the Connection object from memory
      end if   
      set iADOObj = Nothing 
    end sub

    I hope this helps.

    Advansys Support

Viewing 1 replies (of 1 total)
  • You must be logged in to reply to this topic.