Selecting a blob field returns empty result set

[yassine-y]

If you're having a generation problem please answer these questions before submitting your issue. Thanks!

What version of SQLBoiler are you using (sqlboiler --version)?

SQLBoiler v2.5.0 I modified the core to AllowNativePasswords for MySql.

If this happened at generation time what was the full SQLBoiler command you used to generate your models? (if not applicable leave blank)

If this happened at runtime what code produced the issue? (if not applicable leave blank)

 /* one of the columns of the User table is "photo" which is a blob, and when it's removed it works. when selected on its own or * it returns an empty result set.*/
   users, err := models.Users(DB.GetMainDB()).All() 
if err != nil {
    http.Error(w, err.Error(), 500)
    return
}
w.WriteHeader(http.StatusOK)
json.NewEncoder(w).Encode(users)

What is the output of the command above with the -d flag added to it? (Provided you are comfortable sharing this, it contains a blueprint of your schema)

Please provide a relevant database schema so we can replicate your issue (Provided you are comfortable sharing this)

CREATE TABLE `users` (
  `id` tinyint(11) UNSIGNED NOT NULL,
  `name` varchar(80) NOT NULL,
  `photo` blob,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `users`
  ADD PRIMARY KEY (`id`);

tbh, any table with a blob didn't work so doesn't really matter the schema, I remember I had a similar problem with Php long time ago and I resolved it by using column binding, thus values are bound and one row at a time, but I don't know if that can be done with SQLboiler, or how to get around it in a cleaner way.

Further information. What did you do, what did you expect?

Expected it to return some rows, the table does return all rows if the "photo" column is omitted, while if it's selected or * it returns an empty result set.

[yassine-y]

UPDATE: changing the model generated by sqlboiler from Null.Bytes to string makes it work changed: Image Null.Bytes boil:"image" json:"image,omitempty" toml:"image" yaml:"image,omitempty" to: Image string boil:"image" json:"image,omitempty" toml:"image" yaml:"image,omitempty"

Anything that can be done to make this fixed for all schema, without having to change the automatically generated code?

[aarondl]

Not for your version, no. In v3 you can use this feature: https://github.com/volatiletech/sqlboiler#types

Though I'm kind of confused why this is a problem. Does mysql seriously not parse blob into bytes? Or is there some bug with the nullbytes type itself maybe?

I THINK the nativepassword stuff was fixed by this commit btw: 1790a25bc094b44c4c6ba523487592d05b0c890c

[aarondl]

Stale.