[ Pobierz całość w formacie PDF ]
.Wor k i ng wi t h s t or ed pr oc edur es 23-13 Un d e r s t a n d i n g s t o r e d p r o c e d u r e p a r a me t e r sYou can edit the definition for any TParam you add, but the attributes of the TParamobjects you add must match the attributes of the parameters for the stored procedureon the server.To edit the TParam for a parameter, select it in the parameter collectioneditor and edit its property values in the Object Inspector.Note Sybase, MS-SQL, and Informix do not return parameter type information.Use theSQL Explorer to determine this information.Note Informix does not return data type information.Use the SQL Explorer to determinethis information.Note You can never set values for output and result parameters.These types of parametershave values set by the execution of the stored procedure.Creating parameters at runtimeIf the name of the stored procedure is not specified in StoredProcName until runtime,no TParam objects will be automatically created for parameters and they must becreated programmatically.This can be done by instantiating a new TParam object orthe TParams::AddParam method.For example, the InterBase stored procedure GET_EMP_PROJ, below, requires oneinput parameter (EMP_NO) and one output parameter (PROJ_ID).CREATE PROCEDURE GET_EMP_PROJ (EMP_NO SMALLINT)RETURNS (PROJ_ID CHAR(5))ASBEGINFOR SELECT PROJ_IDFROM EMPLOYEE_PROJECTWHERE EMP_NO = :EMP_NOINTO :PROJ_IDDOSUSPEND;ENDThe C++Builder code to associate this stored procedure with a TStoredProc namedStoredProc1 and create TParam objects for the two parameters is:{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[0]->Name = "EMP_NO";StoredProc1->Params[1]->Name = "PROJ_ID";StoredProc1->ParamByName("EMP_NO")->AsSmallInt = 52;StoredProc1->ExecProc();Edit1->Text = StoredProc1->ParamByName("PROJ_ID")->AsString;}23-14 Dev el oper s Gui de Vi e wi n g p a r a me t e r i n f o r ma t i o n a t d e s i g n t i me__finally{delete P1;delete P2;}}Binding parametersWhen you prepare and execute a stored procedure, its input parameters areautomatically bound to parameters on the server.Use the ParamBindMode property to specify how parameters in your storedprocedure component should be bound to the parameters on the server.By defaultParamBindMode is set to pbByName, meaning that parameters from the storedprocedure component are matched to those on the server by name.This is the easiestmethod of binding parameters.Some servers also support binding parameters by ordinal value, the order in whichthe parameters appear in the stored procedure.In this case the order in which youspecify parameters in the parameter collection editor is significant.The firstparameter you specify is matched to the first input parameter on the server, thesecond parameter is matched to the second input parameter on the server, and so on.If your server supports parameter binding by ordinal value, you can setParamBindMode to pbByNumber.Tip If you want to set ParamBindMode to pbByNumber, you need to specify the correctparameter types in the correct order.You can view a server s stored procedure sourcecode in the SQL Explorer to determine the correct order and type of parameters tospecify.Viewing parameter information at design timeIf you have access to a database server at design time, there are two ways to viewinformation about the parameters used by a stored procedure:" Invoke the SQL Explorer to view the source code for a stored procedure on aremote server.The source code includes parameter declarations that identify thedata types and names for each parameter." Use the Object Inspector to view the property settings for individual TParamobjects.You can use the SQL Explorer to examine stored procedures on your databaseservers if you are using BDE native drivers.If you are using ODBC drivers youcannot examine stored procedures with the SQL Explorer.While using the SQLExplorer is not always an option, it can sometimes provide more information thanthe Object Inspector viewing TParam objects.The amount of information returnedabout a stored procedure in the Object Inspector depends on your database server.Wor k i ng wi t h s t or ed pr oc edur es 23-15 Wo r k i n g wi t h Or a c l e o v e r l o a d e d s t o r e d p r o c e d u r e sTo view individual parameter definitions in the Object Inspector:1 Select the stored procedure component.2 Set the DatabaseName property of a stored procedure component to the BDE aliasfor your database server (or the DatabaseName property of a TDatabase).3 Set the StoredProcName property to the name of the stored procedure.4 click the ellipsis button in for the TStoredProc::Params property in the ObjectInspector.5 Select individual parameters in the collection editor to view their property settingsin the Object Inspector.For some servers some or all parameter information may not be accessible.In the Object Inspector, when viewing individual TParam objects, the ParamTypeproperty indicates whether the selected parameter is an input, output, input/output,or result parameter.The DataType property indicates the data type of the value theparameter contains, such as string, integer, or date.The Value edit box enables you toenter a value for a selected input parameter.Note Sybase, MS-SQL, and Informix do not return parameter type information.Use theSQL Explorer or vendor-supplied server utilities to determine this information.Note Informix does not return data type information.Use the SQL Explorervendor-supplied server utilities to determine this information.For more about setting parameter values, see Setting parameter information atdesign time on page 23-13.Note You can never set values for output and result parameters.These types of parametershave values set by the execution of the stored procedure.Working with Oracle overloaded stored proceduresOracle servers allow overloading of stored procedures; overloaded procedures aredifferent procedures with the same name.The stored procedure component sOverload property enables an application to specify the procedure to execute.If Overload is zero (the default), there is assumed to be no overloading.If Overload isone (1), then the stored procedure component executes the first stored procedure itfinds on the Oracle server that has the overloaded name; if it is two (2), it executes thesecond, and so on.Note Overloaded stored procedures may take different input and output parameters.Seeyour Oracle server documentation for more information.23-16 Dev el oper s Gui de Ch a p t e r24Chapter24Working with ADO componentsThe ADOExpress components are a set of classes that provide data access throughthe ADO framework, bypassing use of the Borland Database Engine (BDE).ADO, orMicrosoft ActiveX Data Objects, is a set of data objects that provide an application theability to access data through an OLE DB provider.The C++Builder ADOExpresscomponents encapsulate the functionality of these ADO objects and make theirfunctionality available in the C++Builder object-oriented paradigm [ Pobierz caÅ‚ość w formacie PDF ]
zanotowane.pl doc.pisz.pl pdf.pisz.pl odbijak.htw.pl
.Wor k i ng wi t h s t or ed pr oc edur es 23-13 Un d e r s t a n d i n g s t o r e d p r o c e d u r e p a r a me t e r sYou can edit the definition for any TParam you add, but the attributes of the TParamobjects you add must match the attributes of the parameters for the stored procedureon the server.To edit the TParam for a parameter, select it in the parameter collectioneditor and edit its property values in the Object Inspector.Note Sybase, MS-SQL, and Informix do not return parameter type information.Use theSQL Explorer to determine this information.Note Informix does not return data type information.Use the SQL Explorer to determinethis information.Note You can never set values for output and result parameters.These types of parametershave values set by the execution of the stored procedure.Creating parameters at runtimeIf the name of the stored procedure is not specified in StoredProcName until runtime,no TParam objects will be automatically created for parameters and they must becreated programmatically.This can be done by instantiating a new TParam object orthe TParams::AddParam method.For example, the InterBase stored procedure GET_EMP_PROJ, below, requires oneinput parameter (EMP_NO) and one output parameter (PROJ_ID).CREATE PROCEDURE GET_EMP_PROJ (EMP_NO SMALLINT)RETURNS (PROJ_ID CHAR(5))ASBEGINFOR SELECT PROJ_IDFROM EMPLOYEE_PROJECTWHERE EMP_NO = :EMP_NOINTO :PROJ_IDDOSUSPEND;ENDThe C++Builder code to associate this stored procedure with a TStoredProc namedStoredProc1 and create TParam objects for the two parameters is:{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[0]->Name = "EMP_NO";StoredProc1->Params[1]->Name = "PROJ_ID";StoredProc1->ParamByName("EMP_NO")->AsSmallInt = 52;StoredProc1->ExecProc();Edit1->Text = StoredProc1->ParamByName("PROJ_ID")->AsString;}23-14 Dev el oper s Gui de Vi e wi n g p a r a me t e r i n f o r ma t i o n a t d e s i g n t i me__finally{delete P1;delete P2;}}Binding parametersWhen you prepare and execute a stored procedure, its input parameters areautomatically bound to parameters on the server.Use the ParamBindMode property to specify how parameters in your storedprocedure component should be bound to the parameters on the server.By defaultParamBindMode is set to pbByName, meaning that parameters from the storedprocedure component are matched to those on the server by name.This is the easiestmethod of binding parameters.Some servers also support binding parameters by ordinal value, the order in whichthe parameters appear in the stored procedure.In this case the order in which youspecify parameters in the parameter collection editor is significant.The firstparameter you specify is matched to the first input parameter on the server, thesecond parameter is matched to the second input parameter on the server, and so on.If your server supports parameter binding by ordinal value, you can setParamBindMode to pbByNumber.Tip If you want to set ParamBindMode to pbByNumber, you need to specify the correctparameter types in the correct order.You can view a server s stored procedure sourcecode in the SQL Explorer to determine the correct order and type of parameters tospecify.Viewing parameter information at design timeIf you have access to a database server at design time, there are two ways to viewinformation about the parameters used by a stored procedure:" Invoke the SQL Explorer to view the source code for a stored procedure on aremote server.The source code includes parameter declarations that identify thedata types and names for each parameter." Use the Object Inspector to view the property settings for individual TParamobjects.You can use the SQL Explorer to examine stored procedures on your databaseservers if you are using BDE native drivers.If you are using ODBC drivers youcannot examine stored procedures with the SQL Explorer.While using the SQLExplorer is not always an option, it can sometimes provide more information thanthe Object Inspector viewing TParam objects.The amount of information returnedabout a stored procedure in the Object Inspector depends on your database server.Wor k i ng wi t h s t or ed pr oc edur es 23-15 Wo r k i n g wi t h Or a c l e o v e r l o a d e d s t o r e d p r o c e d u r e sTo view individual parameter definitions in the Object Inspector:1 Select the stored procedure component.2 Set the DatabaseName property of a stored procedure component to the BDE aliasfor your database server (or the DatabaseName property of a TDatabase).3 Set the StoredProcName property to the name of the stored procedure.4 click the ellipsis button in for the TStoredProc::Params property in the ObjectInspector.5 Select individual parameters in the collection editor to view their property settingsin the Object Inspector.For some servers some or all parameter information may not be accessible.In the Object Inspector, when viewing individual TParam objects, the ParamTypeproperty indicates whether the selected parameter is an input, output, input/output,or result parameter.The DataType property indicates the data type of the value theparameter contains, such as string, integer, or date.The Value edit box enables you toenter a value for a selected input parameter.Note Sybase, MS-SQL, and Informix do not return parameter type information.Use theSQL Explorer or vendor-supplied server utilities to determine this information.Note Informix does not return data type information.Use the SQL Explorervendor-supplied server utilities to determine this information.For more about setting parameter values, see Setting parameter information atdesign time on page 23-13.Note You can never set values for output and result parameters.These types of parametershave values set by the execution of the stored procedure.Working with Oracle overloaded stored proceduresOracle servers allow overloading of stored procedures; overloaded procedures aredifferent procedures with the same name.The stored procedure component sOverload property enables an application to specify the procedure to execute.If Overload is zero (the default), there is assumed to be no overloading.If Overload isone (1), then the stored procedure component executes the first stored procedure itfinds on the Oracle server that has the overloaded name; if it is two (2), it executes thesecond, and so on.Note Overloaded stored procedures may take different input and output parameters.Seeyour Oracle server documentation for more information.23-16 Dev el oper s Gui de Ch a p t e r24Chapter24Working with ADO componentsThe ADOExpress components are a set of classes that provide data access throughthe ADO framework, bypassing use of the Borland Database Engine (BDE).ADO, orMicrosoft ActiveX Data Objects, is a set of data objects that provide an application theability to access data through an OLE DB provider.The C++Builder ADOExpresscomponents encapsulate the functionality of these ADO objects and make theirfunctionality available in the C++Builder object-oriented paradigm [ Pobierz caÅ‚ość w formacie PDF ]