2

We are trying to insert a large string into a table column and getting an error "length can't exceed maximum length(8388607 bytes)". (0x7F FFFF). The input data field length exceeds 10MB.

HANA version SPS 9 (Rev 97)
Data type of variable and table column is CLOB
Using INSERT in a SQLSCRIPT Stored Procedure

The HANA data types documentation say that maximum length of any LOB object is 2GB (0x7FFF FFFF). Our string length is well within this limit. So this is very confounding. Will appreciate any hints to resolve this.

Thanks a lot.

---------- CODE

CREATE PROCEDURE XXX_SCHEMA.PROC_INSERT_INTO_CLOB 
    ( IN DATA_CLOB CLOB,  ) 
    BEGIN
 LANGUAGE SQLSCRIPT SQL SECURITY INVOKER default schema XXX_SCHEMA AS               
        INSERT INTO "XXX_SCHEMA"."XXX::DB_YY_CLOB"
            (
                'ABC'   ,
                CURRENT_TIMESTAMP ,
                DATA_CLOB       
           )
            SELECT F1,
                  F2,
                  :DATA_CLOB
            FROM DUMMY ;
    END;


-- Table Defintion
table.schemaName =  "XXX_SCHEMA";
table.tableType = ROWSTORE;
table.columns = [
{name = "F1";sqlType = NVARCHAR;nullable = false; length = 3;},
{name = "F2";sqlType = TIMESTAMP;nullable = true;},
{name = "DATA_CLOB";sqlType = CLOB;nullable = true;}];
Dinesh
  • 4,437
  • 5
  • 40
  • 77
  • Without seeing your code and the table definition providing a reasonable answer is not possible. Please add more details to your question. – Lars Br. Mar 06 '16 at 08:10
  • @LarsBr. added table definition and sample code. The actual code is too vast to pinpoint the use case. The invocation is from XSJS. – Dinesh Mar 07 '16 at 18:10

1 Answers1

0

The reason for the error is that you seem to use string methods to deal with the CLOB data. When I tried simple things like inserting a really long value generated via

update rclob set data_clob = lpad ('X', 2000000000, 'Y');

I also received the error message

Could not execute 'update rclob set data_clob = lpad ('X', 2000000000, 'Y')'
SAP DBTech JDBC: [384]: string is too long: length can't exceed maximum length(8388607bytes) at function lpad() (at pos 29)

Since LPAD produces a string before it gets entered into the CLOB, the error message is thrown before the CLOB column is actually touched.

Generally LOB columns can only be inserted by binding the data to a parameter in the insert statement.

Lars Br.
  • 9,949
  • 2
  • 15
  • 29
  • This is called from XSJS which only has type `string` but also has a method `setClob`. My XSJS binds a proc using `PrepareCall` which in turn returns per-column DB type informing how each parameter is set. Next we execute the prepared statement. I will appreciate if you could please show in `XSJS` what needs to be changed. Thanks. – Dinesh Mar 08 '16 at 23:40