[sqlite] UNIQUE constraint failed: ip_addr.pid

2015-09-14 Thread Martin Engelschalk
Hi Petr, if you Google for "database table primary key" the first few results lead to quite good explanations. Also, the english wikipedia's article "Unique key" explains primary keys. HTH Martin Am 14.09.2015 22:25 schrieb Petr L?z?ovsk? : > > I had googled to verify such idea before, but

[sqlite] UNIQUE constraint failed: ip_addr.pid

2015-09-14 Thread Martin Engelschalk
Hello Petr, defining the column pid as INTEGER PRIMARY KEY you added an implicit contraint; a primary key means that only one record with a given value of pid can exist in the table. See https://www.sqlite.org/lang_createtable.html#rowid Martin Am 14.09.2015 um 21:04 schrieb Petr L?z?ovsk?: >

[sqlite] Determine query type

2015-08-07 Thread Martin Engelschalk
Hi Stephan Ben and all, by the way: checking the column count only applies to SQLite. Other databases (Postgres, Oracle) support the insert into returning or update ... returning syntax, resulting in statements that change the database and have columns at the same time. This is a feature

[sqlite] How to get length of all columns in a table

2015-06-01 Thread Martin Engelschalk
Hi, the length funktion takes an expression as an argument, for example a column name like select length(MyColumn) from MyTable; Offhand, I can think of no way to calculate the sum of the lengths of the contents of all columns of a table without naming all the columns. There are two ways:

[sqlite] SQL Syntax To Copy A Table

2015-03-26 Thread Martin Engelschalk
Hi Nige, create table as select * from See also http://www.sqlite.org/lang_createtable.html Martin Am 26.03.2015 um 16:29 schrieb Nigel Verity: > Hi > > I know this must seem a fairly dumb question, but I can't find an easy way to > create a copy of table using just SQL. > > My requirement

[sqlite] Query help

2015-03-23 Thread Martin Engelschalk
(select groupid,groupname from groups union select 0, '*') g_helper on ( g_helper.groupid = EnginePreferences.groupid); Martin Am 23.03.2015 um 10:04 schrieb Martin Engelschalk: > Hi, > > SELECT engine,coalesce(groupname,*) as > groupname,data

[sqlite] Query help

2015-03-23 Thread Martin Engelschalk
Hi, SELECT engine,coalesce(groupname,*) as groupname,databasename,key,value FROM EnginePreferences left join groups on (groups.groupid = EnginePreferences.groupid); OR SELECT engine,coalesce(groupname,*) as groupname,databasename,key,value FROM EnginePreferences left join (select

[sqlite] data which when inserted into a table cannot be queried - a bug ?

2015-03-11 Thread Martin Engelschalk
Hi, SELECT oid, ip, name FROM hosts ; 1|a.proper.host.name|2886748296 shows that ip = 'a.proper.host.name ' and name = 2886748296, so of course your queries give no data. The problem is that your insert statement does not name the columns, which is never a good idea. Martin Am 11.03.2015 um

Re: [sqlite] replace many rows with one

2014-12-10 Thread Martin Engelschalk
Hi Simon, Am 10.12.2014 12:39, schrieb Simon Slavin: Dear folks, A little SQL question for you. The database file concerned is purely for data manipulation at the moment. I can do anything I like to it, even at the schema level, without inconveniencing anyone. I have a TABLE with about

Re: [sqlite] [SQLite]Ba​sic query

2014-12-08 Thread Martin Engelschalk
Hi Shinichiro, If opening the database failed with sqlite3_open() != SQLITE_OK, it is probably best to check the return code and error message using sqlite3_errmsg(). It will give a strong hint. Debugging into the sqlite3 code itself never worked for me. Make sure that - the directory the

Re: [sqlite] [SQLite]Ba​sic query

2014-12-08 Thread Martin Engelschalk
Hi Shinichiro, which Version of VC++ do you use? As far as I know, older versions do not support debugging source files with more than 65535 lines. Also, why do you want to debug into the sqlite.c file? The file sqlite.c is just another source file for your compiler; optimizations would

Re: [sqlite] Search for text in all tables

2014-12-04 Thread Martin Engelschalk
Hi Baruch, in such cases I do a sqlite3_prepare_v2 on "select * from mytable LIMIT 1" and get the column names using sqlite3_column_count and sqlite3_column_name. Martin Am 04.12.2014 11:45, schrieb Baruch Burstein: On Thu, Dec 4, 2014 at 12:18 PM, Paul Sanderson <

Re: [sqlite] Search for text in all tables

2014-12-04 Thread Martin Engelschalk
Hi Baruch, no, not with SQL as I know it. Perhaps you can .dump and .output the database to a text file using sqlite3 command shell, replace your text and then create the database from the SQL using .read. HTH Martin Am 04.12.2014 08:44, schrieb Baruch Burstein: Hi, Is it possible to

Re: [sqlite] Comparing to NULL in prepared statment

2014-11-05 Thread Martin Engelschalk
Hi Baruch, in such cases I use one of the following methods: SELECT col1 FROM table1 WHERE col2=:val or (col2 is null and :val is null) SELECT col1 FROM table1 WHERE coalesce(col2, '#') = coalesce(:val, '#') where '#' is a value that i know is never used in col2 (this is a drawback) Yours

Re: [sqlite] Make a database read-only?

2014-10-14 Thread Martin Engelschalk
, Thank you, I'll definitely look into that. It's unfortunate that there isn't a simpler way to do this... oh well. Best, Ross On Tue, Oct 14, 2014 at 7:22 AM, Martin Engelschalk < engelsch...@codeswift.com> wrote: Hello Ross, you could add triggers to all tables that RAISE(ROLLBACK, 'For

Re: [sqlite] Make a database read-only?

2014-10-14 Thread Martin Engelschalk
Hello Ross, you could add triggers to all tables that RAISE(ROLLBACK, 'Forbidden') on all operations (insert, update and delete), see http://www.sqlite.org/lang_createtrigger.html, bottom of the page. However, it is difficult to see how to stop the downloaders from removing these triggers or

Re: [sqlite] how to add multiple columns at a time

2014-10-08 Thread Martin Engelschalk
Hello Anand, the syntax diagram at https://www.sqlite.org/lang_altertable.html shows that an alter table statement for sqlite can contain "add column" only once. I myself came against that fact when porting my DDL statements from PostgreSQL to SQLite. So, the answer is "you can't". Martin

Re: [sqlite] The IN (... ) clause

2014-09-14 Thread Martin Engelschalk
Hi Jose, you are probably looking for the between-Operator: Open http://www.sqlite.org/lang_expr.html and search for "The BETWEEN operator" inn you case, date BETWEEN '2014-01-01' AND '2014-01-05' Martin Am 14.09.2014 07:07, schrieb jose isaias cabrera: Greetings! I know that the IN

Re: [sqlite] String compare scoring functions

2014-08-29 Thread Martin Engelschalk
Hello Maurizio, 1) Logical answer Comparing two addresses and getting a similarity score can be a VERY complicated task if your requirements are high enough, and you can buy some very expensive software to perform this. I know because I work with some. 2) Technical answer For such a

Re: [sqlite] SELECT ... GROUP BY: Bug or misunderstanding?

2014-08-22 Thread Martin Engelschalk
Hi Christoph, the id column does not appear in an aggregate function and also not in group by. Your statement uses 'GROUP BY Name' and so returns exactly one row per name. If there are several rows with the same name, the ID of your result is from one of these rows. The appropriate

Re: [sqlite] Question about coalesce and data types

2014-08-21 Thread Martin Engelschalk
Am 21.08.2014 11:39, schrieb Clemens Ladisch: Martin Engelschalk wrote: It seems the solution is to actually pass all bind variable values by their appropriate sqlite3_bind_* - function instead of just using sqlite3_bind_text. However, this means quite a lot of work for me. Isn't it also work

Re: [sqlite] Question about coalesce and data types

2014-08-21 Thread Martin Engelschalk
, schrieb Clemens Ladisch: Martin Engelschalk wrote: create table TestTable (col_a numeric); insert into TestTable (col_a) values (1); retrieve the row, as expected: select * from TestTable where col_a = '1'; do not retrieve the row: select * from TestTable where coalesce(col_a, 5) = '1' Can

[sqlite] Question about coalesce and data types

2014-08-20 Thread Martin Engelschalk
Hello list, I checked the coalesce function and observed the follwoing results: I create a simple table with one column and one row: create table TestTable (col_a numeric); insert into TestTable (col_a) values (1); commit; The following statements retrieve the row, as expected: select *

[sqlite] Problem with coalesce in a where clause with bind variables

2014-08-06 Thread Martin Engelschalk
Hello List, I have a strange problem with a statement like select id from some_table where coalesce(some_col, 1) = :1 where :1 is a bind variable which i bind in my program using sqlite3_bind_text. I get no records, even if there are no null values anywhere in some_col. I get the

Re: [sqlite] Prepare statement in separate function

2011-10-11 Thread Martin Engelschalk
Hello John, why do you malloc() your DB- and Statement handle? I declare a sqlite3* pDB; sqlite3_stmt* pStmnt; then open the database with int nRet = sqlite3_open("MyDatabaseName", ); and prepare a statement using nRet = sqlite3_prepare_v2(pDB, "insert .", -1, , ); no need to malloc

Re: [sqlite] Update one table from matches in another

2011-08-10 Thread Martin Engelschalk
Hi, Am 10.08.2011 11:14, schrieb flakpit: > To see what matches the location table with locations in the inventory table, > I can issue the following command and it works returning 17 locations > matched and is correct. There are 21 locations in the locations table but > only 17 used in the

Re: [sqlite] SQLite and Java

2011-08-02 Thread Martin Engelschalk
Yin, A Google search of "sqlite java api" gives several good hits. Click on the first http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers and scroll down to Java Martin Am 02.08.2011 06:01, schrieb yinlijie2011: > Dear, > I want use SQLite, but my program language is Java. And >

Re: [sqlite] Better way to get records by IDs

2011-05-20 Thread Martin Engelschalk
Hi, to order, you have to use "order by". In that case, however, it gets complicated. SELECT * FROM Jobs WHERE rec IN (87, 33, 27,2, 1) order by case rec when 87 then 1 when 33 then 2 when 37 then 3 when 2 then 4 when 1

Re: [sqlite] Better way to get records by IDs

2011-05-20 Thread Martin Engelschalk
Hi, you want this: select * from Jobs where rec in (1, 2) Martin Am 20.05.2011 15:00, schrieb jose isaias cabrera: > Greetings. > > I would like to get a bunch of records of IDs that I already know. For > example, this table called Jobs, > rec,...,data,... > 1,...,aaa,... > 2,...,zzz,... > ...

Re: [sqlite] Three questions

2011-05-11 Thread Martin Engelschalk
Hello, This question does not arise with SQLite, because parallel transaction are not supported, as Igor and Pavel pointed out. However, consider this: If you have a unique constraint on a table like in your example, when should the database enforce it? To use your example and add a second

Re: [sqlite] Confused

2011-05-06 Thread Martin Engelschalk
now the queries are > running pretty fast (completes in ms :D ). > > Is this a good approach ? > Creating indexes affects the insert/update/delete query, isnt it? > > Thanks and Regards, > Arjabh > On Fri, May 6, 2011 at 2:06 PM, Martin Engelschalk< > engelsch...@codeswi

Re: [sqlite] Confused

2011-05-06 Thread Martin Engelschalk
Hello Arjabh, the autoindex is created for the rowid, because you did not define a column with type "integer primary key". See here: http://www.sqlite.org/lang_createtable.html#rowid SQLite can only use an index for where - clause in your statements if the columns in the where clause are the

Re: [sqlite] Cant get PRAGMA temp_store = MEMORY to work

2011-02-02 Thread Martin Engelschalk
Hi, i have something to add: SQLITE_DEFAULT_TEMP_CACHE_SIZE is not used anywhere in the sqlite.c file of the amalgamationand the comment above seems to be incomplete. Is this intended? Martin Am 02.02.2011 13:38, schrieb Martin Engelschalk: > Hi list, > > I have a problem getti

Re: [sqlite] Query help

2011-02-02 Thread Martin Engelschalk
Hello Marco, As far as i can see, the union is necessary. However, the second select in the union can be rewritten as a join: SELECT 'ID', id FROM MKObjects WHERE type='PANEL' AND platform='IPHONE' UNION SELECT prop_key, prop_value FROM MKProperties JOIN MKObjects on

[sqlite] Cant get PRAGMA temp_store = MEMORY to work

2011-02-02 Thread Martin Engelschalk
Hi list, I have a problem getting PRAGMA temp_store = MEMORY to work. I do a select joining two tables with an order by for which no index esists. This selects all the data in my database, 1 million records. The database size is 196 MB. When using version 3.2.5 (the amalgamation, no special

Re: [sqlite] 64 bit sqlite 3

2010-12-17 Thread Martin Engelschalk
Hi, we compiled the amalgamation with VC++ 2010 64 bit and had no problems whatsoever. Martin Am 17.12.2010 10:36, schrieb giuseppe500: > There is a version of SQLite 3 for 64-bit systems? > or, you can simply compile the source of sqlite3 at 64-bit with c++ 2008? > thanks. >

Re: [sqlite] Slowdown when switching from Version 3.2.5 to 3.7.4

2010-12-17 Thread Martin Engelschalk
Hi, it was my own fault. Sorry for the noise. 3.7.4 is a lot faster. Martin Am 17.12.2010 09:30, schrieb Wiktor Adamski: > There is a lot more synchronization in 3.7.4. If you disable it new > version may be faster. > ___ > sqlite-users mailing list >

Re: [sqlite] UTF-8

2010-12-16 Thread Martin Engelschalk
Hello Ming, sqlite does nothing to transform data between codepages, and it assumes that data you insert is passed in UTF8. However, sqlite will acept any data and store it. If the firefox plugin does not show you data correctly, then you problably did not pass correct UTF8 to sqlite. Can you

Re: [sqlite] Slowdown when switching from Version 3.2.5 to 3.7.4

2010-12-16 Thread Martin Engelschalk
010 at 4:01 AM, Martin Engelschalk< > engelsch...@codeswift.com> wrote: > >> Hello List, >> >> i tried switching from Version 3.2.5 to 3.7.4 to make use of the new >> features. >> >> I create a new database file and load 1.000.000 records into a sing

[sqlite] Slowdown when switching from Version 3.2.5 to 3.7.4

2010-12-16 Thread Martin Engelschalk
Hello List, i tried switching from Version 3.2.5 to 3.7.4 to make use of the new features. I create a new database file and load 1.000.000 records into a single table. Without changing anything in my own code (which reads from a file and does additional processing before inserting the

Re: [sqlite] sqlite3 question

2010-10-30 Thread Martin Engelschalk
Hello Lizhe, in order for the members in this list to help you, please provide more details. First, the most probable cause for this error is that the database file is in fact corrupted in some way or is not a sqlite database file. What step/steps leads to corruption? What sqlite function returns

Re: [sqlite] COUNT very slow

2010-09-24 Thread Martin Engelschalk
, but in this case an index on DateTime will help (except when most of the records are older than yesterday). Also, you could keep track of the number of records for each day with a table containing DateTime and RecordCount. > Il 24/09/2010 10.29, Martin Engelschalk ha scritto: >>

Re: [sqlite] COUNT very slow

2010-09-24 Thread Martin Engelschalk
Hello Michele, sqlite does not remember the number of records in a table. Therefore, counting them requires to scan the full table, which explains the slow perfornamce. This topic has been discussed previously in this list. See

Re: [sqlite] SQLite System Time

2010-09-19 Thread Martin Engelschalk
Hi, what are you selecting exactly? Do you use a sqlite date / time function? Have you read http://www.sqlite.org/lang_datefunc.html? Martin Am 19.09.2010 00:41, schrieb Ady Puiu: > Hello and sorry if post my question here but I'm not sure where to ask... > > From where does SQLite gets the

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

2010-08-20 Thread Martin Engelschalk
Am 20.08.2010 13:38, schrieb Max Vlasov: >> In my case (which is certainly not typical), a (several GB) large >> database is built up in several batches, one table at a time, while in >> parallel many intermediate files on the disk are created. This resulted >> in a very fragmented database

Re: [sqlite] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

2010-08-20 Thread Martin Engelschalk
Am 19.08.2010 23:56, schrieb Simon Slavin: > On 19 Aug 2010, at 9:27pm, Taras Glek wrote: > >> I really appreciate that sqlite got this feature to reduce >> fragmentation, but why not expose this as a pragma? > Do you have figures which suggest that reducing fragmentation leads to any >

Re: [sqlite] How can i install SQLite

2010-08-01 Thread Martin Engelschalk
Hi, sqlite needs not and cannot be installed. It is an emedded system which you link to your application. See http://www.sqlite.org/serverless.html Martin Am 01.08.2010 08:58, schrieb MKiran: > Please help me on SQLite install > > ___ >

Re: [sqlite] Coping with database growth/fragmentation

2010-07-23 Thread Martin Engelschalk
Hello Taras, List, I have been fighting the same problems described here for a long time, and have no real elegant solution. So, the proposed solution of the OP below would be ideal for me too. The proposed pragma could also define a number of pages to be allocated at once instead of a

Re: [sqlite] concat 2 const chars ?

2010-07-09 Thread Martin Engelschalk
Hi, i think you are asking a C question, and not an sql question. Is your example C code? If this is the case, you should read up on C basics. Feel free to contact me directly (also in German) Martin Am 09.07.2010 21:06, schrieb rollerueckwaerts: > Hello, > I try to get an sql query string from

Re: [sqlite] Fwd: A DLL for my WinXP

2010-06-21 Thread Martin Engelschalk
You also can search Google "sqlite dll download" and follow the first hit ... Am 21.06.2010 12:56, schrieb Arbol One: > Helloo! is any body there??!! > nock nock ... Is this group dead??!! > > Where can I find the DLL file for my winxp? > TIA > > > > Original Message >

Re: [sqlite] Fwd: A DLL for my WinXP

2010-06-21 Thread Martin Engelschalk
Here: http://www.sqlite.org/sqlitedll-3_6_23_1.zip Am 21.06.2010 12:56, schrieb Arbol One: > Helloo! is any body there??!! > nock nock ... Is this group dead??!! > > Where can I find the DLL file for my winxp? > TIA > > > > Original Message > Subject: A DLL for my WinXP

Re: [sqlite] how to install sqlite3 in windows+python2.5

2010-06-10 Thread Martin Engelschalk
http://www.sqlite.org/sqlitedll-3_6_23_1.zip Am 10.06.2010 08:05, schrieb zeal: > Hi, > > i could not find the sqlite3.dll from http://www.sqlite.org/download.html > or would you please paste the linkage here? > thanks and best wish for you > > >

Re: [sqlite] Problem with DATABASE_LOCKED

2010-03-15 Thread Martin Engelschalk
Hello Igor, yes, sorry, i forgot to mention that, i do have write access. The database file itself gets created. Martin Am 15.03.2010 20:02, schrieb Igor Tandetnik: > Martin Engelschalk > <engelsch...@codeswift.com> wrote: > >> we experience a problem at a customer

[sqlite] Problem with DATABASE_LOCKED

2010-03-15 Thread Martin Engelschalk
Dear Sqlite users, we experience a problem at a customer site, where the very first statement the program executes on a newly created database failes with a "database locked" error. The statement that failes is "PRAGMA synchronous = OFF". The reason seems to be that the customer inists on

Re: [sqlite] Error: no such table on .import

2010-02-12 Thread Martin Engelschalk
Hi, because of the semicolon following the table name in your .import - command. Remove it. Martin Phil Hibbs wrote: > I'm doing this in SQLite: > > sqlite> .separator tabs > sqlite> create table head >...> ( id varchar(10) >...> , tplnr varchar(20) >...> , plnal varchar(2) >

Re: [sqlite] SQLite - IIS, PHP and java

2010-02-10 Thread Martin Engelschalk
equential write?) > > As for Traffic: > Java could do 1 or 2 write's once a day. > PHP will be doing 2000 - 4000 reads a day. Mostly in the morning around > 08:30 and afternoon 17:00 > > Cheers > Alexis > > > Martin Engelschalk wrote: > >> Hi, >> &

Re: [sqlite] SQLite - IIS, PHP and java

2010-02-10 Thread Martin Engelschalk
Hi, the important question is: What about updates to the database? Will there be concurrent updates, or will the db be read only? Will some processes read an others write? What amount of traffic do you expect on the site? See http://www.sqlite.org/faq.html#q5 Martin alexis_ wrote: > Hi

Re: [sqlite] Newbie problem using special column name

2010-01-25 Thread Martin Engelschalk
Hi, try enclosing your column name with double quotes " create table test("column-1" varchar(255)) However, i strongly advise not to use this character, because it is the minus-operator in sql. You will have to make sure that you enclose the column name every time you (or somone other) uses

Re: [sqlite] Sqlite3 Optimization Question

2010-01-22 Thread Martin Engelschalk
Hi, perhaps you could drop the primary key. The query you mentioned, SELECT count(*) FROM table WHERE column = '%q' does not utilize it, and if you do execute queries which do, do not update the db, and have no other tables, then the primary key serves no function. Martin Michael Thomason

Re: [sqlite] size control of sqlite database

2010-01-14 Thread Martin Engelschalk
Hi Roger, yes, thank you, i did not see this. Martin Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > Martin.Engelschalk wrote: > >> However, i could not find a way to determine when the empty pages are >> used up and the file will start to grow again without checking

Re: [sqlite] Escaping strings to be used in queries in C/C++

2009-12-16 Thread Martin Engelschalk
t the sqlite3_mprintf() should suffice, however. And thanks > again for the help. > > Kurt > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Martin Engelschalk > Sent: Wednesday, December 16, 2009 11

Re: [sqlite] Escaping strings to be used in queries in C/C++

2009-12-16 Thread Martin Engelschalk
Hi, The function "sqlite3_mprintf" is what you look for. see http://www.sqlite.org/capi3ref.html#sqlite3_mprintf Also, you might want to use bind variables instead of putting literals into your SQL text. see http://www.sqlite.org/capi3ref.html#sqlite3_bind_blob Martin Kurt D. Knudsen wrote: >

Re: [sqlite] Fine-grainy error report needed

2009-11-16 Thread Martin Engelschalk
Hi, First, the index of the bind variable (second parameter to sqlite3_bind_, your 'i') must begin with 1, and not 0, see http://www.sqlite.org/capi3ref.html#sqlite3_bind_blob Sqlite does not use strong typing, which means that you can put any data into any column. However INTERGER

Re: [sqlite] Maximum length of the field name

2009-11-15 Thread Martin Engelschalk
Hi, AFAIK, there is no limit. At least, browsing http://www.sqlite.org/limits.html, I found none. The maximum length of an SQL statement, 100 by default, limits the column names you can use, because you have to issue a "create table" - statement. Martin Ev wrote: > What's the maximum

Re: [sqlite] Question about the update of tuples in sqlite-views

2009-10-20 Thread Martin Engelschalk
Hi, a view does not need to be updated. Think of a view as a stored select statement. Martin Koston, Thorsten (ICT) wrote: > Hello, > > i have a question about the update machanism for tuples in different > views: > > For example from a table we have three different views. > How will be the

Re: [sqlite] Table aliases

2009-10-09 Thread Martin Engelschalk
Hi, yes, creating a table will duplicate your data. However, a view will not: create view CurrentLanguage as select * from SomeLanguage; see http://www.sqlite.org/lang_createview.html Martin Shaun Seckman (Firaxis) wrote: > Happy Friday everyone! > > I've got several tables

Re: [sqlite] [Duplicates] How to keep only one row?

2009-08-17 Thread Martin Engelschalk
Hi, If you are looking for a delete - command, then you have to decide which of the duplicate rows you want to keep. Are they all the same even in the other fields? Perhaps you want to do something like delete from members where exists (select rowid from members m2 where m2.name = members

Re: [sqlite] Updating a database by email

2009-07-11 Thread Martin Engelschalk
Hi, sqlite as such has nothing to do with eMail. My Thought: I would write an application that queries a mailbox (via POP3, for example, there are many possible ways) every n seconds, analyses the mails it reads and performs the appropriate actions on the sqlite database. This could be a

Re: [sqlite] How to find the version of the database.

2009-06-26 Thread Martin Engelschalk
Hi, a database file does not have a version. You can access it with different versions of the library. AFAIK there is no way to determine what version of the library created it or which version wrote to it last. Martin Kalyani Phadke wrote: > Is there any way to find the version of SQlite3

Re: [sqlite] Order by term not in result set

2009-06-09 Thread Martin Engelschalk
Hi, the column name in the order by - clause "name" has to match one of the columns of the select statement, because it is a union. Your columns are "id", "url" ad "selected", none of which is "name". Obviously, your table does contain a column named "name", but because of the union this can

Re: [sqlite] synchronizing sqlite local data base to a remote data base

2009-06-08 Thread Martin Engelschalk
Hi, What Simon is right. When solving a similar problem in the past I created special tables in both databases which i filled from triggers on the data tables. These tables contained the changed data which i then could reproduce on the other database using a special deamon process. However,

Re: [sqlite] Why row is not found?

2009-06-05 Thread Martin Engelschalk
ype... > but what is strange is that the same db and the same query worked fine > with sqlite 3.4.2 and the behavior changed with sqlite 3.6.x > > -- > Marco Bambini > http://www.sqlabs.com > http://www.creolabs.com/payshield/ > > > > > > > On Jun 5

Re: [sqlite] Why row is not found?

2009-06-05 Thread Martin Engelschalk
Please note that the following query returns the exact row: > SELECT * FROM lo_user WHERE CAST(lo_name AS TEXT)='admin'; > but I really don't have an explanation... > > -- > Marco Bambini > http://www.sqlabs.com > http://www.creolabs.com/payshield/ > > > > > >

Re: [sqlite] Why row is not found?

2009-06-05 Thread Martin Engelschalk
Hi, attachments do not make it through the list. There is no row with the value 'admin' in the field 'lo_name' in your table. Did you check that there are no blank spaces or other invisible characters? Martin Marco Bambini wrote: > Anyone can please explain me why this query: > SELECT * FROM

Re: [sqlite] how can we solve IF EXIST in SQLite

2009-06-02 Thread Martin Engelschalk
w recorde i jus want to update the first row coz > its also having the same product id i jus want set the quantity = 10+15 and > the date new date that is 30-05-2009 > and suppose if i insert row with different product_id it should be inserted > as it is.. > > Martin En

Re: [sqlite] how can we solve IF EXIST in SQLite

2009-06-02 Thread Martin Engelschalk
Hi, what language is this? it certainly is not SQL or a "query". I suspect that you can not use "insert or replace" (see http://www.sqlite.org/lang_insert.html), because you look first for a record with prod_batch_code=1000, and if you do not find it you insert one with prod_batch_code = 1003.

Re: [sqlite] OT: how best to convert sqlite3_int64 to and from string in a cross platform fashion?

2009-05-03 Thread Martin Engelschalk
Sorry, i forgot: my INT64 is the same as sqlite_int64 Martin Engelschalk wrote: > Hi, > > This is what i do, works for me. No OSX hovever, sorry. > > #ifdef _WIN32 > #define INT64 __int64 > #else > #define INT64 long long > #endif > > // From INT64 to String &g

Re: [sqlite] OT: how best to convert sqlite3_int64 to and from string in a cross platform fashion?

2009-05-03 Thread Martin Engelschalk
Hi, This is what i do, works for me. No OSX hovever, sorry. #ifdef _WIN32 #define INT64 __int64 #else #define INT64 long long #endif // From INT64 to String INT64 iOther; char mBuffer[64]; #ifdef _WIN32 sprintf(mBuffer, "%I64d", iOther); #else sprintf(mBuffer, "%lld", iOther);

Re: [sqlite] Appending Text to a Column

2009-04-14 Thread Martin Engelschalk
Hi, use the || - Operator to concatenate text. + adds to numbers. update tblEntry set Tags = Tags || ' new text' where RowID = 13 martin centipede moto wrote: > I am trying to append text to a field within my database - sothat if a user > has stored 'search, tools' into the Tags column of

Re: [sqlite] what is the default for the commit when the connection is opened.

2009-04-08 Thread Martin Engelschalk
Hi, sqlite does not know an "auto commit". If you do not call "begin transaction", then every insert/update/delete statement is wrapped in its own transaction. This is like "auto commit" If you do call "begin transaction", you start a transaction which you have to finish with "commit" or

Re: [sqlite] Selecting records by INDEXED key

2009-03-31 Thread Martin Engelschalk
Hi, your select statement does not include a "where" or "order by" - clause for which the index can be used, which is the cause of the error. If you want to select "the records by their index sequence", you should use Select * From "APPLE" ORDER BY "MySurname"; The index will then be used

Re: [sqlite] Sqlite versus mySQL in PHP

2009-03-27 Thread Martin Engelschalk
Hi, wrap your inserts in a transaction. Place $q = sqlite_query("begin"); before your loop, and $q = sqlite_query("commit"); after your loop of inserts. Martin Anton Rifco wrote: > Hi guys, > > I would like to ask a question about sqlite in php. I don't know if I am > sending this message to

Re: [sqlite] SQLITE : Constraint question

2009-03-11 Thread Martin Engelschalk
Hi, sqlite does not enforce datatypes. In this, sqlites works differently from other database engines. See http://www.sqlite.org/different.html and search for "*Manifest typing" The key sentence is *"SQLite thus allows the user to store any value of any datatype into any column regardless of

Re: [sqlite] SELECT queries and NULL value parameters

2009-03-11 Thread Martin Engelschalk
= ''; > 1 > sqlite> SELECT Count(*) FROM foo WHERE a = NULL; > 0 > sqlite> SELECT Count(*) FROM foo WHERE a IS NULL; > 1 > sqlite> SELECT Count(*) FROM foo WHERE a IS NULL OR a = ''; > 2 > sqlite> > > >> Tom >> >> -Original Message

Re: [sqlite] insert in C

2009-03-11 Thread Martin Engelschalk
Hi, use sqlite3_prepare and sqlite3_bind. See http://www.sqlite.org/capi3ref.html#sqlite3_prepare and sqlite3_bind_text under http://www.sqlite.org/capi3ref.html#sqlite3_bind_blob const char* szTail=0; sqlite3_stmt* pVM; int nRet = sqlite3_prepare(mpDB, "insert into table1 values(?)",

Re: [sqlite] set a Trigger on select

2009-03-05 Thread Martin Engelschalk
Hi, you are right, afaik triggers on select are not supported. I would define a function, read the data i need from the hardware inside the function and return it. See http://www.sqlite.org/capi3ref.html#sqlite3_create_function select MyField, GetHardwareSerialNumber() from MyTable Martin

Re: [sqlite] Random Syntax changed in 3.6.11?

2009-03-02 Thread Martin Engelschalk
Hi, in http://www.sqlite.org/lang_corefunc.html the random() function is documented as taking no arguments, So, use SELECT word FROM dict ORDER BY RANDOM() LIMIT 1; I tried an older version of sqlite (can't say which, but 3.*), and could call random() with 0, 1, 2, 3 and 4 parameters. I would

Re: [sqlite] Is this possible?

2009-03-01 Thread Martin Engelschalk
.. and if anything is returned, displaying > the line from Table A followed by any matches. But ideally it seems > there should be some way to do this with a single call to SQLite. > > If grabbing a blank from table A to put before each line of table B is > too difficult the

Re: [sqlite] Is this possible?

2009-03-01 Thread Martin Engelschalk
Hi Mike, sorry, i don't fully get it. However, a single SQL statement can not return data rows of different structure as you indicated in your example. Especially, I am confused about "blank". It seems to come from Table A, same as "Group *". Do you want to put "Group *" as a sort of headline,

Re: [sqlite] Is this possible?

2009-03-01 Thread Martin Engelschalk
Hi Mike, you can't use attachments in this mailing list. Best post your data model, some data and the desired result. Martin Mike Yenco wrote: > Is there a way that SQLite can return all matching items to a search > string in Table B, but return a group name from Table A before each > set of

Re: [sqlite] running sqlite with gcc C, continuation.....

2009-02-25 Thread Martin Engelschalk
Hi, you have to compile sqlite3.c, using something like gcc -c sqlite3.c making an object file, and then linking it to your own program. Your call combines compiler and linker using only your own source file. Also, it should not be necessary to define sqlite3_open and sqlite3_close (or any

Re: [sqlite] running sqlite with gcc C

2009-02-25 Thread Martin Engelschalk
Hello Michael, download the source code, compile it with your compiler and link it to your program. See http://www.sqlite.org/download.html. Its easiest to use the amalgamation: sqlite-amalgamation-3_6_11.zip There is indeed no

Re: [sqlite] number of columns in a table

2009-02-18 Thread Martin Engelschalk
Hi, see http://www.sqlite.org/capi3ref.html#sqlite3_column_count. Do this: sqlite3_stmt *pStmnt; sqlite3_prepare(YourDbHandle, "select * from YourTable limit 1", -1, , 0); int NumberOfColumns = sqlite3_column_count(pStmnt); sqlite3_finalize(pStmnt) Martin baxy77bax wrote: > hi, > > my

Re: [sqlite] Group by week

2009-02-04 Thread Martin Engelschalk
Hi, perhaps you want to look at http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions or perhaps you would want to post a little more information about your problem Martin Moshe Sharon wrote: > Hi > > How can I select group by week > > moshe >

Re: [sqlite] segmentation violation in fulltest on Mac OS X

2009-01-19 Thread Martin Engelschalk
Hello, Threads: use them, but don't abuse them Threads don't kill programs, programmers do ;-) Martin D. Richard Hipp wrote: > On Jan 19, 2009, at 3:50 AM, Jens Miltner wrote: > > >> Hello, >> >> I just upgraded to sqlite 3.6.10 and keep getting a segmentation >> violation when running the

Re: [sqlite] Collation not used

2009-01-09 Thread Martin Engelschalk
Igor Tandetnik wrote: > Martin Engelschalk > <engelsch...@codeswift.com> 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 >

Re: [sqlite] Collation not used

2009-01-09 Thread Martin Engelschalk
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

Re: [sqlite] Delete Trigger on INSERT OR REPLACE?

2009-01-02 Thread Martin Engelschalk
t or replace into ch(fname, red, green, blue, dsize) > values('xxx', 0, 0, 0, 0); > sqlite> select chId from blobdata where chId = 1; > 1 > > The entry is still here. > > Tobias > > > Martin Engelschalk schrieb: > >> Hello Tobi, >> >> See

Re: [sqlite] Delete Trigger on INSERT OR REPLACE?

2009-01-02 Thread Martin Engelschalk
Hello Tobi, See http://www.sqlite.org/lang_conflict.html, near the bottom: "When this conflict resolution strategy deletes rows in order to satisfy a constraint, it does not invoke delete triggers on those rows. This behavior might change in a future release." I would be interested if an

Re: [sqlite] sqlite3_create_function callback problem

2008-12-17 Thread Martin Engelschalk
Hi, i suspect that you problem is the class MyClass. Try to define your function "ScorePosition" as a C-function outside of a class. Martin Guillaume Schub wrote: > Hi everyone, > > I have spent nearly one day on this already with no success. I am > currently trying to create some custom

  1   2   >