Can't support BLOB type for the bulk insert

[zhanghaiyang9999]

I used the example code of merge/main.go to test bulk insert, all other data types are good except the BLOB data type. If the table contains BLOB type, the error is ORA-03146: invalid buffer length for TTC field Please refer the following test codes, thanks!

//create a test table conn.Exec(Create Table blobtest (COLUMN1 BLOB)) val, err := ioutil.ReadFile("test.jpg") buffer := bytes.Buffer{} buffer.Write(val)

col_len := 1
names := make([]interface{}, col_len)

blobs := make([]interface{}, 2)
blobs[0] = go_ora.Blob{Data: buffer.Bytes()}
blobs[1] = go_ora.Blob{Data: buffer.Bytes()}

names[0] = sql.Named("C1", blobs)
sql_str := `INSERT INTO blobtest(COLUMN1)VALUES(:C1)`
_, err = conn.Exec(sql_str, names...)
log.Println(err)

[sijms]

fixed in next release

[zhanghaiyang9999]

Thanks! that's great! I tested this with v2.7.22, the error was same. Will be the fix in release v2.7.23?

[sijms]

yes i fix all new issues and make the new release

[zhanghaiyang9999]

yes i fix all new issues and make the new release

Thanks! I will wait the new release 2.7.23

[sijms]

fixed in v2.7.23

[zhanghaiyang9999]

it's great! I verified it!!!

[zhanghaiyang9999]

hi @sijms ,it fixed the blob bulk insert issue, but it introduced a regression issue, if the value is nil, it will return the error: ORA-03115: unsupported network datatype or representation This error related to all the types, such as vachar, blob,etc.

before the release 2.7.22, nil is supported for all the filed types.

[sijms]

ok i will test

[sijms]

I use this function for testing

func bulkInsert2(db *sql.DB, rowNum int) error {
    t := time.Now()
    sqlText := `INSERT INTO GOORA_TEMP_VISIT(VISIT_ID, NAME, VAL, VISIT_DATE, major) VALUES(:1, :2, :3, :4, :5)`
    id := make([]int, rowNum)
    name := make([]*string, rowNum)
    val := make([]*float64, rowNum)
    date := make([]*time.Time, rowNum)
    major := make([]sql.NullString, rowNum)
    initalVal := 0.1
    value := "test"
    dateVal := time.Now()
    for x := 0; x < rowNum; x++ {
        id[x] = x + 1
        if x%2 == 0 {
            name[x] = nil
            val[x] = nil
            date[x] = nil
        } else {
            name[x] = &value
            val[x] = &initalVal
            date[x] = &dateVal
        }
        initalVal += 0.1
        if x == 0 {
            major[x] = sql.NullString{"M-13", true}
        } else {
            if x%2 == 0 {
                major[x] = sql.NullString{"", false}
            } else {
                major[x] = sql.NullString{"SP-17", true}
            }

        }
    }
    _, err := db.Exec(sqlText, id, name, val, date, major)
    if err != nil {
        return err
    }
    fmt.Println("Finish insert ", rowNum, " rows: ", time.Now().Sub(t))
    return nil
}

result from sql developer

id	name	val	date	major
1	(null)	(null)	(null)	M-13
2	test	1.1	28-NOV-23	SP-17
3	(null)	(null)	(null)	(null)
4	test	1.1	28-NOV-23	SP-17
5	(null)	(null)	(null)	(null)
6	test	1.1	28-NOV-23	SP-17
7	(null)	(null)	(null)	(null)
8	test	1.1	28-NOV-23	SP-17
9	(null)	(null)	(null)	(null)
10	test	1.1	28-NOV-23	SP-17

[zhanghaiyang9999]

I use this function for testing

func bulkInsert2(db *sql.DB, rowNum int) error {
  t := time.Now()
  sqlText := `INSERT INTO GOORA_TEMP_VISIT(VISIT_ID, NAME, VAL, VISIT_DATE, major) VALUES(:1, :2, :3, :4, :5)`
  id := make([]int, rowNum)
  name := make([]*string, rowNum)
  val := make([]*float64, rowNum)
  date := make([]*time.Time, rowNum)
  major := make([]sql.NullString, rowNum)
  initalVal := 0.1
  value := "test"
  dateVal := time.Now()
  for x := 0; x < rowNum; x++ {
      id[x] = x + 1
      if x%2 == 0 {
          name[x] = nil
          val[x] = nil
          date[x] = nil
      } else {
          name[x] = &value
          val[x] = &initalVal
          date[x] = &dateVal
      }
      initalVal += 0.1
      if x == 0 {
          major[x] = sql.NullString{"M-13", true}
      } else {
          if x%2 == 0 {
              major[x] = sql.NullString{"", false}
          } else {
              major[x] = sql.NullString{"SP-17", true}
          }

      }
  }
  _, err := db.Exec(sqlText, id, name, val, date, major)
  if err != nil {
      return err
  }
  fmt.Println("Finish insert ", rowNum, " rows: ", time.Now().Sub(t))
  return nil
}

result from sql developer

id name val date major 1 (null) (null) (null) M-13 2 test 1.1 28-NOV-23 SP-17 3 (null) (null) (null) (null) 4 test 1.1 28-NOV-23 SP-17 5 (null) (null) (null) (null) 6 test 1.1 28-NOV-23 SP-17 7 (null) (null) (null) (null) 8 test 1.1 28-NOV-23 SP-17 9 (null) (null) (null) (null) 10 test 1.1 28-NOV-23 SP-17

It occurred in named method: COLUMN1 type is VARCHAR2 COLUMN2 type is BLOB For example:

col_len := 2 names := make([]interface{}, col_len)

blobs := make([]interface{}, 1) blobs[0] = go_ora.Blob{Data: buffer.Bytes()} vars:= make([]interface{}, 1) vars[0]=nil names[0] = sql.Named("C1", blobs) names[1] = sql.Named("C2",vars)

sqlstr := INSERT INTO test(COLUMN1,COLUMN2)VALUES(:C1,:C2) , err = conn.Exec(sql_str, names...) log.Println(err)

[sijms]

another version of the function use interface{} and named parameters

func bulkInsert3(db *sql.DB, rowNum int) error {
    t := time.Now()
    sqlText := `INSERT INTO GOORA_TEMP_VISIT(VISIT_ID, NAME, VAL, VISIT_DATE, major, DATA) VALUES(:id, :name, :val, :dat, :major, :data)`
    id := make([]int, rowNum)
    name := make([]*string, rowNum)
    val := make([]*float64, rowNum)
    date := make([]interface{}, rowNum)
    major := make([]sql.NullString, rowNum)
    data := make([]interface{}, rowNum)
    initalVal := 0.1
    value := "test"
    dateVal := time.Now()
    for x := 0; x < rowNum; x++ {
        id[x] = x + 1
        if x%2 == 0 {
            name[x] = nil
            val[x] = nil
            date[x] = nil
            data[x] = nil
        } else {
            name[x] = &value
            val[x] = &initalVal
            date[x] = dateVal
            data[x] = go_ora.Blob{Data: []byte("this is a test"), Valid: true}
        }
        initalVal += 0.1
        if x == 0 {
            major[x] = sql.NullString{"M-13", true}
        } else {
            if x%2 == 0 {
                major[x] = sql.NullString{"", false}
            } else {
                major[x] = sql.NullString{"SP-17", true}
            }

        }
    }
    _, err := db.Exec(sqlText, sql.Named("id", id),
        sql.Named("name", name),
        sql.Named("val", val),
        sql.Named("dat", date),
        sql.Named("major", major),
        sql.Named("data", data))
    if err != nil {
        return err
    }
    fmt.Println("Finish insert ", rowNum, " rows: ", time.Now().Sub(t))
    return nil
}

everything is ok you code

col_len := 2
// here ok you use col_len
names := make([]interface{}, col_len)

// here you should use col_len also
blobs := make([]interface{}, 1)
blobs[0] = go_ora.Blob{Data: buffer.Bytes()}
vars:= make([]interface{}, 1)
vars[0]=nil
names[0] = sql.Named("C1", blobs)
names[1] = sql.Named("C2",vars)

// you pass different array size(1 and 2) so the driver will use the lower one which is 1
// if you has array with one parameter so no need for bulk insert
sql_str := INSERT INTO test(COLUMN1,COLUMN2)VALUES(:C1,:C2)
_, err = conn.Exec(sql_str, names...)
log.Println(err)

[zhanghaiyang9999]

hi @sijms ,the issue is related to nil value, from my test, the error only occurred when some fields values are nil, for example:

Insert 3 records for two fields, the following codes worked well, all the values are not nil.

    col_len := 2
names := make([]interface{}, col_len)

col_2 := make([]interface{}, 3)
col_2[0] = `test`
col_2[1] = `test`
col_2[2] = `test`
blobs := make([]interface{}, 3)
blobs[0] = go_ora.Blob{Data: buffer.Bytes(), Valid: true}
blobs[1] = go_ora.Blob{Data: buffer.Bytes(), Valid: true}
blobs[2] = nil

names[0] = sql.Named("C1", blobs)
names[1] = sql.Named("C2", col_2)
//log.Println(names)
sql_str := `INSERT INTO blobtest(COLUMN1,COLUMN2)VALUES(:C1,:C2)`
_, err = conn.Exec(sql_str, names...)

But if set the collum2 value is nil, it will failed, the codes are: col_len := 2 names := make([]interface{}, col_len)

col_2 := make([]interface{}, 3)
col_2[0] = nil
col_2[1] = nil
col_2[2] = nil
blobs := make([]interface{}, 3)
blobs[0] = go_ora.Blob{Data: buffer.Bytes(), Valid: true}
blobs[1] = go_ora.Blob{Data: buffer.Bytes(), Valid: true}
blobs[2] = nil

names[0] = sql.Named("C1", blobs)
names[1] = sql.Named("C2", col_2)
//log.Println(names)
sql_str := `INSERT INTO blobtest(COLUMN1,COLUMN2)VALUES(:C1,:C2)`
_, err = conn.Exec(sql_str, names...)
log.Println(err)

if the 3 values are nil, it will failed, if the 3 values have any one not nil, it will succeed. the following codes are ok, because one value is 'test' col_len := 2 names := make([]interface{}, col_len)

col_2 := make([]interface{}, 3)
col_2[0] = `test`
col_2[1] = nil
col_2[2] = nil
blobs := make([]interface{}, 3)
blobs[0] = go_ora.Blob{Data: buffer.Bytes(), Valid: true}
blobs[1] = go_ora.Blob{Data: buffer.Bytes(), Valid: true}
blobs[2] = nil

names[0] = sql.Named("C1", blobs)
names[1] = sql.Named("C2", col_2)
//log.Println(names)
sql_str := `INSERT INTO blobtest(COLUMN1,COLUMN2)VALUES(:C1,:C2)`
_, err = conn.Exec(sql_str, names...)
log.Println(err)

by the way, the version 2.7.22 is good(expect the blob issue.).

[sijms]

fixed in next release

[sijms]

Fixed in v2.7.24

[zhanghaiyang9999]

Fixed in v2.7.24

Thanks !great!!

[zhanghaiyang9999]

verified!!