From: Richard Huxton <dev@archonet.com>
To: Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
Cc: Scott Marlowe <smarlowe@g2switchworks.com>
Cc: pgsql-sql@postgresql.org
Cc: pgsql-admin@postgresql.org
Subject: Re: [SQL] Deadlock on transaction
Date: Mon, 12 Feb 2007 19:50:49 +0000
Message-ID: <45D0C519.2080107@archonet.com> (raw)
In-Reply-To: <55c095e90702121120s5b98a2d8u4a05a31f9625ffa@mail.gmail.com>
References: <55c095e90702120943v2909044eo91445b0eda3958c7@mail.gmail.com>
<1171302488.3565.15.camel@state.g2switchworks.com>
<55c095e90702121008u6d3d27c0m16e52d63f97c3e7d@mail.gmail.com>
<45D0B565.9060402@archonet.com>
<55c095e90702121047x1c66b098xbcf0d308195ff584@mail.gmail.com>
<45D0B8A4.7000602@archonet.com>
<55c095e90702121120s5b98a2d8u4a05a31f9625ffa@mail.gmail.com>
Ezequias Rodrigues da Rocha wrote:
> This is delphi. I don't intent you understand but the sql actions are quite
> simple (I am reading a list of numbers).
Well, let's see - the last Pascal I did was in 1986 I think...
> If not dm.database1.InTransaction then
> dm.database1.StartTransaction;
>
> For i:= 0 to memo1.Lines.Count - 1 do
Loop through your import.
> Begin
> // Catching ID
> dm.qQ1.SQL.text:= Concat('select id from base.cartao where
> numero = ', memo1.lines[i]);
> dm.qQ1.open;
> cartaoID:= dm.qQ1.fieldByName('id').asString;
> // Cathing the ticket ID
OK, you're fetching ticket IDs one at a time.
> dm.qQ1.SQL.clear;
> dm.qQ1.SQL.text:= Concat('select nextval(',QuotedStr('
> base.ingresso_id') , ')');
> dm.qQ1.open;
> IngressoID:= dm.qQ1.fieldByName('nextval').asString;
And now you've generated a new ID from a sequence.
> // $$$$$$$$$$ Recording Tickets $$$$$$$$$$
> dm.Qq1.sql.text:= Concat('Insert into base.ingresso values ('
> , QuotedStr(IngressoID), ',' , EstadoID, ',' , 'now()', ',' , valor, ',' ,
> valor_promotor, ',' , AssentoID, ',', CaixaID, ',' , CartaoID, ',' ,
> PromocaoID, ',' , SessaoID, ',' , VendedorID, ')');
> // Showmessage(dm.Qq1.sql.text);
> dm.Qq1.execSQL;
Insert one row into "ingresso".
> // ########### Recording Tickets ###########
>
> // Pegando o Id do Bilhete no PostgreSQL
> dm.qQ1.SQL.clear;
> dm.qQ1.SQL.text:= Concat('select nextval(',QuotedStr('
> base.bilhete_id') , ')');
> dm.qQ1.open;
> BilheteID:= dm.qQ1.fieldByName('nextval').asString;
>
> dm.qQ1.SQL.clear;
> dm.qQ1.SQL.add(Concat('Insert into base.bilhete (id, estado,
> uso_sequencia, promocao_documento, assento_id, cartao_id, ingresso_id,
> promocao_id, sessao_id, vendedor_venda_id )'));
> dm.qQ1.SQL.add(Concat('values(', BilheteID, ',' ,
> QuotedStr(EstadoID), ',' , '0,0', ',' , QuotedStr(AssentoID), ',' ,
> QuotedStr(CartaoID), ',' , QuotedStr(IngressoID) , ',' ,
> QuotedStr(PromocaoID), ',' , QuotedStr(SessaoID) , ',' ,
> QuotedStr(VendedorID), ')' ) );
>
> dm.qQ1.execSQL;
Insert a row into "bilhete".
>
> end;
I don't see anything like "dm.database1.CommitTransaction" - are you
sure you are committing after the inserts?
One other point - if Delphi doesn't offer something like it, you might
find it useful to write a function that makes it easier to build queries:
my_db_func('INSERT INTO foo (id,a,b,c) VALUES (?,?,?,?)',
'NUM,TEXT,DATE,DATE',
variable1,variable2,variable3...);
I'd be surprised if there wasn't something like that already though.
--
Richard Huxton
Archonet Ltd
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, ezequias.rocha@gmail.com, smarlowe@g2switchworks.com, pgsql-admin@postgresql.org
Subject: Re: [SQL] Deadlock on transaction
In-Reply-To: <45D0C519.2080107@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox