# Raw SQL

reviewed: 11 April 2025

CaseMaster will hide SQL from developers most of the time but does offer ways of dealing with 'raw' SQL in a number of ways.

Inlineview

We have already seen that we can use inlineView instead of table in a BO descriptor (see here).

This is a useful alternative for creating a view (for example when connecting to a legacy database where you are not able to create views).

sql.execute()

The sql.execute() function allow you to execute SQL (update / delete / insert and DDL) directly.

The sql.execute function returns the number of rows that has been affected. The function takes two parameters:

  • The SQL to execute
  • Optionally the name of the datasource to execute the query against

The datasource must be a RDBMS datasource (i.e. the primary or an alternative datasource) and primary is the default datasource.

Needless to say that this function needs to be executed with GREAT CARE.

sql.getBOForSelectSQL()

There is another way of dealing with raw select SQL and that is to create a BO on-the-fly based on raw SQL.

This is best explained using an example:

    set( 'sql', 'select * from [user]' )    

    set( 'cms', sql.getBOForSelectSQL( [sql] ) )        // 1

    set( 'descriptor', boDesc.parse( [cms] ) )          // 2

    iterate iterator.ofEntity(
        <
            <
                name: 'bo'
                entity: [descriptor]                    // 3
            >
        >
    )
        response.write( bo.attr( [bo], 'id' ) )         // 4
        response.write( '<br>' )
        response.write( bo.attr( [bo], 'name' ) )       
        response.write( '<br>' )
    end-iterate

Step-by-step explanation:

  1. sql.getBOForSelectSQL() generates the .cms code for a descriptor based on SQL
  2. boDesc.parse() will parse a valid .cms file and create a descriptor object
  3. You can use an entity name or a descriptor object for the entity tag
  4. The dynamic BO will have an attribute for each output column of the SQL

<End of document>