Re: [sqlite] Simple example for dummy user writing C code

[My Name]

On Thu, Apr 2, 2009 at 3:54 PM, Rich Rattanni  wrote:

> Igor, be careful your not solving someone's homework
>

Not homework but real work :-)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Simple example for dummy user writing C code

[Rich Rattanni]

Oops, hit send to soon.  Your second question has been discussed in
the thread "IP from number with SQL" started on Sun, Mar 15, 2009 at
4:10 PM.  Of course with the knowledge that IPv6 is just IPv4 with
more bits thrown at it, you can tweak the discussion to suit your
needs.

On Thu, Apr 2, 2009 at 8:54 AM, Rich Rattanni  wrote:
> Igor, be careful your not solving someone's homework
>
> On Thu, Apr 2, 2009 at 7:39 AM, Igor Tandetnik  wrote:
>> "My Name" 
>> 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", );
>>
>> sqlite3_stmt* stmt = NULL;
>> sqlite3_prepare_v2(db, "select * from foo where a=? and b=?;", ,
>> 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

Re: [sqlite] Simple example for dummy user writing C code

[Rich Rattanni]

Igor, be careful your not solving someone's homework

On Thu, Apr 2, 2009 at 7:39 AM, Igor Tandetnik  wrote:
> "My Name" 
> 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", );
>
> sqlite3_stmt* stmt = NULL;
> sqlite3_prepare_v2(db, "select * from foo where a=? and b=?;", ,
> 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

Re: [sqlite] Simple example for dummy user writing C code

[Igor Tandetnik]

"My Name" 
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", );

sqlite3_stmt* stmt = NULL;
sqlite3_prepare_v2(db, "select * from foo where a=? and b=?;", , 
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] Simple example for dummy user writing C code

[My Name]

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

I didn't find a _simple_ example for C to do all this...

PS. What is the best way to store IPv6 addresses?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users