Problem repeating a query within an ADO Transaction

Discussion in 'ASP General' started by CJM, Apr 3, 2007.

  1. CJM

    CJM Guest

    I have a fairly simple ASP application talking to an Oracle 10g DB using a
    mixture of ADO (OraOLEDB) and OO4O - the query here concerns some ADO code.

    One part of the application records PartNo/SerialNo combinations; the user
    specifies a PartNo, a SerialNo and the parent PartNo - the parent takes the
    SerialNo from the child object - and the application INSERTs these as
    records into the DB.

    Amongst the validation, is a check to confirm that each PartNo/SerialNo
    combination has not be used before. This code seems to work fine.
    Clearly, if the user enters the same PartNo for both the child and parent
    items, the DB would ordinarily complain:

    "ORA-00001: unique constraint (IFSAPP.PART_SERIAL_CATALOG_PK) violated
    ORA-06512"

    However, I'm expecting the validation routines to handle this clash before
    the INSERT statement is attempted. Unfortunately, in this type of scenario
    this is not the case - the validation routine responds that the parent
    PartNo/SerialNo has not been used before, and thus the INSERT is attempted
    and the unique constraint error occurs.

    I assume that this is because previous INSERTs were not committed since the
    whole operation is wrapped up within an ADO transaction. However, I would
    have expected that the validation code would have taken an changes made
    within the transaction into account, but it doesn't appear to be the case.

    Is my logic wrong, or should I be expecting the validation to consider
    previous statements within the same transaction? If my expectations are
    correct, where might I be going wrong?

    Thanks in advance

    Chris


    Selected code snippets:
    - ASP:

    Function SerialExists (sSerialNo, sPartNo)
    Dim iResult, bResult

    With oDB
    .Parameters.Add "sPartNo", sPartNo, ORAPARM_INPUT, 1
    .Parameters.Add "sSerialNo", sSerialNo, ORAPARM_INPUT, 1
    .Parameters.Add "iResult", 0, ORAPARM_OUTPUT
    .Parameters("iResult").serverType = 1

    .ExecuteSQL ("declare iResult VARCHAR2(100); Begin :iResult :=
    IFSAPP.PART_SERIAL_CATALOG_API.CHECK_EXIST:)sPartNo, :sSerialNo); end;")

    SerialExists = .Parameters("iResult").Value

    .Parameters.Remove "sPartNo"
    .Parameters.Remove "sSerialNo"
    .Parameters.Remove "iResult"

    End With
    End Function


    - Oracle:

    FUNCTION Check_Exist___ (
    part_no_ IN VARCHAR2,
    serial_no_ IN VARCHAR2 ) RETURN BOOLEAN
    IS
    dummy_ NUMBER;
    CURSOR exist_control IS
    SELECT 1
    FROM PART_SERIAL_CATALOG_TAB
    WHERE part_no = part_no_
    AND serial_no = serial_no_;
    BEGIN
    OPEN exist_control;
    FETCH exist_control INTO dummy_;
    IF (exist_control%FOUND) THEN
    CLOSE exist_control;
    RETURN(TRUE);
    END IF;
    CLOSE exist_control;
    RETURN(FALSE);
    END Check_Exist___;
    CJM, Apr 3, 2007
    #1
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. nita
    Replies:
    1
    Views:
    864
    Saravana
    Nov 20, 2004
  2. ronaldlee

    Transfer ADO Code to ADO.NET

    ronaldlee, Dec 17, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    452
    Kevin Spencer
    Dec 17, 2004
  3. Replies:
    0
    Views:
    1,308
  4. Lit
    Replies:
    6
    Views:
    547
  5. Vencz Istv?n
    Replies:
    2
    Views:
    282
Loading...

Share This Page