Can't Update or Modify Data in an External Table (v7.6)

[RKushner]

The only way I can find to get read/write access to an external table is using a grid control, but I cannot do that this time. I have been struggling with Datasets, but it looks like all they do is read only access. It looks like it doesn't matter if the external table is defined thru Metastorm or created directly in the database server. The table that I need to work with is in the same database with the Metastorm "e" tables and the "folder" table (with the custom variables).

I really appreciate any help, but I am sceptical. I don't think it can be done without going to 9.0 (which we are doing soon, but not for this project) and using Custom Business Objects.

Thanx!!!

[Ari Chopra]

Can you please elaborate on what exactly you are trying to accomplish?  You are trying to write to a database table from what I gather.

You have a few options-

1) Use an editable grid form field control (you mentioned that you can't use grids- can you explain why?

2) Use the Integration Wizard to INSERT, UPDATE, DELETE, using the %ExecSQL command.  You could do this on the "Do This" of a command button click, or some other field event.  You may then have another field on the form (could be a grid, dropdown box, etc) that is set to refresh itself, which queries the data from the database table in order to show the updated value(s).

3) You could write Server Script code (VBScript, JScript.NET) to perform the database update, rather than using the Integration Wizard.

hope that helps.

[Jerome҉]

You are right (as is Ari), you cannot get access to the data in the same way you can in v9 - this is one of the main advantages of v9 IMO. The dataset control only allows read-only access.

We did try once to create something like you have in v9 in v7, but it is in fact too awkward for a number of complex reasons.

Best bet is reading data, assigning to temporary variables and writing after editing. Or do it in v9, of course!

[Randall Brodka]

Here is some sample JScript code to perform inserts and updates on an external table (within the Metastorm Database):

//___ Create New Reference ________________________________________
function Create_New_Reference() {
    var SQL = '';

    SQL = SQL.concat("INSERT INTO DPB_Reference (");
    SQL = SQL.concat("DPB,Name,Address1,Address2,City,State,Zip,");
    SQL = SQL.concat("Contact,Phone,Fax,Email,Account,Assessment,");
    SQL = SQL.concat("FileName,Method,Response,Status,Verified");
    SQL = SQL.concat(") VALUES (");
    SQL = SQL.concat("'",ework.Parent_ID,          "',");
    SQL = SQL.concat("'",escape(ework.Name),       "',");
    SQL = SQL.concat("'",escape(ework.Address1),   "',");
    SQL = SQL.concat("'",escape(ework.Address2),   "',");
    SQL = SQL.concat("'",escape(ework.City),       "',");
    SQL = SQL.concat("'",ework.State,              "',");
    SQL = SQL.concat("'",escape(ework.Zip),        "',");
    SQL = SQL.concat("'",escape(ework.Contact),    "',");
    SQL = SQL.concat("'",escape(ework.Phone),      "',");
    SQL = SQL.concat("'",escape(ework.Fax),        "',");
    SQL = SQL.concat("'",escape(ework.Email),      "',");
    SQL = SQL.concat("'",escape(ework.Account),    "',");
    SQL = SQL.concat("'",escape(ework.Assessment), "',");
    SQL = SQL.concat("'",ework.FileName,           "',");
    SQL = SQL.concat("'",ework.Method,             "',");
    SQL = SQL.concat("'",escape(ework.Response),   "',");
    SQL = SQL.concat("'","Created",                "',");
    SQL = SQL.concat("'",ework.Verified,           "')");
    ework.ExecSQL(SQL);

    SQL = '';
    SQL = SQL.concat("SELECT SCOPE_IDENTITY()");
    var DPB_Reference = ework.SelectSQL(SQL);
    ework.DOB_Reference = DPB_Reference;

    var DateTm  = ework.FormatTime(ework.System.Time,'yyyy/mm/dd hh:nn:ss');

    SQL = '';
    SQL = SQL.concat("UPDATE DPB_Reference SET ");
    SQL = SQL.concat("Created = '",DateTm,"' ");
    SQL = SQL.concat("WHERE RecordNumber = '",DPB_Reference,"' ");
    ework.ExecSQL(SQL);

    Create_History(DPB_Reference,'Created');

    if (ework.Verified == true) {
       SQL = '';
       SQL = SQL.concat("UPDATE DPB_Reference SET ");
       SQL = SQL.concat("Confirmed = '",DateTm,"' ");
       SQL = SQL.concat("WHERE RecordNumber = '",DPB_Reference,"' ");
       ework.ExecSQL(SQL);
       Create_History(DPB_Reference,'Verified');
    }
    ework.Check_Complete = true;
}

//___ Escape _________________________________________
function escape(text) {
    text = text.replace(/\'/g,"''");
    return text;
}


//___ Create History ___________________________________________________
function Create_History(DPB_Reference,Action) {
    var DateTm  = ework.FormatTime(ework.System.Time,'yyyy/mm/dd hh:nn:ss');
    var Person  = getDistinguishedName();
    var Result  = ework.Comment;

    SQL = '';
    SQL = SQL.concat("INSERT INTO DPB_Reference_History (");
    SQL = SQL.concat("DPB_Reference,DPB,Date,Person,Action,Result");
    SQL = SQL.concat(") VALUES (");
    SQL = SQL.concat("'",DPB_Reference,  "',");
    SQL = SQL.concat("'",ework.FolderID, "',");
    SQL = SQL.concat("'",DateTm,         "',");
    SQL = SQL.concat("'",escape(Person), "',");
    SQL = SQL.concat("'",escape(Action), "',");
    SQL = SQL.concat("'",escape(Result), "')");

    ework.ExecSQL(SQL);
}