score:0

Accepted answer

Figured it out! Turns out, it was the stored procedure causing all the trouble.

In the procedure I check to see if a ngram exists by doing a dynamic SQL query (since the table name is passed in) that stores a value in @ngramId, which is a session variable. I store it in @ngramId and not into ngramId (a procedure output parameter) because prepared statements can only select into session variables (or so I've been told by an error when I originally created the procedure). Next I set the value of @ngramId to ngramId and check if ngramId is null to determine if the ngram exists in the table; if null, the ngram is inserted into the table and ngramId is set to the last inserted id.

The problem with this is that because @ngramId is a session variable and because I used the same database connection for all procedures calls, the value of @ngramId persisted between calls. So for example, if I made a call with the ngram "I", and it was found in the database with id 1, @ngramId now had the value of 1. Next if I tried to insert another ngram that did not exist in the table, the dynamic select statement did not return anything so the value of @ngramId remained as 1. Since the output parameter ngramId is populated with the value of @ngramId and now it was no longer NULL, it bypassed the if statement that inserted the ngram in the database and returned the id of the last ngram found in the table, resulting in the seeming caching of ngram ids.

The solution to this was to add the following line as the very first statement in the procedure:

 SET @ngramId = NULL;

Which resets the value of @ngramId between calls to the procedure over the same session.


Related Query

More Query from same tag