This page is my own personal work. Anyone can use it for their own edification, but must realize that this material is not supported by me and the site is not affiliated with IBM Rational Software or Atlassian.
General.
Dynamic filters.
Case-insensitive sorting.
Create a custom style.
Place multiple elements in a single cell.
dataSetRow["CONTACTLASTNAME"]+", "+dataSetRow["CONTACTFIRSTNAME"]Table of Contents
Create a formatted report title.
<CENTER><B>
Customer List
</B></span><BR>
<FONT size="small">For internal use only</FONT><BR><BR>
Report generated on <VALUE-OF>new Date()</VALUE-OF>
</CENTER><BR><BR>
Insert dynamic text.
Change a report's query.
Create a report template.
Create a new database.
Set a test database.
Toggle the "Visible to designer only" switch.
Remove a database.
Set up SQL Anywhere.
Determine the schema used by a user database.
Upgrade a user database.
Logically delete a db if the physical db is gone.
# pdsql -v access -db \\machine\share\schema-repository-name -u admin-login -p admin-password These steps will actually remove the user database name from the MASTR. > select master_dbid from master_dbs where name='user-database-name'; master_dbid dbid WRITE DOWN THE DBID! > delete from master_dbs where name='user-database-name'; 1 rows affected. > delete from master_links where to_master_dbid = dbid and link_type in (3,4); n rows affected. > quit; This step will logically remove the user database name from the MASTR. >update master_dbs set is_deleted = 1 where name = 'user-database-name'; 1 rows affected. >quit;Log into the Designer and verify that the database has been deleted.
Rename a database set connection name.
# installutil renamedbset old-dbset-name new-dbset-name
Toggle a database between Test and Production modes.
Move a schema repository's physical database.
Name a dbset (master database).
Set the schema repository data code page value.
# installutil lscodepage -dbset dbset admin-user admin_password Ex: # installutil lscodepage -dbset 2003.06.00 cqadmin rational
# installutil setdbcodepagetoplatformcodepage -dbset dbset admin_user admin_password -or- # installutil setdbcodepagetoascii -dbset dbset admin_user admin_password
Interpret the values of ratl_priv_mask in a backend db table.
0 | SU |
1 | AU+DLA |
2 | AU+DLA+PFA |
11 | AU+DLA+PFA+SE |
27 | AU+DLA+PFA+SE+UA |
31 | AU+DLA+PFA+SE+UA+SA |
16 | AU+UA |
28 | AU+SA |
8 | AU+SE |
0 | AU+SD |
17 | AU+DLA |
19 | AU+DLA+PFA |
25 | AU+DLA+SE |
Rename a database.
Enable multiline text searches in Oracle: CLOB.
http://www-1.ibm.com/support/docview.wss?uid=swg21124348
Unlock a user database or schema repository.
> installutil unlockschemarepo -or- > installutil unlockuserdb dbvendor server db dbologin dbopassword connectoptions Ex: > installutil unlockuserdb MS_ACCESS mycomputer \\mycomputer\share\userdb.mdb admin "" ""
sql> select db_locked from dbglobal; sql> update dbglobal set db_locked = 0;
Upgrade a user database programmatically.
################################# # Push the lastest changes to the destination db. print "\nLogging into the destination dbset ($dest_dbset) ...\n"; $adminSession = CQAdminSession::Build; $adminSession->Logon($dest_login,$dest_passwd,$dest_dbset); # Push the changes. $databaseObj = $adminSession->GetDatabase($dest_db); $schemaRevObj = $databaseObj->GetSchemaRev; if ( int($schemaRevObj->GetRevID) < $end_ver ) { print "Upgrading ($dest_db) to version ($end_ver) ...\n"; $schemasObj = $adminSession->GetSchemas; $found_schema = 0; for ( $x = 0; $x < $schemasObj->Count; $x++ ) { $schemaObj = $schemasObj->Item($x); $schemaName = $schemaObj->GetName; if ( $schemaName eq $schema_name ) { $schemaRevsObj = $schemaObj->GetSchemaRevs; $nrevs = $schemaRevsObj->Count; if ( $end_ver > $nrevs ) { print "ERROR: The schema repository ($dest_dbset) only has ($nrevs) revisions for the ($schema_name) schema. Cannot upgrade ($dest_db) database to version ($end_ver).\n"; goto FINISH; } $schemaRevObj = $schemaRevsObj->Item($end_ver - 1); $found_schema = 1; last; } } if ( $found_schema ) { if ( ! $preview ) { $databaseObj->Upgrade($schemaRevObj); } else { print "Database ($dest_db) not upgraded in preview mode.\n"; } } else { print "ERROR: Unable to find a schema named ($schema_name) in the destination schema repository ($dest_dbset).\n"; goto FINISH; } } else { print "Destination db ($dest_db) version is up to date.\n"; } CQAdminSession::Unbuild($adminSession);
Configure (or determine) a schema repository LDAP authentication.
# This command ensures users don't attempt LDAP authentication during the upgrade. installutil setauthenticationalgorithm schema-repo admin passwd CQ_ONLY # This command sets the string that connects the CQ schema repo to the LDAP server. The -h option sets the # primary and secondary LDAP servers. The -p option sets the port number. If the LDAP servers don't allow # anonymous access, the -D and -w options are required. See the manual for those and other options. # The -w option is a password known to the LDAP administrators. # You'll have to work with the LDAP team to know/understand what "cn" and "dc" paramaters to use. # Anonymous LDAP login installutil setldapinit schema-repo admin passwd "-h ’primary-server secondary-server’ -p port" # Log into LDAP using a designated service account installutil setldapinit schema-repo admin passwd "-h ’primary-server secondary-server’ -p port -D cn=search_user,cn=Users,dc=svc-account,dc=com -w svc-account-password" # This command sets up the LDAP search criteria. The -s option tells it search the subtree. The domain name is # broken up in the -b option's "dc" parts. For example, ent.wfb.bank.corp would be "dc=efc,dc=wbd,dc=bank,dc=corp". # Note that "%login%" is literal. That will be filled in by CQ. installutil setldapsearch schema-repo admin passwd "-s sub -b ou=dept,domain-components (&(objectCategory=person)(samAccountName=%login%))" # The following command sets the LDAP mapping criteria. In this example, LDAP will authenticate against the user's login. # Other choices are specified in the manual. Note that whatever field is chosen for the map must have unique values # across all LDAP-authenticated users in CQ. If CQMS is employed, this field must be the same across all sites. installutil setcqldapmap schema-repo admin passwd CQ_LOGIN_NAME %login% # The following validates that the above commands set the schema up correctly. # This will return the current set of paramaters and should not return any error messages. installutil validateldap schema-repo admin passwd test-user test-user-passwd # The following sets CQ to look for the user in CQ. If the user is found and set to authenticate agains LDAP, # or if the user isn't found, it will authenticate against LDAP. Otherwise, the system will authenticate against CQ. installutil setauthenticationalgorithm schema-repo admin passwd CQ_FIRST
Add/remove users to/from LDAP authentication.
$adminSessionObj = CQAdminSession::Build; $adminSessionObj->Logon($admin-login,$admin-passwd,$dbset); $userObj = $adminSessionObj->GetUser($login); $userObj->SetLDAPAuthentication($login); CQAdminSession::Unbuild($adminSessionObj);
Basic SQL.
-- Display field values: SELECT field1, field2, field3 FROM recordtype; -- Filter on field values: SELECT field1, field2 FROM recordtype WHERE field1 = 'value' If the value contains a single quote, enter it as a double-single quote. For example, "Eric's" would get entered as 'Eric''s'. Numerical comparisons don't need quotes, as in "WHERE field1 > 100". -- Filter on mulitple fields: WHERE field1 = 'value' AND field2 < 1000 -- Filter on multiple values (or) within a field: WHERE field1 IN ('value1','value2','value3','value4') -- Field starts with "valu": WHERE field1 LIKE 'valu%' -- Field contains "valu": WHERE field1 LIKE '%valu%' -- Escape single quotes (if string = "Bob's", double up the internal quote): WHERE field1 = 'Bob''s' -- Filter on a range of values (field1 is an INTEGER): WHERE field1 BETWEEN 1000 AND 2000 -- Group nested filters (field2 is a SHORT_STRING): WHERE field1 BETWEEN 1000 AND 2000 AND (field2 BETWEEN '00004040' AND '00005050' OR field2 BETWEEN '00006000' AND '00006050') -- Field does not equal a value: WHERE field1 <> 'value' -- Find out if a reference field is empty or not: WHERE field1 is NULL WHERE field1 is not NULL -- Field does not contain a value: WHERE field1 NOT LIKE '%valu%' -- Sort in ascending (ASC) or descending (DESC) order: WHERE field1 <> 'value' ORDER BY field2 ASC -- Sort multiple fields in the order in which they are listed: ORDER BY field1 ASC, field2 DESC -- Query on dynamically determined values (select in select): SELECT field1 FROM recordtype WHERE field1 > (SELECT field2 FROM recordtype WHERE field3 = 'value') -- Mathematically analyze a set of returned values with AVG, COUNT, MAX, and SUM. These four will return a single value unless GROUP BY is used. SELECT SUM(field1) FROM recordtype -- Group a set of returned values. This will return the sum of "field2"s for each different value of field1. SELECT field1, SUM(field2) FROM recordtype GROUP BY field1 -- If a returned row is identical to another, only return one of them: SELECT DISTINCT field1 FROM recordtype -- Assign each table (recordtype) a variable when more than one table is involved: SELECT T1.field1, T2.field2 FROM recordtype1 T1, recordtype2 T2 WHERE T1.dbid = T2.parent_dbid -- Change a field value. Note that "UPDATE"s are not allowed in CQ pdsql. UPDATE recordtype SET field = 'newvalue' WHERE field = 'oldvalue' If the new value is '', use "SET field = NULL". -- Change a field substring. Note that UPDATEs are not allowed in CQ pdsql. UPDATE recordtype SET field = REPLACE(field, '/old/', '/new/') -- Get a listing of all tables: SELECT * from cat; In pdsql: tables; -- Get a listing of all columns in a table. DESC tablename; In pdsql: columns tablename; -- Change the state of a record. No hooks are run when doing this. SELECT id,name FROM entitydef WHERE name = 'recordtype'; # this gets the entitydef id SELECT id,name FROM statedef WHERE entitydef_id = entitydefid # this gets the state id UPDATE tablename SET state = newstateid WHERE id = 'recordid'; -- Delete a row. DELETE FROM tableName WHERE columnName=value WARNING: If the "where" clause is not correct (does not uniquely select a row), it may delete ALL rows. -- Determine the current DB2 database name SELECT CURRENT_SERVER from sysibm.sysdummy1; -- Get a db table name for a CQ record type. SELECT db_name FROM entitydef WHERE name = 'record-type-name'; -- Get a db column name for a CQ field. SELECT id FROM entitydef WHERE name = 'CQ-record-type'; SELECT db_name FROM fielddef WHERE entitydef_id = 'entitydef-id' AND name = 'CQ-fieldname'; -- Just get a count of rows returned. SELECT count(id) FROM tablename;Table of Contents
Determine the next dbid that will get used.
installutil getrecordlimits -dbset dbset username password { user_db | -all }
Increase the record ID (dbid) limit.
installutil getrecordlimits -dbset dbset username password { user_db | -all }Table of Contents
Rational ClearQuest Diagnostics utility.
CQ feature levels
> select name, feature_level, metaschema_version from master_dbs;
installutil showfeaturelevels
Use pdsql.
pdsql -u db-owner -p password -v ss -db userdb-name -s server MS Access: pdsql -u admin -p password -v access -db full-unc-path-to-schemarepo.mdbThe -u option is the owner of the SQL database, independent of any instance. The -p option is the owner's password. The -v option is type of database, such as ss = SQL Server. The -db option is the name of the user database to be accessed. The -s option specifies the hostname of the server on which the SQL database resides.
Update schema repository after copying new data.
Register a schema repository.
installutil clientregisterschemarepo parameters
Make a copy of a database.
Generate a summary of database details.
$repo = "Development"; $adminSession = CQAdminSession::Build(); $adminSession->Logon($login,$passwd,$repo); @DB_VENDOR = ('Unknown', 'SQL Server', 'MS Access', 'SQL Anywhere','Oracle', 'DB2'); $databases_entity = $adminSession->GetDatabases(); for ( $i = 0; $i < $databases_entity->Count(); $i++ ) { $database = $databases_entity->Item($i); $db_name = $database->GetName(); $db_vendor = $DB_VENDOR[$database->GetVendor()]; $db_host = $database->GetServer(); $db_sid = $database->GetDatabaseName(); $db_login = $database->GetDBOLogin(); $db_password = $database->GetDBOPassword(); $connect_options = $database->GetConnectOptions(); $schema_rev_entity = $database->GetSchemaRev(); if ( "$db_name" eq "MASTR") { $schema_name = 'N/A'; $schema_version = 'N/A'; } else { $schema_name = $schema_rev_entity->GetSchema()->GetName(); $schema_version = $schema_rev_entity->GetRevID(); } print " Schema repo:\t$repo Database:\t$db_name Schema:\t$schema_name Schema ver:\t$schema_version Vendor:\t$db_vendor Hostname:\t$db_host SID:\t$db_sid DB login:\t$db_login DB password:\t$db_password Connect options:\t$connect_options\n"; CQAdminSession::Unbuild($adminSession); }Table of Contents
Get a programmatic list of user databases.
$databases_co = $adminSession_o->GetDatabases; $n_databases = $databases_co->Count; for ( $x = 0; $x < $n_databases; $x++ ) { $db_o = $databases_co->Item($x); $db = $db_o->GetName; ...Table of Contents
Determine the database set if you know the user database.
List the database set(s) known to a Unix server.
cqreg showTable of Contents
Add a schema repository (dbset) connection to a Unix server.
cqreg add_dbset -vendor vendor -server server-name -database dbset -u db-user -p db-password "connect-options" Example: Database = schema repository (dbset) name cqreg add_dbset -vendor ORACLE -server oracle01 -database MUOS -u Oracleuser -p Oraclepass "SERVER_VER=9.2;SID=MUOS;HOST=oracle01;LOB_TYPE=CLOB"Table of Contents
Remove a schema repository (dbset) connection from a Unix server.
cqreg drop_dbset -dbset dbset-name -forceTable of Contents
Programmatically determine a local dbset name if you know the underlying SID.
View which server a database is associated with.
select * from master_dbs where database_name='db-name';Table of Contents
Add free-form text to an email rule.
E-mail notification.
Generate emails from within the schema instead of with Email Rules.
' ''''''''''''''''''''''''''''' ' Send email to the new Tester if that field changed, but only in the Testing state. if GetFieldValue("State").GetValue = "Testing" and GetFieldValue("Tester").GetValue <> GetFieldOriginalValue("Tester").GetValue then set mailObj = CreateObject("PAINET.MAILMSG") address = GetFieldValue("Tester_Email").GetValue mailObj.AddTo(address) subject = "You have been assigned as the Tester for Defect " & GetFieldValue("id").GetValue mailObj.SetSubject(subject) body = "Headline: " & GetFieldValue("Headline").GetValue & vbCrLf mailObj.SetBody(body) status = mailObj.Deliver ' Retry if this first deliver failed for some reason. if status = 0 then status = mailObj.Deliver end if end if
Ensure SMTP server is responding.
# ping hostname # telnet hostname 25Debug email notification issues.
Native Client REGEDIT4 [HKEY_CURRENT_USER\Software\Rational Software\ClearQuest\Diagnostic] "Trace"="Email" "Output"="ODS" "EMailSendVB"="ODS" Webserver REGEDIT4 [HKEY_USERS\.default\Software\Rational Software\ClearQuest\Diagnostic] "Trace"="Email" "Output"="ODS" "EMailSendVB"="ODS"Stop the ClearQuest service, import the registry files, and restart ClearQuest.
Lock down Email Rules.
' ''''''''''''''''''''''''''''''''' ' This function will check to see if the current logged in user is a part of a group ' allowed to Modify the Email Rule. Ensure users in the "Admin" group can get in. usergroups = GetSession.GetUserGroups name_text = GetFieldValue("Name").GetValue email_rule_AccessControl = FALSE if InStr(name_text,"(") and IsArray(usergroups) then string1 = split(name_text,"(") string2 = split(string1(1),")") allowed = split(string2(0),",") for each group in userGroups if group = "Admin" then email_rule_AccessControl = TRUE exit for end if for each allowed_group in allowed if group = allowed_group then email_rule_AccessControl = TRUE exit for end if end if next end if
Create an Email_Rule record.
Modify an Email_Rule record.
Set up the Rational Email Reader.
Include attachments with email.
$boundary = "SOME_VERY_UNIQUE_STRING"; $smtp->datasend("MIME-Version: 1.0\n"); $smtp->datasend("Content-Type: multipart/mixed; boundary=\"$boundary\"\n"); $smtp->datasend("\n--$boundary\n"); $smtp->datasend("Content-Type: text/plain;\n\n"); $smtp->datasend("This is the text body of the message.\n"); $temp = $ENV{TMP} || $ENV{TEMP}; if ( "$temp" eq "" ) { die "Must have a temp directory defined"; } foreach $filename ("file1.xls","file2.doc") { # Copy the file from the database to the local disk. $full_path = "$temp/$filename"; $attachment->Load($full_path); # Read the file contents, then delete the temporary file. open(FILE,$full_path); binmode(FILE); @buffer = (<FILE>); close(FILE); $content = join("",@buffer); unlink($full_path); # Mime encode the file and get it's mimed length. $encode = encode_base64($content); $length = length($encode); # Write the email entry. $attachment = "\n--$boundary\n"; $attachment .= "Content-Type: application/octet-stream; name=\"$filename\"\n"; $attachment .= "Content-Disposition: attachment; filename=\"$filename\"\n"; $attachment .= "Content-Transfer-Encoding: base64\n"; $attachment .= "Content-Length: $length\n"; $attachment .= "\n$encode\n"; $smtp->datasend($attachment); } $smtp->datasend("--$boundary--\n");Table of Contents
Force a user to enable email notification.
if ( ! $session->IsEmailEnabled ) { $result = "\n\nERROR: You must have email notification enabled to utilize this record type.\n"; }Table of Contents
Programmatically send emails.
send_mail("emailaddress\@company.com;address2\@company.com","Subject","The body text allows <u><b>HTML</b></u>.\n"); sub send_mail { use Net::SMTP; my $smtp_server = "smtpserver.company.com"; my $to = $_[0]; my $subject = $_[1]; my $body = $_[2]; my $smtp = Net::SMTP->new("$smtp_server"); if ( "$smtp" eq "" ) { $session->OutputDebugString("send_mail: ERROR: Unable to reach the SMTP server: $smtp_server\n"); return; } $smtp->mail("ClearQuest-NoReply\@company.com"); foreach $address (split(/\;/,$to)) { $address =~ s/\s+//g; $smtp->to($address); } $smtp->data(); $smtp->datasend("To: $to\n"); $smtp->datasend("Subject: $subject\n"); $smtp->datasend("Content-type: text/html\n\n"); $smtp->datasend("\n"); $smtp->datasend("$body\n"); $smtp->dataend(); $smtp->quit; return; }
my $mailmsg = CQMailMsg::Build; $mailmsg->AddTo("emailaddress\@company.com"); $mailmsg->SetSubject("This is the subject"); $mailmsg->SetBody("This is the body text!\n"); $mailmsg->Deliver; CQMailMsg::Unbuild($mailmsg);
Function Email_This_User (entity_type, entity_id, field_name) email_to = GetFieldValue(field_name).GetValue email_subject = entity_type & " " & entity_id email_body = "This email was sent from ClearQuest." Set oOutlook = CreateObject("Outlook.Application") Set oEmail = oOutlook.CreateItem(dummy) oEmail.To = email_to oEmail.Subject = email_subject oEmail.Body = email_body oEmail.Display end function
mailto:user@company.com?subject=This%20is%20the%20subject&body=This%20is%20the%20body
Install the EmailPlus package.
Basic EmailPlus rule.
EmailPlus tags.
#@expression::$session->GetSessionDatabase->GetDatabaseName;@#
#@expression::$entity->GetActionName;@#
State: #?State?# #@EXPRESSION::if (#?State?# ne #%State%#) { "(Old Value: ". #%State%#.")"; }@#Table of Contents
EmailPlus Advanced Rule.
OneOf($list, $item) | This routine takes a list and searches it for the presence of the given item. It returns TRUE if the item is found else FALSE. $list - A reference to an array of string values $item - The value to search the list for |
Gfv($fieldName) | Simple encapsulation of GetFieldValue which is equivalent to: GetFieldValue($fieldName)->GetValue() $fieldName – The name of the field |
Gfov($fieldName) | This is similar to Gfv, except it is the simple encapsulation of GetFieldOriginalValue which returns the original value of a field before any changes were made. |
Gfvs($fieldName) | This function is the simple encapsulation of ClearQuest API call GetFieldValueStatus and is equivalent to: GetFieldValue($fieldName)->GetValueStatus() |
FChg($list) | Examines the list of field names given and returns a string to indicate what fields have changed: "ANY" means one or more of the fields in the list have changed "ALL" means all the fields in the list have changed "NONE" means none of the fields have changed $list - A reference to an array of field names to check for change. |
StoDT($dateString) | Converts a date string value from a ClearQuest DATE_TIME field to a UTC date/time in seconds. The return value is the number of non-leap seconds since the epoch. On most systems the epoch is 00:00:00 UTC, January 1, 1970. $dateString - A date string of the format “YYYY-MMClearQuest DD hh:mm:ss”. |
DTtoS($timestamp) | Converts a UTC Date/Time in seconds into a date string of format YYYY-MM-DD hh:mm:ss. $timestamp – A UTC date/time in seconds. |
EmailPlus subscribers