On 5/8/13 2:07 PM, Martin Proulx wrote:
Hello,
I want to import data in tables that have GENERATED BY DEFAULT AS
IDENTITY columns.
The plan is to rely on ij and the foreignViews tool to import the
tables one by one and then reset the next value of the identity column
using "RESTART WITH".
I can easily achieve this with a literal value for the restart value
(ALTER TABLE ILM_USERS ALTER ID RESTART WITH 123;), but I haven't been
able to find a way to pass the result of an expression for the new value.
Everything I've tried has failed:
ij> ALTER TABLE ILM_USERS ALTER ID RESTART WITH (SELECT MAX(ID)+1 FROM
ILM_USERS);
ERROR 42X01: Syntax error: Encountered "(" at line 1, column 45.
ij> ALTER TABLE ILM_USERS ALTER ID RESTART WITH INTEGER('123');
ERROR 42X01: Syntax error: Encountered "INTEGER" at line 1, column 45.
ij> PREPARE TEST AS 'ALTER TABLE ILM_USERS ALTER ID RESTART WITH ?';
ERROR 42X01: Syntax error: Encountered "?" at line 1, column 45.
Is there a way to somehow pass in a dynamic value?
Since I haven't grasped all subtleties of the SQL syntax yet, I don't
know if things don't work because of my invalid syntax or because it
is just not supported.
Unless someone explains how this can be done, the only solution I can
think of at this point is to create a procedure that would take the
next value as a parameter and perform the "RESTART WITH" alteration in
Java...
Thanks for your help!
Martin
Hi Martin,
I don't know of any better solution to your problem. The restart value
must be a numeric literal according to the SQL Standard, volume 2,
section 11.73 (<alter sequence generator statement>). In general, the
SQL committee has not parameterized DDL operations.
That said, there are other statements where Derby allows a ? parameter
instead of the literal demanded by the Standard. You are welcome to
create a JIRA for this issue. I think there is a strong likelihood that
the community would support this extension.
I have created https://issues.apache.org/jira/browse/DERBY-6219 as a
place to propose other improvements to help people import data through
the foreign views.
Thanks,
-Rick
