文件列表:

programs/ (0, 2020-11-26)
programs/connect.go (517, 2020-11-26)
programs/count_rows.go (905, 2020-11-26)
programs/create_table.go (734, 2020-11-26)
programs/delete_rows.go (1075, 2020-11-26)
programs/get_column_names.go (1021, 2020-11-26)
programs/hello_world.go (91, 2020-11-26)
programs/insert_multiple_rows.go (1158, 2020-11-26)
programs/insert_one_row.go (726, 2020-11-26)
programs/prepare_and_select.go (1823, 2020-11-26)
programs/select_rows.go (1018, 2020-11-26)
programs/update_multiple_rows_in_one_unit_of_work.go (1892, 2020-11-26)
programs/update_multiple_rows_with_autocommit.go (1584, 2020-11-26)
programs/update_row.go (983, 2020-11-26)

# Accessing Db2 with the Go programming language The sample programs in this repository show how to access and modify data in **Db2** using the **Go** programming language. *Go* was designed at Google to improve programming productivity. You can use the *Db2 Community Edition* to work with the sample programs. It is a full-featured version of Db2 but with automatically enforced sizing limitations. It can deploy up to 4 cores, 16 GB memory and 100 GB storage for the database. Read through the following tutorial to understand how the sample programs select data from *Db2* and how they insert, update and delete data. To understand the program examples, basic knowledge of *Go* language or *C* programming language is recommended. If you just want to get hands-on programming experience with *Go* language and *Db2* you can also take the tutorial on a virtual machine in the cloud. This has all required software pre-installed and can be used for up to one week: https://www.ibm.com/cloud/garage/dte/tutorial/go-db2-use-go-language-develop-db2-database-applications ## Overview of the sample programs Here is an overview of the sample programs. Click one of the following links to jump to the corresponding tutorial section further down: * [`connect.go` Connects to the Db2 *sample* database.](https://github.com/andreas-christian/Go-Db2/blob/master/#connect) * [`count_rows.go` Counts the number of records in some of the tables.](https://github.com/andreas-christian/Go-Db2/blob/master/#count_rows) * [`select_rows.go` Executes a SELECT statement and retrieves the result set.](https://github.com/andreas-christian/Go-Db2/blob/master/#select_rows) * [`prepare_and_select.go` Executes a SELECT statement multiple times with different filter conditions.](https://github.com/andreas-christian/Go-Db2/blob/master/#prepare_and_select) * [`create_table.go` Executes a CREATE TABLE statement.](https://github.com/andreas-christian/Go-Db2/blob/master/#create_table) * [`insert_one_row.go` Executes a simple INSERT statement.](https://github.com/andreas-christian/Go-Db2/blob/master/#insert_one_row) * [`insert_multiple_rows.go` Prepares an INSERT statement and then inserts multiple rows into a table.](https://github.com/andreas-christian/Go-Db2/blob/master/#insert_multiple_rows) * [`delete_rows.go` Deletes multiple rows in a loop.](https://github.com/andreas-christian/Go-Db2/blob/master/#delete_rows) * [`get_column_names.go` Returns the names of the columns in a table.](https://github.com/andreas-christian/Go-Db2/blob/master/#get_column_names) * [`update_row.go` Updates exactly one row in a table. ](https://github.com/andreas-christian/Go-Db2/blob/master/#update_row) * [`update_multiple_rows_with_autocommit.go` Updates multiple rows in a loop. Each update is immediately commited.](https://github.com/andreas-christian/Go-Db2/blob/master/#update_multiple_rows_with_autocommit) * [`update_multiple_rows_in_one_unit_of_work.go` Updates multiple rows in one unit of work.](https://github.com/andreas-christian/Go-Db2/blob/master/#update_multiple_rows_in_one_unit_of_work) ## Download and Install the required software Here are the prerequisites to run the sample programs on your local machine: - Install a *Go language* binary release suitable for your operating system: https://golang.org/ - Install Db2. The Db2 Community Edition is available as standard download or as a docker container which can be installed with a single command as described here: https://medium.com/@ajstorm/installing-db2-on-your-coffee-break-5be1d811b052. You find more infos about the different deployment options of *Db2 Community Edition* in the following article: https://www.ibm.com/cloud/blog/announcements/ibm-db2-developer-community-edition - Install the *go_ibm_db* cli driver (also check the API documentation): https://github.com/ibmdb/go_ibm_db - Download and extract this Github repository. If you have the ```git``` command line tool installed you can clone the repository to your local machine like this: ```git clone https://github.com/andreas-christian/Go-Db2``` ## Create the Db2 sample database After you have installed the required software you also need to create the Db2 *sample* database. On Linux for example, you can run the following commands in a shell: ``` su - db2inst1 db2sample ``` The sample database contains a number of sample tables that include some data you can work with. The tables are located in schema *DB2INST1*. To see all the tables that are included in the sample database you can take the following steps (Linux): ``` su - db2inst1 db2start db2 connect to sample db2 "select substr(tabname,1,20) from syscat.tables where tabschema='DB2INST1'" ``` Details around the ```db2sample``` command can be found here: https://www.ibm.com/support/knowledgecenter/SSEPGG_11.5.0/com.ibm.db2.luw.apdv.samptop.doc/doc/t0006757.html ## How to execute the sample programs **Note:** Run the sample programs in the specified order. Some of the programs make changes to the database that are required later on by other programs. Also adjust the connect parameters in the program code and replace the password with the password of your *db2inst1* user. The following example shows how to execute the sample programs. We assume that you have downloaded and extracted the git repository in the home directory of your user. To run program *hello_world.go* on Linux for example, you take the following steps: ``` cd ~/Go-Db2/programs go run hello_world.go ``` # Go Db2 Tutorial In the following sections, we explain each of the sample programs in more detail. The *Go* code of each sample program is listed at the end of each section. ## connect.go ```connect.go``` is a simple *Go* program that connects to the *SAMPLE* database. It imports the following packages which are required to deploy the Db2 driver API: ``` import _ "github.com/ibmdb/go_ibm_db" import "database/sql" ``` **Note:** The underscore before the package *github.com/ibmdb/go_ibm_db* is required. It ensures that the init function of the package is executed and package-level variables are created. The function *sql.Open()* is executed to setup a database connection. It requires the driver name *go_ibm_db* and the connection string *con* as input parameters. The connection string specifies *hostname, port number, database name, user name,* and *password*. If *sql.Open()* was executed successfully, the database handle *db* is initialized. Otherwise it will be set to *nil*. Before the program terminates it calls function *db.Close()*. It closes the database connection and cleans up the database handle. Since the database handle *db* is always required to execute a Db2 API function, we define the connection related variables *db, err,* and *con* outside of function *main()*. This makes sure we can access the database handle in all functions that are defined in package *main*. We put the *defer* keyword in front of the call of function *db.Close()*. This makes sure that the function is automatically executed as soon as a return statement is executed anywhere in function main(). Execute *connect.go* from the shell as described in the previous section. ``` // connect.go package main import ( _ "github.com/ibmdb/go_ibm_db" "database/sql" "fmt" ) var err error var db *sql.DB var con = "HOSTNAME=localhost;PORT=50000;DATABASE=SAMPLE;UID=DB2INST1;PWD=db2inst1" func connect() error { db, err = sql.Open("go_ibm_db", con) if err != nil { fmt.Println(err) return err } return nil } func main() { if connect() != nil { return } else { defer db.Close() } fmt.Println("Success!") } ``` # count_rows.go In the next example, we use function *db.QueryRow()* to run a query that is expected to return at most one row. If there are multiple rows in the query result set, the function will only access the first row and discard the rest. We use the following type of *SELECT* statement in this example: ``` select count(*) statement from ``` You notice that the SQL statement text is created by appending the table name in variable *tabname* to the rest of the SQL text. That means the SQL statement is created at runtime (not at compile time). The statement always returns exactly one row which contains the number of records that are stored in the table. The *Scan()* function copies the columns from the current row into the values pointed. Since we expect a single integer value in the query result set, we define variable *count* of type *int32* and pass a pointer to that variable into function *scan()*. ``` // count_rows.go package main import ( _ "github.com/ibmdb/go_ibm_db" "database/sql" "fmt" ) var err error var db *sql.DB var con = "HOSTNAME=localhost;PORT=50000;DATABASE=SAMPLE;UID=DB2INST1;PWD=db2inst1" func connect() error { db, err = sql.Open("go_ibm_db", con) if err != nil { fmt.Println(err) return err } return nil } func count(tabname string) error { var count int32 err = db.QueryRow("SELECT count(*) FROM "+tabname).Scan(&count) if err != nil { fmt.Println(err) return err } fmt.Printf("Table \"%s\" contains %d rows.\n",tabname,count) return nil } func main() { if connect() != nil { return } else { defer db.Close() } count("ACT") count("DEPARTMENT") count("EMPLOYEE") count("ORG") } ``` # select_rows.go Next, you learn how to run a *SELECT* statement that returns more than one row. In this example, we use the following select statement: ``` select firstnme, lastname, job from employee where job='MANAGER' ``` Function *db.Query()* prepares and executes the *SELECT* statement. It is also possible to separate the preparation and execution of a SQL statement. This can help to reduce the overhead for statement preparation and is shown in a later example. Function *rows.Next()* iterates over the result set and prepares the next result row for reading with the *Scan()* api. Since the *SELECT* statement returns three values in each row of the result set, we define variables *a,b,* and *c* and pass their address to function *Scan()*. The function copies the columns from the current row into variables *a,b,* and *c*. ``` // select_rows.go package main import ( _ "github.com/ibmdb/go_ibm_db" "database/sql" "fmt" ) var err error var db *sql.DB var con = "HOSTNAME=localhost;PORT=50000;DATABASE=SAMPLE;UID=DB2INST1;PWD=db2inst1" func connect() error { db, err = sql.Open("go_ibm_db", con) if err != nil { fmt.Println(err) return err } return nil } func main() { if connect() != nil { return } else { defer db.Close() } rows,err := db.Query("select firstnme, lastname, job from employee where job='MANAGER'") if rows != nil {defer rows.Close()} if err != nil { fmt.Println(err) return } // iterate over all rows in the query result var a,b,c string for rows.Next() { err = rows.Scan(&a,&b,&c) if err != nil{ fmt.Println(err) return } fmt.Printf("%-10s %-10s %-10s\n",a,b,c) } } ``` # prepare_and_select.go In some cases you may want to execute a query several times but with different filter conditions. In this case, you can prepare the statement once and then execute it multiple times and use a different filter condition in each execution. This allows you to reuse the prepared statement and reduces the overhead of statement preparation. To do this you need to use a parameter marker (?) as shown in the example below: ``` select firstnme, lastname, job, workdept from employee where workdept = ? ``` The statement is prepared by executing function *db.Prepare()*. The function returns a handle *st* to the prepared statement: ``` st, err := db.Prepare("select firstnme, lastname, job, workdept from employee where workdept = ?") ``` Function *st.Query()* prepares and executes the SQL statement. We use statement handle *st* to reference the prepared statement. We also have to pass the appropriate number of parameters to the function. Since we prepared the statement with one parameter marker, we pass one parameter dept to the function: ``` rows,err := st.Query(dept) ``` Here is the corresponding sample program: ``` // prepare_and_select.go package main import ( _ "github.com/ibmdb/go_ibm_db" "database/sql" "fmt" ) var err error var db *sql.DB var con = "HOSTNAME=localhost;PORT=50000;DATABASE=SAMPLE;UID=DB2INST1;PWD=db2inst1" func connect() error { db, err = sql.Open("go_ibm_db", con) if err != nil { fmt.Println(err) return err } return nil } func main() { if connect() != nil { return } else { defer db.Close() } // prepare the statement once with a parameter marker st, err := db.Prepare("select firstnme, lastname, job, workdept from employee where workdept = ?" ) if err !=nil { fmt.Println("Error in Prepare: ") fmt.Println(err) return } // execute the statement multiple times and use a different // work department in the where clause for each query execution departments := []string{"A00","B01","C01","D11","D21","E11","E21"} for _,dept := range departments{ fmt.Printf("\nSelect records for department '%s'\n", dept) rows,err := st.Query(dept) if err != nil { fmt.Println("Error in Query: ") fmt.Println(err) return } // iterate over all rows in the query result for rows.Next() { var a,b,c,d string err = rows.Scan(&a,&b,&c,&d) if err != nil{ fmt.Println(err) return } fmt.Printf("%-10s %-10s %-10s %-10s\n",a,b,c,d) } rows.Close() } } ``` # create_table.go The next sample program creates a new table using the following SQL statement: ``` create table LINEITEM(ID int, NAME varchar(20), QTY int) ``` In this example, the statement is prepared and executed in one step. In case the table already exists, function *Exec()* will return error *SQL0601* and the program will only print the error message and terminate. ``` // create_table.go package main import ( _ "github.com/ibmdb/go_ibm_db" "database/sql" "fmt" ) var err error var db *sql.DB var con = "HOSTNAME=localhost;PORT=50000;DATABASE=SAMPLE;UID=DB2INST1;PWD=db2inst1" func connect() error { db, err = sql.Open("go_ibm_db", con) if err != nil { fmt.Println(err) return err } return nil } func main() { if connect() != nil { return } else { defer db.Close() } _,err:=db.Exec("create table LINEITEM(ID int,NAME varchar(20),QTY int)") if err != nil{ fmt.Println("Error:") fmt.Println(err) return } fmt.Println("TABLE CREATED") } ``` # insert_one_row.go The next sample program inserts a single row into table *LINEITEM* using the following SQL statement: ``` insert into lineitem values (99,'Flowers',5) ``` The Db2-GO API provides function *Exec()* to execute DML statements (*INSERT, UPDATE, DELETE, CREATE,DROP*). This function can either prepare and execute a statement in one single step or you can first prepare a statement and then use *Exec()* to execute the statement. In cases where you only execute a SQL statement one time, you can keep the code simple and prepare and execute the statement in one single step: ``` _,err:=db.Exec("insert into lineitem values (99,'Flowers',5)") ``` Since the *INSERT* statement does not return any data, we only retrieve the err code from the function call (see the underscore left from *err*). ``` // insert_one_row.go package main import ( _ "github.com/ibmdb/go_ibm_db" "database/sql" "fmt" ) var err error var db *sql.DB var con = "HOSTNAME=localhost;PORT=50000;DATABASE=SAMPLE;UID=DB2INST1;PWD=db2inst1" func connect() error { db, err = sql.Open("go_ibm_db", con) if err != nil { fmt.Println(err) return err } return nil } func main() { if connect() != nil { return } else { defer db.Close() } _,err:=db.Exec("insert into lineitem values (99,'Flowers',5)") if err != nil{ fmt.Println("Error:") fmt.Println(err) return } fmt.Println("Row inserted.") } ``` # insert_multiple_rows.go If you want to insert multiple records into a table, you can first prepare the *INSERT* statement and then execute it multiple times as shown in the next example. We use a *INSERT* statement which contains parameter markers as follows: ``` insert into lineitem values (?,?,?) ``` We first execute function *Prepare()* which returns handle *st* to the prepared statement: ``` st, err := db.Prepare("insert into lineitem values (?,?,?)") ``` Then we create a slice *lineitems*, iterate over the elements in the slice, assign the value of each element to variable *item*, and its index number to variable *idx*. **Note:** In Go language a *slice* can be used similar to an array. While arrays have a static size, slices can grow in size (although under covers they are based on static arrays). ``` lineitems := []string{"Shirt","Bicycle","Laptop","Coffee","Burger","Watch"} for idx,item := range lineitems { ``` Finally, we call function *Exec()* and pass parameter values for each of the three parameter markers. We use a constant value 5 for column *QTY* (quantity): ``` _,err = st.Exec(idx,item,5) ``` Here is the corresponding sample program: ``` // insert_multiple_rows.go package main import ( _ "github.com/ibmdb/go_ibm_db" "database/sql" "fmt" ) var err error var db *sql.DB var con = "HOSTNAME=localhost;PORT=50000;DATABASE=SAMPLE;UID=DB2INST1;PWD=db2inst1" func connect() error { db, err = sql.Open("go_ibm_db", con) if err != nil { fmt.Println(err) return err } return nil } func main() { if connect() != nil { return } else { defer db.Close() } // prepare the statement once with parameter markers st, err := db.Prepare("insert into lineitem values (?,?,?)" ) if err !=nil { fmt.Println("Error in Prepare: ") fmt.Println(err) return } lineitems:= []string{"Shirt","Bicycle","Laptop","Coffee","Burger","Watch"} for idx,item := range lineitems{ _,err = st.Exec(idx,item,5) if err != nil{ fmt.Println("Error:") fmt.Println(err) return } fmt.Println("Row inserted.") } } ``` # delete_rows.go The next program example deletes multiple records from table *LINEITEM*. It uses a *DELETE* statement which contains a parameter marker as follows: ``` delete from lineitem where name=? ``` The statement is prepared with the following function call: ``` st, err := db.Prepare("delete from lineitem where name=?") ``` The names of the items to be deleted are stored in slice *lineitems*. The program iterates over the slice as shown below: ``` lineitems := []string{"Shirt","Coffee"} for _,item := range lineitems{ _,err = st.Exec(item) ``` There are dif ... ...

近期下载者：

相关文件：

评论：[我要评论] [举报此文件]

收藏者：