"My Name" <[email protected]>
wrote in message
news:[email protected]
> I'm having hard time to store and retrieve data with SQLite. Let's
> assume I have this structure in my C code to hold my data
>
> struct foo {
> long a;
> float b;
> char c[1024];
> int d;
> }
>
> so the SQL definition would be
>
> CREATE TABLE foo
> (
> a LONG;
> b FLOAT;
> c VARCHAR(1024);
> d INT;
> );
>
> In real life c[1024] does not hold a printable string but variable
> length binary data and d tells the data length.
You probably want to store it as a BLOB then, not as text. You don't
need a separate column for d - a BLOB column knows its length (and so
does text, so you don't need extra column either way).
> Let's also assume I
> have N records where some of the fields can be same.
>
> { 1, 1.0, "data1", 5 }
> { 1, 2.0, "data2", 5 }
> { 2, 1.0, "data3", 5 }
> { 2, 2.0, "data4", 5 }
> { 5, 6.0, "data5", 5 }
>
> And here's the "dummy user" part, how should I read from and write to
> the database? I want to execute
>
> DELETE FROM foo WHERE b < ...
> INSERT INTO foo VALUE (......)
> SELECT * FROM foo WHERE a=... AND b=...
> SELECT c,d FROM foo WHERE a=... AND b=...
sqlite3* db = NULL;
sqlite3_open("myfile.db", &db);
sqlite3_stmt* stmt = NULL;
sqlite3_prepare_v2(db, "select * from foo where a=? and b=?;", &stmt,
NULL);
sqlite3_bind_int(stmt, 1, 42);
sqlite3_bind_double(stmt, 2, 4.2);
while (sqlite3_step(stmt) == SQLITE_ROW) {
foo row;
row.a = sqlite3_column_int(stmt, 0);
row.b = sqlite3_column_double(stmt, 1);
row.d = sqlite3_column_bytes(stmt, 2);
assert(row.d <= sizeof(row.c));
memcpy(row.c, sqlite3_column_blob(stmt, 2), row.d);
// do something with row
}
sqlite3_finalize(stmt);
sqlite3_close(db);
DELETE and INSERT are left as an exercise for the reader. They work the
same way, except that you only need to call sqlite3_step once, and of
course there are no column values to retrieve.
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
