go-ora and time.Time{} changes from v2.7.24 to v2.7.25

[wafflze]

I am researching a potential issue created (or maybe solved) when going from go-ora version 2.7.24 to any version greater than that.

Currently, I believe the issue is related to the following (commit af81479):

Version 2.7.25

• Added feature that enables the driver to read Oracle 23c wallet
• Introduced passing time.Time{} for DATE, TIMESTAMP, and TIMESTAMP WITH TIMEZONE data types, so there is no need for go_ora.TimeStamp and go_ora.TimeStampTZ custom types, and they will be deprecated.
• Other bug fixes

I have a table with a single DATE type column:

-- Create table
create table MyTable
(
  current_date DATE
);

Prior to version 2.7.24, we could simply write:

var t time.Time
expectedtopass := time.Date(2023, 12, 31, 0, 0, 0, 0, time.Local)
if err := p.odb.FetchOne("select current_date from MyTable where current_date = :1", []interface{}{expectedtopass}, &t); err != nil {
    return err
}

and get the expected result.

However, since the changes that took place in version 2.7.25 and later, we have to modify our select statements to look like the following: TO_DATE(TO_CHAR(:1, 'YYYY-MM-DD'), 'YYYY-MM-DD'). This is a problem because we have hundreds of queries that would need to be modified:

var t time.Time
expectedtopass := time.Date(2023, 12, 31, 0, 0, 0, 0, time.Local)
if err := p.odb.FetchOne("select current_date from MyTable where current_date = TO_DATE(TO_CHAR(:1, 'YYYY-MM-DD'), 'YYYY-MM-DD')", []interface{}{expectedtopass}, &t); err != nil {
    return err
}

Without rolling back to versions prior to 2.7.24, how would you recommend addressing this? One of the reasons for the need to update our dependencies is to stay ahead of security scans in our pipelines.

[sijms]

code:

package main

import (
	"database/sql"
	"fmt"
	_ "github.com/sijms/go-ora/v2"
	"os"
	"time"
)

func execCmd(db *sql.DB, stmts ...string) error {
	for _, stmt := range stmts {
		if _, err := db.Exec(stmt); err != nil {
			if len(stmts) > 1 {
				return fmt.Errorf("error: %v in execuation of stmt: %s", err, stmt)
			} else {
				return err
			}
		}
	}
	return nil
}

func createTable(db *sql.DB) error {
	t := time.Now()
	err := execCmd(db, `CREATE TABLE TTB_568(
	ID			number(10)	NOT NULL,
	NAME		VARCHAR(200),
	VAL			number(10,2),
	LDATE	date,
	PRIMARY KEY(ID)
	)`)
	if err != nil {
		return err
	}
	fmt.Println("Finish create table :", time.Now().Sub(t))
	return nil
}

func dropTable(db *sql.DB) error {
	t := time.Now()
	err := execCmd(db, "drop table TTB_568 purge")
	if err != nil {
		return err
	}
	fmt.Println("Finish drop table: ", time.Now().Sub(t))
	return nil
}

func insert(db *sql.DB) error {
	t := time.Now()
	t_input := time.Date(2023, 12, 31, 0, 0, 0, 0, time.Local)
	_, err := db.Exec("INSERT INTO TTB_568 (ID, LDATE) VALUES(:ID, :LDATE)", 1, t_input)
	if err != nil {
		return err
	}
	fmt.Println("Finish insert data: ", time.Now().Sub(t))
	return nil
}

func query(db *sql.DB) error {
	t := time.Now()
	t_input := time.Date(2023, 12, 31, 0, 0, 0, 0, time.Local)
	var t_output time.Time
	err := db.QueryRow("SELECT LDATE FROM TTB_568 WHERE LDATE=:1", t_input).Scan(&t_output)
	if err != nil {
		return err
	}
	fmt.Println("Output Date: ", t_output)
	fmt.Println("Finish query: ", time.Now().Sub(t))
	return nil
}
func main() {
	db, err := sql.Open("oracle", os.Getenv("DSN"))
	if err != nil {
		fmt.Println("can't connect: ", err)
		return
	}
	defer func() {
		err = db.Close()
		if err != nil {
			fmt.Println("can't close connection: ", err)
		}
	}()

	err = createTable(db)
	if err != nil {
		fmt.Println("Can't create table: ", err)
		return
	}
	defer func() {
		err = dropTable(db)
		if err != nil {
			fmt.Println("Can't drop table: ", err)
		}
	}()
	err = insert(db)
	if err != nil {
		fmt.Println("can't insert: ", err)
		return
	}
	err = query(db)
	if err != nil {
		fmt.Println("can't query: ", err)
		return
	}
}

output:

Finish create table : 2.844176912s
Finish insert data:  410.130307ms
Output Date:  2023-12-31 00:00:00 +0000 UTC
Finish query:  307.126702ms
Finish drop table:  151.51636ms