3.6.7 Preconditions and notes on connecting to SQL Server

This subsection describes the preconditions and notes on connecting to SQL Server.

Organization of this subsection
(1) Preconditions for SQL Server
(2) Notes on connecting to SQL Server
(3) Notes on connecting to SQL Server 2005
(4) Notes on connecting to SQL Server 2008 or SQL Server 2012

(1) Preconditions for SQL Server

The preconditions for SQL Server are as follows:

Note that you can only connect to SQL Server in Windows.

(2) Notes on connecting to SQL Server

This subsection describes the notes related to the character code conversion in the system and the notes on the DB Connector settings when you connect to SQL Server.

(a) Notes on character code conversion in a system

When you are connected to SQL Server and want to store data containing a Japanese character code in the database, you must take into account the character code conversion in the system. This section gives an overview of the character code conversion in a system, and describes the notes on specifying the settings to avoid garbled characters.

With Java, the Japanese character code is expressed using Unicode. When you use SQL Server, the character code conversion is implemented during the processing between the Web client and Application Server and between Application Server and SQL Server.

The following figure gives an overview of the character code conversion when SQL Server is used.

Figure 3-19 Overview of the character code conversion when SQL Server is used

[Figure]

A description of points 1 to 4 in the figure is as follows:

  1. When Application Server receives data from the Web client, the external character code is converted to Unicode.
  2. When Application Server stores the data in SQL Server, Unicode is converted to the database storage character code.
  3. When the data stored in SQL Server is obtained by Application Server, the database storage character code is converted to Unicode.
  4. When Application Server sends the data to the Web client, Unicode is converted to the external character code.

When you use SQL Server, problems such as garbled characters might occur depending on the combination of the external character code and database storage character code, and the types of converter used for implementing the character code conversion. To avoid garbling, you must note the character code settings.

SQL Server supports the character data types described in the following table. When you use SQL Server, you can prevent the occurrence of garbling during the character code conversion by using the Unicode data types.

Table 3-31 Character data types supported by SQL Server

CategoryCharacter data type
Unicode data typesnchar, nvarchar, ntext
Non-Unicode character data typeschar, varchar, text

The following paragraphs describe the character code conversion when you use the Unicode data types and when you use the non-Unicode data types as the database storage character code in SQL Server:

When you use the Unicode data types
If true (default value) is set in the sendStringParametersAsUnicode key of the DB Connector property, garbling does not occur. If false is set, and if you use Shift_JIS, EUC-JP, ISO-2022-JP, or UTF-8 in the external character code, garbling might occur.
When you use the non-Unicode character data types
Garbling might occur depending on the external character code settings.
  • When Windows-31J is used in the external character code
    Garbling does not occur.
  • When Shift_JIS, EUC-JP, or ISO-2022-JP is used in the external character code
    The characters such as those shown below might be garbled.

    [Figure]

  • When UTF-8 is used in the external character code
    The characters such as those shown below might be garbled.

    [Figure]

(b) Notes on specifying the selectMethod property of DB Connector

The following table describes the notes on specifying direct in the value of the selectMethod property (item name of <config-property-name>) of DB Connector.

Table 3-32 Notes on specifying the selectMethod property of DB Connector

ConditionNotes
The connection error detection functionality is enabledA connection error that occurs might be misdiagnosed as normal. As a result, the connection with an error might be returned to the user application program, so do not use the connection error detection functionality. When an error is detected, execute the cjclearpool command.
Multiple Statement, PreparedStatement and CallableStatement are generated concurrentlyThe JDBC driver for SQL Server generates a connection to SQL Server for each concurrently generated statement.
Also, note that when the statement pooling functionality is used, connections are generated for each pooled statement, and a large amount of memory is consumed.

(3) Notes on connecting to SQL Server 2005

Make sure you use the connection pooling functionality to connect to SQL Server 2005.

The following events might occur depending on the behavior of SQL Server JDBC Driver 3.0:

(a) The database sessions are not disconnected and the unused sessions are left behind.

The database sessions are not disconnected and the un-used sessions are left behind until garbage collection occurs. To avoid this event, execute the javagc command.

Note that this event occurs in the following conditions:

When the following operations are performed:
  • Connection test of DB Connector
  • Stopping the DB Connector
  • Execution of the cjclearpool command
When the following functionality is executed:
  • Method cancellation functionality
    Message KDJE31016-W is output.
  • Transaction timeout functionality
    Message KDJE31002-W is output.
  • Connection count adjustment functionality
    Message KDJE49532-I is output.
  • Connection sweeper functionality
    Message KDJE50010-I is output when the managed connections are destroyed, including cases when the connection sweeper functionality is operated by setting the log or trace level output by DB Connector to WARNING or INFORMATION.
(b) An attempt to obtain a connection fails.

When the number of database sessions reaches the maximum concurrent user connections for the database because the above-mentioned (a) database sessions are not disconnected, you can no longer generate new sessions. When you execute the getConnection method of the javax.sql.DataSource interface from the user program, java.sql.SQLException might occur. To avoid this event, specify 0 (unlimited) as the maximum concurrent user connections for the database.

Note that this event occurs when 1 or more is specified as the maximum concurrent user connections for the database.

(4) Notes on connecting to SQL Server 2008 or SQL Server 2012

The notes on connecting to SQL Server 2008 or SQL Server 2012 are as follows. Note that none of the precautions on using SQL Server 2005 are applicable.