Tuesday, 19 May 2015

Execute stored procedure from form

I was asked to figure out how to execute a stored procedure from a form. Here is a working sample (VB.net):

 Public Overrides Sub Button_Click()  
      ' prepare SQL execution  
      Dim MyCommand As Autodesk.Map.IM.Data.Provider.Command = New Autodesk.Map.IM.Data.Provider.Command("", Me.Document.Connection)  
      ' define input/output parameters for stored procedure  
      Dim ParameterOut as Autodesk.Map.IM.Data.Provider.DataParameter  
      Dim ParameterIn1 as Autodesk.Map.IM.Data.Provider.DataParameter  
      Dim Parameterin2 as Autodesk.Map.IM.Data.Provider.DataParameter  
      Dim returnValue as String  
      ' set up SQL command - type StoredProcedure  
      MyCommand.CommandType = System.Data.CommandType.StoredProcedure  
      ' PackageName.ProcedureName  
      MyCommand.CommandText = "emp_eval.eval_department"  
      ' assign values to input parameters  
      ParameterIn1 = MyCommand.CreateParameter()    
      ParameterIn1 .DbType = System.Data.DbType.Int32  
      ParameterIn1 .ParameterName = "DEPARTMENT_ID"  
      ParameterIn1 .Value = 100  
      ParameterIn1 .Direction = System.Data.ParameterDirection.Input  
      ParameterIn2= MyCommand.CreateParameter()    
      ParameterIn2.DbType = System.Data.DbType.Int32  
      ParameterIn2.ParameterName = "DUMMY_ID"  
      ParameterIn2.Value = 101  
      ParameterIn2.Direction = System.Data.ParameterDirection.Input  
      ' set up output parameter - return value   
      ParameterOut= MyCommand.CreateParameter()    
      ParameterOut.DbType = System.Data.DbType.String  
      ParameterOut.ParameterName = "TEXT"  
      'Direction needs to be Output and not ReturnValue   
      ParameterOut.Direction = System.Data.ParameterDirection.Output   
      ' add parameters to sql command, keep order of parameters in mind  
      MyCommand.Parameters.Add(ParameterIn1 )   
      MyCommand.Parameters.Add(ParameterIn2 )  
      MyCommand.Parameters.Add(ParameterOut )  
      ' execute SQL   
      ' get output/return value  
       returnValue = MyCommand.Parameters.Item("TEXT").Value  
       MsgBox("ok --> " & returnValue)  
      'refresh dialog   
  End Sub  

In Oracle the procedure is defined as:

PROCEDURE eval_department(department_id IN NUMBER , dummy_id IN NUMBER, text OUT VARCHAR2) ;

I found a presentation from the 2007 Oracle Spatial user Conference with some sample code but I needed to adapt it a bit:


Map 2013

No comments:

Post a Comment