Ugly embedded SQL behaviour
Date: Thu, 1 Sep 1994 16:56:10 GMT
Message-ID: <CvGn1M.Ewq_at_world.std.com>
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 - 18:56:10 CEST