Igor, be careful your not solving someone's homework....
On Thu, Apr 2, 2009 at 7:39 AM, Igor Tandetnik <itandet...@mvps.org> wrote: > "My Name" <mylistuser1...@gmail.com> > wrote in message > news:ee8102080904012149h3b8d64d9u8b972b1e6fbbf...@mail.gmail.com >> 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 > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
