DDDriverDatabaseCommands

Last Updated:

Driver Database Commands for the Cycle, Suite, and Step drivers

This keyword library provides Driver Database Commands that can be used by all three DDE Drivers--CycleDriver, SuiteDriver, and StepDriver. That means they can be used in any keyword driven test tables regardless of the test tables level--Cycle, Suite, or Step.

Each different driver command has different parameters as described in its documentation. For reference, the first fields of ALL Driver Command test records are defined below:

Most all commands here require a data source name, Since this driver uses jdbc, then a format like "jdbc:odbc:drivername" is usually supplied for that parameter. Another examples could be: jdbc:oracle:thin:@machine:1111:DBNAME
If the jdbc:odbc: is omitted, then the 'drivername' is assumed to be Sun's jdbc-odbc bridge (sun.jdbc.odbc.JdbcOdbcDriver) and that string (jdbc:odbc:) is automatically prepended.

Notice that for all of the commands here which accept the ADBAlias and ATableAlias parameters, that they are not used. They are only there because there for compatibility with the Component Function versions of these commands (CFDatabase).

In order to specify the driver, a 'setJdbcDriver' command can be used once at the beginning

Field #1
The "C" = DRIVER COMMAND record type specifier.
Field #2
The Driver Command keyword.

Examples:

C, setJdbcDriver, "sun.jdbc.odbc.JdbcOdbcDriver" C, setJdbcDriver, "oracle.jdbc.driver.OracleDriver"

Driver Command parameters must be placed in the test record in the field position specified in the documentation. Some parameters are optional and can be skipped. Optional parameters can only be skipped if all subsequent optional paramaters are also skipped.

Example:

c, deleteDBTableRecords, ADBAlias, ATableAlias, DBTableName, DBQueryCond, DBSourceName

The above example skips the UserId, Password, and SQLStatus (they are not even included)

DDDriverCommands


[How To Read This Reference]
CopyDBTableColumnToFile
RJ TID SDC
This routine will copy the contents of one or more DBTable column(s) to a file.
CopyDBTableToFile
RJ TID SDC
This routine will copy the contents of a DBTable to a file.
DeleteDBTableRecords
RJ TID SDC
Deletes records in a database table
ExecSQLQuery
RJ TID SDC
Executes the query provided in a queryStr on the database table.
GetDBTableColumnCount
RJ TID SDC
Executes the query in a specified database table and returns the total column count.
GetDBTableRowCount
RJ TID SDC
Executes the query in a database table.
GetDBValue
RJ TID SDC
Executes the query and returns the case-sensitive value as result of the execution.
SetJdbcDriver
RJ TID SDC
set Jdbc Driver
VerifyDatabaseNullValue
RJ TID SDC
Verifies the value of a field in a database table is NULL
VerifyDatabaseValue
RJ TID SDC
Verifies the case-sensitive value of a field in a database table
VerifyDBNullValue
RJ TID SDC
Verifies the value of a field in a database table is NULL
VerifyDBValue
RJ TID SDC
Verifies the case-sensitive value of a field in a database table

DDDriverDatabaseCommands::CopyDBTableColumnToFile
RJ TID SDC

This routine will copy the contents of one or more DBTable column(s) to a file.

This routine will export the contents of one or more DBTable column(s) to a delimeted file. If columnDelimiter is omitted, a horizontal tab is used to delimit fields.

A data source name and Query String is required. You also have to specify a variable to get the status code. And optionally user id and password (this is because some drivers don't need a user/password).



Fields: [ ]=Optional with Default Value
  1. ADBAlias
    not used


  2. ATableAlias
    not used


  3. DBSourceName
    Name of the Datasource containing the DBTable


  4. FileOut
    Name of the output file.


  5. SQLQuery
    The file will contain the results of the executed Query. The query is NOT validated by this function.


  6. [ DirectoryOut = ]
    OPTIONAL parameter. If not specified the file will be stored in the default test directory


  7. [ SQLStatus = ]
    A variable which holds the current SQL run status.


  8. [ Delimiter = ]
    Optional Delimiter to be used between columns written to file.


  9. [ UserID = ]
    UserID for accessing the Datasource (if required).


  10. [ Password = ]
    Password for accessing the Datasource (if required).


Examples:
[How To Read This Reference]

DDDriverDatabaseCommands::CopyDBTableToFile
RJ TID SDC

This routine will copy the contents of a DBTable to a file.

This routine will export the contents of a DBTable to a delimeted file.

A data source name and Query String is required. You also have to specify a variable to get the status code. And optionally user id and password (this is because some drivers don't need a user/password).



Fields: [ ]=Optional with Default Value
  1. ADBAlias
    not used


  2. ATableAlias
    not used


  3. DBSourceName
    Name of the Datasource containing the DBTable


  4. FileOut
    Name of the output file.


  5. DBTableName
    Name of the TABLE to access within the DBSourceName


  6. [ ColumnCount = ]
    If specified the output file will contain only the number of columns specified.

    OPTIONAL parameter. If specified the output file will contain only the number of columns specified. This number will become the first dimension of the retreival array. If NOT specified then no maximum.



  7. [ RowCount = ]
    If specified the output file will contain only the number of rows specified.

    OPTIONAL parameter. If specified the output file will contain only the number of columns specified. This number will become the first dimension of the retreival array. If NOT specified then no maximum.



  8. [ SQLQuery = ]
    OPTIONAL parameter. If specified, the file will contain the results of the executed Query. The query is NOT validated by this function. If this is provided, the the DBTableName must be ""


  9. [ delimiter = ]
    OPTIONAL parameter. If not specified the default ',' seperator will be used.


  10. [ DirectoryOut = ]
    OPTIONAL parameter. If not specified the file will be stored in the default test directory


  11. [ SQLStatus = ]
    A variable which holds the current SQL run status.


  12. [ UserID = ]
    UserID for accessing the Datasource (if required).


  13. [ Password = ]
    Password for accessing the Datasource (if required).


Examples:
[How To Read This Reference]

DDDriverDatabaseCommands::DeleteDBTableRecords
RJ TID SDC

Deletes records in a database table

Deletes records in a database table.

A table name is required. You also have to specify data source name, and optionally user id and password (this is because some drivers don't need a user/password).

A query condition can be included. It can be any valid expression allowed by the WHERE clause of the DELETE FROM sentence of the SQL driver you are using. For example: "CliBal > 100000 and CliCat = 'C'".
Including "where" in the expression itself is optional. It will be prefixed to the expression if it is not provided.



Fields: [ ]=Optional with Default Value
  1. ADBAlias
    not used


  2. ATableAlias
    not used


  3. DBTableName
    Name of the TABLE to access within the DBSourceName


  4. DBQueryCond
    Expression allowed by the WHERE clause of the DELETE FROM sentence of the SQL driver

    A query condition can be included, if not then "" is supplied. It can be any valid expression allowed by the WHERE clause of the DELETE FROM sentence of the SQL driver you are using. For example: "CliBal > 100000 and CliCat = 'C'".
    "where" will be added to any expression if it is not provided.

  5. DBSourceName
    Name of the Datasource containing the DBTable


  6. [ UserID = ]
    UserID for accessing the Datasource (if required).


  7. [ Password = ]
    Password for accessing the Datasource (if required).


  8. [ SQLStatus = ]
    SQL status code as a result of executing the query gets stored in this variable


Examples:
[How To Read This Reference]

DDDriverDatabaseCommands::ExecSQLQuery
RJ TID SDC

Executes the query provided in a queryStr on the database table.

Executes the query in a database table.

A data source name and Query String is required. You also have to specify a variable to get the status code. And optionally user id and password (this is because some drivers don't need a user/password).

Use this command with caution. It doesn't return any value back other than the SQL Status code. So, remember not to use 'SELECT' command inside the queryStr. It should rather be used to do an 'UPDATE', 'INSERT' or 'DELETE'.



Fields: [ ]=Optional with Default Value
  1. ADBAlias
    not used


  2. ATableAlias
    not used


  3. DBSourceName
    Name of the Datasource containing the DBTable


  4. queryStr
    Actual query stored in a queryStr.


  5. SQLStatusCode
    SQL status code as a result of executing the query.


  6. [ UserID = ]
    UserID for accessing the Datasource (if required).


  7. [ Password = ]
    Password for accessing the Datasource (if required).


Examples:
[How To Read This Reference]

DDDriverDatabaseCommands::GetDBTableColumnCount
RJ TID SDC

Executes the query in a specified database table and returns the total column count.

Executes the query in a specified database table and returns the total column count.

A data source name and Table name are required. You also have to specify a variable to get the result of the SQL and status code. And optionally user id and password (this is because some drivers don't need a user/password).



Fields: [ ]=Optional with Default Value
  1. ADBAlias
    not used


  2. ATableAlias
    not used


  3. DBSourceName
    Name of the Datasource containing the DBTable


  4. DBTableName
    Name of the TABLE to access within the DBSourceName


  5. ColumnCount
    SQL result get stored in this return variable.


  6. SQLStatusCode
    SQL status code as a result of executing the query.


  7. [ UserID = ]
    UserID for accessing the Datasource (if required).


  8. [ Password = ]
    Password for accessing the Datasource (if required).


Examples:
[How To Read This Reference]

DDDriverDatabaseCommands::GetDBTableRowCount
RJ TID SDC

Executes the query in a database table.

Executes the query in a database table.

A data source name and Query String is required. You also have to specify a variable to get the status code. And optionally user id and password (this is because some drivers don't need a user/password).



Fields: [ ]=Optional with Default Value
  1. ADBAlias
    not used


  2. ATableAlias
    not used


  3. DBSourceName
    Name of the Datasource containing the DBTable


  4. DBTableName
    Name of the TABLE to access within the DBSourceName


  5. RowCount
    SQL result get stored in this return variable.


  6. SQLStatusCode
    SQL status code as a result of executing the query.


  7. [ UserID = ]
    UserID for accessing the Datasource (if required).


  8. [ Password = ]
    Password for accessing the Datasource (if required).


Examples:
[How To Read This Reference]

DDDriverDatabaseCommands::GetDBValue
RJ TID SDC

Executes the query and returns the case-sensitive value as result of the execution.

Executes the query specified in the queryStr and returns the case-sensitive value as result of the execution.

To return the value, it executes a SQL query sentence using a data source to connect to the data base (using a 'SELECT').

A queryStr and the two return variables to store the query result and SQL Status code are required. You also have to specify data source name and optionally user id and password (this is because some drivers don't need a user/password).

If the query returns more than one record, the returned value is the corresponding to the first one, unless the field name be something like COUNT(*) or SUM(InvoiceTot).



Fields: [ ]=Optional with Default Value
  1. ADBAlias
    not used


  2. ATableAlias
    not used


  3. DBSourceName
    Name of the Datasource containing the DBTable


  4. queryStr
    Actual query stored in a queryStr.


  5. QueryResult
    SQL result get stored in this return variable.


  6. SQLStatusCode
    SQL status code as a result of executing the query.


  7. [ UserID = ]
    UserID for accessing the Datasource (if required).


  8. [ Password = ]
    Password for accessing the Datasource (if required).


Examples:
[How To Read This Reference]

DDDriverDatabaseCommands::SetJdbcDriver
RJ TID SDC

set Jdbc Driver

sets a JDBC driver, can be called more than once with multiple drivers.

Fields: [ ]=Optional with Default Value
  1. DriverClassName
    Name of the driver class


Examples:
[How To Read This Reference]

DDDriverDatabaseCommands::VerifyDatabaseNullValue
RJ TID SDC

Verifies the value of a field in a database table is NULL

Verifies the value of a field in a database table is NULL.

Sometimes you have to check if a field's value is NULL. This NULL value is different from any other (even zero for numerics and null string for characters), so if you try to check it using VerifyDBValue() function, if will fail.

A table and field name are required. You also have to specify data source name, user id and password (the user id and password can be "" meaning not required).

A query condition can be included. It can be any valid expression allowed by the WHERE clause of the SELECT sentence of the SQL driver you are using. For example: "CliBal > 100000 and CliCat = 'C'".
Including "where" in the expression itself is optional. It will be prefixed to the expression if it is not provided.



Fields: [ ]=Optional with Default Value
  1. ADBAlias
    not used


  2. ATableAlias
    not used


  3. DBFieldName
    Name of the FIELD within DBTableName used in the verification


  4. DBTableName
    Name of the TABLE to access within the DBSourceName


  5. DBQueryCond
    Expression allowed by the WHERE clause of the SELECT sentence of the SQL driver

    A query condition can be included, if not then "" must be supplied. It can be any valid expression allowed by the WHERE clause of the SELECT sentence of the SQL driver you are using. For example: "CliBal > 100000 and CliCat = 'C'".
    "where" will be added to any expression if it is not provided.

  6. DBSourceName
    Name of the Datasource containing the DBTable


  7. [ UserID = ]
    UserID for accessing the Datasource (if required).


  8. [ Password = ]
    Password for accessing the Datasource (if required).


  9. [ SQLStatus = ]
    SQL status code as a result of executing the query gets stored in this variable


Examples:
[How To Read This Reference]

DDDriverDatabaseCommands::VerifyDatabaseValue
RJ TID SDC

Verifies the case-sensitive value of a field in a database table

Verifies the case-sensitive value of a field in a database table.

To return the value, it executes a SQL query sentence using a data source to connect to the data base (using a 'SELECT').

A table and field name are required. You also have to specify data source name, user id, password, and the expected value.

If the query returns more than one record, the returned value is the corresponding to the first one, unless the field name be something like COUNT(*) or SUM(InvoiceTot).

A query condition can be included. It can be any valid expression allowed by the WHERE clause of the SELECT sentence of the SQL driver you are using. For example: "CliBal > 100000 and CliCat = 'C'".
Including "where" in the expression itself is optional. It will be prefixed to the expression if it is not provided.



Fields: [ ]=Optional with Default Value
  1. ADBAlias
    not used


  2. ATableAlias
    not used


  3. DBFieldName
    Name of the FIELD within DBTableName used in the verification


  4. DBTableName
    Name of the TABLE to access within the DBSourceName


  5. DBQueryCond
    Expression allowed by the WHERE clause of the SELECT sentence of the SQL driver

    A query condition can be included, if not then "" must be supplied. It can be any valid expression allowed by the WHERE clause of the SELECT sentence of the SQL driver you are using. For example: "CliBal > 100000 and CliCat = 'C'".
    "where" will be added to any expression if it is not provided.

  6. DBSourceName
    Name of the Datasource containing the DBTable


  7. UserID
    UserID for accessing the Datasource (if required, if not then "" must be supplied).


  8. Password
    Password for accessing the Datasource (if required, if not then "" must be supplied).


  9. ExpectedValue
    Benchmark value to compare against retrieved DBFieldName value


  10. [ SQLStatus = ]
    SQL status code as a result of executing the query gets stored in this variable


Examples:
[How To Read This Reference]

DDDriverDatabaseCommands::VerifyDBNullValue
RJ TID SDC

Verifies the value of a field in a database table is NULL

Verifies the value of a field in a database table is NULL.

Sometimes you have to check if a field's value is NULL. This NULL value is different from any other (even zero for numerics and null string for characters), so if you try to check it using VerifyDBValue() function, if will fail.

A table and field name are required. You also have to specify data source name, user id and password (the user id and password can be "" meaning not required).

A query condition can be included. It can be any valid expression allowed by the WHERE clause of the SELECT sentence of the SQL driver you are using. For example: "CliBal > 100000 and CliCat = 'C'".
Including "where" in the expression itself is optional. It will be prefixed to the expression if it is not provided.



Fields: [ ]=Optional with Default Value
  1. ADBAlias
    not used


  2. ATableAlias
    not used


  3. DBFieldName
    Name of the FIELD within DBTableName used in the verification


  4. DBTableName
    Name of the TABLE to access within the DBSourceName


  5. DBQueryCond
    Expression allowed by the WHERE clause of the SELECT sentence of the SQL driver

    A query condition can be included, if not then "" must be supplied. It can be any valid expression allowed by the WHERE clause of the SELECT sentence of the SQL driver you are using. For example: "CliBal > 100000 and CliCat = 'C'".
    "where" will be added to any expression if it is not provided.

  6. DBSourceName
    Name of the Datasource containing the DBTable


  7. [ UserID = ]
    UserID for accessing the Datasource (if required).


  8. [ Password = ]
    Password for accessing the Datasource (if required).


  9. [ SQLStatus = ]
    SQL status code as a result of executing the query gets stored in this variable


Examples:
[How To Read This Reference]

DDDriverDatabaseCommands::VerifyDBValue
RJ TID SDC

Verifies the case-sensitive value of a field in a database table

Verifies the case-sensitive value of a field in a database table.

To return the value, it executes a SQL query sentence using a data source to connect to the data base (using a 'SELECT').

A table and field name are required. You also have to specify data source name, user id, password, and the expected value.

If the query returns more than one record, the returned value is the corresponding to the first one, unless the field name be something like COUNT(*) or SUM(InvoiceTot).

A query condition can be included. It can be any valid expression allowed by the WHERE clause of the SELECT sentence of the SQL driver you are using. For example: "CliBal > 100000 and CliCat = 'C'".
Including "where" in the expression itself is optional. It will be prefixed to the expression if it is not provided.



Fields: [ ]=Optional with Default Value
  1. ADBAlias
    not used


  2. ATableAlias
    not used


  3. DBFieldName
    Name of the FIELD within DBTableName used in the verification


  4. DBTableName
    Name of the TABLE to access within the DBSourceName


  5. DBQueryCond
    Expression allowed by the WHERE clause of the SELECT sentence of the SQL driver

    A query condition can be included, if not then "" must be supplied. It can be any valid expression allowed by the WHERE clause of the SELECT sentence of the SQL driver you are using. For example: "CliBal > 100000 and CliCat = 'C'".
    "where" will be added to any expression if it is not provided.

  6. DBSourceName
    Name of the Datasource containing the DBTable


  7. UserID
    UserID for accessing the Datasource (if required, if not then "" must be supplied).


  8. Password
    Password for accessing the Datasource (if required, if not then "" must be supplied).


  9. ExpectedValue
    Benchmark value to compare against retrieved DBFieldName value


  10. [ SQLStatus = ]
    SQL status code as a result of executing the query gets stored in this variable


Examples:
[How To Read This Reference]