I am running a query through Go using
stmt, err := db.Prepare(sqlstring)
if err != nil{
log.Fatal(err)
}
defer stmt.Close()
this is the sql string with ? being '安'
select
k_ele.value as kanji, id as k_ele_id,
NULL as r_ele_id, NULL as r_ele_val,
NULL as gloss_id, NULL as gloss_val
from k_ele where value like ?
UNION ALL
select
k_ele.value as kanji, NULL as k_ele_id,
r_ele.id as r_ele_id, r_ele.value as r_ele_val,
NULL as gloss_id, NULL as gloss_val
from
r_ele, k_ele where k_ele.value like ? and k_ele.fk = r_ele.fk
UNION ALL
select
k_ele.value as kanji, NULL as k_ele_id,
NULL as r_ele_id, NULL as r_ele_val,
gloss.id as gloss_id, gloss.value as gloss_val
from
sense, gloss, k_ele
where
k_ele.value like ? and
k_ele.fk = sense.fk and
gloss.fk = sense.id;
This query can be used fine through sqlite3 on console and DB Browser for SQLite and returns 8 rows and even the for loop in the go code loops 8 times.
The problem is that during
rows.Scan()
none of the column values are being stored in the variables except kanji which appears in every row (since it is in every row) but nothing but kanji is printed out in log.Println().
rows, err := stmt.Query(parameter,parameter,parameter)
if err != nil {
log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
var kanji, r_ele_val, gloss_val string
var k_ele_id, r_ele_id, gloss_id int
rows.Scan(&kanji,&k_ele_id,&r_ele_id,&r_ele_val,&gloss_id,&gloss_val)
log.Println(kanji,k_ele_id, r_ele_id,r_ele_val,gloss_id,gloss_val)
}
Here is the output (It is ? because my cmd can't print the japanese characters (if the ? is sent to my browser, it will properly show the character)
2015/02/11 22:18:42 ? 105921 0 0
2015/02/11 22:18:42 ? 0 0 0
2015/02/11 22:18:43 ? 0 0 0
2015/02/11 22:18:43 ? 0 0 0
2015/02/11 22:18:43 ? 0 0 0
2015/02/11 22:18:43 ? 0 0 0
2015/02/11 22:18:43 ? 0 0 0
2015/02/11 22:18:43 ? 0 0 0
The strange thing is if I change the order of the columns for example putting gloss_id, gloss_val first in the select statement. Every variable gets 0 stored, even kanji before a gloss_id has a value. (These are the same sets of data)
2015/02/11 22:23:12 0 0 0
2015/02/11 22:23:12 0 0 0
2015/02/11 22:23:13 ? 0 0 197326 cheap
2015/02/11 22:23:13 ? 0 0 197327 rash
2015/02/11 22:23:13 ? 0 0 197328 thoughtless
2015/02/11 22:23:13 ? 0 0 197329 careless
2015/02/11 22:23:13 ? 0 0 197330 indiscreet
2015/02/11 22:23:13 ? 0 0 197331 frivolous
The sql works perfectly fine in sqlite3, the Golang for loop runs the correct number of times as well. Here is what the data should look like.
I'm not sure if this is a bug or is there something wrong with what I'm doing? I suspect it is a problem with UNION ALL but I'm not sure how to get this working.
EDIT: I made rows.Scan return err this time as well as printout the columns
err := rows.Scan(&kanji,&k_ele_id,&r_ele_id,&r_ele_val,&gloss_id,&gloss_val)
if err != nil {
log.Print("rows.Column(): ")
log.Println(rows.Columns())
log.Println(kanji,k_ele_id, r_ele_id,r_ele_val,gloss_id,gloss_val)
log.Fatal(err)
}
2015/02/12 22:38:56 rows.Column():
2015/02/12 22:38:56 [kanji k_ele_id r_ele_id r_ele_val gloss_id gloss_val] <nil>
2015/02/12 22:38:56 ? 105921 0 0
2015/02/12 22:38:56 sql: Scan error on column index 2: converting string "<nil>"
to a int: strconv.ParseInt: parsing "<nil>": invalid syntax
The error message says that there is an attempt to convert string to int in column index 2 (r_ele_id) but isn't r_ele_id an int already (as can be seen in the picture of my schema)? Because of the need to use UNION ALL for this query there are going to be NULLs in every row, I believe I need to use type NullInt64 instead of int for the *id types. Will try this now and report back
