problem with sequences

[vz]

I created a sequence in postgres but I still can't get it to automatically generate a number. I have a insert form in coldfusion that I want the user to enter infor into and then click submit, I want postgresql to automatically generate a number to insert into one of the fields. When I altered my table after the sequence was created I set up a default that looked like this: order_id (column name) default nextval('seq_ordersplaced'), I thought that this would work but it does nothing. Would anyone know how to code this into a web page that would populate the form?
Thanks

 

[kedrew]

I don't think I've run into this problem; but I don't recall adding AutoIncriment after creating the table.

A couple of things that may be able to help you...

#1) Retrieve Sequence Value before insert
SELECT nextval('<sequence_name>');

then
INSERT INTO table (id, name) values (<result from SELECT>, '<name value>');

I realize that you have to do this every time your webscript is run, but in the event that you ever need the auto-number for a key in a different table, you will already have it.

#2) Setting default value on table creation.
CREATE SEQUENCE mysequence;
CREATE TABLE mytable (id int4 DEFAULT nextval('mysequence') [PRIMARY KEY], name varchar(50), ....)

Hope that helps

 

[vz]

Thank you that helps, but where would I code this into coldfusion? I have already tried that and I keep getting error messages, it works when I do it manually with just straight sql, but since i am doing this with a cold fusion insert form it needs to be incremented everytime automatically and I keep getting error messages.
Thanks

 

[kedrew]

I cannot help you with the ColdFusion part. All I can offer is a chunk of code written in Java that outlines the procedure I used.

The example if from a function that was to lookup a ID of a record; if the record didn't exist, it needed to create a new record and return the ID. Rather than inserting the record and requerying, the code obtains the next ID from the specifed sequence, stores that ID, inserts the new record with the specified ID, and returns the ID.

Hopefully my comments are sufficent enought for you to follow and translate to ColdFusion.

// check to see if the remoteHost name already exisits
rs = stmt.executeQuery("SELECT * FROM la_remotehost WHERE hostname='" + remoteHost + "'");            

//if a valid result exists, save the ID number
if (rs.next()) {
    //retrive the ID of the 
    id = rs.getInt("id");            
}

//close the current statement
stmt.close();

//if a valid result didn't exist
//  the ID number will not be updated from the default value
//  therefore, check to see if ID equals 0, and if so, we will need to INSERT the remote host name
if (id == 0) {

   //get the next value in the sequence
   stmt = con.createStatement();
   rs = stmt.executeQuery("SELECT nextval('la_remotehost_id_seq')");
   if (rs.next()) {
        id = rs.getInt(1);
    }
    //close the statment
    stmt.close();

    //prepare INSERT statement
    PreparedStatement pstmt = con.prepareStatement("INSERT INTO la_remotehost (id, hostname) VALUES (?, ?)");
        
    //add the new id value
    pstmt.setInt(1, id);
        
    //add the hostname to the statement
    pstmt.setString(2, remoteHost);
        
    //execute the INSERT statement
    pstmt.executeUpdate();
        
    //close the Prepared statement
    pstmt.close();
}