Description

Creates a new IAQAADOStoredProc object and returns a reference to it.

Declaration

ADO.CreateADOStoredProc()

Result An IAQAADOStoredProc object

Applies To

The method is applied to the following object:

ADO

Result Value

An object of the IAQAADOStoredProc type.

Remarks

The IAQAADOStoredProc object is an analogue of a Borland VCL TADOStoredProc object. Methods and properties are identical.

For detailed information on the underlying TADOStoredProc object, see VCL documentation on ADODB classes.

Once the IAQAADOStoredProc object is created, you can use it to launch procedures stored on the database server. To do this:

Establish a connection to the database (set the Connection or ConnectionString property).
Specify the name of the stored procedure (set the ProcedureName property).
If necessary, specify the procedure parameters (set the Parameters property).
The parameter's data types should be specified using the Data.DB.TFieldType constants. See Working with Stored Procedure Parameters in the VCL documentation.
Run the stored procedure using the Open or ExecProc methods.

Example

The script below demonstrates how to call a stored SQL procedure which has two input parameters and how to get a result parameter.

JavaScript, JScript

function CreatingStoredProcedure()
{
var SProc;
  SProc = ADO.CreateADOStoredProc();
  SProc.ConnectionString = "Provider=SQLNCLI;Server=SERVER_NAME;" +
                 "Database=DATABASE_NAME;Uid=USER_NAME; Pwd=PASSWORD;";
  SProc.ProcedureName = "dbo.SelectFromTable";
    // Adding a return parameter
  SProc.Parameters.AddParameter();
  SProc.Parameters.Items(0).name = "RETURN_VALUE";
  SProc.Parameters.Items(0).DataType = ftInteger;
  SProc.Parameters.Items(0).Direction = pdReturnValue;
  SProc.Parameters.Items(0).Value = null;
    // Adding an in parameter:
  SProc.Parameters.AddParameter();
  SProc.Parameters.Items(1).name = "TableName";
  SProc.Parameters.Items(1).DataType = ftFixedChar;
  SProc.Parameters.Items(1).Size = 32;
  SProc.Parameters.Items(1).Value = "chicken";
    // Adding an in parameter:
  SProc.Parameters.AddParameter();
  SProc.Parameters.Items(2).name = "Fields";
  SProc.Parameters.Items(2).DataType = ftFixedChar;
  SProc.Parameters.Items(2).Size = 32;
  SProc.Parameters.Items(2).Value = "Caption";
    // Adding an out parameter
  /*
  SProc.Parameters.AddParameter();
  SProc.Parameters.Items(2).name = "OutParamName";
  SProc.Parameters.Items(2).DataType = ftSmallint;
  SProc.Parameters.Items(2).Direction = pdOutput;
  SProc.Parameters.Items(2).Value = null;
  */
    // Running the procedure
  SProc.ExecProc();
    // Obtaining the return value
  Log.Message("Result: " + SProc.Parameters.Items(0).Value);
  // Log.Message("Result: " +
  // SProc.Parameters.ParamByName("RETURN_VALUE").Value);
}

Python

def CreatingStoredProcedure():
  SProc = ADO.CreateADOStoredProc()
  SProc.ConnectionString = "Provider=SQLNCLI;Server=SERVER_NAME;" + "Database=DATABASE_NAME;Uid=USER_NAME; Pwd=PASSWORD;"
  SProc.ProcedureName = "dbo.SelectFromTable"
  # Adding a return parameter 
  SProc.Parameters.AddParameter()
  SProc.Parameters.Items[0].name = "RETURN_VALUE"
  SProc.Parameters.Items[0].DataType = ftInteger 
  SProc.Parameters.Items[0].Direction = pdReturnValue
  SProc.Parameters.Items[0].Value = None
  # Adding an in parameter: 
  SProc.Parameters.AddParameter()
  SProc.Parameters.Items[1].name = "TableName"
  SProc.Parameters.Items[1].DataType = ftFixedChar
  SProc.Parameters.Items[1].Size = 32
  SProc.Parameters.Items[1].Value = "chicken"
  # Adding an in parameter: 
  SProc.Parameters.AddParameter() 
  SProc.Parameters.Items[2].name = "Fields"
  SProc.Parameters.Items[2].DataType = ftFixedChar
  SProc.Parameters.Items[2].Size = 32
  SProc.Parameters.Items[2].Value = "Caption"
  
  # Adding an out parameter  
  # SProc.Parameters.AddParameter()
  # SProc.Parameters.Items[2].name = "OutParamName"
  # SProc.Parameters.Items[2].DataType = ftSmallint
  # SProc.Parameters.Items[2].Direction = pdOutput  
  # SProc.Parameters.Items[2].Value = None
  
  # Running the procedure 
  SProc.ExecProc()
  # Obtaining the return value  
  Log.Message("Result: " + SProc.Parameters.Items[0].Value)
  # Log.Message("Result: " +  \
  # SProc.Parameters.ParamByName["RETURN_VALUE"].Value)

VBScript

Sub CreatingStoredProcedure
  Dim SProc
  Set SProc = ADO.CreateADOStoredProc
  SProc.ConnectionString = "Provider=SQLNCLI;Server=SERVER_NAME;"&_
                    "Database=DATABASE_NAME;Uid=USER_NAME; Pwd=PASSWORD;"
  SProc.ProcedureName = "dbo.SelectFromTable"
    ' Adding a return parameter
  Call SProc.Parameters.AddParameter()
  SProc.Parameters.Items(0).name = "RETURN_VALUE"
  SProc.Parameters.Items(0).DataType = ftInteger
  SProc.Parameters.Items(0).Direction = pdReturnValue
  SProc.Parameters.Items(0).Value = Null
    ' Adding an in parameter
  Call SProc.Parameters.AddParameter()
  SProc.Parameters.Items(1).name = "TableName"
  SProc.Parameters.Items(1).DataType = ftFixedChar
  SProc.Parameters.Items(1).Size = 32
  SProc.Parameters.Items(1).Value = "chicken"
    ' Adding an in parameter
  Call SProc.Parameters.AddParameter()
  SProc.Parameters.Items(2).name = "Fields"
  SProc.Parameters.Items(2).DataType = ftFixedChar
  SProc.Parameters.Items(2).Size = 32
  SProc.Parameters.Items(2).Value = "Caption"
    ' Adding an out parameter
  'Call SProc.Parameters.AddParameter()
  'SProc.Parameters.Items(2).name = "OutParamName"
  'SProc.Parameters.Items(2).DataType = ftSmallint
  'SProc.Parameters.Items(2).Direction = pdOutput
  'SProc.Parameters.Items(2).Value = null
    ' Running the procedure
  Call SProc.ExecProc()
    ' Obtaining the return value
  Call Log.Message("Result: " & SProc.Parameters.Items(0).Value)
  ' Call Log.Message("Result: " &_
  ' SProc.Parameters.ParamByName("RETURN_VALUE").Value)
End Sub

DelphiScript

function CreatingStoredProcedure;
var SProc;
begin
  SProc := ADO.CreateADOStoredProc();
  SProc.ConnectionString := 'Provider=SQLNCLI;Server=SERVER_NAME;' +
                 'Database=DATABASE_NAME;Uid=USER_NAME; Pwd=PASSWORD;';
  SProc.ProcedureName := 'dbo.SelectFromTable';
    // Adding a return parameter
  SProc.Parameters.AddParameter();
  SProc.Parameters.Items(0).name := 'RETURN_VALUE';
  SProc.Parameters.Items(0).DataType := ftInteger;
  SProc.Parameters.Items(0).Direction := pdReturnValue;
  SProc.Parameters.Items(0).Value := nil;
    // Adding an in parameter:
  SProc.Parameters.AddParameter();
  SProc.Parameters.Items(1).name := 'TableName';
  SProc.Parameters.Items(1).DataType := ftFixedChar;
  SProc.Parameters.Items(1).Size := 32;
  SProc.Parameters.Items(1).Value := 'chicken';
    // Adding an in parameter:
  SProc.Parameters.AddParameter();
  SProc.Parameters.Items(2).name := 'Fields';
  SProc.Parameters.Items(2).DataType := ftFixedChar;
  SProc.Parameters.Items(2).Size := 32;
  SProc.Parameters.Items(2).Value := 'Caption';
    // Adding an out parameter
  {
  SProc.Parameters.AddParameter();
  SProc.Parameters.Items(2).name := 'OutParamName';
  SProc.Parameters.Items(2).DataType := ftSmallint;
  SProc.Parameters.Items(2).Direction := pdOutput;
  SProc.Parameters.Items(2).Value := null;
  }
    // Running the procedure
  SProc.ExecProc();
    // Obtaining the return value
  Log.Message('Result: ' + SProc.Parameters.Items(0).Value);
  // Log.Message('Result: ' +
  // SProc.Parameters.ParamByName('RETURN_VALUE').Value);
end;

C++Script, C#Script

function CreatingStoredProcedure()
{
var SProc;
  SProc = ADO["CreateADOStoredProc"]();
  SProc["ConnectionString"] = "Provider=SQLNCLI;Server=SERVER_NAME;" +
                 "Database=DATABASE_NAME;Uid=USER_NAME; Pwd=PASSWORD;";
  SProc["ProcedureName"] = "dbo.SelectFromTable";
    // Adding a return parameter
  SProc["Parameters"]["AddParameter"]();
  SProc["Parameters"]["Items"](0)["name"] = "RETURN_VALUE";
  SProc["Parameters"]["Items"](0)["DataType"] = ftInteger;
  SProc["Parameters"]["Items"](0)["Direction"] = pdReturnValue;
  SProc["Parameters"]["Items"](0)["Value"] = null;
    // Adding an in parameter:
  SProc["Parameters"]["AddParameter"]();
  SProc["Parameters"]["Items"](1)["name"] = "TableName";
  SProc["Parameters"]["Items"](1)["DataType"] = ftFixedChar;
  SProc["Parameters"]["Items"](1)["Size"] = 32;
  SProc["Parameters"]["Items"](1)["Value"] = "chicken";
    // Adding an in parameter:
  SProc["Parameters"]["AddParameter"]();
  SProc["Parameters"]["Items"](2)["name"] = "Fields";
  SProc["Parameters"]["Items"](2)["DataType"] = ftFixedChar;
  SProc["Parameters"]["Items"](2)["Size"] = 32;
  SProc["Parameters"]["Items"](2)["Value"] = "Caption";
    // Adding an out parameter
  /*
  SProc["Parameters"]["AddParameter"]();
  SProc["Parameters"]["Items"](2)["name"] = "OutParamName";
  SProc["Parameters"]["Items"](2)["DataType"] = ftSmallint;
  SProc["Parameters"]["Items"](2)["Direction"] = pdOutput;
  SProc["Parameters"]["Items"](2)["Value"] = null;
  */
    // Running the procedure
  SProc["ExecProc"]();
    // Obtaining the return value
  Log["Message"]( "Result: " + SProc["Parameters"]["Items"](0)["Value"] );
  // Log["Message"]( "Result: " +
  // SProc["Parameters"]["ParamByName"]("RETURN_VALUE")["Value"] );
}

ADO.CreateADOStoredProc Method

Description

Declaration

Applies To

Result Value

Remarks

Example

See Also