1

I'm using ORMLite in my android app and I searched a lot but I don't have a clear way to do what I want. I would like to know if I can change the starting value of a generatedId value. For example now it starts in 1 and I would like to start in 010001 or 020001. I noticed that we have sqlite_sequence table inside. Must I change that value or must I do it in another way?.

Thank you very much!

acostela
  • 2,597
  • 3
  • 33
  • 50

3 Answers3

1

Finally I solved it. Here is the trick:

@DatabaseField(allowGeneratedIdInsert = true, generatedId = true, columnName = ID)
private int id;

I allow generatedIdInsert so I can set manually my Id and if I don't generate it manually it will be autogenerated. After that I do a first explicit insert.

daoObject.setId(010000);
dao.create(daoObject);

Now when I insert a new object the generatedId will start in 010001.

I hope that this little trick help somebody.

acostela
  • 2,597
  • 3
  • 33
  • 50
  • I really don't think that this would work. Have you tried it? – Gray Nov 10 '14 at 17:57
  • Yes, I'm actually using it and it's working fine. Why you think it's not correct? – acostela Nov 10 '14 at 17:58
  • This is tremendously database dependent. It certainly would not work in (for example) postgres or mysql. You are leveraging an implementation detail Sqlite that might bite you. See my answer. For example have you seen this: "AUTOINCREMENT guarantees that automatically chosen ROWIDs will be increasing but not that they will be sequential." https://www.sqlite.org/autoinc.html – Gray Nov 10 '14 at 18:04
  • So is better to use for example a compose primary key? e.g: id autoincremented, tabletId Integer, – acostela Nov 10 '14 at 18:24
1

For example now it starts in 1 and I would like to start in 010001 or 020001.

In general I take the opinion that you should not do something like this. The id field is designed to be an identity and your application should not depend on the values being anything particular.

If you need some specific number then I would generate it externally to the database and add it as another field on the items. So the id can float and be whatever the DB wants and you can have your special ID that you control.

Gray
  • 115,027
  • 24
  • 293
  • 354
  • 1
    I mark this as correct because after this explanation I will use a compose primary key with two fields. Thank you very much! – acostela Nov 10 '14 at 18:28
0

You have to have the AUTO_INCREMENT = 0100001; in your CREATE TABLE but as this is most certainly generated code by ORMLite you would have to force it by using the ORMLite annotation.

@DatabaseField(generatedId = true,
    columnDefinition = "INTEGER PRIMARY KEY AUTOINCREMENT = 0100001")
private int id;
Gray
  • 115,027
  • 24
  • 293
  • 354
mach
  • 8,315
  • 3
  • 33
  • 51
  • Thank you for your response. Are you sure that this works? because I'm getting an exception because of malformed SQL statement. I read that "INTEGER PRIMARY KEY" will set autoincrement automatically. So maybe that's the reason what I'm getting my exception. "AUTO_INCREMENT": syntax error (code 1): , while compiling: CREATE TABLE `lineavent` (`articulo` VARCHAR , `hora` VARCHAR , `cliente` INTEGER , `caja` INTEGER , `_id` INTEGER PRIMARY KEY AUTO_INCREMENT=0100001 , `kilo` FLOAT , `partida` INTEGER , `precio` FLOAT ) – acostela Nov 10 '14 at 08:51
  • Sorry, no. I extrapolated two different answers. http://stackoverflow.com/a/2130646/233048 and https://groups.google.com/forum/#!topic/ormlite-user/TxnpaBnJdhc – mach Nov 10 '14 at 08:56
  • 1
    I'm trying to insert explicitly a row in my DB with a certain value for the ID so SQLlite will use numbers higher than them. But generatedId = true; doesn't allow me to do that. I read that here http://stackoverflow.com/questions/692856/set-start-value-for-autoincrement-in-sqlite – acostela Nov 10 '14 at 08:59