could somebody pls help on how to lock a table eg statement.mkd such that other workstations in a multiuser environment would not be able to empty or edit its contents whilst the 1st workstation has not finished processing.
thanks

Need more information.
- What version of PSQL are you using?
- Are you using WGE or Server Engne?
- What interface (Btrieve, ODBC, etc) are you using?
- Do you need to lock the whole table or just certain records?

hi, this are the details as requested;
version - 8.5
engine - server
interface - odbc
i am processing the transaction in a stored procedure

could you give me the advantages and disadvantages of locking the whole table or records within the table, but in this particular case i wish i could lock the whole statement.mkd from other workstation access.

i would be most grateful if you could give a code example or site to check for examples

thanks

With v8.5 and ODBC and Stored Procedures, you are going to be limited to the Start Transaction and Commit statements (http://www.pervasive.com/library/doc...ref-4-13.html).

Why do you want to lock the whole table? What does your Stored Procedure do?

the thing is the sp is used to process customer deposits(cr) and withdrawals(dr) and print a stmt from the file.
the sp will do something like this;

delete from statement;
\\cr
insert into statement (code, dr, cr)
select dp.code, 0, dp.cr
from deposit dp
where dp.code = ........
\\dr
insert into statement (code, dr, cr)
select wt.code, wt.dr, 0
from withdrawal wt
where wt.code = ........
but before the end of processing another workstation wld call same sp and empty what is being processed. so my wish was to lock statement.mkd until one workstation completes its processing.
what am i doing wrong? how do i use the START TRANSACTION, COMMIT commands etc etc
hope my explanations are ok
nb. when i called the site location u gave i had the ff msg
The page you are looking for has moved or is no longer available.
thanks once again

The link should be:
http://www.pervasive.com/library/doc...lref-4-13.html

Post your actual Create Procedure statement. That might let me help a little better.

hi, pls find below the detailed sp as requested;

CREATE PROCEDURE stmtproc(in:customercode decimal(9),
in:startdate date, in:enddate date, in:begindate date);
declare :opbalenddate varchar(10)
set :begindate = convert(:begindate,sql_date)
set :opbalenddate = convert(:opbalenddate,sql_date)
set :opbalenddate = :startdate - 1
declare :opbaldate date
set :opbaldate = :begindate + 1
/*empty report/statement file*/
delete from smultistatement;
/*detailed transactions*/
/*credits*/
/*receipts*/
begin
insert into smultistatement
(code, dated, savingsloancode, debit, credit, description, reportno, thetype)
select rec.code, rec.dated, svg.description, 0,
IF (rec.dated < usr.redenodate, rec.savingsdeposit/usr.baserate,
IF(rec.dated >= usr.redenodate, rec.savingsdeposit/cur.exchangerate,0)),
IF (rec.cheque = '' or rec.cheque is null,
'Receipt no '+convert(rec.receiptno,sql_char),
IF(rec.cheque is not null, 'Receipt/chq no '+convert(rec.receiptno,sql_char)
+' '+convert(rec.cheque,sql_char), 'xxx')), svg.reportno, 'D'
from receipt rec, currencies cur, usersetup usr, savings svg
where (rec.savingsdeposit > 0) and (rec.code = :customercode)
and (rec.dated between :startdate and :enddate)
and (rec.cleared = 'Y') and (rec.currencycode = cur.code)
and (rec.savingscode = svg.code);
end
/*debits*/
/*payment*/
begin
insert into smultistatement
(code, dated, savingsloancode, debit, credit, description, reportno, thetype)
select pay.code, pay.dated, svg.description,
(pay.savingswithdrawal+pay.withinterest), 0,
'Voucher# '+convert(pay.VoucherNo,sql_char), svg.reportno, 'D'
from payment pay, savings svg
where ((pay.savingswithdrawal+pay.withinterest)> 0)
and (pay.code = :customercode)
and (pay.dated between :startdate and :enddate)
and (pay.savingscode = svg.code);
end
/*end of detail transactions*/
/*compute opening balance*/
/*credits*/
delete from sharesdrcr;
delete from savingsdrcr;

insert into sharesdrcr
(code, savingscode, debit, credit)
select :customercode, svg.code, 0, 0
from savings svg;
/*receipts*/
begin
insert into savingsdrcr
(code, savingscode, debit, credit)
select rec.code, rec.savingscode, 0, sum(rec.savingsdeposit/usr.baserate)
from receipt rec, usersetup usr, savings svg
where (rec.savingsdeposit > 0) and (rec.code = :customercode)
and (rec.dated between :opbaldate and :opbalenddate)
and (rec.savingscode = svg.code)
group by rec.code, rec.savingscode;
end
/*end of credits*/
/*debits*/
/*payment*/
begin
insert into savingsdrcr
(code, savingscode, debit, credit)
select pay.code, pay.savingscode, sum(pay.savingswithdrawal), 0
from payment pay, savings svg
where (pay.code = :customercode)
and (pay.dated between :opbaldate and :opbalenddate)
and (pay.savingscode = svg.code)
group by pay.code, pay.savingscode;
end
/*end of debits*/
/*end of opening balance*/
/*update savings debits*/
update sharesdrcr shc set shc.debit = (select sum(sdc.debit)
from savingsdrcr sdc
where (shc.code = sdc.code) and (shc.savingscode = sdc.savingscode));

/*update savings credits*/
update sharesdrcr shc set shc.credit = (select sum(sdc.credit)
from savingsdrcr sdc
where (shc.code = sdc.code) and (shc.savingscode = sdc.savingscode));

/*credit balance*/
begin
insert into smultistatement
(code, dated, debit, credit, description, savingsloancode, reportno, thetype)
select :customercode, :opbalenddate, 0,(shc.credit - shc.debit),
'Bal b/f', svg.description, svg.reportno, 'D'
from sharesdrcr shc, savings svg
where (shc.credit > shc.debit) and shc.savingscode = svg.code;
end
/*debit balance*/
begin
insert into smultistatement
(code, dated, debit, credit, description, savingsloancode, reportno, thetype)
select :customercode, :opbalenddate, (shc.debit - shc.credit), 0,
'Bal b/f', svg.description, svg.reportno, 'D'
from sharesdrcr shc, savings svg
where (shc.credit < shc.debit) and shc.savingscode = svg.code;
end
/*zero balance*/
begin
insert into smultistatement
(code, dated, debit, credit, description, savingsloancode, reportno, thetype)
select :customercode, :opbalenddate, 0, 0, 'Bal b/f', svg.description, svg.reportno,
'D'
from sharesdrcr shc, savings svg
where (shc.credit = shc.debit) and shc.savingscode = svg.code;
end
/*END OF PROCESSING*/

That's a significant stored procedure. Seems like you're doing a lot of processing in there. Is all of it required in one stored procedure?

Here's an example showing how the START TRANSACTION and COMMIT WORK can be used: