Re: [sqlite] Wish to store a C structure in sqlite column
On Sep 21, 2006, at 7:18 AM, Narendran wrote:
as far as my knowledge SQLITE allows me to declare the column types
suppoted by the programming languare or say i am using blob . My
requirement
is i wish to store a structure in the SQLite column.
Instead of storing the structure in a single column, create a
separate table to represent the structure, and then use a column to
reference a row in that table via a foreign key.
Given the extremely lightweight nature of the structure that you
later posted:
typedef struct ethernetcard1
{
char port[10];
char ipaddress[20];
char mask[20];
int bandwidth;
}
as well as the fact that sooner or later you're likely to want to
query on it, you may as well just store it as real data rather than
as a BLOB:
CREATE TABLE 'ETHERNETCARD' (
ID INTEGER PRIMARY KEY,-- SQLite does this implicitly as ROWID
PORT VARCHAR(10),
IPADDRESS VARCHAR(20),
MASK VARCHAR(20),
BANDWIDTH INTEGER
);
Of course, you might also want to encode your IP address and netmask
into network-byte-order integers rather than store them as strings,
but I think the above gives you an idea of what I mean. And if
"port" refers to a physical port name (such as "en1") you might even
want to have a separate table for ports, and have the port column
just contain a foreign key referencing that table...
If you start to decompose your use of SQLite in this fashion, you'll
actually be using the database *as* a database, and you'll be much
better able to leverage it to do new and interesting things in the
future.
-- Chris
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Wish to store a C structure in sqlite column
Narendran wrote: > thanks a lot, > I am unable to resolve the problem for 3 days,hat's off to everyone. As far as I know, all technologies which transfer data between machines (e.g. RPC), languages (e.g. JNI) or wear the cross platform crown (e.g. SQLite) have some sort of data definition. Without any doubt they'll take explicit care about byte ordering, word size, alignment etc. File compression utilities are platform agnostic too. Since this is an old problem and none of RPC, JNI etc came up with a better way this implies to me that it's simply not possible to invent a simple, general yet reliable method in a couple of days. XML is meant to cover this area so I suspect it's just not do-able in the general case. I assume you've looked at and decided against the XML route, so.. For fixed, published data structures I'd look at the source for gzip or bzip and see how they marshal their data. They're cross platform file formats and are pretty well tested. For arbitrary data structures (which is what I suspect is what you want) and having used Python (rather than RPC or JNI) a lot recently I'd grab the source and have a look at the code which implements the "struct" module. This module lets you read/write C style data structures (from/to files, sockets etc) based on a simple ASCII format string. Again, it's pretty well tested and will be cross platform at the Python end and platform specific (which is what you want) at the C end. HTH Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Wish to store a C structure in sqlite column
Narendran wrote: thanks a lot, I believe i can store a structure now, but there is still a cache . I am in the process on creating a Independant API,I can store a structure in SQLite thro the above specified ways. what if some one else wants to use my API's which i used to create the DBI's ,and the destination Database engine doesn't support the blob datatypes. My question may be silly, but this is a serious issues for me. I am unable to resolve the problem for 3 days,hat's off to everyone. Thanking you , B.Narendran my suggestion of using base64 encoding for your struct will mean you can store it in any text field in any database, and its also possible to send it thru just about any protocol and it will most likely still be OK. you do know that you should not store pointers in your structure? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Wish to store a C structure in sqlite column
Narendran a écrit : thanks a lot, I believe i can store a structure now, but there is still a cache . I am in the process on creating a Independant API,I can store a structure in SQLite thro the above specified ways. what if some one else wants to use my API's which i used to create the DBI's ,and the destination Database engine doesn't support the blob datatypes. My question may be silly, but this is a serious issues for me. I am unable to resolve the problem for 3 days,hat's off to everyone. Thanking you , B.Narendran you can always encode it in text then -- Noël Frankinet Gistek Software SA http://www.gistek.net - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Wish to store a C structure in sqlite column
thanks a lot, I believe i can store a structure now, but there is still a cache . I am in the process on creating a Independant API,I can store a structure in SQLite thro the above specified ways. what if some one else wants to use my API's which i used to create the DBI's ,and the destination Database engine doesn't support the blob datatypes. My question may be silly, but this is a serious issues for me. I am unable to resolve the problem for 3 days,hat's off to everyone. Thanking you , B.Narendran -- View this message in context: http://www.nabble.com/Wish-to-store-a-C-structure-in-sqlite-column-tf2312254.html#a6443235 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Wish to store a C structure in sqlite column
I hackish method might be something like this:
struct s
{
...
};
struct s myS;
char buf[sizeof(s)*2]; // *2 as base64 encoding will be approx 33% bigger.
base64_encode( , buf, sizeof(s) );
INSERT INTO table ( myTextField ) VALUES ( 'buf' );
then retrieval is the opposite.
Noel Frankinet wrote:
Narendran a écrit :
Noel Frankinet wrote:
Narendran a écrit :
Dear Friends,
I am in the process of forming a Generic API,(sql oriented and
BerkelyDB
and sister databases). In the process of integration ,i like to
store a
Structure in Sqlite.
as far as my knowledge SQLITE allows me to declare the column types
suppoted by the programming languare or say i am using blob . My
requirement
is i wish to store a structure in the SQLite column.
I am unable to form a sql statement to store the structure ,i am
also
not
clear with whether i can have a strucure as column type.
suggestions will be really helpful.
Thanking you,
B.Narendran
You will need to turn your c struct into a blob and store that blob.
When retrieving the blob, you need a way to turn it back into your
struct.
Its releatively easy if your struct does not contains pointers.
Best wishes
--
Noël Frankinet
Gistek Software SA
http://www.gistek.net
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Dear Frankinet,
Thanks for ur reply,
I am unable to understand what u have said. I am having a structure
and I
am converting in to a blob. This means i am supposed to remove the
'\0' in
between the strucure and put a final '\0' (NULL) character . Blob
need only
on e null character to terminate it.
I tried to memcopy the structure elements and store them ,I can
store but
i am unable to find a way to retrieve it back.
typedef struct ethernetcard1
{
char port[10];
char ipaddress[20];
char mask[20];
int bandwidth;
}
what i tried is
char *buffer;
int bufferlen;buffersize;
bufferlen = strlen(port)+strlen(ipaddress)+strlen(mask)+sizeof(int)+1;
memcpy(buffer,user.port,strlen(user.port);
buffersize = strlen(user.port);
memcpy(buffer,user.ipaddress,strlen(user.ipaddress));
buffersize += strlen(user.ipaddress);
and finally i included a NULL character to the buffer to make it as
string
and i can insert in to a text field in sqlite column . I am unable to
figure
out a way to retrieve it back if i am storing in this way or a blob
type I
think blob will be similar to this .
expecting ur valuable suggestion.
Thanking you,
Narendran
hello Narendran,
Unfortunately, I'm still using 2.xx, so I encode the blob in character
using sqlite_encode and I decode it back when I get it from sqlite.
You are on the right track, but for string you should have a way to
store the length.
I have written some encoding function (like write_string and
read_string) to help encoding and decoding from the buffer (and avoi
all those mesy memcpy).
I hope this help
Best wishes
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Wish to store a C structure in sqlite column
Narendran a écrit :
Noel Frankinet wrote:
Narendran a écrit :
Dear Friends,
I am in the process of forming a Generic API,(sql oriented and
BerkelyDB
and sister databases). In the process of integration ,i like to store a
Structure in Sqlite.
as far as my knowledge SQLITE allows me to declare the column types
suppoted by the programming languare or say i am using blob . My
requirement
is i wish to store a structure in the SQLite column.
I am unable to form a sql statement to store the structure ,i am also
not
clear with whether i can have a strucure as column type.
suggestions will be really helpful.
Thanking you,
B.Narendran
You will need to turn your c struct into a blob and store that blob.
When retrieving the blob, you need a way to turn it back into your struct.
Its releatively easy if your struct does not contains pointers.
Best wishes
--
Noël Frankinet
Gistek Software SA
http://www.gistek.net
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Dear Frankinet,
Thanks for ur reply,
I am unable to understand what u have said. I am having a structure and I
am converting in to a blob. This means i am supposed to remove the '\0' in
between the strucure and put a final '\0' (NULL) character . Blob need only
on e null character to terminate it.
I tried to memcopy the structure elements and store them ,I can store but
i am unable to find a way to retrieve it back.
typedef struct ethernetcard1
{
char port[10];
char ipaddress[20];
char mask[20];
int bandwidth;
}
what i tried is
char *buffer;
int bufferlen;buffersize;
bufferlen = strlen(port)+strlen(ipaddress)+strlen(mask)+sizeof(int)+1;
memcpy(buffer,user.port,strlen(user.port);
buffersize = strlen(user.port);
memcpy(buffer,user.ipaddress,strlen(user.ipaddress));
buffersize += strlen(user.ipaddress);
and finally i included a NULL character to the buffer to make it as string
and i can insert in to a text field in sqlite column . I am unable to figure
out a way to retrieve it back if i am storing in this way or a blob type I
think blob will be similar to this .
expecting ur valuable suggestion.
Thanking you,
Narendran
hello Narendran,
Unfortunately, I'm still using 2.xx, so I encode the blob in character
using sqlite_encode and I decode it back when I get it from sqlite.
You are on the right track, but for string you should have a way to
store the length.
I have written some encoding function (like write_string and
read_string) to help encoding and decoding from the buffer (and avoi all
those mesy memcpy).
I hope this help
Best wishes
--
Noël Frankinet
Gistek Software SA
http://www.gistek.net
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Wish to store a C structure in sqlite column
Dear Friends,
I am in the process of forming a Generic API,(sql oriented and
BerkelyDB
and sister databases). In the process of integration ,i like to store a
Structure in Sqlite.
as far as my knowledge SQLITE allows me to declare the column types
suppoted by the programming languare or say i am using blob . My
requirement
is i wish to store a structure in the SQLite column.
I am unable to form a sql statement to store the structure ,i am also
not
clear with whether i can have a strucure as column type.
suggestions will be really helpful.
Thanking you,
B.Narendran
A C struct is already a blob. Inserting it to a table is quite
straightforward. I assume you already created your tables, and have your
connection open. You can try these to insert a struct:
struct MyStruct {
long nSomeStuff[1024];
};
MyStruct thisStruct = {0};
sqlite3* db; // already opened
sqlite3_stmt* pStmt = NULL;
const char* pszUnused;
sqlite3_prepare (db, "INSERT INTO TABLE (BLOBCOLUMN) VALUES (?);", -1,
, );
sqlite3_bind_blob (pStmt, 1, , sizeof(MyStruct), SQLITE_STATIC);
sqlite3_step (pStmt);
sqlite3_finalize (pStmt);
Blob data must be prepared using a wildcard (?) and be bound later. Remember
that when binding, the index of the first column is 1, not 0. And you have
to check return values for each of the sqlite3_* functions, they may fail or
return busy. There's no need to do any memory copy. If you will destroy the
struct before sqlite3_step is called, then change SQLITE_STATIC to
SQLITE_TRANSIENT. This way, sqlite will make an internal copy when
sqlite3_bind_blob is called.
When retrieving data, the size of the column is determined by
sqlite3_column_bytes. You use the value returned by this function to decide
how much memory you needed to copy from the pointer returned by
sqlite3_column_blob to your own struct.
I'm not sure if it'll help you to understand, in the eye of a database
system, a C struct doesn't have any difference to the data in a block of
memory buffer, or something like long long nVars[100];. They only need two
things to get started, a pointer, and the size.
Best regards,
He Shiming
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Wish to store a C structure in sqlite column
Noel Frankinet wrote:
>
> Narendran a écrit :
>> Dear Friends,
>>
>>
>> I am in the process of forming a Generic API,(sql oriented and
>> BerkelyDB
>> and sister databases). In the process of integration ,i like to store a
>> Structure in Sqlite.
>>
>> as far as my knowledge SQLITE allows me to declare the column types
>> suppoted by the programming languare or say i am using blob . My
>> requirement
>> is i wish to store a structure in the SQLite column.
>>
>> I am unable to form a sql statement to store the structure ,i am also
>> not
>> clear with whether i can have a strucure as column type.
>>
>> suggestions will be really helpful.
>>
>> Thanking you,
>> B.Narendran
>>
> You will need to turn your c struct into a blob and store that blob.
> When retrieving the blob, you need a way to turn it back into your struct.
> Its releatively easy if your struct does not contains pointers.
>
> Best wishes
>
> --
> Noël Frankinet
> Gistek Software SA
> http://www.gistek.net
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>
Dear Frankinet,
Thanks for ur reply,
I am unable to understand what u have said. I am having a structure and I
am converting in to a blob. This means i am supposed to remove the '\0' in
between the strucure and put a final '\0' (NULL) character . Blob need only
on e null character to terminate it.
I tried to memcopy the structure elements and store them ,I can store but
i am unable to find a way to retrieve it back.
typedef struct ethernetcard1
{
char port[10];
char ipaddress[20];
char mask[20];
int bandwidth;
}
what i tried is
char *buffer;
int bufferlen;buffersize;
bufferlen = strlen(port)+strlen(ipaddress)+strlen(mask)+sizeof(int)+1;
memcpy(buffer,user.port,strlen(user.port);
buffersize = strlen(user.port);
memcpy(buffer,user.ipaddress,strlen(user.ipaddress));
buffersize += strlen(user.ipaddress);
and finally i included a NULL character to the buffer to make it as string
and i can insert in to a text field in sqlite column . I am unable to figure
out a way to retrieve it back if i am storing in this way or a blob type I
think blob will be similar to this .
expecting ur valuable suggestion.
Thanking you,
Narendran
--
View this message in context:
http://www.nabble.com/Wish-to-store-a-C-structure-in-sqlite-column-tf2312254.html#a6441692
Sent from the SQLite mailing list archive at Nabble.com.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Wish to store a C structure in sqlite column
Narendran a écrit : Dear Friends, I am in the process of forming a Generic API,(sql oriented and BerkelyDB and sister databases). In the process of integration ,i like to store a Structure in Sqlite. as far as my knowledge SQLITE allows me to declare the column types suppoted by the programming languare or say i am using blob . My requirement is i wish to store a structure in the SQLite column. I am unable to form a sql statement to store the structure ,i am also not clear with whether i can have a strucure as column type. suggestions will be really helpful. Thanking you, B.Narendran You will need to turn your c struct into a blob and store that blob. When retrieving the blob, you need a way to turn it back into your struct. Its releatively easy if your struct does not contains pointers. Best wishes -- Noël Frankinet Gistek Software SA http://www.gistek.net - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Wish to store a C structure in sqlite column
Dear Friends, I am in the process of forming a Generic API,(sql oriented and BerkelyDB and sister databases). In the process of integration ,i like to store a Structure in Sqlite. as far as my knowledge SQLITE allows me to declare the column types suppoted by the programming languare or say i am using blob . My requirement is i wish to store a structure in the SQLite column. I am unable to form a sql statement to store the structure ,i am also not clear with whether i can have a strucure as column type. suggestions will be really helpful. Thanking you, B.Narendran -- View this message in context: http://www.nabble.com/Wish-to-store-a-C-structure-in-sqlite-column-tf2312254.html#a6429089 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
