Reply to comment

Description: 
With YQL, apps run faster with fewer lines of code and a smaller network footprint. One can now leverage YQL to query, filter, sort, and unify records from any QuickBase table

The Yahoo! Query Language is an expressive SQL-like language. With YQL, apps run faster with fewer lines of code and a smaller network footprint. One can now leverage YQL to query, filter, sort, and unify records from any QuickBase table.

http://intuitlabs.com/apps/yql-and-intuit-quickbase

What is YQL?

The Yahoo! Query Language is an expressive SQL-like language that lets you query, filter, and join data across Web services. With YQL, apps run faster with fewer lines of code and a smaller network footprint.

Yahoo! and other websites across the Internet make much of their structured data available to developers, primarily through Web services. To access and query these services, developers traditionally endure the pain of locating the right URLs and documentation to access and query each Web service.

With YQL, developers can access and shape data across the Internet through one simple language, eliminating the need to learn how to call different APIs.

It has generated a lot of excitement because of its potential to turn various web services (recently estimated to be around 5000) to your own databases, literally.

Making QuickBase YQL-ized

We have gone ahead and made QuickBase YQL-friendly. It works for both quickbase.com and workplace.intuit.com domains. Instead of learning proprietary query syntax, one can now leverage YQL to query, filter, sort, and uniquify records from any QuickBase table.

With the recently added writing capability in YQL, you can now also insert, update and delete records in a QuickBase table.

Example: Query QuickBase Data

Let's assume that we have a QuickBase table about books, with the following fields: title, author, year, price, and pages. It looks like following when I do "List All" in QuickBase GUI.

IMG

You can access the QuickBase used in this demo here - https://workplace.intuit.com/db/befpcwgem

Before we begin, you need some basic things:

  • Your QuickBase ID: Let's assume it is 'xxxxxxx'.
  • QuickBase Ticket: Let's assume it is 'yyyyyy' (Depending on the app the table belongs to, you may also need a QuickBase App Token.)

Say when we want to find out info on title, author and price for the books with more than 500 pages and an author named "david". Let's do this using YQL via Yahoo Console which is a web-based handy tool to experiment with YQL queries.

  • Navigate to Yahoo Console (You need an Yahoo account)
  • Click "show community tables" on the right of the page.
  • Type the following code in the "Your YQL statement" box.

view plaincopy to clipboardprint?

  1. select title, author, price   
  2. from intuit.quickbase  
  3. where qid='xxxxxxxx'  
  4. and ticket='yyyyyyy'   
  5. and author like '%david%'   
  6. and domain='workplace.intuit.com'  
  7. and pages > 500;  

       select title, author, price

       from intuit.quickbase

       where qid='xxxxxxxx'

       and ticket='yyyyyyy'

       and author like '%david%'

       and domain='workplace.intuit.com'

       and pages > 500;

Click here to see what is returned after execution.

Example: Query QuickBase Structure

Beside the "intuit.quickbase" table, we are also supporting query to an "intuit.quickbase.info" table, with which you can obtain detailed field definition of a given QuickBase table.

For example, with the following YQL statement for the same "book" table we used:

view plaincopy to clipboardprint?

  1. select * from intuit.quickbase.info   
  2. where qid='xxxxxxxx'  
  3. and domain='workplace.intuit.com'  
  4. and ticket='yyyyyyy';  

       select * from intuit.quickbase.info

       where qid='xxxxxxxx'

       and domain='workplace.intuit.com'

       and ticket='yyyyyyy';

Click here to see what is returned.

Example: Insert, Update, and Delete

Things get a little tricky when we want to handle INSERT and UPDATE. Since we want to pass the needed QuickBase parameters (such as table id and ticket) together with the table field names, there is potentially a name collision. The way that I decided to go is to prefix the table fields in these statement with a constant string field. For example, to insert a book record in our example book table, you need to construct a statement similar to the following:

view plaincopy to clipboardprint?

  1. INSERT INTO intuit.quickbase (qid, ticket, field.title, field.author, field.price, field.pages)   
  2. VALUES ('xxxxx', 'yyyyyy', 'Advanced Rails Recipes', 'Mike Clark', 25.71, 464);  

    INSERT INTO intuit.quickbase (qid, ticket, field.title, field.author, field.price, field.pages)

    VALUES ('xxxxx', 'yyyyyy', 'Advanced Rails Recipes', 'Mike Clark', 25.71, 464);

Here the qid and ticket keys are used to identify the specific QuickBase table and perform authentication, and all the keys prefixed with field are taken as table field identifiers. There is no constraint on how these keys are ordered in your statement, as long as there is a one-to-one mapping between the keys and their values.

An UPDATE statement can be constructed similarly:

view plaincopy to clipboardprint?

  1. UPDATE intuit.quickbase SET field.title='Some New Title', field.pages=1000   
  2. WHERE qid='xxxxx' AND ticket='yyyyy' AND rid = 2;  

   UPDATE intuit.quickbase SET field.title='Some New Title', field.pages=1000

   WHERE qid='xxxxx' AND ticket='yyyyy' AND rid = 2;

In the above UPDATE statement, the only thing worth notice is the newly introduced rid key. This is essentially the record id used in every QuickBase table.

Finally, an example of the DELETE statement:

view plaincopy to clipboardprint?

  1. DELETE FROM intuit.quickbase WHERE qid='xxxxx' AND ticket='yyyyy'   
  2. AND rid IN (10, 11, 12, 13, 14, 15, 16);  

   DELETE FROM intuit.quickbase WHERE qid='xxxxx' AND ticket='yyyyy'

   AND rid IN (10, 11, 12, 13, 14, 15, 16);

For now, other than using the rid key, there is no support for using other fields in the WHERE filter for the UPDATE and DELETE statements. This is mainly because that YQL currently does not support local filtering for these two statements. To get around of this, you can construct a compound call leveraging a SELECT statement, for example:

view plaincopy to clipboardprint?

  1. DELETE FROM intuit.quickbase WHERE qid='xxxxx' AND ticket='yyyyy'   
  2. AND rid IN (SELECT rid FROM intuit.quickbase WHERE pages > 200);  

   DELETE FROM intuit.quickbase WHERE qid='xxxxx' AND ticket='yyyyy'

   AND rid IN (SELECT rid FROM intuit.quickbase WHERE pages > 200);

There is a catch, though, to make the above statement work. By default, QuickBase does not return the so called "Record ID#" field which results no rid value be returned from the SELECT statement. In order to get around this, you can use the QuickBase GUI: go to customize/tables, and configure the Record ID# field as shown below:

IMG

Open Data Tables and Input Parameters

With the use statement, you can refer to the YQL open data tables for intuit.quickbase and intuit.quickbase.info respectively from:

    http://www.datatables.org/quickbase/intuit.quickbase.xml

    http://www.datatables.org/quickbase/intuit.quickbase.info.xml

They are automatically included if you are using the YQL community table env located at http://datatables.org/alltables.env

As you have seen, you can conveniently use field names in constructing the 'WHERE' clause in your YQL SELECT statement. As discussed in the previous section, you need prefix a QuickBase field name with field (such as field.title) in the INSERT and UPDATE statements.

  • ticket: required, your QuickBase ticket.
  • qid: required, your QuickBase table id.
  • domain: optional, can be 'workplace.intuit.com' or 'www.quickbase.com', and it defaults to the latter.
  • apptoken: optional, your QuickBase app token, whether you need it depends on the app that the table belongs to.

Ruby Example

Here is a very simple ruby snippet to show how to embed what I have covered above in your code (Of course, you need replace the ticket, qid, and the YQL statement string with your own):

view plaincopy to clipboardprint?

  1. require 'net/http'  
  2. require 'rexml/document'  
  3.   
  4. def qb_yql(query)  
  5.   yql_url = 'http://query.yahooapis.com/v1/public/yql'  
  6.   query   = "use 'http://www.datatables.org/quickbase/intuit.quickbase.xml' as intuit.quickbase;" + query + " and ticket='YOUR_TICKET';"  
  7.   url     = "#{yql_url}?q=#{URI.escape(query)}"  
  8.   # call pretty formatting (there is a bug at c:/ruby/lib/ruby/1.8/rexml/document.rb:186)  
  9.   doc = REXML::Document.new(Net::HTTP.get_response(URI.parse(url)).body)  
  10.   doc.write($stdout,2)  
  11. end  
  12.   
  13. q = "select user_name, notes, start from intuit.quickbase \  
  14.     where qid='YOUR_QB_ID'  \  
  15.     and notes like '%good%'"  
  16.   
  17. qb_yql(q)  

require 'net/http'

require 'rexml/document'

 

def qb_yql(query)

  yql_url = 'http://query.yahooapis.com/v1/public/yql'

  query   = "use 'http://www.datatables.org/quickbase/intuit.quickbase.xml' as intuit.quickbase;" + query + " and ticket='YOUR_TICKET';"

  url     = "#{yql_url}?q=#{URI.escape(query)}"

  # call pretty formatting (there is a bug at c:/ruby/lib/ruby/1.8/rexml/document.rb:186)

  doc = REXML::Document.new(Net::HTTP.get_response(URI.parse(url)).body)

  doc.write($stdout,2)

end

 

q = "select user_name, notes, start from intuit.quickbase \

    where qid='YOUR_QB_ID'  \

    and notes like '%good%'"

 

qb_yql(q)

 

 

 

 

 

 

 The Yahoo! Query Language is an expressive SQL-like language. With YQL, apps run faster with fewer lines of code and a smaller network footprint. One can now leverage YQL to query, filter, sort, and unify records from any QuickBase table.

http://intuitlabs.com/apps/yql-and-intuit-quickbase

What is YQL?

The Yahoo! Query Language is an expressive SQL-like language that lets you query, filter, and join data across Web services. With YQL, apps run faster with fewer lines of code and a smaller network footprint.

Yahoo! and other websites across the Internet make much of their structured data available to developers, primarily through Web services. To access and query these services, developers traditionally endure the pain of locating the right URLs and documentation to access and query each Web service.

With YQL, developers can access and shape data across the Internet through one simple language, eliminating the need to learn how to call different APIs.

It has generated a lot of excitement because of its potential to turn various web services (recently estimated to be around 5000) to your own databases, literally.

Making QuickBase YQL-ized

We have gone ahead and made QuickBase YQL-friendly. It works for both quickbase.com and workplace.intuit.com domains. Instead of learning proprietary query syntax, one can now leverage YQL to query, filter, sort, and uniquify records from any QuickBase table.

With the recently added writing capability in YQL, you can now also insert, update and delete records in a QuickBase table.

Example: Query QuickBase Data

Let's assume that we have a QuickBase table about books, with the following fields: title, author, year, price, and pages. It looks like following when I do "List All" in QuickBase GUI.

IMG

You can access the QuickBase used in this demo here - https://workplace.intuit.com/db/befpcwgem

Before we begin, you need some basic things:

  • Your QuickBase ID: Let's assume it is 'xxxxxxx'.
  • QuickBase Ticket: Let's assume it is 'yyyyyy' (Depending on the app the table belongs to, you may also need a QuickBase App Token.)

Say when we want to find out info on title, author and price for the books with more than 500 pages and an author named "david". Let's do this using YQL via Yahoo Console which is a web-based handy tool to experiment with YQL queries.

  • Navigate to Yahoo Console (You need an Yahoo account)
  • Click "show community tables" on the right of the page.
  • Type the following code in the "Your YQL statement" box.

view plaincopy to clipboardprint?

  1. select title, author, price   
  2. from intuit.quickbase  
  3. where qid='xxxxxxxx'  
  4. and ticket='yyyyyyy'   
  5. and author like '%david%'   
  6. and domain='workplace.intuit.com'  
  7. and pages > 500;  

       select title, author, price

       from intuit.quickbase

       where qid='xxxxxxxx'

       and ticket='yyyyyyy'

       and author like '%david%'

       and domain='workplace.intuit.com'

       and pages > 500;

Click here to see what is returned after execution.

Example: Query QuickBase Structure

Beside the "intuit.quickbase" table, we are also supporting query to an "intuit.quickbase.info" table, with which you can obtain detailed field definition of a given QuickBase table.

For example, with the following YQL statement for the same "book" table we used:

view plaincopy to clipboardprint?

  1. select * from intuit.quickbase.info   
  2. where qid='xxxxxxxx'  
  3. and domain='workplace.intuit.com'  
  4. and ticket='yyyyyyy';  

       select * from intuit.quickbase.info

       where qid='xxxxxxxx'

       and domain='workplace.intuit.com'

       and ticket='yyyyyyy';

Click here to see what is returned.

Example: Insert, Update, and Delete

Things get a little tricky when we want to handle INSERT and UPDATE. Since we want to pass the needed QuickBase parameters (such as table id and ticket) together with the table field names, there is potentially a name collision. The way that I decided to go is to prefix the table fields in these statement with a constant string field. For example, to insert a book record in our example book table, you need to construct a statement similar to the following:

view plaincopy to clipboardprint?

  1. INSERT INTO intuit.quickbase (qid, ticket, field.title, field.author, field.price, field.pages)   
  2. VALUES ('xxxxx', 'yyyyyy', 'Advanced Rails Recipes', 'Mike Clark', 25.71, 464);  

    INSERT INTO intuit.quickbase (qid, ticket, field.title, field.author, field.price, field.pages)

    VALUES ('xxxxx', 'yyyyyy', 'Advanced Rails Recipes', 'Mike Clark', 25.71, 464);

Here the qid and ticket keys are used to identify the specific QuickBase table and perform authentication, and all the keys prefixed with field are taken as table field identifiers. There is no constraint on how these keys are ordered in your statement, as long as there is a one-to-one mapping between the keys and their values.

An UPDATE statement can be constructed similarly:

view plaincopy to clipboardprint?

  1. UPDATE intuit.quickbase SET field.title='Some New Title', field.pages=1000   
  2. WHERE qid='xxxxx' AND ticket='yyyyy' AND rid = 2;  

   UPDATE intuit.quickbase SET field.title='Some New Title', field.pages=1000

   WHERE qid='xxxxx' AND ticket='yyyyy' AND rid = 2;

In the above UPDATE statement, the only thing worth notice is the newly introduced rid key. This is essentially the record id used in every QuickBase table.

Finally, an example of the DELETE statement:

view plaincopy to clipboardprint?

  1. DELETE FROM intuit.quickbase WHERE qid='xxxxx' AND ticket='yyyyy'   
  2. AND rid IN (10, 11, 12, 13, 14, 15, 16);  

   DELETE FROM intuit.quickbase WHERE qid='xxxxx' AND ticket='yyyyy'

   AND rid IN (10, 11, 12, 13, 14, 15, 16);

For now, other than using the rid key, there is no support for using other fields in the WHERE filter for the UPDATE and DELETE statements. This is mainly because that YQL currently does not support local filtering for these two statements. To get around of this, you can construct a compound call leveraging a SELECT statement, for example:

view plaincopy to clipboardprint?

  1. DELETE FROM intuit.quickbase WHERE qid='xxxxx' AND ticket='yyyyy'   
  2. AND rid IN (SELECT rid FROM intuit.quickbase WHERE pages > 200);  

   DELETE FROM intuit.quickbase WHERE qid='xxxxx' AND ticket='yyyyy'

   AND rid IN (SELECT rid FROM intuit.quickbase WHERE pages > 200);

There is a catch, though, to make the above statement work. By default, QuickBase does not return the so called "Record ID#" field which results no rid value be returned from the SELECT statement. In order to get around this, you can use the QuickBase GUI: go to customize/tables, and configure the Record ID# field as shown below:

Open Data Tables and Input Parameters

With the use statement, you can refer to the YQL open data tables for intuit.quickbase and intuit.quickbase.info respectively from:

    http://www.datatables.org/quickbase/intuit.quickbase.xml

    http://www.datatables.org/quickbase/intuit.quickbase.info.xml

They are automatically included if you are using the YQL community table env located at http://datatables.org/alltables.env

As you have seen, you can conveniently use field names in constructing the 'WHERE' clause in your YQL SELECT statement. As discussed in the previous section, you need prefix a QuickBase field name with field (such as field.title) in the INSERT and UPDATE statements.

  • ticket: required, your QuickBase ticket.
  • qid: required, your QuickBase table id.
  • domain: optional, can be 'workplace.intuit.com' or 'www.quickbase.com', and it defaults to the latter.
  • apptoken: optional, your QuickBase app token, whether you need it depends on the app that the table belongs to.

Ruby Example

Here is a very simple ruby snippet to show how to embed what I have covered above in your code (Of course, you need replace the ticket, qid, and the YQL statement string with your own):

view plaincopy to clipboardprint?

  1. require 'net/http'  
  2. require 'rexml/document'  
  3.   
  4. def qb_yql(query)  
  5.   yql_url = 'http://query.yahooapis.com/v1/public/yql'  
  6.   query   = "use 'http://www.datatables.org/quickbase/intuit.quickbase.xml' as intuit.quickbase;" + query + " and ticket='YOUR_TICKET';"  
  7.   url     = "#{yql_url}?q=#{URI.escape(query)}"  
  8.   # call pretty formatting (there is a bug at c:/ruby/lib/ruby/1.8/rexml/document.rb:186)  
  9.   doc = REXML::Document.new(Net::HTTP.get_response(URI.parse(url)).body)  
  10.   doc.write($stdout,2)  
  11. end  
  12.   
  13. q = "select user_name, notes, start from intuit.quickbase \  
  14.     where qid='YOUR_QB_ID'  \  
  15.     and notes like '%good%'"  
  16.   
  17. qb_yql(q)  

require 'net/http'

require 'rexml/document'

 

def qb_yql(query)

  yql_url = 'http://query.yahooapis.com/v1/public/yql'

  query   = "use 'http://www.datatables.org/quickbase/intuit.quickbase.xml' as intuit.quickbase;" + query + " and ticket='YOUR_TICKET';"

  url     = "#{yql_url}?q=#{URI.escape(query)}"

  # call pretty formatting (there is a bug at c:/ruby/lib/ruby/1.8/rexml/document.rb:186)

  doc = REXML::Document.new(Net::HTTP.get_response(URI.parse(url)).body)

  doc.write($stdout,2)

end

 

q = "select user_name, notes, start from intuit.quickbase \

    where qid='YOUR_QB_ID'  \

    and notes like '%good%'"

 

qb_yql(q)

Reply

Type the characters you see in this picture. (verify using audio)
Type the characters you see in the picture above; if you can't read them, submit the form and a new image will be generated. Not case sensitive.

© 1997-2014 Intuit Inc. All Rights Reserved.

Online Database VeriSign Secured Web Based Software TRUSTe Certification Online Database SSAE Audit