I use Oracle 10gR2 and the database has charset UTF8.
When i create a varchar2(10) column in a table, and you look at the Servoy dataprovider, Servoy is showing a TEXT (10) column.
I used a length of 10 to keep the test simple, it also happens with 100 or 1000
No when i enter 10 charaters and one or more of these characters is a special character (à or €) that uses more then 1 byte, i get an error
when saving the data the following error is show in the admin page :
java.sql.SQLException: ORA-12899: value too large for column “SERDEVSA_BERP2”.“OFFERTEKAARTEN”.“OFFEKRT_OMSCHR” (actual: 13, maximum: 10)
Then i tryed to create a varchar2(10 char) in Oracle which means that 10 characters ( instead of bytes ) are allowed.
When I look at the Servoy dataprovider now, Servoy is showing a TEXT (40) column ?? .
Now there is the “old” problem, but also another one. The user can now enter 40 characters in the field and when saving
the following error is show in the admin page :
java.sql.SQLException: ORA-12899: value too large for column “SERDEVSA_BERP2”.“OFFERTEKAARTEN”.“OFFEKRT_VERSIE_MOTIVATIE” (actual: 40, maximum: 10)
Does this mean that i have to go back to a WE8ISO8859P1 charset or ???
when i insert <test12 à €> in the field k2 and then query that i get the following result :
SQL> select length(k2),vsize(k2) from testspecial;
LENGTH(K2) VSIZE(K2)
10 13
This is correct.
If Servoy would show the field in the Dataprovider as TEXT(10) then there would be no problem.
This would mean that the user can enter a maximum of 10 characters in the field of the form
and if more then 10 bytes of storage are needed, Oracle can store these.
This issue would mean that if i use UTF8 in Oracle then every time a user fills a field completely to the end
and it contains a special character the save will fail. I can never explain this to a user.
You will still have the same issue when the user fills the longer field.
So i would have to explain to the user that he/she should not fill the field completely !
I have had this problem before. My research led to the following. You can configure Oracle to either allocate bytes to determine the field length or characters (nls_length_semantics parameter, can be BYTE or CHAR). The maximum length for a varchar field is 4000 bytes (!), no matter what other settings you have made. If you configured Oracle to associate the field length with characters rather than bytes, it means that in the worst case a varchar(4000) will only hold 1000 characters if the encoding is one that uses up to 4 bytes per character and your text contains 4byte characters. Now to Servoy. Servoy asks for the field length and Oracle returns 4000. Servoy will then truncate every String to 4000 characters (in Java), which, unfortunately will not be equal to 4000 bytes (because of the encoding that Servoy is not even aware of). So if you use a varchar(4000) and have for example a UTF-8 encoded DB, it is not unlikely that you will run into a problem. We have create a plugin that truncates a String to the number of characters provided the DB encoding. We use this to truncate text in all our Varchar(4000) fields (of course, you can also have a problem with varchar(3000)).
The problem that you have is different, however, although related. You create a varchar2(10) in Oracle and Servoy sees that as a Text(40). Now when you try to enter a text with “funny” characters, Servoy will make sure it is truncated to 40 chars, but that, of course, is too much. So why does Servoy see a Text(40)? In my case, it was simply the wrong JDBC driver. After downloading the exact (!!) JDBC driver for the DB version I was using, the problem vanished. So I recommend that you download the precise driver for the DB version you are using. In an Oracle environment, it really makes a big difference if your DB is 10.2.0.4 an your driver is made for 10.2.0.3.
There is also the “4000” problem , i know. But there you get another Oracle error, not this issue.
I was planning to solve that exactly as you do, use a maximum of for instance 3000.
I even found a way to check in the onDataChange of the field if the number of bytes (not chars) exceeds 4000.
Then i can give an error message to the user and let him change the data.
But that error is not the issue when using 10 or 100 long varchar2 fields.
I am using 10.2.0.1.0 and as far as i know i am using the jdbc driver for that version.
But i will di some testing with other versions.
But do you say that if you create a varchar2(10 char) column and then look at it in Servoy dataprovider
it is a TEXT(10) column ??
That would be a great releive to me, because i would know that i could work !!
Problem is solved, it was the Oracle jdbc driver.
I upgraded the database from 10.2.0.1.0 to 10.2.0.4.0 and after that copied the ojdbc14.jar from the
Oracle directory to the Servoy directory.
According to Oracle the problem is also solved in 10.2.0.3.0
In 10.2.0.2.0 you can solve it by installing a specific patch (patchno = 4485954)
After that a varchar2(10 char) is shown as TEXT(10) in Servoys dataprovider.