Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2010
    Posts
    4

    Question Unanswered: ODBC- orcale 11g batch insert issue. Help!

    Hi Guys,

    My enviroment is :
    Orcale server : 11g
    Orcale ODBC : 11g
    Client OS : Widnows server 2003 R2 enterprise

    My design is when I insert 10 records, if one record insert failed, the others records insert to DB successful.

    But actually, if there is one record insert failed, the others records cannot be inserted to DB. Why?

    NOTE: 1. I use odbc driver is Orcale odbc 11g.
    2. config odbc "batch commit mode"= Commit all successful statements.
    3. parmStatusArray variable is always equal "0", it cannot get row's state. parmasProcessed variable is right, equal 10.
    4. Using the same code insert records to MS sql server 2008 successful. I guess when Orcale meet a issue, it will auto rollback to the last right snapshot. how to turn off the rollback?




    My Code like below:

    Code:
    #define DESC_LEN 51
    #define ARRAY_SIZE 10
    
    typedef tagPartStruct {
    SQLREAL Price;
    SQLUINTEGER PartID;
    SQLCHAR Desc[DESC_LEN];
    SQLINTEGER PriceInd;
    SQLINTEGER PartIDInd;
    SQLINTEGER DescLenOrInd;
    } PartStruct;
    
    PartStruct PartArray[ARRAY_SIZE];
    SQLCHAR * Statement = "INSERT INTO Parts (PartID, Description,
    Price) "
    "VALUES (?, ?, ?)";
    SQLUSMALLINT i, ParamStatusArray[ARRAY_SIZE];
    SQLULEN ParamsProcessed;
    
    SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_BIND_TYPE,SQL_PARAM_BIND_BY_COLUMN,0);
    SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMSET_SIZE, ARRAY_SIZE, 0);
    SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_STATUS_PTR, ParamStatusArray, 0);
    SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMS_PROCESSED_PTR, &ParamsProcessed, 0);
    
    // Bind the parameters in row-wise fashion.
    SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_ULONG, SQL_INTEGER, 5, 0,
    &PartArray[0].PartID, 0, &PartArray[0].PartIDInd);
    SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR, DESC_LEN - 1, 0,
    PartArray[0].Desc, DESC_LEN, &PartArray[0].DescLenOrInd);
    SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_FLOAT, SQL_REAL, 7, 0,
    &PartArray[0].Price, 0, &PartArray[0].PriceInd);
    
    // Set part ID, description, and price.
    for (i = 0; i < ARRAY_SIZE; i++) {
    GetNewValues(&PartArray.PartID, PartArray.Desc, &PartArray.Price);
    PartArray[0].PartIDInd = 0;
    PartArray[0].DescLenOrInd = SQL_NTS;
    PartArray[0].PriceInd = 0;
    }
    
    // Execute the statement.
    SQLExecDirect(hstmt, Statement, SQL_NTS);
    
    // Check to see which sets of parameters were processed successfully.
    for (i = 0; i < ParamsProcessed; i++) {
    printf("Parameter Set Status\n");
    printf("------------- -------------\n");
    switch (ParamStatusArray) {
    case SQL_PARAM_SUCCESS:
    case SQL_PARAM_SUCCESS_WITH_INFO:
    printf("%13d Success\n", i);
    break;
    
    case SQL_PARAM_ERROR:
    printf("%13d Error\n", i);
    break;
    
    case SQL_PARAM_UNUSED:
    printf("%13d Not processed\n", i);
    break;
    
    case SQL_PARAM_DIAG_UNAVAILABLE:
    printf("%13d Unknown\n", i);
    break;
    
    }

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by Gl.Nathan View Post
    But actually, if there is one record insert failed, the others records cannot be inserted to DB. Why?
    That's the way batching works. Either all or nothing.

    4. Using the same code insert records to MS sql server 2008 successful. I guess when Orcale meet a issue, it will auto rollback to the last right snapshot. how to turn off the rollback?
    I don't know ODBC nor C, but in JDBC/Java there is the same limitation and as far as I know there is no way around it.

  3. #3
    Join Date
    Nov 2010
    Posts
    4

    Red face

    But my goal is when I insert 10 records, if one/two records failed(such as because of primary keys), the others correct records (9/8 records) should be inserted to DB successfully. is any way to handle this? Or Orcale cann't support this situation? I don't want nothing when errors.


    OCI can do this, but for ODBC, I don't know which attributes I forgot to set, or orcale ODBC doesn't support this actually.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by Gl.Nathan View Post
    But my goal is when I insert 10 records, if one/two records failed(such as because of primary keys), the others correct records (9/8 records) should be inserted to DB successfully. is any way to handle this? Or Orcale cann't support this situation? I don't want nothing when errors.
    You can do this, but not when you use batch inserts.

    If you submit one INSERT statement for each row, then this should work.

  5. #5
    Join Date
    Nov 2010
    Posts
    4
    Thanks shammat for your answers,

    Maybe Batch mode doesn't suit my requirement~~~~~~

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    if you encapsulate your batch into a pl/sql block then you can use an error table and
    LOG ERRORS REJECT LIMIT UNLIMITED at the end of your single insert
    this would insert all records and the rejects goto your error table.

    example (assuming an ERR$ table on "A"):
    PHP Code:
    begin
      insert into A
      select username from b
      LOG ERRORS REJECT LIMIT UNLIMITED
    ;
    end
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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