SQLite Forum

Returning expr not working with sqlite3_exec() in C code
Login

Returning expr not working with sqlite3_exec() in C code

(1) By lokakit (loki1993) on 2021-06-11 09:48:35 [link] [source]

Returning expr not working with sqlite3_exec() gives below error in C code , but works fine on sqlite3 terminal

sqlite> insert INTO mqtt6 values ('abcd',1623066863471168894,1623066863471,119) RETURNING (SELECT sum(size_t) from mqtt6)  ;
76041
sqlite> 

but when i try to run the same command from C code, i get error:

char * str="abcd";
sprintf(sql,"insert OR REPLACE INTO mqtt6 values. 
('%s',%llu,%llu,%lu);",str,ts,ts,strlen(str)+8+19+4); 
rc = sqlite3_exec(db, sql, 0, 0, &err_msg);

i get error as below:

loki@loki-01 ~/sqlite_dbstat/simple_sqlite - $ ./a.out

bytes_written=0  insert OR REPLACE INTO mqtt6 values ('abcd',1623066863471168258,1623066863471168258,119) RETURNING (SELECT sum(size_t) from mqtt6);
**SQL error: near "RETURNING": syntax error**

(2) By Keith Medcalf (kmedcalf) on 2021-06-11 10:32:03 in reply to 1 [link] [source]

That code fragment cannot possibly result in the text shown. The variable sql will point to a text string that contains:

insert or REPLACE INTO mqtt6 values.('abcd',something,something,35)

which looks absolutely nothing like the output you have provided.

What is the version of SQLite3 that you are linking with? Perhaps it does not understand the "returning" clause. (The sqlite3 CLI is an SQLite3 Application and does not have to be linked against the same version of SQLite3 as a.out is being linked to).

(3.1) By lokakit (loki1993) on 2021-06-11 11:14:58 edited from 3.0 in reply to 2 [link] [source]

Deleted

(4.1) By lokakit (loki1993) on 2021-06-11 11:16:04 edited from 4.0 in reply to 3.0 [link] [source]

complete C code:

#include <sqlite3.h>
#include <stdio.h>
#include <string.h>
#define TABLE_NAME "mqtt5"
int main(void) {

        sqlite3 *db;
        char *err_msg = 0;

        int rc = sqlite3_open("test.db", &db);

        if (rc != SQLITE_OK) {

                fprintf(stderr, "Cannot open database: %s\n", sqlite3_errmsg(db));
                sqlite3_close(db);

                return 1;
        }
        //make table
        char *sql_cr = "CREATE TABLE IF NOT EXISTS mqtt6 (payload TEXT NOT NULL, timestamp INTEGER , uuid TEXT PRIMARY KEY NOT NULL,size_t INTEGER);"; 
        rc = sqlite3_exec(db, sql_cr, 0, 0, &err_msg);

        unsigned long long ts=1623066863471168257;
        unsigned long long bytes_written=0;
        char sql[1000]={'\0'};
        char * str="abcd";
        sprintf(sql,"insert  INTO mqtt6 values ('%s',%llu,%llu,%lu) RETURNING (SELECT sum(size_t) from mqtt6);",str,ts,ts,strlen(str)+8+19+4); 
        printf("bytes_written=%llu  %s\n",bytes_written,sql);

        rc = sqlite3_exec(db, sql, 0, 0, &err_msg);

        if (rc != SQLITE_OK ) {

                fprintf(stderr, "SQL error: %s\n", err_msg);

                sqlite3_free(err_msg);        
                sqlite3_close(db);

                return 1;
        } 
        sqlite3_close(db);
        return 0;
}

i checked sqlite3 libraries i linked with are greater than 3.31

lobhatta@lobhatta-ubn-01 ~/sqlite_dbstat/simple_sqlite - $ ../sqlite-dbstat_install/bin/sqlite3 --version 3.35.5 2021-04-19 18:32:05 1b256d97b553a9611efca188a3d995a2fff712759044ba480f9a0c9e98fae886 lobhatta@lobhatta-ubn-01 ~/sqlite_dbstat/simple_sqlite - $

lobhatta@lobhatta-ubn-01 ~/sqlite_dbstat/simple_sqlite - $ cat Makefile all: gcc insert_simple.c -I../sqlite-dbstat_install/include/ -L../sqlite-dbstat_install/lib/ -lsqlite3 -o a.out lobhatta@lobhatta-ubn-01 ~/sqlite_dbstat/simple_sqlite - $


lobhatta@lobhatta-ubn-01 ~/sqlite_dbstat/simple_sqlite - $ cat ../sqlite-dbstat_install/lib/pkgconfig/sqlite3.pc 
# Package Information for pkg-config

prefix=/home/lobhatta/sqlite_dbstat/sqlite-dbstat_install
exec_prefix=${prefix}
libdir=${exec_prefix}/lib
includedir=${prefix}/include

Name: SQLite
Description: SQL database engine
Version: 3.35.5
Libs: -L${libdir} -lsqlite3
Libs.private: -lz -lm -ldl -lpthread 
Cflags: -I${includedir}

(5) By Keith Medcalf (kmedcalf) on 2021-06-11 11:55:45 in reply to 4.1 [link] [source]

That code works fine for me. I would suspect that you are not linking against what you think you are linking against.

How about you add the following line:

        printf("sqlite3 libversion: %s\n", sqlite3_libversion());

after the line

        printf("bytes_written=%llu  %s\n",bytes_written,sql);

and see if that sheds any light ...

bytes_written=0  insert  INTO mqtt6 values ('abcd',1623066863471168257,1623066863471168257,35) RETURNING (SELECT sum(size_t) from mqtt6);
sqlite3 libversion: 3.36.0

(6) By lokakit (loki1993) on 2021-06-11 13:12:43 in reply to 5 [source]

thanks that was the issue its working now :)