NeoDBpro

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 )