4

As we know in numerous of the documentations it is mentioned that the 3rd element of the Host array SQLCA.SQLERRD i.e. sqlca.sqlerrd[2] contains the cumulative number of rows processed by the SQL statement so far (in terms of Pro*C).

Does someone know of the significance of the other elements i.e. [0],[1] and what they signify?

Burhan Ali
  • 2,258
  • 1
  • 28
  • 38
Anjan Biswas
  • 7,746
  • 5
  • 47
  • 77

2 Answers2

5

According to the error handling documentation, they don't do anything; they're reserved for future use, though it's starting to look unlikely they'll actually ever be utilised. sqlerrd is declared as long[6], but only [2] and [4] appear to be used, with the latter the offset into the SQL statement for a parse error.

It's interesting that they don't use [0], [1] or [3] though; wonder if they were used for something else in an earlier version. This old informix link has a similar structure with similar meanings for [2] and [4], so might give a hint at what they expected to use the others for; but may be a meaningless coincidence...

PostgreSQL seems to have the same structure, as does DB2, and SQL Server has something that looks related. It's starting to look suspiciously like a standard... albeit one that Oracle only partially adheres to.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • In some older documents I see that they refer to `sqlerrd[3]` as the Number of records processed instead of `sqlerrd[2]`. Its also curious that "Why `[6]` why not more, or less?" – Anjan Biswas Oct 03 '12 at 20:48
  • I remember the info in IBM url in the corner of my head, but now the "error handling" documentation has confused me. Its now like an array with only 2 elements in use which is not [0] and [1], but [2] and [4]. – Anjan Biswas Oct 03 '12 at 20:53
  • @Annjawn - is that in Oracle documents? I've seen references to it as an estimated number of rows after the parse, and the Informix link hints at something similar, but I don't think I've ever seen it in anything specifically Oracle-related. Would be interesting to see that if you can link to it. – Alex Poole Oct 03 '12 at 20:54
  • Yes, that seems to be the case - six elements but only two used. I don't know off-hand (but might check tomorrow if I remember) if the others are always zero, uninitialised, or might hold info that just isn't documented. – Alex Poole Oct 03 '12 at 20:55
  • Honestly, I don't remember where I saw the info and that was one of the reason why I asked this question. But I will dig a little bit more to see if I can find any older references. – Anjan Biswas Oct 03 '12 at 20:55
-1

From the "Informix Guide to SQL" (February 1998):

('First', 'Second', etc. because not all languages index arrays beginning with 0.)

First - After a successful PREPARE statement for a SELECT, UPDATE, INSERT, or DELETE statement, or after a select cursor is opened, this field contains the estimated number of rows affected.

Second - When SQLCODE contains an error code, this field contains either zero or an additional error code, called the ISAM error code, that explains the cause of the main error. After a successful insert operation of a single row, this field contains the value of any SERIAL value generated for that row.

Third - After a successful multirow insert, update, or delete operation, this field contains the number of rows that were processed. After a multirow insert, update, or delete operation that ends with an error, this field contains the number of rows that were successfully processed before the error was detected.

Fourth - After a successful PREPARE statement for a SELECT, UPDATE, INSERT, or DELETE statement, or after a select cursor has been opened, this field contains the estimated weighted sum of disk accesses and total rows processed.

Fifth - After a syntax error in a PREPARE, EXECUTE IMMEDIATE, DECLARE, or static SQL statement, this field contains the offset in the statement text where the error was detected.

Sixth - After a successful fetch of a selected row, or a successful insert, update, or delete operation, this field contains the rowid (physical address) of the last row that was processed. Whether this rowid value corresponds to a row that the database server returns to the user depends on how the database server processes a query, particularly for SELECT statements.

Menachem
  • 911
  • 7
  • 22
  • The question is about Oracle but this answer seems to be about IBM Informix. Is there a relationship between the two that would make this answer relevant? – Burhan Ali Dec 04 '13 at 23:49
  • Sorry, did not notice your [Oracle] tag. But if SQLCA is standard (or semi-standard), it is likely that you'll find similar features across database implementations. And no, this is not from IBM Informix; it's from Informix _before_ it was bought out by IBM. – Menachem Dec 09 '13 at 22:42