driver

// SPDX-FileCopyrightText: 2014-2021 SAP SE
//
// SPDX-License-Identifier: Apache-2.0

package main

import (
	"database/sql"
	"log"

	// Register hdb driver.
	_ "github.com/SAP/go-hdb/driver"
)

const (
	driverName = "hdb"
	hdbDsn     = "hdb://user:password@host:port"
)

func main() {
	db, err := sql.Open(driverName, hdbDsn)
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

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

Output:

ExampleBulkInsert inserts 1000 rows into a database table.

The insert SQL command is "bulk insert" instead of "insert". After the insertion of the values a final stmt.Exec() without parameters must be executed.

Caution: Bulk statements need to be executed in the context of a transaction or connection to guarantee that that all statement operations are done within the same connection.

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"

	"github.com/SAP/go-hdb/driver"
	"github.com/SAP/go-hdb/driver/drivertest"
)

func main() {
	connector, err := driver.NewConnector(drivertest.DefaultAttrs())
	if err != nil {
		log.Fatal(err)
	}
	db := sql.OpenDB(connector)
	defer db.Close()

	tableName := driver.RandomIdentifier("table_")

	// Create table.
	if _, err := db.Exec(fmt.Sprintf("create table %s (i integer, f float)", tableName)); err != nil {
		log.Fatal(err)
	}

	// Get connection for bulk insert.
	conn, err := db.Conn(context.Background())
	if err != nil {
		log.Fatal(err)
	}

	// Prepare statement on basis of connection (bulk insert).
	stmt, err := conn.PrepareContext(context.Background(), fmt.Sprintf("bulk insert into %s values (?,?)", tableName))
	if err != nil {
		log.Fatal(err)
	}
	defer stmt.Close()

	// Bulk insert.
	for i := 0; i < 1000; i++ {
		if _, err := stmt.Exec(i, float64(i)); err != nil {
			log.Fatal(err)
		}
	}
	// Call final stmt.Exec().
	if _, err := stmt.Exec(); err != nil {
		log.Fatal(err)
	}

	// Select number of inserted rows.
	var numRow int
	if err := db.QueryRow(fmt.Sprintf("select count(*) from %s", tableName)).Scan(&numRow); err != nil {
		log.Fatal(err)
	}
	fmt.Print(numRow)

	// Drop table.
	if _, err := db.Exec(fmt.Sprintf("drop table %s", tableName)); err != nil {
		log.Fatal(err)
	}

}

Output:

1000

ExampleCallSimpleOut creates a stored procedure with one output parameter and executes it. Stored procedures with output parameters must be executed by sql.Query or sql.QueryRow. For TestConnector see main_test.go.

const procOut = `create procedure %s (out message nvarchar(1024))
language SQLSCRIPT as
begin
    message := 'Hello World!';
end
`

connector, err := NewConnector(drivertest.DefaultAttrs())
if err != nil {
	log.Fatal(err)
}
db := sql.OpenDB(connector)
defer db.Close()

procedure := RandomIdentifier("procOut_")

if _, err := db.Exec(fmt.Sprintf(procOut, procedure)); err != nil { // Create stored procedure.
	log.Fatal(err)
}

var out string

if err := db.QueryRow(fmt.Sprintf("call %s(?)", procedure)).Scan(&out); err != nil {
	log.Fatal(err)
}

fmt.Print(out)

Output:

Hello World!

ExampleCallTableOut creates a stored procedure with one table output parameter and executes it making use of sql.Rows scan parameters (non-legacy mode - *please see connector.SetLegacy(false)). Stored procedures with table output parameters must be executed by sql.Query as sql.QueryRow will close the query after execution and prevent querying output table values. For TestConnector see main_test.go.

const procTable = `create procedure %[1]s (out t %[2]s)
language SQLSCRIPT as
begin
  create local temporary table #test like %[2]s;
  insert into #test values('Hello, 世界');
  insert into #test values('SAP HANA');
  insert into #test values('Go driver');
  t = select * from #test;
  drop table #test;
end
`
connector, err := NewConnector(drivertest.DefaultAttrs())
if err != nil {
	log.Fatal(err)
}
// *Switch to non-legacy mode.
connector.SetLegacy(false)
db := sql.OpenDB(connector)
defer db.Close()

tableType := RandomIdentifier("TableType_")
procedure := RandomIdentifier("ProcTable_")

if _, err := db.Exec(fmt.Sprintf("create type %s as table (x nvarchar(256))", tableType)); err != nil { // Create table type.
	log.Fatal(err)
}

if _, err := db.Exec(fmt.Sprintf(procTable, procedure, tableType)); err != nil { // Create stored procedure.
	log.Fatal(err)
}

var tableRows sql.Rows // Scan variable of table output parameter.

// Query stored procedure.
rows, err := db.Query(fmt.Sprintf("call %s(?)", procedure))
if err != nil {
	log.Fatal(err)
}
defer rows.Close()

if !rows.Next() {
	log.Fatal(rows.Err())
}
if err := rows.Scan(&tableRows); err != nil {
	log.Fatal(err)
}

for tableRows.Next() {
	var x string

	if err := tableRows.Scan(&x); err != nil {
		log.Fatal(err)
	}

	fmt.Println(x)
}
if err := tableRows.Err(); err != nil {
	log.Fatal(err)
}

Output:

Hello, 世界
SAP HANA
Go driver

ExampleCallTableOutLegacy creates a stored procedure with one table output parameter and executes it in legacy mode. Legacy mode: Stored procedures with table output parameters must be executed by sql.Query as sql.QueryRow will close the query after execution and prevent querying output table values. The scan type of a table output parameter is a string containing an opaque value to query table output values by standard sql.Query or sql.QueryRow methods. For TestConnector see main_test.go.

const procTable = `create procedure %[1]s (out t %[2]s)
language SQLSCRIPT as
begin
  create local temporary table #test like %[2]s;
  insert into #test values('Hello, 世界');
  insert into #test values('SAP HANA');
  insert into #test values('Go driver');
  t = select * from #test;
  drop table #test;
end
`

connector, err := NewConnector(drivertest.DefaultAttrs())
if err != nil {
	log.Fatal(err)
}
db := sql.OpenDB(connector)
defer db.Close()

tableType := RandomIdentifier("TableType_")
procedure := RandomIdentifier("ProcTable_")

if _, err := db.Exec(fmt.Sprintf("create type %s as table (x nvarchar(256))", tableType)); err != nil { // Create table type.
	log.Fatal(err)
}

if _, err := db.Exec(fmt.Sprintf(procTable, procedure, tableType)); err != nil { // Create stored procedure.
	log.Fatal(err)
}

var tableQuery string // Scan variable of table output parameter.

// Query stored procedure.
rows, err := db.Query(fmt.Sprintf("call %s(?)", procedure))
if err != nil {
	log.Fatal(err)
}
defer rows.Close()

if !rows.Next() {
	log.Fatal(rows.Err())
}
if err := rows.Scan(&tableQuery); err != nil {
	log.Fatal(err)
}

// Query stored procedure output table.
tableRows, err := db.Query(tableQuery)
if err != nil {
	log.Fatal(err)
}
defer tableRows.Close()

for tableRows.Next() {
	var x string

	if err := tableRows.Scan(&x); err != nil {
		log.Fatal(err)
	}

	fmt.Println(x)
}
if err := tableRows.Err(); err != nil {
	log.Fatal(err)
}

Output:

Hello, 世界
SAP HANA
Go driver

ExampleManyInsert inserts 1000 rows into a database table via a 'many' operation.

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"

	"github.com/SAP/go-hdb/driver"
	"github.com/SAP/go-hdb/driver/drivertest"
)

func main() {
	// Number of rows to be inserted into table.
	numRow := 1000

	connector, err := driver.NewConnector(drivertest.DefaultAttrs())
	if err != nil {
		log.Fatal(err)
	}
	db := sql.OpenDB(connector)
	defer db.Close()

	tableName := driver.RandomIdentifier("table_")

	// Create table.
	if _, err := db.Exec(fmt.Sprintf("create table %s (i integer, f double)", tableName)); err != nil {
		log.Fatal(err)
	}

	// Prepare statement.
	stmt, err := db.PrepareContext(context.Background(), fmt.Sprintf("insert into %s values (?, ?)", tableName))
	if err != nil {
		log.Fatal(err)
	}
	defer stmt.Close()

	// Prepare data.
	data := make([][]interface{}, numRow)
	for i := 0; i < numRow; i++ {
		data[i] = []interface{}{i, float64(i)}
	}

	// Insert many.
	if _, err := stmt.Exec(data); err != nil {
		log.Fatal(err)
	}

	// Select number of inserted rows.
	if err := db.QueryRow(fmt.Sprintf("select count(*) from %s", tableName)).Scan(&numRow); err != nil {
		log.Fatal(err)
	}
	fmt.Print(numRow)

	// Drop table.
	if _, err := db.Exec(fmt.Sprintf("drop table %s", tableName)); err != nil {
		log.Fatal(err)
	}

}

Output:

1000

TODO ExampleQuery: tbd

connector, err := NewConnector(drivertest.DefaultAttrs())
if err != nil {
	log.Fatal(err)
}
db := sql.OpenDB(connector)
defer db.Close()

table := RandomIdentifier("testNamedArg_")
if _, err := db.Exec(fmt.Sprintf("create table %s (i integer, j integer)", table)); err != nil {
	log.Fatal(err)
}

var i = 0
if err := db.QueryRow(fmt.Sprintf("select count(*) from %s where i = :1 and j = :1", table), 1).Scan(&i); err != nil {
	log.Fatal(err)
}

if err := db.QueryRow(fmt.Sprintf("select count(*) from %s where i = ? and j = :3", table), 1, "soso", 2).Scan(&i); err != nil {
	log.Fatal(err)
}

fmt.Print(i)

Output:

0