SQLite: creating a table

[cfanatic]

Hi all.

I am having trouble making sense of the plethora of information on the SQLite site. My code below creates a file called "ljdata.sl3". The code compiles and runs, but after creating a table called "people", the file is blank and is 0 kb in size. Why? Is it because I have not inserted any rows or columns? Or was the table never created at all? I expected at least something to happen to the file.

Posting for help here since I am using the SQLite C Interface. Thanks in advance.

#include <stdio.h>
#include "sqlite3.h"
#include <stdlib.h>

int main(void)
{
    char *test = "ljdata.sl3"; // database filename
    sqlite3 *myHandle;
    int retval; // return value of operations
    const char *tail;
    sqlite3_stmt *pStmt;  // prepared statement

    if((retval = sqlite3_open(test, &myHandle)) != SQLITE_OK)
    {
        perror("sqlite3_open error");
        exit(EXIT_FAILURE);
    }
    if((retval = sqlite3_prepare_v2(myHandle, "create table if not exists ljdata.sl3.people", 0, &pStmt, &tail)) != SQLITE_OK)
    {
        perror("sqlite3_prepare_v2 error");
        exit(EXIT_FAILURE);
    }
    if((retval = sqlite3_step(pStmt)) == SQLITE_ERROR)
    {
        perror("sqlite3_step error");
        exit(EXIT_FAILURE);
    }
    if((retval = sqlite3_finalize(pStmt)) != SQLITE_OK)
    {
        perror("sqlite3_finalize error");
        exit(EXIT_FAILURE);
    }
    if((retval = sqlite3_close(myHandle)) != SQLITE_OK)
    {
        perror("sqlite3_close");
        exit(EXIT_FAILURE);
    }
    return 0;
}

[dwks]

Suggestion:

void die(const char *function) {
    perror(function);
    exit(EXIT_FAILURE);
}

Anyway... you can use the command-line sqlite tool to try out your commands.

$ sqlite3 test.db
SQLite version 3.7.13 2012-06-11 02:05:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table if not exists ljdata.sl3.people;
Error: near ".": syntax error
sqlite> create table if not exists ljdata;
Error: near ";": syntax error
sqlite> create table if not exists ljdata (char);
sqlite> 
$ wc -c test.db 
2048 test.db
$

As you can see, it looks like you can't have "." characters in table names. Also, you need to specify the column data types. I don't know much about sqlite but hopefully this will get you started. By the way, sqlite3_exec() is a wrapper around prepare_v2, step, and finalize. One-Step Query Execution Interface

[edit] A useful-looking link I stumbled upon: http://milky.manishsinha.net/2009/03/30/sqlite-with-c/ [/edit]