Re: Ugly embedded SQL behaviour

> I'm irritated at Oracle's embedded SQL (Pro*c) product. I
> found a bug that cost me two days on a tight schedule. I'm
> posting this so other folk won't hit the same trap.

RTFM -- it is not a bug; indeed, the behavior you describe is documented. I refer you to page number 1-16 of the Pro*C supplement to the Oracle Precompilers Guide, version 1.3:

"When you use a character array as a host variable, ORACLE strips trailing blanks (but *not* nulls) from right to left (highest element to lowest.)

"Be careful. An uninitialized memory location can contain null characters. To make sure they are not inserted into the database or used in a WHERE clause, you must blank-pad the array to its length (that is, fill unused character positions with blanks). ..."

> It turns out that, while buf contains exactly the text
> implied by the strcpy, terminating nul character and
> all, the Execute immediate parsing didn't stop when
> it saw the nul character. It looked deeper into the
> buffer, saw the remains of other strings, and declared
> the error.
> When I shrunk the size of buf, and did a memset(buf, 0, sizeof buf)
> before loading any text into buf, the problem went away. Isn't
> that special!

Quite special. I suggest you have a look at the manuals.  

> If you are considering using Oracle embedded SQL products, and
> have a choice between it and Informix, take another look at
> Informix. I've had two weeks of pain with Oracle, and have learned
> the fine art of being on hold, waiting for support people to
> get to me. The informix stuff went quite smoothly by comparison.

If you are considering using any embedded SQL products, I strongly suggest reading the available documentation. Just because you are more familiar with another product doesn't necessarily mean they all work the same way. Received on Sat Sep 03 1994 - 09:22:18 CEST