auto incremntal id's

The following is my cgi in Perl.
As you may see I am trying to insert the customer id automatically by selcting the last max id and +1... But i dont have a clue exactly how to do this.
Can anyone please have a look at what I have done so far and point out where Ive gone wrong. Every time i try to send this error message is either 'invalid number' or primary key violation.

Many thanks

Claire.



$ENV{"ORACLE_HOME"} = "/home/oracle/product/9.2.0";
print "Content-type: text/html\n\n";
my $dbh = DBI-> connect('dbi:Oracle:host=info200.infc.ulst.ac.uk;sid=ORCL;port=1521',
'watters', 'P32208403')
or die "Couldn't connect to database: " . DBI->errstr;
my $customername=param('winecustomer');
my $address1=param('address1');
my $city=param('city');
my $postcode=param('postcode');
my $country=param('country');
my $telno=param('telno');
my $email=param('email');
my $cardno=param('cardno');
my $cardtype=param('cardtype');
my $expirydate=param('expirydate');
my $chname=param('chname');
my $wine=param('wine');
my $quantity=param('quantity');
my $customerid=('select max(customerid)+1 from winecustomer');




my $sth2=$dbh->prepare('insert into winecustomer
(customerid) values(max(customerid) + 1)
');

$sth2->execute($customerid);
my $sql =qq{insert into winecustomer (customerid, customername,address1, postcode, country,telno, email, city) values(?,?,?,?,?,?,?,?)};

my $sth=$dbh->prepare($sql);

$sth->execute($customerid, $customername, $address1, $postcode, $country, $telno, $email, $city) or die $DBI::errstr;



print "Thankyou , your details have been recieved\n\n";
$dbh->disconnect;

Hi,

I think you are better of using sequence. that way you let the database decide on the next value ...


Galit.

definitely use a sequence

select max(id) will fail unless there is only ever at most one (1) user, or else you use transaction locks

Hi,

than use the sequence

select XXX.nextval/currval into BBB from dual.

if you choose the nextval it will assign a number and no one but the transaction which issued that statment will be given that value.

Galit.