Database Hacking

2005-11-30
2013-05-02
  • Chris Farley
    Chris Farley
    2005-11-30

    Newbie question: I imported a bunch of business partners into Compiere. I then wanted to turn them into vendors. I figured it would be okay to do this in sql*plus:

    UPDATE c_bpartner SET isvendor = 'Y' WHERE ad_client_id = '1000001' and c_bp_group = '1000002';

    I thought this would flag all my newly imported records (which belonged to a unique business partner group). However, it didn't, and it caused the Compiere client to crash whenever I accessed one of the business partner records.

    I switched the isvendor flag back to 'N' using sql*plus, and everything worked again. 

    Next, I wanted to delete all the records in the database. So I issued this query:

    DELETE FROM c_bpartner WHERE ad_client_id = '1000001' and c_bp_group_id = '1000002';

    Even after I cleared the client cache, all my business partner records were still showing up when I viewed them with the Compiere client. I checked some other related tables (c_bp_customer_acct, c_bp_vendor_acct, etc), and it seems as if the deletion of the master record cascaded to the related records.

    So, what's going on? I'm guessing business partner records are also stored elsewhere. Or there's some other cacheing mechanism.

    Or it's just too late...

     
    • Q Wang
      Q Wang
      2005-12-02

      Which version are you using?  I also used "update c_bpartner set isVendor = 'y'..." sql statement to update the imported data to vendors, and had no problems at all.  I was using 252d. 

       
    • I also used the sql statement to update the imported vendors, and it's worked fine. My version is 253a.

      My sql was:

      update compiere.c_bpartner set isvendor = 'Y' where ad_client_id = 1000000 and ad_org_id = 1000000 and c_bpartner_id > 1000002;

       
    • Chris Farley
      Chris Farley
      2005-12-03

      I am using 253a. I think I may be experiencing issues with the cache. Even after I update the name of a business partner and reset the client cache, it doesn't reflect the change right away. If I restart the server, then the new value is visible.

      However, updating 'isvendor' still hasn't worked for me, even after a server restart.

      Am I correct that the server caches database records, and that a change to the database might not be picked up right away? Is there a way to cause the server to dump its cache?

       
    • Q Wang
      Q Wang
      2005-12-04

      I have never had this problem before but you could try to use "cache reset" window under "application data -> system admin". Hope this could help.

       
    • Chris Farley
      Chris Farley
      2005-12-10

      The cache reset doesn't do much for me. I've been playing around trying to simply delete a product.

      If I go into sqlplus and enter a query like:

      DELETE FROM m_product WHERE m_product_id = xxxxx;

      Oracle responds that one row has been deleted.

      Compiere still caches this record. I can search for it and view it even after I run a "cache reset". Even after I restart the client. Even after I restart the server. If I try and modify and save the record, Compiere crashes hard.

      The "cache reset" process only clears the local cache. I think there is some kind of server cache, and I have no idea how to clear it. I figured it would be in memory, but it does seem to persist even after server resets.

      Any ideas?

       
    • Chris Farley
      Chris Farley
      2005-12-11

      Never mind. I figured it out. sqlplus by default puts me into a transaction, and doesn't commit what I do until issue a 'commit' apparently.