Continuing from previous article, Managing External Database Connections, this article describes the process of Managing Database Queries. 

What Is a Database Query?

In Enterprise Forms a Database Query is basically a way of providing data interactions between forms and database objects (tables, stored procedures and raw sql) as defined through supported external database connections.  Once defined, they are used in forms as specialized tokens with its own namespace.  For example, say we have a database table called “Users” and wish to retrieve data from a column called “username” we could define the Database Query such that when used it is accessed as simple token  “[USERS:username]”. 

A Database Query is specific to each form template, thus a database query created in one form template cannot be accessed from another form template.  A form template, however, can have as many database queries as required provided the namespaces defined in each query is unique. 

How to create a Database Query

Unlike creating Database Connections, creating Database Query does not require Host or Super Users access to the system.  As long as the user has Form Creator access to the form template, the user is able to create database queries. 

Using the above example we wish to create a database query that accesses data from “Users” table located the Current DNN portal database.  This also assumes we have already created a Database Connection for the Current Database.

• In the Form Template Details, click on Database Queries icon from the top navigation bar to open the Edit Database Query view in Add New mode
• Select a Database Connection from list of available External Database Connections.  Refer to here for more information on how to define External Database Connections
• Provide a Token Namespace that is unique to this database query.  A token namespace is a way of defining the database query as well as how it could be access when used.  Thus it is possible provide any text (Alphanumeric only) in this field property, the Token Namespace have to be a unique name for the form template.  Thus you cannot define two queries with “USERS” as the Token Namespace.  Any violation if this rule results in an error message being generated.   In our particular example enter “USERS” as the Token Namespace property.
• Select a SQL Type from the list of available types available.  Currently there are 3 available supported SQL Types; Table, Stored Procedure and Raw SQL.  Depending on the selected Database Connection and security access granted, Stored Procedure and Raw SQL may not be available.  In our particular example, select “Table” as the SQL Type.
• Enter the SQL Text associate with the selected SQL Type.   The SQL Text depends on the selected SQL Type.  If the selected SQL Type is
  • Table:  SQL Text must contain the name of a database table (or view) available in the database.  E.g “Users”
  • Stored Procedure:  SQL Text must contain the name of the Stored Procedure and any associated parameters of the stored procedure separated by space.  E.g.  “myStoredProcedure ‘param1’, ‘param2’, ‘[QUERY:tabid]’”
  • Raw SQL:  SQL Text can contain any valid SQL syntax supported by the database.
• NOTE:  An important note is that SQL Text supports Dynamic Tokens within the text.  For example you want to pass in the current userid to a stored procedure called “myStoredProcedure”, the SQL Text would be something along this line “myStoredProcedure ‘[USER:USERID]’”  (without the outer double quotes).  When executed, the system will translate the userid token to the id of the current logged user.  This provides a very powerful way dynamically access data.
• To Preview data from the provided Query, click on Preview Sample Data link. 
• Click on Update to apply changes.  Note that before changes could be updated, the Query is literally validated for errors, thus you will need to ensure that the database must be online and database query are valid before the system will create the Database Query definition.
• Once the Database Query has been created is can be used in anywhere in the form that support dynamic tokens.

How to use Database Query tokens

The real advantage of Database Query is how easily it can be accessed and used in forms.  Like any other supported tokens, Database Query is accessed via the standard token format, tokenspace and property name such as [NAMESPACE:PropertyName].  “NAMESPACE” is the Token Namespace defined when creating the Database Query, and “PropertyName” is the column name from the return dataset of the query.  In our example to access the “username” column we simply call the query as “[USERS:username]”.
NOTE:  By default the token accesses only the very first row of the returned dataset.  Since version 4.1 it is possible to access data other than the first row using a more advanced syntax. 

[NAMESPACE:XXX|YYY]
Where XXX is the row index of the returned dataset starting from 1
and YYY is the columnname or column index of the returned datset.

For example

[USERS:2|username]  accesses the username column from the second data data row
[USERS:1|username] is equivalent to [USERS:username]