Stored Procedures
A stored procedure is a set of SQL statements that are stored and executed on the database server. Stored procedures can improve the performance of complex database actions because less information needs to be sent between the server and the client.
Note: Stored procedures and functions are advanced tools that require familiarity with SQL commands. Not all databases support stored procedures and some, such as MySQL, provide only limited support.
Purpose: |
Obtain the names of all stored procedures in a database and store the results in a variable. |
Category: |
Stored Procedures |
Syntax: |
dbpGetProcedureNames "database id" "delimiter" "variable" database id The name assigned to the database. delimiter The character or characters used to separate the procedure names. variable The name of the variable where the procedure names will be stored. Each procedure name will be separated by the specified delimiter. |
Example: |
dbpGetProcedureNames "DB1" "[#13]" "[ProcList]" |
SQL Equivalent: |
SHOW PROCEDURES |
Purpose: |
Obtain a list of parameters required by a specific stored procedure and store the results in a variable. Note: Many types of databases, including Access and MySQL, do not support this feature and will return an error. |
Category: |
Stored Procedures |
Syntax: |
dbpGetProcedureParameters "database id" "procedure" "delimiter" "variable" database id The name assigned to the database. procedure This is the name of the procedure containing the parameters to retrieve. delimiter The character or characters used to separate the parameter names. variable The name of the variable where the parameter names will be stored. Each parameter name will be separated by the specified delimiter. |
Example: |
dbpGetProcedureParameters "DB1" "SamplProc" "[#13]" "[ParamList]" |
SQL Equivalent: |
N/A |
Purpose: |
Assign a value to an input parameter prior to executing a stored procedure. Input parameters are used to pass information to a stored procedure. The number of input parameters and their names depends on the stored procedure. Note: Some databases, such as MySQL, do not correctly initialize stored procedure parameters. When that happens, you must use the dbpAddParameter action to define each of the procedures parameters (both input and output) manually before executing the procedure. |
Category: |
Stored Procedures |
Syntax: |
dbpSetParameter "database id" "param name" "input value" database id The name assigned to the database containing the stored procedure. param name The name of the parameter. input value The value to assign to the parameter. |
Example: |
dbpSetParameter "DB1" "@User" "Bob" dbpSetParameter "DB1" "@Password" "applesauce" dbpExecproc "DB1" "SecurityCheck" |
SQL Equivalent: |
N/A |
Purpose: |
Use this action to manually define parameters to be used with a stored procedure. This action is intended to be used with databases, such as MySQL, that do not correctly initialize stored procedure parameters. Each parameter required by the procedure must be defined with a separate call to dbpAddParameter prior to executing the procedure. For most databases that properly initialize stored parameters, you should use the dbpSetParameter action instead. |
||||||||||
Category: |
Stored Procedures |
||||||||||
Syntax: |
dbpAddParameter "database id" "param name" "data type" "dir" "input value" database id The name assigned to the database. param name The name of the parameter. data type The type of value the parameter represents. Select one of the following: String, Char, Memo, Boolean, Integer, BigInt, AutoInc, Currency, Float, Date, Time, DateTime, Picture dir The type of parameter. Select one of the following:
size Specifies the size of a String or Char type parameters. For all other types, this will be ignored. input value For Input or InputOutput types, this is the value to assign to the parameter. For all other types this will be ignored. |
||||||||||
Example: |
dbpAddParameter "DB1" "@param1" "Integer" "Output" "" "" |
||||||||||
SQL Equivalent: |
N/A |
Purpose: |
Clear all parameters defined with dbpSetParameter or dbpAddParameter. You should use this to clear parameters from memory after running a stored procedure. |
||
Category: |
Stored Procedures |
||
Syntax: |
dbpClearParameters "database id" "options" database id The name assigned to the database. options This is a compound parameter and can contain any combination of the following items:
|
||
Example: |
dbpClearParameters "DB1" "ClearVariables=Yes" |
||
SQL Equivalent: |
N/A |
Purpose: |
Execute a stored procedure or function. Parameters created with the dbpSetParameter anddbpAddParameter actions are passed to the procedure. After the procedure has executed, NeoDBpro will automatically create variables for each of the output parameters returned by the server. |
Category: |
Stored Procedures |
Syntax: |
dbpExecProc "database id" "procedure" "results table" database id The name assigned to the database. procedure This is the name of the procedure to be executed. results table (optional) If the procedure returns a data set, this parameter can be used to enter the name of the table where you want the results of the query to be displayed. This may be the name of an existing database table or it can be a temporary table. If the table specified does not exist in the database, it is assumed to be a temporary table. Leave this parameter blank if the procedure does not return any results. A temporary table exists only while your publication is running and is not physically part of the database. However, most temporary tables derive their contents from real data, so any edits you make may affect other tables. A temporary table created with dpbExecProc can be opened with the dbpOpenTable and displayed with dbpShowGrid actions. Actions that require a physical table, such as dbpAddField or dbpQuery, cannot be used with a temporary table. |
Example: |
dbpSetParameter "DB1" "@User" "Bob" dbpSetParameter "DB1" "@Password" "applesauce" dbpExecproc "DB1" "SecurityCheck" "" The following example executes a stored procedure and sends the results to a temporary table, which is then opened and displayed in a grid: dbpExecProc "DB1" "CalculateSales" "Temp1" dbpOpenTable "DB1" "Temp1" "" dbpShowGrid "DB1" "Temp1" "Rectangle1" |
SQL Equivalent: |
CALL procedure ( parameters ) |