SQL Select and then Update from Progress

[Earlt]

I am trying to read a record from a SQL database during a Progress program, then update that record depending on what is in a certain field. I am able to INSERT, UPDATE, and DELETE record from the SQL database from my program, but I have not been able to SLECT a record and use that data within my program. Can anyone give me some ideas?

I have been writing inProgress for years, but having only been trying to use SQL for a couple of months.

Thanks

 

[cecsno]

I am trying to read a record from a SQL database during a Progress program, then update that record depending on what is in a certain field. I am able to INSERT, UPDATE, and DELETE record from the SQL database from my program, but I have not been able to SLECT a record and use that data within my program. Can anyone give me some ideas?

I have been writing inProgress for years, but having only been trying to use SQL for a couple of months.

Thanks

DEFINE VARIABLE v-txt AS CHARACTER NO-UNDO.
SELECT min(pt_part) INTO v-txt FROM pt_mstr .
MESSAGE v-txt VIEW-AS ALERT-BOX.

 

[Earlt]

I tried what you suggested but I still can't get it to work. The following is my code. The include file supples the Dsn, Server, Userid, and Password to the database. I get an error message telling me the syntax in wrong in the Select Statement, but I can't find anything wrong with it.


DEFINE VARIABLE ObjRecordset AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE ObjConnection AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE ObjCommand AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE ODBC-Dsn AS CHARACTER NO-UNDO.
DEFINE VARIABLE ODBC-Server AS CHARACTER NO-UNDO.
DEFINE VARIABLE ODBC-Userid AS CHARACTER NO-UNDO.
DEFINE VARIABLE ODBC-Passwd AS CHARACTER NO-UNDO.
DEFINE VARIABLE ODBC-Query AS CHARACTER NO-UNDO.
DEFINE VARIABLE ODBC-Status AS CHARACTER NO-UNDO.
DEFINE VARIABLE ODBC-Reccount AS INTEGER NO-UNDO.
DEFINE VARIABLE ODBC-Null AS CHARACTER NO-UNDO.
DEFINE VARIABLE ODBC-Cursor AS INTEGER NO-UNDO.
DEFINE VARIABLE v-txt AS CHARACTER NO-UNDO.
Create "ADODB.Connection" ObjConnection.
Create "ADODB.RecordSet" ObjRecordSet.
Create "ADODB.Command" ObjCommand.
{R:\WEB\WARE.I}
ObjConnection:Open ( "data source=" + ODBC-DSN + ";server=" + ODBC-SERVER, ODBC-USERID, ODBC-PASSWD, 0 ) NO-ERROR.
If ( error-status:num-messages > 0 ) THEN
DO:
ODBC-STATUS = "Error: Could not establish connection.".
MESSAGE "Error: Could not establish connection.".
PAUSE.
END.
Else
DO:
ODBC-QUERY = "SELECT nextord INTO v-txt FROM sysmstr".
Assign ObjCommand:ActiveConnection = ObjConnection
ObjCommand:CommandText = ODBC-QUERY
ObjCommand:CommandType = 1 /* adCmdText */
ObjConnection:CursorLocation = 3 /* adUseClient */
ObjRecordSet:CursorType = 3 /* adOpenStatic */
ObjRecordSet = ObjCommand:Execute ( output ODBC-NULL, "", 32 ).
MESSAGE v-txt VIEW-AS ALERT-BOX.
END.

 

[cecsno]

Your using an ADO connection, the following is proably closer, but I didn't test it.


DEFINE VARIABLE ObjRecordset AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE ObjConnection AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE ObjCommand AS COM-HANDLE NO-UNDO.
DEFINE VARIABLE ODBC-Dsn AS CHARACTER NO-UNDO.
DEFINE VARIABLE ODBC-Server AS CHARACTER NO-UNDO.
DEFINE VARIABLE ODBC-Userid AS CHARACTER NO-UNDO.
DEFINE VARIABLE ODBC-Passwd AS CHARACTER NO-UNDO.
DEFINE VARIABLE ODBC-Query AS CHARACTER NO-UNDO.
DEFINE VARIABLE ODBC-Status AS CHARACTER NO-UNDO.
DEFINE VARIABLE ODBC-Reccount AS INTEGER NO-UNDO.
DEFINE VARIABLE ODBC-Null AS CHARACTER NO-UNDO.
DEFINE VARIABLE ODBC-Cursor AS INTEGER NO-UNDO.
DEFINE VARIABLE v-txt AS CHARACTER NO-UNDO.
Create "ADODB.Connection" ObjConnection.
Create "ADODB.RecordSet" ObjRecordSet.
Create "ADODB.Command" ObjCommand.
{R:\WEB\WARE.I}
ObjConnection:Open ( "data source=" + ODBC-DSN + ";server=" + ODBC-SERVER, ODBC-USERID, ODBC-PASSWD, 0 ) NO-ERROR.
If ( error-status:num-messages > 0 ) THEN
DO:
ODBC-STATUS = "Error: Could not establish connection.".
MESSAGE "Error: Could not establish connection.".
PAUSE.
END.
Else
DO:
ODBC-QUERY = "SELECT nextord FROM sysmstr".
Assign ObjCommand:ActiveConnection = ObjConnection
ObjCommand:CommandText = ODBC-QUERY
ObjCommand:CommandType = 1 /* adCmdText */
ObjConnection:CursorLocation = 3 /* adUseClient */
ObjRecordSet:CursorType = 3 /* adOpenStatic */
ObjRecordSet = ObjCommand:Execute ( output ODBC-NULL, "", 32 )
ODBC-RECCOUNT = ObjRecordSet:RecordCount.

If ( ODBC-RECCOUNT > 0 ) and not ( ODBC-RECCOUNT = ? ) then
Do:
ObjRecordSet:MoveFirst no-error.
Do while ODBC-CURSOR < ODBC-RECCOUNT:
v-txt = ObjRecordSet:FIELDS.
display v-txt.
END.
END.
END.
Release object ObjConnection no-error.
Release object ObjCommand no-error.
Release object ObjRecordSet no-error.

 

[heber_fomin]

Is there any Progress documentation about the use of COM Objects ADO, also, does anybody had use ADO to run database stored procedures from the PROGRESS 4gl?