🚀 go-pugleaf

Hi "news",

> Ive found a problem where my app is getting VARCHAR2 fields with their 
> trailing space trimmed. A packet trace shows that the data is getting to my 
> client box properly, so the trim happens somewhere in BDP code.

I thought that VARCHAR fields were always truncated?

Groetjes,
           Bob Swart

-- 
Bob Swart Training & Consultancy (eBob42.com)  Forever Loyal to Delphi
Blog: http://www.drbob42.com/blog - RSS: http://drbob42.com/weblog.xml
New Delphi 2006 Courseware e-books at http://www.eBob42.com/courseware

"Bob Swart" <b.swart@chello.nl> wrote in message 
news:4586F404.1060904@chello.nl...
> Hi "news",
>
>> Ive found a problem where my app is getting VARCHAR2 fields with their 
>> trailing space trimmed. A packet trace shows that the data is getting to 
>> my client box properly, so the trim happens somewhere in BDP code.
>
> I thought that VARCHAR fields were always truncated?
>
> Groetjes,
>           Bob Swart


I don't believe so. Anyways, I do have data in my database which has 
trailing spaces. Those spaces aren't making it to my client code. This is a 
problem because I am doing the WHERE clause style of concurrency checking... 
so my WHERE clause never matches in these cases since I don't have the same 
data the server has.

I should point out that I am using Oracle (v8 and v9).

I just created a simple test bed application with two text boxes.
One text box is bound to a dataset generated and filled by BDP.
The other text box is bound to a dataset generated and filled by MS Oracle 
provider.
Both are querying the exact same table.field.
MS Oracle provider is getting showing the data correctly with all trailing 
spaces intact.
BDP is not.

Is this a bug? any ideas?

thanks :) 

news.borland.com wrote:
> Ive found a problem where my app is getting VARCHAR2 fields with their
> trailing space trimmed. A packet trace shows that the data is getting
> to my client box properly, so the trim happens somewhere in BDP code.
>
> How can I get the exact text, I dont want it trimmed.

Then use CHAR fields, not VARCHAR. The definition of VARCHAR is that data 
*should* be returned with any trailing blanks trimmed.

-- 
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"At the apex of every great tragedy of mankind there stands the figure
of an incorruptible altruist." - Ayn Rand 

"Wayne Niddery [TeamB]" <wniddery@chaffaci.on.ca> wrote in message 
news:4587fdf9$1@newsgroups.borland.com...
> news.borland.com wrote:
>> Ive found a problem where my app is getting VARCHAR2 fields with their
>> trailing space trimmed. A packet trace shows that the data is getting
>> to my client box properly, so the trim happens somewhere in BDP code.
>>
>> How can I get the exact text, I dont want it trimmed.
>
> Then use CHAR fields, not VARCHAR. The definition of VARCHAR is that data 
> *should* be returned with any trailing blanks trimmed.
>
.Net v1.1, Oracle 8 & 9i

Well it is apparently not Oracle's definition by default. I see that it is 
for SQL Server and that they have an ANSI_PADDING option. Regardless, this 
is the server's responsibility. Why is BDP enforcing this in client side 
code? And why only unidirectionally? i.e. only on SELECT not on UPDATE.

I cannot alter the tables to use CHAR, project manager won't allow it. This 
is a show stopper for me. As I mentioned in another post I am using WHERE 
clauses for concurrency/synchonization. Its just not going to work if I 
can't GET what is actually on the DB.?! I wonder if theres any chance of 
getting this fixed?

I could do trimming locally on all strings before any UPDATE, and then fix 
all the existing data, and hope trailing spaces never find their way in. or 
maybe load my WHERE clause up with trim() statements to trim server side 
values... but I dont like it.. better to start porting to ODP or MS data 
providers I think. 

news.borland.com wrote:
>
> Regardless, this is the server's responsibility. Why is BDP enforcing
> this in client side code? And why only unidirectionally? i.e. only on
> SELECT not on UPDATE.

I do agree that if the server stores trailing blanks you enter then you 
should also be able to select them back. After checking a bit more I realize 
I need to modify my statement about the difference a little as well. The 
difference is that, with char, the database will *pad* anything you enter to 
the defined length - i.e. will add trailing blanks. With varchar it won't, 
however you can still include trailing blanks yourself, they're just treated 
as any other character. Caveat is that each product may still have slightly 
different rules about this, but this is the most common AFAIK.

> I cannot alter the tables to use CHAR, project manager won't allow
> it. This is a show stopper for me. As I mentioned in another post I
> am using WHERE clauses for concurrency/synchonization. Its just not
> going to work if I can't GET what is actually on the DB.?! I wonder
> if theres any chance of getting this fixed?

I've looked in QC and don't see anything specific about this, but please do 
enter something there in this area:
http://qc.codegear.com/wc/qcmain.aspx?da=4572

Are the fields always being padded to the defined field length, or could 
there be *some* trailing blanks but still with a total string length less 
than the max defined field length? If always padded fully, then the 
application could pad out what it gets after the select. In the latter case 
though there doesn't appear to be any good work around.

Perhaps others here using Oracle might be able to suggest something.

> I could do trimming locally on all strings before any UPDATE, and
> then fix all the existing data, and hope trailing spaces never find
> their way in. or maybe load my WHERE clause up with trim() statements
> to trim server side values... but I dont like it.. better to start
> porting to ODP or MS data providers I think.

Personally, I do tend to trim such fields, or if I fail to do so, am happy 
to get them back trimmed, so for me, I'm curious as to why the trailing 
blanks are are actually important (not a criticism, I just can't imagine a 
situation where I would ever actually want trailing blanks, so I'm curious).

Oh, and could you change your posting name from "news.borland.com" to 
something else? Thanks.
-- 
Wayne Niddery - Winwright, Inc (www.winwright.ca)
"A man is likely to mind his own business when it is worth minding,
when it is not, he takes his mind off his own meaningless affairs by
minding other people's business." - Eric Hoffer 

> I've looked in QC and don't see anything specific about this, but please 
> do enter something there in this area:
> http://qc.codegear.com/wc/qcmain.aspx?da=4572
>
> or could there be *some* trailing blanks but still with a total string 
> length less than the max defined field length?

Yes this is the case. Ok I created bug report: Report #:  37941, thanks for 
the tip.

> Personally, I do tend to trim such fields, or if I fail to do so, am happy 
> to get them back trimmed, so for me, I'm curious as to why the trailing 
> blanks are are actually important (not a criticism, I just can't imagine a 
> situation where I would ever actually want trailing blanks, so I'm 
> curious).
>
> Oh, and could you change your posting name from "news.borland.com" to 
> something else? Thanks.

Sorry, that must have been the default posting name.
The trailing blanks aren't really 'important' to preserve. Its just a 
problem of existing data and also I have to operate side by side with 
another application that I can't really modify or control. Why are the 
spaces in the data? I dont know, blame the users. :)

john wrote:
>
> The trailing blanks aren't really 'important' to preserve. Its just a
> problem of existing data and also I have to operate side by side with
> another application that I can't really modify or control. Why are the
> spaces in the data? I dont know, blame the users. :)

Ok, then in my opinion (which cannot of course supercede your own if it 
differs <g>) is that you don't have to do anything, it'll be ok if your app 
strips them and puts them back. The only possible issue I see is if there 
are full string comparisons being made in the other app rather than sql 
"like xxx%", but then if users are putting essentially random trailing 
blanks in anyway, then this is already either a problem or not and if it is, 
your app won't make things worse and will gradually make them better.

-- 
Wayne Niddery - Winwright, Inc (www.winwright.ca)
Working for yourself is great because you get to work half days, and
you can choose any twelve hours you want.