Recent

Author Topic: 2 tables, primary key, foreign key- HELP  (Read 6502 times)

abmservice

  • Jr. Member
  • **
  • Posts: 69
2 tables, primary key, foreign key- HELP
« on: December 20, 2009, 04:46:03 pm »
I have 2 tables. I must add to the table first and get some data master key to insert the master key in place of the foreign key table 2. I do it all in 1 transaction - Component Sqlquery and firebird 2.1. how to do it?

Loesje

  • Full Member
  • ***
  • Posts: 168
    • Lazarus Support website
Re: 2 tables, primary key, foreign key- HELP
« Reply #1 on: December 28, 2009, 11:17:40 pm »
Seems all pretty straight-forward to me. What did you try?

abmservice

  • Jr. Member
  • **
  • Posts: 69
Re: 2 tables, primary key, foreign key- HELP
« Reply #2 on: December 29, 2009, 02:36:53 pm »
You can use this feature http://www.firebirdfaq.org/faq243/.
how to do it?

clauslack

  • Sr. Member
  • ****
  • Posts: 275
Re: 2 tables, primary key, foreign key- HELP
« Reply #3 on: December 29, 2009, 03:10:16 pm »
Works this http://www.firebirdfaq.org/faq243/
with SQLDb fpc 2.3.1, Firebird 2.1.2
In memo1 write
insert into imagenes (Socio) values (21095) returning clave_imagen
Code: [Select]
procedure TForm1.Button1Click(Sender: TObject);
begin
    IBConnection1.Open;
    if SQLTransaction1.Active then SQLTransaction1.Commit;
    SQLQuery1.close;
    SQLQuery1.SQL.clear;
    SQLQuery1.SQL.AddStrings(memo1.lines);
    SQLTransaction1.StartTransaction;
    SQLQuery1.open;
end;
In SQLQuery1.FieldByName('clave_imagen').AsInteger is the primary key.

The other way is create a stored procedure for seach the primary key(SQLQuery1.FieldByName('clave_generada').AsInteger), then you save the primary key with a normal slq update.
Firebird code, generator
Code: [Select]
CREATE GENERATOR GEN_IMAGENES_ID;
Stored Procedure
Code: [Select]
SET TERM ^ ;
CREATE PROCEDURE GENERA_CLAVE_IMAGEN
RETURNS (   CLAVE_GENERADA Integer )
AS
BEGIN
    clave_generada = GEN_ID(GEN_IMAGENES_ID, 1);
    suspend;
END^
SET TERM ; ^

With SQLTransaction, you can save the primary key in many tables with the same Transaction.

Regards



abmservice

  • Jr. Member
  • **
  • Posts: 69
Re: 2 tables, primary key, foreign key- HELP
« Reply #4 on: December 29, 2009, 07:55:23 pm »
I tried but the FPC 2.2.4 and LAZARUS 0.9.28.2 can not work because

abmservice

  • Jr. Member
  • **
  • Posts: 69
Re: 2 tables, primary key, foreign key- HELP
« Reply #5 on: December 29, 2009, 08:17:57 pm »
I am grateful for his help

 

TinyPortal © 2005-2018