File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes getting different number for LENGTH() in anon block vs sql pckg Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "getting different number for LENGTH() in anon block vs sql pckg" Watch "getting different number for LENGTH() in anon block vs sql pckg" New topic
Author

getting different number for LENGTH() in anon block vs sql pckg

Matt Kohanek
Village Idiot
Ranch Hand

Joined: Apr 04, 2009
Posts: 483

Hello

This is confusing me a bit. I set up an anonymous block to see what LENGTH would return on a String - I need to check that it will match the value of the length property from javascript

Here is the block (note there is a line break in v_var)

I run this, and it returns 21 as I would expect. I enter this into a textarea where I have some javascript returning the length property, also 21.

When I submit that text area however...
The value of the textarea is mapped to a param

Both of the values getting logged there are 22 - for each line break I have, it gets counted as two characters. Now I understand why this would be counted as such, with Kanji and other special characters out there - although, if that is the case, wouldn't all characters, even the 'normal' ones equal 2 characters there?
My question is, what is the difference between here and the anonymous block I ran earlier?
I guess it must have something to do with the difference between SQL and PL SQL (I think of this as I have typed most of this thread out...)

So what about my main problem then, which is getting the count I have in the package to be the same count returned by the javascript length property?
I do not want to pass something so insignificant into the procedure as a parameter - that is just not going to happen. Is there some SQL magic I can use to get a count I would receive from javascript in my procedure? - I don't know, something like converting to a CLOB and then getting the LENGTH (I will have to try that actually...)
Is there any way to reconcile these LENGTH values?

Thanks


True wisdom is in knowing you know nothing - Socrates
Koen Aerts
Ranch Hand

Joined: Feb 07, 2012
Posts: 344

Line breaks are either 1 byte, or 2 bytes long. Generally on UNIX/Linux platforms it's 1 byte, while on Windows it's 2 bytes. So quite possibly if I type text in a textbox in Firefox in Linux, the line breaks are submitted as a 1 byte character. If I do the same in Firefox on Windows, the page break will be 2 bytes long. More details on the difference of carriage returns can be seen here: http://koenaerts.ca/dos2unix-equivalent/

So best thing to do on the server side, is to convert 2-byte carriage returns to a 1-byte equivalent, and then do your character count. This can be easily done in your Oracle stored procedure - i.e. replace(my_string_value, chr(13))
Matt Kohanek
Village Idiot
Ranch Hand

Joined: Apr 04, 2009
Posts: 483

Yeah I am not sure why I didn't think to just replace line returns with a blank space or something (for the count sake only, not changing input values)
I think I was too focused on learning about the differences between different length functions and the like
Thanks though, I will just go with this solution.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: getting different number for LENGTH() in anon block vs sql pckg