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

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`. ;-)

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

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.

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!

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

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.

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...