Property Name
Last updated on
Apr 27, 2021
Database access functions let you query a database.
In accessing database information, you can, for example, retrieve metadata that describes the schema or structure of a database. This metadata includes information such as the names of tables, columns, stored procedures, and views. You can also show the results of executing a database query or stored procedure. When accessing a database through this API, you use structured query language (SQL) statements.
For the collection of functions that manage a database connection, see Database connection functions.
The following list describes some of the arguments that are common to the functions that are available:
Most database access functions use a connection name as an argument. You can see a list of valid connection names in the Connection Manager, or you can use the MMDB.getConnectionList() function to get a list of all the connection names programmatically.
Stored procedures often require parameters. You can specify parameter values for database access functions in two ways. First, you can provide an array of parameter values (paramValuesArray). If you specify only parameter values, the values must be in the sequence in which the stored procedure requires the parameters. Second, you specify parameter values to provide an array of parameter names (paramNameArray). You can use the MMDB.getSPParamsAsString() function to get the parameters of the stored procedure. If you provide parameter names, the values that you specify in paramValuesArray must be in the sequence of the parameter names that you specify in paramNameArray.
MMDB.getColumnAndTypeList()
Availability
Dreamweaver UltraDev 1.
Description
This function gets a list of columns and their types from an executed SQL SELECT statement.
Arguments
connName, statement
The connName argument is a connection name that is specified in the Connection Manager. It identifies the connection string that Dreamweaver should use to make a database connection to a live data source.
The statement argument is the SQL SELECT statement to execute.
Returns
An array of strings that represents a list of columns (and their types) that match the SELECT statement, or an error if the SQL statement is invalid or the connection cannot be made.
Example
The code var columnArray = MMDB.getColumnAndTypeList("EmpDB","Select * from Employees") returns the following array of strings:
columnArray[0] = "EmpName" columnArray[1] = "varchar" columnArray[2] = "EmpFirstName" columnArray[3] = "varchar" columnArray[4] = "Age" columnArray[5] = "integer"
MMDB.getColumnList()
Availability
Dreamweaver UltraDev 1.
Description
This function gets a list of columns from an executed SQL SELECT statement.
Arguments
connName, statement
The connName argument is a connection name that is specified in the Connection Manager. It identifies the connection string that Dreamweaver should use to make a database connection to a live data source.
The statement argument is the SQL SELECT statement to execute.
Returns
An array of strings that represents a list of columns that match the SELECT statement, or an error if the SQL statement is invalid or the connection cannot be made.
Example
The code var columnArray = MMDB.getColumnList("EmpDB","Select * from Employees") returns the following array of strings:
columnArray[0] = "EmpName" columnArray[1] = "EmpFirstName" columnArray[2] = "Age"
MMDB.getColumns()
Availability
Dreamweaver MX, arguments updated in Dreamweaver MX 2004.
Description
This function returns an array of objects that describe the columns in the specified table.
Arguments
connName, tableName
The connName argument is the connection name. This value identifies the connection containing the string that Dreamweaver should use to make a database connection to a live data source.
The tableName argument is the table to query.
Returns
An array of objects, one object for each column. Each object defines the following three properties for the column with which it is associated.
|
|
Description |
|
name |
Name of the column (for example, price) |
|
datatype |
Data type of the column (for example, small money) |
|
definedsize |
Defined size of the column (for example, 8) |
|
nullable |
Indicates whether the column can contain null values |
Example
The following example uses MMDB.getColumns() to set the tooltip text value:
var columnNameObjs = MMDB.getColumns(connName,tableName);
var databaseType = MMDB.getDatabaseType(connName);
for (i = 0; i < columnNameObjs.length; i++)
{
var columnObj = columnNameObjs[i];
var columnName = columnObj.name;
var typename = columnObj.datatype;
if (dwscripts.isNumber(typename))
{
// it already is a num
typename = dwscripts.getDBColumnTypeAsString(typename, databaseType);
}
var tooltiptext = typename;
}
MMDB.getColumnsOfTable()
Availability
Dreamweaver UltraDev 1.
Description
This function gets a list of all the columns in the specified table.
Arguments
connName, tableName
The connName argument is a connection name that is specified in the Connection Manager. It identifies the connection string that Dreamweaver should use to make a database connection to a live data source.
The tableName argument is the name of a table in the database that is specified by the connName argument.
Returns
An array of strings where each string is the name of a column in the table.
Example
The statement MMDB.getColumnsOfTable ("EmpDB","Employees"); returns the following strings:
["EmpID", "FirstName", "LastName"]
MMDB.getPrimaryKeys()
Availability
Dreamweaver MX.
Description
This function returns the column names that combine to form the primary key of the named table. A primary key serves as the unique identifier for a database row and consists of at least one column.
Arguments
connName, tableName
The connName argument is a connection name that is specified in the Connection Manager. It identifies the connection string that Dreamweaver should use to make a database connection to a live data source.
The tableName argument is the name of the table for which you want to retrieve the set of columns that comprises the primary key of that table.
Returns
An array of strings. The array contains one string for each column that comprises the primary key.
Example
The following example returns the primary key for the specified table.
var connName = componentRec.parent.parent.parent.name; var tableName = componentRec.name; var primaryKeys = MMDB.getPrimaryKeys(connName,tableName);
MMDB.getProcedures()
Availability
Dreamweaver MX.
Description
This function returns an array of procedure objects that are associated with a named connection.
Arguments
connName
The connName argument is a connection name that is specified in the Connection Manager. It identifies the connection string that Dreamweaver should use to make a database connection to a live data source.
Returns
An array of procedure objects where each procedure object has the following set of three properties:
|
Property Name |
Description |
|
schema |
Name of the schema that is associated with the object. This property identifies the user that is associated with the stored procedure in the SQL database that the getProcedures() function accesses. The database that this function accesses depends on the type of connection.
|
|
catalog |
Name of the catalog that is associated with the object (owner qualifier). The value of the catalog property is defined by an attribute of the OLE DB driver. This driver attribute defines a default user.database property to use when the OLE DB connection string does not specify a database. |
|
procedure |
Name of the procedure. |
Note:
Dreamweaver connects to and gets all the tables in the database whenever you modify a recordset. If the database has many tables, Dreamweaver might take a long time to retrieve them on certain systems. If your database contains a schema or catalog, you can use the schema or catalog to restrict the number of database items Dreamweaver gets at design time. You must first create a schema or catalog in your database application before you can apply it in Dreamweaver. Consult your database documentation or your system administrator.
Example
The following code gets a list of procedures:
var procObjects = MMDB.getProcedures(connectionName);
for (i = 0; i < procObjects.length; i++)
{
var thisProcedure = procObjects[i]
thisSchema = Trim(thisProcedure.schema)
if (thisSchema.length == 0)
{
thisSchema = Trim(thisProcedure.catalog)
}
if (thisSchema.length > 0)
{
thisSchema += "."
}
var procName = String(thisSchema + thisProcedure.procedure);
}
MMDB.getSPColumnList()
Availability
Dreamweaver UltraDev 1.
Description
This function gets a list of result set columns that are generated by a call to the specified stored procedure.
Arguments
connName, statement, paramValuesArray
The connName argument is a connection name that is specified in the Connection Manager. It identifies the connection string that Dreamweaver should use to make a database connection to a live data source.
The statement argument is the name of the stored procedure that returns the result set when it executes.
The paramValuesArray argument is an array that contains a list of design-time parameter test values. Specify the parameter values in the order in which the stored procedure expects them. You can use the MMDB.getSPParamsAsString() function to get the parameters for the stored procedure.
Returns
An array of strings that represents the list of columns. This function returns an error if the SQL statement or the connection string is invalid.
Example
The following code can return a list of result set columns that are generated from the executed stored procedure, getNewEmployeesMakingAtLeast:
var paramValueArray = new Array("2/1/2000", "50000")
var columnArray = MMDB.getSPColumnList("EmpDB", ¬
"getNewEmployeesMakingAtLeast", paramValueArray)
The following values return:
columnArray[0] = "EmpID", columnArray[1] = "LastName", ¬
columnArray[2] ="startDate", columnArray[3] = "salary"
MMDB.getSPColumnListNamedParams()
Availability
Dreamweaver UltraDev 1.
Description
This function gets a list of result set columns that are generated by a call to the specified stored procedure.
Arguments
connName, statement, paramNameArray, paramValuesArray
The connName argument is a connection name that is specified in the Connection Manager. It identifies the connection string that Dreamweaver should use to make a database connection to a live data source.
The statement argument is the name of the stored procedure that returns the result set when it executes.
The paramNameArray argument is an array that contains a list of parameter names. You can use the MMDB.getSPParamsAsString() function to get the parameters of the stored procedure.
The paramValuesArray argument is an array that contains a list of design-time parameter test values. You can specify if the procedure requires parameters when it executes. If you have provided parameter names in paramNameArray, specify the parameter values in the same order that their corresponding parameter names appear in paramNameArray. If you did not provide paramNameArray, specify the values in the order in which the stored procedure expects them.
Returns
An array of strings that represents the list of columns. This function returns an error if the SQL statement or the connection string is invalid.
Example
The following code can return a list of result set columns that are generated from the executed stored procedure, getNewEmployeesMakingAtLeast:
var paramNameArray = new Array("startDate", "salary")
var paramValueArray = new Array("2/1/2000", "50000")
var columnArray = MMDB.getSPColumnListNamedParams("EmpDB", ¬
"getNewEmployeesMakingAtLeast", paramNameArray, paramValueArray)
The following values return:
columnArray[0] = "EmpID", columnArray[1] = "LastName",¬ columnArray[2] ="startDate", columnArray[3] = "salary"
MMDB.getSPParameters()
Availability
Dreamweaver MX.
Description
This function returns an array of parameter objects for a named procedure.
Arguments
connName, procName
The connName argument is a connection name that is specified in the Connection Manager. It identifies the connection string that Dreamweaver should use to make a database connection to a live data source.
The procName argument is the name of the procedure.
Returns
An array of parameter objects, each specifying the following set of properties:
|
Property name |
Description |
|
name |
Name of the parameter (for example, @@lolimit) |
|
datatype |
Datatype of the parameter (for example, smallmoney) |
|
direction |
Direction of the parameter: 1–The parameter is used for input only. 2–The parameter is used for output only. In this case, you pass the parameter by reference and the method places a value in it. You can use the value after the method returns. 3– The parameter is used for both input and output. 4– The parameter holds a return value. |
Example
The following example retrieves the parameter objects for the specified procedure and creates a tooltip for each object using its properties.
var paramNameObjs = MMDB.getSPParameters(connName,procName);
for (i = 0; i < paramNameObjs.length; i++)
{
var paramObj = paramNameObjs[i];
var tooltiptext = paramObj.datatype;
tooltiptext+=" ";
tooltiptext+=GetDirString(paramObj.directiontype);
}
MMDB.getSPParamsAsString()
Availability
Dreamweaver UltraDev 1.
Description
This function gets a comma-delimited string that contains the list of parameters that the stored procedure takes.
Arguments
connName, procName
The connName argument is a connection name that is specified in the Connection Manager. It identifies the connection string that Dreamweaver should use to make a database connection to a live data source.
The procName argument is the name of the stored procedure.
Returns
A comma-delimited string that contains the list of parameters that the stored procedure requires. The parameters’ names, direction, and data type are included, separated by semicolons (;).
Example
The code MMDB.getSPParamsAsString ("EmpDB","getNewEmployeesMakingAtLeast") can return a string of form name startDate;direction:in;datatype:date, salary;direction:in;datatype:integer.
In this example, the stored procedure, getNewEmployeesMakingAtLeast, has two parameters: startDate and Salary. For startDate, the direction is in and the data type is date. For salary, the direction is in and the data type is date.
MMDB.getTables()
Availability
Dreamweaver UltraDev 1.
Description
This function gets a list of all the tables that are defined for the specified database. Each table object has three properties: table, schema, and catalog.
Arguments
connName
The connName argument is a connection name that is specified in the Connection Manager. It identifies the connection string that Dreamweaver should use to make a database connection to a live data source.
Returns
An array of objects where each object has three properties: table, schema, and catalog. Table is the name of the table. Schema is the name of the schema that contains the table. Catalog is the catalog that contains the table.
Example
The statement MMDB.getTables ("EmpDB"); might produce an array of two objects. The first object’s properties might be similar to the following example:
object1[table:"Employees", schema:"personnel", catalog:"syscat"]
The second object’s properties might be similar to the following example:
object2[table:"Departments", schema:"demo", catalog:"syscat2"]
MMDB.getViews()
Availability
Dreamweaver UltraDev 4.
Description
This function gets a list of all the views that are defined for the specified database. Each view object has catalog, schema, and view properties.
Arguments
connName
The connName argument is a connection name that is specified in the Connection Manager. It identifies the connection string that Dreamweaver should use to make a database connection to a live data source.
Returns
An array of view objects where each object has three properties: catalog, schema, and view. Use catalog or schema to restrict or filter the number of views that pertain to an individual schema name or catalog name that is defined as part of the connection information.
Example
The following example returns the views for a given connection value, CONN_LIST.getValue():
var viewObjects = MMDB.getViews(CONN_LIST.getValue())
for (i = 0; i < viewObjects.length; i++)
{
thisView = viewObjects[i]
thisSchema = Trim(thisView.schema)
if (thisSchema.length == 0)
{
thisSchema = Trim(thisView.catalog)
}
if (thisSchema.length > 0)
{
thisSchema += "."
}
views.push(String(thisSchema + thisView.view))
}
MMDB.showResultset()
Availability
Dreamweaver UltraDev 1.
Description
This function displays a dialog box that contains the results of executing the specified SQL statement.The dialog box displays a tabular grid in which the header provides column information that describes the result set. If the connection string or the SQL statement is invalid, an error appears. This function validates the SQL statement.
Arguments
connName, SQLstatement
The connName argument is a connection name that is specified in the Connection Manager. It identifies the connection string that Dreamweaver should use to make a database connection to a live data source.
The SQLstatement argument is the SQL SELECT statement.
Returns
Nothing. This function returns an error if the SQL statement or the connection string is invalid.
Example
The following code displays the results of the executed SQL statement:
MMDB.showResultset("EmpDB","Select EmpName,EmpFirstName,Age ¬
from Employees")
MMDB.showSPResultset()
Availability
Dreamweaver UltraDev 1.
Description
This function displays a dialog box that contains the results of executing the specified stored procedure. The dialog box displays a tabular grid in which the header provides column information that describes the result set. If the connection string or the stored procedure is invalid, an error appears. This function validates the stored procedure.
Arguments
connName, procName, paramValuesArray
The connName argument is a connection name that is specified in the Connection Manager. It identifies the connection string that Dreamweaver should use to make a database connection to a live data source.
The procName argument is the name of the stored procedure to execute.
The paramValuesArrayargument is an array that contains a list of design-time parameter test values. Specify the parameter values in the order in which the stored procedure expects them. You can use the MMDB.getSPParamsAsString() function to get the parameters of the stored procedure.
Returns
This function returns an error if the SQL statement or the connection string is invalid; otherwise, it returns nothing.
Example
The following code displays the results of the executed stored procedure:
var paramValueArray = new Array("2/1/2000", "50000")
MMDB.showSPResultset("EmpDB", "getNewEmployeesMakingAtLeast", ¬
paramValueArray)
MMDB.showSPResultsetNamedParams()
Availability
Dreamweaver UltraDev 1.
Description
This function displays a dialog box that contains the result set of the specified stored procedure. The dialog box displays a tabular grid in which the header provides column information that describes the result set. If the connection string or the stored procedure is invalid, an error appears. This function validates the stored procedure. This function differs from the MMDB.showSPResultset() function because you can specify the parameter values by name instead of the order in which the stored procedure expects them.
Arguments
connName, procName, paramNameArray, paramValuesArray
The connName argument is a connection name that is specified in the Connection Manager. It identifies the connection string that Dreamweaver should use to make a database connection to a live data source.
The procName argument is the name of the stored procedure that returns the result set when it executes.
The paramNameArray argument is an array that contains a list of parameter names. You can use the MMDB.getSPParamsAsString() function to get the parameters of the stored procedure.
The paramValuesArray argument is an array that contains a list of design-time parameter test values.
Returns
This function returns an error if the SQL statement or the connection string is invalid; otherwise, it returns nothing.
Example
The following code displays the results of the executed stored procedure:
var paramNameArray = new Array("startDate", "salary")
var paramValueArray = new Array("2/1/2000", "50000")
MMDB.showSPResultsetNamedParams("EmpDB","getNewEmployees¬
MakingAtLeast", paramNameArray, paramValueArray)
