DataFaucet ORM

Hello World

What documentation would be complete without the ubiquitous Hello World example? :)

Create a table in your database called "hello_world". In this table add two character columns named "locale" and "hello". We'll use this new table for the following samples.

First you need to get your datasource from the open faucet:

<cfset ds = request.DataFaucet.getDatasource() />

Next we'll add some data to our new table

<cfscript>
  // get the datasource
  ds = request.DataFaucet.getDatasource(); 
  tmp = StructNew(); 
  tmp.locale = "en"; 
  tmp.hello = "Hello!"; 
  ds.insert("hello_world",tmp); 
  
  tmp.locale = "sp"; 
  tmp.hello = "Hola!"; 
  ds.insert("hello_world",tmp); 
  
  tmp.locale = "en_us_tx"; 
  tmp.hello = "Howdy!"; 
  ds.insert("hello_world",tmp); 
</cfscript>

After executing this code you should have three new records in your hello_world table. Now you want to get that data back out. Hold on to your lunch, here we go!

<cfdump var="#ds.select('*','hello_world')#" />

Now you should have a dump that looks like this:

query
  HELLO LOCALE
1 Hello! en
2 Hola! sp
3 Howdy! en_us_tx

There you are, data to say hello in English, Spanish and Texan.

But it's not very useful unless you can filter the data is it? That is, generally speaking, when we're working with web applications, we don't just pour all the data flat out of the database, we select just a small number of records our users want to see. So we need some way to filter the data down to just the handful of records we want.

<cfdump var="#ds.select(table='hello_world',filters=URL)#" />

Yes I know using the URL scope is sloppy. However, after making this change, add this string to the URL of your page "&locale=en". You should see the dump reduced to just the record for English. And if you change the URL string again to "&locale=en_us_tx" then you get the record for Texan.

DataFaucet offers several ways to build SQL queries, this is only the fastest or easiest method. Other methods offer you much greater control over precisely how the query is generated. For example:

<cfscript>
  // get the datasource
  ds = request.DataFaucet.getDatasource(); 
  // get the statement object 
  select = ds.getStatement("select").init("hello_world"); 
  
  // filter by language and geographic region 
  select = ds.filterLocale("locale","en_us_tx"); 
</cfscript>

<!--- get the data --->
<cfdump var="#select.execute()#" />

<!--- display the generated sql --->
<cfoutput>
  <pre>#htmleditformat(select.getSyntax())#</pre>
</cfoutput>

When you execute this code, aside from seeing that you get both English and texan in your query, the generated SQL syntax should look something like this:

SELECT [datafaucet].[dbo].[hello_world].* 
FROM [datafaucet].[dbo].[hello_world] 
WHERE UPPER([datafaucet].[dbo].[hello_world].[locale]) IN ('EN','EN_US','EN_US_TX')

Features like this help to make DataFaucet one of the fastest and easiest ways to build an application.