inserting a sequence value to the table

[Aravind Prasad]

Hello Ranchers!!
I'm using JSP and Oracle to do a project.
there is a table and the table details are here!!


==========================================================================
column nam
==========================================================================
register_num
name
age
=========================================================================

here for the register_num it is the primary key and also have a sequence generated..
the reister_num is a varchar2(20) datatype..
and tha value it assigns is
ERP/05/2006/001
here ERP is the sting that i passes
/05/2006 is the current month and the year..
after doing all these thing i can do concatenate the string to produce like this

i'm using a condition also..
if(no >0 && no<=9) val = ERP/05/2006/00000
if(no >=10 && no <=99) val = ERP/05/2006/0000
if(no >=100 && no <=999) val = ERP/05/2006/000
if(no >=1000 && no <=9999) val = ERP/05/2006/00
if(no >=10000 && no <=99999) val = ERP/05/2006/0

this is what i've to do ..
but there is no change in the sequence..
the value that has to the inserted is

ERP/05/2006/000001
ERP/05/2006/000002
ERP/05/2006/000003
ERP/05/2006/000004
ERP/05/2006/000005
-----
ERP/05/2006/000100
ERP/05/2006/000101 like that...

---------------------------------------------------------------
so if i insert the value the query will be like this

insert into table_name(register_no,name,age) values(reg_no.nextval,name,age)

---------------------------------
this query is doing the inserting proceedure..
but it is just inserting the register_no as 1,2,3 etc

i want to chage that value and has to insert the String like this
ERP/05/2006/000001
================

please help me ranchers..
how can i do this problem

thanks in advance..
regards
Aravind Prasad

[stu derby]

Generally speaking, using a long String/varchar/varchar2 as a primary key tends to hurt performance, mostly because of the larger storage size required for the key. For example, your primary key index is going to be something like 3 to 5 times bigger using a varchar2(20) than if you used a number. Getting the index from disk will take longer and comparing 2 keys will take longer.

However, you're probably already stuck on with that decision...

You can do something like:

SQL = "insert into table_name (register_no,name,age) values (?||to_char(sysdate,'/mm/yy/')||to_char(reg_no.nextval,'000000'),?,?)";
PreparedStatement ps = connection.prepareStatement(SQL);
ps.setString(1, "ERP");
// bind the values for name and age 
ps.set??? ...
ps.set??? ...
ps.executeUpdate();

[Aravind Prasad]

thank you very much STU..
I can now insert the value like this
ERP/05/2006/00001

but there is also a problem for this
it is taking a space value
ie., the value inserting in table is
ERP/05/2006/ 00001
there is a space b/w 2006/ and 00001

i'm copying the whole.. Prepare Statement..
===========================================================

String query = "insert into system.ERP_MAIN(id,username,password,confirm,admin,status,country,subcountry,branch)values(?||to_char(sysdate,'/mm/yyyy/') || to_char(system.login_user_id.nextval,'0000'),?,?,?,?,?,?,?,?)";

PreparedStatement ps = dbCon.prepareStatement(query);
ps.setString(1,"ERP");
ps.setString(2,username);
ps.setString(3,pass1);
ps.setString(4,pass2);
ps.setInt(5,0);
ps.setInt(6,0);
ps.setInt(7,0);
ps.setInt(8,0);
ps.setString(9,branch);


==================================================
After executing it is giving a space..
how can i fix that space.. there!!

[Megha Rajeevan]

Hello Aravind..
If i'm using some 2 tables..
reg_id is the primary key of 1st table..
using this id only we are inserting the user details to second table

if i want to insert the value to next table.. then
i want to get the reg_id which is stored in the table.. isn't it..
so how can i get that value..

please help me..

thank you
regards
Megha

[Shailesh Chandra]

you can use trim to fix the problem, however I am not sure why this problem is occuring.

To verify same I executed the query

select  length(to_char(1,'0000')) from dual

and I got result 5 on my Oracle 10g

Use this code written below in your method.

String query = "insert into system.ERP_MAIN(id,username,password,confirm,admin,status,country,subcountry,branch)values(?||to_char(sysdate,'/mm/yyyy/') || trim(to_char(system.login_user_id.nextval,'0000')),?,?,?,?,?,?,?,?)";



Shailesh

[Aravind Prasad]

Thank you very much Shailash!!

Now it is working properly.. and one more thing..
is there any other way to take this value to any variable like megha asked.

ie., the value of reg_id.nextval
the value is 12345
in the next table i want to insert using the query

insert into table_Two (reg,name,sex) values('value_stored_in_variable','name','sex');

is there any other way!!

[Megha Rajeevan]

Exactly Aravind.
But how to insert the value to a variable..
or else.. we have to write another query.. and get the last value stored in the array like this
this is just an example


select id from table_name order by id;
while(rs.next())
{
}

[Megha Rajeevan]

Exactly Aravind.
But how to insert the value to a variable..
or else.. we have to write another query.. and get the last value stored in the array like this
this is just an example


select id from table_name order by id;
ArrayList test = new ArrayList();
while(rs.next())
{
test.add(rs1.getString(1));
}
Object t[] = test.toArray();
int leng = t.length;
String new_reg_val= t[leng];



like this it is ok..
but if there is any query or something like this
then that will reduce the pain..

any way ranchers will help!! for that

regards
Megha

[Aravind Prasad]

Hello Ranchers
is this the only way to do so..
like what megha done the code..

is there any other way to do so..

please help me ranchers..
it is also very urgent!!

thanks in advance

please reply me
regards
Aravind Prasad

[Shailesh Chandra]

It completely depends on your implementation !!! If you want to use same sequence number twice !! then that is one way to do it.

For me I never rely on sequences directly. because If your transaction fails, the sequence number does not rollback.

I always use sequence number pool, using singleton classes. If transaction fails I return the sequence number to pool

At a time I keep a configurable number of sequences in pool.

So it totally depends on your implementation

Shailesh

[Aravind Prasad]

Hello Ranchers
Again the sequence is creating problems with me..
so i've planned to put the column id as integer
so while using the insert statement
how to write!! that

String sql = insert into table_name (id,name,age)values(reg_id.nextval,name,age);
PreparedStatement ps = dbCon.prepareStatement(query);
ps.setInt(1,0);
ps.setString(2,name);
ps.setInt(3,age);


what shoul i put in the place of setInt..

please reply me ranchers

thanks in advance

regards
Aravind Prasad