| Author |
Message
|
| Gaya3 |
 Posted: Tue Feb 19, 2008 5:34 am Post subject: Auto Sequencing from ESQL |
 |
|
Jedi
Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
|
Hi All,
I would like to create Auto Incrementing with Sequences, strictly on database part.
my intention is to create a auto sequence variable (Primary Key) and insert to the database this i have to do from ESQL.
SET Environment.TRANS_SEQ = PASSTHRU ('CREATE SEQUENCE TRANS_SEQ INCREMENT BY 1 START WITH 1') ;
Prompts that doesn't have privileges to do the above command.
at database level we will create a seq variable, through that we usually insert the value.
From ESQL how can we do the same
Regards
Gayathri
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die |
|
| Back to top |
|
 |
| AJStar |
| Posted: Tue Feb 19, 2008 7:53 pm Post subject: Re: Auto Sequencing from ESQL |
|
|
Acolyte
Joined: 27 Jun 2007
Posts: 64
|
| Gaya3 wrote: |
| SET Environment.TRANS_SEQ = PASSTHRU ('CREATE SEQUENCE TRANS_SEQ INCREMENT BY 1 START WITH 1') ; |
This is not supported through ESQL.
You might want to use a stored proc that executes the query and returns the sequence number.
_________________
Regards
AJ
Last edited by AJStar on Wed Feb 20, 2008 11:38 pm; edited 1 time in total |
|
| Back to top |
|
|
| VivekMeshram |
| Posted: Tue Feb 19, 2008 9:25 pm Post subject: |
|
|
Voyager
Joined: 25 Mar 2002
Posts: 83
|
Hi Gayathri,
I have one suggestion; see whether this fit for your requirements.
CREATE Database seq as follows
CREATE SEQUENCE TRANS_SEQ
AS INTEGER
START WITH 24000
INCREMENT BY 1
MINVALUE 24000
MAXVALUE 99999
CYCLE
CACHE 500
ORDER;
Once sequence has been created, to get next sequence number use the following ESQL
SET Environment.SeqNum[] = PASSTHRU('SELECT NEXTVAL FOR TRANS_SEQ FROM SYSIBM.SYSDUMMY1')
This is I had done to append the incremental seq no to output file for target system. I hope this will help you if you have this sort of requirement.
_________________
Thanks
Vivek S Meshram.
·IBM Certified Specialist – IBM WebSphere MQ v5.3 / v5.2 |
|
| Back to top |
|
|
| mgk |
| Posted: Wed Feb 20, 2008 1:32 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
You could do this in ESQL if you used a SHARED int variable, and wrote a function that added one to it (in an ATOMIC block if you use additional instances) and returned it to the caller...
Regards,
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| Gaya3 |
| Posted: Thu Feb 21, 2008 9:23 pm Post subject: |
|
|
Jedi
Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
|
Hi
Why this is not working in my ESQL
Where i declared a variable for TEST_SEQ as Decimal which Start with 1
INSERT INTO Database.TEST(ID,NAME) VALUES ('TEST_SEQ.NEXT_VAL','Gayathri');
Error:Character value insertion
Tested both the cases,
INSERT INTO Database.TEST(ID,NAME) VALUES (TEST_SEQ.NEXT_VAL,'Gayathri');
Error : Violating the constraints
Note: i have created the sequence variable at Backend side
Regards
Gayathri
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die |
|
| Back to top |
|
|
| AJStar |
| Posted: Thu Feb 21, 2008 9:42 pm Post subject: |
|
|
Acolyte
Joined: 27 Jun 2007
Posts: 64
|
| Gaya3 wrote: |
Hi
Why this is not working in my ESQL
INSERT INTO Database.TEST(ID,NAME) VALUES ('TEST_SEQ.NEXT_VAL','Gayathri');
|
| AJStar wrote: |
| This is not supported through ESQL. |
_________________
Regards
AJ |
|
| Back to top |
|
|
| Gaya3 |
| Posted: Thu Feb 21, 2008 9:48 pm Post subject: |
|
|
Jedi
Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
|
Hi
Then how can i insert a next value counter to the database,
are there any way....
even i tried selecting the next value from the database, its returning NULL value here
Regards
Gayathri
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die |
|
| Back to top |
|
|
| AJStar |
| Posted: Thu Feb 21, 2008 9:56 pm Post subject: |
|
|
Acolyte
Joined: 27 Jun 2007
Posts: 64
|
| mgk wrote: |
| You could do this in ESQL if you used a SHARED int variable, and wrote a function that added one to it (in an ATOMIC block if you use additional instances) and returned it to the caller... |
If you want only DB to generate, then
| AJStar wrote: |
| You might want to use a stored proc that executes the query and returns the sequence number. |
_________________
Regards
AJ |
|
| Back to top |
|
|
| AkankshA |
| Posted: Thu Feb 21, 2008 10:02 pm Post subject: |
|
|
Grand Master
Joined: 12 Jan 2006
Posts: 1494
Location: Singapore
|
| Gaya3 wrote: |
Hi
Then how can i insert a next value counter to the database,
are there any way....
even i tried selecting the next value from the database, its returning NULL value here
Regards
Gayathri |
you have declared the seq in the DB ... so first extract the next value and then insert
_________________
Cheers |
|
| Back to top |
|
|
| Gaya3 |
| Posted: Fri Feb 22, 2008 4:53 am Post subject: |
|
|
Jedi
Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
|
Hi
I declared a sequence value at Database side
Declared a variable on ESQL to store the next value by selecting
I dont know why i am getting a NULL value here again
Regards
Gayathri
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die |
|
| Back to top |
|
|
| AkankshA |
| Posted: Sun Feb 24, 2008 10:46 pm Post subject: |
|
|
Grand Master
Joined: 12 Jan 2006
Posts: 1494
Location: Singapore
|
have you initialised the sequence??
do u increment it ??
can u post your code here....
_________________
Cheers |
|
| Back to top |
|
|
| Gaya3 |
| Posted: Sun Feb 24, 2008 11:04 pm Post subject: |
|
|
Jedi
Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
|
Hi
This is the way that i have declared,
even i tried to store the value by using
--Environment.Seq_val = (Select Item t.next_Value from Test as t);
DECLARE TEST_SEQ DECIMAL 1;
INSERT INTO Database.TEST(ID,NAME) VALUES (TEST_SEQ.NEXT_VALUE,'Gayathri');
As its getting a null value when i tried to get the sequence value from Database,
the next if i initialize the variable its not incrementing
Regards
Gayathri
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die |
|
| Back to top |
|
|
| JLRowe |
| Posted: Mon Feb 25, 2008 6:00 am Post subject: |
|
|
Yatiri
Joined: 25 May 2002
Posts: 664
Location: South East London
|
| Gaya3 wrote: |
Hi
This is the way that i have declared,
even i tried to store the value by using
--Environment.Seq_val = (Select Item t.next_Value from Test as t);
DECLARE TEST_SEQ DECIMAL 1;
INSERT INTO Database.TEST(ID,NAME) VALUES (TEST_SEQ.NEXT_VALUE,'Gayathri');
As its getting a null value when i tried to get the sequence value from Database,
the next if i initialize the variable its not incrementing
Regards
Gayathri |
What is your database?
Go ask your dba how to do it in a single statement. In oracle for example, doing it in a single statement is only supported in 10g. Before that, you had to do a select from sequence, and then use that value to insert.
Test you SQL outside broker first. |
|
| Back to top |
|
|
| AkankshA |
| Posted: Tue Feb 26, 2008 2:04 am Post subject: |
|
|
Grand Master
Joined: 12 Jan 2006
Posts: 1494
Location: Singapore
|
| Quote: |
This is the way that i have declared,
even i tried to store the value by using
--Environment.Seq_val = (Select Item t.next_Value from Test as t);
DECLARE TEST_SEQ DECIMAL 1;
INSERT INTO Database.TEST(ID,NAME) VALUES (TEST_SEQ.NEXT_VALUE,'Gayathri');
As its getting a null value when i tried to get the sequence value from Database,
the next if i initialize the variable its not incrementing |
ok
let me say how i do it..
declare a sequence in DB
get its value using passthru
SET Environment.SQLBox[] = PASSTHRU('SELECT FILTER_SEQ.NEXTVAL FROM DUAL')
use this value for further processing
Environment.SQLBox.NEXTVAL
_________________
Cheers |
|
| Back to top |
|
|
| Gaya3 |
| Posted: Tue Feb 26, 2008 4:50 am Post subject: |
|
|
Jedi
Joined: 12 Sep 2006
Posts: 2493
Location: Boston, US
|
Hi
Its working for me....
but here the problem is , it retrieves 7 sequence numbers at a stretch, if i run again it will take the 8th value onwards
say for eg: it starts with 1,2,3,4,5,6,7
it will insert 1 to database, but next time it will insert 8
This is what i am facing now
Regards
Gayathri
_________________
Regards
Gayathri
-----------------------------------------------
Do Something Before you Die |
|
| Back to top |
|
|
|
|
