[sqlite-dev] Error 25: column index out of range when binding for a DATETIME

[Jeffrey Walton]

Hi Everyone,

I'm having trouble binding a parameter for a DELETE. I am trying to
delete records older than 120 days. Below, I have a table with a
column dtime of type DATETIME. days is a dirty parameter specified by
the user.

/* negative for days in the past */
int days = 120;
days = -days;

const char DELETE_STMT[] = "DELETE from blacklist " \
"WHERE dtime < datetime('now', '? days');";

rc = sqlite3_prepare_v2(conn, DELETE_STMT, -1, &stmt, NULL);
if (!SQLITE_SUCCESS(rc))
{
log_error("Failed to prepare query, Error (%d): %s\n",
rc, sqlite3_errmsg(conn));
errs++; goto finish;
}

rc = sqlite3_bind_int(stmt, 1, days);
if (!SQLITE_SUCCESS(rc))
{
log_error("Failed to bind days, Error (%d): %s\n",
rc, sqlite3_errmsg(conn));
errs++; goto finish;
}

The bind fails with:

Error: Failed to bind days, Error (25): column index out of range

Knob turning has not been helpful. Googling is not turning up useful hits.

How do I fix this?

Thanks in advance.
_______________________________________________
sqlite-dev mailing list
sqlit...@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-dev

[Keith Medcalf]

On Thursday, 31 October, 2019 07:17, Jeffrey Walton <nolo...@gmail.com> wrote:

First, your question should go to the sqlite-users mailing list, not the developers mailing list.

>I'm having trouble binding a parameter for a DELETE. I am trying to
>delete records older than 120 days. Below, I have a table with a
>column dtime of type DATETIME. days is a dirty parameter specified by
>the user.

See https://sqlite.org/datatype3.html

> /* negative for days in the past */
> int days = 120;
> days = -days;

> const char DELETE_STMT[] = "DELETE from blacklist " \
> "WHERE dtime < datetime('now', '? days');";

This statement contains no parameter. You have a string constant with a ? character inside the string. Parameters go outside of constants, not inside them. Perhaps try something like this (which will work only if days is negative):

const char DELETE_STMT[] = "DELETE from blacklist " \
"WHERE dtime < datetime('now', ? || ' days');";

You would not expect this to work would you:

int days = -120;
printf("Days = days\n");

> rc = sqlite3_prepare_v2(conn, DELETE_STMT, -1, &stmt, NULL);
> if (!SQLITE_SUCCESS(rc))
> {
> log_error("Failed to prepare query, Error (%d): %s\n",
> rc, sqlite3_errmsg(conn));
> errs++; goto finish;
> }
>
> rc = sqlite3_bind_int(stmt, 1, days);
> if (!SQLITE_SUCCESS(rc))
> {
> log_error("Failed to bind days, Error (%d): %s\n",
> rc, sqlite3_errmsg(conn));
> errs++; goto finish;
> }
>

--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.

[Jeffrey Walton]

On Thu, Oct 31, 2019 at 9:52 AM Keith Medcalf <kmed...@dessus.com> wrote:
> On Thursday, 31 October, 2019 07:17, Jeffrey Walton <nolo...@gmail.com> wrote:

> ...

> > /* negative for days in the past */
> > int days = 120;
> > days = -days;
>
> > const char DELETE_STMT[] = "DELETE from blacklist " \
> > "WHERE dtime < datetime('now', '? days');";
>
> This statement contains no parameter. You have a string constant with a ? character inside the string. Parameters go outside of constants, not inside them. Perhaps try something like this (which will work only if days is negative):
>
> const char DELETE_STMT[] = "DELETE from blacklist " \
> "WHERE dtime < datetime('now', ? || ' days');";

Perfect, thanks.

That information may make good reading at
https://www.sqlite.org/lang_datefunc.html . I would never have figured
out the syntax on my own.

Jeff

[Warren Young]

On Oct 31, 2019, at 7:57 AM, Jeffrey Walton <nolo...@gmail.com> wrote:
>
> That information may make good reading at
> https://www.sqlite.org/lang_datefunc.html . I would never have figured
> out the syntax on my own.

This is a matter of SQLite syntax parsing, not something specific to its date and time functions.

As far as I can tell, the SQLite docs don’t say how quoting and parameter specs interact. I’d expect it to be covered here:

https://sqlite.org/lang_expr.html#varparam