Write new records to DB via BPM

Yahoo Archive
1
Steven,



Thanks for the code snippet. Inserting the records was a lot simpler than what the OpenEdge documentation made it look like.



I included a release statement (RELEASE ChgLog. In your example) which in effect, does a write (or rewrite) to the DB. I have been told that this statement is optional, but I’ve run into record locking issues which were resolved by releasing the record as soon as I was done with it.



Thanks again,

Kevin Simon



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Steven Gotschall
Sent: Tuesday, November 30, 2010 10:36 AM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Write new records to DB via BPM





This is kind of long, but I use this code in a BPM to update the change log for
UD105A.


DEFINE VARIABLE NextLogNum as Integer Initial 1 NO-UNDO.

Find First UD105A where (UD105A.Company = ttUD105A.Company) AND (UD105A.key1 =
ttUD105A.key1) AND (UD105A.key2 = ttUD105A.key2) AND (UD105A.key3 =
ttUD105A.key3) AND (UD105A.key4 = ttUD105A.key4) AND (UD105A.key5 =
ttUD105A.key5) AND (UD105A.ChildKey1 = ttUD105A.ChildKey1) AND (UD105A.ChildKey2
= ttUD105A.ChildKey2) AND (UD105A.ChildKey3 = ttUD105A.ChildKey3) AND
(UD105A.ChildKey4 = ttUD105A.ChildKey4) AND (UD105A.ChildKey5 =
ttUD105A.ChildKey5) NO-LOCK NO-ERROR.

/* Delete All ChgLog entries for this quote */
/* THIS CODE IS ONLY FOR TESTING */
/*
For Each ChgLog Where (ChgLog.Company = ttUD105A.Company) AND
(ChgLog.Identifier = "UD105") AND (ChgLog.Key1 = ttUD105a.Key1) AND
(ChgLog.TableName = "UD105A"):
Delete ChgLog.
END.
*/

/* Get next Change Log Number */
For Each ChgLog Where (ChgLog.Company = ttUD105A.Company) AND
(ChgLog.Identifier = "UD105") AND (ChgLog.Key1 = ttUD105a.Key1) AND (ChgLog.Key2
= (ttUD105a.Childkey1 + "~~" + ttUD105a.key1 + "~~" + ttUD105a.key2 + "~~" +
ttUD105a.key3)) AND (ChgLog.TableName = "UD105A") AND (ChgLog.DateStamp = TODAY)
NO-LOCK BY LogNum Descending:
NextLogNum = ChgLog.LogNum + 1.
Leave.
END.

/* MORE TEST CODE */
/* Find First ChgLog where (ChgLog.Company = ttUD105A.Company) AND
(ChgLog.DateStamp = TODAY) AND (ChgLog.Identifier = "UD105") AND
(ChgLog.TableName = "UD105A") AND (ChgLog.LogNum = 1) AND (ChgLog.Key2 =
ttUD105a.Childkey1 + "~~" + ttUD105a.key1 + "~~" + ttUD105a.key2 + "~~" +
ttUD105a.key3) AND (ChgLog.Key1 = ttUD105a.Key1) NO-ERROR. */

Create ChgLog NO-ERROR.
ASSIGN ChgLog.Company = ttUD105a.Company
ChgLog.Identifier = "UD105"
ChgLog.Key1 = ttUD105a.Key1
ChgLog.Key2 = ttUD105a.Childkey1 + "~~" + ttUD105a.key1 + "~~" + ttUD105a.key2
+ "~~" + ttUD105a.key3
ChgLog.LogNum = NextLogNum
ChgLog.TableName = "UD105A"
ChgLog.LogText = Substring(DCD-USERID + " ",1,14) + String(NOW,
"99/99/99 hh:mm:ss") + " UPDATE " + ttUD105a.Childkey1 + CHR(10) + CHR(10)
ChgLog.DateStamp = TODAY.

if UD105A.Number01 <> ttUD105A.Number01 Then Do:
ASSIGN ChgLog.LogText = ChgLog.LogText + "MINIMUM ORDER DOLLAR AMOUNT changed
from " + String(UD105A.Number01) + " ==> " + String(ttUD105A.Number01) + Chr(10)
+ Chr(10).
End.

if UD105A.Character01 <> ttUD105A.Character01 Then Do:
ASSIGN ChgLog.LogText = ChgLog.LogText + "QUOTE COMMENT changed from '" +
String(UD105A.Character01) + "' ==> '" + String(ttUD105A.Character01) + "'" +
Chr(10) + Chr(10).
End.

if UD105A.Character02 <> ttUD105A.Character02 Then Do:
ASSIGN ChgLog.LogText = ChgLog.LogText + "FREIGHT COMMENT changed from '" +
String(UD105A.Character02) + "' ==> '" + String(ttUD105A.Character02) + "'" +
Chr(10) + Chr(10).
End.

if UD105A.Character03 <> ttUD105A.Character03 Then Do:
ASSIGN ChgLog.LogText = ChgLog.LogText + "ADDITIONAL INFORMATION changed from
'" + String(UD105A.Character03) + "' ==> '" + String(ttUD105A.Character03) + "'"
+ Chr(10) + Chr(10).
End.

if UD105A.Character04 <> ttUD105A.Character04 Then Do:
ASSIGN ChgLog.LogText = ChgLog.LogText + "POPUP ALERT MESSAGE changed from '"
+ String(UD105A.Character04) + "' ==> '" + String(ttUD105A.Character04) + "'" +
Chr(10) + Chr(10).
End.

if UD105A.CheckBox02 <> ttUD105A.CheckBox02 Then Do:
ASSIGN ChgLog.LogText = ChgLog.LogText + "FREIGHT INCLUSIVE PRICING CheckBox
changed from " + String(UD105A.CheckBox02) + " ==> " +
String(ttUD105A.CheckBox02) + Chr(10) + Chr(10).
End.

if UD105A.CheckBox03 <> ttUD105A.CheckBox03 Then Do:
ASSIGN ChgLog.LogText = ChgLog.LogText + "PARENT/CHILD QUTOE CheckBox changed
from " + String(UD105A.CheckBox03) + " ==> " + String(ttUD105A.CheckBox03) +
Chr(10) + Chr(10).
End.

if UD105A.CheckBox04 <> ttUD105A.CheckBox04 Then Do:
ASSIGN ChgLog.LogText = ChgLog.LogText + "PACKAGE DEAL CheckBox changed from "
+ String(UD105A.CheckBox04) + " ==> " + String(ttUD105A.CheckBox04) + Chr(10) +
Chr(10).
End.

if UD105A.CheckBox05 <> ttUD105A.CheckBox05 Then Do:
ASSIGN ChgLog.LogText = ChgLog.LogText + "POPUP CheckBox changed from " +
String(UD105A.CheckBox05) + " ==> " + String(ttUD105A.CheckBox05) + Chr(10) +
Chr(10).
End.

if UD105A.CheckBox06 <> ttUD105A.CheckBox06 Then Do:
ASSIGN ChgLog.LogText = ChgLog.LogText + "ENABLED CheckBox changed from " +
String(UD105A.CheckBox06) + " ==> " + String(ttUD105A.CheckBox06) + Chr(10) +
Chr(10).
End.

if UD105A.Date01 <> ttUD105A.Date01 Then Do:
ASSIGN ChgLog.LogText = ChgLog.LogText + "CREATION DATE changed from " +
String(UD105A.Date01) + " ==> " + String(ttUD105A.Date01) + Chr(10) + Chr(10).
End.

if UD105A.Date02 <> ttUD105A.Date02 Then Do:
ASSIGN ChgLog.LogText = ChgLog.LogText + "VALID UNTIL DATE changed from " +
String(UD105A.Date02) + " ==> " + String(ttUD105A.Date02) + Chr(10) + Chr(10).
End.

if UD105A.Date03 <> ttUD105A.Date03 Then Do:
ASSIGN ChgLog.LogText = ChgLog.LogText + "MODIFIED DATE changed from " +
String(UD105A.Date03) + " ==> " + String(ttUD105A.Date03) + Chr(10) + Chr(10).
End.

if UD105A.ShortChar01 <> ttUD105A.ShortChar01 Then Do:
ASSIGN ChgLog.LogText = ChgLog.LogText + "ATTENTION changed from '" +
String(UD105A.ShortChar01) + "' ==> '" + String(ttUD105A.ShortChar01) + "'" +
Chr(10) + Chr(10).
End.

if UD105A.ShortChar02 <> ttUD105A.ShortChar02 Then Do:
ASSIGN ChgLog.LogText = ChgLog.LogText + "PREPARED BY changed from '" +
String(UD105A.ShortChar02) + "' ==> '" + String(ttUD105A.ShortChar02) + "'" +
Chr(10) + Chr(10).
End.

if UD105A.Character05 <> ttUD105A.Character05 Then Do:
ASSIGN ChgLog.LogText = ChgLog.LogText + "TERMS changed from '" +
String(UD105A.Character05) + "' ==> '" + String(ttUD105A.Character05) + "'" +
Chr(10) + Chr(10).
End.

if UD105A.Character06 <> ttUD105A.Character06 Then Do:
ASSIGN ChgLog.LogText = ChgLog.LogText + "LEAD TIME changed from '" +
String(UD105A.Character06) + "' ==> '" + String(ttUD105A.Character06) + "'" +
Chr(10) + Chr(10).
End.

if UD105A.ShortChar05 <> ttUD105A.ShortChar05 Then Do:
ASSIGN ChgLog.LogText = ChgLog.LogText + "APPROVED BY changed from '" +
String(UD105A.ShortChar05) + "' ==> '" + String(ttUD105A.ShortChar05) + "'" +
Chr(10) + Chr(10).
End.

if UD105A.ShortChar06 <> ttUD105A.ShortChar06 Then Do:
ASSIGN ChgLog.LogText = ChgLog.LogText + "EMAIL ADDRESS changed from '" +
String(UD105A.ShortChar06) + "' ==> '" + String(ttUD105A.ShortChar06) + "'" +
Chr(10) + Chr(10).
End.

if UD105A.ShortChar07 <> ttUD105A.ShortChar07 Then Do:
ASSIGN ChgLog.LogText = ChgLog.LogText + "FOB changed from '" +
String(UD105A.ShortChar07) + "' ==> '" + String(ttUD105A.ShortChar07) + "'" +
Chr(10) + Chr(10).
End.



________________________________
From: ksimon8fw <ksimon@... <mailto:ksimon%40simstrak.com> >
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Sent: Tue, November 30, 2010 10:20:02 AM
Subject: [Vantage] Write new records to DB via BPM


Has anyone written new records out to the database (OpenEdge) in a BPM? I'm
strugglign with the syntax of doing this. I'm doing some mass generation of
records on the UD tables, so I'm not concerned about violating any type of
business logic.

I'd love sample code if anyone has done this. I'd prefer to stay away from ODBC
at all costs.

Thanks,
Kevin Simon

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
2
Has anyone written new records out to the database (OpenEdge) in a BPM? I'm strugglign with the syntax of doing this. I'm doing some mass generation of records on the UD tables, so I'm not concerned about violating any type of business logic.

I'd love sample code if anyone has done this. I'd prefer to stay away from ODBC at all costs.

Thanks,
Kevin Simon
3
This is kind of long, but I use this code in a BPM to update the change log for
UD105A.
Â
Â
DEFINE VARIABLE NextLogNum as Integer Initial 1 NO-UNDO.
Â
 Find First UD105A where (UD105A.Company = ttUD105A.Company) AND (UD105A.key1 =
ttUD105A.key1) AND (UD105A.key2 = ttUD105A.key2) AND (UD105A.key3 =
ttUD105A.key3) AND (UD105A.key4 = ttUD105A.key4) AND (UD105A.key5 =
ttUD105A.key5) AND (UD105A.ChildKey1 = ttUD105A.ChildKey1) AND (UD105A.ChildKey2
= ttUD105A.ChildKey2) AND (UD105A.ChildKey3 = ttUD105A.ChildKey3) AND
(UD105A.ChildKey4 = ttUD105A.ChildKey4) AND (UD105A.ChildKey5 =
ttUD105A.ChildKey5) NO-LOCK NO-ERROR.
Â
 /* Delete All ChgLog entries for this quote */
 /* THIS CODE IS ONLY FOR TESTING           */
 /*
 For Each ChgLog Where (ChgLog.Company = ttUD105A.Company) AND
(ChgLog.Identifier = "UD105") AND (ChgLog.Key1 = ttUD105a.Key1) AND
(ChgLog.TableName = "UD105A"):
  Delete ChgLog.
 END.
 */
 Â
/* Get next Change Log Number */
 For Each ChgLog Where (ChgLog.Company = ttUD105A.Company) AND
(ChgLog.Identifier = "UD105") AND (ChgLog.Key1 = ttUD105a.Key1) AND (ChgLog.Key2
= (ttUD105a.Childkey1 + "~~" + ttUD105a.key1 + "~~" + ttUD105a.key2 + "~~" +
ttUD105a.key3)) AND (ChgLog.TableName = "UD105A") AND (ChgLog.DateStamp = TODAY)
NO-LOCK BY LogNum Descending:
  NextLogNum = ChgLog.LogNum + 1.
  Leave.
 END.
Â
 /* MORE TEST CODE */
/*Â Find First ChgLog where (ChgLog.Company = ttUD105A.Company) AND
(ChgLog.DateStamp = TODAY) AND (ChgLog.Identifier = "UD105") AND
(ChgLog.TableName = "UD105A") AND (ChgLog.LogNum = 1) AND (ChgLog.Key2 =
ttUD105a.Childkey1 + "~~" + ttUD105a.key1 + "~~" + ttUD105a.key2 + "~~" +
ttUD105a.key3) AND (ChgLog.Key1 = ttUD105a.Key1) NO-ERROR. */
 Â
Create ChgLog NO-ERROR.
 ASSIGN ChgLog.Company = ttUD105a.Company
  ChgLog.Identifier = "UD105"
  ChgLog.Key1 = ttUD105a.Key1
  ChgLog.Key2 = ttUD105a.Childkey1 + "~~" + ttUD105a.key1 + "~~" + ttUD105a.key2
+ "~~" + ttUD105a.key3
  ChgLog.LogNum = NextLogNum
  ChgLog.TableName = "UD105A"
  ChgLog.LogText = Substring(DCD-USERID + "              ",1,14) + String(NOW,
"99/99/99 hh:mm:ss") + "Â Â UPDATEÂ " + ttUD105a.Childkey1 + CHR(10) + CHR(10)
  ChgLog.DateStamp = TODAY.

 if UD105A.Number01 <> ttUD105A.Number01 Then Do:
  ASSIGN ChgLog.LogText = ChgLog.LogText + "MINIMUM ORDER DOLLAR AMOUNT changed
from " + String(UD105A.Number01) + " ==> " + String(ttUD105A.Number01) + Chr(10)
+ Chr(10).
 End.
Â
 if UD105A.Character01 <> ttUD105A.Character01 Then Do:
  ASSIGN ChgLog.LogText = ChgLog.LogText + "QUOTE COMMENT changed from '" +
String(UD105A.Character01) + "' ==> '" + String(ttUD105A.Character01) + "'" +
Chr(10) + Chr(10).
 End.
 Â
if UD105A.Character02 <> ttUD105A.Character02 Then Do:
  ASSIGN ChgLog.LogText = ChgLog.LogText + "FREIGHT COMMENT changed from '" +
String(UD105A.Character02) + "' ==> '" + String(ttUD105A.Character02) + "'" +
Chr(10) + Chr(10).
 End.

 if UD105A.Character03 <> ttUD105A.Character03 Then Do:
  ASSIGN ChgLog.LogText = ChgLog.LogText + "ADDITIONAL INFORMATION changed from
'" + String(UD105A.Character03) + "' ==> '" + String(ttUD105A.Character03) + "'"
+ Chr(10) + Chr(10).
 End.

if UD105A.Character04 <> ttUD105A.Character04 Then Do:
  ASSIGN ChgLog.LogText = ChgLog.LogText + "POPUP ALERT MESSAGE changed from '"
+ String(UD105A.Character04) + "' ==> '" + String(ttUD105A.Character04) + "'" +
Chr(10) + Chr(10).
 End.
 Â
if UD105A.CheckBox02 <> ttUD105A.CheckBox02 Then Do:
  ASSIGN ChgLog.LogText = ChgLog.LogText + "FREIGHT INCLUSIVE PRICING CheckBox
changed from " + String(UD105A.CheckBox02) + " ==> " +
String(ttUD105A.CheckBox02) + Chr(10) + Chr(10).
 End.

 if UD105A.CheckBox03 <> ttUD105A.CheckBox03 Then Do:
  ASSIGN ChgLog.LogText = ChgLog.LogText + "PARENT/CHILD QUTOE CheckBox changed
from " + String(UD105A.CheckBox03) + " ==> " + String(ttUD105A.CheckBox03) +
Chr(10) + Chr(10).
 End.
Â
 if UD105A.CheckBox04 <> ttUD105A.CheckBox04 Then Do:
  ASSIGN ChgLog.LogText = ChgLog.LogText + "PACKAGE DEAL CheckBox changed from "
+ String(UD105A.CheckBox04) + " ==> " + String(ttUD105A.CheckBox04) + Chr(10) +
Chr(10).
 End.
 Â
if UD105A.CheckBox05 <> ttUD105A.CheckBox05 Then Do:
  ASSIGN ChgLog.LogText = ChgLog.LogText + "POPUP CheckBox changed from " +
String(UD105A.CheckBox05) + " ==> " + String(ttUD105A.CheckBox05) + Chr(10) +
Chr(10).
 End.
Â
 if UD105A.CheckBox06 <> ttUD105A.CheckBox06 Then Do:
  ASSIGN ChgLog.LogText = ChgLog.LogText + "ENABLED CheckBox changed from " +
String(UD105A.CheckBox06) + " ==> " + String(ttUD105A.CheckBox06) + Chr(10) +
Chr(10).
 End.
 Â
if UD105A.Date01 <> ttUD105A.Date01 Then Do:
  ASSIGN ChgLog.LogText = ChgLog.LogText + "CREATION DATE changed from " +
String(UD105A.Date01) + " ==> " + String(ttUD105A.Date01) + Chr(10) + Chr(10).
 End.
 Â
if UD105A.Date02 <> ttUD105A.Date02 Then Do:
  ASSIGN ChgLog.LogText = ChgLog.LogText + "VALID UNTIL DATE changed from " +
String(UD105A.Date02) + " ==> " + String(ttUD105A.Date02) + Chr(10) + Chr(10).
 End.
 Â
if UD105A.Date03 <> ttUD105A.Date03 Then Do:
  ASSIGN ChgLog.LogText = ChgLog.LogText + "MODIFIED DATE changed from " +
String(UD105A.Date03) + " ==> " + String(ttUD105A.Date03) + Chr(10) + Chr(10).
 End.
 Â
if UD105A.ShortChar01 <> ttUD105A.ShortChar01 Then Do:
  ASSIGN ChgLog.LogText = ChgLog.LogText + "ATTENTION changed from '" +
String(UD105A.ShortChar01) + "' ==> '" + String(ttUD105A.ShortChar01) + "'" +
Chr(10) + Chr(10).
 End.
 Â
if UD105A.ShortChar02 <> ttUD105A.ShortChar02 Then Do:
  ASSIGN ChgLog.LogText = ChgLog.LogText + "PREPARED BY changed from '" +
String(UD105A.ShortChar02) + "' ==> '" + String(ttUD105A.ShortChar02) + "'" +
Chr(10) + Chr(10).
 End.
 Â
if UD105A.Character05 <> ttUD105A.Character05 Then Do:
  ASSIGN ChgLog.LogText = ChgLog.LogText + "TERMS changed from '" +
String(UD105A.Character05) + "' ==> '" + String(ttUD105A.Character05) + "'" +
Chr(10) + Chr(10).
 End.
 Â
if UD105A.Character06 <> ttUD105A.Character06 Then Do:
  ASSIGN ChgLog.LogText = ChgLog.LogText + "LEAD TIME changed from '" +
String(UD105A.Character06) + "' ==> '" + String(ttUD105A.Character06) + "'" +
Chr(10) + Chr(10).
 End.
 Â
if UD105A.ShortChar05 <> ttUD105A.ShortChar05 Then Do:
  ASSIGN ChgLog.LogText = ChgLog.LogText + "APPROVED BY changed from '" +
String(UD105A.ShortChar05) + "' ==> '" + String(ttUD105A.ShortChar05) + "'" +
Chr(10) + Chr(10).
 End.
 Â
if UD105A.ShortChar06 <> ttUD105A.ShortChar06 Then Do:
  ASSIGN ChgLog.LogText = ChgLog.LogText + "EMAIL ADDRESS changed from '" +
String(UD105A.ShortChar06) + "' ==> '" + String(ttUD105A.ShortChar06) + "'" +
Chr(10) + Chr(10).
 End.
 Â
if UD105A.ShortChar07 <> ttUD105A.ShortChar07 Then Do:
  ASSIGN ChgLog.LogText = ChgLog.LogText + "FOB changed from '" +
String(UD105A.ShortChar07) + "' ==> '" + String(ttUD105A.ShortChar07) + "'" +
Chr(10) + Chr(10).
 End.
Â
Â


________________________________
From: ksimon8fw <ksimon@...>
To: vantage@yahoogroups.com
Sent: Tue, November 30, 2010 10:20:02 AM
Subject: [Vantage] Write new records to DB via BPM

Â
Has anyone written new records out to the database (OpenEdge) in a BPM? I'm
strugglign with the syntax of doing this. I'm doing some mass generation of
records on the UD tables, so I'm not concerned about violating any type of
business logic.


I'd love sample code if anyone has done this. I'd prefer to stay away from ODBC
at all costs.


Thanks,
Kevin Simon







[Non-text portions of this message have been removed]