Re: Ugly embedded SQL behaviour

You need to be careful with what character types you are using with Oracle. This also depends on the value of the precompiler option MODE. For a variable declared as a character buffer as you describe, with MODE=ORACLE (the default) the precompiler will assign to this variable external datatype 1 (VARCHAR2). (This is assuming that you are on Oracle7). The behavior of this datatype is that trailing blanks will be stripped, and the remainder will be sent to the database. If MODE=ANSI, then it will be assigned datatype 97 (CHARZ), which is a fixed-length null-terminated string. In this case, the database will stop at the null. You can also change the default datatype by using EXEC SQL VAR. In your case, that would be:

EXEC SQL BEGIN DECLARE SECTION;
  static char buf[32000];
  EXEC SQL VAR buf IS STRING (32000);
EXEC SQL END DECLARE SECTION; If you do this, then the database will look for the null terminator and will work as you expect. Also note that if the variable is declared as char *, then the default datatype will be 5 (STRING) and you don't need the EXEC SQL VAR. You use EXEC SQL VAR (and EXEC SQL TYPE) to override the default external datatype assigned to your host variables.

The reason that the memset worked is because then you were still sending the entire buffer to the database, but the 0 is treated as white space for the SQL statement, so you didn't get the error.

In article <CvGn1M.Ewq_at_world.std.com>, smayo_at_world.std.com (Scott A Mayo) writes:
|>
|> 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.
|>
|> Given this sequence:
|>
|> EXEC SQL BEGIN DECLARE SECTION;
|> static char buf[32000]; /* big buffer to build SQL command in */
|> EXEC SQL END DECLARE SECTION;
|>
|> ... buf gets used for various things ...
|>
|> /* Time to do a delete
|> */
|> strcpy(buf, "DELETE FROM ani WHERE key = 5");
|> EXEC SQL EXECUTE IMMEDIATE :buf;
|>
|> where ani is a table I have sucessfully inserted into and
|> fetched from, and key is a numeric column in the table,
|> the error returned to sqlca.sqlcode is -933, and the error
|> text is "ORA-00933 SQL command not properly ended".
|>
|> 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!
|>
|> 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.
Received on Thu Sep 01 1994 - 21:18:11 CEST