Hello all,
I've read all postings about this topic I found with deja but still it's
not working.
I'm trying to reset the SERIAL column 'lfdnr' after deleting a
consecutive number of rows using this code:
int d_basiszahlen(xdate* pdtVon)
{
EXEC SQL BEGIN DECLARE SECTION;
date dateVon;
long max_nr;
EXEC SQL END DECLARE SECTION;
int rc;
dateVon = XDateToLong(pdtVon);
EXEC SQL DELETE FROM basiszahlen WHERE gueltig_von = :dateVon;
rc = sqlca.sqlcode;
if (rc == 0)
{
EXEC SQL SELECT MAX(lfdnr) INTO :max_nr FROM basiszahlen;
rc = sqlca.sqlcode;
if (rc == 0)
{
max_nr++;
EXEC SQL ALTER TABLE basiszahlen MODIFY lfdnr SERIAL(max_nr);
rc = sqlca.sqlcode;
}
}
return rc;
}
The ESQL compiler gives me a syntax error in the ALTER TABLE statement:
Error -33051: esqlc - Syntax error on identifier or symbol 'max_nr'.
All examples I found were about modifying the serial column using a
constant serial value but not a variable. Also I've read that 'serial'
is a separate data type but I cannot declare 'max_nr' as serial or
SERIAL (gives another syntax error).
adTHANKSvance, Jens
↪ replying to Jens Schacherl
Jens Schacherl wrote:
> Hello all,
>
> I've read all postings about this topic I found with deja but still it's
> not working.
> I'm trying to reset the SERIAL column 'lfdnr' after deleting a
> consecutive number of rows using this code:
>
> ...
> max_nr++;
> EXEC SQL ALTER TABLE basiszahlen MODIFY lfdnr SERIAL(max_nr);
> ...
>
> The ESQL compiler gives me a syntax error in the ALTER TABLE statement:
> Error -33051: esqlc - Syntax error on identifier or symbol 'max_nr'.
>
Prepare the statement and it should go through OK.
e.g.
sprintf(l_sql_stmt, "%s%d%s",
"ALTER TABLE basiszahlen MODIFY (lfdnr SERIAL(", max_nr, "));");
EXEC SQL PREPARE alter_stmt FROM :l_sql_stmt;
if (strncmp(SQLSTATE, "00", 2) > 2)
printf("Alter : SQLCODE is %d\\n", SQLCODE);
EXEC SQL EXECUTE alter_stmt;
if (strncmp(SQLSTATE, "00", 2) > 2)
printf("Execute : SQLCODE is %d\\n", SQLCODE);
Rudy