Board index » delphi » Credit/Debit tmp table

Credit/Debit tmp table


2006-02-09 08:07:06 PM
delphi158
Plus Minus Credit Debit
100 0 100 0
0 50 50 0
0 100 0 50
The data is Plus and Minus, and the credit and debits are calculated field,
I created a temporary table that calculates these two field and wrote a SP
to prepare data. But in the multiuser environment, if two user wants to use
same events some conflictions occurs.
Is there any other solution without creating temp table,
Thanks
Kadir
 
 

Re:Credit/Debit tmp table

Kadir writes:
Quote
if two user wants to use same events some conflictions occurs.
What kind of conflict? What error message do you get. Temporary tables
are private to the connection so if each user has his/her own
connection there should not be any conflict.
An alternative solution is to use a selectable stored procedure to
return the result set and not use a temporary table.
--
Bill Todd (TeamB)
 

Re:Credit/Debit tmp table

Thanks for your answer,
I uses IB7.1, IBX, D7, I created a table (whose name is tmp only), on each
report
request, first empty, prepared data, fill them and calculate field values
so
according to rules I mentioned before. The confliction is, if two user
simultaneously uses same SP the data overritten.
But I am looking for short way if there is (without write any data) , I
appended the SP I uses but I am not sure the easiest way?
Kadir
CREATE PROCEDURE UCDETAY (
HESAPKODU VARCHAR (20), // Account number
PTIPI SMALLINT, // Type of money
TARIH1 TIMESTAMP, // start date
TARIH2 TIMESTAMP) // stop date
AS
declare variable B FLOAT;
declare variable A FLOAT;
declare variable SNO INTEGER;
declare variable AYTL FLOAT;
declare variable BYTL FLOAT;
declare variable AUSD FLOAT;
declare variable BUSD FLOAT;
declare variable AEUR FLOAT;
declare variable BEUR FLOAT;
declare variable DAD FLOAT;
declare variable C FLOAT;
BEGIN
DELETE FROM TMPCDETAY; // First empty the table (in fact it is
not temporary)
SELECT
SUM(ADET), SUM(BYTL), SUM(AYTL), SUM(BUSD), SUM(AUSD), SUM(BEUR),
SUM(AEUR)
FROM
MFIS M, DFIS D
WHERE
M.FISNO = D.FISNO AND HESAPKODU LIKE :HESAPKODU AND TARIH <= :TARIH1
and M.AKTIF=0
INTO
:DAD, :BYTL, :AYTL, :BUSD, :AUSD, :BEUR, :AEUR;
IF (:BYTL is not null and :PTIPI =1) THEN
BEGIN
if (:BYTL>:AYTL) then
BEGIN
INSERT INTO TMPCDETAY (TARIH, ACK, ADET, BORC, ALACAK)
VALUES (:TARIH1, 'DEVIR', :DAD,:BYTL - :AYTL, 0);
END
else
BEGIN
INSERT INTO TMPCDETAY (TARIH, ACK, ADET, BORC, ALACAK)
VALUES (:TARIH1, 'DEVIR', :DAD, 0, :AYTL - :BYTL);
END
END
IF (:BUSD is not null and :PTIPI = 2 ) THEN
BEGIN
if (:BUSD>:AUSD) then
BEGIN
INSERT INTO TMPCDETAY (TARIH, ACK, ADET, BORC, ALACAK)
VALUES (:TARIH1, 'DEVIR', :DAD,:BUSD - :AUSD, 0);
END
else
BEGIN
INSERT INTO TMPCDETAY (TARIH, ACK, ADET, BORC, ALACAK)
VALUES (:TARIH1, 'DEVIR', :DAD, 0, :AUSD - :BUSD);
END
END
IF (:BEUR is not null and :PTIPI = 3 ) THEN
BEGIN
if (:BEUR>:AEUR) then
BEGIN
INSERT INTO TMPCDETAY (TARIH, ACK, ADET, BORC, ALACAK)
VALUES (:TARIH1, 'DEVIR', :DAD,:BEUR - :AEUR, 0);
END
else
BEGIN
INSERT INTO TMPCDETAY (TARIH, ACK, ADET, BORC, ALACAK)
VALUES (:TARIH1, 'DEVIR', :DAD, 0, :AEUR - :BEUR);
END
END
IF (:PTIPI = 1) THEN
BEGIN
INSERT INTO TMPCDETAY (TARIH, ACK, FISNO, FATNO, IRSNO, ADET, BRFIYAT,
BORC, ALACAK)
SELECT TARIH, ACIKLAMA, D.FISNO, FATNO, IRSNO, ADET, BRFIYAT, BYTL, AYTL
FROM MFIS M, DFIS D
WHERE M.FISNO = D.FISNO AND HESAPKODU LIKE :HESAPKODU AND (M.TARIH>=
:TARIH1 AND M.TARIH <= :TARIH2)
AND AKTIF=0
ORDER BY M.TARIH, D.SNO;
END
IF (:PTIPI = 2) THEN
BEGIN
INSERT INTO TMPCDETAY (TARIH, ACK, FISNO, FATNO, IRSNO, ADET, BRFIYAT,
BORC, ALACAK)
SELECT TARIH, ACIKLAMA, D.FISNO, FATNO, IRSNO, ADET, BRFIYAT, BUSD, AUSD
FROM MFIS M, DFIS D
WHERE M.FISNO = D.FISNO AND HESAPKODU LIKE :HESAPKODU AND (M.TARIH>=
:TARIH1 AND TARIH <= :TARIH2)
AND AKTIF=0
ORDER BY M.TARIH, D.SNO;
END
IF (:PTIPI = 3) THEN
BEGIN
INSERT INTO TMPCDETAY (TARIH, ACK, FISNO, FATNO, IRSNO, ADET, BRFIYAT,
BORC, ALACAK)
SELECT TARIH, ACIKLAMA, D.FISNO, FATNO, IRSNO, ADET, BRFIYAT, BEUR, AEUR
FROM MFIS M, DFIS D
WHERE M.FISNO = D.FISNO AND HESAPKODU LIKE :HESAPKODU AND (TARIH>=
:TARIH1 AND TARIH <= :TARIH2)
AND AKTIF=0
ORDER BY M.TARIH, D.SNO;
END
C = 0;
FOR
SELECT SNO, BORC, ALACAK FROM TMPCDETAY INTO :SNO, :B, :A
DO
begin
C = (:B - :A) + C;
if ( C>0 ) then
BEGIN
UPDATE tmpcdetay SET BBK = :C, ABK = 0 WHERE SNO = :SNO;
END
ELSE
BEGIN
UPDATE tmpcdetay SET ABK = -:C, BBK = 0 WHERE SNO = :SNO;
END
END
end
----- Original Message -----
From: "Bill Todd" <XXXX@XXXXX.COM>
Newsgroups: borland.public.interbase.sql
Sent: Thursday, February 09, 2006 2:56 PM
Subject: Re: Credit/Debit tmp table
Quote
Kadir writes:

>if two user wants to use same events some conflictions occurs.

What kind of conflict? What error message do you get. Temporary tables
are private to the connection so if each user has his/her own
connection there should not be any conflict.

An alternative solution is to use a selectable stored procedure to
return the result set and not use a temporary table.

--
Bill Todd (TeamB)
 

Re:Credit/Debit tmp table

InterBase 7.5.1 is a free upgrade for IB 7.1 owners and it supports
true temporary tables that are private to the connection. I would
upgrade to 7.5.1 and change your tmp table to a global temporary table.
That will prevent collisions between users.
--
Bill Todd (TeamB)
 

Re:Credit/Debit tmp table

Kadir,
If you have a need to avoid temporary tables, just add one more column
to your "temp" table: A_GUID VARCHAR(40), make it part of the PK and
then when a particular client loads up the temp data, just pass in the
GUID, and then of course use the GUID to get back only the data passed
in by that client.
I have had a situation where each computer running the program is
assigned a unique integer and so I used an integer. temp tables
eliminate the need for this method, but it still works.
Loren