Hello All,

 I need suggestion for selecting the data from a table after decrypting from CLOB column type.

Data is being stored in a table which has LVARCHAR data type(earlier created) in encrypted format and then inserting in another table (newly created) which has CLOB data type in SBSPACE.

But while selecting data with  decrypting it is giving error. 

Below is the details:

Source table

==========

              create table "informix".cs_cons_phto

  ( seqn integer not null ,

                phto_file lvarchar(32739)

Target Table 

==========

create table "informix".cs_csid_phto_00 (

seqn            Integer not null,

phto_file       clob    null

) PUT phto_file IN (sbs001);

Inserting data in new table:

==========================

Inserting the data into target table , inserting the lvarchar into clob column which is in the target table

INSERT INTO cs_csid_phto_00 ( seqn, phto_file)

SELECT     3,   ENCRYPT_AES( DECRYPT_CHAR(phto_file, 'MDID ON DB KEY.')::CLOB,     'mySecretPassword'  )

FROM cs_cons_phto where seqn = 999999;

9632: Value does not match the type of column (phto_file).

and if try to select get below error: (another sequence)

select DECRYPT_CHAR( phto_file, 'MDID ON DB KEY.' )
from cs_csid_phto_00 where seqn = 4;

26005: The encrypted data is wrong or corrupted.

Kindly Suggest, what should be done.

Thanks

Amit 

Looks like the target table's phto_file column needs to be BLOB rather than CLOB to avoid the 9632 error, but I'm not sure this is correct behavior (of the ENCRYPT_AES() function).
Per the documentation the function's return data type should be the same as that of its data argument, but your case seems to be creating a bit of ambiguity between the source column's data type, decrypt_char() return type, the CLOB cast and the target column's type.

Otoh, have you considered moving the encrypted data as is from that LVARCHAR column to the CLOB column?  Or do you have to de- and re-encrypt the data, for some reason?

HTH,
 Andreas

Dear Andreas,

     Thanks for your reply.

   Yes, initially data stored as LVARCHAR column, and now want to move to new Table with large object, so created table with CLOB.

   I can also try to use BLOB, and will try the same process.

Thanks 

Amit Patel