DMQL Tutorial

One of the big advantages to using RETS over a different delivery method (such as FTP) is the ability to tell the server specifically which records you'd like to be returned. In order to do this, a query language called DMQL (or, more recently, DMQL2) is used that defines the structure of search conditions that you send to the server.

Introduction

DMQL is made up of simple name-value pair conditions passed as part of the requested URL in RETS. By passing in requested conditions, you can limit what types of records you get back in real-time.

Syntax

Name-value conditions are passed with each surrounded in parentheses. For example:

(name=value)

To pass multiple conditions, each is separated by a comma (see Operators below for more information about separators). This will only return records where name1 is equal to value1 and name2 is equal to value2:

(name1=value1),(name2=value2)

You can also search a single field for multiple values. For example, the below returns records that match 1) name1 is value1a or value1b or value1c, and 2) name2 is value2:

(name1=value1a,value1b,value1c),(name2=value2)

The name part is either the SystemName (when requesting StandardNames use as false, default) or the StandardName (when requesting StandardNames use as true) defined for the field.

The value part is made up of searchable patterns as described in the sections below.

Character Fields

In RETS, the condition value allows for special characters depending on the type of field being searched. For Character fields, you're able to use wildcard characters that allow you to find character patterns in a field.

Example: where 'name' field starts with 'value'

(name=value*)

Example: where 'name' field contains 'value'

(name=*value*)

Example: where 'name' field starts with either 'value' or 'other'

(name=value*,other*)

Example: where 'name' field equals 'val?e' where ? represents a single character

(name=val?e)

Numeric Fields

In RETS, the condition value allows for special characters depending on the type of field being searched. For Tiny, Small, Int, Long and Decimal fields, you're able to use special characters to indicate ranges of numbers.

Example: where 'number_field' is greater than or equal to 2

(number_field=2+)

Example: where 'number_field' is less than or equal to 5

(number_field=5-)

Example: where 'number_field' is between 2 and 4 (including 2 and 4)

(number_field=2-4)

Date/Time Fields

In RETS, the condition value allows for special characters depending on the type of field being searched. For Date, DateTime and Time fields, you're able to use special characters similar to Numeric fields (see above) to search for ranges of dates and times.

For Date fields, the value must be sent in YYYY-MM-DD format.

For Time fields, the value must be sent in HH:MM:SS format.

For DateTime fields, the value must be sent in YYYY-MM-DDTHH:MM:SS format (Date and Time separated by a 'T').

Example: where 'date_field' is on or after 2009-01-01

(date_field=2009-01-01+)

Example: where 'date_field' is on or before 2009-01-01

(date_field=2009-01-01-)

Example: where 'date_field' is between 2009-01-01 and 2009-01-25 (including 2009-01-01 and 2009-01-25)

(date_field=2009-01-01-2009-01-25)

Example: where 'datetime_field' is on or after 2009-01-01T00:00:00

(datetime_field=2009-01-01T00:00:00+)

Timezones and GMT conversion

When requesting records based on a Date or Time, the value you send must be converted to GMT. This ensures that the server knows exactly what timeframe you're interested in querying for since your local timezone information isn't available to the RETS server. The server will read your GMT date/time and will convert that back to it's local timezone in order to run queries on the data.

Also, note that dates and times you receive back from the RETS server will NOT be converted back to GMT time.

Examples

The examples below demonstrate some common queries done with a RETS server. The following describes the fields being used in the examples (see the metadata information available from the RETS server for specific field names you can use):

All records that have been modified on or since 2009-02-01T00:00:00 GMT

(LIST_87=2009-02-01T00:00:00+)

All records that have at least 2 pictures and costs $179,000 or less

(LIST_133=2+),(LIST_22=179000-)

All records that have 4 pictures on either Main St. or West St.

(LIST_133=4),(LIST_34=Main,West)

All records that have a status of "Active"

(LIST_15=|RNKKL0G8XM0)

All records that have a status of either "Active" or "Pending"

(LIST_15=|RNKKL0G8XM0,RNKKL0G8Y02)