[Building Sakai] truncating to fit field

[Charles Hedrick]

It seems to me that the best approach would be to find all the .sql files and add “char” to all the definitions. That sounds like something to do for Sakai 11.

On Feb 28, 2014, at 10:10 AM, Brian Jones <bjones86 at uwo.ca> wrote:

> We run with Oracle DB's at Western and we've had problems with this in the
> past. Many, many columns are created as VARCHAR2 (XXX), where XXX is a
> number of BYTEs rather than CHARs.
> 
> From the documentation we've read, and according to our DBA, it is not
> recommended to set NLS_LENGTH_SEMANTICS=char.
> 
> I think it would be better if the auto.ddl scripts created the columns
> explicitly as VARCHAR2 (XXX CHAR).
> 
> Brian Jones
> Programmer/Analyst
> Information Technology Services
> Support Services Building, Room 4326
> Western University
> (519) 661-2111 x86969
> bjones86 at uwo.ca
> 
> From: sakai-dev-bounces at collab.sakaiproject.org
> [mailto:sakai-dev-bounces at collab.sakaiproject.org] On Behalf Of Sam
> Ottenhoff
> Sent: Friday, February 28, 2014 9:43 AM
> To: Charles Hedrick
> Cc: sakai-dev
> Subject: Re: [Building Sakai] truncating to fit field
> 
> We already have code in Sakai to check whether the database connection can
> read/write proper UTF-8.  I would be fine with adding another check for
> Oracle semantics, but it doesn't make much sense to me to rewrite all insert
> string truncation code to deal with an Oracle db that isn't multi-byte
> preferred.
> 
> On Fri, Feb 28, 2014 at 8:21 AM, Charles Hedrick <hedrick at rutgers.edu>
> wrote:
>> 
>> The Oracle web page recommends not using “char” in the declaration, but
> instead setting NLS_LENGTH_SEMANTICS to char. However unless I have some
> assurance that sakai sitss actually set NLS_LENGTH_SEMANTICS, I need to
> assume the worst.
>> 
>> On Feb 27, 2014, at 5:12 PM, Sam Ottenhoff <ottenhoff at longsight.com>
> wrote:
>> 
>>> Adding code to work around this seems wrong.  Isn't the issue that your
> database was setup with a non-multibyte character set which means that the
> length calculations Oracle is making are based on one byte = one character?
>  Our confluence page
> (https://confluence.sakaiproject.org/display/I18N/Common+UTF-8+Problems)
> recommends "AL32UTF8".
>>> 
>>> Can any of our Oracle-using institutions chime in here?  If the database
> is setup initially with a multi-byte character set, are the tables created
> correctly where the length of SAKAI_SITE_GROUP/TITLE is 99 multi-byte
> characters and not 99 bytes?
>>> 
>>> --Sam
>>> 
>>> 
>>> 
>>> On Thu, Feb 27, 2014 at 4:49 PM, Charles Hedrick <hedrick at rutgers.edu>
> wrote:
>>> my new code uses this: https://gist.github.com/lpar/1031951, except for
> mysql, where a substring will do (because it interprets limits as
> characters, not bytes).
>>> 
>>> Yuck.
>>> 
>>> This is LSNBLDR-352.
>>> 
>>> On Feb 27, 2014, at 3:54 PM, Steve Swinsburg <steve.swinsburg at gmail.com>
> wrote:
>>> 
>>>> The field is declared wrong, if it's going to store utf8 but use
> substrb or substrc to trim the string in bytes or utf8 chars.
>>>> 
>>>> Cheers
>>>> Steve
>>>> 
>>>> sent from my mobile device
>>>> 
>>>> On 28/02/2014 3:29 AM, "Charles Hedrick" <hedrick at rutgers.edu> wrote:
>>>> I am generating a group name. The maximum length is 99 characters. In
> mysql it appears that truncating to 99 characters is fine, because mysql
> interprets field lengths in characters.
>>>> 
>>>> However in Oracle, unless the declaration uses “char”, it is understood
> to be bytes. That means that UTF-8 characters can take more than one space.
> Is there some standard method in Sakai to truncate a string to a length that
> will fit into a given database field?
>>>> 
>>>> Unfortunately, the SAKAI_SITE_GROUP is defined in oracle using field
> names without “char” after them.
>>>> 
>>>> 
>>>> _______________________________________________
>>>> sakai-dev mailing list
>>>> sakai-dev at collab.sakaiproject.org
>>>> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>>>> 
>>>> TO UNSUBSCRIBE: send email to
> sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of
> "unsubscribe"
>>> 
>>> 
>>> _______________________________________________
>>> sakai-dev mailing list
>>> sakai-dev at collab.sakaiproject.org
>>> http://collab.sakaiproject.org/mailman/listinfo/sakai-dev
>>> 
>>> TO UNSUBSCRIBE: send email to
> sakai-dev-unsubscribe at collab.sakaiproject.org with a subject of
> "unsubscribe"
>>> 
>> 
>