[sqlite] Can the page size cause bugs? (was Re: problems with shared cache?)
Turning off the shared cache seems to have solved the problem for most users, but one win32 user continues to report problems. I noticed that in 3.6.12 the default page size is automatically calculated on Windows. In my application I explicitly set the page size to 4096. Can having a page size that doesn't match the disk geometry cause problems? That is the only reason I can think of that the user continues to report problems that aren't reproducible with the same DB file here. To recap, the user gets errors like: sqlalchemy.exc.IntegrityError: (IntegrityError) PRIMARY KEY must be unique u'update cards set priority = ? where id = ?' [[1, -9223199285979494924L], [1, -9221822696858457935L], [1, -9220362552298800344L], [1, -9218865005459903182L], [1, -9218053570259598995L], [1, -9217626953400592469L], [1, -9217257525142991358L], [2, -9217039826750418600L], [1, -9217011234538438799L], [1, -9216054651420921523L], [1, -9215471921529813571L], [3, -9215405945578177558L], [1, But if the user saves the deck and sends it to me, all the ids are unique. Cheers, Damien On Wed, Mar 25, 2009 at 9:38 AM, Damien Elmes wrote: > I can define the primary key column as not null if you think that will > help, but dumping the table reveals the ids are being assigned > sequential integers. > > On Tue, Mar 24, 2009 at 11:34 PM, Jim Wilcoxson wrote: >> Not sure if it will make a difference, but in your trigger stuff you >> explicitly coded null for the primary key value. Have you tried >> changing that so that you don't specify the primary key field at all? >> I can't remember from the previous post, but I think it was (or should >> be) set up as autoincrement. >> >> I think SQLite allows using multiple nulls for the primary key, but >> according to their docs, it is non-standard and it says something >> about "this may change in the future". Maybe you are getting caught >> in the middle of a change that is going to occur across multiple >> revisions of SQLite. >> >> Jim >> >> >> On 3/24/09, Damien Elmes wrote: >>> Sorry, my application's files are called decks, and I unwittingly used >>> the wrong terminology. >>> >>> Any ideas about the problem? >>> >>> On Sat, Mar 21, 2009 at 2:27 AM, Griggs, Donald >>> wrote: > However, when I ask the user to send me their deck, I find that: > > sqlite> pragma integrity_check; > integrity_check > --- > ok > sqlite> select id, count(id) from cards group by id having > count(id) >> 1; > sqlite> > > Any ideas? Obviously, that user is not playing with a full deck. ;-) ___ 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 >>> >> >> >> -- >> Software first. Software lasts! >> ___ >> 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
[sqlite] Optimizing, for newbies (and others!)
Hey there, i was reading through and my curiosity led me too google some more, i found a document i wanted to share with other newbies. I believe this is a gold mine which goes through a lot of optimization ideas... it's a bit old, but i believe the most of it is still current. http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html Simon -- When Earth was the only inhabited planet in the Galaxy, it was a primitive place, militarily speaking. The only weapon they had ever invented worth mentioning was a crude and inefficient nuclear-reaction bomb for which they had not even developed the logical defense. - Asimov ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] "-journal" file?
> Whenever my phBB3 install is failing on the PPC Mac Mini, it appears > that SQLite is producing an empty database file (size 0) plus a file > with the same name plus "-journal" appended. What kind of error is > this? Does this mean SQLite somehow crashed out while attempting to > write to the DB? The journal file is created when a database transaction begins. The file is used to ensure that the transaction is "atomic"-- ie, that it completes fully, or not at all. In your case, the leftover journal file likely indicates that the phBB3 install either crashed mid-update, or it neglected to "commit" the pending transaction. The next process that opens the database will detect the incomplete transaction, and roll the database back to its previous (in this case empty) state. Some helpful info: http://www.sqlite.org/tempfiles.html http://www.sqlite.org/atomiccommit.html ~Eric ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] "-journal" file?
Whenever my phBB3 install is failing on the PPC Mac Mini, it appears that SQLite is producing an empty database file (size 0) plus a file with the same name plus "-journal" appended. What kind of error is this? Does this mean SQLite somehow crashed out while attempting to write to the DB? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Library Linking Issues on Ubuntu
> >#include
> >#include
> >#include
> >#include
> >#include
> >
> >int main()
> >{
> >// create the database
> >sqlite3 *db;
> >int rc;
> >
> >rc = sqlite3_open("cmx.db", &db);
> >}
>
> This code will not create a database file, because there is
> no schema to store.
> You have to create at least one table in the database to
> convince sqlite it's worth to create it.
This code is just BAREBONES code that will produce the error I'm getting.
>
> >What folders should I be looking in to verify that
> >sqlite3 is where it needs to be on my system?
>
> Try:
>
> find / -name '*sqlite*' -ls | more
>
> It will take quite a while, but if it's your personal system
> it won't hurt anybody.
it is my personal machine - I'll give that a try. I've searched for other
standard c++ header files - I'll try this search see what comes up.
thanks!
_
Rediscover Hotmail®: Get quick friend updates right in your inbox.
http://windowslive.com/RediscoverHotmail?ocid=TXT_TAGLM_WL_HM_Rediscover_Updates1_042009
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite on Mac OS X with PowerPC?
On Thu, Apr 2, 2009 at 2:04 PM, Zobeid Zuma wrote: > I recently tried installing phpBB3 with SQLite on two Macs -- one > Intel-based and the other PPC. > > The Intel Mac worked perfectly, but the PPC machine keeps stalling > out. It's turned into quite a puzzle for me. So. . . I recalled > that SQLite is only available as a binary for Intel-based Macs, and > wondered if there is a reason for that. . .? > > Does it, in fact, work on PPC? Or should I just give that up as a bad > idea? > SQLite works perfectly well on PPC Macs. I use it everyday. Just compile it yourself. -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Carbon Model http://carbonmodel.org/ Open Source Geospatial Foundation http://www.osgeo.org/ Sent from Madison, WI, United States ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite on Mac OS X with PowerPC?
I recently tried installing phpBB3 with SQLite on two Macs -- one Intel-based and the other PPC. The Intel Mac worked perfectly, but the PPC machine keeps stalling out. It's turned into quite a puzzle for me. So. . .I recalled that SQLite is only available as a binary for Intel-based Macs, and wondered if there is a reason for that. . .? Does it, in fact, work on PPC? Or should I just give that up as a bad idea? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] install sqlite bin on linux fedora 10?
On Thu, Apr 2, 2009 at 1:47 PM, Antonio Pedro Dinis wrote: > install sqlite bin on linux fedora 10? > > i downloaded the sqlite3-3.6.12.bin , and i dont know how to install it on > linux and start working with sqllite with php > > any ideas Google? > > thanks > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] install sqlite bin on linux fedora 10?
install sqlite bin on linux fedora 10? i downloaded the sqlite3-3.6.12.bin , and i dont know how to install it on linux and start working with sqllite with php any ideas thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] indexing question
hi, i need some help to see if i understood the point about indexing. if i have 2 tables. each table has 2 columns. and now i want to join those tables through one column in each table: example T1 C1 C2 | +--+ | T2 | C1 C2 let say the columns in question are C1(t1) and C2(t2) first question: can i create index X1 in both columns C1(t1) and C2(t2) ? second question if the answer to first question is yes, then if i index the C1(t1) with index X1 and C2(t2) with L1. will i join my tables slower than if i join them when they are having the same index. and what is the actual gain when i create indexes. i know from experience that the stuff go faster , but why ? (if the answer is too long please direct me to the right literature to study this further). thank you -- View this message in context: http://www.nabble.com/indexing-question-tp22851213p22851213.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] question about indexing
On Thu, Apr 2, 2009 at 12:01 PM, Igor Tandetnik wrote: > baxy77bax wrote: >> i need some help to see if i understood the point about indexing. >> >> if i have 2 tables. each table has 2 columns. and now i want to join >> those tables through one column in each table: >> >> example >> T1 >> C1 C2 >>> >> +--+ >> | >> T2 | >> C1 C2 >> >> let say the columns in question are C1(t1) and C2(t2) >> first question: >> can i create index X1 in both columns C1(t1) and C2(t2) ? > > No. But you can of course create two indexes, one on T1(C1) and another > on T2(C2). Though you would only need one of them to speed up the join. > >> and what is the >> actual gain when i create indexes. > > The same you get from an index printed at the end of a textbook: it > allows you to quickly look up a term and jump to the right page. See http://www.mail-archive.com/sqlite-users@sqlite.org/msg37474.html for a very, very nice explanation from Igor of how indexes work. The explanation uses the same metaphor of an index in a text book as described above by Igor. > > Igor Tandetnik > > > -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Carbon Model http://carbonmodel.org/ Open Source Geospatial Foundation http://www.osgeo.org/ Sent from Madison, WI, United States ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] question about indexing
baxy77bax wrote: > i need some help to see if i understood the point about indexing. > > if i have 2 tables. each table has 2 columns. and now i want to join > those tables through one column in each table: > > example > T1 > C1 C2 >> > +--+ > | > T2 | > C1 C2 > > let say the columns in question are C1(t1) and C2(t2) > first question: > can i create index X1 in both columns C1(t1) and C2(t2) ? No. But you can of course create two indexes, one on T1(C1) and another on T2(C2). Though you would only need one of them to speed up the join. > and what is the > actual gain when i create indexes. The same you get from an index printed at the end of a textbook: it allows you to quickly look up a term and jump to the right page. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Library Linking Issues on Ubuntu
On Thu, 2 Apr 2009 00:20:15 -0700, centipede moto
wrote:
>g++ -Wall cmxmc.cpp -lsqlite3 -o cmxmc
I won't comment on your compile / link problems, I'm not
familiar with Ubuntu and g++.
I hope you read the documentation and FAQ on the SQLite
site.
>Here is my app code:
>
>#include
>#include
>#include
>#include
>#include
>
>int main()
>{
>// create the database
>sqlite3 *db;
>int rc;
>
>rc = sqlite3_open("cmx.db", &db);
>}
This code will not create a database file, because there is
no schema to store.
You have to create at least one table in the database to
convince sqlite it's worth to create it.
>What folders should I be looking in to verify that
>sqlite3 is where it needs to be on my system?
Try:
find / -name '*sqlite*' -ls | more
It will take quite a while, but if it's your personal system
it won't hurt anybody.
>Thanks!
--
( Kees Nuyt
)
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] question about indexing
hi, i need some help to see if i understood the point about indexing. if i have 2 tables. each table has 2 columns. and now i want to join those tables through one column in each table: example T1 C1 C2 | +--+ | T2 | C1 C2 let say the columns in question are C1(t1) and C2(t2) first question: can i create index X1 in both columns C1(t1) and C2(t2) ? second question if the answer to first question is yes, then if i index the C1(t1) with index X1 and C2(t2) with L1. will i join my tables slower than if i join them when they are having the same index. and what is the actual gain when i create indexes. i know from experience that the stuff go faster , but why ? (if the answer is too long please direct me to the right literature to study this further). thank you -- View this message in context: http://www.nabble.com/question-about-indexing-tp22851121p22851121.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select the first 2 rows
Remember, the order of rows returned from a SELECT, even one with a LIMIT clause, is undefined, so the "first 2 rows" may not be consistently the same. If you are dependent upon the rows being returned in a particular order (say by "rowid"), you should include an ORDER BY clause on your SELECT to ensure the rows are returned in the order you require. -Shane On Wed, Apr 1, 2009 at 3:02 PM, Eric Minbiole wrote: > > Hi all, > > I have a big table and I want only select the first 2 rows. > > I have tried this : > > select top 2 from table; > > but it doesn't work! Any help please. > > JP > > Use a LIMIT clause instead of TOP: > > SELECT * FROM table LIMIT 2; > > http://www.sqlite.org/lang_select.html > ___ > 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
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] General SQL question...
Igor Tandetnik wrote: > John Elrick wrote: > >> The following two queries appear to be functionally equivalent...that >> is to say the results they produce are identical. Is there any >> intrinsic advantage to one over the other? If so, what is that >> advantage? >> > > The difference is purely stylistical. According to > http://sqlite.org/optoverview.html, all conditions in ON clauses are > logically moved to WHERE clause before query plan is determined. > Thank you, Igor. John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Improving query performance
D. Richard Hipp wrote: > On Apr 1, 2009, at 2:00 PM, John Elrick wrote: > >> explain query plan >> select DISTINCT RESPONSES.RESPONSE_OID >> from DATA_ELEMENTS, RESPONSES, SEQUENCE_ELEMENTS >> where >> SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_NAME = :sequence_element_name and >> DATA_ELEMENTS.DATA_ELEMENT_NAME = :data_element_name and >> RESPONSES.instance_parent = SEQUENCE_ELEMENTS.SEQUENCE_ELEMENT_OID and >> RESPONSES.definition_parent = DATA_ELEMENTS.DATA_ELEMENT_OID >> >> orderfromdetail >> 00TABLE DATA_ELEMENTS WITH INDEX data_element_name_idx >> 12TABLE SEQUENCE_ELEMENTS WITH INDEX sequence_element_name_idx >> 21TABLE RESPONSES >> > > > The index is not being used on the RESPONSES table because your WHERE > clause constraint is comparing a TEXT column (instance_parent) against > an INTEGER column (sequence_element_oid). The rules of SQLite are > that this requires a NUMERIC comparison, but the index is constructed > using a TEXT collation and so the index cannot be used. > > Various workarounds: > > (1) redefine RESPONSES.INSTANCE_PARENT to be type INTEGER. (Do the > same with RESPONSES.definition_parent). > > (2) Add a "+" sign in front of sequence_element_oid in the where clause: > > ... instance_parent = +sequence_element_oid... > > This will force the RHS of the expression to be an expression rather > than a column name. That will force the use of TEXT collating for the > comparison, and thus make the index usable. > > (3) Case the integer on the RHS to text: > > ... instance_parent = CASE(seqence_element_oid AS varchar) ... > > Seems like (1) is probably the right fix, but any of these three will > work. Good heavens Richard! I didn't notice. There is no reason they can't be integers and I honestly thought they were. Thanks very much. John ___ 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
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", &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
Re: [sqlite] Simple example for dummy user writing C code
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", &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
Re: [sqlite] Converting BLOB Data type to String
"SATISH" wrote in message news:49d4a5bc.0c636e0a.6f6d.4...@mx.google.com > I tried what u suggested me,I got a compiler error cannot convert > const void * to void* . Make it const void* data = sqlite3_column_blob(my_stmt, col_no); int size = sqlite3_column_bytes(my_stmt, col_no); CString str(static_cast(data), size); Is that really so hard that you had to ask? > I am storing string as blob because I want hide my data from others I'm not sure why you expect a column of type BLOB to be any more difficult to examine than a column of type TEXT. > I am > writing an application which works on portable apps in my apps I am > using > sqlite if any one opens the database using tools available in the > internet > any one can view my data http://www.hwaci.com/sw/sqlite/see.html http://www.sqlite-crypt.com > i.e. what I am carrying so I am avoiding > this by > storing it using blob. Not really - rather, you are burying your head in the sand and believing that noone can see you now. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Converting BLOB Data type to String
Hello Igor, I tried what u suggested me,I got a compiler error cannot convert const void * to void* . I am storing string as blob because I want hide my data from others I am writing an application which works on portable apps in my apps I am using sqlite if any one opens the database using tools available in the internet any one can view my data i.e. what I am carrying so I am avoiding this by storing it using blob. Can you please help in converting this i.e. const void * to CString or tell me any other way to hide my data from others (the operation should not be time consuming) Regards, G.Satish -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Thursday, April 02, 2009 4:57 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Converting BLOB Data type to String "SATISH" wrote in message news:49d4529b.034c6e0a.2f1d.0...@mx.google.com >I have written a string into database by converting into "BLOB > Data Type".writing into database is Ok I got a problem when reading > from the database to read a blob from the database I am using the > function "const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);" > this functions returns me const void * where I want to convert this > into "CString".please any one of you can help me in converting const > void * to CString. Try this: void* data = sqlite3_column_blob(my_stmt, col_no); int size = sqlite3_column_bytes(my_stmt, col_no); CString str(static_cast(data), size); But why do you store your string as BLOB, and not as text, in the first place? 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] dates
Quoting ga...@schoolteachers.co.uk:
> I am trying to get date testing and manipulation to work. Should this work:
>
> select julianday('now') - julianday(startmonday) from wb
>
> startmonday is a text field that contains 2009-03-30.
>
> Also the following returns 'none'
>
> select julianday(startmonday) from wb
>
> Any help would be much appreciated
> Kind Regards
> Garry (Sqlite newbie)
Sorted it, I had the wrong date format in the field!!
Thanks anyway
Garry
___
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
"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", &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
Re: [sqlite] Converting BLOB Data type to String
"SATISH" wrote in message news:49d4529b.034c6e0a.2f1d.0...@mx.google.com >I have written a string into database by converting into "BLOB > Data Type".writing into database is Ok I got a problem when reading > from the database to read a blob from the database I am using the > function "const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);" > this functions returns me const void * where I want to convert this > into "CString".please any one of you can help me in converting const > void * to CString. Try this: void* data = sqlite3_column_blob(my_stmt, col_no); int size = sqlite3_column_bytes(my_stmt, col_no); CString str(static_cast(data), size); But why do you store your string as BLOB, and not as text, in the first place? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] dates
I am trying to get date testing and manipulation to work. Should this work:
select julianday('now') - julianday(startmonday) from wb
startmonday is a text field that contains 2009-03-30.
Also the following returns 'none'
select julianday(startmonday) from wb
Any help would be much appreciated
Kind Regards
Garry (Sqlite newbie)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Converting BLOB Data type to String
Hello Buddies, I have inserted into database using the sqlite api function "int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));".Here before binding I have converted the const void * to CString. I am trying to retrieve the data using the sqlite api function "const void *sqlite3_column_blob(sqlite3_stmt*, int iCol);" Regards, G.Satish. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jens Miltner Sent: Thursday, April 02, 2009 3:12 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Converting BLOB Data type to String Am 02.04.2009 um 07:45 schrieb SATISH: > Hello Buddies, > >I have written a string into database by converting into > "BLOB Data > Type".writing into database is Ok I got a problem when reading from > the > database to read a blob from the database I am using the function > "const > void *sqlite3_column_blob(sqlite3_stmt*, int iCol);" this functions > returns > me const void * where I want to convert this into "CString".please > any one > of you can help me in converting const void * to CString. How did you write the data to the database? SQLite does not have per- column data types, but rather uses per-value data types, i.e. whatever your inserted the data as will be used as the data type for that value. Did you try to just retrieve the value as a string (using sqlite3_column_text)? ___ 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] Converting BLOB Data type to String
Am 02.04.2009 um 07:45 schrieb SATISH: > Hello Buddies, > >I have written a string into database by converting into > "BLOB Data > Type".writing into database is Ok I got a problem when reading from > the > database to read a blob from the database I am using the function > "const > void *sqlite3_column_blob(sqlite3_stmt*, int iCol);" this functions > returns > me const void * where I want to convert this into "CString".please > any one > of you can help me in converting const void * to CString. How did you write the data to the database? SQLite does not have per- column data types, but rather uses per-value data types, i.e. whatever your inserted the data as will be used as the data type for that value. Did you try to just retrieve the value as a string (using sqlite3_column_text)? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Library Linking Issues on Ubuntu
Have you also installed the corresponding development package? Don't
know its name, but typically if you install a package named abcdefgh,
its devel package is named abcdefgh-devel.
On 04/02/2009 10:20 AM, centipede moto wrote:
> I keep getting:
>
> undefined reference to `sqlite3_open'
>
> Errors. I have seen forum posts etc on this but none of the suggestions are
> helping. I've tried including the sqlite3.c file in my compile but I get tons
> of errors and it won't build. I've tried adding -lsqlite3 to my compile code,
> then I get:
>
> /usr/bin/ld: cannot find -lsqlite3
>
> I am running Ubuntu, I've installed sqlite3 through apt-get and my compile
> code looks like this:
>
> g++ -Wall cmxmc.cpp -lsqlite3 -o cmxmc
>
> Here is my app code:
>
> #include
> #include
> #include
> #include
> #include
>
> int main()
> {
> // create the database
> sqlite3 *db;
> int rc;
>
> rc = sqlite3_open("cmx.db",&db);
> }
>
>
> What folders should I be looking in to verify that sqlite3 is where it needs
> to be on my system?
>
> Thanks!
>
> _
> Windows Live™: Keep your life in sync.
> http://windowslive.com/explore?ocid=TXT_TAGLM_WL_allup_1a_explore_042009
> ___
> 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
[sqlite] Library Linking Issues on Ubuntu
I keep getting:
undefined reference to `sqlite3_open'
Errors. I have seen forum posts etc on this but none of the suggestions are
helping. I've tried including the sqlite3.c file in my compile but I get tons
of errors and it won't build. I've tried adding -lsqlite3 to my compile code,
then I get:
/usr/bin/ld: cannot find -lsqlite3
I am running Ubuntu, I've installed sqlite3 through apt-get and my compile code
looks like this:
g++ -Wall cmxmc.cpp -lsqlite3 -o cmxmc
Here is my app code:
#include
#include
#include
#include
#include
int main()
{
// create the database
sqlite3 *db;
int rc;
rc = sqlite3_open("cmx.db", &db);
}
What folders should I be looking in to verify that sqlite3 is where it needs to
be on my system?
Thanks!
_
Windows Live™: Keep your life in sync.
http://windowslive.com/explore?ocid=TXT_TAGLM_WL_allup_1a_explore_042009
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
