Announcement

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

  • 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.

  • #2
    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?

    Comment


    • #3
      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

      Comment


      • #4
        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
        Last edited by pavo123; 18th Feb 2019, 02:25.

        Comment


        • #5
          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.

          Comment


          • #6
            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:
            Code:
                        {
                            fieldName:"BIGSTRING",
                            operator:"iContains",
                            value:"Spitzen"
                        },
            Part of SQL Statement:
            Code:
            AND (LOWER(TABLE_NAME.BIGSTRING) LIKE LOWER('%spitzen%') ESCAPE'\'  AND TABLE_NAME.BIGSTRING IS NOT NULL)
            Best regards
            Pavo

            Comment


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

              Comment

              Working...
              X