db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: Is it possible to "RESTART WITH" IDENTITY columns with the result of an expression?
Date Thu, 09 May 2013 13:17:48 GMT
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

Mime
View raw message