• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
1

Having issue while inserting value through CF2021

Explorer ,
Mar 06, 2023 Mar 06, 2023

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

TOPICS
Advanced techniques , Database access , Reporting

Views

559

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Explorer , Mar 09, 2023 Mar 09, 2023

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

Votes

Translate

Translate
Explorer ,
Mar 07, 2023 Mar 07, 2023

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)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Mar 07, 2023 Mar 07, 2023

Copy link to clipboard

Copied

Configure the Oracle database to use UTF-8 encoding.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Mar 07, 2023 Mar 07, 2023

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. 


/Charlie (troubleshooter, carehart.org)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Mar 08, 2023 Mar 08, 2023

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.  

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Mar 09, 2023 Mar 09, 2023

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:

  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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Mar 09, 2023 Mar 09, 2023

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Mar 09, 2023 Mar 09, 2023

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

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Mar 09, 2023 Mar 09, 2023

Copy link to clipboard

Copied

LATEST

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation