GoLang
GoLang : Using sqlx for record mapping into Structs
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
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)
This post will be updated with new GoLang posts.
GoLang: Oracle driver/library renamed to : godror
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
GoLang – Consuming Oracle REST API from an Oracle Cloud Database)
Does anyone write code to access data in a database anymore, and by code I mean SQL? The answer to this question is ‘It Depends’, just like everything in IT.
Using REST APIs is very common for accessing processing data with a Database. From using an API to retrieve data, to using a slightly different API to insert data, and using other typical REST functions to perform your typical CRUD operations. Using REST APIs allows developers to focus on write efficient applications in a particular application, instead of having to swap between their programming language and SQL. In later cases most developers are not expert SQL developer or know how to work efficiently with the data. Therefore leave the SQL and procedural coding to those who are good at that, and then expose the data and their code via REST APIs. The end result is efficient SQL and Database coding, and efficient application coding. This is a win-win for everyone.
I’ve written before about creating REST APIs in an Oracle Cloud Database (DBaaS and Autonomous). In these writings I’ve shown how to use the in-database machine learning features and to use REST APIs to create an interface to the Machine Learning models. These models can be used to to score new data, making a machine learning prediction. The data being used for the prediction doesn’t have to exist in the database, instead the database is being used as a machine learning scoring engine, accessed using a REST API.
In that article I showed how easy it was to use the in-database machine model using Python.
Python has a huge fan and user base, but some of the challenges with Python is with performance, as it is an interrupted language. Don’t get be wrong on this, as lots of work has gone into making Python more efficient. But in some scenarios it just isn’t fast enough. In does scenarios people will switch into using other quicker to execute languages such as C, C++, Java and GoLang.
Here is the GoLang code to call the in-database machine learning model and process the returned data.
import ( "bytes" "encoding/json" "fmt" "io/ioutil" "net/http" "os" ) func main() { fmt.Println("---------------------------------------------------") fmt.Println("Starting Demo - Calling Oracle in-database ML Model") fmt.Println("") // Define variables for REST API and parameter for first prediction rest_api = "<full REST API>" // This wine is Bad a_country := "Portugal" a_province := "Douro" a_variety := "Portuguese Red" a_price := "30" // call the REST API adding in the parameters response, err := http.Get(rest_api +"/"+ a_country +"/"+ a_province +"/"+ a_variety +"/"+ a_price) if err != nil { // an error has occurred. Exit fmt.Printf("The HTTP request failed with error :: %s\n", err) os.Exit(1) } else { // we got data! Now extract it and print to screen responseData, _ := ioutil.ReadAll(response.Body) fmt.Println(string(responseData)) } response.Body.Close() // Lets do call it again with a different set of parameters // This wine is Good - same details except the price is different a_price := "31" // call the REST API adding in the parameters response, err := http.Get(rest_api +"/"+ a_country +"/"+ a_province +"/"+ a_variety +"/"+ a_price) if err != nil { // an error has occurred. Exit fmt.Printf("The HTTP request failed with error :: %s\n", err) os.Exit(1) } else { responseData, _ := ioutil.ReadAll(response.Body) fmt.Println(string(responseData)) } defer response.Body.Close() // All done! fmt.Println("") fmt.Println("...Finished Demo ...") fmt.Println("---------------------------------------------------") }