Skip to main content
F
Faizan278243197lsr
Inspiring
March 7, 2023
Answered

Having issue while inserting value through CF2021

  • March 7, 2023
  • 2 replies
  • 1093 views

Having issue while inserting value through CF2021.

I have a table named Pass having a column COMPANY_NAME VARCHAR2(50 BYTE). While inserting arabic value shows error 

[Macromedia][Oracle JDBC Driver][Oracle]ORA-12899: value too large for column "UATALL"."GATE_PASS"."COMPANY_NAME" (actual: 80, maximum: 50)

though, the value I am insering here actually is 43 charectors only but showing actual value 80 while inserting: 

INSERT INTO PASS(COMPANY_NAME) VALUES (
('شركة يخت انترناشيونال للملاحة البحرية ذ م م ') )

    This topic has been closed for replies.
    Correct answer Faizan278243197lsr

    Once again, "ColdFusion uses 2 bytes per character when it stores strings."

    Therefore, ColdFusion may have stored the 43 characters as double the number of bytes!

     

    Do the following test:

     

    <cfscript>
testString="شركة يخت انترناشيونال للملاحة البحرية ذ م م";
writeOutput("<p>Number of characters = " & len(testString) & "</p>");

stringObject = createObject("java", "java.lang.String").init(javaCast("string", testString));
writeOutput("<p>Number of bytes = " & arrayLen(stringObject.getBytes()));	
</cfscript>

     

    The result is:

    Number of characters = 43

Number of bytes = 79

     


    Used javascript function to count any language characters length.
    So that I can restrict users to not exceed character limit. Issue is resolved. Thank you so much for your help BKBK.

    var cmp_name_val = encodeURIComponent($('#cmp_nm').val()).replace(/%[A-F\d]{2}/g, 'U').length;
    if (cmp_name_val > 49) {
    alert("Company name in English cannot be more than 50 characters. In Arabic cannot be more than 25 characters. Avoid using any mixed characters");
    }

    2 replies

    Charlie Arehart
    Community Expert
    Charlie ArehartCommunity Expert
    Community Expert
    March 7, 2023

    Are you saying this same code worked with the same db in some earlier version of cf? If so, what version? And do you still have access to its cf admin, to compare closely the dsn settings for this db in each--especially the "connection string" and the "advanced settings"? Someone may have configured the previous version to change the defaults, while your cf2021 settings remain the defaults.

     

    Also, are the dsns both the same db driver type? One could be Adobe's oracle driver while the other is using Oracle's driver configured as an "other" driver. 

    /Charlie (troubleshooter, carehart. org)
    F
    Faizan278243197lsrAuthor
    Inspiring
    March 9, 2023

    I never noticed before or in any previous version.

    But now I am looking into this, the string length counting perfectly at interface level but when inserting into DB not accepting string due to exceeding max characters of that column.  

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    March 9, 2023

    Did you apply my suggestion on encoding?

    Make sure that the encoding for ColdFusion and for the Oracle is UTF-8.

     

    The reason is as follows. ColdFusion uses 2 bytes per character when it stores strings. So the string length in bytes is not necessarily the same as the number of characters. In ColdFusion the string length in bytes depends on the character encoding. For details, see for example https://helpx.adobe.com/coldfusion/developing-applications/developing-cfml-applications/developing-globalized-applications/handling-data-in-coldfusion.html 

     

    For ColdFusion, one way to set UTF-8 encoding is as follows:

    1. Log into the ColdFusion Administrator
    2. Go to Server Settings > Java and JVM.
    3. Under JVM Arguments add (if it is not yet there): -Dfile.encoding=UTF8
    4.  Restart ColdFusion. ColdFusion will then have UTF-8 encoding

     

    For a way to configure UTF-8 on Oracle, search the web.

    F
    Faizan278243197lsrAuthor
    Inspiring
    March 7, 2023

    Corrections:

    [Macromedia][Oracle JDBC Driver][Oracle]ORA-12899: value too large for column "UATALL"."PASS"."COMPANY_NAME" (actual: 80, maximum: 50)

    BKBK
    Community Expert
    BKBKCommunity Expert
    Community Expert
    March 7, 2023

    Configure the Oracle database to use UTF-8 encoding.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices