RDBGet - RDB Get

The RDB(Get) component outputs the results of the specified RDBMS SELECT statement.

To retrieve the fields of RDB in the flow service, it is necessary to make the type of the field of each RDB correspond to the data type of the flow service. About the field of each RDB and correspondence with the data type of the flow service, please refer to "Conversion of data type with RDB" of flow service manual.

Stream Information

Input	Format	All
	Number of Inputs	Unlimited
	Description	The input stream is not used. Stream formats are not selectable.
Output	Format	Record

Component Properties

Name	Data Type	Mapping	Description
Connection	connection	-	Specifies RDB to connect. The connections are defined in the Connection Pane of Flow Designer or the Flow Service Management Console.
SQL	string	In & Out	Specifies the SQL to be called against RDBMS. The SQLBuilder will automatically create the SQL statement that will be set for this property. It is possible to modify the SQL statement after it is generated. For details, please refer to following topic "Using SQL Builder". The SQL statement can be set with a Mapper component.
RecordFilter	boolean	-	Specifies whether the StartRow and GetCount properties are to be used to filter out a subset of records from the SELECT operations resulting record set. true [true] - enable the StartRow and GetCount properties. false [false] - disable the StartRow and GetCount properties.
StartRow	int	In & Out	If RecordFilter =true, this property specifies from which record in the SELECT operation's resulting record set records are to be filtered to the output. The index of the line is an index of one base. If the StartRow is greater than the number of records in the SELECT operation's resulting record set, a RecordNotFoundException will occur. If the CountZeroException is set to false an empty set is output.
GetCount	int	In & Out	If RecordFilter =true, this property specifies how many records in the SELECT operation's resulting record set records are to be filtered to the output. For example, if StartRow =11 and GetCount =10, the ten records starting from the SELECT operation's resulting record set's eleventh row will be output. If GetCount is greater than the number of records located from the StartRow , all the records from the StartRow to the end of the record set will be output. If GetCount is set to zero, all records from the StartRow to the end of the record set will be output.
QueryTimeout	int	In & Out	Specifies the time, in seconds, that this component will wait for the SELECT command results to be returned by the RDBMS. If a result is not received by the specified QueryTimeout period, Exception is thrown. If this property is set to zero, this component will not timeout.
LoopProcess	loopProcess	-	Specifies if it outputs results as a stream by specified lines by GetByLoop or as a single stream. true [true] - Send each record individually along the output stream. false [false] - Send out all of the data as a single set.
GetByLoop	int	In & Out	Specifies the number of lines to output at once if LoopProcess is set to true.
CountZeroException	boolean	-	Specifies whether to throw error when the number of results are empty. true [true] - Throws an exception if the result is empty. false [false] - Doens't throw an exception, but the output stream will be empty.
Transaction	boolean	-	Specifies whether to treat the connection to DBMS as a transaction. If the process of this component doesn't update anything and the flow is not transactional, it is faster when this property is set to false. Typically, because this component calls only SELECT statement, this property don't require to be set to true. true [true] - Transactional false [false] - Non-transactional
FetchSize	int	-	Specifies the size to set JDBC setFetchSize() Default value is 1000. In the case of using MySQL, Specifies "useCursorFetch = true" URL for the connection.
TransformSQLParameter	boolean	-	If false , component do not transform sql parameter like $ and ? in sql statement. In this case, note that the SQL Parameter will be ignored. true [true] - component transform SQL parameter false [false] - component do not transform SQL parameter
SQLParameter	category	In & Out	Defines the name, data type and default value of the parameters that may be used in an SQL statement. SQLParameter is defined in the SQLBuilder. Please refer to following topic "Using SQL Builder".

Loop

If LoopProcess =true, each record resulting from the SQL query will be sent out as an individual record set. This results in looping.

Transaction

Commit	If Transaction =true, commits the RDBMS.
Rollback	If Transaction =true, rolls back the RDBMS.

Exceptions

Type	Parameter	Stream for error handling flow	Error Code	Description
Exception	None	This component's input stream	Database vendor-specific exception code	Invalid SQL .
			-	Connection not found.
			Database vendor-specific exception code	The query times out.
ConnectException	None	This component's input stream	-	Failure connecting to the DBMS.
RecordNotFoundException	None	This component's input stream	3	CountZeroException is set to true, and the query results in zero records.
			4	CountZeroException is set to true, and the record filtering results in zero records.

Limitation

When using Microsoft Access in database, the combination of setting「Transactionize」property of start component in property flow to「No」, setting the LoopProcess property of this component to「true」, and setting Transaction property to「true」can not be used.

Cancel of the flow

When flow is canceled, the flow usually aborts after the execution of that component is ends. However, the execution of this component is forced to be ended, then it aborts. If the execution is forced to be ended, the transaction is rolled back.

Relationship between columns of SELECT statement and fields of output stream

After the SQL builder is finished, the「Do you want to update field definition？」message dialog box will be shown. When click「Yes」, the columns of SELECT statement selected by SQL builder are defined in stream pane as output stream. When click「No」, they will not be defined in stream pane.

To retrieve records as the output stream of this component, though it is required to define fields in stream pane, since each column of SELECT statement generated by SQL builder and the fields of output stream are mapped by the order, it is not necessary that column name and field name must match.

Even when the number of SELECTed columns is different from the number of fields defined in the output stream, or when the column data type is different from the data type of corresponding field, the mapping can also be implemented by the order.

How to use SQL Parameters

SQLParameter defined values and character strings can be inserted dynamically into the SQL statement. SQL statement parameters are referenced in the following two ways:

• $parameterName$
  replacement string
• ?parameterName?
  SQL parameter value

A replacement string replaces the parameter reference in the SQL statement. The replacement string replaces the parameter reference before the SQL query is made. The replacement string's SQLParameter data type must be set to String.

Use the appropriate escape sequence "$$" or "??" to have a single "$" or "?" character in the resulting query statement.

An SQLParameter defined parameter value can be inserted into the SQL statement. The SQL parameter's value replaces the SQL parameter reference before the SQL query is made.

For example, given the SQL statement below:

SELECT GROUP, NAME, KANA FROM EMPLOYEE WHERE GROUP = ?GROUP? ORDER BY $ORDER$

and the SQL parameters defined as:

GROUP=Sales
ORDER=KANA

the resulting query statement will be:

SELECT GROUP, NAME, KANA FROM EMPLOYEE WHERE GROUP = ? ORDER BY KANA

where the GROUP parameter's "Sales" value will be assigned to the first ? position.

If setting the SQL property with a preceding Mapper component, or using replacement string substitution within the SQL statement, a statement construction pass will be required before the SQL statement is executed.
Using only an SQLParameter value reference runs faster than a replacement string reference.

Using SQL builder

SQL builder is a specialized tool to automatically generate SQL statement. Using SQL builder, the following items can be done through simple operations.

• Specify tables and fields to read from table list retrieved from RDBMS
• Specify conditions or sort orders in the field
• Specify any conditions in the field
• Define the parameters, and generate SQL statement which uses dynamic values at the runtime of Flow（SQL parameters）
• Execute SQL statement which directly edits the SQL statement automatically generated by SQL builder
• Using the test function to actually run the SQL statement and confirm the results

●Start SQL builder

Start SQL builder using any of the following operations.

• Double-click the RDBGet component
• Right-click the RDBGet component and then click「SQL builder」from the shown menu

●Page of SQL builder

• (1)Left side of the page：area for displaying the table list
• (2)Centre of the page：area for placing the list box showing the tables
• (3)Upper right corner of the page：area for displaying the detailed information of RDBMS
• (4)Bottom right corner of the page：area for specifying the fields to actually read or conditions to read
• (5)Bottom of the page：area for displaying and editing the SQL statement generated automatically

●Specify tables to read

After starting the SQL builder, the table list will be shown in (1). To specify the tables to read, there are two ways. Using the following operations, fields list of the table will be shown in (2).

Drag＆Drop

Click the table name from list of (1) and start to drag, and drop to (2).

From menu

Right-click the empty space of (2) and click the「Specify table names and add」from the shown menu. Input table names in the shown dialog and then click「OK」.

• Delete tables
  Right-click the list of fields of (2) and click「Delete tables」from the shown menu.

●Display detailed information of RDBMS

Click the table shown in (1) or (2), the detailed information will be shown in (3).

●Specify fields to actually read(SELECT)

To specify the field to actually read, there are two ways. Following the operations below, the SQL statement will be shown in (5).

From the page of (2)

Select the checkbox which is on the left side of field name in the list of fields. It will be shown in the select column tab of (4). To cancel the specified fields, clear the checkbox. It will be deleted from the select column tab of (4).

• Read the entire table
  Click a field in the field list, right-click and then click「Select all」from the shown menu.
  To cancel SELECT ALL, right-click and then click「Deselect all」from the shown menu.

From the page of (4)(Select column tab)

Click the empty field of field name, and select fields to read from the shown pull-down list.

●Edit fields that have been specified reading

Click field in (4), right-click and then execute「Move up」「Move down」「Delete」from the shown menu.

●Inhibit duplicates(DISTINCT)

Right-click in page (2) and when execute「Inhibit duplicates(DISTINCT)」from the shown menu, DISTINCT statement will be attached to the auto-generated SELECT statement.

●Sort records(ORDER BY)

To sort the records in ascending or descending order and then read, there are two following ways. Using the following operations, it will reflected in the SQL statement of (5).

From page (2)

Click a field in the field list, right-click and then click「Ascending」or「Descending」from「Add to sort」of the shown menu.

From page (4)(Sort tab)

Click the empty field of field name, and select the sort key field from the shown pull-down list. Select「Ascending」or「Descending」from the sort order pull-down list of the specified field name.

Cancle the sort of records

Click a field in the field list, right-click and then click「(none)」from「Add to sort」of the shown menu.
Or click a field of the sort tab, right-click and then click the「Delete」from the shown menu.

●Using aggregate functions(GROUP BY)

To apply the aggregate functions on the field, there are the following two ways. Following the operations below, the SQL statement will be shown in (5).

From page (2)

Click a field in the field list, right-click and then select aggregate functions which to apply through「Aggregate functions」from the shown menu.

From page (4)(Aggregate function tab)

Click the empty field of field name, and then select fields which apply aggregate functions from the shown pull-down list. Select aggregate functions which to apply from the aggregate function pull-down list of the specified field name.

When setting「COUNT(*)」, it can only be set from page「2」, the selection can not be implemented in the aggregate function tab.

Cancel aggregate functions

Select fields in page (2), right-click and then select「none」through「Aggregate functions」from the shown menu.
Or click the fields of aggregate function tab, right-click and then click「Delete」from the shown menu.

When aggregate functions are set but the field is not contained in SELECT object(Field is not checked), that item will not be contained in SELECT statement.
Fields which do not contain the aggregate functions in the field of SELECT object will be set automatically to the GROUP BY statement.

●Joining tables(Relations)

In SQL builder, the SQL statement which contains relations can be generated automatically. To set relations among tables, please follow the steps below.

1. Display multiple target tables in (2).
2. Click the field name of target table and start to drag, and then combine the link to the field names of other target tables.
3. Right-click the combined link and then click the type from「Relation type」from the shown menu.

To delete relations, right-click the combined link and then click「Delete relations」from the shown menu.

●Extraction of records(WHERE)

In SQL builder, you can compare the extract conditions of record with a fixed value or a dynamic value in the Flow. Also, you can input the conditional expressions directly and add it to the SQL statement.

Compare with a fixed value

1. Click a field in field list of (2), right-click and then click「Add conditions」from the shown menu.
2. Click the「Compare with a fixed value」radio button in the edit dialog box of conditions.
3. Select conditional expressions from the pull-down list.
4. Input the fixed value to text field.
5. To quote values with「'」, you should check the「Quote values with「'」」checkbox.

The extract conditions will be added to the SQL statement of (5). The conditional expressions will be shown in the field list of (2).

Use SQL parameters to specify the dynamic conditions of dynamic values in execution

In SQL builder, define any names of the parameters, and through specifying the parameter names when adding conditions to field name as extract conditions, parameters can be embedded to the WHERE statement of SQL statement. Config the mapper before this component, and through mapping to the defined parameters, you can set the dynamic values in execution as extract conditions.

To specify the extract conditions of dynamic values, please follow the steps below.

1. Initially, define any name, data type and initial value's parameters in parameter tab of (4).
2. Click a field in field list of (2), right-click and then click「Add conditions」from the shown menu.
3. Click the「Compare with parameters」radio button in the edit dialog box of conditions.
4. Select conditional expressions from the pull-down list.
5. Select from the pull-down list which shows the defined parameters.
6. To quote values with「'」, you should check the「Quote values with「'」」checkbox.

Config the mapper before RDBGet component, and mapper value fields to the field names of parameters which exist on the output side of mapping window.

Extract conditions will be added to the SQL statement of (5). The conditional expressions will be shown in the field list of (2).

Specify any conditions

To specify any conditions, there are the following two ways. By the following operations, the extract conditions will be added to the SQL statement of (5).

From edit dialog box of conditions

1. Click a field in field list of (2), right-click and then click「Add conditions」from the shown menu.
2. Click「Directly input」radio button in the edit dialog box of conditions.
3. Directly input conditional expressions to the text box.

From page (4)(Condition tab)

1. Click「Add any conditions」of the condition tab.
2. Directly input conditional expressions to the text box.

Combine extract conditions

When combining various conditions to extract, you can specify the logical operators in the conditional tab of (4). Click the「AND」or「OR」from the pull-down list of「Relationships」item of conditional field.

Edit extract conditions

To edit the extracted conditions, double-click the field of conditional tab. Or, click the field, right-click and click「Edit conditions」from the shown menu.

Cancel the extract conditions

Click the field of conditional tab, right-click and click the「Delete」from the shown menu.

●Using SQL parameters to generate dynamic SQL statement at runtime

SQL parameters of SQL builder, except for setting the extract conditions(WHERE statement), can replace any strings of SQL statement. Define parameters of any name, and directly write parameters in (5). Besides, you can write when specifying any conditions in the edit dialog box of conditions. Config the mapper before this component, through mapping to the defined parameters, you can set dynamic SQL statement at runtime.

To specify dynamic SQL statement, follow the steps below.

1. In parameter tab of (4), define data type as String parameters in any name or initial values.
2. In (5), use $ to quote field names like「$field name of parameter$」 in those parts which use strings to replace.
3. Config mapper before the RDBGet component.
4. Mapping fields which are strings to replace to the SQL parameter field names on the output side of mapping window.

●Edit parameters

Right-click a field in (4) and do「Move up」「Move down」「Insert」「Delete」from the shown menu.

●Directly edit the auto-generated SQL statement

The auto-generated SQL statement through the specifications of (1)～(4) will display in (5). When directly edit (5), that SQL statement will be issued to RDBMS directly. In direct edition, when synchronize the auto-generated SQL statement, please pay attention that it will return to the original statement. About synchronization settings, please refer to the following paragraph 「Options of displaying auto-generated SQL statement」.

●Test the SQL statement

To actually issue and test the SQL statement in RDBMS, perform the following steps.

1. Click the「Test SELECT」on the bottom right side of page (5).
2. When defining parameters, set testing values to「Initial value」in the dialog box which is for specifying parameter value and then click「OK」.

When it is normally ended, the execution result set will display in the dialog box. If it is ended abnormally, the result set will display in the error message dialog box.

Change the number to display in the result set

Change the lines of「Lines of test result」field on the bottom right side of page (5) and then click「Test SELECT」. Initial value is 100.

●Options to display SQL statement auto-generated

For displaying (5), you can specify the options on the bottom right side of page.

For editing the SQL statement of (5)

Use SQL builder to select specified fields, and the conditions are called model. If the「Always synchronize SQL」checkbox is ON, models of (1)～(4) will synchronize with the SQL statement of (5) and are auto-generated. The auto-generated SQL statement can also be directly edited in (5). In this case, to manipulate the model and do not synchronize to the directly edited SQL statement, the「Always synchronize SQL」checkbox will automatically be OFF. When reset the direct editing or auto-generate SQL statement from re-operated model, click「Apply model to SQL」.

Options

• Always synchronize SQL
  If ON, auto-generate when select fields and specify conditions each time.
  If OFF, do not auto-generate even when select fields or specify conditions.

• Omit the default schema
  If ON, omit the default schema.
  If OFF, describe the default schema.

• Use aliases
  If ON, describe aliases.
  If OFF, omit aliases.

• Quote name with「"」
  If ON, quote field name with "(double quotations).
  If OFF, do not quote field name with "(double quotations).

• Indent SQL
  If ON, indent and display SQL statement.
  If OFF, display SQL statement in one line.

• Aplly model to SQL
  When clicked, SQL statement will be auto-generated from model. Set the directly edited SQL statement to invalid.

●Attention

• Use the appropriate escape sequence "$$" or "??" to have a single "$" or "?" character in the SQL .
• When using mapper to insert values to SQL , or when using replacement strings, parse is required in each SQL execution, and it may take time to process. Only when specifying WHERE statement, it is much faster to use SQL parameters.
• Comments are not supported in SQL Builder.
  However, if you use SQL Builder to automatically generate SQL statements and edit the SQL statements with the SQL statement property of the RDBGet component, you can write comments. In this case, when editing again, please be careful not to start SQL Builder and edit it in "SQL statement" property. * When SQL statements automatically generated by SQL Builder are edited, SQL Builder may not be able to respond properly.
• If a property expression is set in SQLParameter, the SQL builder is opened read-only. To edit with SQL builder, clear the property expression of SQLParameter.

●Example

When SQL value is as follows

SELECT GROUP, NAME, KANA FROM EMPLOYEE WHERE GROUP = ?GROUP? ORDER BY $ORDER$

When SQLParameter is as follows

GROUP=Sales
ORDER=KANA

The actually executed SQL statement is as follows. 「Sales」will be input in the GROUP parameter, and「KANA」will be input in the ORDER parameter.

SELECT GROUP, NAME, KANA FROM EMPLOYEE WHERE GROUP = Sales ORDER BY KANA