Additional connection attributes can be specified for the Database, as illustrated for character set in the examples below.
The charset attribute (defaults to the character set installed with the server):

tip

This parameter is very important as it determines the byte value to character mapping for CHAR/VARCHAR/TEXT values. It applies to characters from the extended set (codes 128-255). For NCHAR/NVARCHAR/NTEXT values however, it does not apply as these are stored using Unicode.

For a MS SQL SERVER connection, use charset as in the example below:

Some charset options are:

    • UTF-8
    • UTF-16
    • ISO-8859-1
    • Cp1252
  • For a Teradata connection, use, for example, charset=UTF8
    • For an Oracle connection, you cannot use charset. The JDBC Thin driver can access databases that use any of the following character sets:
    • US7ASCII (ASCII)
    • WE8ISO8859P1 (ISO-latin-1)
    • AL24UTFFSS (Unicode 1.2)
    • UTF8 (Unicode 2.0)

This happens automatically with no special action on your part. Databases that use other character sets are not yet supported. The JDBC Thin driver can only use the US7ASCII character set.

  • For a DB2 connection, you cannot use charset. This is because the IBM DB2 Driver for JDBC and SQLJ has no information about the server-side CCSID that is used for output parameter values; the driver requests the stored procedure output data in UTF-8 Unicode.
  • The prepareSQL attribute (defaults to 3 for SQL Server, 1 for Sybase):This parameter specifies the mechanism used for Prepared Statements.
    ValueDescription
    0SQL is sent to the server each time without any preparation, literals are inserted in the SQL (slower)
    1Temporary stored procedures are created for each unique SQL statement and parameter combination (faster)
    2sp_executesql is used (fast)
    3 sp_prepare and sp_cursorprepare are used in conjunction with sp_execute and sp_cursorexecute (faster, SQL Server only)
  • The useCursors attribute (defaults to false):

This parameter instructs jTDS to use server side cursors instead of direct selects (AKA firehose cursors) for forward-only read-only result sets (with other types of result sets server- or client-side cursors are always used).
With firehose cursors the SELECT query is sent and the server responds with all the resulting rows. This is the fastest approach but it means that the driver has to cache all results if another request needs to be made before all rows have been processed. So when using multiple Statements per Connection it is preferable to have server-side cursors instead; these will allow the driver to request only a limited number of rows at a time (controllable through the fetchSize property of a Statement). This means extra request-response cycles, but less caching by the driver.
With SQL Server a so called fast forward-only cursor will be created when this property is set to true. With Sybase a usual forward-only read-only cursor is created.

  • No labels