Uploaded image for project: 'CUBRID APIs'
  1. CUBRID APIs
  2. APIS-370

CUBRID python driver should support SET types (LIST/SET/MULTISET) in prepared statements

    Details

      Description

      If you want to execute a query in a table containing a SET, LIST or MULTISET data type, then the prepared query in python does not work.

      Example:

      {code}
      CREATE TABLE set_tbl ( col_1 set(int, CHAR(1)));{code}

      More information is here:
      http://www.cubrid.org/manual/841/en/Collection%20Types-SET

      For example if you want to execute this query:

      {code}
      INSERT INTO set_tbl VALUES ({3,3,3,2,2,1,0,'c','c','c','b','b','a'});{code}

      you cannot do it using prepared statements.

      {code}
      insert into set_tbl values ;{code}

      with parameters given to execute command: (

      {3,3,3,2,2,1,0,'c','c','c','b','b','a'}

      ,) give out an error that cannot convert the set data type. Same problem occurs for list data type or if you use set instead of brackets in the python code when giving the parameters.

      1. 370.txt
        2 kB
        谢韦华[Bert]
      2. apis_370.py
        5 kB
        Ray Yin

        Issue Links

          Activity

          Hide
          ryin005 Ray Yin added a comment - - edited

          The current execute function doesn't support this feature:
          Test Case:

              def test_set_prepare(self): 
                      conStr="CUBRID:localhost:33188:test_python:::"
                      self.con = CUBRIDdb.connect(conStr, "dba","")        
                      self.cur = self.con.cursor()
                      self.con.set_autocommit(True)
                      
                      value = [('1','2','3','4')] 
                      self.cur.execute("DROP TABLE IF EXISTS set_tbl")
                      self.cur.execute("CREATE TABLE set_tbl ( col_1 set(int))")
                      self.cur.execute("insert into set_tbl values (?)", value)
          

          Results: Failed, DatabaseError: (-494, 'ERROR: DBMS, -494, Semantic: Cannot coerce host var to type set. ')

          Summary: the 'execute' function should support the set data type since it is the common function to implement prepared query in python

          Reopen the issue

          Show
          ryin005 Ray Yin added a comment - - edited The current execute function doesn't support this feature: Test Case: def test_set_prepare(self): conStr="CUBRID:localhost:33188:test_python:::" self.con = CUBRIDdb.connect(conStr, "dba","") self.cur = self.con.cursor() self.con.set_autocommit(True) value = [('1','2','3','4')] self.cur.execute("DROP TABLE IF EXISTS set_tbl") self.cur.execute("CREATE TABLE set_tbl ( col_1 set(int))") self.cur.execute("insert into set_tbl values (?)", value) Results: Failed, DatabaseError: (-494, 'ERROR: DBMS, -494, Semantic: Cannot coerce host var to type set. ') Summary: the 'execute' function should support the set data type since it is the common function to implement prepared query in python Reopen the issue
          Hide
          cn15800 谢韦华[Bert] added a comment - - edited

          one parameter

          {code:title=python|borderStyle=solid}
          from CUBRIDdb import FIELD_TYPE
          def issue_370():

          #CUBRIDdb _cubrid
          con = CUBRIDdb.connect('CUBRID:10.34.64.218:30000:demodb:dba::')
          c = con.cursor()

          set_val=(('1','23','48'),)
          etype = FIELD_TYPE.INT

          c.execute("insert into set_tbl_int VALUES", set_val,etype)
          con.commit();

          c.close()
          con.close() {code}
          Show
          cn15800 谢韦华[Bert] added a comment - - edited one parameter {code:title=python|borderStyle=solid} from CUBRIDdb import FIELD_TYPE def issue_370(): #CUBRIDdb _cubrid con = CUBRIDdb.connect('CUBRID:10.34.64.218:30000:demodb:dba::') c = con.cursor() set_val=(('1','23','48'),) etype = FIELD_TYPE.INT c.execute("insert into set_tbl_int VALUES ", set_val,etype) con.commit(); c.close() con.close() {code}
          Hide
          cn15800 谢韦华[Bert] added a comment - - edited

          two parameter

          {code}
          def issue_370():

          #CUBRIDdb _cubrid
          con = CUBRIDdb.connect('CUBRID:10.34.64.218:30000:demodb:dba::')
          c = con.cursor()

          set_val=(('abc','def'),('1','23','48'))
          etype = (FIELD_TYPE.CHAR,FIELD_TYPE.INT)

          c.execute("CREATE TABLE set_tbl_char_int ( col_1 set(CHAR(5)),col_2 set(int));")
          c.execute("insert into set_tbl_int VALUES(?,?)", set_val,etype)
          con.commit();

          c.close()
          con.close() {code}
          Show
          cn15800 谢韦华[Bert] added a comment - - edited two parameter {code} def issue_370(): #CUBRIDdb _cubrid con = CUBRIDdb.connect('CUBRID:10.34.64.218:30000:demodb:dba::') c = con.cursor() set_val=(('abc','def'),('1','23','48')) etype = (FIELD_TYPE.CHAR,FIELD_TYPE.INT) c.execute("CREATE TABLE set_tbl_char_int ( col_1 set(CHAR(5)),col_2 set(int));") c.execute("insert into set_tbl_int VALUES(?,?)", set_val,etype) con.commit(); c.close() con.close() {code}
          Hide
          ryin005 Ray Yin added a comment -

          The following case has been failed :

          • Environment:
            • OS : Linux 64
            • CUBRID : CUBRID 9.1 (9.1.0.0212) (64bit release build for linux_gnu) (Mar 14 2013 00:16:33)
            • Python Driver: 9.1.0.0001 (20130529)
          • Testcode {code}
            def test_set_prepare(self):
            conStr="CUBRID:localhost:33188:test_python:::"
            self.con = CUBRIDdb.connect(conStr, "dba","")
            self.cur = self.con.cursor()
            self.con.set_autocommit(True)

            value = [('1','2','3','4')]
            self.cur.execute("DROP TABLE IF EXISTS set_tbl")
            self.cur.execute("CREATE TABLE set_tbl ( col_1 set(int))")
            self.cur.execute("insert into set_tbl values ", value){code}

          *Reuslt: InterfaceError: (-30006, 'ERROR: CLIENT, -30006, Invalid parameter')

          • Summary : the case is failed becuase the there is no etype parameter in the execute function, however the etype should be set as the default value when the parameter is not avaliable
          Show
          ryin005 Ray Yin added a comment - The following case has been failed : Environment: OS : Linux 64 CUBRID : CUBRID 9.1 (9.1.0.0212) (64bit release build for linux_gnu) (Mar 14 2013 00:16:33) Python Driver: 9.1.0.0001 (20130529) Testcode {code} def test_set_prepare(self): conStr="CUBRID:localhost:33188:test_python:::" self.con = CUBRIDdb.connect(conStr, "dba","") self.cur = self.con.cursor() self.con.set_autocommit(True) value = [('1','2','3','4')] self.cur.execute("DROP TABLE IF EXISTS set_tbl") self.cur.execute("CREATE TABLE set_tbl ( col_1 set(int))") self.cur.execute("insert into set_tbl values ", value){code} *Reuslt: InterfaceError: (-30006, 'ERROR: CLIENT, -30006, Invalid parameter') Summary : the case is failed becuase the there is no etype parameter in the execute function, however the etype should be set as the default value when the parameter is not avaliable
          Hide
          ryin005 Ray Yin added a comment - - edited
          • Testcode (apis370.py)
            def test_set_prepare_int(self): 
                conStr="CUBRID:localhost:33188:test_python:::"
                self.con = CUBRIDdb.connect(conStr, "dba","")        
                self.cur = self.con.cursor()
                self.con.set_autocommit(True)
                
                value =  [('1','2','3','4'),('5', '6', '7')] 
                self.cur.execute("DROP TABLE IF EXISTS set_tbl")
                self.cur.execute("CREATE TABLE set_tbl ( col_1 set(int), col_2 set(int))")
                self.cur.execute("insert into set_tbl values (?, ?)", value)
                
                self.cur.execute("SELECT * FROM set_tbl")
                data = self.cur.fetchone()
                self.assertEquals(data[0],['1', '2', '3', '4'])
            
                value =  [('a','b','c','d'),('5', '6', '7')]
                etype = (1,8) 
                self.cur.execute("DROP TABLE IF EXISTS set_tbl")
                self.cur.execute("CREATE TABLE set_tbl ( col_1 set(char), col_2 set(int))")
                self.cur.execute("insert into set_tbl values (?, ?)", value, etype)
                
                self.cur.execute("SELECT * FROM set_tbl")
                data = self.cur.fetchone()
                self.assertEquals(data[0],['a', 'b', 'c', 'd'])
                self.assertEquals(data[1],['5', '6', '7'])
            
            def test_set_prepare_char(self): 
                conStr="CUBRID:localhost:33188:test_python:::"
                self.con = CUBRIDdb.connect(conStr, "dba","")        
                self.cur = self.con.cursor()
                self.con.set_autocommit(True)
                
                value =  [('a','b','c','d')] 
                self.cur.execute("DROP TABLE IF EXISTS set_tbl")
                self.cur.execute("CREATE TABLE set_tbl ( col_1 set(CHAR) )")
                self.cur.execute("insert into set_tbl values (?)", value)
                
                self.cur.execute("SELECT * FROM set_tbl")
                data = self.cur.fetchone()
                self.assertEquals(data[0],['a', 'b', 'c', 'd'])
            
                value =  [('h','j')] 
                self.cur.execute("DROP TABLE IF EXISTS set_tbl")
                self.cur.execute("CREATE TABLE set_tbl ( col_1 set(CHAR) )")
                self.cur.execute("insert into set_tbl values (?)", value, 1)
                
                self.cur.execute("SELECT * FROM set_tbl")
                data = self.cur.fetchone()
                self.assertEquals(data[0],['h', 'j'])
            
            def test_set_prepare_string(self): 
                conStr="CUBRID:localhost:33188:test_python:::"
                self.con = CUBRIDdb.connect(conStr, "dba","")        
                self.cur = self.con.cursor()
                self.con.set_autocommit(True)
                
                value =  [('abc','bcd','ceee','dddddd')] 
                self.cur.execute("DROP TABLE IF EXISTS set_tbl")
                self.cur.execute("CREATE TABLE set_tbl ( col_1 set(varchar) )")
                self.cur.execute("insert into set_tbl values (?)", value)
                
                self.cur.execute("SELECT * FROM set_tbl")
                data = self.cur.fetchone()
                self.assertEquals(data[0],['abc', 'bcd', 'ceee', 'dddddd'])
            
            def test_set_prepare_combine(self): 
                conStr="CUBRID:localhost:33188:test_python:::"
                self.con = CUBRIDdb.connect(conStr, "dba","")        
                self.cur = self.con.cursor()
                self.con.set_autocommit(True)
                
                value =  [('abc','def'),('1','23','48')] 
                self.cur.execute("DROP TABLE IF EXISTS set_tbl")
                self.cur.execute("CREATE TABLE set_tbl ( col_1 set(varchar),col_2 set(int) )")
                self.cur.execute("insert into set_tbl values (?,?)", value)
                
                self.cur.execute("SELECT * FROM set_tbl")
                data = self.cur.fetchone()
                self.assertEquals(data[0],['abc', 'def'])
                self.assertEquals(data[1],['1', '23', '48'])
            
            
          • Result: Pass
          Show
          ryin005 Ray Yin added a comment - - edited Environment: OS : Linux 64 CUBRID : CUBRID 9.1 (9.1.0.0212) (64bit release build for linux_gnu) (Mar 14 2013 00:16:33) Python Driver: 9.1.0.0001 (20130603) svn: http://svn.cubrid.org/cubridapis/python/trunk/ Testcode (apis370.py) def test_set_prepare_int(self): conStr="CUBRID:localhost:33188:test_python:::" self.con = CUBRIDdb.connect(conStr, "dba","") self.cur = self.con.cursor() self.con.set_autocommit(True) value = [('1','2','3','4'),('5', '6', '7')] self.cur.execute("DROP TABLE IF EXISTS set_tbl") self.cur.execute("CREATE TABLE set_tbl ( col_1 set(int), col_2 set(int))") self.cur.execute("insert into set_tbl values (?, ?)", value) self.cur.execute("SELECT * FROM set_tbl") data = self.cur.fetchone() self.assertEquals(data[0],['1', '2', '3', '4']) value = [('a','b','c','d'),('5', '6', '7')] etype = (1,8) self.cur.execute("DROP TABLE IF EXISTS set_tbl") self.cur.execute("CREATE TABLE set_tbl ( col_1 set(char), col_2 set(int))") self.cur.execute("insert into set_tbl values (?, ?)", value, etype) self.cur.execute("SELECT * FROM set_tbl") data = self.cur.fetchone() self.assertEquals(data[0],['a', 'b', 'c', 'd']) self.assertEquals(data[1],['5', '6', '7']) def test_set_prepare_char(self): conStr="CUBRID:localhost:33188:test_python:::" self.con = CUBRIDdb.connect(conStr, "dba","") self.cur = self.con.cursor() self.con.set_autocommit(True) value = [('a','b','c','d')] self.cur.execute("DROP TABLE IF EXISTS set_tbl") self.cur.execute("CREATE TABLE set_tbl ( col_1 set(CHAR) )") self.cur.execute("insert into set_tbl values (?)", value) self.cur.execute("SELECT * FROM set_tbl") data = self.cur.fetchone() self.assertEquals(data[0],['a', 'b', 'c', 'd']) value = [('h','j')] self.cur.execute("DROP TABLE IF EXISTS set_tbl") self.cur.execute("CREATE TABLE set_tbl ( col_1 set(CHAR) )") self.cur.execute("insert into set_tbl values (?)", value, 1) self.cur.execute("SELECT * FROM set_tbl") data = self.cur.fetchone() self.assertEquals(data[0],['h', 'j']) def test_set_prepare_string(self): conStr="CUBRID:localhost:33188:test_python:::" self.con = CUBRIDdb.connect(conStr, "dba","") self.cur = self.con.cursor() self.con.set_autocommit(True) value = [('abc','bcd','ceee','dddddd')] self.cur.execute("DROP TABLE IF EXISTS set_tbl") self.cur.execute("CREATE TABLE set_tbl ( col_1 set(varchar) )") self.cur.execute("insert into set_tbl values (?)", value) self.cur.execute("SELECT * FROM set_tbl") data = self.cur.fetchone() self.assertEquals(data[0],['abc', 'bcd', 'ceee', 'dddddd']) def test_set_prepare_combine(self): conStr="CUBRID:localhost:33188:test_python:::" self.con = CUBRIDdb.connect(conStr, "dba","") self.cur = self.con.cursor() self.con.set_autocommit(True) value = [('abc','def'),('1','23','48')] self.cur.execute("DROP TABLE IF EXISTS set_tbl") self.cur.execute("CREATE TABLE set_tbl ( col_1 set(varchar),col_2 set(int) )") self.cur.execute("insert into set_tbl values (?,?)", value) self.cur.execute("SELECT * FROM set_tbl") data = self.cur.fetchone() self.assertEquals(data[0],['abc', 'def']) self.assertEquals(data[1],['1', '23', '48']) Result: Pass

            People

            • Assignee:
              cn15800 谢韦华[Bert]
              Reporter:
              ovidiu.veliscu Veliscu Ovidiu
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: