Use the DB adapter definition support function to design the contents of the SQL statements executed in DB adapters. The variable components of SQL execution statements, such as data and the names of tables and columns, are designed as argument elements.

Note that the DB adapter definition support function allows you to specify the following SQL commands:

• SELECT
• INSERT

The data types you can specify are those supported by the JDBC driver used by the DB Connector.

Design the content of the DB adapters. The aspects you need to design are described below.

(a) Level of DB adapter creation

Because a DB adapter is required for each DB Connector, you need to prepare DB adapters according to how the DB Connectors are structured.

For example, a system that integrates multiple databases will have a DB Connector for each database. In this case, you will need to create a DB adapter for each DB Connector.

Figure C-2 When there is a DB Connector for each database

You can also prepare multiple DB Connectors for the same database. This is useful in situations where you want to use different connection pool settings for individual DB adapters. In this case, you need to create a DB adapter for each DB Connector regardless of the number of databases in the system.

Figure C-3 When a database has multiple DB Connectors

(b) Level of SQL statement definition

You can define one or more SQL statements in a DB adapter. Design the contents of the SQL statements to be defined in each DB adapter. Examples of SQL statement design are shown below.

• When executing different SQL statements for different databases
  If you want to execute different SQL statements for different databases, define the appropriate SQL statements in the DB adapter for each database.
  For example, if you want to find data in Database 1 in the figure and register that data in Database 2, define a SELECT statement in DB adapter 1 and an INSERT statement in DB adapter 2.
  

  Figure C-4 Executing different SQL statements for different databases

  

• When executing different SQL statements for the same database
  If you want to execute different SQL statements for different activities in a database, define multiple SQL statements in the DB adapter.
  For example, if you want data found by the first activity to be registered by the next activity, define SELECT and INSERT statements in the same DB adapter.
  

  Figure C-5 Executing different SQL statements for the same database

  

  When using the DB adapter definition support function to define multiple SQL statements in a DB adapter, define an operation for each SQL statement to execute.
  For details on how to specify operations, see (5) Creating SQL statements.
  

Create sample DB adapter definition files to use for debugging purposes. DB adapter definition files are created in Eclipse. Use the procedure below to create a new DB adapter definition file.

Note

• If the following message appears when editing a DB adapter definition file, synchronize the resources with the file system by right-clicking the project in Package Explorer view and selecting Refresh.
  Resource 'file-path' is out of sync with file system.
  
• Deleting a DB adapter definition file does not automatically delete the output directory that was created when outputting the file. Delete the directory manually as needed.
• When working with a DB adapter definition file, use the view that opens when you select the HCSCTE-DBEditor perspective.

1. Create a project in Eclipse.
   We recommend that you select HCSCTE as the project type. For details on how to create a HCSCTE project, see 3.1.1 Creating a Project in the Service Platform Basic Development Guide.
   
2. From the Eclipse menu, select Window, Open Perspective, and then Other.
   The Open Perspective dialog box appears.
   
3. Select HCSC-DBEditor, and then click OK.
   The HCSCTE-DBEditor perspective appears.
   
4. From the Eclipse menu, select File, New, and then Other.
   A dialog box appears in which you can select a wizard.
   
5. Under HCSCTE-DBEDITOR, select DBEditor File and then click Next.
   The New DBA File wizard appears.
   
6. In the Enter or select the parent folder field, enter or select the folder in which you want to save the definition file of the DB adapter.
7. In the File name field, enter the file name for the definition file.
8. Click Finish.
   The new definition file is created, and the HCSCTE-DBEditor window appears.
   

   

In the DB adapter definition file, set the information for the database you want to use. This process differs depending on whether the database you are using for debugging differs from the database in the production environment. You cannot debug SQL statements without a debug database.

Reference note

Service Architect incorporates an embedded database you can use for debugging purposes.

(a) When providing a debug database separate from the production environment database

To set up the debug database in an environment where the debug database is separate from the database for the production environment:

1. Open the DB adapter definition file in Eclipse.
   The HCSCTE-DBEditor window appears.
   
2. On the Connections tab, right-click DataSources and select New DataSource.
   The New DataSource dialog box appears.
   

   

3. Enter values in the input fields in the DBConnection area.
   Enter the settings for SQL debugging.
   Here, you enter the settings required to connect to the database using the JDBC driver.
   

   Table C-1 Information to define in input fields in DBConnection area (New DataSource dialog box)

   Item	Description	Default value#
   Name	The name displayed on the Connections tab of the HCSCTE-DBEditor window. Specify a name that is unique within the DB editor definition file.	HiRDB
   Without DataBase	Select this check box if there is no debug database.	Cleared
   Driver Jar	Specify the JAR file of the JDBC driver. Make sure that you obtain the appropriate JDBC driver in advance.	service-platform-installation-directory\DB\CLIENT\UTL\pdjdbc2.jar
   Driver	Specify the package name (fully qualified Java class name) of the JDBC you want to use.	JP.co.Hitachi.soft.HiRDB.JDBC.HiRDBDriver
   URL	Specify the database connection URL, including the port number and host name of the connection-target database.	jdbc:hitachi:hirdb://DBID=22200,DBHOST=localhost
   User Name	Specify the login user name used when connecting to the database.	admin
   Password	Specify the password used when connecting to the database.	admin (shown as **** on screen)

   Note:

   The values in the input fields in the DBConnection area are only used when debugging SQL statements. They are not used in the SQL operation definition file and HITACHI Application Integrated Property file generated by the DB adapter definition support function.

   #

   The default values are the values entered when you click the Default button in the New DataSource dialog box.

   The initial values are the same as the environment created when you execute the HCSC Easy Setup function with the initial values.

   
   
4. Enter values in the input fields in the DBA Settings area.
   The settings in this area are used to define the SQL operation definition file for the DB adapter and the HITACHI Application Integrated Property file. Specify the values according to the settings of the DB Connector you are using.
   

   Table C-2 Information to define in input fields in DBA Settings area (New DataSource dialog box)

   Item	Description	Default value#
   Data base reference name	Specify the database reference name.	DB_SERVER1
   Data base type	Specify the database type.	HIRDB
   Resource adapter name	Specify the name of the resource adapter specified in the property definition (display-name) of the DB Connector. If you cannot select the name in the drop-down list, enter the value directly.	DBConnector_HiRDB_Type4_CP
   Maximum value of instance	Specify the maximum number of instances of the DB adapter. To allow unlimited instances, specify 0.	0
   Minimum value of instance	Specify the minimum number of instances of the DB adapter.	1
   Existence of DB Connector	Specify whether to share the DB Connector referenced by the DB adapter.	Shareable
   Maximum Result Number	Specify the maximum number of results to be returned by a SELECT statement. If the SELECT statement returns more than the specified number of results, only the number specified here is displayed. Example: If you specify 1000, results for a maximum of 1000 records is displayed.	1000
   Statement Maximum Number	If you want to execute multiple SQL statements in a single service component execution request, specify the maximum number of SQL statements to execute.	1024

   #

   The default values are the values entered when you click the Default button in the New DataSource dialog box.

   The initial values are the same as the environment created when you execute the HCSC Easy Setup function using the initial values.

   
   

   Note

   When using HiRDB as the DBMS in the DB adapter configuration, use the following combination of database type and resource adapter name:

   • When using HiRDB as the database type, set DBConnector_DABJ_CP or DBConnector_DABJ_XA as the resource adapter name.
   • When using HIRDB-TYPE4 as the database type, set DBConnector_HiRDB_Type4_CP or DBConnector_HiRDB_Type4_XA as the resource adapter name.

5. Click OK.
   The settings are registered.
   

(b) When using the production environment database for debugging

To set up the data source in an environment that does not use a separate database for debugging:

1. Open the DB adapter definition file in Eclipse.
   The HCSCTE-DBEditor window appears.
   
2. On the Connections tab, right-click DataSources and then select New DataSource.
   The New DataSource dialog box appears.
   
3. Select the Without DataBase check box in the DBConnection area.
4. In the DBA Settings area, enter the database settings for the production environment.
5. Click OK.
   The settings are registered.
   

To define the SQL statements executed in the DB adapter:

1. Open the DB adapter definition file in Eclipse.
   The HCSCTE-DBEditor window appears.
   
2. From the DataSource pull-down list, select the data source you want to use.
   

   

3. Click the Add button next to the Operation pull-down list.
   A dialog box appears in which you can specify the name of the operation you are adding.
   

   

4. Enter the name of the operation you want to add, and then click OK.
   If you intend to define multiple SQL statements in one DB adapter, define an operation for each SQL statement to execute.
   
5. Define the SQL statement you want to execute in the appropriate fields.
   For details on how to define argument elements, see Format of the SQL operation definition file.
   

Note

• You can enter one SQL statement per operation.
• You cannot use wildcards (*). You must enter the name of every column targeted by a SELECT statement.
• You do not need to terminate the SQL statement with a semicolon.

Debug the SQL statements you defined. When you execute the SQL statements during the debugging process, values are substituted for the argument elements. Note that you can only debug SQL when a separate database from that of the production environment has been provided for debugging purposes.

To debug SQL statements:

1. Create the tables to use for debugging.
   In the database used for debugging, create the tables to use in the debug process. Create the tables according to the SQL statements you defined.
   

   Example:

   Table name: PURCHASEMASTERTABLE

   Table structure:

   ORDERID (VARCHAR)	ORDERNAME (VARCHAR)	PRODUCTCODE (VARCHAR)	PRODUCTNAME (VARCHAR)	NUMBER (INTEGER)
   01	AA001	0A1	NPW	4
   02	AA002	0A1	NPW	3
   03	AA003	0E1	NPW	3
   04	BB001	0E1	NPW	4
   05	BB002	0B1	NPW	5

2. Open the DB adapter definition file in Eclipse.
   The HCSCTE-DBEditor window appears.
   

   

3. With the SQL statement whose argument elements you want to extract selected in the Operation pull-down list, click the Parse icon () in the toolbar. The variables appear on the Variables tab.
   

   

4. In the Value column on the Variables tab, enter the values you want to use for debugging.
   Example:
   

   Argument element name	DBa_inf	data_type	Value
   KEYCOLUMN	column	--	ORDERID
   OPERATOR	preset	--	=
   DATA	data	VARCHAR	'01'

   You cannot perform the subsequent steps unless a debug database has been set up. When you click the Parsed SQL Statement icon (), the SQL statement appears in Messages view with values substituted for its argument elements. Use the information in this window to make sure that the SQL statement has been defined correctly.
   

   

5. On the Connections tab, right-click the data source used for debugging, and then click Connect.
   A connection with the database is established based on the parameters of the data source. If the connection is successful, the following message appears in Messages view: INFO: Data Source <data-source-name> Connection established succesfully.
   If you defined multiple SQL statements, repeat steps 3 to 5 for each statement.
   
6. Click the Execute icon () in the toolbar.
   If execution of the SQL statement fails, an error message appears in Messages view. Amend the SQL statement and argument element names according to the message contents.
   The execution results of the SQL statement are displayed on the Results tab.
   

   

Change the data source settings in the DB adapter definition file to the settings for the database in the production environment.

Note

Before making this change, disconnect the data source you were using for debugging. To disconnect a data source:

1. Open the DB adapter definition file used for debugging in Eclipse.
2. On the Connections tab, right-click the data source used for debugging, and then click Disconnect.

To change the database settings:

1. Open the DB adapter definition file you used for debugging in Eclipse.
   The HCSCTE-DBEditor window appears.
   
2. On the Connections tab, right-click the data source and select Edit DataSource.
   The Edit DataSource dialog box appears.
   
3. In the fields in the DBA Settings area, enter the database information for the production environment.
   

   

4. Click OK.
   The settings are registered.
   

To generate the DB adapter definition files to be used in the production environment:

1. Open the DB adapter definition file for debugging in Eclipse.
   The HCSCTE-DBEditor window appears.
   
2. From the DataSource pull-down list, select the data source you want to apply to the DB adapter definition files for the production environment.
   

   

3. Click the Auto generate icon () in the toolbar.
   The Auto Generate dialog box appears.
   

   

4. In the Output Directory field, specify the directory to which to output the DB adapter definition files for the production environment.
   If you click the ... button, the following path is selected in the Output Directory field:
   Eclipse-project-path\DB-adapter-definition-file-name_Output
   

   Note

   • If you specify the path of the Eclipse project as the output directory, you can use Eclipse functionality to import and export DB adapter definition files and the results of definition file generation in a batch.
   • Specify the output directory as an absolute path that contains no more than 155 bytes when converted to UTF-8 encoding.

5. Select the check box for the operation or operations for which you want to generate DB adapter definition files.
   If you defined multiple SQL statements for one DB adapter, select all the operations required to define the SQL.
   
6. Click Finish.
   The DB adapter definition files for use in the production environment are output to the directory you specified.
   If you specified the path of the Eclipse project as the output directory, refresh the Eclipse project after the DB adapter definition files are generated. The DB adapter definition files appear in the Package Explorer view of Eclipse.
   The following table lists the generated files:
   

   Table C-3 Generated files

   File type	File name	Number of files	Remarks
   SQL operation definition file	csa_sql_dbadapter.xml	1	--
   HITACHI Application Integrated Property file	cscadapter_property.xml	1	--
   Standard request message#1	operation-name_Input.xsd	0 to n#2	operation-name is replaced with the specified operation name.
   Standard response message#3	operation-name_Output.xsd	0 to n#2	operation-name is replaced with the specified operation name.
   Service component message format for request and response messages	ServiceMessageFile.xsd	1	Use the same file as the service component message of the request message and response message.

   Legend:

   --: No description.

   #1

   If there are no argument elements in the SQL statements, a standard message format for the request message is not generated. In this case, use the service component message. When invoking the DB adapter without argument elements, use an invoke Java activity or other means to generate the request message and invoke the DB adapter.

   Example:

   Operation name: select_op

   SQL statement to execute: SELECT ORDERID FROM PURCHASEMASTERTABLE

   Message generated by invoke java activity or similar:

   <?xml version="1.0" encoding="UTF-8"?>

   <DBadapter>

   <DBA_MULTI_SQL>

   <select_op>

   <DBA_IN_DATA/>

   </select_op>

   </DBA_MULTI_SQL>

   </DBadapter>

   #2

   The number of operations specified when generating the DB adapter definition files for the production environment.

   #3

   The standard response message is only output when the SQL command is SELECT. If the command is INSERT, use the service component message format of the response message.

   
   

Create the DB adapter using the DB adapter definition files generated for the production environment.

(a) Creating the DB adapter

Define a new DB adapter using the HCSCTE interface integrated into Eclipse. For details on how to define a new DB adapter, see 3.2.5 Adding a new database adapter.

You also need to add the operations specified in (8) Generating DB adapter definition files for the production environment to the DB adapter you created. If you specified multiple operations, add each operation you specified.

(b) Setting the SQL operation definition file and HITACHI Application Integrated Property File

Display the Service-adapter definition (details) window for the DB adapter you created. Add the following files to the Self-defined file area:

• csa_sql_dbadapter.xml (the generated SQL operation definition file)
• cscadapter_property.xml (the generated HITACHI Application Integrated Property File)
  

  Figure C-6 Self-defined files to add to the DB adapter

  

(c) Setting request message and response message information

Display the Service-adapter definition (standard) window for the DB adapter you created. Set the following information:

Operation names

• Operation in the Service component control information area
  Select the operation name from the drop-down list.
  

Request message for operation

• Message format under Standard in the Request message area
  Specify operation-name_Input.xsd.
  
• Message format under Service component in the Request message area
  Specify ServiceMessageFile.xsd.
  

Response message for operation

• Message format under Standard in the Response message area
  Specify operation-name_Output.xsd. Specify this file only if the SQL command in the specified operation is SELECT. You do not need to specify the file if the SQL command is INSERT.
  
• Message format under Service component in the Response message area
  Specify ServiceMessageFile.xsd.
  

The following figure shows an example of setting this information:

Figure C-7 Example of setting request message and response message

(d) Defining data transformation (mapping)

Define the source and destination mappings to use when transforming data.

• Defining data transformation (mapping) of request messages
  You can use the auto mapping function to perform data transformation of the standard message and service component message in a request message. For details on the auto mapping function, see 6.4 Mapping in the manual Service Platform Basic Development Guide.
  
• Defining data transformation (mapping) of response messages
  Data transformation of the standard message and service component message in a response message must be mapped manually.
  For details on how to transform the results of a SELECT statement to a standard message, see 3.3.5(5) Data transformation.
  A standard message format file for response messages is not output for INSERT statements. As such, you do not need to set up mapping for these statements. The results of an INSERT statement are stored in an XML message in the same message format as the service component side.