Results 1 to 8 of 8

Thread: lock a table

  1. #1
    Join Date
    Oct 2008
    Posts
    13

    Question Unanswered: lock a table

    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

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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?
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Oct 2008
    Posts
    13
    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

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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?
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  5. #5
    Join Date
    Oct 2008
    Posts
    13
    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

  6. #6
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  7. #7
    Join Date
    Oct 2008
    Posts
    13
    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*/

  8. #8
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    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:
    Code:
    CREATE PROCEDURE TransTest()
    AS
    BEGIN
    START TRANSACTION; 
    UPDATE Billing B 
    
    SET Amount_Owed = Amount_Owed - Amount_Paid 
    WHERE Student_ID IN 
    (SELECT DISTINCT E.Student_ID 
    FROM Enrolls E, Billing B 
    WHERE E.Student_ID = B.Student_ID); 
    COMMIT WORK; 
    end;
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •