Update specific Interbase record question. 1

[reisende]

Hi everybody. We are moving into InterBase for our tables instead of using flat files (DBF's) and I am stuck on something.

I am designing a daily tracking application for sales calls and the like and need to be able to update an existing record in an InterBase table.

I have a stored procedure and a generator which create a new record and increment the primary key when the application is initialized.

I'm using a page control to separate many data entry points and would like to update the just created record with newly entered data after the user moves to the next page(s).

My problem is I can't figure out how to get the GEN_ID (primary key) into an UPDATE SQL statement so the app knows which record to update.

I'm stuck here:
[tt]
UPDATE DT_COMPLETE SET CM_CLINIC = :clinic, CM_WEEK_OF = :week_of, CM_WHO_ADDED = :user, CM_REF_TH = :ref_th, CM_REF_FR = :ref_fr, CM_REF_MN = :ref_mn, CM_REF_TS = :ref_ts, CM_REF_WD = :ref_wd, CM_REF_TOTAL = :ref_total WHERE CM_ID = :id;
[/tt]

The :id param needs to be populated with the generated primary key. I'm used to doing this with the web where I can use sessions and queryStrings, I'm just not sure how to translate it to Delphi 7.

Any help would be greatly appreciated.

 

[hilfy]

Can you set up the stored procedure to return to you the id of the record that it just created?

-D

 

[reisende]

That is a good question . I wish I wasn't such a newbie with stored procedures, but I haven't worked with them much at all in the past. In fact, today's the first time I ever used one. I'm just using examples I got from our senior programmer to learn from.

I'm new to role of programming, all my experience is with the web, so I'm trying to learn as much as I can.

Could I trouble you for an example and how I'd need to make sure it gets into my update statement?

Thanks for the quick response.

 

[hilfy]

Unfortunately, I'm not very familiar with stored procs in Interbase - we use Oracle here with the DOA data access components that make this easy to do.

If someone doesn't read this and give you an answer, you may want to repost this question specifically in a new thread.

-D

 

[svanels]

To obtain the last created number from the database
use:
Select Gen_id(ID_NUMBER_GEN,0) from rdb$Database into :SAMPLE_ID;

where :sample_id is a integer variable


I had an similar problem, and solved it entirely using Interbase stored procedures.

I had a big table split up in several small tables, based on the type of data, but all of them are linked by a primary key field.
In the main table I used a trigger and generator to create the ID number.

CREATE TRIGGER SAMPLE_INSERT FOR RSAMPLE
ACTIVE BEFORE INSERT POSITION 0
AS BEGIN
NEW.RNUM = GEN_ID(ID_NUMBER_GEN,1);
END


When this is done the newly generated number must be communicated to the dependent tables. This is done by a trigger that executes a stored procedure.


CREATE TRIGGER PREPARE_LINKS FOR RSAMPLE
ACTIVE AFTER INSERT POSITION 0
AS BEGIN
execute procedure PREPARE_DEPENDANT_TABLES;
END

The current value of the generator is obtained and inserted in the other tables


CREATE PROCEDURE PREPARE_DEPENDANT_TABLES
AS
DECLARE VARIABLE SAMPLE_ID integer;
begin
Select Gen_id(ID_NUMBER_GEN,0) from rdb$Database into :SAMPLE_ID;
INSERT INTO R_CW (RNUM) values SAMPLE_ID);
INSERT INTO R_OIL (RNUM) values SAMPLE_ID);
....etc.
INSERT INTO R_MISC (RNUM) values SAMPLE_ID);
end



The triggers in Interbase are similar to the before post and after post in delphi.

The advantage of this approach is that it is application independent. If you create in your application a new record, Interbase will take care that the links are implemented and maybe a simple refresh of your dataset will resolve your case

Regards

Steven van Els
SAvanEls@cq-link.sr

 

[reisende]

Thanks Steven,

My table isn't as complicated as yours, but you gave me a good idea. I'll just use that SQL to get the current GEN_ID and use that as a parameter to pass to my update statement.

I wish I was more comfortable with Stored Procedures, but I guess that will come with time.