RAD Studio
|
There are four types of parameters that can be associated with stored procedures:
Access to stored procedure parameters is provided by the Params property (in TStoredProc, TSQLStoredProc, TIBStoredProc) or the Parameters property (in TADOStoredProc). When you assign a value to the StoredProcName (or ProcedureName) property, the dataset automatically generates objects for each parameter of the stored procedure. For some datasets, if the stored procedure name is not specified until runtime, objects for each parameter must be programmatically created at that time. Not specifying the stored procedure and manually creating the TParam or TParameter objects allows a single dataset to be used with any number of available stored procedures.
You can specify stored procedure parameter values at design time using the parameter collection editor. To display the parameter collection editor, click on the ellipsis button for the Params or Parameters property in the Object Inspector.
If the dataset has a Params property (TParam objects), the following properties must be correctly specified:
With some datasets, if the name of the stored procedure is not specified until runtime, no TParam objects are automatically created for parameters and they must be created programmatically. This can be done using the TParam.Create method or the TParams.AddParam method:
var P1, P2: TParam; begin ... with StoredProc1 do begin StoredProcName := 'GET_EMP_PROJ'; Params.Clear; P1 := TParam.Create(Params, ptInput); P2 := TParam.Create(Params, ptOutput); try Params[0].Name := 'EMP_NO'; Params[1].Name := 'PROJ_ID'; ParamByname('EMP_NO').AsSmallInt := 52; ExecProc; Edit1.Text := ParamByname('PROJ_ID').AsString; finally P1.Free; P2.Free; end; end; ... end;
TParam *P1, *P2; StoredProc1->StoredProcName = "GET_EMP_PROJ"; StoredProc1->Params->Clear(); P1 = new TParam(StoredProc1->Params, ptInput); P2 = new TParam(StoredProc1->Params, ptOutput); try { StoredProc1->Params->Items[0]->Name = "EMP_NO"; StoredProc1->Params->Items[1]->Name = "PROJ_ID"; StoredProc1->ParamByName("EMP_NO")->AsSmallInt = 52; StoredProc1->ExecProc(); Edit1->Text = StoredProc1->ParamByName("PROJ_ID")->AsString; } __finally { delete P1; delete P2; }
Even if you do not need to add the individual parameter objects at runtime, you may want to access individual parameter objects to assign values to input parameters and to retrieve values from output parameters. You can use the dataset's ParamByName method to access individual parameters based on their names. For example, the following code sets the value of an input/output parameter, executes the stored procedure, and retrieves the returned value:
with SQLStoredProc1 do begin ParamByName('IN_OUTVAR').AsInteger := 103; ExecProc; IntegerVar := ParamByName('IN_OUTVAR').AsInteger; end;
SQLDataSet1->ParamByName("IN_OUTVAR")->AsInteger = 103;
SQLDataSet1->ExecSQL();
int Result = SQLDataSet1->ParamByName("IN_OUTVAR")->AsInteger;
Copyright(C) 2009 Embarcadero Technologies, Inc. All Rights Reserved.
|
What do you think about this topic? Send feedback!
|