com.isomorphic.sql

## Class SQLDataSource

- java.lang.Object
- - com.isomorphic.base.Base
  - - com.isomorphic.datasource.DataSource
    - - com.isomorphic.datasource.BasicDataSource
      - - com.isomorphic.sql.SQLDataSource

- All Implemented Interfaces:
    IToJSON

      public class SQLDataSource
      extends BasicDataSource

  A DataSource implementation for SQL database engines. SmartClient Server supports all major database products and allows you to connect a SmartClient databound widget to an existing database table with a few clicks in a simple wizard. DataSources created in this way require zero code and support all four CRUD operations out of the box.

  SmartClient Server's SQL support is mature, feature-rich and very customizable. See the client reference documentation for details of configuring and using SQL DataSources - search for "sqlDataSource". For a wider general discussion of data integration options with SmartClient Server, search the client documentation for "serverDataIntegration"

  See Also:
    Serialized Form

- - ### Field Summary

    - ### Fields inherited from class com.isomorphic.datasource.DataSource

      `OP_ADD, OP_CLIENT_EXPORT, OP_CUSTOM, OP_DOWNLOAD_FILE, OP_FETCH, OP_GET_FILE, OP_GET_FILE_VERSION, OP_HAS_FILE, OP_HAS_FILE_VERSION, OP_LIST_FILE_VERSIONS, OP_LIST_FILES, OP_LOAD_SCHEMA, OP_REMOVE, OP_REMOVE_FILE, OP_REMOVE_FILE_VERSION, OP_RENAME_FILE, OP_SAVE_FILE, OP_UNIQUE_NAME, OP_UPDATE, OP_VALIDATE, OP_VIEW_FILE`

  - ### Method Summary

    All Methods Static Methods Concrete Methods
    Modifier and Type | Method and Description
    --- | ---
    `static int[]` | `executeAsBatch(DSRequest[] dsRequests)` Executes an array of DSRequests in a single JDBC `Statement.executeBatch()` call.
    `static java.util.List` | `executeAsBatch(java.util.List dsRequests)` Convenience implementation of `SQLDataSource.executeAsBatch(DSRequest[])`, if you prefer to pass and return Lists instead of arrays.
    `static java.lang.String` | `getSQLClause(SQLClauseType clauseType, DSRequest dsRequest)` Returns the text of the generated SQL we will use to implement a clause of the supplied type for the supplied DSRequest.

    - ### Methods inherited from class com.isomorphic.datasource.BasicDataSource

      `getAuditDataSource, getListProperty, getMapProperty, getObjectProperty, getProperty, shouldAutoJoinTransaction, shouldAutoStartTransaction, transformMultipleFields, transformMultipleFields`

    - ### Methods inherited from class com.isomorphic.datasource.DataSource

      `add, add, addDynamicDSGenerator, addDynamicDSGenerator, addDynamicDSGenerator, clearDynamicDSGenerators, convertRelativeDates, convertRelativeDates, convertRelativeDates, convertRelativeDates, convertRelativeDates, execute, executeAdd, executeClientExport, executeCustom, executeFetch, executeFileSource, executeRemove, executeUpdate, fetch, fetch, fetchById, fetchById, fetchSingle, fetchSingle, filter, filter, fromXML, fromXML, fromXML, fromXML, fromXML, fromXML, fromXML, fromXML, getAuditChangedFieldsFieldName, getAuditRevisionFieldName, getAuditTimestampFieldName, getAuditTypeFieldName, getAuditUserFieldName, getDefaultDynamicDSGenerator, getDynamicDSGenerators, getEnumConstantProperty, getEnumOrdinalProperty, getEnumTranslateStrategy, getField, getFieldNames, getFile, getFileAsInputStream, getFileAsString, getFileContentsField, getFileFormatField, getFileNameField, getFileTypeField, getID, getListProperties, getListProperties, getName, getPrimaryKey, getProperties, getProperties, getProperties, getPropertyJavaClass, getRecordXPath, getRelatedDisplayRecord, getRelatedDisplayRecord, getTableName, getTransactionObject, getTransactionObject, hasFile, hasRecord, hasRecord, initialized, isModificationOperation, isServerOnly, listFiles, listFiles, listFiles, listFiles, remove, removeDynamicDSGenerator, removeDynamicDSGenerator, removeDynamicDSGenerator, removeFile, renameFile, saveFile, setEnumConstantProperty, setEnumOrdinalProperty, setEnumTranslateStrategy, setOmitNullMapValuesInResponse, setProperties, transformImportValue, transformImportValue, update, update, validate, validate, validate, validateRecord`

    - ### Methods inherited from interface com.isomorphic.js.IToJSON

      `toJSON`

- - ### Method Detail

    - #### getSQLClause

          public static java.lang.String getSQLClause(SQLClauseType clauseType,
                                                      DSRequest dsRequest)
                                               throws java.lang.Exception

      Returns the text of the generated SQL we will use to implement a clause of the supplied type for the supplied DSRequest. The clause text will be exactly the same as the value of the corresponding `$defaultXxxxxClause` Velocity variable that you can use when developing your own `customSQL` queries. Clauses consist of just the variable part and exclude the SQL keywords. The one exception to this is the special clause type "All"; if you specify this clause type, we return a full-formed SQL query suitable for passing straight to the executeQuery() or executeUpdate() method of a JDBC driver or equivalent.

      Note that we ordinarily generate SQL that is self-contained and context-free, so you can store the clauses that this API returns and use them to construct a query for later execution without any dependence on the DSRequest. However, we are not always able to do this; binary columns and CLOBs cannot be assigned inline, so we have to generate SQL containing '?' markers, which can only be executed as parameterized queries. This in turn means that you need to store the values of any such fields in order to provide them as parameters when you come to execute the query.

      Also note that the query clauses produced by SQLDataSource are database-specific and will only work if run against the same database product they were generated for.

      Parameters:
        `clauseType` - The type of clause to generate and return
        `dsRequest` - The DSRequest embodying the fetch or update operation
      Returns:
        The text of the generated SQL we will use to implement a clause of `clauseType` for the supplied `dsRequest`
      Throws:
        `java.lang.Exception`

    - #### executeAsBatch

          public static java.util.List executeAsBatch(java.util.List dsRequests)
                                               throws java.lang.Exception

      Convenience implementation of `SQLDataSource.executeAsBatch(DSRequest[])`, if you prefer to pass and return Lists instead of arrays.
      Parameters:
        `dsRequests` - List of DSRequests to execute as a batch
      Returns:
        List of update counts, each entry pertains to the corresponding (by ordinal position) DSRequest
      Throws:
        `java.lang.Exception`

    - #### executeAsBatch

          public static int[] executeAsBatch(DSRequest[] dsRequests)
                                      throws java.lang.Exception

      Executes an array of DSRequests in a single JDBC `Statement.executeBatch()` call. The implementation builds up the batch by calling `SQLDataSource.getSQLClause(SQLClauseType,DSRequest)` to obtain the query text to execute for each DSRequest, and then adding each query with calls to `Statement.addBatch(java.lang.String)`. This means that many of the sophistications of DSRequest processing are bypassed. Note the follwoing:

      - No DMI, security checks, or other normal consequences of dsRequest.execute() will occur, because the DSRequests are not executed in the normal way
      - executeAsBatch() allocates and frees its own SQL connection, and does not participate in automatic transctions
      - However, the entire batch is committed or rolled back in a single transaction that is specific to the batch, assuming that the underlying database is capaable of transactions

      This method is intended to be used for edge cases where a large number of records must be updated, and the performance overhead of updating them singly makes normal DSRequest processing impractical. exeuteAsBatch() can give dramatic performance improvements in these circumstances, but at the cost of losing much of the functionality of a DSRequest.

      Parameters:
        `dsRequests` - Array of DSRequests to execute as a batch
      Returns:
        Array of update counts, each entry pertains to the corresponding (by ordinal position) DSRequest
      Throws:
        `java.lang.Exception`