telling apart errors returned by database/sql

[Jochen Voss]

Dear all,

In a go program, I need to recognise when an insert into a SQL table fails

because the primary key is already present (as opposed to failing for some

other reason).  Currently I am checking whether the string value of the

error returned by the Exec method of sql.DB equals "column ... is not unique".

My questions:

- Is there a better way of checking for failure due to duplicate keys?

- Is the error string guaranteed to be independent of the

  database backend?

Many thanks,

Jochen

[Andy Balholm]

The errors are specific to the database backend. At least some backends define their own error type; for example http://godoc.org/github.com/lib/pq#PGError . So use a type assertion or type switch on the error; if it is the backend's database error type, check to see if it is the specific kind of error that you're looking for.

[Jochen Voss]

Dear Andy,

Many thanks for your answer.

On Sunday, 9 June 2013 01:17:06 UTC+2, Andy Balholm wrote:

The errors are specific to the database backend. At least some backends define their own error type; for example http://godoc.org/github.com/lib/pq#PGError . So use a type assertion or type switch on the error; if it is the backend's database error type, check to see if it is the specific kind of error that you're looking for.

This sounds painful!  Are libraries using sql databases then forced to be backend specific (if they do error handling)?

What I'm trying to do is to implement an account system, which stores user information in a database.  When somebody tries to add a new account, I need to tell apart duplicate user names from other problems (like the DB server being down, the table not existing, etc.)  It would be great if there was a backend-independent way of doing this.

Many thanks,

Jochen

[Dougx]

All database code using database/sql has to be database specific, unfortunately.

[Julien Schmidt]

In such a case I would make another query to check for an existing account with that id before even trying to insert the new account data.

Something like this should work:

var duplicate bool

err := db.QueryRow("SELECT 1 FROM users WHERE id = ? LIMIT 1", id).Scan(&duplicate)

[Julien Schmidt]

Giving this another thought, this would be an race condition.

But the chance might be very low that someone other registers another account with this id in the meantime. I think I would risk to let the registration fail in such a case.

[Arne Hormann]

How about a driver specific error with the error code as a field?

It can be used with a type assertion if the driver is imported without underscore.

But we'd first have to make sure the errors returned by the database are not wrapped.

[Julien Schmidt]

On Tuesday, June 11, 2013 3:01:41 PM UTC+2, dja...@gmail.com wrote:

insert into table ( select key, val1, val2  /* from dual if databse is oracle*/   where  not exists  ( select * from table where keyField  != key ))

Almost.

This should work (MySQL syntax).

package main

import (
"database/sql"
_ "github.com/go-sql-driver/mysql"
)

func main() {
db, err := sql.Open("mysql", "user:pass@/dbname")
if err != nil {
panic(err.Error())
}
defer db.Close()

key := 42
value := "Username"
res, err := db.Exec(`INSERT INTO foo SELECT ?, ? FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM foo WHERE id=?)`, key, value, key)
if err != nil {
println("Exec err:", err.Error())
} else {
rc, _ := res.RowsAffected()
if rc != 1 {
println("Duplicate!")
} else {
println("tuple inserted")
}
}

} 

"FROM DUAL" is not necessary in all DBMS, it is just a pseudo-table for those DBMS which always require a reference table in SELECT statements with WHERE conditions. 

[John Nagle]

There's a standardized set of SQLSTATE codes for SQL
systems. Postgres and IBM DB2 use them directly.
That's what the Go SQL interface should be returning
on an error. MySQL has different codes, but there's a translation
table at

http://dev.mysql.com/doc/refman/5.7/en/connector-j-reference-error-sqlstates.html

SQLite also has its own set of codes, and those too
should be translated.

The "error" type returned by SQL packages should have
a Sqlstate() function to return the system-independent error
code, and perhaps a function to return the system-dependent
error code. Losing that information is unacceptable for
any serious database work.

John Nagle

[Lars Seipel]

On Tue, Jun 11, 2013 at 05:29:45PM -0700, John Nagle wrote:
> The "error" type returned by SQL packages should have
> a Sqlstate() function to return the system-independent error

I like the idea.

[Jochen Voss]

Dear John,

On Wednesday, 12 June 2013 01:29:45 UTC+1, John Nagle wrote:

     The "error" type returned by SQL packages should have
a Sqlstate() function to return the system-independent error
code, and perhaps a function to return the system-dependent
error code.

If the SQL package was changed to behave like you suggest, this

would solve my problem in an elegant way.

 

Losing that information is unacceptable for
any serious database work.

I agree!

Many thanks,

Jochen

 

[edmund...@gmail.com]

This is what I expected to see and it is dissapointing to see it is not how it is implemented even 3 years after your post.

[Zach]

The `pq.PGError` class appears to be deprecated and it's now suggested to use `pq.Error`. Here's a more concrete example of an insert failing due to a constraint violation (on conflict clause...) 

if err, ok := err.(*pq.Error); ok && err.Code.Class().Name() == "integrity_constraint_violation" {
     ...
}