How does the WebSphere Adapter for JDBC deal with Null, null string (empty string), blank string, null value, default value and skip over some field(s)

Answer

For the different operations below, JDBC Adapter will make the following actions:

1)”Create” Operation

In UTE environment, you can input different values in the figure as shown below:

In the above figure, we see that different values are set to the different attributes. When it runs, it generates the following business object data:
[7/16/10 15:09:18:390 CST] 00000047 JDBCRA001 3 JDBCInteraction execute Execute Interaction for the function Create
[7/16/10 15:09:18:390 CST] 00000047 JDBCRA001 3 JDBCInteraction execute <?xml version="1.0" encoding="UTF-8"?>
<p:YuanjsPerson
xsi:type="p:YuanjsPerson"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/yuanjsperson"&gt;
<pkey>1</pkey>
<col1 xsi:nil="true"/>
<col2>Null</col2>
<col3>NULL</col3>
<col4></col4>
<col5> </col5>
<col7></col7>
</p:YuanjsPerson>

Here you need to pay special attention to this point:
“unset” means the element disappears or be removed from business object data.
“set default” means “empty string”.

It generates the corresponding SQL statement as below:
[7/16/10 15:09:18:750 CST] 00000047 JDBCRA001 1 com.ibm.j2ca.dbadapter.core.runtime.DBSQLBuilder
composeCreateSQL Create SQL is :
INSERT INTO YUANJS.PERSON ( PKEY, COL2, COL3, COL4, COL5, COL7 ) VALUES ( ? , ? , ? , ? , ? , ? )
Here you need to pay special attention to this point:
<1> For “set null”, it will issue a statement like “insert into person(pkey,col1) values(1,null);
<2> For “set Null”, it will issue a statement like “insert into person(pkey,col2) values(1,'Null'); “
<3> For “set NULL”, it will issue a statement like “insert into person(pkey,col3) values(1,'NULL'); “
<4> For “empty string”, it will issue a statement like “insert into person(pkey,col4) values(1,''); “
<5> For “blank string” with 3 blanks , it will issue a statement like “insert into person(pkey,col5) values(1,' '); “
<6> For “unset”, SQL statement doesn’t contain the corresponding column of “unset” attribute and there is no any operation on the column.
<7> For “set default”, it is the same as “empty string”.

2) “Update” Operation

In UTE environment, you can input different values like Figure 1 and you get the same business object data as above.

“unset” means the element disappears and it causes the element to be removed from business object.
“set default” means “empty string”.

It will generate the SQL statement shown as below:
[7/16/10 15:43:29:687 CST] 00000048 JDBCRA001
1 com.ibm.j2ca.dbadapter.core.runtime.DBSQLBuilder composeUpdateSQL Update SQL is :
UPDATE YUANJS.PERSON SET COL1 = ? , COL2 = ? , COL3 = ? , COL4 = ? , COL5 = ? , COL7 = ? WHERE (PKEY = ?)

Here you need to pay special attention to this point:
<1> For “set null”, it will issue a statement like “update person set col1= null where (pkey='1') ; “
<2> For “set Null”, it will issue a statement like “update person set col2= 'Null' where (pkey='1') ; “
<3> For “set NULL”, it will issue a statement like “update person set col3= 'NULL' where (pkey='1') ; “
<4> For “empty string”, it will issue a statement like “update person set col4= '' where (pkey='1') ; “
<5> For “blank string” with 3 blanks, it will issue a statement like “update person set col5= ' ' where (pkey='1') ; “
<6> For “unset”, SQL statement doesn’t contain the corresponding column of “unset” attribute and there is no any operation on the column.
<7> For “set default”, it is the same as “empty string”.

3) “Delete” Operation

In UTE environment, you can input different values like Figure 1 and you get the same business object data as above.

“unset” means the element disappears and it causes the element to be removed from business object.
“set default” means “empty string”.

It will generate the SQL statement shown as below:
[7/16/10 15:57:33:250 CST] 00000049 JDBCRA001 1 com.ibm.j2ca.dbadapter.core.runtime.DBSQLBuilder composeDeleteSQL Delete SQL is : DELETE FROM YUANJS.PERSON WHERE (PKEY = ?)

For “Delete” Operation, only attributes with primary keys are added into where clause statement and other attributes would be ignored at all. Thus in above case, there are just 1 attribute (pkey) as a criteria in the where clause statement. If above any attribute and “pkey” attribute are regarded as a composite keys, then it will generate the following sql statement.
<1> For “set null”, it will issue a statement like “delete from person where (pkey='1') and (col1= is null); “
<2> For “set Null”, it will issue a statement like “delete from person where (pkey='1') and (col2 = 'Null'); “
<3> For “set NULL”, it will issue a statement like “delete from person where (pkey='1') and (col3 = 'NULL'); “
<4> For “empty string”, it will issue a statement like “delete from person where (pkey='1') and (col4 = ''); “
<5> For “blank string” with 3 blanks, it will issue a statement like “delete from person where (pkey='1') and (col5 = ' '); “
<6> For “unset”, where clause statement doesn’t contain the corresponding column of “unset” attribute and there is no column in the criteria. It will issue a statement like “delete from person where (pkey='1'); “
<7> For “set default”, it is the same as “empty string”.

4) “Retrieve” Operation

In UTE environment, you can input different values like Figure 1 and you get the same business object data as above.

“unset” means the element disappears and it causes the element to be removed from business object.
“set default” means “empty string”.

It will generate the SQL statement shown as below:
[7/16/10 18:12:26:703 CST] 00000048 JDBCRA001 1 com.ibm.j2ca.dbadapter.core.runtime.DBSQLBuilder composeRetrieveSQL Retrieve SQL is : SELECT PKEY, COL1, COL2, COL3, COL4, COL5, COL6, COL7 FROM YUANJS.PERSON WHERE (PKEY = ?)

For “Retrieve” Operation, only attributes with primary keys are added into where clause statement and other attributes would be ignored at all. Thus in above case, there are just 1 attribute (pkey) as a criteria in the where clause statement. If above any attribute and “pkey” attribute are regarded as a composite keys, then it will generate the following sql statement.
<1> For “set null”, it will issue a statement like “select … from person where (pkey='1') and (col1 is null); “
<2> For “set Null”, it will issue a statement like “select … from person where (pkey='1') and (col2 = 'Null'); “
<3> For “set NULL”, it will issue a statement like “select … from person where (pkey='1') and (col3 = 'NULL'); “
<4> For “empty string”, it will issue a statement like “select … from person where (pkey='1') and (col4 = ''); “
<5> For “blank string” with 3 blanks, it will issue a statement like “select … from person where (pkey='1') and (col5= ' '); “
<6> For “unset”, where clause statement doesn’t contain the corresponding column of “unset” attribute and there is no column in the criteria. It will issue a statement like “select … from person where (pkey='1'); “
<7> For “set default”, it is the same as “empty string”.

5) “RetrieveAll” Operation

In UTE environment, you can input different values like Figure 1 and you get the same business object data as above.

“unset” means the element disappears and it causes the element to be removed from business object.
“set default” means “empty string”.

It will generate the SQL statement shown as below:

[7/16/10 18:16:51:078 CST] 00000048 JDBCRA001 1
com.ibm.j2ca.dbadapter.core.runtime.DBSQLBuilder buildSQLForExistsNRetrieveAll RetrieveAll SQL is :
SELECT PKEY, COL1, COL2, COL3, COL4, COL5, COL6, COL7 FROM YUANJS.PERSON WHERE (PKEY = ?) AND (COL1 IS NULL) AND (COL2 = ?) AND (COL3 = ?) AND (COL4 = ?) AND (COL5 = ?) AND (COL7 = ?)

For “RetrieveAll” Operation, any attributes are added into where clause statement.
<1> For “set null”, it will issue a statement like “select … from person where … and (col1 is null); “
<2> For “set Null”, it will issue a statement like “select … from person where … and (col2 = 'Null'); “
<3> For “set NULL”, it will issue a statement like “select … from person where … and (col3 = 'NULL'); “
<4> For “empty string”, it will issue a statement like “select … from person where … and (col4 = ''); “
<5> For “blank string” with 3 blanks, it will issue a statement like “select … from person where … and (col5 = ' '); “
<6> For “unset”, where clause statement doesn’t contain the corresponding column of “unset” attribute and there is no column in the criteria.
<7> For “set default”, it is the same as “empty string”.

Please pay attention to this point:
<1>”Retrieve” operation only returns a row according to primary keys and could not return multiple rows. Please Note: Only the primary key provided in top-level business is used to construct the WHERE clause in the SQL statement.
<2>“RetrieveAll” operation could return multiple row according to some attributes' values.
For more details about “Retrieve” and “RetrieveAll” operation, please refer to these links:
Retrieve: http://publib.boulder.ibm.com/infocenter/dmndhelp/v6r2mx/topic/com.ibm.wsadapters.620.jca.jdbc.doc/doc/cjdb_retrieveoperate.html
RetrieveAll:http://publib.boulder.ibm.com/infocenter/dmndhelp/v6r2mx/topic/com.ibm.wsadapters.620.jca.jdbc.doc/doc/cjdb_retrievealloperate.html

6) “Exists” Operation

In UTE environment, you can input different values like Figure 1 and you get the same business object data as above.

“unset” means the element disappears and it causes the element to be removed from business object.
“set default” means “empty string”.

It will generate the SQL statement shown as below:
[7/16/10 18:20:28:812 CST] 00000048 JDBCRA001 1
com.ibm.j2ca.dbadapter.core.runtime.DBSQLBuilder buildSQLForExistsNRetrieveAll RetrieveAll SQL is :
SELECT count(*) as numRecords FROM YUANJS.PERSON WHERE (PKEY = ?) AND (COL1 IS NULL) AND (COL2 = ?) AND (COL3 = ?) AND (COL4 = ?) AND (COL5 = ?) AND (COL7 = ?)

For “Exists” Operation, any attributes are added into where clause statement. It is the same as the “RetrieveAll” operation.

7) Other matters for attention

You should avoid the following wrong business object data when executing different operations:

7.1. When there is a business graph on business object

On “create”, “update”, “delete”, ”retrieve” operations, these business object data are illegal.

<1> business graph is empty:

<?xml version="1.0" encoding="UTF-8"?>
<2> business object is empty:

<?xml version="1.0" encoding="UTF-8"?>
<createYuanjsPersonBGInput
xmlns:ns2="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/yuanjspersonbg&quot; xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:type="ns2:YuanjsPersonBG">
<verb>Create</verb>
</createYuanjsPersonBGInput>
<3> all attributes under business object are empty:

<?xml version="1.0" encoding="UTF-8"?>
<createYuanjsPersonBGInput
xmlns:ns2="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/yuanjspersonbg&quot; xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:type="ns2:YuanjsPersonBG">
<verb>Create</verb>
<YuanjsPerson/>
</createYuanjsPersonBGInput>

On “retrieveAll” ,”exists” operation, the business object data is illegal.
business graph is empty:

7.2. When there is no business graph on business object

On “create”, “update”, “delete”,”retrieve” operations, these business object data are illegal

<1> business object is empty:

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

<2> all attributes under business object are empty:


<?xml version="1.0" encoding="UTF-8"?>
<createYuanjsPersonInput
xmlns:ns2="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/yuanjsperson&quot; xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:type="ns2:YuanjsPerson"/>


On “retrieveAll” ,”exists” operation, the business object data is illegal.
business object is empty:

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

7.3. How to unset, set null, set empty string, set blank string by using BO API in Java

In some cases, you have to assign values to business object in java code and so we give some examples as below.

1.) unset business object attribute(s) or set business object attribute(s) to null in java
If business object data is set like this:
DataObject customer = bg.createDataObject("YuanjsCustomer");
customer.set("pkey", "0001");
customer.unset("fname");
customer.set("lname", null);

It will generate the business object data:
<?xml version="1.0" encoding="UTF-8"?>
<p:YuanjsCustomer xmlns:p="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/yuanjscustomer]" xmlns:ns0="[http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/yuanjscustomer" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:type="p:YuanjsCustomer">
<pkey>0001</pkey>
<lname xsi:nil="true"/>
</p:YuanjsCustomer>

2.) set business object to null in java
If business object data is set like this:
DataObject bg = (DataObject) factory.create(
"http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/yuanjscustomebg]", "YuanjsCustomerBG"); DataObject customer = bg.createDataObject("YuanjsCustomer"); It will generate the business object data: xmlns:ns0="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/yuanjscustomer" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:type="p:YuanjsCustomer"/>

3.) set business object attribute(s) to empty string, blank string, null string in java
If business object data is set like this:
DataObject customer = bg.createDataObject("YuanjsCustomer");
customer.set("pkey", "0001");
customer.setString("fname","");
customer.setString("lname", null);
customer.setString("ccode", " ");

It will generate the business object data:
<?xml version="1.0" encoding="UTF-8"?>
<p:YuanjsCustomer
xmlns:p="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/yuanjscustomer" xmlns:ns0="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/yuanjscustomer"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:type="p:YuanjsCustomer">
<pkey>0001</pkey>
<fname></fname>
<lname xsi:nil="true"/>
<ccode> </ccode>
</p:YuanjsCustomer>