Copy link to clipboard
Copied
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 (
('شركة يخت انترناشيونال للملاحة البحرية ذ م م ') )
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");
}
Copy link to clipboard
Copied
Corrections:
[Macromedia][Oracle JDBC Driver][Oracle]ORA-12899: value too large for column "UATALL"."PASS"."COMPANY_NAME" (actual: 80, maximum: 50)
Copy link to clipboard
Copied
Configure the Oracle database to use UTF-8 encoding.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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:
For a way to configure UTF-8 on Oracle, search the web.
Copy link to clipboard
Copied
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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");
}