Package ortus.boxlang.runtime.bifs.global.jdbc

Class QueryExecute

java.lang.Object
ortus.boxlang.runtime.bifs.BIF
ortus.boxlang.runtime.bifs.global.jdbc.QueryExecute
@BoxBIF public class QueryExecute extends BIF

  • Constructor Details

    • QueryExecute

      public QueryExecute()
      Constructor

  • Method Details

    • _invoke

      public Object _invoke(IBoxContext context, ArgumentsScope arguments)
      Execute an SQL query and returns the results.

      Parameters

      The parameters argument can be used to bind parameters to the SQL query. You can use either an array of binding parameters or a struct of named binding parameters. The SQL must have the parameters bound using the syntax ? for positional parameters or :name for named parameters.

      Example: 

       queryExecute( sql: "SELECT * FROM users WHERE id = ?", params: [ 1 ] );
 queryExecute( sql: "SELECT * FROM users WHERE id = :id", params: { id: 1 } );

      You can also treat each named parameter to not only be a key-value pair, but also a struct with additional options:

      • value:any - The value to bind to the parameter
      • type:string - The type of the value, defaults to "varchar"
      • maxLength:numeric - The maximum length of the value, only applicable for string types
      • scale:numeric - The scale of the value, only applicable for decimal types, defaults to 0
      • null:boolean - Whether the value is null, defaults to false
      • list:boolean - Whether the value is a list, defaults to false
      • separator:string - The separator to use for list values, defaults to ","

      Example: 

       queryExecute( sql: "SELECT * FROM users WHERE id = :id", params: { id: { value: 1, type: "integer" } } );
 queryExecute( sql: "SELECT * FROM users WHERE id IN (:ids)", params: { ids: { value: [ 1, 2, 3 ], type: "integer", list: true, separator: "," } } );

      Options

      The available options for this BIF are:
        invalid input: '<'>cache:boolean - Whether to cache the query results, defaults to false
      • cacheKey:string - Your own cache key, if not specified, the SQL will be used as the cache key
      • cacheTimeout:timespan|seconds - The timeout for the cache, defaults to 0 (no timeout)
      • cacheLastAccessTimeout:timespan|seconds - The timeout for the last access to the cache, defaults to 0 (no timeout)
      • cacheProvider:string - The cache provider to use, defaults to the default cache provider
      • columnKey:string - The column to use as the key when returntype is "struct"
      • datasource:string - The datasource name to use for the query, if not specified, the default datasource will be used
      • dbtype:string - The database type to use for the query, this is either for query of queries or HQL. Mutually exclusive with datasource
      • fetchsize:numeric - Number of rows to fetch from database at once, defaults to all rows (0)
      • maxrows:numeric - Maximum number of rows to return
      • result - The name of the variable to store the results of the query
      • returntype - The return type: "query", "array", "struct"
      • timeout - Query timeout in seconds
      Specified by:
      _invoke in class BIF
      Parameters:
      context - The context in which the BIF is being invoked.
      arguments - Argument scope for the BIF.
      Returns:
      The result of the invocation