1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
|
---
template=post
title=Debugging My SQL Query
style=/styles/post.css
style=writing.css
published=2024-11-13 7:24pm CST
description=Tiny retelling about debugging a weird SQL problem.
art=images/awoo.png
art_alt=Some("no such table: awoo")
---
working on a project i call naily <i>(or just "numbers")</i> and i had an sql problem :(
i was trying to load a page that ran an sql query. it was this:
<pre><code>SELECT creation_date, state, game, result
FROM double_dodge
WHERE player_id = ?1 AND state = ?2;</code></pre>
the error in my terminal read: <code>no such column: creation_date</code>.
so i went and looked at my create table. it looked fine! the relevant bits:
<pre><code>CREATE TABLE IF NOT EXISTS double_dodge (
...,
creation_date TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
...,
);</code></pre>
so i tried not selecting the <code>creation_date</code> column, but then it got mad at me about
the <code>state</code> column. i can assure you that <code>state</code>, too, was a real column.
i opened the database in sqlite3. like this, <code>sqlite3 onl.db</code>. after trying to do
<code>\d double_dodge</code> and it telling me that it didn't know what <code>\</code> meant,
i ran <code>.help</code> like it suggested i did.
haha, no i didn't. i went to google and searched, apparently, "sqlite cli" which pulled up the
<a href="https://sqlite.org/cli.html">Command Line Shell For SQLite</a> page. i scrolled for a
few dozen seconds and saw <code>.schema</code> which looked promising.
<pre><code>sqlite> sqlite> .schema double_dodge
CREATE TABLE double_dodge (
(lines redacted for brevity)
creation_date TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,</code></pre>
see, it <i>is</i> real. i'm not loosing it. well, i might be.
next i tried to not run that sql query at all until there was data in it. would that matter? i
didn't know, i'd never used sqlite before, but i thought it wouldn't matter. mostly because that
wouldn't make sense for it too, but also because i did something greatly similar elsewhere in
the project.
i commented the code out and was able to use the webpage to put data into the table without issue.
more evidence it was real.
i then uncommented the code and it started to panic again. and it was still poisoning the mutex that
held my database connection <i>(not great probably, the mutex, but)</i>.
does it just say that if it doesn't think the table is real? surely the table is real. we have so
much evidence it is. i changed the table name.
<pre><code>SELECT creation_date, state, game, result
FROM awoo;</code></pre>
<pre><code>Some("no such table: awoo")</code></pre>
okay, so it knows the table is real. it just doesn't think any of the columns are real.
i tried removing my where clause.
aha! it ran.
what's wrong with my where clause? let's look at it again
<pre><code>SELECT creation_date, state, game, result
FROM double_dodge
WHERE player_id = ?1 AND state = ?2</code></pre>
those are all columns that exist, even if sqlite does not believe me. oh, i'm not selecting
<code>player_id</code>. that's probably not good.
<pre><code>SELECT player_id, creation_date, state, game, result
FROM double_dodge
WHERE player_id = ?1 AND state = ?2;</code></pre>
<b><i>success :)</i></b>
i am normally good about remembering to select what i where, but i forgot this time.
i wish it gave me an error that made sense, though, as the where was for
<code>player_id</code> which was not anything it was telling me about.
|
