Having issue while inserting value through CF2021

[Faizan278243197lsr]

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 (
('شركة يخت انترناشيونال للملاحة البحرية ذ م م ') )

[Faizan278243197lsr]

Corrections:

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

[BKBK]

Configure the Oracle database to use UTF-8 encoding.

[Charlie Arehart]

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. 

[Faizan278243197lsr]

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]

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

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.

[Faizan278243197lsr]

BKBK,
I have done changes but issue remain same. Here I am attaching screenshot, the changes I have done in JVM setting and error getting while inserting.

[BKBK]

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

[Faizan278243197lsr · Accepted solution]

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");
}