Recently we launched v201004, and with that introduced the new Statement object which gives support for one of our top requested features - bind variables. In this blog post, we will take a closer look at the Publisher Query Language (PQL), Statements with bind variables, and what we have in store for the future.
Publisher Query Language
PQL plays a very significant role in the DFP API by providing the
developer with a robust way of filtering which objects should be retrieved or
modified before the request is completed. In other words, if you would like to
retrieve only orders which are in the draft state, you could take
one of two approaches. You could fetch all orders within your network and filter
them one by one or instruct the server to only fetch orders in the draft state
before returning all results. By doing the latter, the DFP API allows developers
to create smaller and more direct requests, and, in turn, increases the
efficiency of their code.
PQL has a very similar syntax to SQL, but does not include keywords such
as SELECT or FROM; they are implied by
the method which uses the PQL statement. The following piece of code constructs
a Statement capable
of fetching orders in the draft state and retrieves those orders:
//
Create a statement to only select orders in the
// 'DRAFT' state.
Statement
filterStatement = new Statement();
filterStatement.setQuery("WHERE
status = 'DRAFT' LIMIT 500");
OrderPage
orderPage =
orderService.getOrdersByStatemet(filterStatement);
The documentation included for each "
get*ByStatment" (e.g.
getOrdersByStatement) method indicates which PQL
fields map to which object properties.
Paging
The result for
"get*ByStatment" calls are pages specific to
the service; i.e. an
OrderPage is returned by
getOrdersByStatement.
The limit for the number of objects that can be fetched for a single PQL
request, and in a single
Page,
is 500. Because of this, you should always include
LIMIT 500 in your
statement. However, if you would like to fetch more than 500 objects, you will
need to page through the results by including an
OFFSET <#> in your statement as well. To
page through orders in groups of 500, for example, in your statement, you would
include
LIMIT 500 as
well as an
OFFSET of
an interval of 500.
This can be represented by the following code:
//
Sets defaults for page and filter.
OrderPage
page = new OrderPage();
Statement
filterStatement = new Statement();
int
offset = 0;
do
{
// Create a statement to get all orders.
filterStatement.setQuery(
"WHERE status = 'DRAFT' LIMIT 500 OFFSET " +
offset);
// Get orders by statement.
page = orderService.getOrdersByStatement(filterStatement);
if (page.getResults() != null) {
int i = page.getStartIndex();
for (Order order : page.getResults()) {
System.out.println(i + ") Order with ID \""
+ order.getId() + "\", name \"" + order.getName()
+ "\", and advertiser ID \"" +
order.getAdvertiserId()
+ "\" was found.");
i++;
}
}
offset += 500;
}
while (offset < page.getTotalResultSetSize());
The loop will end when there are no pages left, i.e. the offset is
greater than or equal to the to the total result set size.
Bind variables
Bind variables were recently introduced to allow for reusing of the same
template PQL statement combined with varying parameters. To change the PQL
statement above to differ which status is being selected,
'DRAFT' is changed to the
bind variable
status, represented by :status. Note that bind variables can be any name - not just the name of their property. We chose
:status here for simplicity.
//
Create a statement to only select orders in the state
// bound to status.
Statement
filterStatement = new Statement();
filterStatement.setQuery("WHERE
status = :status LIMIT 500");
To bind to
:status, you will need to create a parameter map with a
String_ParamMapEntry coupling
:status with a
StringParam. Note that ":" is not included in the bind variable name in the parameter map.
// Create the string parameter.
StringParam stringParam = new StringParam();
// Create bind parameters map.
String_ParamMapEntry[] paramMap = new String_ParamMapEntry[] {
new String_ParamMapEntry("status", stringParam)
};
filterStatement.setParams(paramMap);
Before you make the call to
getOrdersByStatement, set the
stringParam value
to the specific status.
stringParam.setValue("DRAFT");
In this case, because
status was bound to a variable declared before the parameter map, you can set the variables value at any time.
The first iteration of the while loop above would then produce the
following XML snippet:
...
<filterStatement>
<query>WHERE status = :status LIMIT 500 OFFSET 0</query>
<params>
<key>status</key>
<value xmlns:ns2=
"https://www.google.com/apis/ads/publisher/v201004"
xsi:type="ns2:StringParam">
<value>DRAFT</value>
</value>
</params>
</filterStatement>
...
Planned features and production
The release of v201004 was the culmination of the last step before we can
begin rolling out access to the production API and we will have more information
about signing up to for production use in the coming weeks. We are still hard at
work on the forecasting and reporting service and will have some news about
those in the following weeks as well.
We appreciate all of the great feedback we've been receiving on the
forum, both on the API and the
client libraries, and we''ll continue to
incorporate it as we continue development.
-- Adam Rogal, The DoubleClick for Publishers API Team
Jeffrey Sham, DFP API Team
Adam Rogal, DFP API Team
