Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
Clear All
new posts

  • pavo123
    started a topic Saving CLOB data

    Saving CLOB data

    Hi Isomorphic,

    I declared field like this:
    Code:
            <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:
    Code:
    ORA-01704: string literal too long
    So is it possible that you change logic about saving CLOB data to be like:
    Code:
    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
    Last edited by pavo123; 14th Feb 2019, 02:59.

  • claudiobosticco
    replied
    I can confirm it's fixed, thank you very much

    Leave a comment:


  • Isomorphic
    replied
    This is fixed and is available for download in nightly builds since Apr 22 (today). Thank you for the report.

    Leave a comment:


  • claudiobosticco
    replied
    SmartClient Version: SNAPSHOT_v12.1d_2019-04-15/EVAL Deployment (expires 2019.06.14_07.21.11) Licensed to: Isomorphic Software (#ISC_EVAL_NIGHTLY)

    Oracle 12c (12.2.0.1.0)

    Hello, maybe the mentioned changes in this area have introduced a bug .

    If I define a length on a text field (clob on the db) and this length is greater than 4000 (ie 4001), when I save a text (which actually is a short text, as a single word, say foobar), it saves on the db 'foobar' with single quotes around it.
    Both for updates and inserts, If you save multiple times, it adds new quotes every time.

    Note: it seems to have the same bug without the length and with type="clob", which however is not documented.

    Leave a comment:


  • Blama
    replied
    Hi Isomorphic,

    using v12.0p_2019-03-23 I can see the behavior with CLOBs changed, but I have trouble understanding way and it feels wrong to me.

    Originally posted by Isomorphic View Post
    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.
    Originally posted by Isomorphic View Post
    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?
    I can see the mentioned behavior for INSERTs with
    Code:
    TO_CLOB('4000 chars')
         || '4000 chars',     || '4000 chars',     || '3000 chars')
    but is this really correct (talking only about INSERTs here)? Also, I see ...' || '... after 1000 CHAR, but this is OK for me.

    Although I can't find the thread right now I'm pretty sure the reason for the change you mention in the 1st linked post "It was changed at the end of January / beginning of February" was also by me. It was about WHERE conditions on those fields for SELECTs. Can you explain/link the thread?

    For INSERTs the new non-parameter binding way generates VERY big SQLs and also the same amount of log data. To me, this just don't feels correct.

    Thank you & Best regards
    Blama
    Last edited by Blama; 27th Mar 2019, 06:44. Reason: formatted

    Leave a comment:


  • pavo123
    replied
    Thank you very much!

    Leave a comment:


  • Isomorphic
    replied
    A fix for this problem has now been committed on 12.0 and 12.1 branches. Please try with tomorrow's builds (dated March 5th)

    Leave a comment:


  • Isomorphic
    replied
    Thank you, we are able to reproduce the problem with the information you sent. The issue is that your text contains extended-ASCII characters - German characters like - which are being converted into 2-byte UTF8 representation on their way into the database (this obviously assumes that your Oracle database is using the UTF8 character set, but we think that's a safe assumption as it is the default and that conversion is the reason for the error in our repro case). We are discussing internally the best way to deal with this, and will post back here when there is more to say.

    Leave a comment:


  • pavo123
    replied
    Hi Isomorphic,

    I sent you an e-mail.

    Best regards
    Pavo

    Leave a comment:


  • Isomorphic
    replied
    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:
    Code:
    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.

    Leave a comment:


  • pavo123
    replied
    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
    Code:
            <field name="MAIL_CONTENT" length="100000" type="text" escapeHTML="true">
                <title><fmt:message key="mailContent" /></title>
            </field>
    .java
    Code:
        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
    Code:
    insert into <table> (MAIL_CONTENT)
      values
      (
          to_clob(' 15k symbols ')
      );
    and not like:
    Code:
    insert into <table> (MAIL_CONTENT)
      values
      (
          to_clob(' <=4000 symbols ')
        ||to_clob(' <=4000 symbols ')
        ||to_clob(' <=4000 symbols ')
        ||to_clob(' 3k symbols ')
      );
    Last edited by pavo123; 28th Feb 2019, 07:09.

    Leave a comment:


  • Isomorphic
    replied
    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?

    Leave a comment:


  • pavo123
    replied
    Hi Isomorphic,

    after reading posts #2, #3, INHO your query should look like (the same as in 6.1p):
    Code:
    INSERT INTO testLongText (theText, pk) VALUES (?, textLongText_pk.NextVal)
    In my case, your query would look like:
    Code:
    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
    Code:
    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
    Last edited by pavo123; 28th Feb 2019, 05:30.

    Leave a comment:


  • Isomorphic
    replied
    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:
    Code:
    <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:
    Code:
    <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:
    Code:
    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.

    Leave a comment:


  • pavo123
    replied
    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

    Leave a comment:

Working...
X