Kbase 20680: How to Insert a Record into SQL Using ADO Programming
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
17/09/2008 |
|
Status: Verified
GOAL:
How to insert a record into SQL database using ActiveX Data Object (ADO) programming technique.
FACT(s) (Environment):
Progress 9.x
OpenEdge 10.x
Windows
FIX:
The code presented below uses ActiveX programming to open a pre-existing System Data Source Name (DSN), that constructs a connection to an SQL database. Once an SQL connection is established, the code creates a record into the SQL customer table based on information that comes from the Progress customer table.
/************************** DEFINITIONS ****************************/
DEFINE VARIABLE vcDSN AS CHARACTER FORMAT "x(30)":U NO-UNDO.
DEFINE VARIABLE vchDB AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE vchRS AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE vchCMD AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE vcUser LIKE vcDSN NO-UNDO.
DEFINE VARIABLE vcPasswd LIKE vcUser NO-UNDO.
/****************************** MAIN *******************************/
/* Get newly created customer record in buffer */
FIND customer 1 NO-LOCK NO-ERROR.
IF NOT AVAILABLE customer THEN
DO:
MESSAGE "Customer# 1 not found!":U VIEW-AS ALERT-BOX ERROR.
RETURN.
END.
ASSIGN vcDSN = "dsnsql":U
vcUser = "sa":U
vcPasswd = "":U.
/* Create Command objects */
CREATE "adodb.connection":U vchDB.
CREATE "adodb.command":U vchCMD.
/* Open Database - vcDSN must be a pre-existing System DSN */
vchDB:OPEN(vcDSN, vcUser, vcPasswd,) NO-ERROR.
/* Test for errors during open to determine if database is accessible */
IF ERROR-STATUS:NUM-MESSAGES > 0 THEN
DO:
MESSAGE "Unable to open Data Source Nname (DSN) ":U +
STRING(ERROR-STATUS:NUM-MESSAGES)
VIEW-AS ALERT-BOX ERROR.
RETURN.
END.
/* Set connection on the command object */
vchCMD:ActiveConnection = vchDB.
/* Insert new record into the SQL database through opened DSN */
vchCMD:CommandText = "INSERT INTO customer
(Cust_Num,Name,Contact,Phone)
VALUES
(" + STRING(Customer.Cust-Num) + ",
'" + Customer.Name + "',
'" + Customer.Contact + "',
'" + Customer.Phone + "' )":U.
vchCMD:EXECUTE(,,).
/* Clean up com objects when done. */
RELEASE OBJECT vchDB.
RELEASE OBJECT vchCMD.