Quantcast

Function sqlite3_prepare_v2 of C API does not work

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Function sqlite3_prepare_v2 of C API does not work

Олег Пруц
Hello SQLite development team,

I am contributing to DB Browser for SQLite (
https://github.com/sqlitebrowser/sqlitebrowser). It is written in C++ and
it relies on C API.
We have a problem: when foreign_keys pragma is enabled, we cannot
use sqlite3_prepare_v2.

Steps to reproduce:

Create database
$ sqlite3 issue901.db
SQLite version 3.15.2 2016-11-28 19:13:37
Enter ".help" for usage hints.
sqlite> CREATE TABLE `a`(`id` integer);
sqlite> CREATE TABLE `b`(`aid` integer, FOREIGN KEY(`aid`) REFERENCES
`a`(`id`));
sqlite> INSERT INTO `a` VALUES(1);
sqlite> .quit
Minimal C program (issue901.c)
#include <sqlite3.h>
#include <stdio.h>

#define NOTWORKING

int main()
{
sqlite3* db;
if(sqlite3_open_v2("issue901.db", &db, SQLITE_OPEN_READWRITE, NULL) !=
SQLITE_OK)
{
printf("sqlite3_open_v2 failed!\n");
return 1;
}

#ifdef NOTWORKING
if(sqlite3_exec(db, "PRAGMA foreign_keys=\"1\"", NULL, NULL, NULL) !=
SQLITE_OK)
{
printf("Enabling foreign keys failed!\n");
return 1;
}
#endif

sqlite3_stmt* stmt;
if(sqlite3_prepare_v2(db, "UPDATE `a` SET `id`=7 WHERE `id`=1;", -1, &stmt,
0) != SQLITE_OK)
{
printf("sqlite3_prepare_v2 failed!\n%s\n", sqlite3_errmsg(db));
return 1;
}

sqlite3_close(db);
return 0;
}
Build and run
$ gcc -lsqlite3 -o issue901 issue901.c
$ ./issue901
sqlite3_prepare_v2 failed!
foreign key mismatch - "b" referencing "a"

More detailed description here
https://github.com/sqlitebrowser/sqlitebrowser/issues/901

Regards,
Oleg Prutz
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Function sqlite3_prepare_v2 of C API does not work

Richard Hipp-3
On 12/21/16, Олег Пруц <[hidden email]> wrote:

> Hello SQLite development team,
>
> I am contributing to DB Browser for SQLite (
> https://github.com/sqlitebrowser/sqlitebrowser). It is written in C++ and
> it relies on C API.
> We have a problem: when foreign_keys pragma is enabled, we cannot
> use sqlite3_prepare_v2.
>
> Steps to reproduce:
>
> Create database
> $ sqlite3 issue901.db
> SQLite version 3.15.2 2016-11-28 19:13:37
> Enter ".help" for usage hints.
> sqlite> CREATE TABLE `a`(`id` integer);
> sqlite> CREATE TABLE `b`(`aid` integer, FOREIGN KEY(`aid`) REFERENCES
> `a`(`id`));

The parent column must be UNIQUE.  I suggest changing the first table
definition to:

   CREATE TABLE "a"("id" INTEGER PRIMARY KEY);

Note also that `...` identifier quoting is a deprecated MySQL-ism.
The perferred way to quote identifiers in SQL is with double-quotes:
"...".


> sqlite> INSERT INTO `a` VALUES(1);
> sqlite> .quit
> Minimal C program (issue901.c)
> #include <sqlite3.h>
> #include <stdio.h>
>
> #define NOTWORKING
>
> int main()
> {
> sqlite3* db;
> if(sqlite3_open_v2("issue901.db", &db, SQLITE_OPEN_READWRITE, NULL) !=
> SQLITE_OK)
> {
> printf("sqlite3_open_v2 failed!\n");
> return 1;
> }
>
> #ifdef NOTWORKING
> if(sqlite3_exec(db, "PRAGMA foreign_keys=\"1\"", NULL, NULL, NULL) !=
> SQLITE_OK)
> {
> printf("Enabling foreign keys failed!\n");
> return 1;
> }
> #endif
>
> sqlite3_stmt* stmt;
> if(sqlite3_prepare_v2(db, "UPDATE `a` SET `id`=7 WHERE `id`=1;", -1, &stmt,
> 0) != SQLITE_OK)
> {
> printf("sqlite3_prepare_v2 failed!\n%s\n", sqlite3_errmsg(db));
> return 1;
> }
>
> sqlite3_close(db);
> return 0;
> }
> Build and run
> $ gcc -lsqlite3 -o issue901 issue901.c
> $ ./issue901
> sqlite3_prepare_v2 failed!
> foreign key mismatch - "b" referencing "a"
>
> More detailed description here
> https://github.com/sqlitebrowser/sqlitebrowser/issues/901
>
> Regards,
> Oleg Prutz
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>


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

Re: Function sqlite3_prepare_v2 of C API does not work

Simon Slavin-3
In reply to this post by Олег Пруц

On 21 Dec 2016, at 11:52pm, Олег Пруц <[hidden email]> wrote:

> I am contributing to DB Browser for SQLite (
> https://github.com/sqlitebrowser/sqlitebrowser). It is written in C++ and
> it relies on C API.
> We have a problem: when foreign_keys pragma is enabled, we cannot
> use sqlite3_prepare_v2.

I was about to suggest you try opening the same file in the SQLite command-line tool, but I see DRH has explained the problem.

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

Re: Function sqlite3_prepare_v2 of C API does not work

Олег Пруц
Thanks for your responses, the original reporters confirmed that it is
possible to insert row in a table with parent colums when making them
UNIQUE (https://github.com/sqlitebrowser/sqlitebrowser/issues/463), so my
issue (https://github.com/sqlitebrowser/sqlitebrowser/issues/901) is
resolved too.

Regards,
Oleg Prutz


2016-12-22 4:38 GMT+03:00 Simon Slavin <[hidden email]>:

>
> On 21 Dec 2016, at 11:52pm, Олег Пруц <[hidden email]> wrote:
>
> > I am contributing to DB Browser for SQLite (
> > https://github.com/sqlitebrowser/sqlitebrowser). It is written in C++
> and
> > it relies on C API.
> > We have a problem: when foreign_keys pragma is enabled, we cannot
> > use sqlite3_prepare_v2.
>
> I was about to suggest you try opening the same file in the SQLite
> command-line tool, but I see DRH has explained the problem.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Loading...