Saving CLOB data

[pavo123]

Hi Isomorphic,

I declared field like this:

        <field name="BIGSTRING" length="100000" type="text" escapeHTML="true" />

When text field is declared as "length>4000", a CLOB is automatically used. That is clear.
When I try to save String value > 4000, I get Oracle DB error:

ORA-01704: string literal too long

So is it possible that you change logic about saving CLOB data to be like:

insert into <table> (clob_column)
  values
  (
      to_clob(' <=4000 symbols ')
    ||to_clob(' <=4000 symbols ')
    ||to_clob(' <=4000 symbols ')
    ...
    ||to_clob(' <=4000 symbols ')
  );

INHO that would solve all possible problems with a CLOB data while inserting in every DB.

Best regards
Pavo

[MarcellKehmstedt]

As far as I can comprehend the situation, the datatype of the db column is correct with CLOB as well as the field type with "text".
But the mapper, that generates the insert DML Statement, shall use parameterized statements and insert the text as a parameter instead of including it as a string literal in the DML statement. Since string literals are limited to 4000 bytes. String parameters do not have that limitation, since the JDBC Driver does translate them to the correct database type.

My Question (as a complete newbie in smartgwt) would be: is there a configuration option for the datasource mapper to make it use parameterized statements? Or do we need to wait for some fix/workaround?

[Blama]

Hi MarcellKehmstedt,

parameter binding for everything is a suggestion I made here.
W.r.t to the problem I'm pretty sure that this should already use parameter binding and is a bug, as BLOB already works this way and I also think that CLOB worked this way before (not sure, though).

Best regards
Blama

[pavo123]

Hi Blama ,

you're right. This is definitly a bug. CLOB worked that way before (using v11.1p_2019-01-17/PowerEdition Deployment (built 2019-01-17)).

Best regards
Pavo

[Isomorphic]

We did indeed previously use parameterized statements for long text values in Oracle, but it had limitations when performing substring filtering. It was changed at the end of January / beginning of February to use precisely the technique the OP suggests - ie, carving long text values up into 4000-char chunks and concatenating. Testing insert of > 4000-char values into an Oracle database with the current codebase shows this to be working as intended, so this is probably just a case of needing to get the latest build.

pavo123, please try your use case with the current build of 12.0 or 12.1 and let us know if you still see the issue.

[pavo123]

Hi Isomorphic,

insert works as you said using current build of 12.0.
But filtering on that field (properties of the field you can see:#1) works like this:

Part of the criteria:

            {
                fieldName:"BIGSTRING",
                operator:"iContains",
                value:"Spitzen"
            },

Part of SQL Statement:

AND (LOWER(TABLE_NAME.BIGSTRING) LIKE LOWER('%spitzen%') ESCAPE'\'  AND TABLE_NAME.BIGSTRING IS NOT NULL)

Best regards
Pavo

[Isomorphic]

That looks correct. Are you saying this does not work correctly?

[pavo123]

Hi Isomorphic,

it works correctly.

Bug from post #1 happens again (using v12.0p_2019-02-26/PowerEdition Deployment (built 2019-02-26))

ORA-01704: string literal too long

when saving CLOB data.

Best regards
Pavo

[Isomorphic]

To be clear: are you saying the problem was fixed but has now reappeared? Or are you saying you get the same Oracle error in slightly different circumstances?

[pavo123]

Hi Isomorphic,

sorry, my fault. That day I tested this bug with 6.1p and with 12.0p so I probably didn't deploy correctly or something like that.
But doesn't matter now. This bug happens in 12.0p current build and it works correctly in 6.1p.

Best regards
Pavo

[Isomorphic]

We have tried this in our own development environment, and also with yesterday's public download of 12.0p, and we cannot reproduce this problem. In both cases, with a dataSource like this:

<DataSource
    ID="longText"
    serverType="sql"
    dbName="Oracle"
    tableName="testLongText"
>
    <fields>
        <field name="pk"            type="sequence"   hidden="true"            primaryKey="true" />
        <field name="theText"    type="text"       title="Long Text"       length="5000"      />
    </fields>
</DataSource>

and a test case like this:

<isomorphic:loadDS ID='longText'/>
var testText = "Lorem ipsum dolor..." // and so on, for 5000 characters
longText.addData({theText: testText}, function(resp, data) {
    alert("OK");
});

we see the server run a query like this:

INSERT INTO testLongText (theText, pk) VALUES (TO_CLOB('Lorem ipsum dolor... etc... for 4000 chars') ||
'Maecenas vestibulum... etc... for 1000 chars', textLongText_pk.NextVal)

If your use case is different from this simple test, please provide more details.

[pavo123]

Hi Isomorphic,

after reading posts #2, #3, INHO your query should look like (the same as in 6.1p):

INSERT INTO testLongText (theText, pk) VALUES (?, textLongText_pk.NextVal)

In my case, your query would look like:

INSERT INTO testLongText (theText, pk) VALUES (TO_CLOB('Lorem ipsum dolor... etc... for 4000 chars Maecenas vestibulum... etc... for 1000 chars', textLongText_pk.NextVal)

If you do not change the query to be like

INSERT INTO testLongText (theText, pk) VALUES (?, textLongText_pk.NextVal)

I'll make a test case for it. Although it's not clear to me why this problem doesn't appear in your case, in my opinion it should.

Best regards
Pavo

[Isomorphic]

As mentioned in post #5, there is a reason why we don't use parameter binding for this case. From your most recent post, we're not clear on what the issue is. Are you saying that 12.0p doesn't generate TO_CLOB() statements, or that it does generate TO_CLOB() statements but that doesn't solve the ORA-1704 problem? Or are you saying that it does generate TO_CLOB() statements and it does solve the problem, but you think it should solve the problem by using parameter binding instead?

[pavo123]

I'm saying that it does generate TO_CLOB() statements but that doesn't solve the ORA-1704 problem. (Using 12.0p 2019-02-28)

.ds.xml

        <field name="MAIL_CONTENT" length="100000" type="text" escapeHTML="true">
            <title><fmt:message key="mailContent" /></title>
        </field>

.java

    private DSResponse addLog(String mailContent) throws Exception {
        DSRequest request = new DSRequest("DatasourceName", DataSource.OP_ADD);
        //Helper.prepareRequest(request, userID, requestContext);

        HashMap<String, Object> valueMap = new HashMap<String, Object>();
        valueMap.put("MAIL_CONTENT", mailContent);
        request.setValues(valueMap);

        return request.execute();
    }

In my case query looks like

insert into <table> (MAIL_CONTENT)
  values
  (
      to_clob(' 15k symbols ')
  );

and not like:

insert into <table> (MAIL_CONTENT)
  values
  (
      to_clob(' <=4000 symbols ')
    ||to_clob(' <=4000 symbols ')
    ||to_clob(' <=4000 symbols ')
    ||to_clob(' 3k symbols ')
  );

[Isomorphic]

In our test case shown above, we have tried increasing the size of the text field to 50K and the size of the value being inserted to 15K, and it still works correctly. The generated SQL is:

INSERT INTO testLongText (theText, pk) VALUES (
    TO_CLOB('4000 chars')
    || '4000 chars',
    || '4000 chars',
    || '3000 chars',
     textLongText_pk.NextVal)

which is functionally equivalent to your example even though it doesn't call TO_CLOB() on each element (because the whole value is defined by the first thing to be concatenated). So we are at a loss to explain why you see different results. Could you post the actual server log that shows the query it generated? You can email it to support@isomorphic.com if you do not want to post it on the public forum.