Reply To: SQl

[Support 1a]

Mike,

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")
  else
  
    ' 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)&"')"
          iADOObj.Execute(iSQLString)

        ' 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 &"'"
          iADOObj.Execute(iSQLString)
          
      end select 

      ' close and remove the Recordset object from memory  
      iRST.Close
      set iRST = Nothing  
      
    else
      msgbox err.description, vbCritical, IDS_CAPTION
    end if
    
    ' Close and remove the Connection object from memory
    iADOObj.Close
      
  end if   
  
  set iADOObj = Nothing 

end sub

I hope this helps.

Advansys Support