Re: [sqlite] Update if exists or Insert if exists

"insert or replace" should do it On Fri, Jan 9, 2009 at 6:21 PM, chiefmccrossan wrote: > > Hi, > > I have a table called Test with one column called Name - CREATE TABLE > "Test" > ("Name" VARCHAR) > > I would like to insert a value into test if the name column is empty

[sqlite] Update if exists or Insert if exists

Hi, I have a table called Test with one column called Name - CREATE TABLE "Test" ("Name" VARCHAR) I would like to insert a value into test if the name column is empty but if the column is not empty then I would like to update/overwrite the current value in the Name column. Can anyone please

Re: [sqlite] What does "PRAGMA integrity_check" actually do?

Roger Binns wrote: >> Writing to the beginning of the file (the first 40 bytes >> or so) corrupts the database so badly that SQLite can't >> even execute the pragma. > > The header contains a signature for the file and important meta > information which make the file useless if they are wrong.

Re: [sqlite] What does "PRAGMA integrity_check" actually do?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Richard Klein wrote: > Writing to the beginning of the file (the first 40 bytes > or so) corrupts the database so badly that SQLite can't > even execute the pragma. The header contains a signature for the file and important meta information which

[sqlite] erratum

"does undetected" => "goes undetected" ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] What does "PRAGMA integrity_check" actually do?

I wrote a test program to deliberately trash a database file, and then see if "PRAGMA integrity_check" could detect the corruption. The only thing I found that works is writing beyond the end of the file. Writing to the beginning of the file (the first 40 bytes or so) corrupts the database so

Re: [sqlite] Hi, a question from Colombia

Carlos, If you don't mind, I'll answer through the list, because there are people there who know much more than I do. Here's your question: > Here I'm looking for subtract two datefields from diferentes tables if > the field1 is not null, if that field is null the value is 'without > close'

Re: [sqlite] Why the difference in these 2 SQLite files?

OK, any DB write to this file on the desktop (not the mobile device) will make the query go fast. A completely trivial update will do it, so it looks somehow the db write on the desktop does makes it go fast. I can't check now if the same applies when doing this db write on the first PC and

Re: [sqlite] how to replace the sentence limit

Carlos Suarez wrote: > Thanks Igor, I haven't note about the diagram, now I want to ask you > help for this: > > CASE > WHEN field1 IS NULL > THEN 'SIN CERRAR' > ELSE (STRFTIME('%J',field1) - STRFTIME('%J',field2)) > END AS DIAS_CERRAR, Yes, please feel free to

Re: [sqlite] 600ms for simple query: How to optimize it?

On Jan 9, 2009, at 3:16 PM, Lukas Haase wrote: > > SELECT t.topic, t.length > FROM printgroup AS pg1 > LEFT JOIN printgroup AS pg2 ON pg1.printgroup = pg2.printgroup > LEFT JOIN topics AS t ON t.topicID = pg2.topicID > LEFT JOIN topic_ids AS ti ON ti.topicID = pg1.topicID > WHERE ti.topic_textID

Re: [sqlite] how to replace the sentence limit

Thanks Igor, I haven't note about the diagram, now I want to ask you help for this: CASE WHEN field1 IS NULL THEN 'SIN CERRAR' ELSE (STRFTIME('%J',field1) - STRFTIME('%J',field2)) END AS DIAS_CERRAR, Igor Tandetnik escribió: > Carlos Suarez > wrote: > >>

Re: [sqlite] Hi, a question from Colombia

Hi, Carlos, People here are helpful and friendly, and we will try to help you in our abundant free time, but try to include as much information as possible with your question, so we can give you meaningful answers. For example, don't just ask "why is my SQL so slow?", but tell us specifically

Re: [sqlite] 600ms for simple query: How to optimize it?

On Fri, 09 Jan 2009 21:16:03 +0100, Lukas Haase wrote in General Discussion of SQLite Database : >Hello Richard! > >Thank you very much!! It works! :-) > > >Indeed. 0-10 milliseconds instead of 500-800 :-) > >But may you tell me why this works and

Re: [sqlite] Hi, a question from Colombia

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Carlos Suarez wrote: > Hi, my name is Carlos Suarez from Colombia, I have a few problems while > I migrated from hsql to sqlite and I want to know if you can help me > with some of this stuff by this way of mail or have I to quote in > somewhere?.

Re: [sqlite] how to replace the sentence limit

Carlos Suarez wrote: > Hello, my name is Carlos I need to replace de common sentence in > hsqldb --LIMIT min rows - max rows with something for delimit a query > in sqlite because this is not supported select * from mytable limit 10 offset 5; -- or select * from

Re: [sqlite] Why the difference in these 2 SQLite files?

Have now also looked at the full EXPLAIN and although understand that it looks indentical to me for both databases: Slow - addropcode p1 p2 p3 0 Goto0 34 1 MemLoad 0 0 2 If 0

Re: [sqlite] Memory loss with sqlite3_step()

On Fri, Jan 09, 2009 at 07:42:27PM +, John Delaney scratched on the wall: > > Hi, > > I am a new user so please make allowances accordingly. > > Using SQLite3 3.5.9, I am seeing a consistent rise in memory with each > call to sqlite3_step(). > This is basic functionality so I am

[sqlite] how to replace the sentence limit

Hello, my name is Carlos I need to replace de common sentence in hsqldb --LIMIT min rows - max rows with something for delimit a query in sqlite because this is not supported thanks, ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] 600ms for simple query: How to optimize it?

Hello Richard! Thank you very much!! It works! :-) D. Richard Hipp schrieb: > On Jan 7, 2009, at 6:11 PM, Lukas Haase wrote: > >> Hello, >> >> Can somebody tell me why this (simple) query take so much time? This >> query does nothing more than querying a table and JOINing two other >> tables

Re: [sqlite] Collation not used

Igor Tandetnik wrote: > Martin Engelschalk > wrote: > >> The problem appeared when my users eliminated leading blanks from the >> data. >> > > I'm not sure I understand. What does this have to do with collating > numbers? Are you saying you were storing

Re: [sqlite] KEY keyword

ed wrote: > however, I am still not clear if the conflict resolution clause will > be used > on a table with no UNIQUE or PRIMARY KEY columns? It may if there are other constraints, such as CHECK or NOT NULL. For more details, see http://sqlite.org/lang_conflict.html Igor

Re: [sqlite] Bool Values

A design constraint of SQLITE was to avoid adding a bunch of bool present in other DB's. (Bad joke, couldn't resist) -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Emil Obermayr Sent: Friday, January 09, 2009 2:58 PM To:

Re: [sqlite] Collation not used

Martin Engelschalk wrote: > The problem appeared when my users eliminated leading blanks from the > data. I'm not sure I understand. What does this have to do with collating numbers? Are you saying you were storing numbers as strings, with leading blanks, and had a

[sqlite] Why the difference in these 2 SQLite files?

Have a large (about half a Gb) SQLite db file, made with version 3.6.1. I make this file on one PC (Windows XP) put the file on a USB stick, take it home, copy it to the home PC (Win XP) local drive and then from there copy the file to a Windows mobile (WM6) device, a Samsung Omnia. On that device

[sqlite] Bool Values

Is there a way to use bool-values to make migration from other DB easier? e.g. select * from address where local = true like defining true as a contant that represents a numeric 1? ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] KEY keyword

Igor,thank you for the explanation however, I am still not clear if the conflict resolution clause will be used on a table with no UNIQUE or PRIMARY KEY columns? I would assume not but i couldn't find the answer in the documentation. thanks, ed On Fri, Jan 9, 2009 at 11:17 AM, Igor Tandetnik

Re: [sqlite] KEY keyword

On Fri, 9 Jan 2009 10:05:49 -0800, ed wrote in General Discussion of SQLite Database : >Hello, >I have a sqlite 3.3.4 app using a db with the following schema: > >CREATE TABLE my_data(n INTEGER KEY, s INTEGER, p INTEGER, od VARCHAR); > >Is the KEY

Re: [sqlite] Collation not used

Hello Igor, in the beginning this was an attempt to circumvent the missing DESC Indices prior to Version 3.3.0. However, it all grew and now i do all kinds of things using collations. I admit that i could achieve most of them in a different way, but i would have to change my application. The

[sqlite] Memory loss with sqlite3_step()

Hi, I am a new user so please make allowances accordingly. Using SQLite3 3.5.9, I am seeing a consistent rise in memory with each call to sqlite3_step(). My code pattern to store a 14830 byte buffer is: A. mem = sqlite3_memory_used(); sqlite3_prepare_v2(... B. mem =

Re: [sqlite] How to create a Datatable [first steps of newbie]

What does your connection string look like? On Fri, Jan 9, 2009 at 2:36 PM, Alessio Forconi wrote: > Thanks for your suggestion, I do not think that is the problem because > the connection is the one used with success in other parts of the > program, you show the code that I

Re: [sqlite] How to create a Datatable [first steps of newbie]

Thanks for your suggestion, I do not think that is the problem because the connection is the one used with success in other parts of the program, you show the code that I wrote following your suggestions: string sqlString = "SELECT * FROM Proclamatori"; conn = new

Re: [sqlite] Collation not used

Martin.Engelschalk wrote: > yes, you are right, Thank you. > Is there a reason for this? Why would you want a collation function for numbers? What are you trying to achieve? Igor Tandetnik ___ sqlite-users mailing list

Re: [sqlite] KEY keyword

ed wrote: > I have a sqlite 3.3.4 app using a db with the following schema: > > CREATE TABLE my_data(n INTEGER KEY, s INTEGER, p INTEGER, od VARCHAR); > > Is the KEY keyword utilized? The way this statement is parsed, column 'n' has the type of 'INTEGER KEY'. Remember, SQLite

[sqlite] Hi, a question from Colombia

Hi, my name is Carlos Suarez from Colombia, I have a few problems while I migrated from hsql to sqlite and I want to know if you can help me with some of this stuff by this way of mail or have I to quote in somewhere?. thanksfully Carlos Suarez

Re: [sqlite] update PHP

Am Donnerstag, 8. Januar 2009 schrieb Kees Nuyt: > Note: With Apache, the easiest location for sqlite3.dll is > the .../apache/bin directory. php_pdo will find it there. > Perhaps the same goes for lighttpd. It works with sqlite3.dll in die PHP-directory. Thanks for that information. Everything

[sqlite] KEY keyword

Hello, I have a sqlite 3.3.4 app using a db with the following schema: CREATE TABLE my_data(n INTEGER KEY, s INTEGER, p INTEGER, od VARCHAR); Is the KEY keyword utilized? Will it act the same as a primary key ? Ultimately, i'm trying to determine if the KEY will enforce a unique constraint,

Re: [sqlite] How to turn off Indexing in SQLite?

> > I am using SQLite and C interface. > > > > How to turn off Indexing in SQLite? create the SQL so the index can not be used add an " OR ( 1 = 0) to the end of the where clause. ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] How to create a Datatable [first steps of newbie]

It should be "Data Source=database.db" not "DataSource=database.db". I didn't realize this because of the line break in the email. This will create a new, empty database named database.db in the directory where your application is running. On Fri, Jan 9, 2009 at 7:32 AM, Alessio Forconi

Re: [sqlite] Retrieving NANs

On Thu, Jan 8, 2009 at 7:03 PM, Gerry Snyder wrote: > Sander Jansen wrote: >> I need to store NaNs in my database and able to retrieve them as well. >> >> Since sqlite will give me back 0.0 when I call sqlite3_column_double >> and a result contains a NAN, >> I was

Re: [sqlite] Collation not used

Hello Dan, yes, you are right, Thank you. Is there a reason for this? martin Dan schrieb: > On Jan 9, 2009, at 9:43 PM, Martin.Engelschalk wrote: > > >> Hello list, >> >> I definied a collation and used it in the order by - clauses of >> queries. >> In one query, sqlite calls the collation

Re: [sqlite] Collation not used

On Jan 9, 2009, at 9:43 PM, Martin.Engelschalk wrote: > Hello list, > > I definied a collation and used it in the order by - clauses of > queries. > In one query, sqlite calls the collation function, and in the other > query, it does not (i checked by inserting a printf inside the >

[sqlite] Collation not used

Hello list, I definied a collation and used it in the order by - clauses of queries. In one query, sqlite calls the collation function, and in the other query, it does not (i checked by inserting a printf inside the collation function). The queries differ only in the order by - clause. This

Re: [sqlite] 50 inserts takeing 8 seconds

On Thu, Jan 08, 2009 at 03:45:26PM +0530, Pramoda M. A scratched on the wall: > Hi all. > > My desktop system with taking 8 seconds to insert 100 rows with only one > column... BEGIN INSERT... INSERT... INSERT... INSERT... INSERT... ... COMMIT On Thu, Jan 08, 2009 at

Re: [sqlite] How to turn off Indexing in SQLite?

"Pramoda M. A" wrote in message news:f7846b8f3c78c049b6a1dff861f6c16f03115...@kcinblrexb01.kpit.com > How to turn off Indexing in SQLite? Don't create any indexes on any of your tables. Igor Tandetnik ___ sqlite-users

Re: [sqlite] How to create a Datatable [first steps of newbie]

Thanks... I get this error "Data Source cannot be empty. Use :memory: to open an in-memory database" but the connection is sattamante the same as that used in other parts of the program Roosevelt Anderson ha scritto: > If you trying to extract data from a sqlite database to a DataTable > here

[sqlite] How to turn off Indexing in SQLite?

Hi All, I am using SQLite and C interface. How to turn off Indexing in SQLite? With Regards, Pramoda.M.A KPIT Cummins Infosystems Limited | Bengaluru ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Transfer data between databases

On Fri, Jan 09, 2009 at 09:18:09AM +0100, Pierre Chatelier wrote: > I have two separate SQLite database files, but containing the same > kind of tables. Is there a quick way to copy rows from one table of > a file to the same table of the other file ? ATTACH DATABASE 'fromdb.sqlite' AS fromdb;

[sqlite] 50 inserts takeing 8 seconds

Hi all. My desktop system with taking 8 seconds to insert 100 rows with only one column... I am inserting using Ffor loop (C interface) Can anybody help me? With Regards, Pramoda.M.A KPIT Cummins Infosystems Limited | Bengaluru

[sqlite] .dump and transactions

Hello together, I run a small script every night via cron to backup a database using the ".dump" statement. SQlite version is 3.6.6.2 on Linux. Normally this script works fine and from time to time I get a backup file that looks like this: --- BEGIN TRANSACTION; END TRANSACTION;

Re: [sqlite] Transfer data between databases

Hi Pierre, you can open both database files at the same time and using the same connection: Look at the attach - Command: http://www.sqlite.org/lang_attach.html You can then use both databases in the same statement (insert into MyTable (... columns ...) select ... columns ... from

Re: [sqlite] Transfer data between databases

2009/1/9 Pierre Chatelier : > Hello, > > This may be a question with a very short answer... > I have two separate SQLite database files, but containing the same > kind of tables. Is there a quick way to copy rows from one table of a > file to the same table of the other file

[sqlite] Transfer data between databases

Hello, This may be a question with a very short answer... I have two separate SQLite database files, but containing the same kind of tables. Is there a quick way to copy rows from one table of a file to the same table of the other file ? I suppose "no", and I will have to perform SELECT on