[sqlite] Fastest way of UPDATE'ing
Greetings! I have this scenario... 6 users with local dbs 1 network db to backup all those DBs and to share info. Every local DB unique record id based on the network DB. So, before each user creates a new record, the tool asks the network ID for the next available sequential record and creates a new record on the local DB using that unique id from the network DB. The question is, what is the fastest way to UPDATE the main DB? Right now, what I am doing is a for each record and UPDATE all the values where id=. Is there a faster way? Thanks, josé - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] database is locked error with 3.3.13
Hi all, Following up: I recently installed SQLite 3.3.13, after having used previous versions. I now get an error: Error: database is locked when I use the sqlite3 command line tool to access a database on a shared volume. But opening the same file with an earlier version works fine. I'm not sure what version introduced this problem. I suspect it's after 3.3.9. It's definitely after 3.1.3. I'm using Mac OS X 10.4.8, with the database file on an AppleShare mounted volume. I tried using sqlite 3.3.10 (which I had on another machine) and still have the problem. I looked for 3.3.9 to re-install it, to try that version, but couldn't find it on the sqlite.org web site. Have others experienced a locking error on remote volumes? Where can I get 3.3.9 and earlier source code? Thanks, Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] bug in the cmd shell
The .import cmd has a problem when it encounters binary zeros embedded in data. I have a large tab-separated text file which has some garbage (aka binary zeros) in one of the fields of one of the records. The .import cmd trips on this and terminates with this msg: /u/crdceed/data/RTM_NY_ceed_positionsPB.dat line 951: expected 14 columns of data but found 19 I should think this would be considered a bug. jim NOTICE: If received in error, please destroy and notify sender. Sender does not intend to waive confidentiality or privilege. Use of this email is prohibited when received in error.
Re: [sqlite] 8 byte integer not same as MSSQL bigint -- missing one value
"Samuel R. Neff" <[EMAIL PROTECTED]> wrote: > Just out of curiosity, why is the range for an 8 byte integer in SQLite one > number off from the 8 byte bigint in MSSQL? > > SQLite: -9223372036854775807 through 9223372036854775807 > MSSQL : -9223372036854775808 through 9223372036854775807 > It simplifies the routine that converts strings to integers if the maximum negative value has the same set of digits as the maximum positive value. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] ISO8601 8-byte packed date type
Hello to all, I am working on an 8-byte data type that stores date time and timezone information and provides helper routines for date arithmatic as well as conversion to/from strings, julian dates, UNIX date times, etc... The type can be stored in a 64-bit integer or as an 8 byte blob. The current implementation is in Delphi. Would anyone be interested in such a feature? Tom -- View this message in context: http://www.nabble.com/ISO8601-8-byte-packed-date-type-tf3364823.html#a9361767 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Update question
Thank you samuel On 07/03/07, Samuel R. Neff <[EMAIL PROTECTED]> wrote: Try this: UPDATE items SET price = ( SELECT price FROM month WHERE id = items.id) WHERE id IN (SELECT id from month); HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Cesar Rodas [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 07, 2007 2:53 PM To: sqlite-users@sqlite.org Subject: [sqlite] Update question Hello to all I want to know if sqlite supports updates to more than a table, as mysql allows Ex: UPDATE items,month SET items.price=month.price WHERE items.id=month.id; Thanks to all - To unsubscribe, send email to [EMAIL PROTECTED] - -- Cesar Rodas http://www.sf.net/projects/pagerank (The PageRank made easy...) http://www.sf.net/projects/fastfs ( The Fast File System) Mobile Phone: 595 961 974165 Phone: 595 21 645590 [EMAIL PROTECTED] [EMAIL PROTECTED]
RE: [sqlite] Update question
Try this: UPDATE items SET price = ( SELECT price FROM month WHERE id = items.id) WHERE id IN (SELECT id from month); HTH, Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] -Original Message- From: Cesar Rodas [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 07, 2007 2:53 PM To: sqlite-users@sqlite.org Subject: [sqlite] Update question Hello to all I want to know if sqlite supports updates to more than a table, as mysql allows Ex: UPDATE items,month SET items.price=month.price WHERE items.id=month.id; Thanks to all - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] 8 byte integer not same as MSSQL bigint -- missing one value
Just out of curiosity, why is the range for an 8 byte integer in SQLite one number off from the 8 byte bigint in MSSQL? SQLite: -9223372036854775807 through 9223372036854775807 MSSQL : -9223372036854775808 through 9223372036854775807 Values are from testing with SQLite 3.3.12 and MSSQL 2005. SQLite: create table x(i integer); insert into x(i) values(-9223372036854775808); insert into x(i) values(-9223372036854775807); insert into x(i) values( 9223372036854775808); insert into x(i) values( 9223372036854775807); select i, typeof(i) from x; MSSQL: create table #x(i bigint); insert into #x(i) select 9223372036854775809 * -1; insert into #x(i) select 9223372036854775808 * -1; insert into #x(i) select 9223372036854775808; insert into #x(i) select 9223372036854775807; select * from #x; Thanks, Sam --- We're Hiring! Seeking a passionate developer to join our team building products. Position is in the Washington D.C. metro area. If interested contact [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] I Need database fot some test
thanks Dennis, Single quote delimit a string literal. Double quotes delimit a quoted identifier (i.e a column or table name etc that contains special characters such as a space). I just checked Sybase Manual, and there it's exactly as you say. -- cheers, Stef Mientki http://pic.flappie.nl - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Update question
Hello to all I want to know if sqlite supports updates to more than a table, as mysql allows Ex: UPDATE items,month SET items.price=month.price WHERE items.id=month.id; Thanks to all
Re: [sqlite] I Need database fot some test
Stef Mientki wrote: thanks Dennis, Marco, And the standard SQL syntax should work from any standard compliant database program. SELECT * FROM "Order Details" I'm just a novice, and although both suggested solutions work, I thought SINGLE QUOTES were thé standard ? Single quote delimit a string literal. Double quotes delimit a quoted identifier (i.e a column or table name etc that contains special characters such as a space). Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] I Need database fot some test
Regarding: "... I thought SINGLE QUOTES were thé standard ?" I believe single quotes are the standard for literal strings, but double quotes for table names. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] I Need database fot some test
thanks Dennis, Marco, And the standard SQL syntax should work from any standard compliant database program. SELECT * FROM "Order Details" I'm just a novice, and although both suggested solutions work, I thought SINGLE QUOTES were thé standard ? -- cheers, Stef Mientki http://pic.flappie.nl - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] I Need database fot some test
Marco Bambini wrote: The space between Order and Details is the problem. This syntax: SELECT * FROM [Order Details] works fine with SQLiteManager. And the standard SQL syntax should work from any standard compliant database program. SELECT * FROM "Order Details" Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] I Need database fot some test
The space between Order and Details is the problem. This syntax: SELECT * FROM [Order Details] works fine with SQLiteManager. Regards, --- Marco Bambini http://www.sqlabs.net http://www.sqlabs.net/blog/ http://www.sqlabs.net/realsqlserver/ On Mar 7, 2007, at 7:43 PM, Stef Mientki wrote: Did anyone test this database ? With 3 out of 4 programs I'm not able to read the table "Order Details", does anyone what's the problem here ? thanks, Mikey C wrote: Here is a database http://www.nabble.com/file/6997/Northwind.db Northwind.db It is an exact SQLite implementation of the well known Microsoft Northwind sample that can be found for MS Access and SQL Server. -- cheers, Stef Mientki http://pic.flappie.nl -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] import operation - primary key need to be automatically generated by SQLite (not from csv file)
csv file is generated by a program. Program generatin csv do not know the existing id values from database table. Program can keep id blank or '' or null or some suitable value in csv file, which SQLite import should understand to generate next id automatically by SQLite itself to use it as primary key. But then for first column what to write in csv file ? Any of the following do not work. /* sample records from csv file data.csv */ ,'name_text_1' '','name_text_3' null,'name_text_2' 'null','name_text_4' Thanks for helping. Rohit -- View this message in context: http://www.nabble.com/import-operation---primary-key-need-to-be-automatically-generated-by-SQLite-%28not-from-csv-file%29-tf3360094.html#a9359741 Sent from the SQLite mailing list archive at Nabble.com. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] I Need database fot some test
Did anyone test this database ? With 3 out of 4 programs I'm not able to read the table "Order Details", does anyone what's the problem here ? thanks, Mikey C wrote: Here is a database http://www.nabble.com/file/6997/Northwind.db Northwind.db It is an exact SQLite implementation of the well known Microsoft Northwind sample that can be found for MS Access and SQL Server. -- cheers, Stef Mientki http://pic.flappie.nl - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] What is wrong with this simple query (offset)?
You were absolutely right, I didn't call the new dll. All solved now. RBS -Original Message- From: Trey Mack [mailto:[EMAIL PROTECTED] Sent: 07 March 2007 13:12 To: sqlite-users@sqlite.org Subject: Re: [sqlite] What is wrong with this simple query (offset)? > It looks all as it should work and it compiles with the same number of > warnings, but I get a bad dll calling convention in VB with the extra > integer argument iFields. You've changed the signature of the method you're calling, and it looks like you changed it correctly in the VB declaration. Maybe you have an older version of the DLL with the older signature in your system32 directory? It's possible this older version is being loaded, and that would cause the error you see. There's a method called sqlite_libversion in that dll that returns VB_SQLITE_VERSION (#defined in vbsql.h). Mine's "3.3.8c" now. Added the extra letter just so I could make sure I have the right version of the dll loaded. HTH, - Trey - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re : [sqlite] Soft search in database
Hello John, a page rank like algorithm does not make sense with only a bunch of text files. Its power comes from its hability to take into account the matrix of links between documents on the web. In this case, a classic TFIDF http://en.wikipedia.org/wiki/Tf-idf algorithm should be sufficient. Pierre - Message d'origine De : John Stanton <[EMAIL PROTECTED]> À : sqlite-users@sqlite.org Envoyé le : Mardi, 6 Mars 2007, 17h22mn 08s Objet : Re: [sqlite] Soft search in database Look up "page rank algorithm", in particular the papers by Brin and Page, the Google founders. Henrik Ræder wrote: > Hi > > (First post - hope it's an appropriate place) > > I've been implementing a database of a few MB of text (indexing > magazines) in SQLite, and so far have found it to work really well. > > Now my boss, who has a wonderfully creative mind, asks me to implement a > full-text search function which is not the usual simplistic 'found' / > 'not found', but more Google-style where a graded list of results is > returned. > > For example, in a search for "MP3 Player", results with the phrases next > to each other would get a high rating, as would records with a high > occurance of the keywords. > > This falls outside the usual scope of SQL, but would still seem a > relatively common problem to tackle. > > Any ideas (pointers) how to tackle this? > > Best regards > > Henrik Ræder Clausen > CD-rom editor > Komputer for alle > > Jidoka Development Hougårdsvej 29 8220 Brabrand DenmarkTlf +45 > 2611 5842 > - To unsubscribe, send email to [EMAIL PROTECTED] - ___ Découvrez une nouvelle façon d'obtenir des réponses à toutes vos questions ! Profitez des connaissances, des opinions et des expériences des internautes sur Yahoo! Questions/Réponses http://fr.answers.yahoo.com
[sqlite] Re : [sqlite] Re : [sqlite] Soft search in database
Hello Jos, not as is. You need to modify slighlty the library. Half a day of work I guess. Pierre - Message d'origine De : Jos van den Oever <[EMAIL PROTECTED]> À : sqlite-users@sqlite.org Envoyé le : Mardi, 6 Mars 2007, 16h33mn 15s Objet : Re: [sqlite] Re : [sqlite] Soft search in database 2007/3/6, Pierre Aubert <[EMAIL PROTECTED]>: > You can also use ft3.sourceforge.net Does this also allow having an inverted index without actually storing the files in the database? Cheers, Jos - To unsubscribe, send email to [EMAIL PROTECTED] - ___ Découvrez une nouvelle façon d'obtenir des réponses à toutes vos questions ! Profitez des connaissances, des opinions et des expériences des internautes sur Yahoo! Questions/Réponses http://fr.answers.yahoo.com
RE: [sqlite] What is wrong with this simple query (offset)?
Thanks, will have a look at that, but I am sure I am calling the new dll as the declaration in VB doesn't mention the full path and I point to the dll by changing the curdir. RBS -Original Message- From: Trey Mack [mailto:[EMAIL PROTECTED] Sent: 07 March 2007 13:12 To: sqlite-users@sqlite.org Subject: Re: [sqlite] What is wrong with this simple query (offset)? > It looks all as it should work and it compiles with the same number of > warnings, but I get a bad dll calling convention in VB with the extra > integer argument iFields. You've changed the signature of the method you're calling, and it looks like you changed it correctly in the VB declaration. Maybe you have an older version of the DLL with the older signature in your system32 directory? It's possible this older version is being loaded, and that would cause the error you see. There's a method called sqlite_libversion in that dll that returns VB_SQLITE_VERSION (#defined in vbsql.h). Mine's "3.3.8c" now. Added the extra letter just so I could make sure I have the right version of the dll loaded. HTH, - Trey - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] What is wrong with this simple query (offset)?
It looks all as it should work and it compiles with the same number of warnings, but I get a bad dll calling convention in VB with the extra integer argument iFields. You've changed the signature of the method you're calling, and it looks like you changed it correctly in the VB declaration. Maybe you have an older version of the DLL with the older signature in your system32 directory? It's possible this older version is being loaded, and that would cause the error you see. There's a method called sqlite_libversion in that dll that returns VB_SQLITE_VERSION (#defined in vbsql.h). Mine's "3.3.8c" now. Added the extra letter just so I could make sure I have the right version of the dll loaded. HTH, - Trey - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] import operation - primary key need to be automatically generated by SQLite (not from csv file)
RohitPatel wrote: /* SQLite 3.3.8 (Windows) used */ /* table t1 */ /* only two columns are given because other columns are irrelevant here */ create table t1 (id INTEGER PRIMARY KEY, name TEXT); /* few sample records from csv file data.csv */ 1,'name_text_1' 2,'name_text_2' 3,'name_text_3' 4,'name_text_4' /* import statement */ .import imp3.csv t1 If your CSV files' id columns are unwanted you could delete them rather than edit them. Writing a simple script to clean up your data would be trivial on 'nix but I see you're on Windows so how about something like: create table t1 (id INTEGER PRIMARY KEY, name TEXT); create table t1raw (id INTEGER, name TEXT); .import imp3.csv t1raw insert into t1(name) select name from t1raw; drop table t1raw; Martin - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] import operation - primary key need to be automatically generated by SQLite (not from csv file)
On 3/6/07, RohitPatel <[EMAIL PROTECTED]> wrote: /* SQLite 3.3.8 (Windows) used */ /* table t1 */ /* only two columns are given because other columns are irrelevant here */ create table t1 (id INTEGER PRIMARY KEY, name TEXT); /* few sample records from csv file data.csv */ 1,'name_text_1' 2,'name_text_2' 3,'name_text_3' 4,'name_text_4' /* import statement */ .import imp3.csv t1 I must have proper primary key valures in csv file. And when table already has records, and importing more from csv file, I need to edit primary key valures again to avoid error. (It becomes cumbersome when csv file has thousands of records) How to achieve import operation - primary key values should be automatically incremented by SQLite and not to be used from csv file ? I don't think there is anyway around it. You are providing SQLite the values to import, you are asking it to import it, but you really don't want to import it. And, of course, SQLite is complaining because PKs are conflicting. Well, SQLite is doing its job and precisely what you would want it to do. It doesn't know any better that you intentionally want to circumvent something you yourself have laid down as a rule. -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] User defined functions naming problem
Hello dear sqlite users, Hello dr. Hipp, I'm trying to create a user-defined function having the name LEFT but that does not seem to work (I get an sql parsing error). All is fine if I rename it to STRLEFT, but I'd stick to the first one since I'd like to add some compatibility with other db engines. I understand there are a number of keywords which sqlite uses, but according to http://www.sqlite.org/lang_keywords.html, these keywords cannot be used as 'names of tables, indices, columns, or databases'. Function names are not included here. So, is there a way to do it, not possible or is it a bug somewhere in the parser? Thanks. _ Express yourself instantly with MSN Messenger! Download today it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ - To unsubscribe, send email to [EMAIL PROTECTED] -
