DatabaseFunctions::CopyDBTableColumnToFile
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
-
DBSourceName
Name of the Datasource containing the DBTable
-
FileOut
Name of the output file.
-
SQLQuery
The file will contain the results of the executed Query. The query is NOT validated
by this function.
- [ DirectoryOut = ]
OPTIONAL parameter. If not specified the file will be stored under the
project "Datapool\Test\" directory.
-
SQLStatus
A variable which holds the current SQL run status.
- [ Delimiter = ]
Optional Delimiter to be used between columns written to file.
- [ UserID = ]
UserID for accessing the Datasource (if required).
- [ Password = ]
Password for accessing the Datasource (if required).
- [ Prompt = ]
Optional parameter valid values 1 - 4 [default is 4 no prompt] for ODBC driver login window.
Examples:
[How To Read This Reference]
DatabaseFunctions::CopyDBTableToFile
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
-
DBSourceName
Name of the Datasource containing the DBTable
-
FileOut
Name of the output file.
-
DBTableName
Name of the TABLE to access within the DBSourceName
- [ 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)
- [ 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)
- [ SQLQuery = ]
OPTIONAL parameter. If specified, the file will contain the results of the
executed Query. The query is NOT validated by this function.
- [ delimiter = ]
OPTIONAL parameter. If not specified the default ',' seperator will be used.
- [ DirectoryOut = ]
OPTIONAL parameter. If not specified the file will be stored under the
project "Datapool\Test\" directory.
-
SQLStatus
A variable which holds the current SQL run status.
- [ UserID = ]
UserID for accessing the Datasource (if required).
- [ Password = ]
Password for accessing the Datasource (if required).
- [ Prompt = ]
Optional parameter valid values 1 - 4 [default is 4 no prompt] for ODBC driver login window.
Examples:
[How To Read This Reference]
DatabaseFunctions::DeleteDBTableRecords
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
-
DBTableName
Name of the TABLE to access within the DBSourceName
- [ 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.
-
DBSourceName
Name of the Datasource containing the DBTable
- [ UserID = ]
UserID for accessing the Datasource (if required).
- [ Password = ]
Password for accessing the Datasource (if required).
- [ 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
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
-
DBSourceName
Name of the Datasource containing the DBTable
-
SQLStatus
A variable which holds the current SQL run status.
- [ UserID = ]
UserID for accessing the Datasource (if required).
- [ Password = ]
Password for accessing the Datasource (if required).
Examples:
[How To Read This Reference]
DatabaseFunctions::ExecSQLQuery
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
-
DBSourceName
Name of the Datasource containing the DBTable
-
queryStr
Actual query stored in a ^queryStr.
-
SQLStatusCode
SQL status code as a result of executing the query.
- [ UserID = ]
UserID for accessing the Datasource (if required).
- [ Password = ]
Password for accessing the Datasource (if required).
- [ 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
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
-
DBSourceName
Name of the Datasource containing the DBTable
-
DBTableName
Name of the TABLE to access within the DBSourceName
-
ColumnCount
SQL result get stored in this return variable.
-
SQLStatusCode
SQL status code as a result of executing the query.
- [ UserID = ]
UserID for accessing the Datasource (if required).
- [ Password = ]
Password for accessing the Datasource (if required).
- [ 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
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
-
DBSourceName
Name of the Datasource containing the DBTable
-
DBTableName
Name of the TABLE to access within the DBSourceName
-
RowCount
SQL result get stored in this return variable.
-
SQLStatusCode
SQL status code as a result of executing the query.
- [ UserID = ]
UserID for accessing the Datasource (if required).
- [ Password = ]
Password for accessing the Datasource (if required).
- [ 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
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
-
DBSourceName
Name of the Datasource containing the DBTable
-
queryStr
Actual query stored in a ^queryStr.
-
QueryResult
SQL result get stored in this return variable.
-
SQLStatusCode
SQL status code as a result of executing the query.
- [ UserID = ]
UserID for accessing the Datasource (if required).
- [ Password = ]
Password for accessing the Datasource (if required).
- [ 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
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
-
DBFieldName
Name of the FIELD within DBTableName used in the verification
-
DBTableName
Name of the TABLE to access within the DBSourceName
- [ 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.
-
DBSourceName
Name of the Datasource containing the DBTable
- [ UserID = ]
UserID for accessing the Datasource (if required).
- [ Password = ]
Password for accessing the Datasource (if required).
- [ 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
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
-
DBFieldName
Name of the FIELD within DBTableName used in the verification
-
DBTableName
Name of the TABLE to access within the DBSourceName
- [ 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.
-
DBSourceName
Name of the Datasource containing the DBTable
- [ UserID = ]
UserID for accessing the Datasource (if required).
- [ Password = ]
Password for accessing the Datasource (if required).
-
ExpectedValue
Benchmark value to compare against retrieved DBFieldName value
- [ 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
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
-
DBFieldName
Name of the FIELD within DBTableName used in the verification
-
DBTableName
Name of the TABLE to access within the DBSourceName
- [ 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.
-
DBSourceName
Name of the Datasource containing the DBTable
- [ UserID = ]
UserID for accessing the Datasource (if required).
- [ Password = ]
Password for accessing the Datasource (if required).
- [ 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
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
-
DBFieldName
Name of the FIELD within DBTableName used in the verification
-
DBTableName
Name of the TABLE to access within the DBSourceName
- [ 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.
-
DBSourceName
Name of the Datasource containing the DBTable
- [ UserID = ]
UserID for accessing the Datasource (if required).
- [ Password = ]
Password for accessing the Datasource (if required).
- [ Prompt = ]
Optional parameter valid values 1 - 4 [default is 4 no prompt] for ODBC driver login window.
-
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]