Home > Articles > Programming

  • Print
  • + Share This

The SELECT Statement

The starting point for any query is a well-formed SELECT statement. The SELECT statement returns the values of properties in a particular folder or folders within a store. It has the following general syntax:

SELECT <list of properties separated by commas or *>
FROM SCOPE (shallow traversal of <some folder URL>
            deep traversal of <some folder URL>)
WHERE <some search condition>
ORDER BY <list of properties separated by commas>

To select specific properties, you need to enclose each property name in quotation marks and separate each property name with commas:

SELECT "DAV:href", "DAV:displayname", "urn:schemas:contacts:fileas"

SELECT * and ADO's GetChildren Method

SELECT * is also valid syntax in a WSS SQL query. A SELECT * query returns all of the properties defined by the expected content class of the specified folder. However, just because you can do something doesn't mean you should. Although using SELECT * might be interesting when playing with WSS SQL to identify every value you can return, you should never use it in production code. It is both extremely time-consuming and unnecessarily broad, and it even makes the Exchange server perform unnecessary processing to get the Stream objects.

Another argument against using SELECT * is that unless you define your custom properties in a schema, you will not get them using the SELECT * query. As we'll see later, adding your own custom properties to an item is as easy as adding them to the item's Fields collection. However, since they are not defined specifically in the item's schema, SELECT * doesn't know anything about them. If you do specify the custom properties in the SELECT statement, they will be retrieved.

While we're on the subject, you may have noticed the GetChildren method of the ADO Record object. Its purpose is to return a Recordset whose rows represent the files and subdirectories in the directory represented by the Record. For example, let's assume that we have a valid ADO Record called objRec. If objRec.RecordType = adCollectionRecord, meaning that the record consists of a collection of records, we can use the code

Dim objRecordset As ADODB.Recordset
Set objRecordset = objRec.GetChildren

to get a Recordset consisting of all of the records in the collection. Unfortunately, this is the computational equivalent of the SELECT * query and should be avoided in production code.

Query Scope

Another component of a well-formed WSS SQL query is its scope. Instead of querying a table in a database, in WSS SQL you issue a query on a folder scope. A folder scope consists of two parts: a folder specified by a URL and a depth. We are already familiar with building URLs; the scope part is even simpler. A scope of deep traversal on a folder means that the folder and all of its subfolders will be searched. A scope of shallow traversal means that only the specified folder will be searched.

As we mentioned earlier, the Public Folders TLH, which is the MAPI client public folder store, is limited to shallow traversal (as are also, you may remember, such Exchange clients as Outlook 2000's All Public Folders). It is simply not implemented like the other public stores in the Web Storage System. Veteran collaborative application programmers will remember that they had to write some recursive code to get at all of the folders in the MAPI store. In Exchange 2000, this is still true for the Public Folders TLH only. Any other public folder store or private folder store allows deep traversals. For example, the following code finds all child folders contained in the particular folder. The query is limited to all visible folders contained in the specified folder; subfolders are not searched:

SELECT "DAV:href", "DAV:displayname"
FROM SCOPE ('shallow traversal of _
"file://./backofficestorage/GomezaMobile.local/MBX/Administrator/
        Contacts"')
WHERE "DAV:isfolder" = True and "DAV:ishidden" = False

On the other hand, this next query will include any and all subfolders:

SELECT "DAV:href", "DAV:displayname"
FROM SCOPE ('deep traversal of _
"file://./backofficestorage/GomezaMobile.local/MBX/Administrator/
        Contacts"')
WHERE "DAV:isfolder" = True and "DAV:ishidden" = False

Note: Lest you believe that WSS SQL accepts only file URLs, let's set the record straight. You can use either file or HTTP URLs in WSS SQL. Therefore, the following query is equivalent to the one given in the text:

SELECT "DAV:href", "DAV:displayname"
FROM SCOPE ('deep traversal of _
"http://GOMEZAWIN2000.GomezaMobile.Local/exchange/Administrator/
        Contacts"')
WHERE "DAV:isfolder" = True and "DAV:ishidden" = False

In our previous examples we used the full URL path. The SCOPE operator can also accept relative URL paths because WSS SQL queries are executed on the basis of the context of an ADO connection. For example, let's pretend that you already have a connection open to the Administrator's root mailbox folder:

file://./backofficestorage/GomezaMobile.local/MBX/Administrator

The following query gets everything (including subfolders) under the Administrator's Contacts folder:

SELECT "DAV:href", "DAV:displayname"
FROM SCOPE ('deep traversal of "/Contacts"')
WHERE "DAV:isfolder" = True and "DAV:ishidden" = False

Note: This relative URL syntax works only with file URLs. If you are writing WSS SQL queries using HTTP URL syntax, you must specify the entire path.

SCOPE Shorthand

A couple of syntax shortcuts are possible when you're working with SCOPE. Use of the SCOPE keyword in a SELECT statement is optional. If you do not use the SCOPE keyword (as in the query that follows), the search depth defaults to shallow:

SELECT "DAV:href" from
"file://./backofficestorage/GomezaMobile.local/MBX/Administrator/
        Contacts"
WHERE "DAV:isfolder" = True and "DAV:ishidden" = False

Note: Not using the SCOPE keyword but still specifying the scope (as in the malformed query that follows) is a simple syntax error:

SELECT "DAV:href", "DAV:displayname"
FROM 'shallow traversal of _
"file://./backofficestorage/GomezaMobile.local/MBX/Administrator/
        Contacts"'
WHERE "DAV:isfolder" = True and "DAV:ishidden" = False

If you do use the SCOPE keyword but fail to specify shallow or deep traversal, the SELECT statement defaults to deep traversal, as in this query:

SELECT "DAV:href", "DAV:displayname"
FROM SCOPE _
('"file://./backofficestorage/GomezaMobile.local/MBX/Administrator/
          Contacts"')
WHERE "DAV:isfolder" = True and "DAV:ishidden" = False

Then there is the sin of omission when dealing with the public folder tree designed for MAPI clients. As we have mentioned several times, a deep traversal on that tree results in an error. As you probably would expect, a query that defaults to a deep traversal (as in the preceding syntax) also generates an error.

Note: When you do not use the SCOPE keyword, you can simply surround the URL with double quotation marks. However, when you do use the SCOPE keyword, not only must you use double quotation marks around the URL, but you must also surround the entire clause with single quotation marks. This syntax must be followed to the letter. More on this later.

You can specify more than one folder in your SCOPE clause as long as you separate each one with commas, as in the following example:

SELECT "DAV:href", "DAV:displayname"
FROM SCOPE ('shallow traversal of _
"file://./backofficestorage/GomezaMobile.local/MBX/Administrator/
        Contacts"', _
'shallow traversal of _
"file://./backofficestorage/GomezaMobile.local/MBX/Administrator/
        Inbox"')
WHERE "DAV:isfolder" = True and "DAV:ishidden" = False

SCOPE Errors

When you search more than one folder, you must specify the same depth for each folder. You cannot mix and match deep and shallow searches in the same SELECT statement. If you do, you get the error shown in Figure 7.6.

Figure 7.6
Error resulting from mixing shallow and deep traversals in a single SELECT statement

While we're on the subject of things that can go wrong when you're working with SCOPE, remember how security works. If you try to execute a query against a folder to which you do not have access, you will get the error message "URL is outside of scope" (see Figure 7.7). The query uses the connection that you specify to do its work. So, for example, if you open a connection to a user's private mailbox and then run a query against a public folder store, you will get the same "URL is outside of scope" error.

Figure 7.7
Error resulting from querying against a folder that the connection can't access

If you decide to do something wrong, such as specifying deep traversal of the MAPI public folder store, you will get the error message "Specified resource was out-of-scope" (see Figure 7.8).

Figure 7.8
Error resulting from specifying deep traversal on a MAPI public folder store

A generic catchall error message for any syntax error in WSS SQL is "Submitted SQL statement was incomplete" (see Figure 7.9). As you may suspect, this is the most common error.

Figure 7.9
General WSS SQL syntax error

The WHERE Clause

We now come to the most diverse part of the SELECT statement: the WHERE clause. It is diverse because WHERE clauses can contain any number of logical conditions chained together by AND and OR keywords. What's more, the WHERE clause can have predicates (GROUP BY, LIKE, ORDER BY, and so on).

The Web Storage System uses tokens as values for search conditions in a WHERE clause. Tokens are surrounded by single quotation marks. They are the literal values on which you want to search. For example, the following query returns all contacts in the Administrator's Contacts folder that work in Massachusetts:

SELECT "DAV:href", "urn:schemas:contacts:fileas", 
   "urn:schemas:contacts:email1"
FROM "file://./backofficestorage/GomezaMobile.local/MBX/Administrator/Contacts"
WHERE ("urn:schemas:contacts:st" = 'MA')

Note that field names used in the WHERE clause are surrounded by double quotation marks, just as field names used in the SELECT clause are. So far, you would expect to specify a literal in SQL by using single quotation marks anyway. Now here's the catch. A token can contain only alphabetical characters unless it is surrounded by double quotation marks. For example, how would one get all contacts who were also vice presidents? Would the following code work?

SELECT "DAV:href", "urn:schemas:contacts:fileas", 
   "urn:schemas:contacts:email1"
FROM "file://./backofficestorage/GomezaMobile.local/MBX/Administrator/Contacts"
WHERE ("urn:schemas:contacts:title" = 'Vice President')

As it turns out, this code would not work. Since the token contains a space, we need to surround it in double quotation marks first and then tack on the single quotation marks. The following query is syntactically correct and returns the desired results:

SELECT "DAV:href", "urn:schemas:contacts:fileas", 
   "urn:schemas:contacts:email1"
FROM "file://./backofficestorage/GomezaMobile.local/MBX/Administrator/Contacts"
WHERE ("urn:schemas:contacts:title" = '"Vice President"')

As we have seen in prior examples, a couple of values can go in the WHERE clause that do not need single or double quotation marks. These are the Boolean True and False values, as in the following query:

SELECT "DAV:href", "DAV:displayname"
FROM SCOPE ('shallow traversal of _
"file://./backofficestorage/GomezaMobile.local/MBX/Administrator/
        Contacts"', _
'shallow traversal of _
"file://./backofficestorage/GomezaMobile.local/MBX/Administrator/
        Inbox"')
WHERE "DAV:isfolder" = True and "DAV:ishidden" = False

Specifying a Date Range in a WHERE Clause

When you're specifying date values in the WHERE clause, the single quotation marks around the date value are all you need. For example, the following query is syntactically correct if you're asking for contacts in the Administrator's Contacts folder that were created on that person's birthday:

SELECT "DAV:href", "urn:schemas:contacts:fileas", 
    "urn:schemas:contacts:email1"
FROM "file://./backofficestorage/GomezaMobile.local/MBX/Administrator/
             Contacts"
WHERE "DAV:creationdate" = '2000-07-28'

However (there always seems to be a however), dates are not stored in items in this format. They also contain the hour, minute, second, and millisecond of creation. So a more plausible WHERE clause would be the following:

WHERE "DAV:creationdate" = '2000-07-28 11:39:39.275'

However (again), you most likely will not know the exact fraction of a second when a particular item was created. A more practical way to guarantee that you get all contacts in the Administrator's Contacts folder that were created on that person's birthday would use the following code:

SELECT "DAV:href", "urn:schemas:contacts:fileas", 
    "urn:schemas:contacts:email1"
FROM "file://./backofficestorage/GomezaMobile.local/MBX/Administrator/
             Contacts"
WHERE "DAV:creationdate" > '2000-07-27' AND "DAV:creationdate" 
    < '2000-07-29'

In this case we are looking for everything greater than the day before and less than the day after.

Embedded WSS SQL

As you may have gathered, WSS SQL suffers from the same problems that plague any embedded SQL code and have discouraged its use. The parsing and overcompensating for the quotation marks that are part of the syntax can become really cumbersome. To make sure that everything looks good to the query parser (in virtually every circumstance), we come up with some string beauties in our VB code, as in the following example, which gets everything from the currently logged-on user's Contacts folder:

  Dim objMailbox As CDO.IMailbox
  Dim objPerson As New CDO.Person
  Dim sSQL As String
 
  ' Get the currently logged-on user:
  ' UserName should be something like:
  ' CN=Administrator,CN=Users,DC=GomezaMobile,DC=local
  objPerson.DataSource.Open "LDAP://" & objInfo.UserName
  
  ' Get his or her mailbox:
  Set objMailbox = objPerson
  
  sSQL = "select ""DAV:href"", " & _
                 """urn:schemas:contacts:fileas"", " & _
                 """urn:schemas:contacts:email1""" & _
                 " from "
  sSQL = sSQL & """" & objMailbox.Contacts & """"

Here's what seems to be another interesting WSS SQL anomaly. Let's assume in the following VB code snippet that EX_DISPLAYNAME and EX_ISFOLDER are previously defined constants containing the schema field names and that RootURL is a valid URL that was passed into the hypothetical function as an argument. This is valid embedded WSS SQL syntax:

  sRootSQL = "SELECT " & """" & EX_DISPLAYNAME & """ " & _
             "FROM scope('shallow traversal of """ & RootURL & """') " & _
             "WHERE (" & """" & EX_ISFOLDER & """ = True) " & _
             "ORDER BY " & """" & EX_DISPLAYNAME & """"

No doubt some of the more experienced SQL/ADO programmers in the reading audience are cringing. Isn't this one of the reasons we have stored procedures and parameterized queries in SQL databases--to get rid of ugly embedded code in programs and help maintainability? Yes it is, but consider the fact that WSS SQL is in its infancy and embedded is all we have right now. So when developing production code, just be careful, use your best judgment, and test, test, test.

Note: You may have noticed from the plethora of WSS SQL query examples that the keywords in the language are not case sensitive. "SELECT" is as good as "select."

Another feature that makes the WHERE clause versatile is the fact that you can add what are called predicates to it. The next few sections discuss these in more detail.

The CAST Function

The CAST function is similar to cast functions in other languages in that it explicitly converts a field to another data type. In WSS SQL, you use CAST to attribute a data type to a property in the returned Recordset object. The Web Storage System recognizes all data types in the Exchange 2000 schema installed with the product. CAST is used mainly for working with custom properties because the Web Storage System might not know what their types are. Using CAST in a WSS SQL query is entirely optional. If you don't use it, your custom properties are evaluated as string values. Boolean properties don't need to be cast.

The basic syntax of the CAST function is as follows:

CAST ("<some value>" AS "<some datatype>")

"<Some value>" can be the name of a property or a literal string (both need to be surrounded by double quotation marks). Here's the catch: The CAST function supports only XML data types. Therefore "<some datatype>" needs to be replaced with one of the values from Table 7.8 (surrounded by double quotation marks, of course).

Some examples will help illustrate all of this. This first one casts a custom date property to a data time before comparing it:

WHERE CAST("custom:mydateprop" AS "datetime") > "2001-12-01T10:00"

The next example casts the string "0" as an integer to compare it with another integer:

WHERE "custom:count" > CAST("0" AS "int")

UUID properties are recognized with or without the braces, as in the next two examples:

   WHERE CAST ("myguid" AS "uuid") 
      = "F04DA480-65B9-11d1-A29F-00AA00C14882"
   WHERE CAST ("myguid" AS "uuid") 
      = "{F04DA480-65B9-11d1-A29F-00AA00C14882}"

All Microsoft Exchange 2000 binary properties are base 64 encoded by default because this is a format that can be sent in XML:

WHERE CAST ("custom:mybinaryprop" AS "bin.base64") > "a98k231KJg823kj"

Table 7.8  XML Property Data Types Supported by CAST

XML Type Description
bin.base64 Binary data (base 64 encoded)
bin.hex Hex data
Boolean In an XML string, this value is 1 for True and 0 for False
char Character data
date Just the date
dateTime Date and Time
datTime.tz Full date and time stamp, including time zone
fixed.14.4 Fixed floating-point number
float Floating-point number
i2 2-byteinteger (Smallint)
i4 4-byte integer
i8 8-byte integer
int 4-byte integer
number General numeric data
r4 4-byte floating-point number (Float)
r8 8-byte floating-point number (Real)
string 2-byte character string (Unicode)
time Time stamp
time.tz Time stamp, including time zone
ui1 Tinyint
ui2 Numeric (5,0)
ui4 Numeric (10, 0)
ui8 Numeric (19, 0)
uri A valid Uniform Resource Identifier
uuid GUID in string format

If you want to encode using hex, you must cast the property and the value against which you are comparing it:

WHERE CAST ("custom:mybinaryprop" AS "bin.hex") = _
CAST("08AF183B82" AS "bin.hex")

Enabling Full-Text Indexing

The next four predicates deal with character and pattern matching in WSS SQL. First we'll introduce each one, and then we'll explain how to use them in WSS SQL queries. Most of these predicates work only if full-text indexing is applied to the store being searched. To implement full-text indexing (Figure 7.10), open the Exchange System Manager, expand Servers, and then expand your Exchange server and right-click on the store to be indexed.

Figure 7.10
Implementing full-text indexing using the Exchange System Manager

You will be prompted for where to put the catalog. After you create the index, right-click on the store and choose Properties. You will see a new Full-Text Indexing tab similar to the one in Figure 7.11, where you can regulate how often the index is updated and when it should be rebuilt.

Figure 7.11
Full-text indexing maintenance using the Exchange System Manager

Note: In order to use the index in your queries, you must check off This index is currently available for searching by clients.

Even after doing this, you will get the error depicted in Figure 7.12 because the index has been enabled but not actually created.

Figure 7.12
A full-text index must be built before it can be used

To build the index, right-click on the store in the Exchange System Manager and select Start Full Population. You will be warned that it might take a long time depending on the size of your store. However, this option will build the complete index right away. You can check on the progress of the index by expanding the store in Exchange System Manager, clicking Full-Text Indexing, and inspecting the "last build time" property, as shown in Figure 7.13.

Figure 7.13
Checking on the Exchange System Manager's progress

For more information, search on "Full-Text Indexing" in the Exchange 2000 SDK.

The CONTAINS Predicate

The CONTAINS predicate allows you to search for a specific word or phrase in a predetermined order. Only whole words are matched, so you cannot use wild-card expressions here. The basic syntax is

CONTAINS ("<property name>", '<search specification>')

The property name is optional. If none is specified, then only the message or document body is searched. You can also use an asterisk (*) to include all properties marked for full-text indexing. The property name must be a string surrounded by double quotation marks, such as urn:schemas:mailheader:to. The asterisk does not have to be surrounded by the quotation marks.

The search specification is the string to search for, surrounded by single quotation marks and following the same rules as discussed earlier for tokens in a WHERE clause. You can use the AND and OR keywords and the FORMSOF predicate (discussed in the next subsection). The NOT operator is allowed only if it follows the AND keyword.

Note: The search specification parameter of the CONTAINS predicate is case sensitive, and the property must match exactly. For example, in the clause that follows, only contacts that work at Sanford And Son will be returned:
WHERE CONTAINS ("urn:schemas:contacts:o", '"Sanford And Son"')

The FORMSOF Predicate

The FORMSOF predicate allows you a bit of leeway. It is used as part of a WHERE clause to find word variations, typically within a CONTAINS or FREETEXT predicate. The basic syntax is

FORMSOF (<type>, "<string>" [, <some other string>])

In the Web Storage System the only type supported is INFLECTIONAL. The string can be one or more words for which the search engine is to find variations. Each word must be delimited by double quotation marks and separated by commas. For example,

FORMSOF (INFLECTIONAL, "fight")

will find items containing "fighter," "fighters," "fighting," and so on.

When FORMSOF is used within a CONTAINS or FREETEXT predicate, the whole FORMSOF predicate is considered a token, so it must be surrounded by single quotation marks:

WHERE CONTAINS ("urn:schemas:contacts:hobbies", _
'FORMSOF (INFLECTIONAL, "photograph", "announce")')

In this case all contacts that listed "photography," "photographs," "photographer," "announcer," "announcing," and so on would be returned.

The FREETEXT Predicate

The FREETEXT predicate is used as part of a WHERE clause to loosely match any item having a variation of a word or a group of words. The basic syntax is

 FREETEXT ("<property name>" [, <some other property name>],
 '<search criteria>')

The property name is optional. If none is specified, then only the message or document body is searched. You can also use an asterisk (*) to include all properties marked for full-text indexing. The property name must be a string surrounded by double quotation marks, such as "urn:schemas:mailheader:to". The asterisk does not have to be surrounded by the quotation marks.

The search specification is the string to search for, surrounded by single quotation marks and following the same rules as discussed earlier for tokens in a WHERE clause. You can use the AND and OR keywords and the FORMSOF predicate (see the previous subsection). The NOT operator is allowed only if it follows the AND keyword.

The main difference is that FREETEXT allows you to find one or more words specified in the search criteria. For example,

WHERE FREETEXT ("urn:schemas:contacts:personaltitle", 'Mr Ms Dr')

pulls all contacts with "Mr," "Ms," or "Dr" in their name. When you're using the FREETEXT predicate, a group of words can match several items. For example, in the clause that follows, any item containing "garden," "gardening," "gardener," "gardens," "rose," "roses," and so on will be returned:

WHERE FREETEXT ("urn:schemas:contacts:hobbies", 'garden rose')

The LIKE Predicate

The LIKE predicate is used as part of the WHERE clause to perform character matches. LIKE does a character-by-character comparison of the specified property value and the supplied characters. It essentially finds characters that match a specified string. The basic syntax is

WHERE "<property name>" LIKE "<value>"

The property name must be surrounded by double quotation marks. The value needs to be surrounded by single quotation marks. The percent character (%) matches any or no characters adjacent to the specified string. For example, "%ar%" would match "Mary":

WHERE ("urn:schemas:contacts:spousecn" LIKE '%ar%')

The GROUP BY Predicate

The GROUP BY predicate organizes the returned Recordset object according to one or more of the properties specified in the SELECT statement. The basic syntax is

GROUP BY "<property name>" [, <some more property names>]

Basically, you can have any number of property names surrounded by double quotation marks and separated by commas. The only catch is that the property names have to be specified in the SELECT clause as well:

SELECT "DAV:href", "urn:schemas:contacts:fileas", _
       "urn:schemas:contacts:email1", _
       "urn:schemas:contacts:st"
  FROM "file://./backofficestorage/GomezaMobile.local/MBX/Administrator/ 
               Contacts"
  GROUP BY "urn:schemas:contacts:st"

Getting Item Counts with GROUP BY

As we'll see shortly, WSS SQL does not support COUNT. However, by including the DAV:visiblecount property in the SELECT statement, you can get a count of the items that you specify in the GROUP BY clause. The DAV:visiblecount property is a read-only property that returns the number of visible items in a Recordset object that are not folders. That is, items that have DAV:isfolder and DAV:ishidden set to True will not be counted.

For example, let's say that you want to count the number of low-importance, normal-importance, and high-importance messages in a user's inbox. Your query could go something like this:

SELECT "DAV:visiblecount", "urn:schemas:httpmail:importance", _
FROM "file://./backofficestorage/GomezaMobile.local/MBX/Administrator/
            Inbox"
GROUP BY "urn:schemas:httpmail:importance"

Let's assume that objRecordset is a new ADO Recordset, that sSQL contains the query string given in the preceding code, and that objConn is a valid connection to the Administrator's root mailbox folder:

objRecordset.Open sSQL, objConn

Now take the resulting Recordset and loop through it, checking for the urn:schemas:httpmail:importance field. Whenever you find it, consult the DAV:visiblecount property to get the total for that importance value. The code to do this would look something like this:

 Dim sImportance As String
 objRecordset.MoveFirst
 Do Until objRecordset.EOF
     Select Case objRecordset("urn:schemas:httpmail:importance")
         Case 2
             sImportance = "High"
         Case 1
             sImportance = "Normal"
         Case 0
             sImportance = "Low"
   End Select
   MsgBox objRecordset("DAV:visiblecount") & " " & sImportance _
      & " importance messages."
 Loop
  • + Share This
  • 🔖 Save To Your Account

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.

Overview


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information


To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.

Surveys

Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.

Newsletters

If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information


Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.

Security


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.

Children


This site is not directed to children under the age of 13.

Marketing


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information


If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.

Choice/Opt-out


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information


Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents


California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure


Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.

Links


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact


Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice


We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020