Re: [sqlite] sqlite3_close doesn't release always the file handle.

[Will Leshner]

On 9/22/06, Ran <[EMAIL PROTECTED]> wrote:


What I do is to open a database, and start a transaction in it. Then,
without ending the transaction, open again the database and simply close it.

I found out, that the inner sqlite3_close return 0 (SQLITE_OK), but the
file handle is not released. So if I do it too many times, I run out of file
handles.


Would you be willing to file a bug report about this? I have a
"customer" who is claiming a similar problem and if you can
demonstrate the problem with your code it might be worth having it
checked out by the SQLite team.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] version 3.3.4

[nick700]

Hi all,

Anyone knows where I could download the precompiled binary of version 3.3.4 
from?

Thank you in advance,

-Nick

Re: [sqlite] Locking problems

[He Shiming]

Hi !

I'm getting a lots of "database is locked" (code:5).

My app keeps giving up on one machine, it might be that
there is a network problem but I not 100% sure.

Anyway, when the app hangs all the other machines
including the machine where the database file is get
the "database is locked" (code:5) error.
To get rid of the error I have to close my app on all
machines (five total) and sometimes reboot the machine
with the database. As far as I can tell there are no
processes still running.

The database is as far as I know not corrupted and seems
ok after "restarting the network".

Why do I get "database is locked" (code:5) and what do I
have to do to avoid it ?
I can try to ensure that there is a try/catch block but
I'm unsure how to unlock the database, Is it enough to
do a sqlite3_close() or do I need to ROLLBACK TRANSACTION
and sqlite3_finalize() before I sqlite3_close() ?

Windows XP, SQLite 3.3.4.

/Martin
ma1999ATjmaDOTse



Usually, the SQLITE_LOCKED means that you tried to prepare or execute a new 
SQL statement without resetting or finalizing a previous one on a single 
connection. The statements should be prepared and executed this way:

sqlite3_prepare
sqlite3_bind_*
sqlite3_step
// more sqlite3_steps if there are more statements
sqlite3_finalize

You can't put another sqlite3_prepare in the middle. If you did, you'll get 
the database is locked error. And if any error encountered during the 
process, you should call sqlite3_reset to reset the statement.


I suggest that you check your code very carefully to see if any statements 
are not finalized or resetted.


Best regards,
He Shiming 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Locking problems

[Martin Alfredsson]

Hi !

I'm getting a lots of "database is locked" (code:5).

My app keeps giving up on one machine, it might be that
there is a network problem but I not 100% sure.

Anyway, when the app hangs all the other machines
including the machine where the database file is get
the "database is locked" (code:5) error.
To get rid of the error I have to close my app on all
machines (five total) and sometimes reboot the machine
with the database. As far as I can tell there are no
processes still running.

The database is as far as I know not corrupted and seems
ok after "restarting the network".

Why do I get "database is locked" (code:5) and what do I
have to do to avoid it ?
I can try to ensure that there is a try/catch block but
I'm unsure how to unlock the database, Is it enough to
do a sqlite3_close() or do I need to ROLLBACK TRANSACTION
and sqlite3_finalize() before I sqlite3_close() ?

Windows XP, SQLite 3.3.4.

/Martin
ma1999ATjmaDOTse



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Citing Triggers As Constraints

[Rich Shepard]

On Fri, 22 Sep 2006, Dennis Cote wrote:


The table must exist before the triggers can be created. The triggers are
associated with the table by naming the table in the trigger's create
statement, not by naming the trigger in the table's create statement.


Dennis,

  I wondered about that after sending the message and leaving the office for
a bit. On reflection it makes perfect sense. I'll drop and recreate the
table.


HTH


  Certainly did!

Thanks,

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.(TM)|Accelerator
 Voice: 503-667-4517  Fax: 503-667-8863

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Citing Triggers As Constraints

[Dennis Cote]

Rich Shepard wrote:
  For some of the tables I have foreign key triggers that apply to 
INSERT,
UPDATE, and DELETE. When I create the table, do I explicitly name each 
one

as an table constraint?

  Example, I have three foreign key triggers, fki_comp_cat, fku_comp_cat,
and fkd_comp_cat that ensure that records in the 'component' table 
match a
record in the 'category' table, and that if an attempt is made to 
delete a

category while there are component rows that reference it, the process
aborts. So, do I write the ddl as:

  create table component(comp_id integer primary key, comp_cat text, 
comp_name

text, CONSTRAINT fki_comp_cat, fku_comp_cat, fkd_comp_cat);

  I don't see this in Mike Owens' book.

TIA,

Rich


Rich,

The table must exist before the triggers can be created. The triggers 
are associated with the table by naming the table in the trigger's 
create statement, not by naming the trigger in the table's create statement.


create table component(comp_id integer primary key, comp_cat text, 
comp_name text);


create trigger fki_comp after insert on component begin ... end;
create trigger fku_comp after update on component begin ... end;

HTH
Dennis Cote



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: sqlite3_get_table and non-strings

[Dennis Cote]

Mark Richards wrote:
I suppose this has already been broached when the design of 
sqlite3_get_table was addressed.  

Mark,

Actually this wasn't broached at that time, because when the 
sqlite_get_table API was designed, sqlite was typeless. All data was 
stored as strings, so it was natural to return all data as strings. This 
is why the legacy APIs sqlite_exec and sqlite_get_table only return 
strings. These APIs were maintained in the new versions for backwards 
compatibility. Only legacy programs should be using those APIs, so they 
would be expecting only string data.


New programs that want to store data using other types should use the 
new 
sqlite_prepare/sqlite_bind_*/sqlite_step/slite_column_*/sqlite_finalize 
APIs. It is much easier than trying to undo the string conversions that 
sqlite does using the old APIs.


HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Citing Triggers As Constraints

[Rich Shepard]

  For some of the tables I have foreign key triggers that apply to INSERT,
UPDATE, and DELETE. When I create the table, do I explicitly name each one
as an table constraint?

  Example, I have three foreign key triggers, fki_comp_cat, fku_comp_cat,
and fkd_comp_cat that ensure that records in the 'component' table match a
record in the 'category' table, and that if an attempt is made to delete a
category while there are component rows that reference it, the process
aborts. So, do I write the ddl as:

  create table component(comp_id integer primary key, comp_cat text, comp_name
text, CONSTRAINT fki_comp_cat, fku_comp_cat, fkd_comp_cat);

  I don't see this in Mike Owens' book.

TIA,

Rich

--
Richard B. Shepard, Ph.D.   |The Environmental Permitting
Applied Ecosystem Services, Inc.(TM)|Accelerator
 Voice: 503-667-4517  Fax: 503-667-8863

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: Re: sqlite3_get_table and non-strings

[Mark Richards]

>>I can't help but notice that verb field is declared as TEXT(80) in 
the >>database, but char[16] in your structure.

I couldn't help notice that you noticed. :)

>>If you mean an instance of a structure, then of course you can
>> allocate one on the heap.
Yes, that's what I meant to say.

Thanks for leading me in the proper direction.

/m


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Re: sqlite3_get_table and non-strings

[Igor Tandetnik]

Mark Richards <[EMAIL PROTECTED]>
wrote:

Ha!  Good question.  I suppose this has already been broached when the
design of sqlite3_get_table was addressed.  My first thought would be
to pass sqlite3_get_table a structure that represents the table in
field-order.  ie:

CREATE TABLE sequence (seq_nr INTEGER NOT NULL PRIMARY KEY, seq_family
INTEGER, enable INTEGER, verb TEXT(80));

typedef struct _sequence
{
int seq_nr;
int seq_family;
int enable;
char text[16];
} sequence[10];


That woudn't work too well with SQLite's manifest typing. I can't help 
but notice that verb field is declared as TEXT(80) in the database, but 
char[16] in your structure. But recall that SQLite ignores these length 
restrictions and allows any field to hold an arbitrarily long string 
(and indeed a value of any other type).


I also wonder how you plan to represent NULL values.

You are trying to design an object-relational mapping (ORM). There are 
quite a few ORM products around, you don't need to reinvent one.



In C is there such a thing as a structure created dynamically (at
runtime)?


If you mean structure the type, then no - C is a statically typed 
language. If you mean an instance of a structure, then of course you can 
allocate one on the heap.


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: sqlite3_get_table and non-strings

[Mark Richards]

> Do you expect sqlite3_get_table to look at char*** pointer and somehow
> guess that it's a structure, and figure out types of individual
> fields? How would you go about implementing something like that?

Ha!  Good question.  I suppose this has already been broached when the 
design of sqlite3_get_table was addressed.  My first thought would be to 
pass sqlite3_get_table a structure that represents the table in 
field-order.  ie:


CREATE TABLE sequence (seq_nr INTEGER NOT NULL PRIMARY KEY, seq_family 
INTEGER, enable INTEGER, verb TEXT(80));	


typedef struct _sequence
{
int seq_nr;
int seq_family;
int enable;
char text[16];
} sequence[10];

Then when each row is parsed each column is populated into the 
associated structure element based on the field type of the column. 
sqlite ought to know that as it's likely an internal value.


I've already written a function like this that works with sqlite_exec to 
a callback.  Packaging it in sqlite3_get_table would simplify some tasks 
but clearly wouldn't offer a generic solution (you'd have to create a 
structure for each table you want to get, and make sure that your SELECT 
statement will generate the columns in the order of your structure).


In C is there such a thing as a structure created dynamically (at runtime)?

--

I'll have a look at sqlite3_prepare, sqlite3_step and sqlite3_column_


/m




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: sqlite3_get_table and non-strings

[Igor Tandetnik]

Mark Richards <[EMAIL PROTECTED]>
wrote:

Although it does not appear to be mentioned in the documentation, is
it correct to assume that sqlite3_get_table can only handle string
datatypes?


It can read any data type from the database, but it converts everything 
to strings before returning to caller.


Yet another reason to use sqlite3_prepare, sqlite3_step and 
sqlite3_column_* instead.



Is sqlite3_get_table intended to work properly if it is I passed a
structure that represents the data I want it to return?


Do you expect sqlite3_get_table to look at char*** pointer and somehow 
guess that it's a structure, and figure out types of individual fields? 
How would you go about implementing something like that?


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] sqlite3_get_table and non-strings

[Mark Richards]

Although it does not appear to be mentioned in the documentation, is it 
correct to assume that sqlite3_get_table can only handle string datatypes?


In a particular problem I am working a query is made to return an 
integer and three string fields.


The issue may have more to do with the wrapper I am using (e_sqlite.c) 
which defines a structure to hold the data with a char fieldtype for 
each column.


It does this in a loop following the call:
recordset[i][j] = (char *) malloc( (strlen(result[count]) + 1) );
strcpy(recordset[i][j], result[count]);

So the result data is assumed to always be a string type.

Is sqlite3_get_table intended to work properly if it is I passed a 
structure that represents the data I want it to return?  It would be 
great if someone might point me to an example of sqlite3_get_table that 
works with different field types.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] sqlite3_close doesn't release always the file handle.

[Ran]

Hi all,

I *think* that sqlite3_close behave strangly.

I use version 3.3.7 on Linux (Fedora Core 5).

What I do is to open a database, and start a transaction in it. Then,
without ending the transaction, open again the database and simply close it.

I found out, that the inner sqlite3_close return 0 (SQLITE_OK), but the
file handle is not released. So if I do it too many times, I run out of file
handles.

You are free to ask why I open and close that many times the same database
while it is already in transaction.
This is my mistake. Actually, it is already fixed.
But I still wonder - shouldn't the sqlite3_close return other thing then
just SQLITE_OK? Especially if the file handle is not released? If it did, I
would find my mistake much earlier.

Here is my script that demonstrate it (you can use /usr/sbin/lsof in linux
to see how many times the file is opened):

 #include 
 int main(int argc, char **argv) {
   sqlite3* db;
   sqlite3* db_inner;
   int rc;
   int i;
   system("rm -f open_many_test.db");

   rc = sqlite3_open("open_many_test.db", );
   sqlite3_exec(db, "begin", 0, 0, 0);
   sqlite3_stmt *pStmt;
   rc = sqlite3_prepare(db,
"create table a (id varchar)",
-1,
,
0);
   rc = sqlite3_step(pStmt);
   sqlite3_finalize(pStmt);

   rc = sqlite3_prepare(db,
"insert into a values('bla')",
-1,
,
0);
   rc = sqlite3_step(pStmt);
   sqlite3_finalize(pStmt);

   for (i = 0; i < 1; i++) {
 rc = sqlite3_open("open_many_test.db", _inner);
 printf("sqlite3_open gives %d\n", rc);

 rc = sqlite3_close(db_inner);
 printf("sqlite3_close gives %d\n", rc);
   }

   sqlite3_exec(db, "commit", 0, 0, 0);
   rc = sqlite3_close(db);
 }


I will appreciate any explaination.

Thanks,

Ran

Re: [sqlite] Performance question

[Dennis Cote]

Michael Wohlwend wrote:


But If I do "select data from pictures where (x between high_x and low_x)
and (y between high_y and low_y) then this takes ca. 8 seconds (!) on wince.

  

Michael,

If you are really writing your between clauses as above with the high 
limit first, then they are not doing what you think. The low limit 
should always be given first.


From the SQL:1999 standard:

8.3 
Function
Specify a range comparison.
Format
 ::=
 [ NOT ] BETWEEN
[ ASYMMETRIC | SYMMETRIC ]
 AND 
Syntax Rules
1) If neither SYMMETRIC nor ASYMMETRIC is specified, then ASYMMETRIC is 
implicit.
2) Let X, Y, and Z be the first, second, and third expression>s, respectively.
3) ‘‘X NOT BETWEEN SYMMETRIC Y AND Z’’ is equivalent to ‘‘NOT ( X 
BETWEEN SYMMETRIC

Y AND Z )’’.
4) ‘‘X BETWEEN SYMMETRIC Y AND Z’’ is equivalent to ‘‘((X BETWEEN 
ASYMMETRIC Y AND

Z) OR (X BETWEEN ASYMMETRIC Z AND Y))’’.
5) ‘‘X NOT BETWEEN ASYMMETRIC Y AND Z’’ is equivalent to ‘‘NOT ( X BETWEEN
ASYMMETRIC Y AND Z )’’.
6) ‘‘X BETWEEN ASYMMETRIC Y AND Z’’ is equivalent to ‘‘X>=Y AND X<=Z’’.
Access Rules
None.
General Rules
one.
Conformance Rules
1) Without Feature T461, ‘‘Symmetric ’’, conforming 
SQL language shall not

specify SYMMETRIC or ASYMMETRIC.
2) Without Feature S024, ‘‘Enhanced structured types’’, no subfield of 
the declared type of a value expression> that is simply contained in a  
shall be of a structured

type.

SQLite does not support symmetric between predicates so conformance rule 
1 applies. This means that Syntax rule 1 also applies and asymmetric is 
implied. This means that syntax rule 6 is used to translate the between 
predicate.


In your case, this means your:

x between high_x and low_x

is equivalent to:

x >= high_x and x <= low_x

which will never be true.

Note that standard SQL does not reorder the limits in the between clause 
if they are given in reverse order (even though it could).


Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Performance question

[Martin Jenkins]

Michael Wohlwend wrote:


I made a database of little pictures, which includes x und y coordinates and


Are x and y indexed?

Martin

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Wish to store a C structure in sqlite column

[Martin Jenkins]

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

[AJ]

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]
-

AW: [sqlite] Performance question

[Michael Wohlwend]

-Ursprüngliche Nachricht-
Von: Gerald Dachs [mailto:[EMAIL PROTECTED] 
Gesendet: Freitag, 22. September 2006 11:28
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] Performance question



>My sql knowledge may be a little bit rusty and I have really no idea how
sqlite is doing "between" >querys. Anyway, once I have learned never to use
between because your query is equivalent to:

>where ((x >= high_x and x <= low_x) or (x >= low_x and x <= high_x) and  (y
>= high_y and y <= > low_y) or (y >= low_y and y <= high_y))

I think it is:
where (x >= low_x and x <= high_x) and (y >= low_y and y <= high_y), i.e. in
"between a and b", a should be lower or equal to b (you don't get a result
otherwise)

So it's without the "or" part. But I will test the other statement too.

>because of the or operators you will get a union of 4 selects. Maybe I am
wrong but I would expect >that "where x >= low_x and x <= high_x and y >=
low_y and y <= high_y" should be faster and all >what you need. You have
indices on x and y, haven't you?

Yep :-)


Cheers 
 Michael

Re: [sqlite] Performance question

[Gerald Dachs]

> But If I do "select data from pictures where (x between high_x and low_x)
> and (y between high_y and low_y) then this takes ca. 8 seconds (!) on
> wince.

My sql knowledge may be a little bit rusty and I have really no idea how
sqlite is doing "between" querys. Anyway, once I have learned never to use
between because your query is equivalent to:

where ((x >= high_x and x <= low_x) or (x >= low_x and x <= high_x) and
 (y >= high_y and y <= low_y) or (y >= low_y and y <= high_y))

because of the or operators you will get a union of 4 selects.
Maybe I am wrong but I would expect that "where x >= low_x and x <= high_x
and
y >= low_y and y <= high_y" should be faster and all what you need. You have
indices on x and y, haven't you?

Gerald


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Wish to store a C structure in sqlite column

[Noel Frankinet]

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]
-

[sqlite] Performance question

[Michael Wohlwend]

Hi,


I made a database of little pictures, which includes x und y coordinates and
a blob (between 100 and 8000 bytes in size,  one blob, total db size 180MB).
If I do 
"select data from pictures where x=? And y=?" 
this works well, also on wince (measured myself: 1 such a select take 5
milliseconds on my wince). If I do this 100 times (to select all pictures in
a rectangle), the time it needs scales linear.
But If I do "select data from pictures where (x between high_x and low_x)
and (y between high_y and low_y) then this takes ca. 8 seconds (!) on wince.
Any idea where this long time comes from? 
The code is written in c++, without wrapper, page_size=512, cache=16MB and
runs on a 400MHz Arm cpu.

Thanks for answering,
 Michael

Re: [sqlite] Wish to store a C structure in sqlite column

[Narendran]

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

[AJ]

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

[Noel Frankinet]

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

[He Shiming]

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] Disabling locking?

[He Shiming]

I want to use SQLite with only one user executing queries sequentially , so
I don't need locking. Is there a simple way to completely disabling 
locking?

Specific instructions if possible please, thanks...



There are two kinds of locks.

First one is a file lock, as in functions returning SQLITE_BUSY. From what I 
understand, if you do execute queries sequentially, or serialize the queries 
from threads yourself. You won't be experiencing any file lockings.


The second is the database lock, as in functions returning SQLITE_ERROR. 
This could only happen when you didn't write your code correctly. For 
instance, if you forgot to finalize one prepared INSERT statement, and tried 
to prepare another INSERT statement right behind, you'll get SQLITE_ERROR, 
while sqlite3_reset will return SQLITE_LOCKED.


Queries must be executed sequentially on a single sqlite connection. It's 
not your choice. If you need to execute parallel queries, you have to open 
another connection and handle busy status. Locking is not a feature for you 
to disable. It's a fact that you can't read or write before another writing 
process is finished.


Best regards,
He Shiming 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Wish to store a C structure in sqlite column

[Narendran]

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]
-