VB 5 and RDO ODBC connection to Informix Stored Procedures
Hi
I'm trying to execute an Informix stored procedure using RDO and ODBC in
VB 5.
The stored procedure deletes a load of records and returns one value to
denote success or failure. However, I cannot read the returned value. I
can pass input parameters into it ok, but I get NULL as the return value.
My VB code is
Private Sub DeleteRec()
Dim Response As String
On Error GoTo dberr
If AirportNum <> 0 Then
Response = MsgBox("Are you sure you want to delete this Record?", vbYesNo, "Confirm")
If Response = vbYes Then
sSQL = "{ ? = call delairport (?) }"
Set qry = dbconnection.CreateQuery("", sSQL)
qry.rdoParameters(0).Direction = rdParamReturnValue
qry.rdoParameters(1).Direction = rdParamInput
qry.rdoParameters(1) = AirportNum
Set RS = qry.OpenResultset(rdOpenDynamic, rdConcurValues)
If qry.rdoParameters(0) <> 0 Then
MsgBox ("A database error has occured")
Else
MsgBox ("Airport Deleted")
Resetscr
End If
End If
End If
Exit Sub
dberr:
DBerror
End Sub
all objects are RDO type (rdoconnection, rdoquery, rdoresultset). Can
anyone help?
The stored procedure is as follows
DROP PROCEDURE "avail".delairport;
CREATE PROCEDURE "avail".delairport (pairport_no INTEGER)
RETURNING SMALLINT;
DEFINE vsegment_no INTEGER;
DEFINE vroute_no INTEGER;
DEFINE vcompany_code CHAR(3);
DEFINE vflight_no INTEGER;
DEFINE return_code SMALLINT;
ON EXCEPTION
ROLLBACK WORK;
LET return_code = 1;
RETURN return_code;
END EXCEPTION;
SET DEBUG FILE TO 'c:\\temp\\mark.txt';
TRACE ON;
TRACE pairport_no;
BEGIN WORK;
FOREACH SELECT route_no INTO vroute_no
FROM route WHERE
(arr_airport_no = pairport_no OR dep_airport_no = pairport_no)
FOREACH SELECT company_code, flight_no INTO vcompany_code, vflight_no
FROM inventory WHERE
route_no = vroute_no
DELETE FROM inventory WHERE
(company_code = vcompany_code AND flight_no = vflight_no); END FOREACH
DELETE FROM route WHERE route_no = vroute_no;END FOREACH
DELETE FROM terminal WHERE airport_no = pairport_no;
DELETE FROM airport WHERE airport_no = pairport_no;
LET return_code = 0;
COMMIT WORK;
RETURN return_code;
END PROCEDURE;
If anyone has any suggestions, please let me know by mailing me directly
Thanks
Mark
--
Mark Andrews
Finalist Computing
Loughborough University
When I used Stored procedures in vb5 I generated my own sql string
instead of using the parameters. So I would build a string that read
"execute procedure blahblah(parm1, "parm2", ... );"
I don't recall why I did it that way, but I think I had trouble with
the parameter objects. If you are interested let me know and I'll dig
up some sample code.
Regards,
Kelly
On Tue, 16 Feb 1999 14:57:09 +0000, Mark Andrews
<comda@student.lboro.ac.uk> wrote:
>Hi
>
>I'm trying to execute an Informix stored procedure using RDO and ODBC in
>VB 5.
>The stored procedure deletes a load of records and returns one value to
>denote success or failure. However, I cannot read the returned value. I
>can pass input parameters into it ok, but I get NULL as the return value.
>
>My VB code is
>
>Private Sub DeleteRec()
> Dim Response As String
>
> On Error GoTo dberr
> If AirportNum <> 0 Then
> Response = MsgBox("Are you sure you want to delete this Record?", vbYesNo, "Confirm")
> If Response = vbYes Then
> sSQL = "{ ? = call delairport (?) }"
> Set qry = dbconnection.CreateQuery("", sSQL)
> qry.rdoParameters(0).Direction = rdParamReturnValue
> qry.rdoParameters(1).Direction = rdParamInput
> qry.rdoParameters(1) = AirportNum
> Set RS = qry.OpenResultset(rdOpenDynamic, rdConcurValues)
> If qry.rdoParameters(0) <> 0 Then
> MsgBox ("A database error has occured")
> Else
> MsgBox ("Airport Deleted")
> Resetscr
> End If
> End If
> End If
> Exit Sub
>dberr:
> DBerror
>End Sub
>
>all objects are RDO type (rdoconnection, rdoquery, rdoresultset). Can
>anyone help?
>
>The stored procedure is as follows
>
>DROP PROCEDURE "avail".delairport;
>CREATE PROCEDURE "avail".delairport (pairport_no INTEGER)
> RETURNING SMALLINT;
>
>DEFINE vsegment_no INTEGER;
>DEFINE vroute_no INTEGER;
>DEFINE vcompany_code CHAR(3);
>DEFINE vflight_no INTEGER;
>DEFINE return_code SMALLINT;
>
>ON EXCEPTION
> ROLLBACK WORK;
> LET return_code = 1;
> RETURN return_code;
>END EXCEPTION;
>
>SET DEBUG FILE TO 'c:\\temp\\mark.txt';
>TRACE ON;
>
>TRACE pairport_no;
>
>BEGIN WORK;
>
>FOREACH SELECT route_no INTO vroute_no
> FROM route WHERE
> (arr_airport_no = pairport_no OR dep_airport_no = pairport_no)
> FOREACH SELECT company_code, flight_no INTO vcompany_code, vflight_no
> FROM inventory WHERE
> route_no = vroute_no
> DELETE FROM inventory WHERE
> (company_code = vcompany_code AND flight_no = vflight_no);> END FOREACH
> DELETE FROM route WHERE route_no = vroute_no;>END FOREACH
>
>DELETE FROM terminal WHERE airport_no = pairport_no;
>DELETE FROM airport WHERE airport_no = pairport_no;>
>LET return_code = 0;
>
>COMMIT WORK;
>RETURN return_code;
>END PROCEDURE;
>
>If anyone has any suggestions, please let me know by mailing me directly
>
>Thanks
>Mark
>
>--
>Mark Andrews
>Finalist Computing
>Loughborough University
>
>
>
>