How to insert a primary or uniique id, autoincrement to a SQL db

You don't name the column if it's a

Okay I will remove the name but hosw cani I concatenate my fixed number + primary no

The least convenient way if noe of the above are available  is to do it through your program - at the start of your progra - dtermin the maximum value of the key filed and then increment it and inster programmatically - in this case the field works as any otgher field - thsi should work everywhere without problem, but it is not so conveninet

If your auti=increemneinting supports non-zero starting number then it will add it for you

sequence, say in Oracle and in other databses  can be started form any number not necessarily one

Adn if you do it manually then there is of course no problem toadd your fixed number in your program

for_van i like that idea of doing it programmically how?  SQLite allows me primary and unique option on a field.  I am checking the autoincrement option now.

this rtalks about autoincreemnet - I only don't knwo if  all "sqllite's" are the same:
http://www.sqlite.org/autoinc.html

progrtammatically - atb the start of your program run - you

select max(key_column) from yout_table

then you get theis number into java code as any jdbc select data stuff
and then before doing next insert you incremenet this value
and insert inceremeneted value as your ke_column vlaue

Thsi is straightfprwrd but probe to mistakes in case of non-standard situations, when you exit not in the right way, etc.etc.
So better to rely on database - at leastb when I'm dealing with oracle and the kind

SQLite does allow auto-increment according to
http://www.sqlite.org/autoinc.html  There website states --->>
You can access the ROWID of an SQLite table using one the special column names ROWID, _ROWID_, or OID. Except if you declare an ordinary table column to use one of those special names, then the use of that name will refer to the declared column not to the internal ROWID.
 
If a table contains a column of type INTEGER PRIMARY KEY, then that column becomes an alias for the ROWID. You can then access the ROWID using any of four different names, the original three names described above or the name given to the INTEGER PRIMARY KEY column. All these names are aliases for one another and work equally well in any context.
 
When a new row is inserted into an SQLite table, the ROWID can either be specified as part of the INSERT statement or it can be assigned automatically by the database engine.

Yes, that' s what I also raed. What happens if you try to use it?

I remove the key_column field from my code as was suggested in order to get a unique rowid number in my field sonum but it still remains null.  Cannot get the table to autmatically insert a unique rowid on the integer primary key field that I designated

did you specify autoincermement when you created the tabele like herer:

http://stackoverflow.com/questions/2817038/how-can-i-create-a-sequence-in-sqlite

create table Categories (pk_categoryid integer primary key autoincrement, category_name text);

It looks like you were doing it more or less roght but do not name your coolumn rowid
but rather something else and declare it INTEGER PRIMARY KEY upon table creation

I'm not sure though, how to start with some number other than one

reading here:
http://www.sqlite.org/faq.html#q1

Here is the long answer: If you declare a column of a table to be INTEGER PRIMARY KEY, then whenever you insert a NULL into that column of the table, the NULL is automatically converted into an integer which is one greater than the largest value of that column over all other rows in the table, or 1 if the table is empty. (If the largest possible integer key, 9223372036854775807, then an unused key value is chosen at random.) For example, suppose you have a table like this:

    CREATE TABLE t1(
      a INTEGER PRIMARY KEY,
      b INTEGER
    );

With this table, the statement

    INSERT INTO t1 VALUES(NULL,123);

is logically equivalent to saying:

    INSERT INTO t1 VALUES((SELECT max(a) FROM t1)+1,123);

I'm guessing that if you insert first time not null into that column - it will then start counting form thast point, because

 INSERT INTO t1 VALUES(NULL,123);

is logically equivalent to saying:

    INSERT INTO t1 VALUES((SELECT max(a) FROM t1)+1,123);

Non of the sQL managers I try allow me to set the autoincrement?  Do you have an sqlite manager that I can download that would do that

Did you try the last example - without any word of autincremet - just say like that
 CREATE TABLE t1(
      a INTEGER PRIMARY KEY,
      b INTEGER
    );

camll this column arbitary (rowid might clash)
and try ito insert first real numbe ansd secnd time to insert null in this field.
Should not this work ?

I changed the code, removed the rowid, repladed it with the column name "sonum".  I then inserted the integer 1, that works.
I then changed the code inserted null, and got a null save in the table.

"INSERT INTO CELL_SOHEAD " +
			    		"(sonum, sodate, buyerno, buyer, buyaddr1," +
			    		"buyaddr2, buycity, buystate, buyzip, ad_date," +
			    		"transport, cashcharge, comments) " +
			    		"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

						pst.setString(1,	null);

Select allOpen in new window

http://docs.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html#setNull%28int,%20int%29

setNull

void setNull(int parameterIndex,
             int sqlType)
             throws SQLException

    Sets the designated parameter to SQL NULL.

    Note: You must specify the parameter's SQL type.

    Parameters:
        parameterIndex - the first parameter is 1, the second is 2, ...
        sqlType - the SQL type code defined in java.sql.Types
    Throws:
        SQLException - if parameterIndex does not correspond to a parameter marker in the SQL statement; if a database access error occurs or this method is called on a closed PreparedStatement
        SQLFeatureNotSupportedException - if sqlType is a ARRAY, BLOB, CLOB, DATALINK, JAVA_OBJECT, NCHAR, NCLOB, NVARCHAR, LONGNVARCHAR, REF, ROWID, SQLXML or STRUCT data type and the JDBC driver does not support this data type

it should be

pst.setNull(1, SqlTYPES.VARCHAR)

or soemmthuind like that

Let me check

http://www.java2s.com/Code/Java/Database-SQL-JDBC/DemoPreparedStatementSetNullforcharstringcolumn.htm

this is how it should be

  pstmt.setNull(1, java.sql.Types.VARCHAR);

but stop i this case;

pstmt.setNull(1, java.sql.Types.INTEGER);

It is not String in the first place - it should be integer
rememeber this:
 CREATE TABLE t1(
      a INTEGER PRIMARY KEY,
      b INTEGER
    );

But even if it were String with PrepareSttaement you need to insert null the way I showed above - by no means  pst.setString(1,      null);

I changed it.  But the field -- sonum -- still save it as a null.

PreparedStatement pst = connection.prepareStatement(
			    		"INSERT INTO CELL_SOHEAD " +
			    		"(sonum, sodate, buyerno, buyer, buyaddr1," +
			    		"buyaddr2, buycity, buystate, buyzip, ad_date," +
			    		"transport, cashcharge, comments) " +
			    		"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
						pst.setNull(1, java.sql.Types.INTEGER); 
						pst.setString(2,	sodate.getText());
						pst.setString(3,	custno.getText());
						pst.setString(4,	""+mimporter+"");
						pst.setString(5,	addr1.getText());
						pst.setString(6,	addr2.getText());
						pst.setString(7,	city.getText());
						pst.setString(8,	(String) cbstates.getSelectedItem());
						pst.setString(9,	zip.getText());
						pst.setString(10,	delivdate.getText());
						pst.setString(11,	group1.getSelection().getActionCommand());
						pst.setString(12,	group2.getSelection().getActionCommand());
						pst.setString(13,	comments.getText());

Select allOpen in new window

try to do it without PreparedStatement - just regular insert

did you specify INTEGER PRIMARY KEY on creation ?

Can you tell me if I am going right?  I am also getting an error inthe update null line.

// INSERT MEMORY VARIABLES FROM JComponents INTO TABLE
				Statement st = connection.createStatement();
				String sqlString = new String("INSERT INTO CELL_SOHEAD " +
			    "(sonum, sodate, buyerno, buyer, buyaddr1," +
			    "buyaddr2, buycity, buystate, buyzip, ad_date," +
			    "transport, cashcharge, comments) " +
			    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
				
			    	rs.moveToInsertRow();
		    		rs.updateNull(1, java.sql.Types.INTEGER); 
					rs.updateString(2,	sodate.getText());
					rs.updateString(3,	custno.getText());
					rs.updateString(4,	""+mimporter+"");
					rs.updateString(5,	addr1.getText());
					rs.updateString(6,	addr2.getText());
					rs.updateString(7,	city.getText());
					rs.updateString(8,	(String) cbstates.getSelectedItem());
					rs.updateString(9,	zip.getText());
					rs.updateString(10,	delivdate.getText());
					rs.updateString(11,	group1.getSelection().getActionCommand());
					rs.updateString(12,	group2.getSelection().getActionCommand());
					rs.updateString(13,	comments.getText());
			    	rs.insertRow();
			    	rs.first();

					int rsInsert = rs.executeUpdate(); 

					ResultSet rsKey = rs.getGeneratedKeys();
		    	rsKey.next();

Select allOpen in new window

wheer do you see thes  update... methods?
they are claeed set.. not update....
these method are not called updateNull - theye are calle setNull -- all of them set not upddatte look in  the API

http://docs.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html

I saw them at http://docs.oracle.com/javase/1.4.2/docs/guide/jdbc/getstart/resultset.html#1012952
where it states -->>  New rows may be inserted into a result set table and into the underlying database table using new methods in the JDBC 2.0 core API. . . . .To access the insert row, an application calls the ResultSet method moveToInsertRow, which positions the cursor on the insert row. Then it calls the appropriate updater methods to add column values to the insert row. When all of the columns of the row to be inserted have been set, the application calls the method insertRow. This method adds the insert row to both the result set and the underlying database simultaneously. Finally, the application needs to position the cursor on a row back in the result set.
rs.moveToInsertRow();
rs.updateObject(1, myArray);
rs.updateInt(2, 3857);
rs.updateString(3, "Mysteries");
rs.insertRow();
rs.first();

I changed to statement.  But I am getting an error on st.setString or st.setNull as --->
The method setNull(int, int) is undefined for the type Statement

Statement st = connection.createStatement();
			String sqlString = new String("INSERT INTO CELL_SOHEAD " +
		    "(sonum, sodate, buyerno, buyer, buyaddr1," +
		    "buyaddr2, buycity, buystate, buyzip, ad_date," +
		    "transport, cashcharge, comments) " +
		    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
    		st.setNull(1, java.sql.Types.INTEGER); 
			st.setString(2,	sodate.getText());
			st.setString(3,	custno.getText());
			st.setString(4,	""+mimporter+"");
			st.setString(5,	addr1.getText());
			st.setString(6,	addr2.getText());
			st.setString(7,	city.getText());
			st.setString(8,	(String) cbstates.getSelectedItem());
			st.setString(9,	zip.getText());
			st.setString(10,	delivdate.getText());
			st.setString(11,	group1.getSelection().getActionCommand());
			st.setString(12,	group2.getSelection().getActionCommand());
			st.setString(13,	comments.getText());
	    	int rsInsert = st.executeUpdate(); 
		    ResultSet rsKey = st.getGeneratedKeys();

Select allOpen in new window

Of course!

For satemnt you need to create the insertstring with all values in line

insert into table(....)  values ("" +sodate.gettext() + "','" + .....)

no setStrings and no question marks for normal statemnt

you can first make a test table with two fileds and check if normal insert would update this key
so that you don't need to type all your fileds

This is an example of how you do simple insert wit the statmenet (without PreparedStatement)

insertString1 = "insert into Employees values(6323, 'Hemanth')";

stmt = con.createStatement();
stmt.executeUpdate(insertString1);

from here:
http://www.jdbc-tutorial.com/jdbc-insert-records.htm

so make imple table wit two filed and decalre the first one this kety as shown above and then
first try to insert real number and then try to insert null

I'm afraid this sqllite is not consistent everyhwher.
Still try inserting without preparedStatement, but if it does not work,
then just use explicitly this variant:


  INSERT INTO t1 VALUES((SELECT max(a) FROM t1)+1,123);

with Statement (not PreparedStatment) you can do it.
and it should work.

This type of statement works fine in my oracle
and it is general sql - should work for you

You were right.  It appears to be working at first.  I managed to set autoincrement to true.  It has conditions to enable you to set it to what you want also.
ERROR --->>>
java.sql.SQLException: column sonum is not unique
      at org.sqlite.DB.execute(DB.java:275)
      at org.sqlite.DB.executeUpdate(DB.java:281)
      at org.sqlite.Stmt.executeUpdate(Stmt.java:103)
      at insertNewMoccaRecord.<init>(insertNewMoccaRecord.java:58)

// INSERT MEMORY VARIABLES FROM JComponents INTO TABLE	
		String sqlString = new String("INSERT INTO CELL_SOHEAD " +
	    "(sonum, sodate, buyerno, buyer, buyaddr1," +
	    "buyaddr2) " +
	    "VALUES ('"+java.sql.Types.INTEGER+"', " +
	    "'"+sodate.getText()+"', '"+custno.getText()+"', '"+mimporter+"', " +
	    "'"+addr1.getText()+"', '"+addr2.getText()+"')");
	    Statement st = connection.createStatement();
		st.executeUpdate(sqlString); 

		ResultSet rsKey = st.getGeneratedKeys();
	    rsKey.next();

Select allOpen in new window

I realized afterwards about the sonum and I changed it to -- select max(sonum) from cell_sohead)+1
for_Van you are a GENIUS.  It works just as you said.  What I don't understand is that one of your experts made use preparedStatements and I had hell trying to get this thing work.  It is only that saved me.  

(select max(sonum) from cell_sohead)+1 --  is not the autoincrement feature request from the code.  Because I turned it off from the db and your small code still works.  How do one insert the autoincrement feature from code.

>(select max(sonum) from cell_sohead)+1 --  is not the autoincrement feature request from the code

don't understand what you mean

also didn't understand whoch variant actually worked

The attached code increments the sonum and inserts the data.  But I thought autoincrement on a key column was done by sending a null string and the db would do it for you.  This is what i meant.  An you are right it only works with the regular statement and not the preparedStatement

String sqlString = new String("INSERT INTO CELL_SOHEAD " +
	    "(sonum, sodate, buyerno, buyer, buyaddr1," +
	    "buyaddr2) " + 
	    "VALUES ((select max(sonum) from cell_sohead)+1, " +
	    "'"+sodate.getText()+"', '"+custno.getText()+"', '"+mimporter+"', " +
	    "'"+addr1.getText()+"', '"+addr2.getText()+"')");
	    Statement st = connection.createStatement();
		st.executeUpdate(sqlString);

Select allOpen in new window

If there is nothing else I am closingn thiis session and I am very happy with your solution.

Genius at Java and SQL