mercoledì 26 aprile 2017

Inserting arbitrary values into an AutoNumber field

UCanAccess 4.0.2 added the ability to insert arbitrary values into an AutoNumber field (Long Integer, incrementing). For technical reasons, UCanAccess does not enable this behaviour by default; we use the statement  DISABLE AUTOINCREMENT ON tablename  to activate the ability to perform such insertions.

Sample code:

Statement st = conn.createStatement();
st.execute("CREATE TABLE test (ID COUNTER PRIMARY KEY, TextField TEXT(50))");
st.execute("INSERT INTO test (TextField) VALUES ('Alpha')");  // -> ID=1
    
// direct insert into AutoNumber field is OFF by default
st.execute("INSERT INTO test (ID, TextField) VALUES (3, 'Bravo')");  // -> ID=2, not 3
    
// allow direct insert into AutoNumber field
st.execute("DISABLE AUTOINCREMENT ON test");
st.execute("INSERT INTO test (ID, TextField) VALUES (7, 'Golf')");  // -> ID=7
st.execute("INSERT INTO test (ID, TextField) VALUES (4, 'Delta')");  // -> ID=4
    
// revert to default behaviour
st.execute("ENABLE AUTOINCREMENT ON test");
st.execute("INSERT INTO test (TextField) VALUES ('Hotel')");  // -> ID=8

Notes:
  1. Direct insert of values ≤ 0 is currently not now supported as of version 4.0.3.
  2. If you use  DISABLE AUTOINCREMENT ON tablename  then you must always supply a value for the AutoNumber column. Later on, if you need AutoNumber values to be automatically assigned then you need to  ENABLE AUTOINCREMENT ON tablename  again.