www.digitalmars.com         C & C++   DMDScript  

digitalmars.D.learn - Sqlite

reply "Paul" <paul example.com> writes:
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
Jan 11 2015
parent reply ketmar via Digitalmars-d-learn <digitalmars-d-learn puremagic.com> writes:
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=20
 INSERT statement fails for some reason. I don't fully understand=20
 the callback function yet (I borrowed this from a C tutorial on=20
 the subject), maybe that is the source of the problem?
=20
=20
 import etc.c.sqlite3;
 import std.stdio;
=20
 //stub
 extern(C) int aCallback(void *n, int c, char **v, char **col)
 {
        return 0;
 }
=20
 void main(){
 =09
 	sqlite3 *db;
 	int result =3D sqlite3_open("myDatabase.db", &db);
 =09
 	if (result) {=09
 		writeln("Failed to open database");
 		return;=09
 	}
 =09
 	//create table=09
 	char *msg =3D null;
 	result =3D sqlite3_exec(db, "CREATE TABLE people('id INT PRIMARY=20
 KEY NOT NULL, surname TEXT NOT NULL');", &aCallback, null, &msg);
 	if (result) {=09
 		writeln("Failed to create table");
 	=09
 		//tidy up on exit
 		sqlite3_close(db);
 		return;	=09
 	}
 =09
 	//insert record
 	msg =3D null;
 	result =3D sqlite3_exec(db, "INSERT INTO people (id, surname)=20
 VALUES (1, 'Smith');", &aCallback, null, &msg);
 	if (result) {=09
 		writeln("Failed to insert record");
 	=09
 		//tidy up on exit
 		sqlite3_close(db);	=09
 		return;=09
 	=09
 	}=09
=20
 	sqlite3_free(msg);
 	sqlite3_close(db);=09
=20
 }
=20
 Many thanks
=20
 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);	=09
    return;=09
  }=09

"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 =3D sqlite3_exec(db, "CREATE TABLE people('id INT PRIMARY=20
 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 =3D 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`. ;-)
Jan 11 2015
parent reply "Paul" <paul example.com> writes:
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
Jan 11 2015
parent reply "Tobias Pankrath" <tobias pankrath.net> writes:
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.
Jan 11 2015
parent reply "Paul" <paul example.com> writes:
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!
Jan 11 2015
parent reply "Paul" <paul example.com> writes:
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
Jan 25 2015
parent reply "Tobias Pankrath" <tobias pankrath.net> writes:
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.
Jan 25 2015
parent "Paul" <paul example.com> writes:
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...
Jan 25 2015