10 Replies Latest reply on Mar 16, 2018 12:45 PM by Mike Albertone

    TimeTen 11.2.2.8.7 - ttBulkCp populating TT_VARCHAR with blank instead of NULL (TypeMode=0)

    Mike Albertone

      Hi,

      we're facing a strange issue when moving from a former TT version to 11.2 using default TypeMode=0.

      We would expect TT_VARCHAR to be populated with NULL when an update or an insert is attempted with values of '' (empty string). This would be expected according to the semantic of TT_VARCHAR when using TypeMode=0 and it's confirmed when we try to update such a field using '' (empty string)

      We can query that field using both ='' or is NULL clauses.

       

      But after migrating from a former DB using ttBulkCp as follows:

       

      ttBulkCp -o DSN=tt41data table table.dump

       

      This creates for instance an entry like:

      "+390921117                      ","0007","6921117                 ","E                               ","COS1      ","","xxxxxx                          ","xxxxxxxx                      ","test1                           ","Y","                        ",0,{59491546000e274c}

      (you can see the "" entry in 6th field)

      ttBulkCp -i DSN=tt41data table table.dump

       

      Once the record is loaded we can see the following EMAILADDRESS field associated to that entry:

      Command> select EMAILADDRESS  from SPA.VODF_CORPORATEDIRECTORY where USERDN='+4390921117';

      <  >

      1 row found.

      We can identify that field value as being a blank:

      Command> select EMAILADDRESS  from SPA.VODF_CORPORATEDIRECTORY where USERDN='+4390921117' and EMAILADDRESS=' ';

      <  >

      1 row found.

       

      by querying using a blank in a string. But trying to put something around that value I can see:

       

      Command> select 'xxx'||EMAILADDRESS||'xxx' from SPA.VODF_CORPORATEDIRECTORY where USERDN='+4390921117';

      < xxxxxx >

      1 row found.

       

      So as you see there's nothing in between those xxx.

      So trying now to update it to ' ' (again a blank):

      Command> update SPA.VODF_CORPORATEDIRECTORY set EMAILADDRESS=' ' where USERDN='+4390921117';

      1 row updated.

       

      Command> select 'xxx'||EMAILADDRESS||'xxx' from SPA.VODF_CORPORATEDIRECTORY where USERDN='+4390921117' and EMAILADDRESS=' ';

      < xxx xxx >

       

      Now the report is different, showing the real blank.

       

      So questions here are actually two:

      1) why is ttBulkCp converting the empty string to a kind blank on a field defined as: EMAILADDRESS                    TT_VARCHAR (100) INLINE

      2) which kind of blank is that string we can find in the DB after ttBulkCp is perfomed

       

      We're not sure if changing to TYpemode=1 (compatible to older TT) may make any difference but we would like to avoid it.

      Thanks in advance!

       

      Cheers,

      Mike