GoLang

GoLang : Using sqlx for record mapping into Structs

Posted on Updated on

When working with GoLang and retrieving records from a database, this is typically done by retrieving a row and then parsing it to extract the various attributes and then in turn mapping them to variables to to a struct. For example, the following code shows the executing a query and then parsing the rows to process the returned attributes and assigning them to a variable.

import (
    "fmt"
    "time"
    "database/sql"
    godror "github.com/godror/godror"
)

func main(){
    username := <username>; 
    password := <password>;
    host := <host>:<port>;
    database := <database name>;

    <code to create the connection - didn't include to save space>

    dbQuery := "select table_name, tablespace_name from user_tables where table_name not like 'DM$%' and table_name not like 'ODMR$%'"
    rows, err := db.Query(dbQuery)
    if err != nil {
        fmt.Println(".....Error processing query")
        fmt.Println(err)
        return
    }
    defer rows.Close()

    fmt.Println("... Parsing query results") 
    var tableName string
    for rows.Next() {
        rows.Scan(&tableName)
        fmt.Println(tableName)
    }

   <code to close the connection - didn't include to save space>
}

As you can see this can add additional lines of code and corresponding debugging.

With the sqlx golang package, we can use their functionality to assign the query results to a struct. This simplifies the coding. The above code becomes the following:

import (
    "fmt"
    "time"
    "database/sql"
    godror "github.com/godror/godror"
    "github.com/jmoiron/sqlx"
)

type TableDetails struct {
    Table       string 'db:"TABLE_NAME"'
    Tablespace  string 'db:"TABLESPACE_NAME"'
}

func main(){
    username := <username>; 
    password := <password>;
    host := <host>:<port>;
    database := <database name>;

   <code to create the connection - didn't include to save space - this time connect using sqlx>

    // select all the rows and load into the struct, in one step
    dbQuery := "select table_name, tablespace_name from user_tables where table_name not like 'DM$%' and table_name not like 'ODMR$%'"
    table_rec := []TableDetails{}
    db.Select(&tanle_rec, dbQuery)

    // load each row separately
    table_rec := []TableDetails{}
    rows, err := db.Queryx(dbQuery)
    for rows.next() {
           // loads the current row into the struct
        err := rows.StructScan(&table_rec)
        fmt.Printf("%+v\n", table_rec)
    }

  <code to close the connection - didn't include to save space>
}

 

 

GoLang: Links to blog posts – working with Oracle Database

Posted on Updated on

This post is to serve as a link to my other blog posts on using GoLang and connecting to/working with data from an Oracle Database.

 

Connecting Go Lang to Oracle Database

The database driver/library got renamed. The following post goes through how to updated to new name.

GoLang: Oracle driver/library renamed to : godror

GoLang: Querying records from Oracle Database using goracle

GoLang: Inserting records into Oracle Database using goracle

Importance of setting Fetched Rows size for Database Query using Golang

GoLang – Consuming Oracle REST API from an Oracle Cloud Database)

Machine Learning with Go Lang

 

This post will be updated with new GoLang posts.

 

 

GoLang: Oracle driver/library renamed to : godror

Posted on Updated on

I’ve posted some previously about using Golang with Oracle. Connecting to an Oracle Database and processing the data.

Golang is very very fast and efficient at processing this data. Much faster than a very commonly used language.

But my previous blog posts on using Golang, have used a driver/library called  goracle.  Here is the link to the blog post on setting it up and connecting to an Oracle Database.

A few months ago goracle was deprecated because of naming (trademark) issues.

But it has been renamed to godror.

The problem now is I need to go an update all the code I’ve written and change all the environment variables to reflect the new driver.

Thankfully the developer of this driver has posted the following code on Github to do this work for you. But you may still encounter some things that require manual changes. If you have only a few Golang programmes, then go ahead and do it manually.

 

You can use "sed" to change everything:

sed -i -e 's,goracle "gopkg.in/goracle.v2",godror "github.com/godror/godror",g; s,gopkg.in/goracle.v2,github.com/godror/godror,g; s/"goracle"/"godror"/g; s/goracle[.]/godror./g' $(find . -type f -name '*.go')
sed -i -e '/goracle.v2/d' go.mod

To change everything using modules:

for dn in $(fgrep -l goracle.v2 $(find . -type f -name 'go.mod') | sed -e 's,/go.mod$,,'); do 
  (cd "$dn" && git pull && sed -i -e 's,goracle "gopkg.in/goracle.v2",godror "github.com/godror/godror",g; s,gopkg.in/goracle.v2,github.com/godror/godror,g; s/"goracle"/"godror"/g; s/goracle[.]/godror./g' $(find . -type f -name '*.go') && sed -i -e '/goracle.v2/d' go.mod  && git commit -am 'goracle -> godror' && git push)
done