Toad World® Forums

chr(0) null string issue


#1

In some C programmng language written applications the string terminator is a chr(0) value. If this value is inserted into an Oracle database. This issue that I have if this value is found anywhere in the string you will not see the characters that follows this value.

Test script is as follows:
create table qtest (c1 varchar2(64));

insert INTO qtest (c1)
values(‘some string’||chr(0)||‘test’);

select c1, dump(c1)
from qtest
/

I have purposely placed the chr(0) in the middle of my string.

If the above test is run in any non-C written tool I get the the following test ‘some string test’. If I use toad, which is apparently a C written program, I get ‘some string’.

This does not help if you are analysing data results from a bit of sql using TOAD. Any ideas how to get around this quickly with some configuration?

Cheers
Q


#2

Hey Quintin,

For what it’s worth, I’ve been able to duplicate this behavior on Toad
10.6.1 GA and the latest beta against a 10.1 DB with AL32UTF8 charset. The
important part is that the results of the query do not match the output from
SQL*Plus.

So I’d say you found a bug. :frowning: And I’m sorry that you have nulls
mid-string in your DB! EEEEEEEW!

Also, for the record, Toad’s written in Delphi. :slight_smile:

Rich – [TeamT]

Disclaimer: We’ll never forget you, Brent!

In some C programmng language written applications the string terminator is
a chr(0) value. If this value is inserted into an Oracle
database. This issue that I have if this value is found anywhere in
the string you will not see the characters that follows this value.

Test script is as follows:
create table qtest (c1 varchar2(64)); insert INTO qtest (c1)
values(‘some string’||chr(0)||‘test’); select c1,
dump(c1)
from qtest
/


#3

Inserting null characters (and other non-printables) in the middle of a string
is … hmm … not a very good idea. Not only C apps won’t process it
correctly (Toad is Delphi). Those nulls will cause problems from now on till the
end of the world, and they’ll byte you when you least expect it. J

My 2 cents.

Andre


#4

Inserting null characters (and other non-printables) in

the middle of a string is … hmm … not a very good idea.

Perhaps… however SQL Plus does handle it and provides the data. I leave the
philosophical ramifications of whether or not that’s right/wrong up to others
to discuss and simply point out Oracle (at least the database and SQLPlus tool)
handles it :wink:

Roger S.


#5

Morning all,

Perhaps… however SQL Plus does handle it and provides the
data. I leave the philosophical ramifications of whether or
not that’s right/wrong up to others to discuss and simply
point out Oracle (at least the database and SQL*Plus tool)
handles it :wink:

SQLPlus is, I believe, written in C and so should suffer from the NULL
character - chr(0) - terminating the string. However, I am of the
opinion that what SQL
Plus does behind the scenes in some OCI sort of
way, is not to retrieve a VARCHR2 or CHAR as a simple C string but as an
array of bytes the length of which is known and defined by the length
indicator (for VARCHAR2) or the column definition (for CHAR). I assume
that when passing said data around, the length is also passed - at least
in SQL*Plus anyway.

So a VARCHAR2(100) with a data length of 17, containing any number of
chr(0) will be returned as an array of bytes 17 in length and not as a
string of length 17 because the string would be terminated at the first
chr(0) as C strings (and I use the term loosely) normally are.

I rather suspect the fact that a C string is actually just an array of
bytes, terminated by a chr(0), that chr(0) is not the problem. It’s more
likely to be the routines used to display/print/send said array of bytes
back to the application that is at fault.

If the routine takes a plain array of bytes as input then it will,
unless given a length to work with, stop after the first chr(0).

In the definition for the (scalar) OCIDefineXXXXX functions, there are
two parameters of note:

  • Valuep which is a pointer to a buffer to hold the result of a SELECT,
    for example.

  • Rlenp which is a pointer to a variable to receive the length of the
    returned data. So, technically, a chr(0) in the middle of a VARCHAR2
    won’t cause truncation of the data.

Now the “lazy” developer could just copy the data from the buffer
without considering the length and potentially truncate the result at
the first chr(0) - possibly using strcpy() which terminates after
copying the chr(0).

However, if the developer considers the length of the returned data and
copies that many bytes then chr(0) will also be copied. Using memcpy()
or memmove() for example, copies a given number of bytes regardless of
any chr(0) in their midst.

Unfortunately, what happens after the OCI system returns the data to the
calling (OCI Components?) is anyone’s guess.

My suspicions fall on either how the OCI component developer(s) did what
they did or how Delphi handles a chr(0) in a byte stream.

Hope I didn’t bore you all too much! :wink:

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk


#6

Hope I didn’t bore you all too much! :wink:

Nah… part of me was always interested in working with the C family (cept
the windows parts) so I found it interesting in an academic fashion.

Roger S.