CdId

Hi,

When deleting a structure with for example CdId = 10 from a structure table, a newly added structure will receive CdId = 11, even though CdID is no longer used. Is there a way to prevent this behaviour, that is being able to assign used CdIds to new structures?

Thanks

Reseting CdId sequences is not supported directly from IJC, but there are some ways how to do this directly in database. This depends on database you are using (I suppose it's Mysql?). In Mysql it's possible to reset auto_increment directly in SQL console. It looks like:

alter table my_table auto_increment=100;

I tested this for standard tables as well as for jchem tables now, but I'd recommend to avoid doing this if it's not really necessary. Please be careful. And it's better to make a database backup before you do this.

Is there a way to do this yet? (Instant JChem 5.7.0)? I am using the built-in database type (Embedded Derby).

I keep forgetting that it's possible to clear all the data in a row without actually deleting the row, and it seems really messy to lose CdId numbers!

No, this is not possible in IJC, only directly within the database. For the Derby database used in local databases you would do it like this:

ALTER TABLE tauto ALTER COLUMN i RESTART WITH 6

See here for more details. http://db.apache.org/derby/docs/10.8/ref/rrefsqlj81859.html

See here for how to connect directly to teh Derby database: http://www.chemaxon.com/instantjchem/ijc_latest/docs/user/help/htmlfiles/tips_and_tricks/db_explorer.html

If you do this you need to be careful to ensure you don't end up creating duplicate values.

Tim

Hi Tim,

Thanks for your answer. I thought it was going to be completely beyond me, but actually it was only slightly beyond me .

I've managed to open the database using the Database Explorer as described in http://www.chemaxon.com/instantjchem/ijc_latest/docs/user/help/htmlfiles/tips_and_tricks/db_explorer.html

and I've managed to execute some SQL commands, which is impressive considering I know nothing about SQL!

 

What I can't manage to do is edit the CD_ID column numbers. I think this is because they are automatically generated.

I typed in:

ALTER TABLE APP.MOLECULES ALTER COLUMN CD_ID RESTART WITH 1

and got the response:

Executed successfully in 0.046 s, 0 rows affected.
Line 1, column 1

Execution finished after 0.046 s, 0 error(s) occurred.

Of course, nothing's changed because no rows were affected!

I've attached a screenshot showing the current state of the data. What I want is for row 1 to contain CD_ID 1, row 2 to contain CD_ID 2, and so on.

Edit:

On the basis that there were only 3 useful rows in that database, I decided it would save time if I simply started a new schema and copied the forms (Grid View, Single Record View etc) over. I've done that and will have to try to remember to Clear Selected Data rather than Delete Row. (I can see why I've got confused and chosen the wrong option before - Clear Selected Data is usually greyed out in my program). It would be useful if we could figure out how to update the CD_ID numbers for anyone else in the future, but it's not urgent.

The statement:

ALTER TABLE APP.MOLECULES ALTER COLUMN CD_ID RESTART WITH 1

sets the next autogenerated value to be 1.

This is probably not what you want. You probably want to set it to be one more than the highest value already in the database. If you set it to less than this you will get errors when you try to insert new rows as the IDs may already be used.

To change the value of an existing ID you need something line this:

UPDATE APP.MOLECULES set CD_ID = 100 where CD_ID = 3

This change the ID from 3 to 100. However if you do this you need to be careful to choose the right values, to set the appropriate next automatically generated ID using the  approach above and to restart IJC before you start doing anything like adding new structures or doing searches.

e.g. generally speaking this is not recommended!

Tim