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

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Keith Medcalf

On Thursday, 31 October, 2019 07:17, Jeffrey Walton <[hidden email]> 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.



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Jeffrey Walton-3
On Thu, Oct 31, 2019 at 9:52 AM Keith Medcalf <[hidden email]> wrote:

> On Thursday, 31 October, 2019 07:17, Jeffrey Walton <[hidden email]> 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
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users