[sqlite] Indexing problem
Hi, I am having a strange problem with a sqlite3 database. See the following transcript: sqlite3 kdb select * from kfz where kfznr=48482364; 48482364|48|0|0C|00|00|0||20|5B93|1746294314|||0|GP-T 1006|0 kfznr is the primary key, so this is to be expected. Now two queries as fired from the application code: sqlite3 kdb select * from kfz where CRC32=-797785824; 48482364|48|0|0C|00|00|0||20|5B93|-797785824|||0|GP-T 1006|0 20209001|20|1|3C|00|32|24999||13|CE42|-797785824|||0|FL-HH 11|1 20209001|20|1|3C|00|32|24999||13|CE42|-797785824|||0|FL-HH 11|1 kdb select * from kfz where CRC32=-1509747892; 48482364|48|0|0C|00|00|0||20|5B93|-1509747892|||0|GP-T 1006|0 20209667|20|1|3C|00|32|202880||99|4FBD|-1509747892|||0|FL-AK 98|1 20209667|20|1|3C|00|32|202880||99|4FBD|-1509747892|||0|FL-AK 98|1 What could cause 48482364 to show up in both results with a different value for CRC32? The table is defined like this: CREATE TABLE IF NOT EXISTS KFZ ( kfznr TEXT PRIMARY KEY, partnernr INTEGER, sendtoTA INTEGER, saeule TEXT, berechtigung2 TEXT, berechtigung TEXT, a_km TEXT, max_km TEXT, kont TEXT, pincode TEXT, CRC32 INTEGER, verweis BLOB, handynummer TEXT, sperrung TEXT, kennzeichen TEXT, kontingentierung INTEGER); CREATE INDEX IF NOT EXISTS IDX_KFZ_PARTNERNR ON KFZ (sendtoTA,partnernr); CREATE INDEX IF NOT EXISTS IDX_KFZ_CRC32 ON KFZ (CRC32); CREATE INDEX IF NOT EXISTS IDX_KFZ_VERWEIS ON KFZ (VERWEIS); CREATE INDEX IF NOT EXISTS IDX_KFZ_HANDYNUMMER ON KFZ (HANDYNUMMER); Ciao, MM ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexing problem
On 26/02/2009 8:23 PM, Marian Aldenhoevel wrote: Hi, I am having a strange problem with a sqlite3 database. See the following transcript: sqlite3 kdb select * from kfz where kfznr=48482364; 48482364|48|0|0C|00|00|0||20|5B93|1746294314|||0|GP-T 1006|0 kfznr is the primary key, so this is to be expected. Now two queries as fired from the application code: sqlite3 kdb select * from kfz where CRC32=-797785824; 48482364|48|0|0C|00|00|0||20|5B93|-797785824|||0|GP-T 1006|0 20209001|20|1|3C|00|32|24999||13|CE42|-797785824|||0|FL-HH 11|1 20209001|20|1|3C|00|32|24999||13|CE42|-797785824|||0|FL-HH 11|1 kdb select * from kfz where CRC32=-1509747892; 48482364|48|0|0C|00|00|0||20|5B93|-1509747892|||0|GP-T 1006|0 20209667|20|1|3C|00|32|202880||99|4FBD|-1509747892|||0|FL-AK 98|1 20209667|20|1|3C|00|32|202880||99|4FBD|-1509747892|||0|FL-AK 98|1 What could cause 48482364 to show up in both results with a different value for CRC32? Another problem: the second and third results from each of query 2 and query 3 are identical; this should not be so because the first column is a primary key. Note that kfznr is allegedly TEXT ... interesting question: is it possible to have two rows, one where typeof(kfznr) is text and the other is integer AND they are not considered equal for primary key purposes? Try repeating all 3 queries with select rowid, typeof(kfznr), * from ... The table is defined like this: CREATE TABLE IF NOT EXISTS KFZ ( Is that as reported by the command-line sqlite3 executable program, or is it from some script that you hope is the one that was used to create the table? kfznr TEXT PRIMARY KEY, partnernr INTEGER, sendtoTA INTEGER, saeule TEXT, berechtigung2 TEXT, berechtigung TEXT, a_km TEXT, max_km TEXT, kont TEXT, pincode TEXT, CRC32 INTEGER, verweis BLOB, handynummer TEXT, sperrung TEXT, kennzeichen TEXT, kontingentierung INTEGER); CREATE INDEX IF NOT EXISTS IDX_KFZ_PARTNERNR ON KFZ (sendtoTA,partnernr); CREATE INDEX IF NOT EXISTS IDX_KFZ_CRC32 ON KFZ (CRC32); CREATE INDEX IF NOT EXISTS IDX_KFZ_VERWEIS ON KFZ (VERWEIS); CREATE INDEX IF NOT EXISTS IDX_KFZ_HANDYNUMMER ON KFZ (HANDYNUMMER); Ciao, MM ___ 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] Maximum number of concurrent users
Hi, What is the maximum number of concurrent users for SQLite? Thanks, Ev ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexing problem
On 26/02/2009 9:45 PM, John Machin wrote:
On 26/02/2009 8:23 PM, Marian Aldenhoevel wrote:
Hi,
I am having a strange problem with a sqlite3 database. See the following
transcript:
sqlite3 kdb select * from kfz where kfznr=48482364;
48482364|48|0|0C|00|00|0||20|5B93|1746294314|||0|GP-T 1006|0
kfznr is the primary key, so this is to be expected. Now two queries as
fired from the application code:
sqlite3 kdb select * from kfz where CRC32=-797785824;
48482364|48|0|0C|00|00|0||20|5B93|-797785824|||0|GP-T 1006|0
20209001|20|1|3C|00|32|24999||13|CE42|-797785824|||0|FL-HH 11|1
20209001|20|1|3C|00|32|24999||13|CE42|-797785824|||0|FL-HH 11|1
kdb select * from kfz where CRC32=-1509747892;
48482364|48|0|0C|00|00|0||20|5B93|-1509747892|||0|GP-T 1006|0
20209667|20|1|3C|00|32|202880||99|4FBD|-1509747892|||0|FL-AK 98|1
20209667|20|1|3C|00|32|202880||99|4FBD|-1509747892|||0|FL-AK 98|1
What could cause 48482364 to show up in both results with a different
value for CRC32?
Another problem: the second and third results from each of query 2 and
query 3 are identical; this should not be so because the first column is
a primary key.
Note that kfznr is allegedly TEXT ... interesting question: is it
possible to have two rows, one where typeof(kfznr) is text and the other
is integer AND they are not considered equal for primary key purposes?
Try repeating all 3 queries with
select rowid, typeof(kfznr), * from ...
So having re-read all about affinity, and done a few desultory tests,
maybe that's not the problem. Maybe it's a WYSI*NOT*WYG problem:
C:\devel\sqlite3sqlite3
SQLite version 3.6.10
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite create table foo (bar text primary key, zot integer);
sqlite insert into foo values('1234', 1);
sqlite insert into foo values(1234, 1);
SQL error: column bar is not unique
sqlite insert into foo values(4567, 2);
sqlite insert into foo values('4567', 3);
SQL error: column bar is not unique
sqlite insert into foo values('4567' || X'00', 3);
sqlite insert into foo values('4567' || X'', 4);
sqlite select rowid, typeof(bar), quote(bar), length(bar), * from foo;
1|text|'1234'|4|1234|1
2|text|'4567'|4|4567|2
3|text|'4567'|4|4567|3
4|text|'4567'|4|4567|4
sqlite -- UH OH!!!
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexing problem
Hi, CREATE TABLE IF NOT EXISTS KFZ ( Is that as reported by the command-line sqlite3 executable program, or is it from some script that you hope is the one that was used to create the table? That is from the script I _know_ is the one that created the table. I will send output from the commandline client later. Yes, kfznr is declared TEXT and inserted as text. Using a prepared statement and bound parameters. Again I can show code when I'm back later. Ciao, MM ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite vs. Oracle (parallelized)
On Mon, Feb 23, 2009 at 04:28:00PM -0500, D. Richard Hipp wrote: Remember: SQLite is not trying to replace Oracle. SQLite is trying to replace fopen(). Exactly. SQLite is *wonderful* for this, and I would hate to see it compromised by adding features that really aren't necessary. There are a lot of feature requests, and many of them are to make SQLite more like MySQL or Oracle --- in which case I would suggest people really should be using those. I'm very happy with how small, compact, and blisteringly fast, SQLite is for a whole range of uses, I don't want it bloated down to make it something it's not. Oh, except right joins. If you add those I won't mind :-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Transactions and attached databases
Hi Donald, On Feb 25, 2009, at 3:39 PM, Griggs, Donald wrote: Greetings, Tito, Did you see page: http://www.sqlite.org/lang_attach.html Transactions involving multiple attached databases are atomic, assuming that the main database is not :memory:. It then goes on to say: If the main database is :memory: then transactions continue to be atomic within each individual database file. But if the host computer crashes in the middle of a COMMIT where two or more database files are updated, some of those files might get the changes where others might not. No, I didn't look at that page... sorry about that. I was looking at the SQLite list instead for answers. Thank you for the help! Regards, -- Tito ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Maximum number of concurrent users
On Feb 26, 2009, at 6:28 AM, Eversogood wrote: Hi, What is the maximum number of concurrent users for SQLite? There is no limit, though writes are serialized. Your question betrays that you are thinking of SQLite as if it were any other client/server database engine. It is not. SQLite is not a replacement for Oracle/MySQL/PostgreSQL. SQLite is designed to replace fopen(). If you are looking for an Oracle replacement, SQLite is probably not for you. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexing problem
Marian Aldenhoevel marian.sqli...@mba-software.de wrote in message news:49a65fac.5060...@mba-software.de I am having a strange problem with a sqlite3 database. See the following transcript: sqlite3 kdb select * from kfz where kfznr=48482364; 48482364|48|0|0C|00|00|0||20|5B93|1746294314|||0|GP-T 1006|0 kfznr is the primary key, so this is to be expected. Now two queries as fired from the application code: sqlite3 kdb select * from kfz where CRC32=-797785824; 48482364|48|0|0C|00|00|0||20|5B93|-797785824|||0|GP-T 1006|0 20209001|20|1|3C|00|32|24999||13|CE42|-797785824|||0|FL-HH 11|1 20209001|20|1|3C|00|32|24999||13|CE42|-797785824|||0|FL-HH 11|1 kdb select * from kfz where CRC32=-1509747892; 48482364|48|0|0C|00|00|0||20|5B93|-1509747892|||0|GP-T 1006|0 20209667|20|1|3C|00|32|202880||99|4FBD|-1509747892|||0|FL-AK 98|1 20209667|20|1|3C|00|32|202880||99|4FBD|-1509747892|||0|FL-AK 98|1 What could cause 48482364 to show up in both results with a different value for CRC32? You have duplicate rows where kfznr value looks the same to a naked eye, but is in fact subtly different. One problem I've seen, for example, is accidentally including a terminating NUL byte in the field's value: sqlite3_bind_text(stmt, 1, param, strlen(param) + 1, NULL); Try this, see if you get any insights: select length(kfznr), * from kfz where kfznr like '%48482364%'; Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Indexing problem
kdb select * from kfz where CRC32=-1509747892; 48482364|48|0|0C|00|00|0||20|5B93|-1509747892|||0|GP-T 1006|0 20209667|20|1|3C|00|32|202880||99|4FBD|-1509747892|||0|FL-AK 98|1 20209667|20|1|3C|00|32|202880||99|4FBD|-1509747892|||0|FL-AK 98|1 What could cause 48482364 to show up in both results with a different value for CRC32? You have duplicate rows where kfznr value looks the same to a naked eye, but is in fact subtly different. One problem I've seen, for example, is accidentally including a terminating NUL byte in the field's value: sqlite3_bind_text(stmt, 1, param, strlen(param) + 1, NULL); Try this, see if you get any insights: select length(kfznr), * from kfz where kfznr like '%48482364%'; Even better: SELECT length(kfznr), hex(kfznr), * FROM kfz WHERE kfznr LIKE '%48482364%'; D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Maximum number of concurrent users
It is an embedded system, so the number of users depends on how it is used. Be aware that it is not a database server like Oracle or DB2, but is a library of routines to link into your application. Eversogood wrote: Hi, What is the maximum number of concurrent users for SQLite? Thanks, Ev ___ 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] SQLite vs. Oracle (parallelized)
On Wed, Feb 25, 2009 at 10:29:59AM -0800, Chris Wedgwood scratched on the wall: On Mon, Feb 23, 2009 at 04:28:00PM -0500, D. Richard Hipp wrote: Remember: SQLite is not trying to replace Oracle. SQLite is trying to replace fopen(). Exactly. SQLite is *wonderful* for this, and I would hate to see it compromised by adding features that really aren't necessary. I'm very happy with how small, compact, and blisteringly fast, SQLite is for a whole range of uses, I don't want it bloated down to make it something it's not. I agree. The only major addition I'd like to see is full support for referential integrity. I know you can fake it, to a degree, with triggers, but I'd still like to see it baked into the database engine itself. Oh, except right joins. If you add those I won't mind :-) I've always wondered about this... someone please correct me if I'm wrong, but my understanding is that there wasn't any difference between a left and right join except for the argument order. It seems like implementing right joins should be pretty easy by just having the parser reverse the arguments when it builds the parse tree. I realize that might screw with the optimizer a bit, but is there anything obvious I'm missing? -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Our opponent is an alien starship packed with atomic bombs. We have a protractor. I'll go home and see if I can scrounge up a ruler and a piece of string. --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error on INSERT with SELECT Max(id) FROM ...
Hi Leo,
insert into 'tblName' Values (NULL, 'five');
create table 'tblRefMaxName' ('ref' integer, 'nn' text);
insert into 'tblRefMaxName' Values (select max(id) from tblName,
'eins');
Apart from the responses already from other posters, you should use
the correct quotes for entities (table and columns names use double
quotes: ) versus literals (single quotes: '). So it should read:
insert into tblName values (null, 'five');
create table tblRefMaxName (ref integer, nn text);
insert into tblRefMaxName select max(id), 'eins' from tblName;
Tom
BareFeet
--
Comparison of SQLite GUI applications:
http://www.tandb.com.au/sqlite/compare/?ml
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL error: no such function: replace
Hi Ben, Unfortunately though I don't have the option of updating it, since I am using it on a very widespread set of machines which I don't have root privileges on... You don't necessarily have to install the newer SQLite at system level. You could incorporate it into your app or install the newer command line tool in your user directory etc. How are you using SQLite? Tom BareFeet -- Comparison of SQLite GUI applications: http://www.tandb.com.au/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Concatenate single field from multiple records?
Is it possible with just an SQL to Concatenate the content from several records of a single field with perhaps a character inbetween? For example if I have TableA that has Field1 with the following info in Field1: rec1: How rec2: are rec3: you rec4: ? Would it be possibly using only SQL to output a single field with the content How are you ? This would mean concatenating the fields with a space between each field. I can do this programatically with a simple 'SELECT Field1 FROM TableA' and then cycling through the result and building a string, but I was curious if this is possible using only SQL. Thanks Greg ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Concatenate single field from multiple records?
Greg Robertson trifus...@gmail.com wrote: Is it possible with just an SQL to Concatenate the content from several records of a single field with perhaps a character inbetween? For example if I have TableA that has Field1 with the following info in Field1: rec1: How rec2: are rec3: you rec4: ? Would it be possibly using only SQL to output a single field with the content How are you ? select group_concat(Field1, ' ') from TableA; http://www.sqlite.org/lang_aggfunc.html Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] manual with sample C programs
On Wed, 25 Feb 2009 19:51:07 -0500 (EST), Michael (mrobi...@cs.fiu.edu) wrote: Hello users group, I have been looking in the documentation for sample programs in C, but no luck yet. The source of the sqlite3 commandline tool may serve as an example. Alternatively, you could take a look at the source of fossil, a Distributed Revision Control, Wiki, and Bug-Tracking application, which uses SQLite extensively. http://www.fossil-scm.org/index.html/doc/tip/www/index.wiki Could you tell me where can I find such documentation, or can you recommend some books. Thanks very much Michael R -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] .timer with windows cli
Kim Boulton wrote: Is there a compiled v3.6.11 sqlite3.exe with the .timer facility enabled. Not for windows (I believe) as follows: [From shell.c] #if !defined(_WIN32) !defined(WIN32) . . . #include sys/time.h #include sys/resource.h . . . . . . #define BEGIN_TIMER beginTimer() #define END_TIMER endTimer() #define HAS_TIMER 1 #else #define BEGIN_TIMER #define END_TIMER #define HAS_TIMER 0 #endif But I've been wrong many times before. ;) Bob -- _ Windows Live™ Hotmail®…more than just e-mail. http://windowslive.com/howitworks?ocid=TXT_TAGLM_WL_t2_hm_justgotbetter_howitworks_022009 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQLite unique features
Hi Everyone, I'm doing some research on SQLite. Can anyone list or point me to documentaion on distinctly unique features available in SQLite. Thanks in advance! - Jack -- View this message in context: http://www.nabble.com/SQLite-unique-features-tp22232541p22232541.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] SQLite unique features
jackspratt jackspratt...@yahoo.com wrote: I'm doing some research on SQLite. Can anyone list or point me to documentaion on distinctly unique features available in SQLite. Did your research include actually going to SQLite's site and clicking on Documentation link? The second document at this link is titled Distinctive Features. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Best GUI?
Greetings, This is a bit of a religious question, but which are the mest/most popular *nix GUIs out there? I'm using a firefox plugin but not entirely happy with it. What do you use and why? TIA, Nuno Magalhães LU#484677 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] manual with sample C programs
Where do I find the source to the command-line tool. I looked but didn't see it. Sorry if this should be obvious. :) J. R. J. R. Westmoreland E-mail: j...@jrw.org -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kees Nuyt Sent: Thursday, February 26, 2009 12:05 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] manual with sample C programs On Wed, 25 Feb 2009 19:51:07 -0500 (EST), Michael (mrobi...@cs.fiu.edu) wrote: Hello users group, I have been looking in the documentation for sample programs in C, but no luck yet. The source of the sqlite3 commandline tool may serve as an example. Alternatively, you could take a look at the source of fossil, a Distributed Revision Control, Wiki, and Bug-Tracking application, which uses SQLite extensively. http://www.fossil-scm.org/index.html/doc/tip/www/index.wiki Could you tell me where can I find such documentation, or can you recommend some books. Thanks very much Michael R -- ( Kees Nuyt ) c[_] ___ 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] manual with sample C programs
On Thu, Feb 26, 2009 at 2:50 PM, J. R. Westmoreland j...@jrw.org wrote: Where do I find the source to the command-line tool. I looked but didn't see it. Sorry if this should be obvious. :) download the source and search for it, or, you can browse the CVS tree. Here is the link to shell.c http://www.sqlite.org/cvstrac/rlog?f=sqlite/src/shell.c I am not very good with C, so there may be dependencies for it, but that should be a start. J. R. J. R. Westmoreland E-mail: j...@jrw.org -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kees Nuyt Sent: Thursday, February 26, 2009 12:05 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] manual with sample C programs On Wed, 25 Feb 2009 19:51:07 -0500 (EST), Michael (mrobi...@cs.fiu.edu) wrote: Hello users group, I have been looking in the documentation for sample programs in C, but no luck yet. The source of the sqlite3 commandline tool may serve as an example. Alternatively, you could take a look at the source of fossil, a Distributed Revision Control, Wiki, and Bug-Tracking application, which uses SQLite extensively. http://www.fossil-scm.org/index.html/doc/tip/www/index.wiki Could you tell me where can I find such documentation, or can you recommend some books. Thanks very much Michael R -- ( Kees Nuyt ) c[_] ___ 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 -- Puneet Kishor http://www.punkish.org/ Carbon Model http://carbonmodel.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ Sent from: Madison WI United States. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] manual with sample C programs
Thanks, I got the amalgamated source and didn't see it there. I'll try that. J. R. Westmoreland E-mail: j...@jrw.org -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P Kishor Sent: Thursday, February 26, 2009 1:58 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] manual with sample C programs On Thu, Feb 26, 2009 at 2:50 PM, J. R. Westmoreland j...@jrw.org wrote: Where do I find the source to the command-line tool. I looked but didn't see it. Sorry if this should be obvious. :) download the source and search for it, or, you can browse the CVS tree. Here is the link to shell.c http://www.sqlite.org/cvstrac/rlog?f=sqlite/src/shell.c I am not very good with C, so there may be dependencies for it, but that should be a start. J. R. J. R. Westmoreland E-mail: j...@jrw.org -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kees Nuyt Sent: Thursday, February 26, 2009 12:05 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] manual with sample C programs On Wed, 25 Feb 2009 19:51:07 -0500 (EST), Michael (mrobi...@cs.fiu.edu) wrote: Hello users group, I have been looking in the documentation for sample programs in C, but no luck yet. The source of the sqlite3 commandline tool may serve as an example. Alternatively, you could take a look at the source of fossil, a Distributed Revision Control, Wiki, and Bug-Tracking application, which uses SQLite extensively. http://www.fossil-scm.org/index.html/doc/tip/www/index.wiki Could you tell me where can I find such documentation, or can you recommend some books. Thanks very much Michael R -- ( Kees Nuyt ) c[_] ___ 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 -- Puneet Kishor http://www.punkish.org/ Carbon Model http://carbonmodel.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ Sent from: Madison WI United States. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] manual with sample C programs
I'm sure I have the right stuff but in my spooling through the file I could have over looked it. I also have a copy of the CVS tree and now I have the right file I'll look there. Much easier to find things. :) Thanks again, J. R. J. R. Westmoreland E-mail: j...@jrw.org -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P Kishor Sent: Thursday, February 26, 2009 2:15 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] manual with sample C programs On Thu, Feb 26, 2009 at 3:06 PM, J. R. Westmoreland j...@jrw.org wrote: Thanks, I got the amalgamated source and didn't see it there. I'll try that. hmmm... my amalgamation tarball does have shell.c separately from sqlite3.c. You sure you got the right source? J. R. Westmoreland E-mail: j...@jrw.org -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P Kishor Sent: Thursday, February 26, 2009 1:58 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] manual with sample C programs On Thu, Feb 26, 2009 at 2:50 PM, J. R. Westmoreland j...@jrw.org wrote: Where do I find the source to the command-line tool. I looked but didn't see it. Sorry if this should be obvious. :) download the source and search for it, or, you can browse the CVS tree. Here is the link to shell.c http://www.sqlite.org/cvstrac/rlog?f=sqlite/src/shell.c I am not very good with C, so there may be dependencies for it, but that should be a start. J. R. J. R. Westmoreland E-mail: j...@jrw.org -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Kees Nuyt Sent: Thursday, February 26, 2009 12:05 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] manual with sample C programs On Wed, 25 Feb 2009 19:51:07 -0500 (EST), Michael (mrobi...@cs.fiu.edu) wrote: Hello users group, I have been looking in the documentation for sample programs in C, but no luck yet. The source of the sqlite3 commandline tool may serve as an example. Alternatively, you could take a look at the source of fossil, a Distributed Revision Control, Wiki, and Bug-Tracking application, which uses SQLite extensively. http://www.fossil-scm.org/index.html/doc/tip/www/index.wiki Could you tell me where can I find such documentation, or can you recommend some books. Thanks very much Michael R -- ( Kees Nuyt ) c[_] ___ 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 -- Puneet Kishor http://www.punkish.org/ Carbon Model http://carbonmodel.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ Sent from: Madison WI United States. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Carbon Model http://carbonmodel.org/ Open Source Geospatial Foundation http://www.osgeo.org/ Sent from: Madison WI United States. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] A bit OT: Good CVS client for Windows
Can someone please give me a suggestion for a good client for CVS that runs under Windows? I have my linux system but figured it would be nice to have a client in both places, a belt and suspenders kind of person I guess. Thanks, J. R. J. R. Westmoreland E-mail: j...@jrw.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A bit OT: Good CVS client for Windows
Try TortoiseCVS. - Sherief On Feb 26, 2009, at 16:28, J. R. Westmoreland j...@jrw.org wrote: Can someone please give me a suggestion for a good client for CVS that runs under Windows? I have my linux system but figured it would be nice to have a client in both places, a belt and suspenders kind of person I guess. Thanks, J. R. J. R. Westmoreland E-mail: j...@jrw.org ___ 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] problem reading in SQL file with To_Date from Oracle
Hello all, I'm new to sqlite3. I have data in a Oracle table with one column being a date type. Now, I've exported this table into a sql file with insert statements. Then, I ran the .read command in sqlite3 to read in the sql statements. sqlite doesn't support the TO_Date function. How do I get around this? My sql file is large, i.e 8000 rows. Is there maybe a way to define this function internally? I'm using TOAD to create the sql file from Oracle. Your inputs appreciated!! Thanks. ps my table schema below: CREATE TABLE NBIAUTO ( OPERATION VARCHAR2(50), INTERFACE VARCHAR2(50), OCRELEASE VARCHAR2(8), RUNDATE DATE, PRODUCTS VARCHAR2(255), SERVER VARCHAR2(50), REQUEST VARCHAR2(4000), REQUEST2 VARCHAR2(4000), RESPONSE CLOB ) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] manual with sample C programs
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 P Kishor wrote: hmmm... my amalgamation tarball does have shell.c separately from sqlite3.c. You sure you got the right source? The .zip amalgamation only includes sqlite3.c, sqlite3ext.h and sqlite3.h. The .tar.gz amalgamation also includes the shell, configure and several other files. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) iEYEARECAAYFAkmnDNUACgkQmOOfHg372QR4IACdHlXXEm4DY86VJi9KXNbSkGnf y90An0p+PVvvTn8kiTqYeHuxPKVphecl =FBdE -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best GUI?
qThis is a bit of a religious question, but which are the mest/most popular *nix GUIs out there? I'm using a firefox plugin but not entirely happy with it. What do you use and why?/q A lot depends on what you are trying to accomplish. For example, I move between several machines, mixed between several Linux distros and two Windows versions with an USB drive. I have used sqliteadmin, sqlitespy, and sqlite database browser (all Windows executables, all work under wine). I have also tried WinSQL (which is an ODBC-driven program I use when my clients give me SQL access to the applications I'm installing for them. None of these really provide the kind of data entry screens I'd like to have; I have not had time to try OOo Base yet. I'd be interested in other responses you get, as well. Alan B. Cohen, CPA ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A bit OT: Good CVS client for Windows
On Feb 26, 2009, at 16:28, J. R. Westmoreland j...@jrw.org wrote: Can someone please give me a suggestion for a good client for CVS that runs under Windows? I have my linux system but figured it would be nice to have a client in both places, a belt and suspenders kind of person I guess. Thanks, J. R. J. R. Westmoreland E-mail: j...@jrw.org ___ 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] .timer with windows cli
Kim Boulton wrote: Is there a compiled v3.6.11 sqlite3.exe with the .timer facility enabled. I don't think so for windows -- but I've been wrong many times before. ;) [From shell.c] #if !defined(_WIN32) !defined(WIN32) . . . . #include sys/time.h #include sys/resource.h . . . . . . . . #define BEGIN_TIMER beginTimer() #define END_TIMER endTimer() #define HAS_TIMER 1 #else #define BEGIN_TIMER #define END_TIMER #define HAS_TIMER 0 #endif Bob -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem reading in SQL file with To_Date from Oracle
On Feb 26, 2009, at 4:37 PM, anjela patnaik wrote: Then, I ran the .read command in sqlite3 to read in the sql statements. sqlite doesn't support the TO_Date function. Please send me an example of the TOAD-generated INSERT statement that includes a TO_Date function. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem reading in SQL file with To_Date from Oracle
On 27/02/2009 8:37 AM, anjela patnaik wrote: Hello all, I'm new to sqlite3. I have data in a Oracle table with one column being a date type. Now, I've exported this table into a sql file with insert statements. Then, I ran the .read command in sqlite3 to read in the sql statements. sqlite doesn't support the TO_Date function. Care to show us a small sample of what the generated SQL statements look like? How do I get around this? My sql file is large, i.e 8000 rows. That's an interesting definition of large :-) Is there maybe a way to define this function internally? No ... your options are to get TOAD to put it out in a more readily digested format and/or manipulate the TOAD output with sed or a Python/perl/awk script. Instead of using a file of SQl insert statements, consider using a data file, to be loaded by the sqlite3 commandline program's .import command. I'm using TOAD to create the sql file from Oracle. Your inputs appreciated!! Thanks. ps my table schema below: CREATE TABLE NBIAUTO ( OPERATION VARCHAR2(50), INTERFACE VARCHAR2(50), OCRELEASE VARCHAR2(8), RUNDATEDATE, PRODUCTS VARCHAR2(255), SERVER VARCHAR2(50), REQUESTVARCHAR2(4000), REQUEST2 VARCHAR2(4000), RESPONSE CLOB ) HTH, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best GUI?
On Thu, 2009-02-26 at 15:44 -0600, Alan Cohen wrote: None of these really provide the kind of data entry screens I'd like to have; I have not had time to try OOo Base yet. Well then. You'll be interested in my projects :) http://entropy.homelinux.org/axis/ ( my home server is unfortunately just a *little* unstable at the moment ... hardware looks to be dying ... so apologies if it goes down ... it's up at the moment anyway ). There are some screenshots of production systems that use the 3 libraries. I'm currently working on a GUI builder ( unreleased, but I can be convinced to dump a very early beta ). I have table browsing and data viewing working. Also creating forms ( via glade ) is working. I plan on implementing schema editing for SQLite and possibly MySQL. Datasheet creating ( via the GUI ) is completely unimplemented. Anyway, email me ( this address is probably better at the moment ) if you're interested in the GUI builder, otherwise check out the libraries. Dan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Question
Hello, I have a question on sqlite - Is there a good way to launch an executable when a table is updated? Thanks. -Bryan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best GUI?
On Thu, Feb 26, 2009 at 4:30 PM, Daniel Kasak daniel.ka...@247realmedia.com wrote: On Thu, 2009-02-26 at 15:44 -0600, Alan Cohen wrote: None of these really provide the kind of data entry screens I'd like to have; I have not had time to try OOo Base yet. Well then. You'll be interested in my projects :) http://entropy.homelinux.org/axis/ ( my home server is unfortunately just a *little* unstable at the moment ... hardware looks to be dying ... so apologies if it goes down ... it's up at the moment anyway ). There are some screenshots of production systems that use the 3 libraries. I'm currently working on a GUI builder ( unreleased, but I can be convinced to dump a very early beta ). I have table browsing and data viewing working. Also creating forms ( via glade ) is working. I plan on implementing schema editing for SQLite and possibly MySQL. Datasheet creating ( via the GUI ) is completely unimplemented. Anyway, email me ( this address is probably better at the moment ) if you're interested in the GUI builder, otherwise check out the libraries. candid feedback http://entropy.homelinux.org/axis/northwind.png nice http://entropy.homelinux.org/axis/client_services.png ouch! see, you might want to try and emulate something like http://www.araelium.com/querious/screenshots/content_75p.png (MySQL tool) or http://fileability.net/Raw%20Screenshots/Form%20Design.png (built for and with SQLite) not something like http://www.bulkrenameutility.co.uk/Screenshots.php or http://www.billproduction.com/BillRed_scrsht.jpg In any case, your work looks very promising, and I look forward to seeing more of it as it would be a nice addition to the mix. -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Carbon Model http://carbonmodel.org/ Open Source Geospatial Foundation http://www.osgeo.org/ Sent from: Madison WI United States. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Best GUI?
On Thu, 2009-02-26 at 16:41 -0600, P Kishor wrote: candid feedback http://entropy.homelinux.org/axis/northwind.png nice http://entropy.homelinux.org/axis/client_services.png ouch! It's actually not so bad once you use it a bit. It's an all-in-one CRM app thingy that does basically everything the consultants have to do at my previous work. The front page that you're looking at, I was told, had to have some basic company details, and a contacts locations list. So that's what it's got. The other pages aren't so cluttered. We found it was best to have everything integrated like this instead of lots of little single-purpose forms because then all features are available within a single click, and it doesn't take time to load the form up. Also people start to get confused with lots of forms open. And seriously, they need all those pages. It's quite common for a consultant to navigate to a client, then flip through at least half of the pages. Anyway, I don't work there any more, so my replacement is free to do what he likes with it :) Dan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question
On Thu, 26 Feb 2009 17:33:25 -0500, Bryan Lemster bryan.lems...@gmail.com wrote: Hello, I have a question on sqlite - Is there a good way to launch an executable when a table is updated? Thanks. Not out-of-the-box, but you could write a function in a loadable extension which forks a process and have that function called in an ON UPDATE trigger. http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions http://www.sqlite.org/c3ref/create_function.html http://www.sqlite.org/lang_createtrigger.html Alternatively, you could change the SQLite source for your purpose. I don't advise this, it's hard to maintain. A loadable extension is the proper mechanism. -Bryan -- ( Kees Nuyt ) c[_] ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Adobe AIR and Sqlite - Adobe could have statically included the FTS module?
Adobe AIR did not include the FTS module. I read in this thread (https://store1.adobe.com/cfusion/webforums/forum/messageview.cfm?forumid=75catid=697threadid=1417051enterthread=y) that they disabled loadable modules. However, it would have been easy for them to statically include the FTS (and maybe other modules) in the build, right? I will be suggesting that to them in a bug ticket if that's the case but wanted to confirm first. Thanks, Scott ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_mprintf - C/C++ formatting 64-bit values?
On 27/02/2009 12:09 PM, His Nerdship wrote: Does anyone know the sqlite3_mprintf/sqlite3_vmprintf format specifier for 64-bit integers? I have tried %Ld and %I64d, but it just guillotines the string at that point. I am using Borland C++ Builder v6. Thanks in advance. Guess: %lld (as in long long) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Writing SQL with special characters
Are there any docs for how to write SQL for special characters like return and linefeed? On some systems (eg. http://www.wilsonmar.com/1eschars.htm) return is \n so an SQL might be something like: UPDATE TableA SET Field1 = line 1\nline 2 which would produce: line 1 line 2 in Field1 Is there somewhere I can find out how I can set special characters in SQLite? Thanks Greg ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Writing SQL with special characters
You can use the hex code of the non-printable character: update tableA set field1 = line1 x'0a' line2 -Toby Are there any docs for how to write SQL for special characters like return and linefeed? On some systems (eg. http://www.wilsonmar.com/1eschars.htm) return is \n so an SQL might be something like: UPDATE TableA SET Field1 = line 1\nline 2 which would produce: line 1 line 2 in Field1 Is there somewhere I can find out how I can set special characters in SQLite? Thanks Greg ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A bit OT: Good CVS client for Windows
You can also use GNU WinCVS, http://www.wincvs.org/download.html Regards, Jay On Fri, Feb 27, 2009 at 3:07 AM, Sherief N. Farouk sher...@mganin.comwrote: On Feb 26, 2009, at 16:28, J. R. Westmoreland j...@jrw.org wrote: Can someone please give me a suggestion for a good client for CVS that runs under Windows? I have my linux system but figured it would be nice to have a client in both places, a belt and suspenders kind of person I guess. Thanks, J. R. J. R. Westmoreland E-mail: j...@jrw.org ___ 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 -- first me then home first home then country first country then world fools always read inverse ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
