rows.Close and rows.Err are kind of jumbled, as documented in #61 .

At the database level, a prepared statement is bound to a single database connection.

When you execute the Stmt, it tries to use the connection. If it’s not available because it’s closed or busy doing something else, it gets another connection from the pool and re-prepares the statement with the database on another connection.

Is that any other existing connection? Or does the database create a new connection?

This special-case error should be handled specially in many circumstances.

A user was creating a transaction but then accessing the DB, and didn't realize that those statements weren't inside the transaction.

The other helpful property of transactions is to bind statements to a single connection, for things like session state, user-defined variables, temporary tables, etc. We could illustrate this.

Hi,

thanks for this resource; it's been very useful for a golang newbie like me.
In particular, it gave me the first clue about how to fix my error, that looked
like: "sql: statement expects 0 inputs; got 1".

That clue was in the fourth paragraph of your tutorial and went like "...the most
surprising [difference] can be the need to use different syntax for placeholder
parameters in prepared statements."

Here was I using the standard Java convention of "?" placeholder characters, when
after some investigation, it turns out that Oracle likes very different placeholders,
whose first character is a colon. For example;
"select * from myTable where myColumn = :a"

Since you already mention the syntax for MySQL and PostgreSQL, could
I suggest you add an example of what the Oracle syntax looks like.
I'm sure it will be useful to someone, in the future.

regards

David

First, thanks for the incredibly useful website.

I chose the word might in the title of the Issue because I personally found the website much easier to read by opening the Safari Web Inspector and using Helvetica instead of Open Sans. Looking at other examples of Open Sans on the web, it seems that there's something strange going on in this website's kerning type settings.

Obviously, feel free to close and ignore this if not useful 😄

Hi

Thanks for the excellent article. I found it quite informative with lots of little gotchas. Would it be possible for you to comment on the following

• Is it possible to work with multiple result sets
  If I have multiple select statements, then how can I ensure that I read each of the result sets individually?
• Invoking stored procedures and passing parameters to stored procedures
• If I include multiple statements in a transaction, does it perform a single batch i/o operation with the db or are multiple network calls made?

Thanks
-Rao

Using sql.NullString as a property type leaves a bad taste in the mouth when designing structs, as it seems to tightly couple SQL to models that may generally be independent of SQL.

I've found that using *string (*int, etc) is more palatable. With automatic dereferencing, it hasn't been much of an issue. I'm curious re: your take on this, and re: your thoughts on including it in the section on NULL values.

Newer versions of database/sql allow passing a context object; it would be good to mention that.

At the database level, a prepared statement is bound to a single database connection.

When you execute the Stmt, it tries to use the connection. If it’s not available because it’s closed or busy doing something else, it gets another connection from the pool and re-prepares the statement with the database on another connection.

Is that any other existing connection? Or does the database create a new connection?

Just found your site and love it -- thanks very much! I noticed your tutorial model assumes mySQL and I wondered if you'd considered using sqlite3, since that db is so pervasive (pre-installed on many platforms), it's quite simple, and there'a Go driver available. Just wondered. I think I'm going to follow through your tutorial using sqlite3 and see how far I can get.

Hello, I really appreciate the site! But, as I follow it, when I want to access some data, I can't because I don't have any. So I had to Google to see if there's an idiomatic way to insert before I could return to the tutorial. If you're amenable, I'd be happy to make a pull request adding a paragraph or so creating and inserting data in the "Retrieving Result Sets" page.

I think the header should be smaller. It takes up over half of my screen.

I just got an email from Github. Copying it below...

Hi VividCortex,

The custom domain for your GitHub Pages site VividCortex/go-database-sql-tutorial needs attention. You must take immediate corrective action to ensure that your site remains available after December 1st, 2014.

Please follow the instructions for setting up a custom domain with GitHub Pages to update your custom domain’s DNS settings to point to the proper GitHub IP addresses.

Why the change?

Nearly a year ago, we announced improvements to how we serve GitHub Pages sites. This week we’re making that change permanent by deprecating our old GitHub Pages infrastructure. If your custom domain is pointed at these legacy IPs, you’ll need to update your DNS configuration immediately to keep things running smoothly.

How long do I have to make the switch?

Starting the week of November 10th, pushing to a misconfigured site will result in a build error and you will receive an email stating that your site’s DNS is misconfigured. Your site will remain available to the public, but changes to your site will not be published until the DNS misconfiguration is resolved.

For the week of November 17th, there will be a week-long brownout for improperly configured GitHub Pages sites. If your site is pointed to a legacy IP address, you will receive a warning message that week, in place of your site’s content. Normal operation will resume at the conclusion of the brownout.

Starting December 1st, custom domains pointed to the deprecated IP addresses will no longer be served via GitHub Pages. No repository or Git data will be affected by the change.

Okay, I’m sold. What do I need to do?

Please follow the instructions for setting up a custom domain with GitHub Pages to update your custom domain’s DNS settings to point to the proper GitHub IP addresses.

Questions? We’re here to help.

Happy Publishing!

— The GitHub Pages Team

Drivers can implement some level of convenience, but ultimately the user can still be exposed to the low-level way the data is transmitted in the driver. For example, the mysql driver will do a strconv.ParseXXX to scan into integers and floats and so on, which avoids some code in your application sometimes. But it is still possible for users to see the differences in binary versus textual protocol; see for example go-sql-driver/mysql#211

The link at the end of each page is broken.
Example: index.md
The link to [Overview of Go's database/sql Package] should be:
https://github.com/VividCortex/go-database-sql-tutorial/blob/gh-pages/overview.md,
but it's
https://github.com/VividCortex/go-database-sql-tutorial/blob/gh-pages/overview.html

Hello,

The link on https://github.com/VividCortex/go-database-sql-tutorial/blob/gh-pages/index.md to "Overview of Go's database/sql Package" is broken :)

Thank you.

Martin.

https://github.com/VividCortex/go-database-sql-tutorial/blob/gh-pages/surprises.md

In the Surprises page, it is stated that the database/sql package does not support multiple result sets/statements but this does not seem to be true anymore.

There is an example on the go database/sql showing how to do multiple result sets.

Hi. Thanks for the useful tutorial.

When preparing a statement with placeholder parameters, both the official "database/sql" docs and your tutorial say to use question marks. Surprisingly, that causes PostgreSQL to spit a cryptic error. Turns out it wants syntax like VALUES($1, $2, $3) and not VALUES(?, ?, ?).

I know you explicitly narrow in on MySQL up front, but as a friendlier alternative to the official docs, it would be helpful to note that ? isn't universal.

A common problem is, that applications often reach too high concurrency at some point. This leads to too many open connections.
With the Go-MySQL-Driver this leads to Error 1040: Too many connections errors.

Some people confuse DB.SetMaxIdleConns with having control over the maximum number of connections, but unfortunately in reality you have no control over it via the database/sql package: https://code.google.com/p/go/issues/detail?id=4805

It would be great if you could add a section which covers this issue and some possible solutions. For example:

• some sort of semaphore (channels)
• using async updates / caches instead

I won't have time to prepare a PR until mid. August, so I hope someone other finds a few good words on this issue ;-)

I think a page illustrating them would be a huge benefit. We (VividCortex) recently implemented a type that does seamless encrypt/decrypt and our code is absolutely clean. What a great thing. Jason Moiron's article on these is excellent. I think I would like to adapt it, trim it down, and give him credit: http://jmoiron.net/blog/built-in-interfaces/

If there was an error that made rows.Next() return false, then it's already Close()d. The only reason to close it would be if you exited the loop for some reason.

When trying to run the sample code, it throws this error:

"cannot use vals (type []sql.RawBytes) as type []interface {} in function argument"

The code is:

    cols, err := rows.Columns()             // Get the column names; remember to check err
vals := make([]sql.RawBytes, len(cols)) // Allocate enough values
ints := make([]interface{}, len(cols))  // Make a slice of []interface{}
for i := range ints {
    vals[i] = &ints[i] // Copy references into the slice
}
for rows.Next() {
    err := rows.Scan(vals...)
    // Now you can check each element of vals for nil-ness,
    // and you can use type introspection and type assertions
    // to fetch the column into a typed variable.
}

In case it's not clear, we should warn people against a long-lived function using defer() to close results. The deferred code keeps a reference to the *sql.Rows, and that eats up memory steadily.

when work with gopkg.in/rana/ora.v3 or github.com/mattn/go-oci8
how to Working with Unknown Columns
my code
values := make([]sql.RawBytes, len(cols)) scans := make([]interface{}, len(cols)) for i := range values { scans[i] = &values[i] } results := make(map[int]map[string]string) i := 0 for query.Next() { if err := query.Scan(scans...); err != nil { fmt.Println("scan err:", err) return } row := make(map[string]string) for j, v := range values { key := cols[j] fmt.Println("xx", j, v) row[key] = string(v) } results[i] = row i++ }
and the error is :

index TIME_STAMP

scan err: sql: Scan error on column index 0: unsupported Scan, storing driver.Value type time.Time into type *sql.RawBytes

In many examples you're using log.Fatal, this will exit the program immediately with os.Exit(1) and probably isn't something you should encourage newcomers to the language to use. It would be nice if the examples just returned errors or logged them, not exited the program (which should be very unusual in go code). It also means things like defer statements you have in there wouldn't run.

So this example is misleading for example:

http://go-database-sql.org/prepared.html

because the defer is never called, since you call log.Fatal which calls os.Exit.

The comment about Go 1.4 will probably cause confusion now too as it is really out of date, probably better just to remove that now that the fix is in, or demote it to a footnote?

Your site is using Maruku, the default Markdown interpreter. Maruku is now obsolete and may cause builds to fail for sites with invalid Markdown or HTML. See https://help.github.com/articles/migrating-your-pages-site-from-maruku for more information on upgrading to a newer Markdown interpreter.

For information on troubleshooting Jekyll see:

https://help.github.com/articles/using-jekyll-with-pages#troubleshooting

At least with the go-sql-drivers/mysql driver, I see that I can kill database connections and continue to call stmt.Query() without any trouble. Either database/sql or the driver is catching the error from the closed connection, opening a new one, preparing a new statement, and retrying the Query() without returning an error to my code.

Which one is doing the magic? I think this should be documented.

Per mysql lock tables

LOCK TABLES is not transaction-safe

And

UNLOCK TABLES implicitly commits any active transaction

Recommended to do:

SET autocommit=0;
LOCK TABLES t1 WRITE, t2 READ, ...;
... do something with tables t1 and t2 here ...
COMMIT;
UNLOCK TABLES;

But per golang transactions doc

Its recommended to:

End the transaction with (Commit, Rollback)

Hence the Mysql recommendation contradicts the golang recommendation. I haven't found proper guidance on how to use transactions with locking in golang.

Do you have guidance (with actual example) of how to do transactions with lock tables?

Would that guidance be don't use transactions but use a dedicated connections?

A doc on this would be very helpful.

FWIW, my use case a standard go webapp running as a pool behind a LB. Any webapp can write to the DB, I want to ensure consistency when writing.

Seems quite standard but I'm having a surprisingly hard time finding examples of this. Any help/guidance would be much appreciated.

I can't find nav in the website.
Part of the HTML code:

<div class="container">

   <div class="row">
   <div class="span3">
   <div class="leftnav">
   <ul class="nav nav-list">
  
 </ul>
   <hr />

What license is this project released under? Will it be okay if I incorporated content form here verbatim in an open source book I am writing?

What if the database server is set to autocommit=0? Then every new connection is in a transaction automatically. Seems like this will cause problems. We should explain whether this is true or not.

First off all it's a great guide to get started with the database/sql package. You start with the import of drivers, creating a connection and in the next step you fetch data. But before you do that it would be good if you describe how to set up tables in a database.

There's apparently an effort underway to improve the database/sql documentation and provide examples.
Issue: https://groups.google.com/forum/#!topic/golang-dev/bUTGb77jYFs
Page: https://code.google.com/p/go-wiki/wiki/SQLInterface

This is a very, very vague issue, so please close if it's not helpful.

I think it would be easier to navigate through the pages if each page had "previous" and "next" links at the bottom.

Currently you have to go through the list and find the page where you currently are before clicking the link to the next page (the list item below).

As discussed in this thread, go-sql-driver/mysql#221, some products will not support prepared statements and using a driver with them that does support prepared statements can be confusing.

Hi!
First of all, amzing tutorial. Thank you very much.

In the Error section of the tutorial you say:

This code is still ugly. Comparing to 1045, a magic number, is a code smell. Some drivers (though not the MySQL one, for reasons that are off-topic here) provide a list of error identifiers.

Browsing through the source code of the MySQL driver you use I find that they do have exported error values.

I feel that the Go community is missing good example database/sql applications that exhibit these best practices and others in terms of how to design a larger application that needs to also interact with a database.

It'd be nice if this guide could link to those apps so programmers can see these techniques put to use. (Unfortunately I can't really offer any suggestions, I've not found any open source apps yet that are good examples.)

I've seen "too many prepared statements" errors in MySQL when I didn't close a statement after using it (and created new statements in a loop). We need to make it clear that statements shouldn't be created and closed a lot if they're going to be reused -- but they equally shouldn't be left open forever.

https://github.com/VividCortex/go-database-sql-tutorial/blob/gh-pages/overview.html is 404

In the MySQL driver I like, the idiomatic code looks like

      if mysqlErr, ok := err.(*mysql.MySQLError); ok {
         // ERROR 1045 (28000): Access denied for user 'foo'@'::1' (using password: NO)
         if mysqlErr.Number != 1045 {

This driver doesn't export error codes, for good reasons. Some drivers such as https://github.com/lib/pq do, and so the code would look slightly different. Also, VividCortex is maintaining a list of MySQL error codes here: https://github.com/VividCortex/mysqlerr

This is a pretty common FAQ on the MySQL driver's issue list. Worth documenting and explaining the variations and the available resources?

I want to translate this tutorial into Chinese. How should I organize the translated documents?
I fork this repo and add zh-CN folder into it for now, but I noticed that some css or js have broken after building.
You can visit this page for more information. https://rayhy.github.io/go-database-sql-tutorial/zh-CN/

Not sure if this is adequately covered already, but in go-sql-driver/mysql#287 someone is not aware that the queries are being run on different connections.

In your examples, you first write "defer rows.Close()" and then you are checking for an error. If I do so and there is an error in the query, I get a panic ("panic: runtime error: invalid memory address or nil pointer dereference"). So I think the order should be reversed (first error checking, then closing):

rows, err := db.Query("select id, name from users where id = ?", 1)
if err != nil {
 log.Fatal(err)
}
defer rows.Close()

BTW, thanks for the nice tutorial! I wish I had something similar when I started...

Michael

The titles on the left are veeeeeeery long.
They mirror the section headers, but each should probably be shortened to fit in one line.

Navigation titles can be changed here: _layouts/article.html - we should probably also raise the span in line 51 if we can't shorten the titles enough.

Formatting of the sidebar resides in _includes/leftnav.html, but I don't think anything has to be changed there.

In Fetching Data from the Database, the explanatory text says Notice, however, that we check the error first, and only call rows.Close() if there isn't an error, in order to avoid a runtime panic.; but this "check error and close if no error" isn't there in the code:

var (
    id int
    name string
)
rows, err := db.Query("select id, name from users where id = ?", 1)
if err != nil {
    log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
    err := rows.Scan(&id, &name)
    if err != nil {
        log.Fatal(err)
    }
    log.Println(id, name)
}
err = rows.Err()
if err != nil {
    log.Fatal(err)
}

I see only a defer rows.Close(). Nowhere else is rows.Close() called.

Could you help us out by adding a linkable section with frequently encountered problems to the database tutorial?
I'd really love to have a bunch of short linkable answers ready when closing issues for the mysql driver - but I lack the experience in technical writing (and am not a native speaker).

What I have in mind is a way to quickly answer and close issues like
"SELECT ? does not work with a slice".