Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    3

    Unanswered: stored proc through ado(ASP) not working

    I have a problem trying to get a stored procedure to run properly. When I execute the procedure through SQL*PLUS it works perfectly. When I try to execute the same procedure using ADO, I get inconsistent results.

    Basically, the problem when using ADO is that although I get a status message (the error variable) that says everything went ok, the changes the procedure does to the tables don't seem to happen.

    PLEASE help!

    Here is the stored procedure:
    (customer_name in VARCHAR2,customer_cc_number in VARCHAR2,
    flight_number in VARCHAR2, trip_date in VARCHAR2,error out NUMBER)
    as
    begin
    DECLARE
    customer_id NUMBER(6);
    f_id NUMBER(6);
    flight_cnt CHAR(3);
    t_date DATE;
    seats_taken NUMBER(3) DEFAULT 0;
    seats_max NUMBER(3);
    purchase_price NUMBER(6,2);
    BEGIN
    t_date := to_date(trip_date, 'DD-MON-YY');

    SELECT C.id into customer_id FROM anonymous.customers C WHERE C.name = customer_name AND C.cc_number = customer_cc_number;

    IF(customer_id IS NULL) THEN
    INSERT INTO anonymous.Customers VALUES(anonymous.customer_id_seq.NextVal, customer_name, customer_cc_number);

    SELECT C.id into customer_id FROM anonymous.customers C WHERE C.name = customer_name AND C.cc_number = customer_cc_number;
    END IF;

    SELECT count(*) INTO flight_cnt FROM anonymous.Flights F WHERE F.route_number = flight_number AND F.flight_day = to_char(to_date(trip_date, 'DD-MON-YY'), 'DY');

    IF(flight_cnt = 0) then error := 2;
    ELSE
    SELECT F.id into f_id FROM anonymous.Routes R INNER JOIN anonymous.Flights F ON F.route_number = R.route_number WHERE R.route_number = flight_number AND F.flight_day = to_char(to_date(trip_date, 'DD-MON-YY'), 'DY');

    SELECT R.seats INTO seats_max FROM anonymous.Routes R WHERE R.route_number = flight_number;

    SELECT F.price INTO purchase_price FROM anonymous.Flights F WHERE F.id = f_id;

    SELECT count(*) INTO seats_taken FROM anonymous.Trips T WHERE T.flight_id = f_id AND T.trip_date = t_date;

    IF(seats_taken < seats_max)THEN
    INSERT INTO anonymous.Trips VALUES(anonymous.trip_id_seq.NextVal, f_id, customer_id, purchase_price, t_date);
    error := 0;
    ELSE
    error := 1;
    END IF;
    END IF;
    END;
    end;
    And here is the ASP code:
    flightNumber = Request.QueryString("flightNumber")
    customerName = Request.QueryString("customerName")
    customerCCNumber = Request.QueryString("customerCCNumber")
    tripDate = Request.QueryString("day") & "-" & Request.QueryString("month") & "-" & Request.QueryString("year")

    set conn = Server.CreateObject("ADODB.Connection")
    with conn
    .CursorLocation = adUseClient
    .open "Provider=MSDAORA.1; Data Source=firefly; User ID=anonymous; Password=blah;"
    end with

    set cmd = Server.CreateObject("ADODB.Command")
    with cmd
    .ActiveConnection = conn
    .CommandType = adCmdText
    .CommandText = "{call ANONYMOUS.CUSTOMER_PURCHASE(?, ?, ?, ?, ?)}"
    .Parameters.Append = .CreateParameter("@customer_name", adBSTR, adParamInput,, customerName)
    .Parameters.Append = .CreateParameter("@customer_cc_number", adBSTR, adParamInput,, customerCCNumber)
    .Parameters.Append = .CreateParameter("@flight_number", adBSTR, adParamInput,, flightNumber)
    .Parameters.Append = .CreateParameter("@trip_date", adBSTR, adParamInput,, tripDate)
    .Parameters.Append = .CreateParameter("@error", adNumeric, adParamOutput,, perr)
    end with

    conn.close
    set conn = nothing
    set cmd = nothing

    if perr = 0 then
    Response.Write "Flight " & flightNumber & " for " & customerName & " on " & tripDate & " was purchased successfully.<br><br>"
    elseif perr = 1 then
    Response.Write "Flight " & flightNumber & " on " & tripDate & " is full and could not be purchased for " & customerName & ".<br><br>"
    elseif perr = 2 then
    Response.Write "Flight " & flightNumber & " is not available on " & tripDate & ".<br><br>"
    end if
    Thank you so much for your help,
    Matt
    Last edited by mattpiek; 01-19-04 at 17:59.

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    I don't see any COMMIT statements?

    In SqlPlus you're executing the procedure and then querying the results using the same connection.

    With your ADO code, you close the connection which suggests you are then querying for results over a different connection.

    Some progs whether they be ADO, ODBC etc will commit on any active session before quitting, others will do a rollback.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  3. #3
    Join Date
    Jan 2004
    Posts
    3
    OK, I have added a COMMIT statement, so the last part of the PL/SQL code looks like this:
    IF(seats_taken < seats_max)THEN
    INSERT INTO anonymous.Trips
    VALUES(anonymous.trip_id_seq.NextVal, f_id, customer_id, purchase_price, t_date);
    error := 0;
    COMMIT;
    ELSE
    error := 1;
    END IF;

    But this doesn't seem to solve the issue. After executing the procedure with ADO I still don't see any changes to the tables. The way I check this is by starting a new SQL*PLUS session and just selecting the supposedly updated table.

    Am I doing the commit right?

    Thank you for your help.

    -Matt

  4. #4
    Join Date
    Jan 2004
    Posts
    3
    ok solved, sorry for wasting anyones time.

    i simply overlooked an execute statement in ado.

    duh


    thanks, you know who you are.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •