Database access functions

Database access functions let you query a database. For the collection of functions that manage a database connection, see "Database connection functions" on page 574.

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. There are two ways of specifying parameter values for database access functions. First, you can provide an array of parameter values (paramValuesArray). If you specify only parameter values, the values need to 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.

Property Name	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. For ODBC connections, the ODBC data source defines the database. The DSN is specified by the dsn property in the connection object (connName) that you pass to the getProcedures() function. For OLE DB connections, the connection string names the database.
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.

^[*] 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)