Myna. Query

Sql query object

Overview

The Query object can be used for queries that return a result set (select statements), as well as those that do not (create, update, insert, delete etc...).

Performing a simple query

var qry = new Myna.Query({
    dataSource:"hr_ds",
    sql:"select last_name,first_name from employees"
});

This creates a Query object and, because both dataSource and sql were provided, immediately executes the SQL.  When a SQL statement is executed, several related properties are populated on the query object.  Of particular interest for this query is Myna.Query.data.  This property contains an array of objects that represent the returned rows.  Each of these objects has a property for every column in the resultset and its corresponding value.  Another useful property is Myna.Query.columns which is an array of column information objects in the order of appearance in the resultset.

Here is a quick example of displaying a Query in a table in an EJS file

<%
var qry = new Myna.Query({
    dataSource:"hr_ds",
    sql:"select * from employees",
    startRow:10,
    maxRows:10
});
%>
<table border="1" >
    <caption> Records <%=qry.startRow%> - <%=qry.maxRows%> of <%=qry.totalRows%></caption>
    <tr>
        <@loop array='qry.columns' element='column'>
            <th><%=column.name%></th>
        </@loop>
    </tr>
    <@loop array='qry.data' element='row'>
        <tr>
            <@loop array='qry.columns' element='column'>
                <th><%=String(row[column.name.toLowerCase()])%></th>
            </@loop>
        </tr>
    </@loop>
</table>

Notice the extra parameters: startRow and maxRows.  These define a “window” in the result set to return into the data property. startRow starts at 1.  Myna.Query.totalRows contains the total number of rows that would have been returned if it was not limited.  Have a look at Myna.Query.pageSize and Myna.Query.page for an alternate way setting the result window.

Inserting or updating records

Myna.Query is useful for updates and inserts as well.  When inserting data the Myna.Query.updateCount property is updated with the number of rows affected.  It is also possible to use parametrized values in your queries.  For example, dates are handled in highly variable ways between different databases.  It is also dangerous to directly include client data into SQL queries because of the the possibility of injecting sql code.  This is where Myna.QueryParams comes in.  This class represents an array of explicitly typed parameters to your query.  This array matches the order of ‘?’ symbols in your query.  Here is an example:

var sql ='insert into employees(first_name,last_name,age,hire_date) values (?,?,?,?)'
var params = new Myna.QueryParams([
    {
        value:"Bob"
    },{
        value:"Dobb"
    },{
        value:32,
        type:"INT4"
    },{
        value:Date.parseDate("01/01/1970","m/d/Y"),
        type:"DATE"
    }
])

var qry = new Myna.Query({
    dataSource:"hr_ds",
    sql:sql,
    parameters:params
})

Here is more compact and maintainable version of the same query using embedded JavaScript blocks:

var p = new Myna.QueryParams();
var qry = new Myna.Query({
    dataSource:"hr_ds",
    sql:<ejs>
        insert into employees(first_name,last_name,age,hire_date) values (
        <%=p.addValue("Bob")%>,
        <%=p.addValue("Dobb")%>,
        <%=p.addValue(32,"INT4")%>,
        <%=p.addValue(Date.parseDate("01/01/1970","m/d/Y"),"DATE")%>
        )
    </ejs>,
    parameters:p
})

Because Myna.QueryParams.addValue returns a ‘?’ symbol, your parameters will always be aligned to the position of the ‘?’ symbols

What if you want to the run the above query again, but with different parameters?

var p = new Myna.QueryParams();
p.addValue("Joe");
p.addValue("Schmoe");
p.addValue(72,"INT4");
p.addValue(Date.parseDate("10/15/1995","m/d/Y"),"DATE");

var qry.execute({
    parameters:p
})

Myna.Query.execute executes a query again, optionally overriding its properties

Caching

Queries can be cached to enhance performance of frequently accessed but infrequently changing data

//cache employee information for 12 hours
var qry=new Myna.Query({
    ds:"hr_ds",
    sql:"select * from employees",
    cache:{
        refreshInterval:Date.getInterval(Date.HOUR,12)
    }
})

See Myna.Cache for more detail on caching.

See also Myna.Query.getRowByColumn and Myna.Query.valueArray for alternate ways of accessing the data in a query object

Object that represents a row in a jdbc result set that can retrieve values and metadata

Detail

This object is normally constructed by <Myna.parseResultSet> to pass to Myna.Query.rowHandler

Object that contains query parameter information

Detail

This object can be contructed from an array or via the Myna.QueryParams.addValue function.  It represents an array of explicitly typed values to use in a query.