Thread overview | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
|
January 11, 2015 Sqlite | ||||
---|---|---|---|---|
| ||||
Can someone please tell me what I'm doing wrong here, the sql INSERT statement fails for some reason. I don't fully understand the callback function yet (I borrowed this from a C tutorial on the subject), maybe that is the source of the problem? import etc.c.sqlite3; import std.stdio; //stub extern(C) int aCallback(void *n, int c, char **v, char **col) { return 0; } void main(){ sqlite3 *db; int result = sqlite3_open("myDatabase.db", &db); if (result) { writeln("Failed to open database"); return; } //create table char *msg = null; result = sqlite3_exec(db, "CREATE TABLE people('id INT PRIMARY KEY NOT NULL, surname TEXT NOT NULL');", &aCallback, null, &msg); if (result) { writeln("Failed to create table"); //tidy up on exit sqlite3_close(db); return; } //insert record msg = null; result = sqlite3_exec(db, "INSERT INTO people (id, surname) VALUES (1, 'Smith');", &aCallback, null, &msg); if (result) { writeln("Failed to insert record"); //tidy up on exit sqlite3_close(db); return; } sqlite3_free(msg); sqlite3_close(db); } Many thanks Paul |
January 11, 2015 Re: Sqlite | ||||
---|---|---|---|---|
| ||||
Posted in reply to Paul Attachments: | On Sun, 11 Jan 2015 20:00:03 +0000 Paul via Digitalmars-d-learn <digitalmars-d-learn@puremagic.com> wrote: > Can someone please tell me what I'm doing wrong here, the sql INSERT statement fails for some reason. I don't fully understand the callback function yet (I borrowed this from a C tutorial on the subject), maybe that is the source of the problem? > > > import etc.c.sqlite3; > import std.stdio; > > //stub > extern(C) int aCallback(void *n, int c, char **v, char **col) > { > return 0; > } > > void main(){ > > sqlite3 *db; > int result = sqlite3_open("myDatabase.db", &db); > > if (result) { > writeln("Failed to open database"); > return; > } > > //create table > char *msg = null; > result = sqlite3_exec(db, "CREATE TABLE people('id INT PRIMARY > KEY NOT NULL, surname TEXT NOT NULL');", &aCallback, null, &msg); > if (result) { > writeln("Failed to create table"); > > //tidy up on exit > sqlite3_close(db); > return; > } > > //insert record > msg = null; > result = sqlite3_exec(db, "INSERT INTO people (id, surname) > VALUES (1, 'Smith');", &aCallback, null, &msg); > if (result) { > writeln("Failed to insert record"); > > //tidy up on exit > sqlite3_close(db); > return; > > } > > sqlite3_free(msg); > sqlite3_close(db); > > } > > Many thanks > > Paul if you'll output the error message, you'll see something unusual here: if (result) { import std.conv : to; writeln("Failed to insert record: ", to!string(msg)); //tidy up on exit sqlite3_close(db); return; } "Failed to insert record: table people has no column named id" wow! but it has! or isn't it? yep, it hasn't. the error is here: > result = sqlite3_exec(db, "CREATE TABLE people('id INT PRIMARY > KEY NOT NULL, surname TEXT NOT NULL');", &aCallback, null, &msg); `CREATE TABLE people('...')` is not the syntax you want. i don't know why sqlite is not rejecting it, but the correct one is this: result = sqlite3_exec(db, "CREATE TABLE people(id INT PRIMARY "~ "KEY NOT NULL, surname TEXT NOT NULL);", &aCallback, null, &msg); note the single quotes in your code: that is where it all goes wrong. i don't know where you got that quotes from, but this is not a valid SQL syntax for `CREATE TABLE`. ;-) |
January 11, 2015 Re: Sqlite | ||||
---|---|---|---|---|
| ||||
Posted in reply to ketmar | On Sunday, 11 January 2015 at 20:20:21 UTC, ketmar via Digitalmars-d-learn wrote:
> note the single quotes in your code: that is where it all goes wrong. i
> don't know where you got that quotes from, but this is not a valid SQL
> syntax for `CREATE TABLE`. ;-)
Thank you, I thought it might be something obvious - that will teach me to cut and paste code! :D
|
January 11, 2015 Re: Sqlite | ||||
---|---|---|---|---|
| ||||
Posted in reply to Paul | On Sunday, 11 January 2015 at 20:30:41 UTC, Paul wrote:
> On Sunday, 11 January 2015 at 20:20:21 UTC, ketmar via Digitalmars-d-learn wrote:
>> note the single quotes in your code: that is where it all goes wrong. i
>> don't know where you got that quotes from, but this is not a valid SQL
>> syntax for `CREATE TABLE`. ;-)
>
> Thank you, I thought it might be something obvious - that will teach me to cut and paste code! :D
Hint: Put the SQL in a file create_people.sql and import it into your code via the import statement:
string sql = import("create_people.sql"); // you'll need a correct -J compiler switch
That way you can easily test if it's correct on the commandline. It's .read <filename> in the sqlite3 shell.
|
January 12, 2015 Re: Sqlite | ||||
---|---|---|---|---|
| ||||
Posted in reply to Tobias Pankrath | On Sunday, 11 January 2015 at 22:19:28 UTC, Tobias Pankrath wrote:
> Hint: Put the SQL in a file create_people.sql and import it into your code via the import statement:
>
> string sql = import("create_people.sql"); // you'll need a correct -J compiler switch
>
> That way you can easily test if it's correct on the commandline. It's .read <filename> in the sqlite3 shell.
Neat, thank you!
|
January 25, 2015 Re: Sqlite | ||||
---|---|---|---|---|
| ||||
Posted in reply to Paul | I'd like to vary the query based on input but if I try to move the string out of the sqlite3_exec call like this: string sqlStatement = "CREATE TABLE people(id INT PRIMARY KEY NOT NULL, surname TEXT NOT NULL);"; result = sqlite3_exec(db, sqlStatement, &aCallback, null, &msg); ...it won't compile: Error: function etc.c.sqlite3.sqlite3_exec (sqlite3*, const(char)* sql,... is not callable using argument types (sqlite3*, string,... I can assign using: const char *sqlStatement = "CREATE TABLE... So how do I get a constant character pointer that can be modified at runtime? Paul |
January 25, 2015 Re: Sqlite | ||||
---|---|---|---|---|
| ||||
Posted in reply to Paul | On Sunday, 25 January 2015 at 18:15:21 UTC, Paul wrote:
> I'd like to vary the query based on input but if I try to move the string out of the sqlite3_exec call like this:
>
> string sqlStatement = "CREATE TABLE people(id INT PRIMARY KEY NOT NULL, surname TEXT NOT NULL);";
> result = sqlite3_exec(db, sqlStatement, &aCallback, null, &msg);
>
> ...it won't compile:
>
> Error: function etc.c.sqlite3.sqlite3_exec (sqlite3*, const(char)* sql,...
> is not callable using argument types (sqlite3*, string,...
>
> I can assign using:
>
> const char *sqlStatement = "CREATE TABLE...
>
> So how do I get a constant character pointer that can be modified at runtime?
>
> Paul
Only string literals convert to const(char)*, because only for them it is guaranteed that they are null terminated. For everything else use toStringz.
|
January 25, 2015 Re: Sqlite | ||||
---|---|---|---|---|
| ||||
Posted in reply to Tobias Pankrath | On Sunday, 25 January 2015 at 18:19:47 UTC, Tobias Pankrath wrote:
> Only string literals convert to const(char)*, because only for them it is guaranteed that they are null terminated. For everything else use toStringz.
So, as a trivial example, is this how it's done?:
string semiC = ";";
const char *sqlStatement = toStringz("CREATE TABLE people(id INT PRIMARY KEY NOT NULL, surname TEXT NOT NULL)"~semiC);
Seems rather ugly but I guess it's a result of interfacing with C...
|
Copyright © 1999-2021 by the D Language Foundation