Re: ESQL C problem with sqlerrd[2]
In article <1992Dec14.230122.22841@unlv.edu> ray@unlv.edu (Ray Tripamer) writes:
>In article <1giuitINNcgq@emory.mathcs.emory.edu> kristen.m.altman@ccd.harris.com (Kristen Altman) writes:
>>In my application, I am using ESQL C 4.1 and formatting dynamic SQL stmts.
>>
>>After the first fetch the number of rows returned in sqlerrd[2]
>>is 0, but the sqlca.sqlcode = 0 also. If I understand this correctly,
>>the sqlerrd[2] says there are no rows, but the sqlcode says there are rows.
>>--
>>Kristen Altman
>>kma@ccd.harris.com
>
>The sqlca.sqlerrd[2] value DOES NOT hold the number of rows found by opening
>a cursor for a select statement (dynamic or otherwise). The sqlerrd[2] value
>is filled in only by the update and delete statements (maybe insert too), but
>not by a select.
>
>The only way to get the number of rows from a select statment is to
>execute some type of "select count(*) into $cnt ..." statement with the same
>from and where clause as the original select statement.
Hello Ray, long time no hear.
Sorry to disagree with you, but Kristen is sort of right. By definition,
sqlerrd[2] is set with the number of rows processed AFTER an SQL statement
has executed. This includes the UPDATE, DELETE, INSERT, and SELECT
statements. The difference with SELECT lies in the use of a cursor. The
SELECT is not complete until the last row is processed and you get sqlcode=100
(I'm talking strictly successful operations). Therefore, you should only
expect sqlerrd[2] to contain a value other than 0 after the last FETCH.
This was strictly true in versions 1.X and 2.X. However, in ONLINE, things
changed. If the number of rows in the table is small, as well as the table
size, ONLINE may fill sqlerrd[2] after the first FETCH. This is because
ONLINE has completed the SELECT, and the results are just waiting in a buffer
to be FETCHed. The following is a test program to demonstrate this:
create table x1 (
x0 char(20),
x2 integer
);
And the program:
main()
{
$ char x1[21];
$ long x2;
$ database briand1;
$ declare c1 cursor for select * from x1;
printf("2 sqlca.sqlcode=%ld, 2=%ld\\n", sqlca.sqlcode, sqlca.sqlerrd[2]);
$ open c1;
printf("3 sqlca.sqlcode=%ld, 2=%ld\\n", sqlca.sqlcode, sqlca.sqlerrd[2]);
while (1)
{
$ fetch c1 into $x1, $x2;
if(sqlca.sqlcode !=0)
break;
printf("4 sqlca.sqlcode=%ld, 2=%ld\\n", sqlca.sqlcode, sqlca.sqlerrd[2]);
printf("x1=%s, x2=%ld\\n", x1, x2);
}
printf("5 sqlca.sqlcode=%ld, 2=%ld\\n", sqlca.sqlcode, sqlca.sqlerrd[2]);
}
With just 3 records in the table, I get the following output:
1 sqlca.sqlcode=0, 2=0
2 sqlca.sqlcode=0, 2=0
3 sqlca.sqlcode=0, 2=0
4 sqlca.sqlcode=0, 2=3
x1=test 1 , x2=1
4 sqlca.sqlcode=0, 2=3
x1=test 2 , x2=2
4 sqlca.sqlcode=0, 2=3
x1=test 3 , x2=3
5 sqlca.sqlcode=100, 2=3
Notice that 4 is the first print after the FETCH, and sqlerrd[2] is already
set to 3. Though the cursor is still open, the ONLINE engine has completed
its work and left the records in the buffer.
Now, with 500 records in the table, running the same program I get this:
1 sqlca.sqlcode=0, 2=0
2 sqlca.sqlcode=0, 2=0
3 sqlca.sqlcode=0, 2=0
4 sqlca.sqlcode=0, 2=0
x1=test 70 , x2=70
4 sqlca.sqlcode=0, 2=0
** Notice that sqlerrd[2] is 0 on the first FETCH
x1=test 71 , x2=71
4 sqlca.sqlcode=0, 2=0
x1=test 72 , x2=72
.
.
.
x1=test 424 , x2=424
4 sqlca.sqlcode=0, 2=0
x1=test 425 , x2=425
*** With approximately 74 rows to go, the engine completes, and sqlerrd[2] is
*** set with the final value
4 sqlca.sqlcode=0, 2=500
x1=test 426 , x2=426
4 sqlca.sqlcode=0, 2=500
x1=test 427 , x2=427
.
.
.
4 sqlca.sqlcode=0, 2=500
x1=test 499 , x2=499
4 sqlca.sqlcode=0, 2=500
x1=test 500 , x2=500
5 sqlca.sqlcode=100, 2=500
So, sqlerrd[2] IS set by a SELECT statement, but not until the engine
completes its work. In V1.X and V2.X this is on the last row, in ONLINE it
will happen prior to the last row, but there is no way to estimate exactly
when this will be. The only gauranteed time it will be set is upon
completion.
>--
>Ray Tripamer
>ray@asci.com