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

Summary
Myna. QuerySql 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...)
Functions
Myna. QueryConstruct a Query object Parameters: optionsOrResultSet - Either a java.sql.ResultSet object, or a Options Object that contains optional properties (see below), or null or undefined to create an empty query object Options: sql - SQL string dataSource - string datasource name defined in Myna Administrator deferExec - Optional default false Normally, new query objects are executed immediately.
Properties
batchMaxnumber of batch queries to accept before calling <executeBatch>
curBatchCountnumber of batch queries currently pending on this query object
columnsAn Array of Objects that represent the columns in the result set.
dateFormatif set to a string format (see <Date.format>), then any date or timestamp values will be converted to formatted strings.
dataAn Array of Objects That represents the query data.
dataSourceThe name of the datasource used to run the query.
dsalternate name for Myna.Query.dataSource
sqlThe SQL String of the query
executionTimeElapsed time in milliseconds to run the query
generatedKeyFor insert queries, contains the generated primary key, if any
updateCountNumber of rows affected by the last update
parametersMyna.QueryParams object representing query parameters
maxRowsMaximum number of rows to return.
startRowRow number in result from which to start returning rows.
pageSizeNumber of rows to return per page.Null means all rows.
pagePage to return.
totalRowstotal rows in the query, if the entire result had been returned.
resultcontains just data result of this query.
logshould executions of this query be logged? 
dbthe Myna.Database object associated with this query Detail: this property is only set when this query was initialized with a datasource
Functions
beautifySqlreturns this sql string formated for readability
formatSqlreturns a multiline text formated string of this.sql, beautified with any values merged in
getRowByColumnreturns first row of the result set that matches the column and value supplied Parameters: columnName - String name of the column to search values - value to match start - Optional default 0 index to start the search from Returns: first row of the result set that matches the column and value supplied
parseResultSetparses a JDBC resultSet object and populates data Parameters: jdbcResultSet - JDBC resultSet Returns: this
valueArrayreturns an array of values of a column in the result set.
defaultRowHandlerdefault rowHandler if not overridden Parameters: row - instance of Myna.QueryResultRow Detail: The default rowHandler function returns each row of the resultset as a JavaScript Object by calling Myna.QueryResultRow.getRow
rowHandlerhandles each row in the query result set Parameters: row - instance of Myna.QueryResultRow Detail: This function takes row information and returns an object to be appended to Myna.Query.data.
executeExecutes an SQL query and updates and returns this Query object.
addBatchadds DML query to batch Parameters: options - Object of optional parameters.
Myna. QueryResultRowObject 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
Functions
QueryResultRowConstructs QueryResultRow object Parameters: rs - a java.sql.ResultSet object, initialized to a valid row.
Properties
querya reference to the calling query
rsa reference to the calling query’s java.sql.ResultSet object
columnsa reference to the calling query’s columns property
rowNumthe current row number, starting with 1
Functions
getValuereturns the value of the indicated column of the current row Parameters: indexOrName - The 0 based index of the column, or the column name
getRowreturns all the values of the current row as a JavaScript object
Myna. QueryParamsObject that contains query parameter information Detail: This object can be constructed from an array or via the Myna.QueryParams.addValue function.
Functions
QueryParamsConstructs QueryParams object Parameters: config - Optional default null Array of parameter objects in the form of [{value:’value’,type:’type’,isNull:false}] See: Myna.Query
addValueadds an sql parameter value to to QueryParams and returns a placeholder.

Functions

Myna. Query

Myna.Query = function (optionsOrResultSet)

Construct a Query object Parameters: optionsOrResultSet - Either a java.sql.ResultSet object, or a Options Object that contains optional properties (see below), or null or undefined to create an empty query object Options: sql - SQL string dataSource - string datasource name defined in Myna Administrator deferExec - Optional default false Normally, new query objects are executed immediately.  Setting this to true cancels that behavior.  The query will not be executed without an explicit call to execute parameters - Optional Default null Myna.QueryParams maxRows - Optional Default null Maximum number of rows to return. null for all rows rowHandler - Optional Default defaultRowHandler function to process each row in the resultset.  This function is passed an instance of Myna.QueryResultRow and what is returned will be pushed onto Myna.Query.data startRow - Optional Default 1 Row number in result from which to start returning rows.  Starts with 1.  Used with maxRows pageSize - Optional Default null Number of rows to return per page.  Null means all rows.  Used with page page - Optional Default null Page to return.  Starts with 1.  This is used with pageSize as a shortcut for startRow and maxRows. cache - Optional, default null If defined, this is an options object describing how this query should be cached.  All options of Myna.Cache are supported except the the “code” parameter which is generated by the query.

Properties

batchMax

number of batch queries to accept before calling <executeBatch>

curBatchCount

number of batch queries currently pending on this query object

columns

An Array of Objects that represent the columns in the result set.  Format:

[
{
name:<String column name>,
typeId:<numeric JDBC type id>,
typeName:<String type name>
},
...
]

... created by parseResultSet

dateFormat

if set to a string format (see <Date.format>), then any date or timestamp values will be converted to formatted strings.  If set to null (default) these values will be Date objects.  Note that this will not change the type for date/time columns in Query.columns or Query.result.columns

data

An Array of Objects That represents the query data.  Format:

[
{
<column name>:<column value>,
...
},
...
]

... created by parseResultSet

dataSource

The name of the datasource used to run the query.  Datasources are configured in the Myna Administrator, located at webroot/myna/administrator/index.sjs

ds

alternate name for Myna.Query.dataSource

sql

The SQL String of the query

executionTime

Elapsed time in milliseconds to run the query

generatedKey

For insert queries, contains the generated primary key, if any

updateCount

Number of rows affected by the last update

parameters

Myna.QueryParams object representing query parameters

maxRows

Maximum number of rows to return. null for all rows

startRow

Row number in result from which to start returning rows.  Starts with 1.  Used with maxRows

pageSize

Number of rows to return per page.Null means all rows.  Used with page

page

Page to return.  Starts with 1.  This is used with pageSize as a shortcut for startRow and maxRows. startRow is set to (((page - 1) * pageSize)+1) and max rows is set to pageSize

totalRows

total rows in the query, if the entire result had been returned.  The actual number of rows is data.length

result

contains just data result of this query.

Detail

this property is an object that looks like this:

{
data:[{colname:value}],
totalRows:numeric,
maxRows:numeric,
startRow:numeric,
columns:[{name:string,type:string}]
}

the “type” in columns is the result from Myna.Database.dbTypeToJs for the database column type.  This property is more appropriate for sending to a client as it is smaller and does not contain any internal information about your database tables

log

should executions of this query be logged?  If this is set to true, then every execution of this query will be logged as a “query” type to the logging database.  Not recommended for production systems

db

the Myna.Database object associated with this query Detail: this property is only set when this query was initialized with a datasource

Functions

beautifySql

beautifySql:function (sql)

returns this sql string formated for readability

See Also

formatSql

formatSql:function ()

returns a multiline text formated string of this.sql, beautified with any values merged in

See Also

getRowByColumn

getRowByColumn:function(columnName,
value,
start)

returns first row of the result set that matches the column and value supplied Parameters: columnName - String name of the column to search values - value to match start - Optional default 0 index to start the search from Returns: first row of the result set that matches the column and value supplied

parseResultSet

parseResultSet:function(jdbcResultSet,
ignoreOffset)

parses a JDBC resultSet object and populates data Parameters: jdbcResultSet - JDBC resultSet Returns: this

valueArray

valueArray:function(columnName)

returns an array of values of a column in the result set.  Parameters: columnName - String Column name to return

defaultRowHandler

defaultRowHandler:function(row)

default rowHandler if not overridden Parameters: row - instance of Myna.QueryResultRow Detail: The default rowHandler function returns each row of the resultset as a JavaScript Object by calling Myna.QueryResultRow.getRow

rowHandler

rowHandler:function(row)

handles each row in the query result set Parameters: row - instance of Myna.QueryResultRow Detail: This function takes row information and returns an object to be appended to Myna.Query.data.  This can be overridden in the query parameters

execute

execute:function(options)

Executes an SQL query and updates and returns this Query object.  Parameters: options - Object of optional parameters.  See Myna.Query.  Detail: execute executes this query object, optionally updating its properties.  Returns: this Example:

var qry = new Query({
dataSource:"mythtv",
sql:<ejs>
select *
from channels
</ejs>,
startRow:1,
maxRows:10
})
qry.execute({startRow:11})

addBatch

addBatch:function(options)

adds DML query to batch Parameters: options - Object of optional parameters.  See Myna.Query.  Detail: registers this query to be run latter in a batch Returns: this Allows the batching of update or insert queries to be sent at once to the DB for processing.  This should only be done with deferred queries.  Call <executeBatch> to submit the batch for processing.  If the number of batches added equals batchMax <executeBatch> is automatically called on this query Example:

var insertQry =new Myna.Query({
ds:$FP.defaultDs,
sql:<ejs>
insert into positions(
id,
qtr_year,
position_number,
position_name
)
values(
{id:varchar},
{qtr_year:varchar},
{position_number:bigint},
{position_name:varchar}
)
</ejs>,
batchMax:1000,//this is the default
deferExec:true//this is important!
})
insertQry.addBatch({
values:{
id:1,
qtr_year:"2012:2",
position_number:10,
position_name:"Thing Do-er I"
}
})
insertQry.addBatch({
values:{
id:2,
qtr_year:"2012:2",
position_number:11,
position_name:"Thing Do-er II"
}
})
insertQry.executeBatch(); // runs queries here

Myna. QueryResultRow

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

Summary
Functions
QueryResultRowConstructs QueryResultRow object Parameters: rs - a java.sql.ResultSet object, initialized to a valid row.
Properties
querya reference to the calling query
rsa reference to the calling query’s java.sql.ResultSet object
columnsa reference to the calling query’s columns property
rowNumthe current row number, starting with 1
Functions
getValuereturns the value of the indicated column of the current row Parameters: indexOrName - The 0 based index of the column, or the column name
getRowreturns all the values of the current row as a JavaScript object

Functions

QueryResultRow

Myna.QueryResultRow=function(rs,
query)

Constructs QueryResultRow object Parameters: rs - a java.sql.ResultSet object, initialized to a valid row. query - the Myna.Query object that created the row.  This object is expects to be initialized with the query metadata prior to constructing this object

Properties

query

a reference to the calling query

rs

a reference to the calling query’s java.sql.ResultSet object

columns

a reference to the calling query’s columns property

rowNum

the current row number, starting with 1

Functions

getValue

getValue:function(indexOrName)

returns the value of the indicated column of the current row Parameters: indexOrName - The 0 based index of the column, or the column name

getRow

getRow:function()

returns all the values of the current row as a JavaScript object

Myna. QueryParams

Object that contains query parameter information Detail: This object can be constructed from an array or via the Myna.QueryParams.addValue function.  It represents an array of explicitly typed values to use in a query.

Summary
Functions
QueryParamsConstructs QueryParams object Parameters: config - Optional default null Array of parameter objects in the form of [{value:’value’,type:’type’,isNull:false}] See: Myna.Query
addValueadds an sql parameter value to to QueryParams and returns a placeholder.

Functions

QueryParams

Myna.QueryParams=function(config)

Constructs QueryParams object Parameters: config - Optional default null Array of parameter objects in the form of [{value:’value’,type:’type’,isNull:false}] See: Myna.Query

addValue

addValue:function(value,
type,
isNull)

adds an sql parameter value to to QueryParams and returns a placeholder.  Parameters: value - value to store type - Optional default ‘VARCHAR’ String or numeric JDBC type.  See java.sql.types for the names isNull - Optional default false If true, the value is ignored and an SQL null of the supplied type will be used instead Returns: Placeholder value for a prepared statement: “?”

Myna.Query = function (optionsOrResultSet)
Construct a Query object Parameters: optionsOrResultSet - Either a java.sql.ResultSet object, or a Options Object that contains optional properties (see below), or null or undefined to create an empty query object Options: sql - SQL string dataSource - string datasource name defined in Myna Administrator deferExec - Optional default false Normally, new query objects are executed immediately.
The name of the datasource used to run the query.
Object that contains query parameter information Detail: This object can be constructed from an array or via the Myna.QueryParams.addValue function.
Provides database metadata and manages Myna.Table objects
beautifySql:function (sql)
returns this sql string formated for readability
formatSql:function ()
returns a multiline text formated string of this.sql, beautified with any values merged in
getRowByColumn:function(columnName,
value,
start)
returns first row of the result set that matches the column and value supplied Parameters: columnName - String name of the column to search values - value to match start - Optional default 0 index to start the search from Returns: first row of the result set that matches the column and value supplied
parseResultSet:function(jdbcResultSet,
ignoreOffset)
parses a JDBC resultSet object and populates data Parameters: jdbcResultSet - JDBC resultSet Returns: this
An Array of Objects That represents the query data.
valueArray:function(columnName)
returns an array of values of a column in the result set.
defaultRowHandler:function(row)
default rowHandler if not overridden Parameters: row - instance of Myna.QueryResultRow Detail: The default rowHandler function returns each row of the resultset as a JavaScript Object by calling Myna.QueryResultRow.getRow
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
getRow:function()
returns all the values of the current row as a JavaScript object
rowHandler:function(row)
handles each row in the query result set Parameters: row - instance of Myna.QueryResultRow Detail: This function takes row information and returns an object to be appended to Myna.Query.data.
execute:function(options)
Executes an SQL query and updates and returns this Query object.
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...)
addBatch:function(options)
adds DML query to batch Parameters: options - Object of optional parameters.
Myna.QueryResultRow=function(rs,
query)
Constructs QueryResultRow object Parameters: rs - a java.sql.ResultSet object, initialized to a valid row.
getValue:function(indexOrName)
returns the value of the indicated column of the current row Parameters: indexOrName - The 0 based index of the column, or the column name
addValue:function(value,
type,
isNull)
adds an sql parameter value to to QueryParams and returns a placeholder.
Myna.QueryParams=function(config)
Constructs QueryParams object Parameters: config - Optional default null Array of parameter objects in the form of [{value:’value’,type:’type’,isNull:false}] See: Myna.Query
An Array of Objects that represent the columns in the result set.
total rows in the query, if the entire result had been returned.
Number of rows to return per page.Null means all rows.
Page to return.
Number of rows affected by the last update
Creates and manages cached objects
contains just data result of this query.
Myna.Database.dbTypeToJs = function(sourceType)
Static function that takes a column type name (“VARCHAR”) or a column type id (-5) and returns string that represents the equivalent Myna type (string, numeric, date, binary, unsupported)
number of batch queries to accept before calling executeBatch