Connecting to a Database

When you call the standard SQL API open of the Go language to create a database connection, a connected object is returned to transfer the driver name and description string.

func Open(driverName, dataSourceName string) (*DB, error)

Example 1:

// A single IP address and a single port (ip:port) are used as an example. In this example, the username and password are stored in environment variables. Before running this example, set environment variables in the local environment (set the environment variable name based on the actual situation).
func main() {
 hostip := os.Getenv("GOHOSTIP")   // GOHOSTIP indicates the IP address written into the environment variable.
 port := os.Getenv("GOPORT")       // GOPORT indicates the port number written into the environment variable.
 usrname := os.Getenv("GOUSRNAME") // GOUSRNAME indicates the username written into the environment variable.
 passwd := os.Getenv("GOPASSWD")   // GOPASSWDW indicates the user password written into the environment variable.
 str := "host=" + hostip + " port=" + port + " user=" + usrname + " password=" + passwd + " dbname=postgres sslmode=disable" // DSN connection string
 //str := "opengauss://" + usrname + ":" + passwd + "@" + hostip + ":" + port + "/postgres?sslmode=disable"  // URL connection string
 db, err:= sql.Open("opengauss", str)
 if err != nil {
  log.Fatal(err)
 }
 defer db.Close()

 err = db.Ping()
 if err != nil {
  log.Fatal(err)
 }

 sqls := []string {
  "drop table if exists testExec",
  "create table testExec(f1 int, f2 varchar(20), f3 number, f4 timestamptz, f5 boolean)",
  "insert into testExec values(1, 'abcdefg', 123.3, '2022-02-08 10:30:43.31 +08', true)",
  "insert into testExec values(:f1, :f2, :f3, :f4, :f5)",
 }

 inF1 := []int{2, 3, 4, 5, 6}
	intF2 := []string{"hello world", "Huawei", "Beijing 2022 Winter Olympics", "nanjing", "Research Center"}
 intF3 := []float64{641.43, 431.54, 5423.52, 665537.63, 6503.1}
 intF4 := []time.Time{
  time.Date(2022, 2, 8, 10, 35, 43, 623431, time.Local),
  time.Date(2022, 2, 10, 19, 11, 54, 353431, time.Local),
  time.Date(2022, 2, 12, 6, 11, 15, 636431, time.Local),
  time.Date(2022, 2, 14, 4, 51, 22, 747653, time.Local),
  time.Date(2022, 2, 16, 13, 45, 55, 674636, time.Local),
 }
 intF5 := []bool{false, true, false, true, true}

 for _, s := range sqls {
  if strings.Contains(s, ":f") {
   for i, _ := range inF1 {
    _, err := db.Exec(s, inF1[i], intF2[i], intF3[i], intF4[i], intF5[i])
    if err != nil {
     log.Fatal(err)
    }
   }
  } else {
   _, err = db.Exec(s)
   if err != nil {
    log.Fatal(err)
   }
  }
 }

 var f1 int
 var f2 string
 var f3 float64
 var f4 time.Time
 var f5 bool
 err = db.QueryRow("select * from testExec").Scan(&f1, &f2, &f3, &f4, &f5)
 if err != nil {
  log.Fatal(err)
 } else {
  fmt.Printf("f1:%v, f2:%v, f3:%v, f4:%v, f5:%v\n", f1, f2, f3, f4, f5)
 }

 row, err :=db.Query("select * from testExec where f1 > :1", 1)
 if err != nil {
  log.Fatal(err)
 }
 defer row.Close()

 for row.Next() {
  err = row.Scan(&f1, &f2, &f3, &f4, &f5)
  if err != nil {
   log.Fatal(err)
  } else {
   fmt.Printf("f1:%v, f2:%v, f3:%v, f4:%v, f5:%v\n", f1, f2, f3, f4, f5)
  }
 }
}

Example 2:

// Multiple IP addresses and ports (ip:port) are used as an example. In this example, the username and password are stored in environment variables. Before running this example, set environment variables in the local environment (set the environment variable name based on the actual situation).
func main() {
 ctx := context.Background()
 ctx2SecondTimeout, cancelFunc2SecondTimeout := context.WithTimeout(ctx, 2 * time.Second)
 defer cancelFunc2SecondTimeout()

 hostip1 := os.Getenv("GOHOSTIP1")   // GOHOSTIP1 indicates the IP address written into the environment variable.
 hostip2 := os.Getenv("GOHOSTIP2")   // GOHOSTIP2 indicates the IP address written into the environment variable.
 hostip3 := os.Getenv("GOHOSTIP3")   // GOHOSTIP3 indicates the IP address written into the environment variable.
 port1 := os.Getenv("GOPORT1")       // GOPORT1 indicates the port number written into the environment variable.
 port2 := os.Getenv("GOPORT2")       // GOPORT2 indicates the port number written into the environment variable.
 usrname := os.Getenv("GOUSRNAME") // GOUSRNAME indicates the username written into the environment variable.
 passwd := os.Getenv("GOPASSWD")   // GOPASSWDW indicates the user password written into the environment variable.

 str := "host="+hostip1+","+hostip2+","+hostip3+" port="+port1+","+port2+" user="+usrname+" password="+passwd+" dbname=postgres sslmode=disable" // DSN connection string.
 //str := "opengauss://"+usrname+":"+passwd+"@"+hostip1+":"+port1+","+hostip2+":"+port2+","+hostip3+"/postgres?sslmode=disable"  // URL connection string.
 db, err:= sql.Open("opengauss", str)
 if err != nil {
  log.Fatal(err)
 }
 defer db.Close()

 // Ping database connection with 2 second timeout
 err = db.PingContext(ctx2SecondTimeout)
 if err != nil {
  log.Fatal(err)
 }

 sqls := []string {
  "drop table if exists testExecContext",
  "create table testExecContext(f1 int, f2 varchar(20), f3 number, f4 timestamptz, f5 boolean)",
  "insert into testExecContext values(1, 'abcdefg', 123.3, '2022-02-08 10:30:43.31 +08', true)",
  "insert into testExecContext values(:f1, :f2, :f3, :f4, :f5)",
 }

 inF1 := []int{2, 3, 4, 5, 6}
	intF2 := []string{"hello world", "Huawei", "Beijing 2022 Winter Olympics", "nanjing", "Research Center"}
 intF3 := []float64{641.43, 431.54, 5423.52, 665537.63, 6503.1}
	intF4 := []time.Time{
		time.Date(2022, 2, 8, 10, 35, 43, 623431, time.Local),
		time.Date(2022, 2, 10, 19, 11, 54, 353431, time.Local),
		time.Date(2022, 2, 12, 6, 11, 15, 636431, time.Local),
		time.Date(2022, 2, 14, 4, 51, 22, 747653, time.Local),
		time.Date(2022, 2, 16, 13, 45, 55, 674636, time.Local),
	}
	intF5 := []bool{false, true, false, true, true}

	for _, s := range sqls {
		if strings.Contains(s, ":f") {
			for i, _ := range inF1 {
				_, err := db.ExecContext(ctx2SecondTimeout, s, inF1[i], intF2[i], intF3[i], intF4[i], intF5[i])
				if err != nil {
					log.Fatal(err)
				}
			}
		} else {
			_, err = db.ExecContext(ctx2SecondTimeout, s)
			if err != nil {
				log.Fatal(err)
			}
		}
	}

	var f1 int
	var f2 string
	var f3 float64
	var f4 time.Time
	var f5 bool
	err = db.QueryRowContext(ctx2SecondTimeout, "select * from testExecContext").Scan(&f1, &f2, &f3, &f4, &f5)
	if err != nil {
		log.Fatal(err)
	} else {
		fmt.Printf("f1:%v, f2:%v, f3:%v, f4:%v, f5:%v\n", f1, f2, f3, f4, f5)
	}

	row, err :=db.QueryContext(ctx2SecondTimeout, "select * from testExecContext where f1 > :1", 1)
	if err != nil {
		log.Fatal(err)
	}
	defer row.Close()

	for row.Next() {
		err = row.Scan(&f1, &f2, &f3, &f4, &f5)
		if err != nil {
			log.Fatal(err)
		} else {
			fmt.Printf("f1:%v, f2:%v, f3:%v, f4:%v, f5:%v\n", f1, f2, f3, f4, f5)
		}
	}
}