how to insert /update parent-child tables in Sybase

I have to create procedure to insert or update tables which have relationship, for example
tableA (aID is primary key)
aID value1 value2
1     1000     2000

tableB (bID is primary key)
bID aID values3 value4
101   1    fjdksal    20

now I have an application want to call  a procedure to insert new values or update the data,  

--in the procedure, what is the easy way to insert or update these parent-child tables?
  using scope_identity() , it gives error message

--no triger in the database , so have to pass the aID into the procedure ,right?

thanks








SayYou_SayMeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jan FranekCommented:
Can you be more specific ? What do you want to achieve ? What is the input for your procedure and what is desired effect on your tables fot that input ?
0
SayYou_SayMeAuthor Commented:
want to insert  values1, values2,values3,value4 into database
so input should be these values

 
0
momi_sabagCommented:
which error do you get from scope_identity()?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Jan FranekCommented:
How do you assign aID and bID ? Are these columns identity ?
0
SayYou_SayMeAuthor Commented:
--aID and bID are primary key
--delare @aID int
   insert into tableA (aID,values1,values2)
   values(2,300,24)
   set @aID=scope_identity()
 insert   into tableB (bID,aID,value3,value4)
   values(102,@aID,'dfads',2)

it give error massege 'Function scope_identity() not found
0
Jan FranekCommented:
According to http://www.sybase.com/content/1047976/MSSQL-ASE-Guide-111006-wp.pdf there is no equivalent to MS SQL function scope_identity.

However, you may use @@identity global variable:


create procedure insert_data ( @value1, @value2 ... )
as
declare @aID int
insert into tableA (value1, value2) values (@value1, @value2)
select @aID = @@identity
insert into tableB (aID, value1, value2) values (@aID, @value1, @value2)

Open in new window

0
Jan FranekCommented:
I don't understand, why do you need to assign @aID, if you know the value (in the first insert it is 2)
0
ProjectChampionCommented:
If I understand your question correctly you want to select the IDENT_CURRENT('tablename'). This returns the last IDENTITY value produced in the specified table, regardless of the connection that created the value, and regardless of the scope of the statement that produced the value.
0
SayYou_SayMeAuthor Commented:
create procedure abc
as
begin
declare @aID int
insert tableA(value1)
values(649)
select @aID=@@identity
insert tableB(aID)
values(@aID)
end

get error message : Foreign key constraint violation occurred

if I change it like this :
create procedure abc
as
begin
declare @bID int
insert tableA(value1)
values(649)
select @bID=@@identity
insert tableB(bID)
values(@bID)
end


the value is inserted into the tableA, like this
aID value1 value2
1     1000     2000
2     649

but the tableB no record be inserted
bID aID values3 value4
101   1    fjdksal    20


experts , any comment ?

Thanks



0
Jan FranekCommented:
Can you post full definition of your tables (including foreign contraints) ?
0
ProjectChampionCommented:
Based on what you've posted so far, you need to something like this

create procedure abc
as
begin
declare @aID int
insert tableA(value1)
values(649)
select @aID=Ident_Current(N'tableA')
insert tableB(aID)
values(@aID)
end
0
Jan FranekCommented:
As far as I understand, SayYou_SayMe is on Sybase ASE database, so there's not IDENT_CURRENT function.

And even if he is on MS SQL, I wouldn't recommend using IDENT_CURRENT if he is not the only user connected to server, because "The last identity value generated can be for any session and any scope" - see http://msdn.microsoft.com/en-us/library/ms175098.aspx
0
ProjectChampionCommented:
Thaks "Jan Franek", you're absolutely right! For a minute I forgot to take my MSSQL hat off.  : )
In Sybase as far as I know we have only @@identity - correct me if I'm wrong.

Dear "SayYou_SayMe",
Back to your question, and in a hind-sight I think the logic of your solution is correct. However the FK violation error, suggest that you might need to commit the insert to the first table first, or at least try to rewrite the whole procedure using nested-transactions. Does it make sense?
0
SayYou_SayMeAuthor Commented:
Thanks guys
could you please help me out  to insert all values into the database?
I have to insert all values into the tables : values1,2,3,4,5,6,7
see the attachment of ERD
thanks

untitled.bmp
0
Jan FranekCommented:
The following procedure (ok - skeleton of procedure) should work in my opinion. But you wrote about foreign constraint violation in comment ID 30156970 - I must admit, that I don't understand, why did you get that error. It seems to me, that with given tables it is not possible.
create proc abc ( @value1 ... @value7 )
as
declare @aID, @bID, @dID
begin tran
insert into tableA...
select @aID = @@identity
insert into tableB...
select @bID = @@identity
insert into tableD...
select @dID = @@identity
insert into tableC...
insert into tableE...
commit

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ProjectChampionCommented:
I believe Jan is right. The above script is the right way to implement such an operatoin, but as Jan has already said correctly it's unlikely to work taking into account your FK constraints - which I'm sure you have a justifiable reason to have them in place.

In case you get FK violation error again the you probably need to make sure every insert is committed before trying to use the @@ID in the next step so it'd be something like this:

Good Luck : )
create proc abc ( @val1 ... @val7 ) 
as 
begin
	declare @aID, ..., @eID
	begin tran 
	  insert into tableA values(@val1)
	  select @aID = @@identity 
	commit tran
	begin tran 
	  insert into tableB values(aID, @val2) 
	  select @bID = @@identity 
	commit tran
	begin tran 
	...
	...
	commit tran
	begin tran 
	  insert into tableF values(@eID, @val7)
	commit tran
end

Open in new window

0
SayYou_SayMeAuthor Commented:
create procedure aaa
as
begin
  declare @aID int
  begin tran
  insert into tableA(name)
  values('dfja;hfdajdh')
  select @aID=@@identity
  commit tran
  begin tran
  insert into tableB(aID)
  values(@aID)
  commit tran
end





tableA.bmp
0
SayYou_SayMeAuthor Commented:
tableB
tableB.bmp
0
SayYou_SayMeAuthor Commented:
relationship
relationship.bmp
0
SayYou_SayMeAuthor Commented:
errorMessage
errorA.bmp
0
SayYou_SayMeAuthor Commented:
I still get error messsage

experts, any thought?
0
SayYou_SayMeAuthor Commented:
error message shows:

Foreign key constraint violation occurred
0
Jan FranekCommented:
I can't see in your tables definitions, that aID and bID are identity columns. Not every primary key has to be identity.

Try to print out @@identity value after insert into tableA to see, where this value is really set and to see if there is really record in tableA with this aID.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.