Calling SQL stored procedures from 4GL

[markh]

I have a procedure to read from ODBC using ADO and COM-HANDLES.
This works fine for standard SQL Select statements but I cannot get it to work for stored procedures.

Here is the Code from my ReadODBC procedure:-

/*-----------------------------------------------------------------*/

DEFINE INPUT-OUTPUT PARAMETER ObjCommand AS COM-HANDLE NO-UNDO.
DEFINE INPUT-OUTPUT PARAMETER ObjRecordSet AS COM-HANDLE NO-UNDO.
DEFINE INPUT PARAMETER cODBC-QUERY AS CHARACTER NO-UNDO.
DEFINE INPUT PARAMETER iCommandType AS INTEGER NO-UNDO.
DEFINE OUTPUT PARAMETER iODBC-CURSOR AS INTEGER NO-UNDO.
DEFINE OUTPUT PARAMETER iODBC-RECCOUNT AS INTEGER NO-UNDO.
DEFINE OUTPUT PARAMETER cODBC-STATUS AS CHARACTER NO-UNDO.

DEFINE VARIABLE cODBC-NULL AS CHARACTER NO-UNDO.

IF NOT ConnectedODBC() THEN RETURN. /*Function that simply checks connection COM-HANDLE ObjConnection (globally defined) is valid*/

Connections: DO:
CREATE "ADODB.Command" ObjCommand NO-ERROR.
IF NOT VALID-HANDLE(ObjCommand) THEN DO:
cODBC-STATUS = "Error: Could not create command object reference.".
LEAVE Connections.
END.

CREATE "ADODB.RecordSet" ObjRecordSet NO-ERROR.
IF NOT VALID-HANDLE(ObjRecordSet) THEN DO:
cODBC-STATUS = "Error: Could not create recordset object reference.".
LEAVE Connections.
END.

iODBC-CURSOR = 0.
iODBC-RECCOUNT = 0.

ASSIGN ObjCommand:ActiveConnection = ObjConnection
ObjCommand:CommandText = cODBC-QUERY
ObjCommand:CommandType = iCommandType
ObjRecordSet:CursorType = 3 /* 3 = adOpenStatic */.

ASSIGN ObjRecordSet = ObjCommand:EXECUTE(OUTPUT cODBC-NULL,"",32) NO-ERROR.

IF ( ERROR-STATUS:NUM-MESSAGES > 0 ) THEN DO:
cODBC-STATUS = ERROR-STATUS::GET-MESSAGE(1).
RETURN.
END.

ASSIGN iODBC-RECCOUNT = ObjRecordSet:RecordCount.

IF ( iODBC-RECCOUNT = 0 ) OR ( iODBC-RECCOUNT = ? ) THEN RETURN.

ObjRecordSet:MoveFirst NO-ERROR.
iODBC-CURSOR = 1.
RETURN.
END.

/*-----------------------------------------------------------------*/

I am passing iCommandType as 4 which from VB is equivalent to adCmdStoredProc

The procedure errors on the line:-
ASSIGN ObjRecordSet = ObjCommand:EXECUTE(OUTPUT cODBC-NULL,"",32) NO-ERROR.
because "the stored procedure does not require any input parameters."

The Execute method has 3 parameters which are optional and in VB the call "Set objRS = objCmd.Execute" works.

However, if I leave the parameters out I get an error stating I need to use 3 parameters.

Can anyone help me?

Thanks.

 

[joey.jeremiah]

how about 4gl dataservers ? not sure, but i think like many others it also
comes with a development license

then you could write queries using 4gl and run stored proc with run
stored-procedure statements.

it also works on non-windows and with batch and character clients, unlike
com-handles. hth

 

[markh]

Unfortunately don't have time currently to look at dataservers (isn't this always the case) and not sure my customer would like to bear the cost either.

I have a couple of thoughts for mixed VB / progress options but was looking for a single Progress 4GL solution without significant R&D.

Thanks for the suggestion anyway.