0 Replies Latest reply: Aug 1, 2012 8:46 AM by 953266 RSS

    Getting confused with error handling when using setMaxIterations

    953266
      When I insert a too long string I expect to receive ORA-12899, which clearly indicates the error. However when I set setMaxIterations, I get "ORA-01461: can bind a LONG value only for insert into a LONG column"

      Example code:
      #include <string>
      #include <occi.h>
      #include <iostream>
      
      using namespace std;
      using namespace oracle::occi;
      
      const string table_name("TEST_ORA_1461_12899");
      
      void createTestTable(Connection* conn)
      {
         try {
            string sql("CREATE TABLE ");
            sql += table_name;
            sql += " (name VARCHAR2(10))";
            Statement* stmt = conn->createStatement(sql);
            try {
            stmt->executeUpdate();
            }
            catch (SQLException & ex)
            {
            cerr << "EXCEPTION " << ex.what() << endl;;
            }
            conn->terminateStatement(stmt);
         }
         catch (SQLException & ex)
         {
            cerr << "EXCEPTION " << ex.what() << endl;;
         }
      }
      
      
      void insert(Connection* conn, const string& value, bool useiteration)
      {
         cout << "Try to insert string '" << value << "' with length " << value.length() << " useiteration=" << boolalpha << useiteration << endl;
         try {
            string sql("INSERT INTO ");
            sql += table_name;
            sql += "(name) VALUES (:1)";
            Statement * stmt = conn->createStatement(sql);
            try {
            stmt->setMaxParamSize(1, 10);
            if (useiteration)
            {
               stmt->setMaxIterations(10);
            }
            cout << "Statment max number of iterations set to " << stmt->getMaxIterations() << endl;
            stmt->setString(1, value);
            if (useiteration)
            {
               stmt->addIteration();
               stmt->setString(1, value);
            } else {
               stmt->execute();
               stmt->setString(1, value);
            }
            stmt->execute();
            }
            catch (SQLException & ex)
            {
            cerr << "EXCEPTION " << ex.what() << endl;
            conn->rollback();
            }
            conn->terminateStatement(stmt);
            conn->commit();
         }
         catch (SQLException & ex)
         {
            cerr << "EXCEPTION " << ex.what() << endl;;
         }
      }
      
      void select(Connection* conn)
      {
         try {
            string sql("SELECT name FROM ");
            sql += table_name;
            Statement* stmt = conn->createStatement(sql);
            try {
            ResultSet* rs = stmt->executeQuery();
            cout << table_name << endl;
            cout << "=============================" << endl;
            int pos=0;
            while (rs->next())
            {
               ++pos;
               cout.width(3);
               cout << pos << ": ";
               cout.width(10);
               string name(rs->getString(1));
               cout << left << name << " length=" << name.length() << " is null=" << boolalpha << rs->isNull(1) << endl;
            }
            }
            catch (SQLException & ex)
            {
            cerr << "EXCEPTION select" << ex.what() << endl;;
            }
            conn->terminateStatement(stmt);
         }
         catch (SQLException & ex)
         {
            cerr << "EXCEPTION select 1 " << ex.what() << endl;;
         }
      }
      
      int main(int argc, char** argv)
      {
         if (argc < 5)
         {
            cerr << argv[0] << " user password connectionString [create | select | insert]" << endl;
            return 1;
         }
      
         Environment* env = Environment::createEnvironment(Environment::THREADED_MUTEXED);
      
         try {
            Connection* conn = env->createConnection(argv[1], argv[2], argv[3]);
            int major, minor, update, patch, patchsub;
            Environment::getClientVersion(major, minor, update, patch, patchsub);
            cout << "Server version: " << conn->getServerVersion().c_str() << endl;
            cout << "Client version: " << major << "." << minor << "." << update << "." << patch << "." << patchsub << endl;
      
            string command(argv[4]);
            if (command == "create")
            {
            createTestTable(conn);
            }
            if (command == "insert")
            {
            insert(conn, "ok1", false);
            insert(conn, "ok2 ok2 ok", true);
            insert(conn, "Not ok, too long string 1", false);
            insert(conn, "Not ok, too long string 1", true);
            }
            if (command == "select")
            {
            select(conn);
            }
            env->terminateConnection(conn);
         }
         catch (SQLException & ex)
         {
            cerr << "EXCEPTION " << ex.what() << endl;;
         }
         Environment::terminateEnvironment(env);
       
         return 0;
      }
      When I run insert I get the following

      Server version: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
      With the Partitioning, Automatic Storage Management, OLAP, Data Mining
      and Real Application Testing options
      Client version: 11.2.0.1.0
      Try to insert string 'ok1' with length 3 useiteration=false
      Statment max number of iterations set to 1
      Try to insert string 'ok2 ok2 ok' with length 10 useiteration=true
      Statment max number of iterations set to 10
      Try to insert string 'Not ok, too long string 1' with length 25 useiteration=false
      Statment max number of iterations set to 1
      EXCEPTION ORA-12899: value too large for column "DPOWNERA"."TEST_ORA_1461_12899"."NAME" (actual: 25, maximum: 10)

      Try to insert string 'Not ok, too long string 1' with length 25 useiteration=true
      Statment max number of iterations set to 10
      EXCEPTION ORA-01461: can bind a LONG value only for insert into a LONG column


      Is there any way to get the same Oracle error back when using iterations?

      /Bjorn