sqlite error

[groupie]

I have just done all updates available on the weekly and go-sqlite3.

When having an empty text field in a database table, where the value is
NULL, I get

ERR: sql: Scan error on column index 2: unsupported driver -> Scan pair:
<nil> -> *string

I was hoping from the changelogs that this was fixed in this weekly
snapshot, but apparently still getting it. Does someone have a solution?

At the moment, I just would do "update table set textfield = ''" :)

Cheers,
C.

[Clemens Perz]

Got it! Use []byte as type for that field and you get a byte array
without error when the original datafield is NULL.

[Brad Fitzpatrick]

I suppose we could make a NULL -> string convert to an empty string.  If a user really cared about NULL vs "" they could Scan into an sql.NullString.

Could you file a bug about this?

[Andy Balholm]

+1 from me

There aren't a lot of situations where the distinction between an empty string and NULL would matter.

In programming with PostgreSQL, I've used an API where every value is a string, and NULL is an empty string. It seemed to work fine.

[Mark Severson]

We quite frequently run into situations at work where we end up
needing to know the distinction between a NULL value and "" (or more
often, NULL and 0).

I was porting one of our tools to Go, using an database/sql ODBC
driver I posted about a week or two ago, and ran into a situation
where I needed to know if a NULL value was present. At the time, my
weekly build didn't contain anything but sql.NullableString, so I
copied it into the nullables I needed. I couldn't help but think that
it seemed like too much work and left me wondering why just passing
the address of a *string/*int wouldn't work.

Perhaps there's just something I'm not understanding that prevents
this from working the way I thought it should, if so, please help me
understand!

An example (taken from my original post):
rows, err := db.Query("SELECT t.id, rel.id, rel.name FROM t LEFT
OUTER JOIN rel ...")
...
for rows.Next() {
var id int
var relID *int
var relName string
err = rows.Scan(&id, &relID, &relName) // fails because it
doesn't like the **int type of the &relID
}


original post: http://groups.google.com/group/golang-nuts/browse_thread/thread/cc90026bde3ce607/

[Andy Balholm]

We quite frequently run into situations at work where we end up 
needing to know the distinction between a NULL value and "" (or more 
often, NULL and 0). 

To me, the distinction between NULL and 0 seems much more likely to be important than between NULL and "". There aren't many situations where the empty string would be a useful piece of information, but 0 is a common number that often conveys real information.

In the PostgreSQL API I was referring to, a NULL number is returned as ""; a zero is returned as "0". It's not very good for performance, but it is easy to use. 

[Andy Balholm]

I've filed a bug:

Issue 2814 - go - database/sql: convert NULL to empty string - The Go Programming Language - Google Project Hosting

[groupie]

Sorry, been away for a few days. didnt get to file the bug. Anyway, I
agree with you, also because "" is the default "not set" value for a
string member in a struct. So everybody should know how to handle that.
And sql.NullString is a nice way out in situations, where the default is
not enough.

[akaariai]

On Jan 31, 2:25 am, groupie <stopmakingse...@gmx.de> wrote:
> Sorry, been away for a few days. didnt get to file the bug. Anyway, I
> agree with you, also because "" is the default "not set" value for a
> string member in a struct. So everybody should know how to handle that.
> And sql.NullString is a nice way out in situations, where the default is
> not enough.

The important distinction would be that WHERE field IS NULL gives
different results depending if the field contains NULL or ''. If you
decide to treat '' and NULL as equal in Go, there could be some
problems for users because the backend doesn't treat them as similar.
Depending on the backend, in Oracle '' is same as NULL.

In practice the difference might not be important, but there sure is a
difference. But then again if you treat '' and NULL as not equal, they
in fact are equal in Oracle... So, '' and NULL as equal should be a
good abstraction, if you don't want to treat this differently
depending on backend used...

- Anssi Kääriäinen

[Kyle Lemons]

You could probably transform equality comparison of a zero string into (col = '' OR col IS NULL) or something similarly clever.

[groupie]

I agree, there is a destinction. But lets see the facts. In go, this

var test string = nil

gives you an error - you will set string to "", which is the default
value for an uninitialsed string. So there is no way to pick up a NULL
as nil when passing this var to the Scan func.

On the other hand, there is sql.NullString defined as

type NullString struct {
String string
Valid bool // Valid is true if String is not NULL
}

So if NULL matters to you, you will use this as the type to pass to
Scan. Mission accomplished :)

And for building queries from types in structs, that one will also make
the difference.

There may be a point for blobs, where it seems you could stuff text into
string and binary data into []byte. Perhaps there is a need for a
NullBlob type? Dunno, most if the time if its empty, its empty, right? :))

> - Anssi K��ri�inen
>

[Mark Severson]

I agree that it is a very nice feature to set the values to their
defaults if a NULL is returned during the scan.

I also agree that sql.NullString (and related) help to fill the void
where it IS important to know the difference between a NULL value and
the default value for a type.

But wouldn't it also be nice to simply pass the address of a pointer
type (see example below) and have database/sql populate the pointer
with nil (if NULL) or the actual value if one was provided? As stated
before, there may be some underlying reason why this is a horrible
idea, but I cannot for the life of me think of what it might be?

--------
var id *int
rows.Scan(&id)

if id == nil {
// NULL value returned
} else {
// Actual value returned
}
--------

[Brad Fitzpatrick]

On Wed, Feb 1, 2012 at 11:16 PM, Mark Severson <miqu...@gmail.com> wrote:

I agree that it is a very nice feature to set the values to their
defaults if a NULL is returned during the scan.

I also agree that sql.NullString (and related) help to fill the void
where it IS important to know the difference between a NULL value and
the default value for a type.

But wouldn't it also be nice to simply pass the address of a pointer
type (see example below) and have database/sql populate the pointer
with nil (if NULL) or the actual value if one was provided? As stated
before, there may be some underlying reason why this is a horrible
idea, but I cannot for the life of me think of what it might be?

--------
var id *int
rows.Scan(&id)

if id == nil {
   // NULL value returned
} else {
   // Actual value returned
}
--------

It's because Go generally tries to avoid using nil values as sentinels.

Go style prefers a signature like:

func Foo() (foo *Foo, ok bool)

or

func Foo() (*Foo, error)

over something like:

// Foo returns a *Foo, unless it couldn't, in which case the return value is nil.

func Foo() *Foo

Hence the database/sql package not encouraging such a style.

It's true that the goprotobuf package does do such a thing, but nobody is a huge fan of it.

[roger peppe]

it's not the only one. encoding/xml, encoding/json and encoding/gob
(for structs only)
all support this style. if you want a nullable X, a pointer to X seems
like a good
way to represent it.

defining NullString seems unnecessary.

[roger peppe]

On 2 February 2012 09:11, roger peppe <rogp...@gmail.com> wrote:
> defining NullString seems unnecessary.

actually, i take that back - NullString is nice because it avoids an allocation.

but i can see a reasonable argument for allowing pointer values too.

[akaariai]

On Feb 2, 11:13 am, roger peppe <rogpe...@gmail.com> wrote:

> On 2 February 2012 09:11, roger peppe <rogpe...@gmail.com> wrote:
>
> > defining NullString seems unnecessary.
>
> actually, i take that back - NullString is nice because it avoids an allocation.
>
> but i can see a reasonable argument for allowing pointer values too.

One reason why it is nice to have "is null" separate from the value is
that in SQL boolean logic is tri-valued. The common mistake this
causes is that if you do
WHERE x = foo
you would think that you get the complement of that set by
WHERE NOT x = foo
but that is not true - there is still the unknown (or NULL) set.

So, it is nice that Go supports this tri-valued boolean logic by
having nullable SQL types. You can do something like this (sorry for
the Pythonic example):
if x.is_null:
...
elif x = 'foo':
...
else:
...

That actually matches the semantics of SQL. Of course, you can do that
with pointers, too, but the SQL nullable types make it much harder to
forget this.

- Anssi

[roger peppe]

actually i think it's the other way around. with the NullBool type,
it's very easy to do:

if !x.Bool {
}

which might be incorrect.
with the pointer version:

if !*x {
}

it'll panic when it's null, emphatically demonstrating your mistake.

i think there are cases for both uses.

if the sql package were to support named rows, it might do that
by allowing a pointer to a struct as the first argument to Scan.
in which case supporting pointers would make it much more easily
interchangeable with the other marshalling packages.

type Record struct {
Name string
Age *int `xml:"omit_empty"`
}

func convertToXML(r *sql.Rows, enc xml.Encoder) {
for rows.Next() {
var rec Record
rows.Scan(&rec)
enc.Encode(&rec)
}
}

[Mark Severson]

Sorry it's taken me so long to respond...

I am very much a fan of the way Go returns errors, so I wasn't advocating the use of nil as sentinel.

Being that the Scan functions return any error that may have occurred, doesn't that mean that any nil value returned should simply be treated as a value rather than a sentinel (as long as the type being provided to the Scan function is a pointer to a pointer, e.g. **Type)?

----

Furthermore, while the use of the nullable types (e.g. NullString) promotes slightly more explicit NULL checking, doesn't this leave a gap in one of the original goals of the package: "Be flexible with type conversions, but be paranoid about silent truncation or other loss of precision."

Allow me to explain what I mean. If my understanding is correct, the sql package provides precision checking when casting to the types provided to the Scan function. Based on this assumption, wouldn't you need a NullInt8, NullInt16, and NullInt32 in addition to NullInt64 to aid in proper prevention of loss of precision? Because the NullInt64 isn't going to report a loss of precision, but would encourage an ill practice of casting the NullInt64's value directly to a uint8, for example.

I hope my I expressed my concern properly, if not, please let me know and I would be glad to try explaining it a different way!

[Mark Severson]

Brad,

I've been watching the sql package for several months and you've always had good insights here, so I would be very interested to know what your thoughts are on the points I brought up in my last post!

Especially if my understanding about the precision protection is correct. I think that alone gives a much stronger argument to allowing double-indirection parameters (**uint16, for example) in the Scan method, as opposed to the Null* types. Allowing the sql package to check to ensure no precision is lost (where as NullInt64 doesn't provide the same protection).

Thanks for listening!

[Brad Fitzpatrick]

Did you notice,

http://code.google.com/p/go/source/detail?r=0eab3b57c894

and

http://code.google.com/p/go/source/detail?r=a90ca1755aa5

?

Anything else you want?

[Mark Severson]

My apologies, I hadn't heard anything back yet and didn't think to check, sorry...

That sounds like it covers what I was looking for though, thank you!