Merge

I am trying to merge 2 parts.
when I use it just as SQL script it works, but I recently made it through the PowerBuilder Window.
Could someone tell me what am I doing wrong?
Sometime it works, sometimes doesn't.
May be I miss something.
Thanks for your help in advance.

//With an embedded SQL

//cb_merge.Clicked()
//===================

Long ls_oid_to_delete, ls_oid_to_keep, to_item_oid, from_item_oid
String  temp_keep, temp_delete, from_sn, to_sn
int inv_status_oid, to_aircraft_oid, to_city_oid, to_status_oid
Datetime inv_move_date
//Get oid's from SingleLineEdit control

//temp_keep = sle_1.text
//temp_delete   = sle_2.text

ls_oid_to_keep = long(sle_1.text)
ls_oid_to_delete = long(sle_2.text)

to_city_oid = dw_1.getItemNumber(1, 'city_oid')
to_aircraft_oid = dw_2.getItemNumber(1, 'aircraft_oid')
to_status_oid = dw_3.getItemNumber(1, 'status_oid')
// insert oids as necessary -
// system will merge the two records into the "oid_to_keep"
// and purge the "oid_to_delete"

Select Serial_Number, item_oid into :from_sn, :from_item_oid from rotable_inventory, item_Master where rotable_inventory.item_oid = item_master.oid and rotable_inventory.oid = :ls_oid_to_delete;

Select Serial_Number, item_oid into :to_sn, :to_item_oid from rotable_inventory, item_master where rotable_inventory.item_oid = item_master.oid and rotable_inventory.oid = :ls_oid_to_keep;

Select Max(inv_move_date) into :inv_move_date from inv_move_log where inv_oid = :ls_oid_to_keep;

update inv_move_log
      set inv_oid = :ls_oid_to_keep,
            upd_timestamp = getdate()
      where inv_oid = :ls_oid_to_delete;

update work_order_data
      set inv_oid = :ls_oid_to_keep,
            upd_timestamp = getdate()
      where inv_oid = :ls_oid_to_delete;

update wo_inv_action
      set inv_oid = :ls_oid_to_keep,
            upd_timestamp = getdate()
      where inv_oid = :ls_oid_to_delete;

update mc_rotable
      set inv_oid = :ls_oid_to_keep,
            upd_timestamp = getdate()
      where inv_oid = :ls_oid_to_delete;

update purchase_order_data
      set inv_oid = :ls_oid_to_keep,
            upd_timestamp = getdate()
      where inv_oid = :ls_oid_to_delete;

update rotable_inventory
      set status_oid = 2428,
      upd_timestamp = getdate()
      where oid = :ls_oid_to_delete;

delete from rotable_inventory where oid = :ls_oid_to_delete;

Insert into Inv_Move_log (inv_oid, to_inv_status_oid, to_on_aircraft_oid,to_city_oid,  create_Date, inv_move_date, From_serial_no, To_serial_no, from_item_oid, to_item_oid, Updated_by_sym, upd_timestamp)
                                     Values(:ls_oid_to_keep, :to_status_oid, :to_aircraft_oid, :to_city_oid, getutcdate(), :inv_move_date, :from_sn, :to_sn, :from_item_oid, :to_item_oid, :g_login_id, getutcdate());
MessageBox("Merge","Merge is Complete. Database has been updated.")
Close(w_inventory_merge)
Namik48075Asked:
Who is Participating?
 
sandeep_patelConnect With a Mentor Commented:
Hi,

I think it looks like database related problem....

1) check which driver you are using for connectivity.
2) is your stored procedure compiled and valid ?

in your code first comment stored procedure declaration and execution part and try to compile. If it compiles then it's sure that problem is in declaration and calling stored procedure..

Regards,
Sandeep
0
 
Namik48075Author Commented:
I have changed it to be done with Stored procedure.
Now its giving me an error message

//With an embedded SQL

//cb_merge.Clicked()
//===================
Long ls_oid_to_delete, ls_oid_to_keep, to_item_oid, from_item_oid
int  to_aircraft_oid, to_city_oid, to_status_oid


//temp_keep = sle_1.text
//temp_delete   = sle_2.text

ls_oid_to_keep = long(sle_1.text)
ls_oid_to_delete = long(sle_2.text)

to_city_oid = dw_1.getItemNumber(1, 'city_oid')
to_aircraft_oid = dw_2.getItemNumber(1, 'aircraft_oid')
to_status_oid = dw_3.getItemNumber(1, 'status_oid')

//IF SQLCA.SQLCode <> 0 THEN
//   MessageBox ( "Error", "DECLARE failed" )
//   RETURN
//END IF;
DECLARE test_inventory_merge procedure for sp_inventory_merge(:ls_oid_to_keep, :ls_oid_to_delete, :to_aircraft_oid, :to_status_oid, :to_city_oid) using SQLCA;

EXECUTE test_inventory_merge;
//IF  ( SQLCA.SQLCode <> 0   ) &
//and ( SQLCA.SQLCode <> 100 ) THEN
//   MessageBox ( "Error", "EXECUTE failed" )
//   RETURN
//END IF

MessageBox("Merge","Merge is Complete. Database has been updated.")


Compile Error:

Database C0038: Line 1: Incorrect syntax near '0';

Please help.

Thanks  a lot.
0
 
sandhiyaaCommented:
Did you executed the procedure seperately at the backend?
Regards,
Sandhiya
0
All Courses

From novice to tech pro — start learning today.