DatabaseFunctions

Last Updated:

Actions for working with Database tables.


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

DatabaseFunctions::CopyDBTableColumnToFile
RC RJ

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 [DSN] 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).

To do: if user id and/or password are not specified, don't include them in the connection string. This would allow the possibility to specify them in the data source definition.



Fields: [ ]=Optional with Default Value
  1. DBSourceName
    Name of the Datasource containing the DBTable


  2. FileOut
    Name of the output file.


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


  4. [ DirectoryOut = ]
    OPTIONAL parameter. If not specified the file will be stored under the project "Datapool\Test\" directory.


  5. SQLStatus
    A variable which holds the current SQL run status.


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


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


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


  9. [ Prompt = ]
    Optional parameter valid values 1 - 4 [default is 4 no prompt] for ODBC driver login window.


Examples:
  • T, ADBAlias, ATableAlias, CopyDBTableColumnToFile, Data Source Name (dsn), ^OutputFileName, ^SQLQuery, [^OutputDirectory], ^sqlStatus, "[Delimiter]", "[UID]", "[PWD]", "[prompt]"
    This routine will export the contents of a single DBTable column to a file.

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

[How To Read This Reference]

DatabaseFunctions::CopyDBTableToFile
RC RJ

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 [DSN] 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).

To do: if user id and/or password are not specified, don't include them in the connection string. This would allow the possibility to specify them in the data source definition. If no output directory is specified, the result file get saved under "Datapool\Test\".



Fields: [ ]=Optional with Default Value
  1. DBSourceName
    Name of the Datasource containing the DBTable


  2. FileOut
    Name of the output file.


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


  4. [ 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 the function will copy a maximum of 100 columns. (arbitrary limit for now)



  5. [ 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 the function will copy a maximum of 100 columns. (arbitrary limit for now)



  6. [ SQLQuery = ]
    OPTIONAL parameter. If specified, the file will contain the results of the executed Query. The query is NOT validated by this function.


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


  8. [ DirectoryOut = ]
    OPTIONAL parameter. If not specified the file will be stored under the project "Datapool\Test\" directory.


  9. SQLStatus
    A variable which holds the current SQL run status.


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


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


  12. [ Prompt = ]
    Optional parameter valid values 1 - 4 [default is 4 no prompt] for ODBC driver login window.


Examples:
  • T, ADBAlias, ATableAlias, CopyDBTableToFile, Data Source Name (dsn), ^OutputFileName, tableName, [^ColumnCount],[^rowCount], [^SQLQuery], [^Delimiter], [^OutputDirectory], ^sqlStatus, "[UID]", "[PWD]", "[prompt]"
    This routine will export the contents of a DBTable to a delimeted file.

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

[How To Read This Reference]

DatabaseFunctions::DeleteDBTableRecords
RC RJ

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).

To do: if user id and/or password are not specified, don't include them in the connection string. This would allow the possibility to specify them in the data source definition.

A query condition can be included, usually it is. 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. DBTableName
    Name of the TABLE to access within the DBSourceName


  2. [ DBQueryCond = ]
    Expression allowed by the WHERE clause of the DELETE FROM sentence of the SQL driver

    A query condition can be included, usually it is. 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.


  3. DBSourceName
    Name of the Datasource containing the DBTable


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


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


  6. [ Prompt = ]
    Optional parameter valid values 1 - 4 [default is 4 no prompt] for ODBC driver login window.


Examples:
  • T, ADBAlias, ATableAlias, DeleteDBTableRecords, Clients, "CID = 25", ContactDatabase, "", "",""
    Delete the records in the CLIENTS table in CONTACTDATABASE where the CID field = 25.

    Delete the records in the CLIENTS table in CONTACTDATABASE where the CID field = 25.
    No UserID or Password was required for this access.


[How To Read This Reference]

DatabaseFunctions::ExecSQLCommit
RJ

This routine will commit the pending queries on the database.

When ExecSQLQuery is used with the autoCommit option turned off, queries will be not be auto-committed as is the default with JDBC. In this way, you can use multiple ExecSQLQuery statements, but then use ExecSQLCommit to commit all of the queries at once. This can be useful if your program is using a special process that requires committing all queries at one time.

A data source name [DSN] 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. DBSourceName
    Name of the Datasource containing the DBTable


  2. SQLStatus
    A variable which holds the current SQL run status.


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


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


Examples:
  • T, ADBAlias, ATableAlias, ExecSQLCommit, Data Source Name (dsn), ^sqlStatus, "[UID]", "[PWD]"
    This routine will commit the pending database queries on the specified database.



[How To Read This Reference]

DatabaseFunctions::ExecSQLQuery
RC RJ

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

Executes the query in a database table.

A data source name [DSN] 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 use Select command inside the queryStr. It cab be use to update Table. or reset some flag.

To do: if user id and/or password are not specified, don't include them in the connection string. This would allow the possibility to specify them in the data source definition.



Fields: [ ]=Optional with Default Value
  1. DBSourceName
    Name of the Datasource containing the DBTable


  2. queryStr
    Actual query stored in a ^queryStr.


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


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


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


  6. [ Prompt = ]
    Optional parameter valid values 1 - 4 [default is 4 no prompt] for ODBC driver login window.


Examples:
  • T, ADBAlias, ATableAlias, ExecSQLQuery, empODBC, "UPDATE Employee Set City = 'Pittsburgh' Where EmpID = '0023' ", ^sqlStatus, "", "", ""
    Executes the query provided in a ^queryStr against the database specified in the DSN.

    Executes the query provided in a queryStr i.e. updates the city column in the Employee table for a EmpID equals to 0023.

[How To Read This Reference]

DatabaseFunctions::GetDBTableColumnCount
RC RJ

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 [DSN] 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).

To do: if user id and/or password are not specified, don't include them in the connection string. This would allow the possibility to specify them in the data source definition.



Fields: [ ]=Optional with Default Value
  1. DBSourceName
    Name of the Datasource containing the DBTable


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


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


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


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


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


  7. [ Prompt = ]
    Optional parameter valid values 1 - 4 [default is 4 no prompt] for ODBC driver login window.


Examples:
  • T, ADBAlias, ATableAlias, GetDBTableColumnCount, empODBC, Employee, ^columnCount, ^sqlStatus, "", "", ""
    Executes the query for the Table name (Employee) on the database specified in the DSN.

    Executes the query for given Table name (Employee) on the database specified in the DSN. And this command returns total column count on the given table and SQL staus code.

[How To Read This Reference]

DatabaseFunctions::GetDBTableRowCount
RC RJ

Executes the query in a database table.

Executes the query in a database table.

A data source name [DSN] 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).

To do: if user id and/or password are not specified, don't include them in the connection string. This would allow the possibility to specify them in the data source definition.



Fields: [ ]=Optional with Default Value
  1. DBSourceName
    Name of the Datasource containing the DBTable


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


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


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


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


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


  7. [ Prompt = ]
    Optional parameter valid values 1 - 4 [default is 4 no prompt] for ODBC driver login window.


Examples:
  • T, ADBAlias, ATableAlias, GetDBTableRowCount, empODBC, Employee, ^rowCount, ^sqlStatus, "", "", ""
    Executes the query for the Table name (Employee) on the database specified in the DSN.

    Executes the query for given Table name (Employee) on the database specified in the DSN. And this command returns total row count on the given table and SQL staus code.

[How To Read This Reference]

DatabaseFunctions::GetDBValue
RC RJ

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 an ODBC data source to connect to the data base.

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).

To do: if user id and/or password are not specified, don't include them in the connection string. This would allow the possibility to specify them in the data source definition.

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. DBSourceName
    Name of the Datasource containing the DBTable


  2. queryStr
    Actual query stored in a ^queryStr.


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


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


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


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


  7. [ Prompt = ]
    Optional parameter valid values 1 - 4 [default is 4 no prompt] for ODBC driver login window.


Examples:
  • T, ADBAlias, ATableAlias, GetDBValue, empODBC, "Select LastName From Employee Where EmpID = '0023'", ^queryResult, ^sqlStatus, "", "", ""
    Executes the query in the ^queryStr on the database specified in the DSN.

    Executes the query in the ^queryStr on the database specified in the DSN. And this command returns single value as result of the query execution and SQL staus code.

[How To Read This Reference]

DatabaseFunctions::VerifyDatabaseNullValue
RC RJ

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, and optionally user id and password (this is because some drivers don't need a user/password).

To do: if user id and/or password are not specified, don't include them in the connection string. This would allow the possibility to specify them in the data source definition.

A query condition can be included, usually it is. 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. DBFieldName
    Name of the FIELD within DBTableName used in the verification


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


  3. [ DBQueryCond = ]
    Expression allowed by the WHERE clause of the SELECT sentence of the SQL driver

    A query condition can be included, usually it is. 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.


  4. DBSourceName
    Name of the Datasource containing the DBTable


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


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


  7. [ Prompt = ]
    Optional parameter valid values 1 - 4 [default is 4 no prompt] for ODBC driver login window.


Examples:
  • T, ADBAlias, ATableAlias, VerifyDatabaseNullValue, LASTNAME, Clients, "CID = 25", ContactDatabase, "", "",""
    Verify the LASTNAME field in the CLIENTS table in CONTACTDATABASE is NULL where the CID field = 25.

    Verify the LASTNAME field in the CLIENTS table in CONTACTDATABASE is NULL for the first record whose CID field = 25. No UserID or Password was required for this access.

[How To Read This Reference]

DatabaseFunctions::VerifyDatabaseValue
RC RJ

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 an ODBC data source to connect to the data base.

A table and field name 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).

To do: if user id and/or password are not specified, don't include them in the connection string. This would allow the possibility to specify them in the data source definition.

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, usually it is. 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. DBFieldName
    Name of the FIELD within DBTableName used in the verification


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


  3. [ DBQueryCond = ]
    Expression allowed by the WHERE clause of the SELECT sentence of the SQL driver

    A query condition can be included, usually it is. 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.


  4. DBSourceName
    Name of the Datasource containing the DBTable


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


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


  7. ExpectedValue
    Benchmark value to compare against retrieved DBFieldName value


  8. [ Prompt = ]
    Optional parameter valid values 1 - 4 [default is 4 no prompt] for ODBC driver login window.


Examples:
  • T, ADBAlias, ATableAlias, VerifyDBValue, LASTNAME, Clients, "CID = 25", ContactDatabase, "", "", "Smith",""
    Verify the LASTNAME field in the CLIENTS table in CONTACTDATABASE contains "Smith" where the CID field = 25.

    Verify the LASTNAME field in the CLIENTS table in CONTACTDATABASE contains "Smith" for the first record whose CID field = 25. No UserID or Password was required for this access.

[How To Read This Reference]

DatabaseFunctions::VerifyDBNullValue
RC RJ

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, and optionally user id and password (this is because some drivers don't need a user/password).

To do: if user id and/or password are not specified, don't include them in the connection string. This would allow the possibility to specify them in the data source definition.

A query condition can be included, usually it is. 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. DBFieldName
    Name of the FIELD within DBTableName used in the verification


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


  3. [ DBQueryCond = ]
    Expression allowed by the WHERE clause of the SELECT sentence of the SQL driver

    A query condition can be included, usually it is. 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.


  4. DBSourceName
    Name of the Datasource containing the DBTable


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


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


  7. [ Prompt = ]
    Optional parameter valid values 1 - 4 [default is 4 no prompt] for ODBC driver login window.


Examples:
  • T, ADBAlias, ATableAlias, VerifyDBNullValue, LASTNAME, Clients, "CID = 25", ContactDatabase, "", "",""
    Verify the LASTNAME field in the CLIENTS table in CONTACTDATABASE is NULL where the CID field = 25.

    Verify the LASTNAME field in the CLIENTS table in CONTACTDATABASE is NULL for the first record whose CID field = 25. No UserID or Password was required for this access.

[How To Read This Reference]

DatabaseFunctions::VerifyDBValue
RC RJ

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 an ODBC data source to connect to the data base.

A table and field name 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).

To do: if user id and/or password are not specified, don't include them in the connection string. This would allow the possibility to specify them in the data source definition.

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, usually it is. 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. DBFieldName
    Name of the FIELD within DBTableName used in the verification


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


  3. [ DBQueryCond = ]
    Expression allowed by the WHERE clause of the SELECT sentence of the SQL driver

    A query condition can be included, usually it is. 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.


  4. DBSourceName
    Name of the Datasource containing the DBTable


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


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


  7. [ Prompt = ]
    Optional parameter valid values 1 - 4 [default is 4 no prompt] for ODBC driver login window.


  8. ExpectedValue
    Benchmark value to compare against retrieved DBFieldName value


Examples:
  • T, ADBAlias, ATableAlias, VerifyDBValue, LASTNAME, Clients, "CID = 25", ContactDatabase, "", "", "Smith",""
    Verify the LASTNAME field in the CLIENTS table in CONTACTDATABASE contains "Smith" where the CID field = 25.

    Verify the LASTNAME field in the CLIENTS table in CONTACTDATABASE contains "Smith" for the first record whose CID field = 25. No UserID or Password was required for this access.

[How To Read This Reference]