1 files changed, 82 insertions, 0 deletions
diff --git a/served/words/debugging-my-sql-query.html b/served/words/debugging-my-sql-query.html
new file mode 100644
index 0000000..e9f5643
--- /dev/null
+++ b/served/words/debugging-my-sql-query.html
@@ -0,0 +1,82 @@
+---
+template=post
+title=Debugging My SQL Query
+style=/styles/post.css
+style=writing.css
+
+published=2024-11-13 7:24pm
+
+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.
\ No newline at end of file