Re: [sqlite] Writing double into a socket file
Alex, Thankyou for the comments. I use non-blocking fd's also but implement multiple threads to take advantage of multiple processor servers. Since threads carry quite a bit of baggage your single thread approach would probably be superior on a single processor machine. It has been experience that a well conceived single thread process gives the best performance on a single processor, as one would expect. I would hand out my code except that the whole things incorporates extra processors such as an embedded server page processor and and compiler plus a security shell and would be a handful to maintain and document in great detail but if you are interested in chunks of code contact me. Alex Katebi wrote: > John, > >My server uses epoll( ) and runs non-blocking in a single thread. I did > some google and found out that people who need a very fast server that is > highly scalable are using this model. non-blocking seems to be more > complicated at first glance but it actually makes the server design much > simpler. I am planning to release my code as open source when it is more > complete. > > Thanks, > -Alex > > On Tue, Jun 10, 2008 at 8:48 PM, John Stanton <[EMAIL PROTECTED]> wrote: > >> We use an application server I wrote which handles HTTP, serves file and >> has embedded Sqlite for the RPCs. The RPC can deliver its result either >> in XML for widely distributed applications or as JSON if it is >> responding to a WWW browser in AJAX mode. >> >> We keep a local library of SQL RPCs so that SQl never appears on the >> network and we have immunity from injection attacks. It also means that >> we can cache compiled SQL, a useful performance win. >> >> We use the Expat parser in remote programs using the XML format. A >> wrapper makes it a verifying parser to ensure well formed XML. >> >> The server is multi threaded and maintains a pool of live threads so it >> can respond quickly and assign multiple threads to one browser >> connection. Shared cache in Sqlite and some extra caching to maintain >> multiple open databases and results makes Sqlite behave like a simple to >> use enterprise DB server, but without the overhead of extra processes. >> We use mutexes for synchronization, set up as read and write locks and >> avoid the POSIX file locks. >> >> We installed Javascript as a procedural language to be used by Sqlite >> instead of PL/SQL but that is not a great success (v. slow) and we are >> going to experiment with using Python. >> >> Based on our experience you should be very happy with your Sqlite based >> RPC capability. >> >> Alex Katebi wrote: >>> John & John, >>> >>>Actually my API used to be XML using SCEW a DOM like XML parser that >> uses >>> Expat. >>> >>>For my particular application RPC made more sense to me. What could be >>> easier than a function call? Another advantage was that I did not have to >>> create any functions. I am just using SQLite's C API. Now the users of my >>> application can query any table on the server side using select. Since my >>> application is a network server, and network debugging capability is >>> crucial. >>> The only ugliness is that select locks the tables. I wish D. Hipp would >> give >>> us an option for pStmt to create a temporary table of the select result >> set >>> and delete that temp table after finalize automatically. This way a >> client >>> can sit on a prepare/step for a long time. >>> >>>I solved the endian issue pretty easy by sending the type code. >>> >>> Thanks, >>> -Alex >>> >>> >>> On Tue, Jun 10, 2008 at 3:07 PM, John Elrick <[EMAIL PROTECTED]> >>> wrote: >>> Alex Katebi wrote: > Yes I need to do it as 8 byte buffer. Convert the endianess to the network > then back to host for 8 byte integer. > I think XML is great for command validation and CLI auto typing, help etc. > Besides parsing issue, XML can not handle binary data directly. > As John pointed out, XML is not intended to handle binary data directly. We use XML as a transfer medium for binary data and simply base64 encode it before encapsulation. John Elrick Fenestra Technologies ___ 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-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-users mailing list sqlite-users@sqlite.org http://sqlite.o
[sqlite] How can I count the number of times a word present in text indexed with FTS 3?
Hello, I was reading the document 'http://www.sqlite.org/cvstrac/wiki?p=FtsUsage' and I was wondering whether there is a way to count the number of times a word is present in text indexed with FTS 3. For example: CREATE VIRTUAL TABLE recipe USING fts3(name, instructions); INSERT INTO recipe VALUES ( 'coffee cake', 'Bake in oven for 30 minutes. Remove from the oven and allow to cool for 5 minutes. Turn pan over onto a serving platter to remove. Serve by pulling apart chunks with forks' ); Is there some way to count the times the word 'oven' appears in the text? Where I'm trying to get at: to retrieve all rows where 'oven' is more often referenced. This would allow me to build a"relevance" list, for example. Thanks in advance, -- Tito ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best Practices
On Tue, Jun 10, 2008 at 1:29 PM, A. H. Ongun <[EMAIL PROTECTED]> wrote: > We have an application requirement for the following: > 1) Maintain an in memory database of real time data. The main table mimics > a Modbus register address scheme and once the table is created and records > are inserted to create the table at startup, all writes are updates for the > state machine. (app. 1000 records). Reads are through multiple Modbus > TCP/IP protocol based devices which result in selects of up to 100 > contiguous records at a time. > 2) At periodic intervals based on events and time intervals a disk (SD > card) database gets updated (inserts). This contains system parameters, and > various different tables. > 3) Occasionally the whole database on disk is written to an external media > (USB) for backup purposes. > 4) Through a web interface user(s) can export portions of a table by > specifying type of data (table) and start and end time in csv format. (cgi > scripts). > All of the above is implemented currently using a "custom" format, and is > really a pain to expand and modify. > Items 1 & 2 are fairly straight forward to implement, they would be in the > same executable as well. > My original thought was to use the sqlite3 shell interface to accomplish > 4. I am concerned a bit about items 3 and 4 though in regards to > performance issues in that I can not afford not to service data requests > that could come several times a second if database could be locked during > operation of 3 and 4. Size of the database can grow to 1-2.4GB. > I am open to any suggestions. > Thanks in advance. > andy Okay, my first question is: What OS is this device running? If you need realtime semantics, you really need an RTOS. If the answer contains the string "Windows" you're in serious trouble, right from the get-go. You mention two databases (#1 has a modbus database, then #2 has a config database), then #3 mentions a "whole database". Which one are you referring to? I know nothing about Modbus, but you mention both reading and writing, and that the reading is being done in bulk by remote devices. How often does this happen? How often are new rows inserted into this Modbus database? My experience with USB memory sticks is: They're SLOW. A typical desktop hard drive does about 30MB/sec, which would require 80 seconds to fully write out a database. A USB memory stick will be maybe a tenth of that. That's over ten minutes to write the whole database out to the USB drive. Based on the limited information I have, the best advice I can give is this: If the rate of inserts is very low (less than one per second), it may be best to maintain two sets of databases: the 'live' ones (modbus in memory and config on SD) and 'backup' ones (on USB stick). When you need to do an insert/update, take all of the data needed for the modification, stuff it into a structure, and stick that structure on a spooling queue. Then, all you need to do is have a thread that pulls items off the spooling queue and applies them to the USB stick's version of the database. * Inserts to the 'live' database aren't blocked by the slow USB stick * People doing reporting can report from the USB stick version. Inserts that come in while the reporting thread has a shared lock will just pile up in the spool, then they can all get processed when the report is done. * Your external backup will probably actually get updated more often. > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- -- Stevie-O Real programmers use COPY CON PROGRAM.EXE ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing double into a socket file
John, My server uses epoll( ) and runs non-blocking in a single thread. I did some google and found out that people who need a very fast server that is highly scalable are using this model. non-blocking seems to be more complicated at first glance but it actually makes the server design much simpler. I am planning to release my code as open source when it is more complete. Thanks, -Alex On Tue, Jun 10, 2008 at 8:48 PM, John Stanton <[EMAIL PROTECTED]> wrote: > We use an application server I wrote which handles HTTP, serves file and > has embedded Sqlite for the RPCs. The RPC can deliver its result either > in XML for widely distributed applications or as JSON if it is > responding to a WWW browser in AJAX mode. > > We keep a local library of SQL RPCs so that SQl never appears on the > network and we have immunity from injection attacks. It also means that > we can cache compiled SQL, a useful performance win. > > We use the Expat parser in remote programs using the XML format. A > wrapper makes it a verifying parser to ensure well formed XML. > > The server is multi threaded and maintains a pool of live threads so it > can respond quickly and assign multiple threads to one browser > connection. Shared cache in Sqlite and some extra caching to maintain > multiple open databases and results makes Sqlite behave like a simple to > use enterprise DB server, but without the overhead of extra processes. > We use mutexes for synchronization, set up as read and write locks and > avoid the POSIX file locks. > > We installed Javascript as a procedural language to be used by Sqlite > instead of PL/SQL but that is not a great success (v. slow) and we are > going to experiment with using Python. > > Based on our experience you should be very happy with your Sqlite based > RPC capability. > > Alex Katebi wrote: > > John & John, > > > >Actually my API used to be XML using SCEW a DOM like XML parser that > uses > > Expat. > > > >For my particular application RPC made more sense to me. What could be > > easier than a function call? Another advantage was that I did not have to > > create any functions. I am just using SQLite's C API. Now the users of my > > application can query any table on the server side using select. Since my > > application is a network server, and network debugging capability is > > crucial. > > The only ugliness is that select locks the tables. I wish D. Hipp would > give > > us an option for pStmt to create a temporary table of the select result > set > > and delete that temp table after finalize automatically. This way a > client > > can sit on a prepare/step for a long time. > > > >I solved the endian issue pretty easy by sending the type code. > > > > Thanks, > > -Alex > > > > > > On Tue, Jun 10, 2008 at 3:07 PM, John Elrick <[EMAIL PROTECTED]> > > wrote: > > > >> Alex Katebi wrote: > >>> Yes I need to do it as 8 byte buffer. Convert the endianess to the > >> network > >>> then back to host for 8 byte integer. > >>> I think XML is great for command validation and CLI auto typing, help > >> etc. > >>> Besides parsing issue, XML can not handle binary data directly. > >>> > >> As John pointed out, XML is not intended to handle binary data > >> directly. We use XML as a transfer medium for binary data and simply > >> base64 encode it before encapsulation. > >> > >> > >> John Elrick > >> Fenestra Technologies > >> ___ > >> 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-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] How to build an extension that gets autoloaded
Try this link. http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions On Tue, Jun 10, 2008 at 12:39 AM, Stephen Woodbridge < [EMAIL PROTECTED]> wrote: > Hi all, > > I was reading through the sqlite source and noticed that there appears > to be an ability to build and extension that is statically linked and > autoloaded, but I can not find any details on how to do this. > > For example, if I want to build an application that uses the rtree > extension on Linux, how would I compile and link link it and get it > autoloaded. > > For a simple example, maybe just an explanation of how to do this for > the sqlite3 shell would suffice. It makes it much simpler to statically > link these into the application when you want to deploy it, because > there are less files and paths and things that can go wrong if the user > messes with things and since it is a dedicated application it will > always need the extension. > > If this is already documented somewhere, please just point to it. > > Thanks, > -Stephen Woodbridge >http://imaptools.com/ > ___ > 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] Writing double into a socket file
We use an application server I wrote which handles HTTP, serves file and has embedded Sqlite for the RPCs. The RPC can deliver its result either in XML for widely distributed applications or as JSON if it is responding to a WWW browser in AJAX mode. We keep a local library of SQL RPCs so that SQl never appears on the network and we have immunity from injection attacks. It also means that we can cache compiled SQL, a useful performance win. We use the Expat parser in remote programs using the XML format. A wrapper makes it a verifying parser to ensure well formed XML. The server is multi threaded and maintains a pool of live threads so it can respond quickly and assign multiple threads to one browser connection. Shared cache in Sqlite and some extra caching to maintain multiple open databases and results makes Sqlite behave like a simple to use enterprise DB server, but without the overhead of extra processes. We use mutexes for synchronization, set up as read and write locks and avoid the POSIX file locks. We installed Javascript as a procedural language to be used by Sqlite instead of PL/SQL but that is not a great success (v. slow) and we are going to experiment with using Python. Based on our experience you should be very happy with your Sqlite based RPC capability. Alex Katebi wrote: > John & John, > >Actually my API used to be XML using SCEW a DOM like XML parser that uses > Expat. > >For my particular application RPC made more sense to me. What could be > easier than a function call? Another advantage was that I did not have to > create any functions. I am just using SQLite's C API. Now the users of my > application can query any table on the server side using select. Since my > application is a network server, and network debugging capability is > crucial. > The only ugliness is that select locks the tables. I wish D. Hipp would give > us an option for pStmt to create a temporary table of the select result set > and delete that temp table after finalize automatically. This way a client > can sit on a prepare/step for a long time. > >I solved the endian issue pretty easy by sending the type code. > > Thanks, > -Alex > > > On Tue, Jun 10, 2008 at 3:07 PM, John Elrick <[EMAIL PROTECTED]> > wrote: > >> Alex Katebi wrote: >>> Yes I need to do it as 8 byte buffer. Convert the endianess to the >> network >>> then back to host for 8 byte integer. >>> I think XML is great for command validation and CLI auto typing, help >> etc. >>> Besides parsing issue, XML can not handle binary data directly. >>> >> As John pointed out, XML is not intended to handle binary data >> directly. We use XML as a transfer medium for binary data and simply >> base64 encode it before encapsulation. >> >> >> John Elrick >> Fenestra Technologies >> ___ >> 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction locks w/ multiple DBs open/ATTACHed(reposted)
On Jun 10, 2008, at 7:55 PM, Igor Tandetnik wrote: > D. Richard Hipp <[EMAIL PROTECTED]> wrote: >> On Jun 10, 2008, at 4:04 PM, Robert Lehr wrote: >>> Specifically, does SQLite lock the DBs incrementally, as they are >>> accessed within the transaction? Or does it lock ALL DBs? >> >> Databases are locked as needed - incrementally. > > So this is another possible source of deadlocks, right? One connection > locks database A, then tries to lock database B. At the same time, > another connection locks database B, then tries to lock database A. > > Is such a deadlock detected, in the same way a deadlock caused by two > read-then-write transactions on a single database is? In the latter > case, if I recall correctly, the deadlock detection causes the > statement > to fail with SQLITE_BUSY immediately, without calling the busy handler > (since waiting won't help any). In this case, both processes wait because there is still the possibility that one will abandon its lock attempt and commit what it has already, allowing the other to proceed. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction locks w/ multiple DBs open/ATTACHed(reposted)
D. Richard Hipp <[EMAIL PROTECTED]> wrote: > On Jun 10, 2008, at 4:04 PM, Robert Lehr wrote: >> Specifically, does SQLite lock the DBs incrementally, as they are >> accessed within the transaction? Or does it lock ALL DBs? > > Databases are locked as needed - incrementally. So this is another possible source of deadlocks, right? One connection locks database A, then tries to lock database B. At the same time, another connection locks database B, then tries to lock database A. Is such a deadlock detected, in the same way a deadlock caused by two read-then-write transactions on a single database is? In the latter case, if I recall correctly, the deadlock detection causes the statement to fail with SQLITE_BUSY immediately, without calling the busy handler (since waiting won't help any). Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] simultaneous transactions
Dave Dyer <[EMAIL PROTECTED]> wrote: > I don't understand why the following transaction > behavior is correct or necessary. The question > involves two simultaneous transactions on the same > database > > Process 1 Process 2 > > BEGIN > > BEGIN > > insert... > > insert... fails "locked" > > end also fails "locked" > > It seems that the end of a transaction can > fail even if all the intermediate actions > succeeded. Process 2 holds a SHARED lock on the database, by virtue of still having an open transaction. Process 1 cannot write to the database file while there are outstanding readers. Note that your "intermediate actions succeeded" statement is somewhat misleading. SQLite holds as many modifications as possible in a memory cache, and only writes them to disk when a) transaction is committed, or b) the cache overflows and has to spill to disk. So while an insert performed by Process 1 nominally succeeded, it most likely didn't actually write anything to disk. It's the END statement that does this - of course it may fail. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with sqlite overall or not?
I just noticed that you are returning from your function before finalize.
On Tue, Jun 10, 2008 at 10:12 AM, piotro <[EMAIL PROTECTED]> wrote:
> Hello. I really need help with this, because Im almost out of
> ideas here. Help please!
>
> sqldata is a vector< vector < char* > >
> row is a vector < char* >
>
> sqldata sql3wrapper::pobierzPodmiot(){
>const char* tail;
>sqlite3_stmt* statement;
>
>std::string query = squery("SELECT * FROM %;",PODMIOTY_TABLE);
>
>sqlite3_prepare_v2(interface,query.c_str(),-1,&statement,&tail);
>sqldata data;
>for(int a=0;sqlite3_step(statement)!=101;a++){
>row set;
>data.push_back(set);
>for(int az=1; azdata[a].push_back((char*)sqlite3_column_text(statement,az));
>}
>std::cout<<(data[a])[2]<}
>return data;
>sqlite3_finalize(statement);
> }
>
> this does cout properly. But when I'm using it in a different place:
>
> int main(){
> sqldata est = sql3wrapper().pobierzPodmiot();
>
>row one = est[0];
>row two = est[1];
>row three = est[2];
>
>std::cout<<'\n'
> the data is couted properly, but the values are just the values coming
> from the last row.
>
> I have three rows of data in the sqlite database. Then I use the
> pobierzPodmiot
> function, get the data correctly, but when the function is used
> somewhere else,
> it hickups pretty much.
>
> Can someone come up with a solution or something, coping with this had
> been tiresome.
>
> Cheers!
>
> ___
> 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] Problem with sqlite overall or not?
For starters sqlite3_column_count( ) should be after prepare once.
You don't need to do it for every step.
The other thing is do you have to use STL when you can do everything by
using SQLite?
Tell us in English what you want to accomplish with your code. We can come
up with a pure SQLite solution.
Thanks,
-Alex
On Tue, Jun 10, 2008 at 10:12 AM, piotro <[EMAIL PROTECTED]> wrote:
> Hello. I really need help with this, because Im almost out of
> ideas here. Help please!
>
> sqldata is a vector< vector < char* > >
> row is a vector < char* >
>
> sqldata sql3wrapper::pobierzPodmiot(){
>const char* tail;
>sqlite3_stmt* statement;
>
>std::string query = squery("SELECT * FROM %;",PODMIOTY_TABLE);
>
>sqlite3_prepare_v2(interface,query.c_str(),-1,&statement,&tail);
>sqldata data;
>for(int a=0;sqlite3_step(statement)!=101;a++){
>row set;
>data.push_back(set);
>for(int az=1; azdata[a].push_back((char*)sqlite3_column_text(statement,az));
>}
>std::cout<<(data[a])[2]<}
>return data;
>sqlite3_finalize(statement);
> }
>
> this does cout properly. But when I'm using it in a different place:
>
> int main(){
> sqldata est = sql3wrapper().pobierzPodmiot();
>
>row one = est[0];
>row two = est[1];
>row three = est[2];
>
>std::cout<<'\n'
> the data is couted properly, but the values are just the values coming
> from the last row.
>
> I have three rows of data in the sqlite database. Then I use the
> pobierzPodmiot
> function, get the data correctly, but when the function is used
> somewhere else,
> it hickups pretty much.
>
> Can someone come up with a solution or something, coping with this had
> been tiresome.
>
> Cheers!
>
> ___
> 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] Accessibility dependend on command and folder
On Jun 10, 2008, at 5:24 PM, maximi wrote: > > Hi > im using php in a webapp to access a sqlite database. now since > hours i'm > confronted with a weird error: > > i can access my database just fine, as long as i use commands as > simple as > "select * from testtable where a='b'" > as soon as there comes any 'distinct' or 'order by' or 'group by' > into play, > i always get the error "unable to open database file", error number > is 14 > (which should be just that) and there is another error code: hy000 > > anybody an idea?? > Probably it is unable to open a temporary file in /tmp or /var/tmp or wherever temporary files are suppose to live on your system. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction locks w/ multiple DBs open/ATTACHed (reposted)
On Jun 10, 2008, at 4:04 PM, Robert Lehr wrote: > I am reposting this question b/c it received no reply previously and > the > answers to my questions will significantly affect our application's > design. > > My current question focuses on SQLite's locking policy for > transactions > on connections which have multiple DBs are open/ATTACHed. > > Specifically, does SQLite lock the DBs incrementally, as they are > accessed within the transaction? Or does it lock ALL DBs? > > If a transaction accesses only ONE DB, will all DBs be locked for that > transaction? what locking sequence does SQLite execute? > > If a transaction reads from multiple DBs but WRITES to only one DB, > what > locking sequence does SQLite execute? > Databases are locked as needed - incrementally. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with sqlite overall or not?
Wednesday, June 11, 2008, 12:12:33 AM, you wrote:
p> Hello. I really need help with this, because Im almost out of
p> ideas here. Help please!
p> sqldata is a vector< vector < char* > >
p> row is a vector < char* >
p> sqldata sql3wrapper::pobierzPodmiot(){
p> const char* tail;
p> sqlite3_stmt* statement;
p> std::string query = squery("SELECT * FROM %;",PODMIOTY_TABLE);
p>
p> sqlite3_prepare_v2(interface,query.c_str(),-1,&statement,&tail);
p> sqldata data;
p> for(int a=0;sqlite3_step(statement)!=101;a++){
p> row set;
p> data.push_back(set);
p> for(int az=1; az
p> data[a].push_back((char*)sqlite3_column_text(statement,az));
p> }
p> std::cout<<(data[a])[2]< }
p> return data;
p> sqlite3_finalize(statement);
p> }
p> this does cout properly. But when I'm using it in a different place:
Try moving:
sqlite3_finalize(statement);
return data;
I would also populate set and then push that to data. And I would use
one of the C++ wrappers seeing that you are using C++.
--
Best regards,
Neville Franks, http://www.surfulater.com http://blog.surfulater.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing double into a socket file
Dennis, My servers main connection itself is in :memory:, but your suggestion will still work. However there are two things I don't like about this method. One is that I need to parse the prepared statments and prefix queries, and replace the table name with the temp table name in the surrogate query. Two is that if the client does lots of queries before disconnecting from the server it can waste whole lot of memory. Three is that it is a hack. However, server can keep track of client temp tables and drop them after finalize. Parsing might not be as bad as I think. Right now this seems to be the only choice. Thanks, -Alex On Tue, Jun 10, 2008 at 4:33 PM, Dennis Cote <[EMAIL PROTECTED]> wrote: > Alex Katebi wrote: > > The only ugliness is that select locks the tables. I wish D. Hipp would > give > > us an option for pStmt to create a temporary table of the select result > set > > and delete that temp table after finalize automatically. This way a > client > > can sit on a prepare/step for a long time. > > > > Alex, > > Your application can do this by itself without any changes to the SQLite > core. You can attach a :memory: database as tmp to your main database, > then prefix your client's query with "create table tmp.result as", and > then return the result of a surrogate query "select * from tmp.result" > instead of the actual result of the client's query. This will only hold > the lock on the main database while the temp table is created, since > memory database don't use any locking because they are private to a > single connection. The client can then scan through the surrogate > results at its leisure. When the client is done you can close the memory > database. > > HTH > Dennis Cote > ___ > 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] simultaneous transactions
I don't understand why the following transaction behavior is correct or necessary. The question involves two simultaneous transactions on the same database Process 1 Process 2 BEGIN BEGIN insert... insert... fails "locked" end also fails "locked" It seems that the end of a transaction can fail even if all the intermediate actions succeeded. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] quick transaction question
Yes the BEGIN Transaction may get a BUSY/LOCKED error. If the BEGIN fails then do not commit, If you do commit you'll get an error indicating that the commit failed due to no active transaction Gregor Brandt <[EMAIL PROTECTED]> wrote: When doing transactional commits I do int error = sqlite3_Exec( .., "BEGIN TRANSACTION;", ... ) // can this ever return an error? int error = sqlite3_exect( ..., "some update command", ... ) if I get an error code, must I never do the "COMMIT;". what if I do? Thanks Gregor ___ 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] Accessibility dependend on command and folder
Hi im using php in a webapp to access a sqlite database. now since hours i'm confronted with a weird error: i can access my database just fine, as long as i use commands as simple as "select * from testtable where a='b'" as soon as there comes any 'distinct' or 'order by' or 'group by' into play, i always get the error "unable to open database file", error number is 14 (which should be just that) and there is another error code: hy000 anybody an idea?? thanks a lot! Max Fechner -- View this message in context: http://www.nabble.com/Accessibility-dependend-on-command-and-folder-tp17764999p17764999.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] transaction locks w/ multiple DBs open/ATTACHed (reposted)
Robert Lehr wrote: > I am reposting this question b/c it received no reply previously and the > answers to my questions will significantly affect our application's > design. > > My current question focuses on SQLite's locking policy for transactions > on connections which have multiple DBs are open/ATTACHed. > > Specifically, does SQLite lock the DBs incrementally, as they are > accessed within the transaction? Or does it lock ALL DBs? > > If a transaction accesses only ONE DB, will all DBs be locked for that > transaction? what locking sequence does SQLite execute? > > If a transaction reads from multiple DBs but WRITES to only one DB, what > locking sequence does SQLite execute? > Other useful information may be found here http://www.sqlite.org/lockingv3.html HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] transaction locks w/ multiple DBs open/ATTACHed (reposted)
Robert Lehr wrote: > I am reposting this question b/c it received no reply previously and the > answers to my questions will significantly affect our application's > design. > > My current question focuses on SQLite's locking policy for transactions > on connections which have multiple DBs are open/ATTACHed. > > Specifically, does SQLite lock the DBs incrementally, as they are > accessed within the transaction? Or does it lock ALL DBs? > > If a transaction accesses only ONE DB, will all DBs be locked for that > transaction? what locking sequence does SQLite execute? > > If a transaction reads from multiple DBs but WRITES to only one DB, what > locking sequence does SQLite execute? > The answers to your questions are probably in here http://www.sqlite.org/atomiccommit.html HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing double into a socket file
Alex Katebi wrote: > The only ugliness is that select locks the tables. I wish D. Hipp would give > us an option for pStmt to create a temporary table of the select result set > and delete that temp table after finalize automatically. This way a client > can sit on a prepare/step for a long time. > Alex, Your application can do this by itself without any changes to the SQLite core. You can attach a :memory: database as tmp to your main database, then prefix your client's query with "create table tmp.result as", and then return the result of a surrogate query "select * from tmp.result" instead of the actual result of the client's query. This will only hold the lock on the main database while the temp table is created, since memory database don't use any locking because they are private to a single connection. The client can then scan through the surrogate results at its leisure. When the client is done you can close the memory database. HTH Dennis Cote ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing double into a socket file
John & John, Actually my API used to be XML using SCEW a DOM like XML parser that uses Expat. For my particular application RPC made more sense to me. What could be easier than a function call? Another advantage was that I did not have to create any functions. I am just using SQLite's C API. Now the users of my application can query any table on the server side using select. Since my application is a network server, and network debugging capability is crucial. The only ugliness is that select locks the tables. I wish D. Hipp would give us an option for pStmt to create a temporary table of the select result set and delete that temp table after finalize automatically. This way a client can sit on a prepare/step for a long time. I solved the endian issue pretty easy by sending the type code. Thanks, -Alex On Tue, Jun 10, 2008 at 3:07 PM, John Elrick <[EMAIL PROTECTED]> wrote: > Alex Katebi wrote: > > Yes I need to do it as 8 byte buffer. Convert the endianess to the > network > > then back to host for 8 byte integer. > > I think XML is great for command validation and CLI auto typing, help > etc. > > Besides parsing issue, XML can not handle binary data directly. > > > > As John pointed out, XML is not intended to handle binary data > directly. We use XML as a transfer medium for binary data and simply > base64 encode it before encapsulation. > > > John Elrick > Fenestra Technologies > ___ > 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] transaction locks w/ multiple DBs open/ATTACHed (reposted)
I am reposting this question b/c it received no reply previously and the answers to my questions will significantly affect our application's design. My current question focuses on SQLite's locking policy for transactions on connections which have multiple DBs are open/ATTACHed. Specifically, does SQLite lock the DBs incrementally, as they are accessed within the transaction? Or does it lock ALL DBs? If a transaction accesses only ONE DB, will all DBs be locked for that transaction? what locking sequence does SQLite execute? If a transaction reads from multiple DBs but WRITES to only one DB, what locking sequence does SQLite execute? -rlehr Robert Lehr ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (juste a test sorry)
looks like it works again, sorry for the inconvenience :) toms a écrit : > (juste a test sorry) > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] (juste a test sorry)
(juste a test sorry) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] prepare peformances
(juste a test sorry)
Dennis Cote a écrit :
>
> I am forwarding this to the list in the hope that someone else will
> have a good idea since the OP, toms, is having trouble posting himself.
>
> Dennis Cote
>
> toms wrote:
>> ok here is the code I use for the statements:
>>
>> here is the SQL:
>>
>> "UPDATE Statistics_Players"
>> " SET "
>> " iMVP = ?,"
>> " Inflicted_iPasses = ?,"
>> " Inflicted_iInterceptions = ?,"
>> " Inflicted_iCatches = ?,"
>> " Inflicted_iTouchdowns = ?,"
>> " Inflicted_iKO = ?,"
>> " Inflicted_iCasualties = ?,"
>> " Inflicted_iDead = ?"
>> " WHERE idPlayer_Listing=?"
>>
>> I init the statement like this (where _szSQL is the previous string)
>>
>> const char*szTail = 0;
>> int rc = sqlite3_prepare(m_pDB->GetSqliteDB(), _szSQL,
>> StrLen(_szSQL), &m_pStatement, &szTail);
>> if( rc != SQLITE_OK )
>> {
>> SDK_ASSERTMSG(0, "Query Init error: %s\n",
>> sqlite3_errmsg(m_pDB->GetSqliteDB()));
>> return;
>> }
>>
>> then, when I want to use the query, I retrieve it (it is kept in a
>> wrapper CQuery class), and then bind every params:
>>
>> CQueryPlayersWriteStats* pQuery =
>> GetDB()->GetQueryPlayersWriteStats();
>> pQuery->Bind(CQueryPlayersWriteStats::Params::PLAYERID ,
>> (u32)GetID() );
>> pQuery->Bind(CQueryPlayersWriteStats::Params::MVP ,
>> (u32)statsPlayer.m_uMVP);
>> pQuery->Bind(CQueryPlayersWriteStats::Params::PASSES,
>> (u32)statsPlayer.m_uNbAccuratePass );
>> pQuery->Bind(CQueryPlayersWriteStats::Params::INTERCEPTIONS ,
>> (u32)statsPlayer.m_uNbInterception );
>> pQuery->Bind(CQueryPlayersWriteStats::Params::CATCHES ,
>> (u32)statsPlayer.m_uNbReception);
>> pQuery->Bind(CQueryPlayersWriteStats::Params::TD,
>> (u32)statsPlayer.m_uNbTouchDown);
>> pQuery->Bind(CQueryPlayersWriteStats::Params::KO,
>> (u32)statsPlayer.m_uNbKo );
>> pQuery->Bind(CQueryPlayersWriteStats::Params::CASUALTIES,
>> (u32)statsPlayer.m_uNbCasualties );
>> pQuery->Bind(CQueryPlayersWriteStats::Params::DEAD ,
>> (u32)statsPlayer.m_uNbDeath);
>> pQuery->SingleExec();
>>
>> where each CQueryPlayersWriteStats::Params is an enum value between 1
>> and 8
>>
>> And here is the code of the bind method:
>>
>> void CQuery::Bind(u32 _uParam, s32 _iValue)
>> {
>> int rc = sqlite3_bind_int(m_pStatement, (int)_uParam, _iValue);
>> if( rc != SQLITE_OK )
>> {
>> SDK_ASSERTMSG(0, "Query Bind error: %s\n",
>> sqlite3_errmsg(m_pDB->GetSqliteDB()));
>> return;
>> }
>> }
>>
>> And I have many versions of the bond method:
>>
>> voidBind(u32 _uParam, s32 _iValue);
>> voidBind(u32 _uParam, u32 _uValue);
>> voidBind(u32 _uParam, f32 _fValue);
>> voidBind(u32 _uParam, const char* _szValue);
>>
>> And after the bond, I only call the Step once:
>>
>> void CQuery::SingleExec()
>> {
>> Reset();
>>
>> int rc = sqlite3_step(m_pStatement);
>>
>> if (rc == SQLITE_ROW)
>> {
>> SDK_WARNING(0, "Seems like a query would be more
>> appropriate");
>> return;
>> }
>>
>> if (rc != SQLITE_DONE)
>> {
>> SDK_WARNING(0, "Could not execute SingleExec!");
>> return;
>> }
>>
>> Reset();
>> }
>>
>> The class CQueryPlayersWriteStats inheritates from CQuery
>>
>> The only doubt I have is about calling Reset twice in the SingleExec
>> method. It was more secure for me to do this.
>> Any idea?
>
> That isn't necessary. You should reset either before or after (I would
> suggest before), but I doubt that is the cause of the slowdown either.
>
>> As I still cannot send on the mailling list, please feel free to
>> forward this on it if you want :)
>>
>> Thanks for the help! :-)
>>
>>
>> Dennis Cote a écrit :
>>> toms wrote:
this is not easily doable: the database is on a server, in order to
change fields I have to change it on the server :/
>>>
>>> I don't need to see the database itself, only the code that accesses
>>> the database through SQLite. I thought you were concerned about
>>> leaking info about the database by disclosing table and field names
>>> etc.
>>>
>>>
yes: I only prepare it once, and then use this prepared statement
to bind parameters.
Then step, but only once (because it is an UPDATE) and then reset
Maybe the gain is not so much when not used in a SELECT?
>>>
>>> That should not be the case. The sqlite3_prepare() call does the SQL
>>> language parsing and VDBE code generation for the query once. The
>>> VDBE code is then executed multiple times.
>>>
>>> When using sqlite3_exec() the string contai
Re: [sqlite] Writing double into a socket file
Alex Katebi wrote: > Yes I need to do it as 8 byte buffer. Convert the endianess to the network > then back to host for 8 byte integer. > I think XML is great for command validation and CLI auto typing, help etc. > Besides parsing issue, XML can not handle binary data directly. > As John pointed out, XML is not intended to handle binary data directly. We use XML as a transfer medium for binary data and simply base64 encode it before encapsulation. John Elrick Fenestra Technologies ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] quick transaction question
Gregor Brandt <[EMAIL PROTECTED]> wrote: > When doing transactional commits I do > > int error = sqlite3_Exec( .., "BEGIN TRANSACTION;", ... ) // can > this ever return an error? No, as far as I know. It just sets a flag in an internal data structure associated with database connection. > int error = sqlite3_exect( ..., "some update command", ... ) > > if I get an error code, must I never do the "COMMIT;". what if I do? This depends on a specific error, and (in case of constraint violations) a conflict resolution clause mentioned in the command. By default, when a statement fails, any changes it might have made are rolled back, returning the database to the state right before the statement was issued. In your example, no changes were made before the statement: at this point, it makes no difference whether you commit or roll back the transaction. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing double into a socket file
Hi, Alex Katebi wrote: > I am trying to implement remote procedure calls (RPC) for SQLite API to be > used in my application. > In particular sqlite3_column_double( ) returns a floating point double. > How can I write this double value into a TCP socket? > I have tried writing 8 bytes as integer values but the received valued at > the other end of the socket is incorrect. > I don't have a lot of experience with real numbers. Can someone help? > Have a look at YAMI (http://www.msobczak.com/prog/yami/). It has the ability to send/receive following data types: eString = 1; /* ASCII string */ eWString = 2; /* wide string*/ eInt = 3; /* integer*/ eDouble = 4; /* double */ eByte= 5; /* opaque byte*/ eBinary = 6; /* opaque binary data */ > Thanks, > -Alex -- Regards, Hakki Dogusan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] quick transaction question
When doing transactional commits I do int error = sqlite3_Exec( .., "BEGIN TRANSACTION;", ... ) // can this ever return an error? int error = sqlite3_exect( ..., "some update command", ... ) if I get an error code, must I never do the "COMMIT;". what if I do? Thanks Gregor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] prepare peformances
I am forwarding this to the list in the hope that someone else will have
a good idea since the OP, toms, is having trouble posting himself.
Dennis Cote
toms wrote:
> ok here is the code I use for the statements:
>
> here is the SQL:
>
> "UPDATE Statistics_Players"
> " SET "
> " iMVP = ?,"
> " Inflicted_iPasses = ?,"
> " Inflicted_iInterceptions = ?,"
> " Inflicted_iCatches = ?,"
> " Inflicted_iTouchdowns = ?,"
> " Inflicted_iKO = ?,"
> " Inflicted_iCasualties = ?,"
> " Inflicted_iDead = ?"
> " WHERE idPlayer_Listing=?"
>
> I init the statement like this (where _szSQL is the previous string)
>
> const char*szTail = 0;
> int rc = sqlite3_prepare(m_pDB->GetSqliteDB(), _szSQL,
> StrLen(_szSQL), &m_pStatement, &szTail);
> if( rc != SQLITE_OK )
> {
> SDK_ASSERTMSG(0, "Query Init error: %s\n",
> sqlite3_errmsg(m_pDB->GetSqliteDB()));
> return;
> }
>
> then, when I want to use the query, I retrieve it (it is kept in a wrapper
> CQuery class), and then bind every params:
>
> CQueryPlayersWriteStats* pQuery = GetDB()->GetQueryPlayersWriteStats();
> pQuery->Bind(CQueryPlayersWriteStats::Params::PLAYERID ,
> (u32)GetID() );
> pQuery->Bind(CQueryPlayersWriteStats::Params::MVP ,
> (u32)statsPlayer.m_uMVP);
> pQuery->Bind(CQueryPlayersWriteStats::Params::PASSES,
> (u32)statsPlayer.m_uNbAccuratePass );
> pQuery->Bind(CQueryPlayersWriteStats::Params::INTERCEPTIONS ,
> (u32)statsPlayer.m_uNbInterception );
> pQuery->Bind(CQueryPlayersWriteStats::Params::CATCHES ,
> (u32)statsPlayer.m_uNbReception);
> pQuery->Bind(CQueryPlayersWriteStats::Params::TD,
> (u32)statsPlayer.m_uNbTouchDown);
> pQuery->Bind(CQueryPlayersWriteStats::Params::KO,
> (u32)statsPlayer.m_uNbKo );
> pQuery->Bind(CQueryPlayersWriteStats::Params::CASUALTIES,
> (u32)statsPlayer.m_uNbCasualties );
> pQuery->Bind(CQueryPlayersWriteStats::Params::DEAD ,
> (u32)statsPlayer.m_uNbDeath);
> pQuery->SingleExec();
>
> where each CQueryPlayersWriteStats::Params is an enum value between 1 and 8
>
> And here is the code of the bind method:
>
> void CQuery::Bind(u32 _uParam, s32 _iValue)
> {
> int rc = sqlite3_bind_int(m_pStatement, (int)_uParam, _iValue);
> if( rc != SQLITE_OK )
> {
> SDK_ASSERTMSG(0, "Query Bind error: %s\n",
> sqlite3_errmsg(m_pDB->GetSqliteDB()));
> return;
> }
> }
>
> And I have many versions of the bond method:
>
> voidBind(u32 _uParam, s32 _iValue);
> voidBind(u32 _uParam, u32 _uValue);
> voidBind(u32 _uParam, f32 _fValue);
> voidBind(u32 _uParam, const char* _szValue);
>
> And after the bond, I only call the Step once:
>
> void CQuery::SingleExec()
> {
> Reset();
>
> int rc = sqlite3_step(m_pStatement);
>
> if (rc == SQLITE_ROW)
> {
> SDK_WARNING(0, "Seems like a query would be more appropriate");
> return;
> }
>
> if (rc != SQLITE_DONE)
> {
> SDK_WARNING(0, "Could not execute SingleExec!");
> return;
> }
>
> Reset();
> }
>
> The class CQueryPlayersWriteStats inheritates from CQuery
>
> The only doubt I have is about calling Reset twice in the SingleExec method.
> It was more secure for me to do this.
> Any idea?
That isn't necessary. You should reset either before or after (I would
suggest before), but I doubt that is the cause of the slowdown either.
> As I still cannot send on the mailling list, please feel free to forward this
> on it if you want :)
>
> Thanks for the help! :-)
>
>
> Dennis Cote a écrit :
>> toms wrote:
>>> this is not easily doable: the database is on a server, in order to change
>>> fields I have to change it on the server :/
>>
>> I don't need to see the database itself, only the code that accesses the
>> database through SQLite. I thought you were concerned about leaking info
>> about the database by disclosing table and field names etc.
>>
>>
>>> yes: I only prepare it once, and then use this prepared statement to bind
>>> parameters.
>>> Then step, but only once (because it is an UPDATE) and then reset
>>> Maybe the gain is not so much when not used in a SELECT?
>>>
>>
>> That should not be the case. The sqlite3_prepare() call does the SQL
>> language parsing and VDBE code generation for the query once. The VDBE code
>> is then executed multiple times.
>>
>> When using sqlite3_exec() the string containing the query is parsed, and
>> VDBE code is generated and then executed for each call.
>>
>> It doesn't really matter what type of a statement is being executed. The
>> benefit is a
Re: [sqlite] Writing double into a socket file
Alex, the whole point of using XML is to avoid using binary data in an RPC situation and to tag the data and thus make extensions less traumatic. The radix change overhead is insignificant compared to the network cost and the bonus is complete freedom from endian dilemmas. The biggest performance improvement we get is by careful use of sockets and avoiding process and thread creation and deletion. For example using sendfile or TransmitFile avoids multiple levels of buffer shadowing. Our RPC implementation is fast and robust and not bloated like XML/RPC. Alex Katebi wrote: > Yes I need to do it as 8 byte buffer. Convert the endianess to the network > then back to host for 8 byte integer. > I think XML is great for command validation and CLI auto typing, help etc. > Besides parsing issue, XML can not handle binary data directly. > > > On Tue, Jun 10, 2008 at 12:31 PM, John Stanton <[EMAIL PROTECTED]> wrote: > >> Can you explain how you are trying to write to the socket and how you >> are receiving? >> >> If you use write or send you just supply a pointer to the value and a >> length to write, viz - written = write(sokfd, (char *)fptr, 8); where >> fptr is a pointer to your floating point number. >> >> We use Sqlite embedded as RPCs but actually encapsulate the data as XML >> and send floating point or other numbers in text form so that there can >> never be byte ordering or format issues. SQL also maps quite nicely to >> XML. The downside is the complexity of the XML parser at the receive end. >> >> Alex Katebi wrote: >>> I am trying to implement remote procedure calls (RPC) for SQLite API to >> be >>> used in my application. >>> In particular sqlite3_column_double( ) returns a floating point double. >>> How can I write this double value into a TCP socket? >>> I have tried writing 8 bytes as integer values but the received valued at >>> the other end of the socket is incorrect. >>> I don't have a lot of experience with real numbers. Can someone help? >>> >>> Thanks, >>> -Alex >> > ___ >>> 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-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] Writing double into a socket file
D. Hipp, I have since identified and fixed my problem, thanks to our user group! A lot of the networking gear like IP routers use big-endian machines since this is the format used by the network protocol control messages. PowerPC is an example. Thanks, -Alex On Mon, Jun 9, 2008 at 10:09 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > > On Jun 9, 2008, at 9:58 PM, Russell Leighton wrote: > > > > > On Jun 9, 2008, at 8:52 PM, Igor Tandetnik wrote: > > > >> "Alex Katebi" <[EMAIL PROTECTED]> > >> wrote in message > >> news:[EMAIL PROTECTED]<[EMAIL PROTECTED]> > >>> I am trying to implement remote procedure calls (RPC) for SQLite API > >>> to be used in my application. > >>> In particular sqlite3_column_double( ) returns a floating point > >>> double. How can I write this double value into a TCP socket? > >> > >> How do you write an int, or a string, into a socket? A double > >> wouldn't > >> be much different: at the end of the day, it's just an 8-byte buffer. > >> > > > > Aren' t there aligment and endian issues as well as potential floating > > point representations between platforms? > > > > > Endianness might be a problem, though these days it is becoming > increasingly difficult to find a big-endian processor. I don't think > different floating point representations are an issue since I am not > aware of any modern machine that does anything other than IEEE754. > SQLite assumes IEEE754 floating point representation, so if you have a > machine that uses something different, SQLite won't work on it (or at > least it won't have a compatible file format.) I have never yet heard > of this being a problem for anyone. > > D. Richard Hipp > [EMAIL PROTECTED] > > > > ___ > 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] Writing double into a socket file
Arjen, Thanks! On Tue, Jun 10, 2008 at 1:39 PM, Arjen Markus <[EMAIL PROTECTED]> wrote: > > I am going to guess yes. > > > > On Mon, Jun 9, 2008 at 9:58 PM, Russell Leighton > > <[EMAIL PROTECTED]> > > wrote: > > > > >> > >> Aren' t there aligment and endian issues as well as potential floating > >> point representations between platforms? > >> > > As most computers nowadays use the IEEE standard to represent > floating-point numbers this problem is much less complicated > than it used to be. I think only endian problems are left and > these are (relatively) easy to deal with. > > Regards, > > Arjen > ___ > 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] Writing double into a socket file
Yes I need to do it as 8 byte buffer. Convert the endianess to the network then back to host for 8 byte integer. I think XML is great for command validation and CLI auto typing, help etc. Besides parsing issue, XML can not handle binary data directly. On Tue, Jun 10, 2008 at 12:31 PM, John Stanton <[EMAIL PROTECTED]> wrote: > Can you explain how you are trying to write to the socket and how you > are receiving? > > If you use write or send you just supply a pointer to the value and a > length to write, viz - written = write(sokfd, (char *)fptr, 8); where > fptr is a pointer to your floating point number. > > We use Sqlite embedded as RPCs but actually encapsulate the data as XML > and send floating point or other numbers in text form so that there can > never be byte ordering or format issues. SQL also maps quite nicely to > XML. The downside is the complexity of the XML parser at the receive end. > > Alex Katebi wrote: > > I am trying to implement remote procedure calls (RPC) for SQLite API to > be > > used in my application. > > In particular sqlite3_column_double( ) returns a floating point double. > > How can I write this double value into a TCP socket? > > I have tried writing 8 bytes as integer values but the received valued at > > the other end of the socket is incorrect. > > I don't have a lot of experience with real numbers. Can someone help? > > > > Thanks, > > -Alex > > ___ > > 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing double into a socket file
> I am going to guess yes. > > On Mon, Jun 9, 2008 at 9:58 PM, Russell Leighton > <[EMAIL PROTECTED]> > wrote: > >> >> Aren' t there aligment and endian issues as well as potential floating >> point representations between platforms? >> As most computers nowadays use the IEEE standard to represent floating-point numbers this problem is much less complicated than it used to be. I think only endian problems are left and these are (relatively) easy to deal with. Regards, Arjen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing double into a socket file
I am going to guess yes. On Mon, Jun 9, 2008 at 9:58 PM, Russell Leighton <[EMAIL PROTECTED]> wrote: > > On Jun 9, 2008, at 8:52 PM, Igor Tandetnik wrote: > > > "Alex Katebi" <[EMAIL PROTECTED]> > > wrote in message > > news:[EMAIL PROTECTED]<[EMAIL PROTECTED]> > >> I am trying to implement remote procedure calls (RPC) for SQLite API > >> to be used in my application. > >> In particular sqlite3_column_double( ) returns a floating point > >> double. How can I write this double value into a TCP socket? > > > > How do you write an int, or a string, into a socket? A double wouldn't > > be much different: at the end of the day, it's just an 8-byte buffer. > > > > Aren' t there aligment and endian issues as well as potential floating > point representations between platforms? > > > ___ > 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] Best Practices
We have an application requirement for the following: 1) Maintain an in memory database of real time data. The main table mimics a Modbus register address scheme and once the table is created and records are inserted to create the table at startup, all writes are updates for the state machine. (app. 1000 records). Reads are through multiple Modbus TCP/IP protocol based devices which result in selects of up to 100 contiguous records at a time. 2) At periodic intervals based on events and time intervals a disk (SD card) database gets updated (inserts). This contains system parameters, and various different tables. 3) Occasionally the whole database on disk is written to an external media (USB) for backup purposes. 4) Through a web interface user(s) can export portions of a table by specifying type of data (table) and start and end time in csv format. (cgi scripts). All of the above is implemented currently using a "custom" format, and is really a pain to expand and modify. Items 1 & 2 are fairly straight forward to implement, they would be in the same executable as well. My original thought was to use the sqlite3 shell interface to accomplish 4. I am concerned a bit about items 3 and 4 though in regards to performance issues in that I can not afford not to service data requests that could come several times a second if database could be locked during operation of 3 and 4. Size of the database can grow to 1-2.4GB. I am open to any suggestions. Thanks in advance. andy ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing double into a socket file
Alex Katebi wrote: > Peter, >I am using my own implementation. I found RPC and others too complicated > to use and it did not give me enough control on the transport layer. I need > my socket to be non-blocking and I am using epoll( ) which is very > efficient. > XDR is a separate specification and can be used independently of RPC. Python has a library for dealing with it. XDR isn't simple, but it does provide the ability to pass complex structures and types between different kinds of machines. You can even create things like linked lists that can be sent over the wire. If that doesn't work for you, there are other options like XML, which comes with it's own complexity and cost. Cheers, Peter ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked error, while deleting
Hi Richard, I had SQLite 3.5.2 and my application has two threads one for write and one for read. Even the connection has "PRAGMA read_uncommited=ON" but the read thread still has problem with the connection is busy. So I don't know if the new version of SQLite allows to have the shared cache mode or not. Thanks, JP - Original Message From: D. Richard Hipp <[EMAIL PROTECTED]> To: General Discussion of SQLite Database Sent: Tuesday, June 10, 2008 7:30:09 AM Subject: Re: [sqlite] Database locked error, while deleting On Jun 10, 2008, at 10:18 AM, Shailesh Birari wrote: > Richard, > Just to go one step ahead, If there are independent connections, do > you > mean that there can exist multiple transactions at the same time? I > think not since the first transaction (write) will exclusively lock > the > database and the second transaction will get a busy error. Correct > me if > I am wrong. Will the behaviour differ in case of shared cache enabled > and disabled? > In shared cache mode, you can do "PRAGMA read_uncommited=ON" and then the reader will be able to see uncommitted data from the writer. And you won't ever get SQLITE_BUSY because the reader is unable to read the schema on account of a busy write transaction. An easier thing for you to do might be to use a single database connection. That way the reader will certainly never block waiting on the writer since they will be serialized. D. Richard Hipp [EMAIL PROTECTED] ___ 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] Writing double into a socket file
Igor, You are right. The answer is to use 8 byte buffer and don't forget to account for the endianess. I had a bug in my code. On Mon, Jun 9, 2008 at 8:52 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > "Alex Katebi" <[EMAIL PROTECTED]> > wrote in message > news:[EMAIL PROTECTED]<[EMAIL PROTECTED]> > > I am trying to implement remote procedure calls (RPC) for SQLite API > > to be used in my application. > > In particular sqlite3_column_double( ) returns a floating point > > double. How can I write this double value into a TCP socket? > > How do you write an int, or a string, into a socket? A double wouldn't > be much different: at the end of the day, it's just an 8-byte buffer. > > 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] Writing double into a socket file
Peter, I am using my own implementation. I found RPC and others too complicated to use and it did not give me enough control on the transport layer. I need my socket to be non-blocking and I am using epoll( ) which is very efficient. Thanks! -Alex On Mon, Jun 9, 2008 at 10:53 PM, Peter A. Friend <[EMAIL PROTECTED]> wrote: > Alex Katebi wrote: > > I am trying to implement remote procedure calls (RPC) for SQLite API to > be > > used in my application. > > In particular sqlite3_column_double( ) returns a floating point double. > > How can I write this double value into a TCP socket? > > I have tried writing 8 bytes as integer values but the received valued at > > the other end of the socket is incorrect. > > I don't have a lot of experience with real numbers. Can someone help? > > > Are you using something like ONC or Sun RPC, or are you rolling your > own? XDR already handles these types of problems for you. > > Peter > > ___ > 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] Writing double into a socket file
Can you explain how you are trying to write to the socket and how you are receiving? If you use write or send you just supply a pointer to the value and a length to write, viz - written = write(sokfd, (char *)fptr, 8); where fptr is a pointer to your floating point number. We use Sqlite embedded as RPCs but actually encapsulate the data as XML and send floating point or other numbers in text form so that there can never be byte ordering or format issues. SQL also maps quite nicely to XML. The downside is the complexity of the XML parser at the receive end. Alex Katebi wrote: > I am trying to implement remote procedure calls (RPC) for SQLite API to be > used in my application. > In particular sqlite3_column_double( ) returns a floating point double. > How can I write this double value into a TCP socket? > I have tried writing 8 bytes as integer values but the received valued at > the other end of the socket is incorrect. > I don't have a lot of experience with real numbers. Can someone help? > > Thanks, > -Alex > ___ > 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] Accessing SQLite from Javascript in Firefox
Ujval Mysore <[EMAIL PROTECTED]> wrote: > Can you please help me to understand the point - "Only script running > in chrome (basically, in FireFox extensions) can > access XPCOM components". Excuse for my ignorance. > > I was able to use SQLite in Internet Explorer using client side ADO. In order for you to use ADO, didn't you have to set up ODBC datasource first, outside the browser? And/or alter IE's security settings to allow running unsafe ActiveX controls? I would be surprised if IE lets any random HTML page access an arbitrary file on the user's machine in its default security configuration. FireFox allows signed scripts to request privileges to access XPCOM components (it's up to the user to grant or deny such privileges). See http://www.mozilla.org/projects/security/components/signed-scripts.html You want UniversalXPConnect privilege. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Accessing SQLite from Javascript in Firefox
What you are looking for is the HTML 5 spec from the WHATWG. This contains a section on using SQL, but it is not yet implemented in Firefox (I think Opera is the only browser that supports it, and only when 9.5 comes out). Cheers, Shawn Wilsher Mozilla Developer On Tue, Jun 10, 2008 at 10:32 AM, Ujval Mysore <[EMAIL PROTECTED]> wrote: > Hi Igor, > > Can you please help me to understand the point - "Only script running in > chrome (basically, in FireFox extensions) can > access XPCOM components". Excuse for my ignorance. > > I was able to use SQLite in Internet Explorer using client side ADO. I am > looking for a similar solution for Firefox. > Is there any way in which I can achieve this without using any Firefox > extensions? > > Thanks, > Ujval > > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik > Sent: Tuesday, June 10, 2008 5:32 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Accessing SQLite from Javascript in Firefox > > "Ujval Mysore" <[EMAIL PROTECTED]> > wrote in message > news:[EMAIL PROTECTED] >> Has anyone tried accessing SQLite from Javascript in Firefox? >> >> I found an Sqlite helper library for Mozilla at >> http://codesnippets.joyent.com/posts/show/1030 . >> >> But when I try to execute the same, firefox throws the following >> exception >> uncaught exception: Permission denied to get property >> UnnamedClass.classes >> >> Any clues what this exception means? > > Only script running in chrome (basically, in FireFox extensions) can > access XPCOM components, including Mozilla Storage ones. Script running > on an HTML page cannot, for security reasons. Hence "permission denied". > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > CAUTION - Disclaimer * > This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely > for the use of the addressee(s). If you are not the intended recipient, please > notify the sender by e-mail and delete the original message. Further, you are > not > to copy, disclose, or distribute this e-mail or its contents to any other > person and > any such actions are unlawful. This e-mail may contain viruses. Infosys has > taken > every reasonable precaution to minimize this risk, but is not liable for any > damage > you may sustain as a result of any virus in this e-mail. You should carry out > your > own virus checks before opening the e-mail or attachment. Infosys reserves the > right to monitor and review the content of all messages sent to or from this > e-mail > address. Messages sent to or from this e-mail address may be stored on the > Infosys e-mail system. > ***INFOSYS End of Disclaimer INFOSYS*** > ___ > 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] Accessing SQLite from Javascript in Firefox
Hi Igor, Can you please help me to understand the point - "Only script running in chrome (basically, in FireFox extensions) can access XPCOM components". Excuse for my ignorance. I was able to use SQLite in Internet Explorer using client side ADO. I am looking for a similar solution for Firefox. Is there any way in which I can achieve this without using any Firefox extensions? Thanks, Ujval -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Igor Tandetnik Sent: Tuesday, June 10, 2008 5:32 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Accessing SQLite from Javascript in Firefox "Ujval Mysore" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Has anyone tried accessing SQLite from Javascript in Firefox? > > I found an Sqlite helper library for Mozilla at > http://codesnippets.joyent.com/posts/show/1030 . > > But when I try to execute the same, firefox throws the following > exception > uncaught exception: Permission denied to get property > UnnamedClass.classes > > Any clues what this exception means? Only script running in chrome (basically, in FireFox extensions) can access XPCOM components, including Mozilla Storage ones. Script running on an HTML page cannot, for security reasons. Hence "permission denied". Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users CAUTION - Disclaimer * This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. ***INFOSYS End of Disclaimer INFOSYS*** ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked error, while deleting
On Jun 10, 2008, at 10:18 AM, Shailesh Birari wrote: > Richard, > Just to go one step ahead, If there are independent connections, do > you > mean that there can exist multiple transactions at the same time? I > think not since the first transaction (write) will exclusively lock > the > database and the second transaction will get a busy error. Correct > me if > I am wrong. Will the behaviour differ in case of shared cache enabled > and disabled? > In shared cache mode, you can do "PRAGMA read_uncommited=ON" and then the reader will be able to see uncommitted data from the writer. And you won't ever get SQLITE_BUSY because the reader is unable to read the schema on account of a busy write transaction. An easier thing for you to do might be to use a single database connection. That way the reader will certainly never block waiting on the writer since they will be serialized. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Accessing SQLite from Javascript in Firefox
Hi Lauri, Yes. I mean like in Google gears. But, I don't want to use Google gears api to do it. I want to access SQLite from plain javascript. Thanks, Ujval -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Lauri Ojansivu Sent: Tuesday, June 10, 2008 7:44 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Accessing SQLite from Javascript in Firefox 2008/6/10 Ujval Mysore <[EMAIL PROTECTED]>: > Hi, > > Has anyone tried accessing SQLite from Javascript in Firefox? > > I found an Sqlite helper library for Mozilla at > http://codesnippets.joyent.com/posts/show/1030 Hi, do you mean like in Google Gears? http://gears.google.com - Lauri ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users CAUTION - Disclaimer * This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. ***INFOSYS End of Disclaimer INFOSYS*** ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked error, while deleting
Richard, Just to go one step ahead, If there are independent connections, do you mean that there can exist multiple transactions at the same time? I think not since the first transaction (write) will exclusively lock the database and the second transaction will get a busy error. Correct me if I am wrong. Will the behaviour differ in case of shared cache enabled and disabled? -Shailesh. > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of D. Richard Hipp > Sent: Tuesday, June 10, 2008 5:28 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Database locked error, while deleting > > > On Jun 10, 2008, at 7:36 AM, Sabyasachi Ruj wrote: > > > Hi, > > > > I have a very big table with around 40,00, 000 rows. 4 columns. 220 > > MB. > > > > Now, I have two threads:- > > Thread1: Is deleting 7,00, 000 rows from the table. > > Thread2: Is doing SELECT on the same table. > > > > Now the problem is sometimes the sqlite3_prepare for the > SELECT query > > is failing with SQLITE_BUSY error. > > > > My questions: - > > 1. What is the best way to handle this error, and continue working > > normally? > > 2. Is there any documentation in sqlite3.org, which discuses the > > locking mechanism for DELETEs? Exacly in what phase of > DELETE sqlite > > creates the exclusive lock, so, the SELECT is failing? > > > > > Are the two threads using the same database connection, or > are they making separate and independent calls to sqlite3_open()? > > > D. Richard Hipp > [EMAIL PROTECTED] > > > > ___ > 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] Problem with sqlite overall or not?
Isn't this simply a vector problem?
You have a vector where you store char pointers. The pointers returned from
sqlite3_column_text will be destroyed when you step afaik.
Try making it a vector instead. Then you will implicitly make
copies of the text in your vector instead.
/Jonas
On Tue, Jun 10, 2008 at 4:12 PM, piotro <[EMAIL PROTECTED]> wrote:
> Hello. I really need help with this, because Im almost out of
> ideas here. Help please!
>
> sqldata is a vector< vector < char* > >
> row is a vector < char* >
>
> sqldata sql3wrapper::pobierzPodmiot(){
>const char* tail;
>sqlite3_stmt* statement;
>
>std::string query = squery("SELECT * FROM %;",PODMIOTY_TABLE);
>
>sqlite3_prepare_v2(interface,query.c_str(),-1,&statement,&tail);
>sqldata data;
>for(int a=0;sqlite3_step(statement)!=101;a++){
>row set;
>data.push_back(set);
>for(int az=1; azdata[a].push_back((char*)sqlite3_column_text(statement,az));
>}
>std::cout<<(data[a])[2]<}
>return data;
>sqlite3_finalize(statement);
> }
>
> this does cout properly. But when I'm using it in a different place:
>
> int main(){
> sqldata est = sql3wrapper().pobierzPodmiot();
>
>row one = est[0];
>row two = est[1];
>row three = est[2];
>
>std::cout<<'\n'
> the data is couted properly, but the values are just the values coming
> from the last row.
>
> I have three rows of data in the sqlite database. Then I use the
> pobierzPodmiot
> function, get the data correctly, but when the function is used
> somewhere else,
> it hickups pretty much.
>
> Can someone come up with a solution or something, coping with this had
> been tiresome.
>
> Cheers!
>
> ___
> 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] Accessing SQLite from Javascript in Firefox
2008/6/10 Ujval Mysore <[EMAIL PROTECTED]>: > Hi, > > Has anyone tried accessing SQLite from Javascript in Firefox? > > I found an Sqlite helper library for Mozilla at > http://codesnippets.joyent.com/posts/show/1030 Hi, do you mean like in Google Gears? http://gears.google.com - Lauri ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem with sqlite overall or not?
Hello. I really need help with this, because Im almost out of
ideas here. Help please!
sqldata is a vector< vector < char* > >
row is a vector < char* >
sqldata sql3wrapper::pobierzPodmiot(){
const char* tail;
sqlite3_stmt* statement;
std::string query = squery("SELECT * FROM %;",PODMIOTY_TABLE);
sqlite3_prepare_v2(interface,query.c_str(),-1,&statement,&tail);
sqldata data;
for(int a=0;sqlite3_step(statement)!=101;a++){
row set;
data.push_back(set);
for(int az=1; azhttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query Execution in SQLite
Igor Tandetnik wrote: > > "sqlite" <[EMAIL PROTECTED]> wrote > in message news:[EMAIL PROTECTED] >> We are using SQLite for our application development which would be >> deployed in a pocket pc.Here we are using a inner join query which >> takes different time during different executions > > What exactly does this mean? Are you running the exact same query > several times (if so, why?), or similar queries with different > parameters? How much different is the time between runs? Why is this a > problem in the first place? > > Igor Tandetnik > > Thanks for your reply, yes we are running the same query with the same > parametes only. The execution time gets differed for us when we tested for > the two different times so we tested the application with the same > location where the query gets called and found that the different > execution takes different time. and the execution time varies between 38 > secs for one time and 54 secs for the second time. Yes this problem is > related to the performance so we are concerned about this. > > Regards, > kartthi. > > > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/Query-Execution-in-SQLite-tp17748185p17754963.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] Database locked error, while deleting
Our PRAGMA cache_size = 4000. Is there any recommended way to calculate the cache size needed? On Tue, Jun 10, 2008 at 5:29 PM, Igor Tandetnik <[EMAIL PROTECTED]> wrote: > "Sabyasachi Ruj" <[EMAIL PROTECTED]> > wrote in message > news:[EMAIL PROTECTED]<[EMAIL PROTECTED]> > > Now, I have two threads:- > > Thread1: Is deleting 7,00, 000 rows from the table. > > Thread2: Is doing SELECT on the same table. > > > > Now the problem is sometimes the sqlite3_prepare for the SELECT query > > is failing with SQLITE_BUSY error. > > Are you sure it's the prepare that fails, and not step? Only the very > first prepare after opening the connection may fail this way, because > SQLite needs to read database schema at this point. > > > 1. What is the best way to handle this error, and continue working > > normally? > > Retry until you manage to go through. Or, prepare all the necessary > statements up front, before commencing any database work. Then prepare > won't fail (of course, step still may). > > > 2. Is there any documentation in sqlite3.org, which discuses the > > locking mechanism for DELETEs? Exacly in what phase of DELETE sqlite > > creates the exclusive lock, so, the SELECT is failing? > > http://sqlite.org/lockingv3.html > > EXCLUSIVE lock is obtained whenever SQLite needs to write to the > database file. This may happen when the transaction is committed, or > when the number of changes in a still-open transaction becomes so large > that it no longer fits in a memory cache and has to be spilled to disk. > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Sabyasachi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to build an extension that gets autoloaded
Hi Shane, Thank you for your response. I do need the rtree extension but I also have a need to some others and would like to know how to statically link in extensions anyway. -Steve Shane Harrelson wrote: > If you just need the rtree extension, I believe it will be included by > default in the next version of the core sqlite library. > > -Shane > > On Tue, Jun 10, 2008 at 12:39 AM, Stephen Woodbridge < > [EMAIL PROTECTED]> wrote: > >> Hi all, >> >> I was reading through the sqlite source and noticed that there appears >> to be an ability to build and extension that is statically linked and >> autoloaded, but I can not find any details on how to do this. >> >> For example, if I want to build an application that uses the rtree >> extension on Linux, how would I compile and link link it and get it >> autoloaded. >> >> For a simple example, maybe just an explanation of how to do this for >> the sqlite3 shell would suffice. It makes it much simpler to statically >> link these into the application when you want to deploy it, because >> there are less files and paths and things that can go wrong if the user >> messes with things and since it is a dedicated application it will >> always need the extension. >> >> If this is already documented somewhere, please just point to it. >> >> Thanks, >> -Stephen Woodbridge >>http://imaptools.com/ >> ___ >> 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query Execution in SQLite
"sqlite" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > We are using SQLite for our application development which would be > deployed in a pocket pc.Here we are using a inner join query which > takes different time during different executions What exactly does this mean? Are you running the exact same query several times (if so, why?), or similar queries with different parameters? How much different is the time between runs? Why is this a problem in the first place? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Accessing SQLite from Javascript in Firefox
"Ujval Mysore" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Has anyone tried accessing SQLite from Javascript in Firefox? > > I found an Sqlite helper library for Mozilla at > http://codesnippets.joyent.com/posts/show/1030 . > > But when I try to execute the same, firefox throws the following > exception > uncaught exception: Permission denied to get property > UnnamedClass.classes > > Any clues what this exception means? Only script running in chrome (basically, in FireFox extensions) can access XPCOM components, including Mozilla Storage ones. Script running on an HTML page cannot, for security reasons. Hence "permission denied". Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked error, while deleting
We are using two different connections to sqlite for these two different threads. So, we are not sharing the connection in SELECT and the DELETE thread. On Tue, Jun 10, 2008 at 5:28 PM, D. Richard Hipp <[EMAIL PROTECTED]> wrote: > > On Jun 10, 2008, at 7:36 AM, Sabyasachi Ruj wrote: > > > Hi, > > > > I have a very big table with around 40,00, 000 rows. 4 columns. 220 > > MB. > > > > Now, I have two threads:- > > Thread1: Is deleting 7,00, 000 rows from the table. > > Thread2: Is doing SELECT on the same table. > > > > Now the problem is sometimes the sqlite3_prepare for the SELECT > > query is > > failing with SQLITE_BUSY error. > > > > My questions: - > > 1. What is the best way to handle this error, and continue working > > normally? > > 2. Is there any documentation in sqlite3.org, which discuses the > > locking > > mechanism for DELETEs? Exacly in what phase of DELETE sqlite creates > > the > > exclusive lock, so, the SELECT is failing? > > > > > Are the two threads using the same database connection, or are they > making separate and independent calls to sqlite3_open()? > > > D. Richard Hipp > [EMAIL PROTECTED] > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Sabyasachi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked error, while deleting
"Sabyasachi Ruj" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Now, I have two threads:- > Thread1: Is deleting 7,00, 000 rows from the table. > Thread2: Is doing SELECT on the same table. > > Now the problem is sometimes the sqlite3_prepare for the SELECT query > is failing with SQLITE_BUSY error. Are you sure it's the prepare that fails, and not step? Only the very first prepare after opening the connection may fail this way, because SQLite needs to read database schema at this point. > 1. What is the best way to handle this error, and continue working > normally? Retry until you manage to go through. Or, prepare all the necessary statements up front, before commencing any database work. Then prepare won't fail (of course, step still may). > 2. Is there any documentation in sqlite3.org, which discuses the > locking mechanism for DELETEs? Exacly in what phase of DELETE sqlite > creates the exclusive lock, so, the SELECT is failing? http://sqlite.org/lockingv3.html EXCLUSIVE lock is obtained whenever SQLite needs to write to the database file. This may happen when the transaction is committed, or when the number of changes in a still-open transaction becomes so large that it no longer fits in a memory cache and has to be spilled to disk. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked error, while deleting
Sabyasachi Ruj wrote: Hi, Hello there! I have a very big table with around 40,00, 000 rows. 4 columns. 220 MB. Now, I have two threads:- Thread1: Is deleting 7,00, 000 rows from the table. Thread2: Is doing SELECT on the same table. Now the problem is sometimes the sqlite3_prepare for the SELECT query is failing with SQLITE_BUSY error. That's obviously not surprising - SQLite is doing the right thing here. My questions: - 1. What is the best way to handle this error, and continue working normally? You could either write a busy handler callback and set it via sqlite3_busy_handler - http://sqlite.org/c3ref/busy_handler.html - or you could use the builtin busy timeout handler set by sqlite3_busy_timeout - http://sqlite.org/c3ref/busy_timeout.html - which will put your thread to sleep and retry for up to the specified number of milliseconds. The second solution might be simpler. 2. Is there any documentation in sqlite3.org, which discuses the locking mechanism for DELETEs? Exacly in what phase of DELETE sqlite creates the exclusive lock, so, the SELECT is failing? The locking mechanism is explained in great detail in http://www.sqlite.org/lockingv3.html and the atomic commits in http://www.sqlite.org/atomiccommit.html . If those should not answer your questions, you could try http://www.google.com/search?q=site:sqlite.org+delete+lock :) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked error, while deleting
On Jun 10, 2008, at 7:36 AM, Sabyasachi Ruj wrote: > Hi, > > I have a very big table with around 40,00, 000 rows. 4 columns. 220 > MB. > > Now, I have two threads:- > Thread1: Is deleting 7,00, 000 rows from the table. > Thread2: Is doing SELECT on the same table. > > Now the problem is sometimes the sqlite3_prepare for the SELECT > query is > failing with SQLITE_BUSY error. > > My questions: - > 1. What is the best way to handle this error, and continue working > normally? > 2. Is there any documentation in sqlite3.org, which discuses the > locking > mechanism for DELETEs? Exacly in what phase of DELETE sqlite creates > the > exclusive lock, so, the SELECT is failing? > Are the two threads using the same database connection, or are they making separate and independent calls to sqlite3_open()? D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked error, while deleting
On Jun 10, 2008, at 7:50 AM, Alex Katebi wrote: > There can only be one prepare per table at a time. The first prepare > has to > be finalized before another one. Table is locked by the first > prepare until > finalized. > Actually, there is no limit on the number of prepared statements on a single table at a single time. The above is not correct. D. Richard Hipp [EMAIL PROTECTED] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked error, while deleting
There can only be one prepare per table at a time. The first prepare has to be finalized before another one. Table is locked by the first prepare until finalized. On Tue, Jun 10, 2008 at 7:36 AM, Sabyasachi Ruj <[EMAIL PROTECTED]> wrote: > Hi, > > I have a very big table with around 40,00, 000 rows. 4 columns. 220 MB. > > Now, I have two threads:- > Thread1: Is deleting 7,00, 000 rows from the table. > Thread2: Is doing SELECT on the same table. > > Now the problem is sometimes the sqlite3_prepare for the SELECT query is > failing with SQLITE_BUSY error. > > My questions: - > 1. What is the best way to handle this error, and continue working > normally? > 2. Is there any documentation in sqlite3.org, which discuses the locking > mechanism for DELETEs? Exacly in what phase of DELETE sqlite creates the > exclusive lock, so, the SELECT is failing? > > > -- > Sabyasachi > ___ > 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] Database locked error, while deleting
Hi, I have a very big table with around 40,00, 000 rows. 4 columns. 220 MB. Now, I have two threads:- Thread1: Is deleting 7,00, 000 rows from the table. Thread2: Is doing SELECT on the same table. Now the problem is sometimes the sqlite3_prepare for the SELECT query is failing with SQLITE_BUSY error. My questions: - 1. What is the best way to handle this error, and continue working normally? 2. Is there any documentation in sqlite3.org, which discuses the locking mechanism for DELETEs? Exacly in what phase of DELETE sqlite creates the exclusive lock, so, the SELECT is failing? -- Sabyasachi ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Accessing SQLite from Javascript in Firefox
Hi, Has anyone tried accessing SQLite from Javascript in Firefox? I found an Sqlite helper library for Mozilla at http://codesnippets.joyent.com/posts/show/1030 . But when I try to execute the same, firefox throws the following exception uncaught exception: Permission denied to get property UnnamedClass.classes Any clues what this exception means? Thanks, Ujval CAUTION - Disclaimer * This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. ***INFOSYS End of Disclaimer INFOSYS*** ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
