[sqlite] UNIQUE constraint failed: ip_addr.pid

[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 have no luck.
>
> Thanks, L.
>
> > 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?:
> >> Have following table:
>
> >> CREATE TABLE ip_addr
> >> (
> >> /*! Person identificator (PID) %%a */ pid INTEGER PRIMARY KEY NOT NULL,
> >> /*! IP address %%b */ ip_addr VARCHAR (16) NOT NULL,
> >> /*! Status: 0 - Allowed, Unassigned to specific customer (blocked) */
> >> /*! Status: 1 - Allowed, Asigned to concrete customer */
> >> /*! Status: 2 - Disallowed, Assigned to blocked user */
> >> /*! Status: 3 - Disallowed, Assigned to history user */
> >> /*! Status of IP address %%c */ ip_status INTEGER NOT NULL,
> >> /*! Type: 1 - Private */
> >> /*! Type: 2 - Public */
> >> /*! Type: 3 - IPv6 */
> >> /*! Type of IP address %%d */ ip_type INTEGER NOT NULL,
> >> /*! Date of blocking %%e */ blocked_at INTEGER,
> >> /*! Blocking note %%f */ blocking_note VARCHAR
> >> );
> >> )
>
> >> If inserting row containing PID already exist in table, sqlite generate 
> >> %subj% error. But there is not such constraint in database definition. Did 
> >> I miss something?
>
> >> L.
>
> >> ___
> >> sqlite-users mailing list
> >> sqlite-users at mailinglists.sqlite.org
> >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> > ___
> > sqlite-users mailing list
> > sqlite-users at mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UNIQUE constraint failed: ip_addr.pid

[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?:
> Have following table:
>
> CREATE TABLE ip_addr
> (
> /*! Person identificator (PID) %%a */ pid INTEGER PRIMARY KEY NOT NULL,
> /*! IP address %%b */ ip_addr VARCHAR (16) NOT NULL,
> /*! Status: 0 - Allowed, Unassigned to specific customer (blocked) */
> /*! Status: 1 - Allowed, Asigned to concrete customer */
> /*! Status: 2 - Disallowed, Assigned to blocked user */
> /*! Status: 3 - Disallowed, Assigned to history user */
> /*! Status of IP address %%c */ ip_status INTEGER NOT NULL,
> /*! Type: 1 - Private */
> /*! Type: 2 - Public */
> /*! Type: 3 - IPv6 */
> /*! Type of IP address %%d */ ip_type INTEGER NOT NULL,
> /*! Date of blocking %%e */ blocked_at INTEGER,
> /*! Blocking note %%f */ blocking_note VARCHAR
> );
> )
>
>
> If inserting row containing PID already exist in table, sqlite generate 
> %subj% error. But there is not such constraint in database definition. Did I 
> miss something?
>
> L.
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Determine query type

[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 I missed in SQLite occasionally, but I guess it isn't 
SQL standard.

Martin

Am 06.08.2015 um 18:57 schrieb Ben Newberg:
> Excellent. This is exactly what I was looking for.
>
> Thanks.
>
> On Thu, Aug 6, 2015 at 11:50 AM, Stephan Beal  
> wrote:
>
>> On Thu, Aug 6, 2015 at 6:46 PM, Ben Newberg  wrote:
>>
>>> Is there a good way to determine if a sql query is either attempting to
>>> modify a database, or simply querying it?
>>>
>> Check the column count of the prepared statement: it will be >0 for a
>> select or select-like pragma. Anything else is an update, delete, drop,
>> create table/view, non-select-like pragma, or similar.
>>
>> --
>> - stephan beal
>> http://wanderinghorse.net/home/stephan/
>> http://gplus.to/sgbeal
>> "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
>> those who insist on a perfect world, freedom will have to do." -- Bigby
>> Wolf
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[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:

select length(MyColumn1 || MyColumn2 || MyColumn3) from MyTable;
or
select length(MyColumn1) + length (MyColumn2) + length (MyColumn3) from 
MyTable;

Also, you will probably want to watch for columns with null values. So 
this will work:

select coalesce(length(MyColumn1),0) + coalesce(length (MyColumn2),0) + 
coalesce(length (MyColumn3),0) from MyTable;

HTH
Martin


Am 01.06.2015 um 17:12 schrieb Drago, William @ CSG - NARDA-MITEQ:
> All,
>
> What is the correct syntax for getting the length of all columns in a table? 
> I tried
>
> SELECT Length(*) FROM myTable;
>
> and
>
> Length(SELECT * FROM myTable);
>
> Neither one of those works, and I can't find anything on line.
>
> Thanks,
> --
> Bill Drago
> Senior Engineer
> L3 Narda-MITEQ
> 435 Moreland Road
> Hauppauge, NY 11788
> 631-272-5947 / William.Drago at L-3COM.com
>
>
> CONFIDENTIALITY, EXPORT CONTROL AND DISCLAIMER NOTE:This e-mail and any 
> attachments are solely for the use of the addressee and may contain 
> information that is privileged or confidential. Any disclosure, use or 
> distribution of the information contained herein is prohibited. In the event 
> this e-mail contains technical data within the definition of the 
> International Traffic in Arms Regulations or Export Administration 
> Regulations, it is subject to the export control laws of the U.S.Government. 
> The recipient should check this e-mail and any attachments for the presence 
> of viruses as L-3 does not accept any liability associated with the 
> transmission of this e-mail. If you have received this communication in 
> error, please notify the sender by reply e-mail and immediately delete this 
> message and any attachments.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQL Syntax To Copy A Table

[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 is to take periodic snapshots of a names and addresses table, 
> to be stored in the same database as the master.
>
> The master table has one index - an auto-incrementing integer ID (non NULL, 
> primary key). There is no need for the corresponding field in the snapshot to 
> be indexed, but the integer ID does need to be copied across.
>
> I'm happy to copy the table structure in one operation and the data in 
> another, but if it can be done in a single operation so much the better.
>
> Thanks
>
> Nige
>
>
>   
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 

*Codeswift GmbH *
Kr?utlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelschalk at codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

[sqlite] Query help

[Martin Engelschalk]

Sorry, i was too fast.

Correction

SELECT engine,coalesce(groupname,'*') as 
groupname,databasename,key,value FROM EnginePreferences left join groups 
on (groups.groupid = EnginePreferences.groupid);

OR

SELECT engine,groupname,databasename,key,value
  FROM EnginePreferences
 left join (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,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 groupid,groupname from groups
>   union
> select 0, '*') g_helper  on ( g_helper.groupid = 
> EnginePreferences.groupid);
>
> HTH
> Martin
>
> Am 23.03.2015 um 09:50 schrieb Marco Bambini:
>> I have a table EnginePreference:
>> CREATE TABLE EnginePreferences (engine TEXT COLLATE NOCASE, 
>> databasename TEXT COLLATE NOCASE, key TEXT COLLATE NOCASE, value 
>> TEXT, groupid INTEGER, UNIQUE(engine,databasename,key))
>>
>> and a table Groups:
>> CREATE TABLE Groups (groupid INTEGER PRIMARY KEY, groupname TEXT 
>> UNIQUE COLLATE NOCASE)
>>
>> I need to select from EnginePreferences replacing groupid with 
>> groupname and I can do that with:
>> SELECT engine,groupname,databasename,key,value FROM EnginePreferences 
>> left join groups on (groups.groupid = EnginePreferences.groupid);
>>
>> What I really need is ALSO to replace groupname with * if groupid is 0.
>> Please note that groupid 0 is never written into the Groups table so 
>> my original query would return NULL as groupname with groupid is 0 
>> instead of the required "*".
>>
>> Any idea?
>> -- 
>> Marco Bambini
>> http://www.sqlabs.com
>> http://twitter.com/sqlabs
>> http://instagram.com/sqlabs
>>
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

-- 

*Codeswift GmbH *
Kr?utlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelschalk at codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

[sqlite] Query help

[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 groupid,groupname from groups
   union
 select 0, '*') g_helper  on ( g_helper.groupid = 
EnginePreferences.groupid);

HTH
Martin

Am 23.03.2015 um 09:50 schrieb Marco Bambini:
> I have a table EnginePreference:
> CREATE TABLE EnginePreferences (engine TEXT COLLATE NOCASE, databasename TEXT 
> COLLATE NOCASE, key TEXT COLLATE NOCASE, value TEXT, groupid INTEGER, 
> UNIQUE(engine,databasename,key))
>
> and a table Groups:
> CREATE TABLE Groups (groupid INTEGER PRIMARY KEY, groupname TEXT UNIQUE 
> COLLATE NOCASE)
>
> I need to select from EnginePreferences replacing groupid with groupname and 
> I can do that with:
> SELECT engine,groupname,databasename,key,value FROM EnginePreferences left 
> join groups on (groups.groupid = EnginePreferences.groupid);
>
> What I really need is ALSO to replace groupname with * if groupid is 0.
> Please note that groupid 0 is never written into the Groups table so my 
> original query would return NULL as groupname with groupid is 0 instead of 
> the required "*".
>
> Any idea?
> --
> Marco Bambini
> http://www.sqlabs.com
> http://twitter.com/sqlabs
> http://instagram.com/sqlabs
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 

*Codeswift GmbH *
Kr?utlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelschalk at codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

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

[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 13:08 schrieb Jason Vas Dias:
> Good day -
> This is the first problem I've encountered with SQLite having
> used it trouble free for a number of years, so I was surprised
> when I discovered I can insert data into a table that then cannot
> be queried :
>
> I have a table:
>
> CREATE TABLE hosts
> (
>  ip  INTEGER  NOT NULL ,
>  nameTEXT NOT NULL
> );
>
> My application does:
>
> BEGIN TRANSACTION;
> INSERT INTO hosts
> VALUES ( "a.proper.host.name", 2886748296)
> ; COMMIT;
>
> And then the data cannot be queried with sqlite :
>
> $ sqlite3 my.db
> SQLite version 3.8.8.1 2015-01-20 16:51:25
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> SELECT oid, ip, name FROM hosts ;
> 1|a.proper.host.name|2886748296
> sqlite> SELECT oid, ip, name FROM hosts WHERE ip == 2886748296;
> sqlite>
>
> Note : no result found ! Why ?
> No good to query by name either :
>
> sqlite> SELECT oid, ip, name FROM hosts WHERE name == "a.proper.host.name" ;
> sqlite>
>
> Selecting by oid==1 does work, but the whole point of this table
> for my application is to map host names and addresses to an
> unique integer OID which is the key referenced in many other
> tables:
> CREATE TABLE ...
> host INTEGER REFERENCES (hosts.oid) ON DELETE CASCADE ,
> ...
>
> Please could anyone suggest why sqlite is failing to select record 1 by
> the values of any of its fields except oid (ROWID) ?
>
> sqlite> EXPLAIN SELECT oid, ip, name FROM hosts
> WHERE ip == 2886748296;
> 0|Trace|0|0|0||00|
> 1|Goto|0|16|0||00|
> 2|OpenRead|0|11|0|2|00|
> 3|OpenRead|1|12|0|k(1,B)|00|
> 4|Int64|0|1|0|2886748296|00|
> 5|SeekGe|1|13|1|1|00|
> 6|IdxGE|1|13|1|1|01|
> 7|IdxRowid|1|2|0||00|
> 8|Seek|0|2|0||00|
> 9|IdxRowid|1|3|0||00|
> 10|Column|1|0|4||00|
> 11|Column|0|1|5||00|
> 12|ResultRow|3|3|0||00|
> 13|Close|0|0|0||00|
> 14|Close|1|0|0||00|
> 15|Halt|0|0|0||00|
> 16|Transaction|0|0|0||00|
> 17|VerifyCookie|0|25|0||00|
> 18|TableLock|0|11|0|hosts|00|
> 19|Goto|0|2|0||00|
> sqlite>
>
> Any ideas anyone ? Any responses gratefully received.
>
> I'm running sqlite built from source tarball: sqlite-autoconf-3080801.tar.gz
> on an Ubuntu 14.04.02 x86_64 8-core Intel i7 (Haswell) system with gcc-4.8.2.
> I built it from source because the Ubuntu sqlite 3.8.2 does not
> support the printf() function - but the same problem happens
> when the 3.8.2 version accesses the same database file - it cannot
> select any data from the 'hosts' table by non-OID field values.
>
> Thanks & Regards,
> Jason
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

-- 

*Codeswift GmbH *
Kr?utlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelschalk at codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

Re: [sqlite] replace many rows with one

[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 300 million (sic.) entries in it, as follows:

CREATE TABLE s2 (a TEXT, b TEXT, theCount INTEGER)

There are numerous cases where two or more rows (up to a few thousand in some 
cases) have the same values for a and b.  I would like to merge those rows into 
one row with a 'theCount' which is the total of all the merged rows.  
Presumably I do something like

CREATE TABLE s2merged (a TEXT, b TEXT, theCount INTEGER)

INSERT INTO s2merged SELECT DISTINCT ... FROM s2
insert into s2merged (a, b, theCount) select a, b, sum(theCount) from s2 
group by a, b;


and there'll be a TOTAL() in there somewhere.  Or is it GROUP BY ?  I can't 
seem to get the right phrasing.

Also, given that this is the last operation I'll be doing on table s2, will it 
speed things up to create an index on s2 (a,b), or will the SELECT just spend 
the same time making its own temporary index ?
Creating the index and select with index will probably be slower than 
select without index


Simon.
___
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]Ba​sic query

[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 database file redsides in exists
- the the user executing the program has the permission to read/write in 
the directory and the database file


This is not really the answer to your question, but you can probably 
solve your problem without debugging into sqlite.


Martin.

Am 08.12.2014 10:55, schrieb Shinichiro Yoshioka:

Hi, Clemens and Martin,

Thank you for your prompt responses.


How exactly are you using SQLite in your program?  If you are not using
the SQLite C API (sqlite3_* functions) directly,

I'm using sqlite APIs for calling from C source code directly.


which Version of VC++ do you use?

I'm using Visual studio express 2010.


why do you want to debug into the sqlite.c file?

Since opening database API is failed, I'm trying to figure out the cause.


The file sqlite.c is just another source file for your compiler;

optimizations would occcur

only if you set them in your compiler options.

Yeah.. I completely agree with you, but I can't find such setting in VC++
property,
So I'm asking this query...

Thanks,


2014-12-08 18:14 GMT+09:00 Clemens Ladisch :


Shinichiro Yoshioka wrote:

I'm about to use sqlite-amalgamation(sqlite3.c) on Visual C++.
But although the compiling was successfully finished, even if I set break
point
on the source code, I can't trace the working line in sqlite3.c

correctly.

How exactly are you using SQLite in your program?  If you are not using
the SQLite C API (sqlite3_* functions) directly, it is likely that there
is a second copy of the SQLite library inside that other database driver.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 occcur only if you set them in your compiler options.


HTH Martin
Am 08.12.2014 10:10, schrieb Shinichiro Yoshioka:

Hi,

I'm about to use sqlite-amalgamation(sqlite3.c) on Visual C++.
But although the compiling was successfully finished, even if I set break
point
on the source code, I can't trace the working line in sqlite3.c correctly.

I suspected that the sqlite3.c was optimized, but there is no opiton like
that.

Always the active line doesn't match to source code in debug mode.

Is the code optimized automatically? if so, how can I deactivate it?

If anyone knows above phenomenon, then could you please answer me??

Thanks,
___
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] Search for text in all tables

[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 <
sandersonforens...@gmail.com> wrote:


If you are a coder then it is a relatively straight forward process
along the lines of

Loop through each table
Loop through each column


This is the part I am having trouble with. I can loop through tables using
sqlite3_master, but how do I loop through columns? Parse the schema?




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Search for text in all tables

[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 somehow search for/replace a string in all columns of all
tables?

Thanks



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Comparing to NULL in prepared statment

[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
Martin

Am 05.11.2014 09:46, schrieb Baruch Burstein:

Hi all,

This is not really a sqlite specific question, but I was wondering if there
might be a sqlite specific answer.
I prepare the following statement:

"SELECT col1 FROM table1 WHERE col2=:val"

col2 is a textual string, and may sometimes be NULL. If I bind a string to
:val it works fine. If I bind a null-pointer the comparison fails since it
should be using ISNULL. Is there a way to do this correctly with a single
statement? I know I can have 2 statments and test the string pointer at
runtime to determine which one to run, but that seems awkward.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Martin Engelschalk]

Hi Ross,

i don't know if there is a simpler way. Perhaps someone on the list has 
a better idea. What I wanted to say is that you probanly cannot prevent 
your downloaders from removing anything you add to make the data read-only.


Martin.


Am 14.10.2014 13:48, schrieb Ross Altman:

Hi Martin,

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, '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 indeed any other mechanism to prevent changes to the
data. It is their file after download

HTH
Martin

Am 14.10.2014 08:19, schrieb Ross Altman:


I need to host a fixed, unchanging database online, and I want to make
sure
that anyone who downloads it cannot add to it. Is there any way to set the
permissions to be read-only within sqlite?

Thanks,
Ross
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


--

*Codeswift GmbH *
Kräutlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

___
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


--

*Codeswift GmbH *
Kräutlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 indeed any other mechanism to prevent changes 
to the data. It is their file after download


HTH
Martin

Am 14.10.2014 08:19, schrieb Ross Altman:

I need to host a fixed, unchanging database online, and I want to make sure
that anyone who downloads it cannot add to it. Is there any way to set the
permissions to be read-only within sqlite?

Thanks,
Ross
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


--

*Codeswift GmbH *
Kräutlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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

Am 08.10.2014 10:29, schrieb AnandArun:

ALTER TABLE "main"."tblCredit" ADD COLUMN "CardDetail" VARCHAR;ALTER TABLE
"main"."tblCredit" ADD COLUMN "CardDetail1" VARCHAR;



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/how-to-add-multiple-columns-at-a-time-tp43355p78464.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


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 clause contains a list of something. I.e.

IN ('2014-01-01', '2014-01-02', '2014-01-03', '2014-01-04', '2014-01-05')

So the question is, is there a shorter way for one to say something like,

IN ('2014-01-01', ..., '2014-01-05')

where the content of the IN would have the first item and the last 
item of the list, but that's it?  Thanks.


josé
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


--

*Codeswift GmbH *
Kräutlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] String compare scoring functions

[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 comparison, you will have to implement your own function in 
sqlite using sqlite3_create_function, see 
http://www.sqlite.org/capi3ref.html#sqlite3_create_function
You cann take the suggestions on stackoverflow and convert them to C or 
at least wrap them in a C function.


Martin

Am 29.08.2014 00:07, schrieb Maurizio Trevisani:

Hello,
I need a function that gives a real result from 0 to 100 representing
the similarity of two input strings.

I need to compare a dataset of addresses with a dataset of addresses
with house numbers, having coordinates, to get a geolocalization of
the addresses.

Since the addresses aren't identical to the addresses of the street
map dataset, I wish to get a score to measure the "similarity" of the
two strings.

I've found 
http://stackoverflow.com/questions/653157/a-better-similarity-ranking-algorithm-for-variable-length-strings
and I hope there is some implementation of a similar function to be
used in Sqlite queries.

Any suggestion, any help?

Thanks,
Maurizio
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


--

*Codeswift GmbH *
Kräutlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 documentation is


"Each expression in the result-set is then evaluated once for each group 
of rows. If the expression is an aggregate expression, it is evaluated 
across all rows in the group. Otherwise, it is evaluated against a 
single arbitrarily chosen row from within the group. If there is more 
than one non-aggregate expression in the result-set, then all such 
expressions are evaluated for the same row. " from here 
http://www.sqlite.org/lang_select.html#resultset


The keyword here is "arbitrarily chosen".

So, you can not expect to get the same id every time even if you do not 
change the sqlite version.


Hope this helps
Martin

Am 22.08.2014 15:19, schrieb Christoph Wiedemann:

SELECT Id, Name, MIN(Score) AS Score FROM Test GROUP BY Name ORDER BY Score


--

*Codeswift GmbH *
Kräutlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about coalesce and data types

[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 for you to converting your values to text?
Yes, but this would mean to change a LOT of SQL and then testing it, 
Also, the SQL is generic and has to work on Postgres and Oracle too.

I will change the binding.



Is there a way to write my own Function so that its result has
an affinity?

No.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Thank you very much, i see my way now.

Martin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question about coalesce and data types

[Martin Engelschalk]

Hello Clemens,

thank you for your answer; i understand now why the where - condition 
returns 'false'. Also, the effect is independent of the function used.


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.
Is there a way to write my own coalesce-Function (or indeed any 
function) so that its result has an affinity? The documentation of the 
sqlite3_result_* family of functions suggests not.


Thank you
Martin


Am 20.08.2014 12:03, 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 someone please explain this to me or point me to some documentation?

The col_a column has numeric affinity, so the string '1' is converted
into a number.  <http://www.sqlite.org/datatype3.html#affinity>

The return value of the function has NONE affinity, so no automatic
conversion happens.  <http://www.sqlite.org/datatype3.html#expraff>


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


--

*Codeswift GmbH *
Kräutlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Question about coalesce and data types

[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 * from TestTable where col_a = 1;
select * from TestTable where col_a = '1';

Now when introducing coalesce, the following statements also retrieve 
the row:


select * from TestTable where coalesce(col_a, 5) = 1
select * from TestTable where coalesce(col_a, '5') = 1

Bur the two next statements do not retrieve the row:

select * from TestTable where coalesce(col_a, 5) = '1'
select * from TestTable where coalesce(col_a, '5') = '1'

When using coalesce, it seems to matter what is right of the = sign in 
the where clause. When comparing directly with the column, this dows not 
matter.
The same effect can be observed if i replace the constant '1' to the 
right of the = with a bind variable that I bind with sqlite_bind_text.


Can someone please explain this to me or point me to some documentation?

Thank you
Martin

--

*Codeswift GmbH *
Kräutlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 expected records if i replace "coalesce(some_col, 1)" with 
"some_col", and also if i replace the bind variable with a constant.
It does not seem to matter how the column was defined in the "create 
table" statement, and changing "coalesce(some_col, 1)" to 
"coalesce(some_col, '1')" did not help.


My version is 3.8.5, the database was created with this version.
I also tried with version 3.7.4, the result was the same.
I am quite sure that i use sqlite3_bind_text correctly, because i use my 
own C++ layer which is tested very well and has been working for several 
years.


Have I missed something?

Thank you
Martin



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Prepare statement in separate function

[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", &pDB);

and prepare a statement using

nRet = sqlite3_prepare_v2(pDB, "insert .", -1, &pStmnt, &szTail);

no need to malloc (or free) anything, and passing pDB or pStmnt to 
functions is easy.


Martin


Am 11.10.2011 11:27, schrieb enjoythe...@hushmail.com:

hello list,

I have a question regarding prepared statements. I'm fairly new to
sqlite3 and it's already pretty late, so if I'm overlooking
something obvious please forgive me :) I'm on Windows 7 x64 and
using sqlite-3070800 (amalgamation).

I'm trying to prepare an insert statement, that I will reuse many
times using bound parameters. In a short test application, if I
prepare the statement within the main function everything is fine:

CODE
...
int rc=0;
pDb = (sqlite3*) malloc (sizeof(sqlite3*));
stmt = (sqlite3_stmt*) malloc (sizeof(sqlite3_stmt*));
...

if((rc=sqlite3_prepare_v2(pDb, INSERT_STMT , -1,&stmt, NULL)) !=
SQLITE_OK){
/* ... error ... */
}

sqlite3_step(stmt);
...
\CODE

However, if I try throw this code in a separate function like this,

CODE
int prepareStatement(sqlite3_stmt* stmt, sqlite3* pDb){

int rc=0;

if((rc=sqlite3_prepare_v2(pDb, INSERT_STMT , -1,&stmt, NULL)) !=
SQLITE_OK){
/* ... error ... */
}
return 0;
}
\CODE

it fails as soon as I call sqlite3_step(stmt) afterwards. The
debugger stops at:

sqlite3_mutex_enter(db->mutex);

At first I thought I was doing smthg wrong during memory
allocation, but everything seems to be fine there. I'm sure I'm
overlooking an obvious mistake. Maybe somebody can give me a hint!

thanks,
John

___
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] Update one table from matches in another

[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 inventory table so I know the query is returning correct
> data.
>
> I was just using this to make sure my matches were going to be correct.
>
> select * from locations t1 where exists (
>select * from hive t2
>where t1.Location=t2.Location
>)
>
>
> Now, I need to update the ItemCount column in the locations table with
> '1'when there is a corresponding match in the inventory table but using the
> query below marks all locations in the locations table so I am out of my
> depth here.
>
>
> update locations
>set ItemCount='1'
>where exists(
>select Location from hive t2 where Location=t2.Location
>)
Try this:

update locations
   set ItemCount='1'
   where exists(
   select Location from hive where locations.Location=hive.Location
   )


>
> Anyone know how to adjust this?
>
> I also want to do the reverse, mark any ItemCount in the locations table
> with '0' when there is no matching entry in the location column in the
> inventory table
>
> update locations
>set ItemCount='0'
>where exists(
>select Location from hive t2 where Location<>t2.Location
>)
Try this:

update locations
   set ItemCount='0'
   where NOT exists(
   select Location from hive where locations.Location=hive.Location
   )

> Have tried several varieties of these commands with errors, getting mixed
> results.

Martin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite and Java

[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 
> thewww.sqlite.org not supply API for Java. What should I do?
>  Thank you!
>
>  Yin Lijie
> ___
> 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] Better way to get records by IDs

[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 then 5
   end;

Martin

Am 20.05.2011 15:55, schrieb jose isaias cabrera:
> "Oliver Peters" on Friday, May 20, 2011 9:47 AM wrote...
>
>
>> jose isaias cabrera  writes:
>>
>>>
>>> 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,...
>>> ...
>>> ...
>>> 99,...,azz,...
>>>
>> [...]
>>
>>
>> What about
>>
>> SELECT *
>> FROM table
>> WHERE id BETWEEN 1 AND 99;
>>
>> greetings
>> Oliver
> I presented a bad example, Oliver.  My apologies.  I want specific IDs, so
> the WHERE rec IN (1,2,27,33,87) works perfectly.  However, I have one last
> question: if I do this call,
>
> SELECT * FROM Jobs WHERE rec IN (87, 33, 27,2, 1)
>
> the result is 1, 2, 27, 33, 87.  How can I get that specific order?
>
> thanks,
>
> josé
>
> ___
> 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] Better way to get records by IDs

[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,...
> ...
> ...
> 99,...,azz,...
>
> I know I can do this call,
>
> begin;
> select * from Jobs where rec=1;
> select * from Jobs where rec=2;
> end;
>
> and that would get me the records, but is there an easier way?  I tried,
>
> select * from Jobs where rec=1, rec=2;
>
> and I only get the first one in the list.
>
> thanks for the help.
>
> josé
>
> ___
> 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] Three questions

[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 colum

00:01 Transaction A: BEGIN
00:02 Transaction B: BEGIN
00:03 Transaction A: INSERT INTO test VALUES (1, 'foo') // works okay
00:04 Transaction B: INSERT INTO test VALUES (1, 'bar') // lets say, this also 
works like you expected.
00:05 Transaction B: COMMIT // shall this work? If yes, the Record ('1', 'bar') 
is now committed. However, Transaction A was first!
00:06 Transaction A: COMMIT // This cannot work. What error message would you 
expect?

Now, consider large transactions with many Operations.
Therefore, the second insert fails on every database system i ever encountered.

Martin


Am 11.05.2011 17:24, schrieb Dagdamor:
> and two transactions (from two different connections) are trying to insert a 
> record at once:
>
> 00:01 Transaction A: BEGIN
> 00:02 Transaction B: BEGIN
> 00:03 Transaction A: INSERT INTO test VALUES (1) // works okay
> 00:04 Transaction B: INSERT INTO test VALUES (1) // aborts with 'duplicate 
> key' error! why???
> 00:05 Transaction A: ROLLBACK // works okay, table remains empty
> 00:06 Transaction B: ??? // has nothing to do because was unable to insert a 
> record into an empty table!
>
> To put it simple, transaction A tried to insert a record but soon aborted 
> itself via ROLLBACK. If I understand transactions principle correctly, a 
> rolled-back transaction should act like it never happened in the first place, 
> and other threads should not see its traces. But for some reason another 
> transaction noticed that and refused to insert values into table. The 
> question is: is that a correct behavior, and I should keep this in mind, or 
> SQLite would handle this scenario different way? :/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Confused

[Martin Engelschalk]

Hello Arjabh,

This is a good approach.

I am not sure what you mean by "affecting a query". Every index
- makes your database larger
- slows down inserts (a little bit)
- speeds up select/update/delete (often dramatically)

Concerning Columns B an C:
If your deletes are of the form "delete from MyTable where B = 15" and 
"delete from MyTable where C = 15", you should create two separate 
indexes on B and C.
If your deletes are of the form  "delete from MyTable where B = 15 AND C 
= 15", you should create one multi-column-index on both columns B and C.

Martin

Am 06.05.2011 11:19, schrieb arjabh say:
> Thank you Martin.
> That was very helpful.
>
> According to project requirement, mostly I have to do select query on Column
> A, and delete query based on columns B and C.
> I created index for the columns B and C as well, and 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...@codeswift.com>  wrote:
>
>> 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 same as the first columns of an
>> index.
>> As you have an index (the primary key) on columnns A and B, a where
>> clause like "where a = 15" will use this index, A where clause like
>> "where a = 15 and b = 17" will also find the rows using this index. Such
>> a query will be very fast.
>>
>> If you fire a query using other columns or combinations of columns (like
>> "where c = 1"), SQLite has to scan the full table for rows matching
>> this. This will take a long time, depending on the size of the table.
>>
>> You might want to read this for further information:
>> http://www.sqlite.org/queryplanner.html
>>
>> Martin
>>
>> Am 06.05.2011 06:17, schrieb arjabh say:
>>   >  Hi All,
>>> I have a sqlite database with single table with columns A, B and C.
>>> I have created composite primary key on A and B, and an autoindex was
>>> created (dont know on which columns this index is created).
>>>
>>> When I fire select/delete query with WHERE clause on column A, it is
>>> completed in milliseconds, whereas when I fire the same kind of query on
>>> basis of columns B or C, it takes couple of minutes.
>>> What is causing this difference?
>>>
>>> A point to note: The table contains millions of rows.
>>>
>>> Thanks and Regards,
>>> Arjabh
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Confused

[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 same as the first columns of an 
index.
As you have an index (the primary key) on columnns A and B, a where 
clause like "where a = 15" will use this index, A where clause like 
"where a = 15 and b = 17" will also find the rows using this index. Such 
a query will be very fast.

If you fire a query using other columns or combinations of columns (like 
"where c = 1"), SQLite has to scan the full table for rows matching 
this. This will take a long time, depending on the size of the table.

You might want to read this for further information: 
http://www.sqlite.org/queryplanner.html

Martin

Am 06.05.2011 06:17, schrieb arjabh say:
> Hi All,
>
> I have a sqlite database with single table with columns A, B and C.
> I have created composite primary key on A and B, and an autoindex was
> created (dont know on which columns this index is created).
>
> When I fire select/delete query with WHERE clause on column A, it is
> completed in milliseconds, whereas when I fire the same kind of query on
> basis of columns B or C, it takes couple of minutes.
> What is causing this difference?
>
> A point to note: The table contains millions of rows.
>
> Thanks and Regards,
> Arjabh
> ___
> 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] Cant get PRAGMA temp_store = MEMORY to work

[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 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 pragmas or
> #defines), my executable consumes 345 MBs of memory, does not write any
> temporary files, and finishes in about 100 seconds.
>
> With version 3.7.5, downloaded today, i have to do some things to get
> sqlite to work like before. I want to avoid temporary files, which are
> created by default for the temporary index.
> Even if i set PRAGMA temp_store = MEMORY after opening the database, a
> temporary file gets written, and the memory usage is 72  MB.
>
> I read the docs and compiled the amalgamation with various values of
> SQLITE_DEFAULT_TEMP_CACHE_SIZE, and after opening the file set PRAGMA
> cache_size to different values.
> However, even if memory usage rises up to 318 MB, a temorary file is
> written which is larger than the database itself.
>
> This is on Windows XP, with 1 GB of freee memory.
>
> What am i missing?
>
> Thank you,
> Martin
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- 

*Codeswift GmbH *
Kräutlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query help

[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 MKProperties.obj_id = MKObjects.id
   WHERE MKObjects.type='PANEL' AND MKObjects.platform='IPHONE'


I am not sure if i understood your question correctly - perhaps you want to add 
some examples.

Martin


Am 02.02.2011 10:04, schrieb Marco Bambini:
> Hello, I have two tables defined as:
>
> CREATE TABLE MKObjects (id INTEGER PRIMARY KEY AUTOINCREMENT, platform TEXT, 
> type TEXT, parent_id INTEGER DEFAULT 0);
> CREATE TABLE MKProperties (id INTEGER PRIMARY KEY AUTOINCREMENT, obj_id 
> INTEGER, prop_key TEXT, prop_value TEXT, UNIQUE(obj_id, prop_key));
>
> I need to create a query that returns 2 columns key, value (column names are 
> not important) where the first row is the label 'ID' with value id from 
> MKObjects and the other rows are the columns prop_key, prop_value from 
> MKProperties where obj_id= MKObjects.id satisfying a WHERE condition.
>
> So far I am using a query like:
> SELECT 'ID', id FROM MKObjects WHERE type='PANEL' AND platform='IPHONE' UNION 
> SELECT prop_key, prop_value FROM MKProperties WHERE obj_id=(SELECT id FROM 
> MKObjects WHERE type='PANEL' AND platform='IPHONE' ORDER BY id);
>
> but I am wondering if there is a better way (without using 3 select 
> statements).
> Thanks a lot.
> --
> Marco Bambini
> http://www.sqlabs.com
>
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Cant get PRAGMA temp_store = MEMORY to work

[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 pragmas or 
#defines), my executable consumes 345 MBs of memory, does not write any 
temporary files, and finishes in about 100 seconds.

With version 3.7.5, downloaded today, i have to do some things to get 
sqlite to work like before. I want to avoid temporary files, which are 
created by default for the temporary index.
Even if i set PRAGMA temp_store = MEMORY after opening the database, a 
temporary file gets written, and the memory usage is 72  MB.

I read the docs and compiled the amalgamation with various values of 
SQLITE_DEFAULT_TEMP_CACHE_SIZE, and after opening the file set PRAGMA 
cache_size to different values.
However, even if memory usage rises up to 318 MB, a temorary file is 
written which is larger than the database itself.

This is on Windows XP, with 1 GB of freee memory.

What am i missing?

Thank you,
Martin
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 64 bit sqlite 3

[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.
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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
> 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] UTF-8

[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 check this?

I just checked the firefox (0.6.5) plugin with my databases, it works 
correctly for me.

Pictures / Attachments do not make it to the list.

Martin


Am 16.12.2010 14:17, schrieb Ming Lu:
> Hello everyone,
>
> i am faceing a problem with unicode to save german umlaute in sqlite:
>
> here ist the problem:
>
> dev-envoriment:
> Visual studio 2008
> SQLite 3.6.20
>
> i used the sqlite c/c++ interface and open a db used sqlite3_open.
> during the running of my application will the german-umlaut or other
> symbol transported into the database. i use the SQLite Manager(firefox
> plugin) to open the db and the umlaut cannot be correctly saved into the
> database(see the pic) :
>
> ->  it should be "ä".
>
> my question is, how can use the sqlite c/c++ interface to save the
> unicode correctly into a sqlite database?
>
> thanks a lot
> best regards
>
> Ming
> ___
> 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] Slowdown when switching from Version 3.2.5 to 3.7.4

[Martin Engelschalk]

Thank you for your response. I will try and find out things here first, 
now that I know that the new version should be faster, not slower.
If i cannot determine the reason, then i will post my exact schema and 
statements.

Martin

Am 16.12.2010 13:19, schrieb Richard Hipp:
> On Thu, Dec 16, 2010 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 single
>> table.
>> Without changing anything in my own code  (which reads from a file and
>> does additional processing before inserting the records), my program
>> runs about 50% slower (89 secs instead of 57 secs)
>>
>> Is this expected? Can i do anything to reach the old perfomance?
>>
> 3.7.4 should be faster than 3.2.5, not slower.  But without further
> knowledge of what your are doing (your schema and the queries you are
> running, at a minimum) we cannot help you figure out what is going on.
>
>> I am on C++ and Windows and compile the amalgamation. I use one large
>> transaction and bind variables.
>>
>> Thanks,
>> Martin
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>

-- 

*Codeswift GmbH *
Kräutlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Slowdown when switching from Version 3.2.5 to 3.7.4

[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 records), my program 
runs about 50% slower (89 secs instead of 57 secs)

Is this expected? Can i do anything to reach the old perfomance?

I am on C++ and Windows and compile the amalgamation. I use one large 
transaction and bind variables.

Thanks,
Martin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite3 question

[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 the error?
does the error occur always, or only with a certain statement?
Can you access the file with the sqlite command line utility?
How was the file created? Do you have a file which is ok?

Martin

Am 30.10.2010 09:21, schrieb lizhe:
> 您好:
> 我们使用的SQLITE3 
> 数据库版本为3.6.23.1版本,在红帽编译器下运行,运行的硬件系统为LINUX(2.6版本),现在出现了一个问题,长时间运行后会出现查询数据库返回"The
>  database disk image is malformed",
> 请问这是什么原因造成的.如何解决,使用最新版本3.7.2是否会避免出现这个问题?
>
>
> Dear Sir:
>  
> I am writing to enquire about a bug we found. Now the SQLITE3 database we 
> use is version 3.6.23.1, which running in the red hat compiler and the 
> hardware system is Linux(version 2.6).We have a problem that for select 
> database(SQL),return "The database disk image is malformed" ,How to solve my 
> trouble? Is use version 3.7.2 ? Do you have any idea about this? I would like 
> get support for SQLite ?
>  Wish you happiness !
> 
> 
> Yours sincerely,
>   
> Lizhe
>   
> ___
> 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] COUNT very slow

[Martin Engelschalk]

Am 24.09.2010 10:38, schrieb Michele Pradella:
>ok, thank you for the advices, I'll try to use a TRIGGER.
> The DB already has an index.
> Anyway if I have to count something like this:
> select COUNT(*) from logs WHERE DateTime<=yesterday
> I can't do it with a TRIGGER
No, 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:
>> 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
>> http://www.mail-archive.com/sqlite-users@sqlite.org/msg10279.html
>>
>> If you need the result quickly, you have to maintain the rnumber of
>> records yourself in a different table, perhaps using triggers.
>>
>> Martin
>>
>>
>> Am 24.09.2010 10:13, schrieb Michele Pradella:
>>>  I have an SQLite DB of about 9GB with about 2.500.000 records.
>>> I can't understand why the "select COUNT(*) from log" statement is
>>> extremely slow, it takes me about 9-10 minutes!
>>> I try with:
>>> select COUNT(1) from logs
>>> select COUNT(DateTime) from logs
>>> same result. Have you idea of why it's so slow?
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] COUNT very slow

[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 
http://www.mail-archive.com/sqlite-users@sqlite.org/msg10279.html

If you need the result quickly, you have to maintain the rnumber of 
records yourself in a different table, perhaps using triggers.

Martin


Am 24.09.2010 10:13, schrieb Michele Pradella:
>I have an SQLite DB of about 9GB with about 2.500.000 records.
> I can't understand why the "select COUNT(*) from log" statement is
> extremely slow, it takes me about 9-10 minutes!
> I try with:
> select COUNT(1) from logs
> select COUNT(DateTime) from logs
> same result. Have you idea of why it's so slow?
> ___
> 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 System Time

[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 current time ? For sure not the OS's time, 
> I've
> tested... I read somewhere that it somehow calculates it from my current 
> region.
> Until now I was ok with that, but now I see that's not the case... It's 1 AM 
> on
> September 19, but SQLite's SELECT returns records from September 18, so what's
> the deal ? And if you're asking, I'm from Romania (+2) and there are no time
> border issues...
>
> Thanks in advance
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

-- 

* Codeswift GmbH *
Kräutlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 file. After that, also several times, the
>> data is selected in a way that uses 80-90% of the data in the database,
>> using joins of all tables and sorting.
>>
>> ...
>>
>> With the new feature available, i can remove my own workaround, which
>> does not work so well annyway. Many thanks to the developers.
>>
>>
> Martin, you gave a good example of the case when this really helps. Although
> I suppose you still need some tweaking. As Dan Kennedy wrote you have to set
> the the parameter and "From that point on, connection "db" extends and
> truncates  the db file in 1MB chunks". So for example if you just created a
> db and maybe did minor changes to the db and have plans to extend it to
> larger size, you have to set SQLITE_FCNTL_CHUNK_SIZE with
> sqlite3_file_control and also write something new and not only write but be
> sure it's not going to be written to a previously disposed page.
>
> As long as cases like yours is real and can be used in real life, maybe a
> change to existing freelist_count pragma is possible? If it is writable
> (PRAGMA freelist_count=1024;), sqlite compares the value supplied with the
> current count and if it is bigger allocates necessary space. It seems this
> syntax will be straightforward and self-explaing. What you think?
>
> Max

Hello Max,

Personally, I use the C Api and do not need any other interface to the 
functionality.

If you make PRAGMA freelist_count writable as you suggest, I would 
expect the database to reserve space once, and not use a larger pice of 
the disk every time ist has to be expanded in the future.

Martin

> ___
> 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] Can we get a pragma for SQLITE_FCNTL_CHUNKS_SIZE?

[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 
> improvement in performance ?
Yes, see below.
> It might be worth noting that fragmentation is normally seen as an issue only 
> under Windows which is very sensitive to it however.  Other operating systems 
> use different ways of handling disk access, however, real figures from 
> real-world examples may disprove this classic view.  Also, many installations 
> of SQLite are on solid state devices where, of course, fragmentation has no 
> effect at all.
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Hello Simon,

I agree completely.

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 file. After that, also several times, the 
data is selected in a way that uses 80-90% of the data in the database, 
using joins of all tables and sorting.

The fragmentation was not a problem for me, but one of my customers did 
not like it. As far as i understood, some automatic tool monitored disk 
fragmentation and generated alarms; also, a backup tool slowed down.
So, while inserting, at strategic places, I created a dummy table with a 
blob field and filled it with a very large empty blob. Then I dropped 
the table. In this way i simulated the new feature.

Under Windows, the insert speed did not change measurably, but the speed 
of the later selects increased by about 15-20%. Also, my customer was 
happy.

With the new feature available, i can remove my own workaround, which 
does not work so well annyway. Many thanks to the developers.

Martin


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How can i install SQLite

[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
>
> ___
> 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] Coping with database growth/fragmentation

[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 number of bytes.

In my case, the database grows continously and the file is often 
extremely fragmented when the growth phase is finished (this concerns 
the file on the disk, not internal fragmentation)

Currently, i monitor the size of the database using pragma 
freelist_count. When I see the value of free pages approach zero, i 
create a dummy table with a blob field and fill it with a very large 
empty blob. Then i drop the table. The empty pages remain behind and 
page_count does not rise any more for a time.
This has been proposed to me on this list a while ago.

However, testing the database in this way and creating and dropping the 
table carries a performance penalty, and finding the strategic places in 
my application to do this has been difficult.

Martin


Am 23.07.2010 03:11, schrieb Taras Glek:


>   Seems like the
> easiest fix here is to add a pragma fs_allocation_size. It would
> preallocate a continuous chunk of diskspace. Sqlite would behave exactly
> as it does now, except it would avoid truncating the file beyond a
> multiple of the fs_allocation_size.
> For example, pragma fs_allocation_size=50M would grow the db file to
> 50megabytes. Once the db grows to beyond 50mb the underlying file would
> get resized to 100mb.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] concat 2 const chars ?

[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 2 const chars.
>
> const char *language;
> language = "'6'";
> const char *sql2 = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id
> = ";  
>
> const char *sql = strcpy(sql2,language);
> // or
> const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id
> = " + language;
> //or
> const char *sql = "SELECT key,name,text FROM uebersetzungen WHERE sprach_id
> = "&  language;
>
>
> nothing works :)
>
> How can i do this ?
>
> Hoping for help :)
> tobi
>
>


Fax: +49 (0) 3212 / 1001404
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 
> Subject:  A DLL for my WinXP
> Date: Sun, 20 Jun 2010 20:59:20 -0400
> From: Arbol One
> To:   sqlite-users@sqlite.org
>
>
>
> Where can I find the DLL file for my winxp?
> TIA
>

-- 

* Codeswift GmbH *
Kräutlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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
> Date: Sun, 20 Jun 2010 20:59:20 -0400
> From: Arbol One
> To:   sqlite-users@sqlite.org
>
>
>
> Where can I find the DLL file for my winxp?
> TIA
>

-- 

* Codeswift GmbH *
Kräutlerweg 20a
A-5020 Salzburg
Tel: +49 (0) 8662 / 494330
Mob: +49 (0) 171 / 4487687
Fax: +49 (0) 3212 / 1001404
engelsch...@codeswift.com
www.codeswift.com / www.swiftcash.at

Codeswift Professional IT Services GmbH
Firmenbuch-Nr. FN 202820s
UID-Nr. ATU 50576309

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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
>
>
> --
> From:
> Sent: Thursday, June 10, 2010 1:59 PM
> To:
> Subject: Re: [sqlite] how to install sqlite3 in windows+python2.5
>
>
>> Download the zip archive containing "sqlite3.dll" for latest sqlite
>> version. Then replace the existing library inside
>> python 2.5 install. directory with the one downloaded.
>>
>>
>> Message d'origine
>> De: zealx...@hotmail.com
>> Date:
>> 10.06.2010 06:04
>> À: "General Discussion of SQLite Database"
>> Objet: Re: [sqlite] how to install
>> sqlite3 in windows+python2.5
>>
>> google told me, this is the real version, but it still old, i need 3.6.23
>> please help me.
>>
>>
>>  
> import sqlite3
> sqlite3.sqlite_version
>
>> '3.3.4'
>>
>> --
>> From: "zeal"
>> 
>> Sent: Thursday, June 10, 2010 11:54 AM
>> To: "General Discussion of SQLite Database"> us...@sqlite.org>
>> Subject: [sqlite] how to install sqlite3 in windows+python2.5
>>
>>  
>>> hi,
>>>
>>> i installed python2.5, it
>>>
>> seems wrapped sqlite3, but the version is
>>  
>>> 2.3.2.
>>> sqlite3 2.3.2 could not use function group_concat, so i want
>>>
>> to use
>>  
>>> latest version of sqlite3.
>>>  i know how to install sqlite3 in linux/unix, but i have no idea
>>> install
>>>
>> sqlite3 on windows.
>>  
>>>  i have download the binary of sqlite3.exe, but it could not be used
>>> in
>>> python scripts.
>>>
>>>
>>>
>> please, help me.
>>  
>>> Thanks Daisy
>>> ___
>>> sqlite-users mailing list
>>> sqlite-
>>>
>> us...@sqlite.org
>>  
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>
>>>
>> ___
>>
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>  
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with DATABASE_LOCKED

[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
>   wrote:
>
>> 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
>> placing the database file on a network file system.
>>  
> Do you have write access to the directory where the database file is located? 
> SQLite needs to create a journal file there.
>
> Igor Tandetnik
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem with DATABASE_LOCKED

[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 
placing the database file on a network file system. However, other such 
scenarios work, becausw the the app is single threaded with one 
connection, no other processes access the database file.

The sqlite version is 3.2.5 (upgrading is not an option, unfortunately), 
I compiled using the amalgamation without special #Defines.

The Machine is SUSE Linux Enterprise Server 10, Kernel: 
2.6.16.60-0.54.5, gcc version 4.1.2
The NFS Software used is limal-nfs-server (LiMaL NFS Server Library) 
Version 1.1.72, the customer also mentions a "Celerra NS-40 F" by "EMC", 
which seems to be an external storage system, and NFS version 4 protocol.

Has anyone any ideas how to overcome this? Thank you for any comments.

Martin

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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)
>...> , ktext varchar(40)
>...> , arbpl varchar(10)
>...> , werks varchar(4)
>...> , verwe varchar(1)
>...> , vagrp varchar(3)
>...> , statu varchar(1)
>...> , sttag varchar(8)
>...> , strat varchar(6)
>...> , batchno varchar(3)
>...> );
> sqlite> .import C:\HEAD.txt head;
> Error: no such table: head;
> sqlite> .schema
> CREATE TABLE head
> ( id varchar(10)
> , tplnr varchar(20)
> , plnal varchar(2)
> , ktext varchar(40)
> , arbpl varchar(10)
> , werks varchar(4)
> , verwe varchar(1)
> , vagrp varchar(3)
> , statu varchar(1)
> , sttag varchar(8)
> , strat varchar(6)
> , batchno varchar(3)
> );
>
> Any idea why I'm getting "no such table"?
>
> Phil Hibbs.
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Martin Engelschalk]

Hi Alexis,

you will be ok. However, make sure to handle the SQLITE_BUSY returncode 
in your apps correctly, and keep the write-transactions short and commit 
or rollbackt them all. Based on your data, a SQLITE_BUSY will be very 
unlikely, but you have to take it into account.

See also http://www.sqlite.org/c3ref/busy_timeout.html

To answer your question: Yes, sqlite will insure sequestial write by 
using its locking nechanism. If there are many write operations however, 
the database will be locked often and other applications will be forced 
to wait for longer and longer times, which can become unaccepable to the 
people using you web application.

Martin

alexis_ wrote:
> Hi Martin
>
> The Java Application will do all the write. PHP will only read. (Just out of
> curiosity what would the implication be if both PHP and Java did write.
> Wouldn’t SQLite insure sequential 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,
>>
>> 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 there,
>>>
>>> I am at the research stage of a project i have been asked to undertake.
>>>
>>> At the moment the O/S will be windows server 2003 and the web server IIS.
>>> These are set and i cannot change them.
>>>
>>> I will be using PHP to deliver the web content to users and also a Java
>>> application will be used to integrate to third party product.
>>>
>>> Both PHP and Java will be accessing a SQLite db concurrently. SQLite,
>>> Java
>>> App and php will all be on one box. So my questing is should this setup
>>> work
>>> OK? Is there anything i should be aware off.
>>>
>>> Thank you for any help, pointers
>>>
>>> Alexis
>>>
>>>
>>>   
>>>   
>> ___
>> 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 - IIS, PHP and java

[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 there,
>
> I am at the research stage of a project i have been asked to undertake.
>
> At the moment the O/S will be windows server 2003 and the web server IIS.
> These are set and i cannot change them.
>
> I will be using PHP to deliver the web content to users and also a Java
> application will be used to integrate to third party product.
>
> Both PHP and Java will be accessing a SQLite db concurrently. SQLite, Java
> App and php will all be on one box. So my questing is should this setup work
> OK? Is there anything i should be aware off.
>
> Thank you for any help, pointers
>
> Alexis
>
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Newbie problem using special column name

[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 ist.

Martin

Patrick Ben Koetter wrote:
> Can I add a column name containing a dash "-" and if yes, how would I do that?
>
> I am asking because I fail to add a column name that contains a dash "-" and I
> don't know if I cause the problem (easy solution) or if its something else
> causing this to fail.
>
> Here's what I try:
>
>   sqlite> create table test(column-1 varchar(255));
>   SQL error: near "-": syntax error
>
> So far I have had a look at the SQLite documentation, but couldn't find
> anything that would tell me about 'reserved' characters or how I would escape
> a dash.
>
> Thanks,
>
> p...@rick
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3 Optimization Question

[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 wrote:
> I am doing mobile development, and the size of my application is
> getting too large for comfort.  I'd like to find a way to reduce the
> file size, but maintain the performance.
>
> I have a database that is read only.  It is ordered and each row is
> unique.  It has only one table, which is significantly large.
>
> CREATE TABLE table (column TEXT PRIMARY KEY);
>
> The only query that ever runs against it is as follows:
>
> SELECT count(*) FROM table WHERE column = '%q'
>
> It works fine, it's easy, and it's fast.
>
> The only problem is that the table is very large.  If I list out the
> contents of the table in a text file, it is one-third the size of the
> database.  So, if the text file is 12 MB, the database is 32 MB.
>
> I get the feeling that the database doesn't need to be that large.  It
> seems like that file size is the table size plus two index sizes.  All
> this, when I only really need just an index to tell if the record
> exist.  I never update the record.
>
> Again, what I have now works fine; it's just a large file.
>
> Do you know of a better way to do this in Sqlite3, or a strategy or
> algorithm to do it from text or some data structure?
>
> Cheers!
> Michael
> ___
> 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] size control of sqlite database

[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 the file
>> size after every insert.
>> 
>
> Doesn't PRAGMA freelist_count help with that?  There is also PRAGMA
> page_count and another to determine page size so you can do all these
> calculations without going outside of SQLite.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
> Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
>
> iEYEARECAAYFAktPZtwACgkQmOOfHg372QRHEgCeKwRMG+DpI1Kq/2jeo/Iw/DO0
> DtoAoIYIqt40E9TIWlebbYPta33S6o9r
> =pY6s
> -END PGP SIGNATURE-
> ___
> 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] Escaping strings to be used in queries in C/C++

[Martin Engelschalk]

Hi Kurt,

The Link i copied is named .#sqlite3_bind_blob, but points at 9 
different functions to bind values of diferent types, especially 
sqlite3_bind_text.

Using Google, i found this page: 
http://www.adp-gmbh.ch/blog/2004/october/6.html, where the rationale 
behind binding is explained quite well.

As for an example of binding values, I use a C++ wrapper, so I have no 
readable code where this can be seen. Sorry.

The Link sent by Simon Davies points to an SQL function "quote", so you 
can use SQL Language (and not C) to create an sql statement text.

Martin


Kurt D. Knudsen wrote:
> Hi Martin,
>
> Thanks for the prompt reply. It seems sqlite3_mprintf() is exactly what
> I was looking for. I do have to ask, though, what is the benefit of
> using the blob binding? I have my tables defined either using VARCHARs
> or TEXTs. I've never used the BLOB type before and am unsure of the
> benefits of it.
>
> I've tried looking up examples that show it being used in practice, but
> I seem to get lost in convoluted examples that show advanced cases
> implementing the functions to perform tasks that are way out of the
> scope of my needs, and therefore confuse me.
>
> As for the person that replied with the QUOTE clause. I have no idea
> how, or where, to use it. I'm a visual person and learn by seeing it
> being done. Normally, I can figure things out on my own, but I'm truly
> stumped.
>
> I think that 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:02 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Escaping strings to be used in queries in C/C++
>
> 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:
>   
>> Hi guys,
>>
>> I've been searching high and low for a solution to this, but haven't
>> found anything that I fully understand. Right now, I'm inserting text
>> into a database that contains single quotes and backslashes. Is there
>> 
> a
>   
>> function that will properly escape these characters so they will be
>> inserted properly?
>>
>> For example:
>>
>> sprintf(query, "INSERT INTO db (file) VALUES ('John's
>> 
> Document.txt');");
>   
>> Obviously, I can escape it manually, but when I'm reading files from a
>> directory listing, I'd need a function to escape it properly. I see
>> 
> that
>   
>> there's some SQLite3 QUOTE or ESCAPE clause/function, but I can't find
>> any information on it.
>>
>> Thanks,
>>
>> Kurt
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>   
>> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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:
> Hi guys,
>
> I've been searching high and low for a solution to this, but haven't
> found anything that I fully understand. Right now, I'm inserting text
> into a database that contains single quotes and backslashes. Is there a
> function that will properly escape these characters so they will be
> inserted properly?
>
> For example:
>
> sprintf(query, "INSERT INTO db (file) VALUES ('John's Document.txt');");
>
> Obviously, I can escape it manually, but when I'm reading files from a
> directory listing, I'd need a function to escape it properly. I see that
> there's some SQLite3 QUOTE or ESCAPE clause/function, but I can't find
> any information on it.
>
> Thanks,
>
> Kurt
> ___
> 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] Fine-grainy error report needed

[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 PRIMARY KEY is an exception and must be an integer. 
Therefore, the error most probably comes from your 'Id' - column.

Martin

A.J.Millan wrote:
> Hi list:
>
> Supposing a dBase without constraints (some like this but not limited to 
> it):
>
> CREATE TABLE tbl (Id INTEGER PRIMARY KEY, Nm INTEGER, Cl INTEGER, Dc REAL, 
> Dm REAL, St INTEGER);
>
> A query to poblate:
>
> "INSERT INTO tbl (id,nm,cl,dc,dm,st) VALUES (?,?,?,?,?,?)"
>
> Then six binds (i = 0 to 5):
>
> sqlite3_bind_xxx (pStmt, i, ...);
>
> and a "step":  resp = sqlite3_step(pStmt);.
>
> Then, if resp ==  SQLITE_MISMATCH (Data type mismatch)
>
> Do is there some way to know the index (i) of the offending bind?
>
> By the way: I'm trying to make a general function to import (duplicate) data 
> from an attached table.
>
> Thanks in advance.
>
> A.J.Millan
>
> ___
> 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] Maximum length of the field name

[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 length of the field name for sqlite?
>
> Thank you.
> ___
> 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] Question about the update of tuples in sqlite-views

[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 view updated if one Tuple updated or changed from the
> DB? 
> (a) only the particular Tuple updated in the different view? 
> (b) or updated the complete views?
>
> Thank you in advance 
>
> T.
>
> ___
> 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] Table aliases

[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 each representing a specific
> language which my application uses to access translated strings.  It'd
> be extremely useful if I were able to alias a table as "CurrentLanguage"
> as opposed to directly referencing the actual name.  This saves me from
> having to tweak my lookup statements on the fly to change the table name
> being accessed.  Is it possible to perform such an alias?  I know that I
> could execute the line "CREATE TABLE CurrentLanguage AS SELECT * from
> SomeLanguage" but will that duplicate all the data or just reference the
> data?  I'd rather not take the memory hit as these tables are quite
> large.  Suggestions?
>
>  
>
> -Shaun
>
>  
>
>  
>
> ___
> 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] [Duplicates] How to keep only one row?

[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 .name and m2.rowid < members .rowid)

This statement deletes all rows where there exists a row with the same 
name and a lowe rowid. Rowid is an internal field of sqlite.

Martin

Gilles Ganault wrote:
> Hello
>
> I have a table that has a lot of duplicates in the Name column. I'd
> like to only keep one row for each.
>
> The following lists the duplicates, but I don't know how to delete the
> duplicates and just keep one:
>
> SELECT name FROM members GROUP BY name HAVING COUNT(*) > 1;
>
> Thank you.
>
> ___
> 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] Updating a database by email

[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 background process or user command triggered.

Martin

Bill Harris wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> I have to coordinate a task list between two sites, and my only
> convenient connection between the two is email.  MS Access 2007 offers
> an email insert and update feature, but it's not quite doing what I
> need.
>
> Is there a way to update / synchronize an sqlite database between two
> sites successfully using email?  I could even see having a copy at both
> sites and periodically emailing the slave version to the master and then
> running a merge of some sort, but I'm not sure that would do what I
> need.
>
> Thoughts?
>
> Thanks,
>
> Bill
> - -- 
> Bill Harris  http://facilitatedsystems.com/weblog/
> Facilitated Systems  Everett, WA 98208 USA
> http://facilitatedsystems.com/  phone: +1 425 374-1845
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.9 (GNU/Linux)
>
> iEYEARECAAYFAkpYEg8ACgkQ3J3HaQTDvd+qLwCfdTUdRz6oJ6ol9sb+s2YOLVZ8
> 8M4AniXdvJWahIc1JOs3XutOHDVgj57n
> =jZLR
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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 database. eg. I have
> test.DB file . I want to know which SQLite3 version its using ..eg 3.5.4
> or 3.6.15? 
>  
> Thanks,
> -K
> ___
> 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] Tips to access SQLite with multiple clients?

[Martin Engelschalk]

Hi Gilles,

first, you write of a "SQL server", but you probably know that sqlite is 
not a server.
You can have multiple applications access a sqlite database file without 
problems if these applications are reading the database and not writing.
If the applications also have to write, then
- read http://www.sqlite.org/faq.html#q5
- keep the transactions short (very short)
- handle he SQLITE_BUSY returncode in your application by waiting a 
little bit an trying again
- avoid accessing the same file via NFS

Martin

Gilles Ganault wrote:
> Hello
>
>   I know that SQLite is not meant to be used by more than one
> simultaneous client, but I don't know of another SQL server that is so
> light, easy to use, and available for Linux and Windows. If you know
> of such a beast, I'm interested.
>
> Otherwise... I'm willing to take the risk of using it to work with
> two-three users with moderate use. MySQL et al. are simply overkill
> for this type of use.
>
> What are the tips you would suggess to minimize the risks of sharing
> an SQLite database between a few clients?
>
> Thank you for any feedback.
>
> ___
> 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] Order by term not in result set

[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 not be used. The order by is executed after the union 
of the two result sets, and the column "name" ist not part of the result 
set.

Use the following:

SELECT id,  url,  selected, name FROM db1.test  UNION 
SELECT id,  url,  selected, name FROM db2.test  
ORDER BY name ASC, id DESC LIMIT 100"


Martin

Susan Shippey wrote:
> Hi,
>
> I get the following error with SQLite 3.6.1
>
> "1st ORDER BY term does not match any column in the result set"
>
> From the following query
> "SELECT id,  url,  selected, FROM db1.test  UNION 
> SELECT id,  url,  selected, FROM db2.test  
> ORDER BY name ASC, id DESC LIMIT 100"
>
> However the equivalent with a single DB i.e. without the UNION seems to work
> fine. 
>
> Is there a general restriction that ORDER BY terms must be in the result
> set? And if so why doesn't it kick in without the UNION?
>
> And is there a performance cost to adding the ORDER term to the result set,
> given that we are ordering by it anyway?
>
> Many thanks,
> Tom
>
>
> ___
> 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] synchronizing sqlite local data base to a remote data base

[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, my application 
logic was such that conflicts (like the one Simon described) could not 
occur.
None of these databases was sqlite, by the way.

Martin

Simon Slavin wrote:
> On 8 Jun 2009, at 7:30pm, Mohey Eldin Hamdy wrote:
>
>   
>> I am trying to synchronize an sqlite local data base with a remote  
>> mssql
>> data base. I wasn't able to find any listed function at
>> http://www.sqlite.org/c3ref/funclist.html to do something like that.  
>> Any
>> ideas please.
>> 
>
>
> The sqlite3 library would not have functions to access MSSQL  
> databases.  But that's the least of your problems.
>
> Synchronising two copies of a database is a whole twisty basket of  
> fish.  Very difficult to program and complicated to keep running.   
> Consider what happens if one user deletes a record from their copy of  
> the database and then the other user updates the record in theirs.   
> Synchronisation would have to be done at the application level anyway,  
> not in the library.
>
> Simon.
> ___
> 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] Why row is not found?

[Martin Engelschalk]

Hi Marco,

How do you insert this data into your database?
I opened your database with an old version of SQLiteSpy, which uses an 
even older version of sqlite. It showed the value as a blob.

Martin
Marco Bambini wrote:
> I understand that the issue could be caused by the wrong datatype...  
> 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, 2009, at 11:17 AM, Martin Engelschalk wrote:
>
>   
>> Your field value is a blob, so you have to use a cast like you did to
>> find the row, or use a blob literal:
>> SELECT * FROM lo_user WHERE lo_name=X'61646d696e';
>>
>>
>>
>> Marco Bambini wrote:
>> 
>>> I just posted the db on my website... there is one row and there
>>> aren't invisible characters.
>>>
>>> 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/
>>>
>>>
>>>
>>>
>>>
>>>
>>> On Jun 5, 2009, at 11:10 AM, Martin Engelschalk wrote:
>>>
>>>
>>>   
>>>> 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 lo_user WHERE lo_name='admin';
>>>>> returns 0 rows in this db?
>>>>>
>>>>>
>>>>>
>>>>> Thanks.
>>>>> -- 
>>>>> Marco Bambini
>>>>> http://www.sqlabs.com
>>>>> http://www.creolabs.com/payshield/
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> 
>>>>>
>>>>> ___
>>>>> sqlite-users mailing list
>>>>> sqlite-users@sqlite.org
>>>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>>>
>>>>>
>>>>>   
>>>> ___
>>>> sqlite-users mailing list
>>>> sqlite-users@sqlite.org
>>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>>
>>>> 
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>>   
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.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] Why row is not found?

[Martin Engelschalk]

Your field value is a blob, so you have to use a cast like you did to 
find the row, or use a blob literal:
SELECT * FROM lo_user WHERE lo_name=X'61646d696e';



Marco Bambini wrote:
> I just posted the db on my website... there is one row and there  
> aren't invisible characters.
>
> 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/
>
>
>
>
>
>
> On Jun 5, 2009, at 11:10 AM, Martin Engelschalk wrote:
>
>   
>> 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 lo_user WHERE lo_name='admin';
>>> returns 0 rows in this db?
>>>
>>>
>>>
>>> Thanks.
>>> -- 
>>> Marco Bambini
>>> http://www.sqlabs.com
>>> http://www.creolabs.com/payshield/
>>>
>>>
>>>
>>>
>>>
>>>
>>> 
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>   
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Why row is not found?

[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 lo_user WHERE lo_name='admin';
> returns 0 rows in this db?
>
>
>
> Thanks.
> -- 
> Marco Bambini
> http://www.sqlabs.com
> http://www.creolabs.com/payshield/
>
>
>
>
>
>
> 
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Martin Engelschalk]

Hi,

as far as I know, you cannot do what you want to do in pure SQL. 
However, perhaps someone cleverer can contradict me.

You could first execute the update statement, check if there was a row 
which was updated using sqlite3_changes() (see 
http://www.sqlite.org/capi3ref.html#sqlite3_changes), and then issue the 
insert if there was none.

Martin

robinsmathew wrote:
> hey thanx for the reply... u leave the things happening inside.. wat i jus
> wanna do is i wanna insert a new row to a table
> the table will be like this
> stock_id PKproduct_id FK   quantitystock_date
> 1 10001028-05-2009
> 10001 1001  527-05-2009
>
> and wen i insert a new row with values  NULL,   1000,   15,30-05-2009 
>   
> i dont want want it as a new 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 Engelschalk wrote:
>   
>> 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.
>> S,. it seems to me that you have to implement the logic in your
>> application.
>>
>> Martin
>>
>> robinsmathew wrote:
>> 
>>> hi am new to SQLite can anybody please tell me how this query can be
>>> solved
>>> in SQLite?
>>>
>>> IF EXISTS (SELECT prod_batch_code FROM stock_tab WHERE
>>> prod_batch_code=1000) 
>>> UPDATE stock_tab 
>>> SET stock_qty=stock_qty+(SELECT purchase_qty ROM purchase_tab
>>> WHERE
>>> oduct_batch_code=1000 ) 
>>> WHERE prod_batch_code=1000
>>> ELSE 
>>> INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty,
>>> stock_date) values (20009, 1003, 200,  
>>> DATETIME('NOW') );
>>>   
>>>   
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>> 
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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.
S,. it seems to me that you have to implement the logic in your application.

Martin

robinsmathew wrote:
> hi am new to SQLite can anybody please tell me how this query can be solved
> in SQLite?
>
> IF EXISTS (SELECT prod_batch_code FROM stock_tab WHERE prod_batch_code=1000) 
> UPDATE stock_tab 
> SET stock_qty=stock_qty+(SELECT purchase_qty ROM purchase_tab WHERE
> oduct_batch_code=1000 ) 
> WHERE prod_batch_code=1000
> ELSE 
> INSERT INTO stock_tab(stock_id, prod_batch_code, stock_qty,
> stock_date) values (20009, 1003, 200,  
> DATETIME('NOW') );
>   

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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
>
> INT64 iOther;
> char mBuffer[64];   
>
> #ifdef _WIN32
> sprintf(mBuffer, "%I64d", iOther);
> #else
> sprintf(mBuffer, "%lld", iOther);
> #endif
>
> // From String to INT64
>
> #ifdef _WIN32
> iOther = _atoi64(mBuffer);
> #elif defined(AIX5)
> iOther = strtoll(mBuffer, 0, 10);
> #elif defined(HPUX)
> iOther = __strtoll(mBuffer, 0, 10);
> #else
> iOther = atoll(mBuffer);
> #endif
>
> Martin
>
> Sam Carleton wrote:
>   
>> I am current developing a system only on Windows, but I do plan to port it
>> to OSX someday.  I am passing ID's as strings to keep maximum flexibility
>> between databases and the existing system.  So how do I convert a
>> sqlite3_int64 to a string and a string to a sqlite3_int64 in a cross
>> platform fashion?
>>
>> Sam
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>   
>> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[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);
#endif

// From String to INT64

#ifdef _WIN32
iOther = _atoi64(mBuffer);
#elif defined(AIX5)
iOther = strtoll(mBuffer, 0, 10);
#elif defined(HPUX)
iOther = __strtoll(mBuffer, 0, 10);
#else
iOther = atoll(mBuffer);
#endif

Martin

Sam Carleton wrote:
> I am current developing a system only on Windows, but I do plan to port it
> to OSX someday.  I am passing ID's as strings to keep maximum flexibility
> between databases and the existing system.  So how do I convert a
> sqlite3_int64 to a string and a string to a sqlite3_int64 in a cross
> platform fashion?
>
> Sam
> ___
> 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] Appending Text to a Column

[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 tblEntry, they can add 
> more tags later like:
>
> update tblEntry set Tags = Tags + ' new text' where RowID = 13
>
> But whenever I try to do this I end up with 0 in the column. I've done 
> searches and found discussions on
>
> concat(..) or append(..)
>
> but when I try to use these sqlite doesn't recognize them. How do I go about 
> adding a value to the end of an existing value? Do I need to retrieve it and 
> append it in my Java code as opposed to in the sql statement?
>
> Thanks!
>
> _
> Windows Live™: Keep your life in sync.
> http://windowslive.com/explore?ocid=TXT_TAGLM_WL_allup_1a_explore_042009
> ___
> 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] what is the default for the commit when the connection is opened.

[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 "rollback".
This does not depend on how you open the database.

Martin

Joanne Pham wrote:
> Hi All,
> When we use the sqlite3_open_v2 to open the database is this defautl to "Auto 
> commit" ?
> Thanks,
> JP
>
>
>   
> ___
> 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] Selecting records by INDEXED key

[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 automatically.

Also, i see that you enclose column names and table names by double 
quotes. This is not necessary.

HTH
Martin

ggcoo...@clearmail.com.au wrote:
> Hi
> I am trying to select (list) records by their index sequence BUT keep
> getting the SQL error.
>
> "SQL error can't use index: Surname"
> . Indices showes the index
>
> ;
> ;Sample INDEX program
> ;
> CREATE TABLE "APPLE" ("MyName", "MySurname", "MyAge", PRIMARY KEY ("MyAge"));
> CREATE INDEX "Surname" on "APPLE" ("MySurname");
>
> INSERT INTO "APPLE" VALUES ('Greg','Hooper', 54);
> INSERT INTO "APPLE" VALUES ('Daniel','Hooper', 30);
> INSERT INTO "APPLE" VALUES ('Nicole','Lvester', 32);
> INSERT INTO "APPLE" VALUES ('Rhonda','Grin', 50);
>
> Select * From "APPLE" INDEX BY "Surname";
>
>
>
> Regards Greg.
>
> ___
> 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 versus mySQL in PHP

[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 the right place, but I don't know where else to ask.
>
> Lately, I read in http://www.sqlite.org/speed.html, that sqlite is a bit
> faster than mysql and postgreSQL. But, when I perform a little testing yo
> compare them in php, I got a contrast result.
>
> For both testing, I use this table :
> CREATE TABLE test (a varchar(20) PRIMARY KEY, b varchar(20), c varchar(20),
>   d varchar(20), e varchar(20),  f varchar(20),
>   g varchar(20), h varchar(20),  i varchar(20), j
> varchar(20))
>
> for that table, I perform 5000 insert for both sqlite and mysql :
> here's for sqlite code :
> === sqlite-test.php ===
> $time_start = microtime(true);
> $db = sqlite_open('pegawai.db', 0666, $sqliteerror);
> for($i = 0; $i < 5000; $i++)
> {
> $sql = "INSERT INTO test VALUES
> ('a$i','b$i','c$i','d$i','e$i','b$i','b$i','b$i','b$i','b$i')";
> $q = sqlite_query($db, $sql);
> }
> sqlite_close($db);
> $time_end = microtime(true);
> $time = $time_end - $time_start;
> echo "executed in $time seconds\n";
> =
>
> and here's for mysql code :
> = mysql-test.php 
> .
> $time_start = microtime(true);
> for($i = 0; $i < 5000; $i++)
> {
> $sql = "INSERT INTO test VALUES
> ('a$i','b$i','c$i','d$i','e$i','b$i','b$i','b$i','b$i','b$i')";
> $q = mysql_query($sql);
> }
> $time_end = microtime(true);
> $time = $time_end - $time_start;
> echo "executed in $time seconds\n";
> ..
> ===
>
> when I run those 2 script, the first one (sqlite) run for 43.4918169975
> seconds, while the second (mysql) only take 0.52137298584 seconds.
>
> It means that sqlite run 80x longer than mysql did (very contrast with the
> one I read in http://www.sqlite.org/speed.html).
>
> Please tell me where do I did wrong. Thanks very much.
>
> Regards,
>
> --
> Anton Rifco S
>
> Student of Informatics Department
> School of Electrical Engineering and Informatics
> Institute Technology of Bandung
> ___
> 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] Sqlite3_open Wrapper Issue

[Martin Engelschalk]

Hi,

sqlite3_open takes a Pointer-Pointer as 2nd argument:

int sqlite3_open(
  const char *filename,   /* Database filename (UTF-8) */
  sqlite3 **ppDb  /* OUT: SQLite db handle */
);

This is what your compiler tries to tell you:

safecalls.c:152: warning: passing argument 2 of ‘sqlite3_open’ from 
incompatible pointer type

So, you have to declare your own function like this as well:

int safe_sqlite3_open(char *tablename, sqlite3 **db)
{
   int retval;

   retval = sqlite3_open(tablename, db);

   if(retval != SQLITE_OK)
  HandleError(retval, "sqlite3_open", "Can't open database: 
%s", sqlite3_errmsg(*db));

   return retval;
 }


And call it like this:

sqlite3 *MyDb;

safe_sqlite3_open("MyDatabaseFile.db3", &MyDb)


Martin

TW wrote:
>   I made a safe_sqlite3_open function like this:
>
> /* From sqlite.c --> query_and_populate function */
>   int safe_sqlite3_open(char *tablename, sqlite3 *db)
>   {
>  int retval;
>
>  retval = sqlite3_open(tablename, db);
>
>  if(retval != SQLITE_OK)
> HandleError(retval, "sqlite3_open", "Can't open 
> database: %s", sqlite3_errmsg(db));
>
>  return retval;
>  }
>
>   In normal usage, I call the function like this:
>
>   /* From safecalls.c --> safe_sqlite3_open */
>   sqlite3 *db;
>
>   rc = safe_sqlite3_open(tablename, &db);
>
>   When I run make, though, I get these errors:
>
>   gcc -g -c cursedj.c -o cursedj.o 
>   gcc -g -c musicinfo.c -o musicinfo.o 
>   gcc -g -c sqlite.c -o sqlite.o
>   sqlite.c: In function ‘query_and_populate’:
>   sqlite.c:14: warning: passing argument 2 of ‘safe_sqlite3_open’ 
> from incompatible pointer type
>   gcc -g -c winmanip.c -o winmanip.o 
>   gcc -g -c output.c -o output.o 
>   gcc -g -c dhandler.c -o dhandler.o
>   gcc -g -c help.c -o help.o 
>   gcc -g -c file.c -o file.o
>   gcc -g -c safecalls.c -o safecalls.o
>   safecalls.c: In function ‘safe_sqlite3_open’:
>   safecalls.c:152: warning: passing argument 2 of ‘sqlite3_open’ 
> from incompatible pointer type
>   gcc -Wall -g -lm -lncurses -lsqlite3 -o cursedj cursedj.o 
> musicinfo.o sqlite.o winmanip.o \
>   output.o dhandler.o help.o file.o safecalls.o
>
>   I know that they're just warnings, but, how can I pass the sqlite3 
> pointer to my safe function 
> without getting any errors?  I've tried making the function parameters "*&", 
> "&", passing the "address of" 
> to the function...all to no avail, except what I have now, but I get those 
> warnings, BUT, the code does 
> work and my program runs the way it is above.
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE : Constraint question

[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 the declared type of that column."

See also http://www.sqlite.org/datatype3.html

Martin

REPKA_Maxime_NeufBox wrote:
> Hello,
> I am working on Database not for a long time. From SQLITE Tutorial exam
> table :*
>
> ->> Why is it possible to change data not defined in the constraint :
> Exemple : enter TEXT if the column is INTERGER ??
>  enter 25 caracters if column is declared VARCHAR(15) ??
> I thought i will get an error return
> See exemple below :
>
> sqlite> pragma table_info('exam');
> 0|ekey|INTEGER|0||1
> 1|fn|VARCHAR(15)|0||0
> 2|ln|VARCHAR(30)|0||0
> 3|exam|INTEGER|0||0
> 4|score|DOUBLE|0||0
> 5|timeEnter|DATE|0||0
>
> sqlite> select * from exam;
> 1|Bob|Anderson|1|75.0|2009-02-24 09:41:04
> 2|Bob|Anderson|2|82.0|2009-02-24 10:11:45
>
> sqlite> UPDATE main.'exam' SET fn='Bob12345678901234567890' WHERE ekey =
> '1';
> sqlite> UPDATE main.'exam' SET exam='NN' WHERE ekey = '1';
> sqlite> select * from exam;
> 1|Bob12345678901234567890|Anderson|NN|75.0|2009-02-24 09:41:04
> 2|Bob|Anderson|2|82.0|2009-02-24 10:11:45
> sqlite>
>
> Sincères salutations
> Maxime REPKA
> Tel : 02.31.34.75.65
> MailTo:repka.max...@neuf.fr
>
> ___
> 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] SELECT queries and NULL value parameters

[Martin Engelschalk]

Hi Tom,

yes, you are right. It does not matter how you put the NULL in your 
query. The bind variable does not change the operator from = to IS.
However, if you do not want to change the SQL text, and know a value 
which your field will never have, and do not use an index, then you can 
write

--- where coalesce(maybenullcolumn, 'nullvalue') = coalesce(?, 'nullvalue')

Martin

Hynes, Tom wrote:
> Thanks for the quick response!  Yes, I understand the differences between 
> querying with IS NULL vs. = NULL.  But I had always thought that when using 
> *parameter binding* a NULL query parameter would be treated like the IS NULL 
> case when doing the comparison, not the equality case.  Hmm, Sounds like I've 
> had a misconception about this.  
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of P Kishor
> Sent: Wednesday, March 11, 2009 10:25 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] SELECT queries and NULL value parameters
>
> On Wed, Mar 11, 2009 at 9:14 AM, Hynes, Tom  wrote:
>   
>>> ... yes, this is expected.
>>>   
>> Can you explain that a bit more?  I certainly would not have expected it.  
>> Thanks.
>> 
>
> sqlite> CREATE TABLE foo (a);
> sqlite> INSERT INTO foo VALUES (1);
> sqlite> INSERT INTO foo VALUES ('ab');
> sqlite> INSERT INTO foo VALUES ('');
> sqlite> INSERT INTO foo VALUES (NULL);
> sqlite> SELECT * FROM foo;
> 1
> ab
>
>
> sqlite> SELECT Count(*) FROM foo;
> 4
> sqlite> SELECT Count(*) FROM foo WHERE a = 1;
> 1
> sqlite> SELECT Count(*) FROM foo WHERE a = '';
> 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-
>> From: sqlite-users-boun...@sqlite.org 
>> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Martin Engelschalk
>> Sent: Wednesday, March 11, 2009 8:59 AM
>> To: General Discussion of SQLite Database
>> Subject: Re: [sqlite] SELECT queries and NULL value parameters
>>
>> Hi,
>>
>> yes, this is expected. Note that you use the = - operator in WHERE
>> maybenullcolumn = @value
>> and NULL = NULL evaluates to false.
>> This is SQL standard.
>>
>> Martin
>>
>> diego.d...@bentley.com wrote:
>> 
>>> Hello,
>>>
>>> In my usage of SQLite, I found a behavior that might be considered a
>>> bug, but I would like others' input on it.
>>>
>>> Consider the following table with a single row, with one column
>>> containing a null value:
>>>
>>> CREATE TABLE MyTable (id integer primary key autoincrement, label
>>> char(255), maybenullcolumn integer);
>>> INSERT INTO MyTable (label) VALUES ('Label');
>>>
>>> If one tries to retrieve that row with the "IS NULL" syntax, it works
>>> fine (the following statement returns 1):
>>> SELECT COUNT(*) FROM MyTable WHERE maybenullcolumn IS NULL;
>>>
>>> ... but if one uses parameters, the statement returns 0:
>>> SELECT COUNT(*) FROM MyTable WHERE maybenullcolumn = @value;
>>> ...
>>> sqlite3_bind_null(pStmt, 1);
>>>
>>> Is this expected?
>>>
>>> Thanks,
>>>
>>> Diego
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>>   
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>> 
>
>
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECT queries and NULL value parameters

[Martin Engelschalk]

Hi,

yes, this is expected. Note that you use the = - operator in WHERE 
maybenullcolumn = @value
and NULL = NULL evaluates to false.
This is SQL standard.

Martin

diego.d...@bentley.com wrote:
> Hello,
>
> In my usage of SQLite, I found a behavior that might be considered a
> bug, but I would like others' input on it.
>
> Consider the following table with a single row, with one column
> containing a null value:
>
> CREATE TABLE MyTable (id integer primary key autoincrement, label
> char(255), maybenullcolumn integer);
> INSERT INTO MyTable (label) VALUES ('Label');
>
> If one tries to retrieve that row with the "IS NULL" syntax, it works
> fine (the following statement returns 1):
> SELECT COUNT(*) FROM MyTable WHERE maybenullcolumn IS NULL;
>
> ... but if one uses parameters, the statement returns 0:
> SELECT COUNT(*) FROM MyTable WHERE maybenullcolumn = @value;
> ...
> sqlite3_bind_null(pStmt, 1);
>
> Is this expected?
>
> Thanks,
>
> Diego
> ___
> 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] insert in C

[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(?)", -1, 
&pVM, &szTail);
   nRes = sqlite3_bind_text(mpVM, 1, "Hello", -1, SQLITE_TRANSIENT);
   nRest = sqlite3_step(mpVM);

HTH
Martin

mrobi...@cs.fiu.edu wrote:
> Good Morning,
>
> I would like to write in C the equivalent code for:
>
>insert into table1 values('Hello');
>
> using a variable  char temp[20]= "Hello";
>
> instead of the literal Hello
>
>
> I have used multiple variations of the following, but no luck
>
> char temp[20]= "Hello";
> sql = "INSERT INTO probes VALUES('%s', :temp)";
> rc = sqlite3_exec(db, sql, NULL, NULL, &zErr);
>
> Thank you
>
> Michael
>
> ___
> 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] set a Trigger on select

[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


Spitzer, Thomas wrote:
> Hello,
>  
> As already said, we plan to use the sqlite for the parameters of an
> embedded system.
> Triggers shall be used, to write the changes do the hardware drivers,
> wherever necessary.
>  
> Among the whole paramitration there are some "parameters" which are
> actually reads to specific hardware addresses.
> There are only a few of them, but it would be nice to read back things
> like "serialnumber" direct from the hardware, instead of writing them to
> the database file only.
> A "Trigger on select" would be the solution, but when I understand the
> manual correct, this is not supported.
>  
> Questions: 
> 1. Is it correct, that triggers on reading values (select) are not
> supported?
> 2. Any idea, for a workaround?
>  
> Rgds,
> Thomas 
>
> Hottinger Baldwin Messtechnik GmbH, Im Tiefen See 45, 64293 Darmstadt, 
> Germany | www.hbm.com 
>
> Registered as GmbH (German limited liability corporation) in the commercial 
> register at the local court of Darmstadt, HRB 1147  
> Company domiciled in Darmstadt | CEO: Andreas Huellhorst | Chairman of the 
> board: James Charles Webster
>
> Als Gesellschaft mit beschraenkter Haftung eingetragen im Handelsregister des 
> Amtsgerichts Darmstadt unter HRB 1147 
> Sitz der Gesellschaft: Darmstadt | Geschaeftsfuehrung: Andreas Huellhorst | 
> Aufsichtsratsvorsitzender: James Charles Webster
>
> The information in this email is confidential. It is intended solely for the 
> addressee. If you are not the intended recipient, please let me know and 
> delete this email.
>
> Die in dieser E-Mail enthaltene Information ist vertraulich und lediglich fur 
> den Empfaenger bestimmt. Sollten Sie nicht der eigentliche Empfaenger sein, 
> informieren Sie mich bitte kurz und loeschen diese E-Mail.
>
> ___
> 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] INSERTing OR REPLACEing Together or one at a time

[Martin Engelschalk]

Hi,

If you could post how it does fail, exactly, maybe we can help 

Martin

jose isaias cabrera wrote:
> Ok.  That is what I am doing now.  Every so often, it fails, for some 
> reason.  It could be a network problem, but I don't think so.  Anyway, 
> thanks for the help.
>
> josé
>
> - Original Message - 
> From: "Martin Engelschalk" 
> To: "General Discussion of SQLite Database" 
> Sent: Monday, March 02, 2009 1:14 PM
> Subject: Re: [sqlite] INSERTing OR REPLACEing Together or one at a time
>
>
>   
>> Hi,
>>
>> it seems to me that between LSOpenProjects and LSOpenSubProjects you
>> have a 1:n relationship, and also between LSOpenSubProjects and 
>> LSOpenJobs.
>> Also, it seems that you want to copy data from an attached database "c"
>> to the main database.
>> So, if you want to preserve this relationship in your main database, you
>> have to use one transaction, Command #2, and rollback in case of failure.
>>
>> Martin
>>
>> jose isaias cabrera wrote:
>> 
>>> Greetings.
>>>
>>> which command is safer to use:
>>>
>>> Command #1:
>>>
>>> BEGIN;
>>> INSERT OR REPLACE INTO LSOpenProjects
>>> SELECT * FROM c.LSOpenProjects
>>> WHERE ProjID = 2000;
>>> COMMIT;
>>> BEGIN;
>>> INSERT OR REPLACE INTO LSOpenSubProjects
>>> SELECT * FROM c.LSOpenSubProjects
>>> WHERE ProjID = 2000;
>>> COMMIT;
>>> BEGIN;
>>> INSERT OR REPLACE INTO LSOpenJobs
>>> SELECT * FROM c.LSOpenJobs
>>> WHERE ProjID = 2000;
>>> COMMIT;
>>>
>>>
>>> Command #2:
>>>
>>> BEGIN;
>>> INSERT OR REPLACE INTO LSOpenProjects
>>> SELECT * FROM c.LSOpenProjects
>>> WHERE ProjID = 2000;
>>> INSERT OR REPLACE INTO LSOpenSubProjects
>>> SELECT * FROM c.LSOpenSubProjects
>>> WHERE ProjID = 2000;
>>> INSERT OR REPLACE INTO LSOpenJobs
>>> SELECT * FROM c.LSOpenJobs
>>> WHERE ProjID = 2000;
>>> COMMIT;
>>>
>>> This latter one is failing every so often.  But, it should not matter 
>>> much,
>>> since it is writing to three different tables.  I should say that there 
>>> is
>>> always, just one LSOpenProject record; at least, one LSOpenSubProjects
>>> records and, at least, one LSOpenJobs record.  But 99% of the time, there 
>>> is
>>> always more than one LSOpenJobs.
>>>
>>> Any ideas?
>>>
>>> thanks,
>>>
>>> josé
>>>
>>>
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>>>
>>>   
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>> 
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Random Syntax changed in 3.6.11?

[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 be interested 
what this means.

Martin

Alberto Simões wrote:
> Hello
>
> I am using random as:
>
> SELECT word FROM dict ORDER BY RANDOM(term) LIMIT 1;
>
> And I am getting:
>
> DBD::SQLite::db prepare failed: wrong number of arguments to function
> RANDOM()(1) at dbdimp.c line 271 at OpenDict.pm line 64.
>
> (yes, using it through DBD::SQLite, but I do not think that is the problem).
>
> Thanks
> Alberto
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] INSERTing OR REPLACEing Together or one at a time

[Martin Engelschalk]

Hi,

it seems to me that between LSOpenProjects and LSOpenSubProjects you 
have a 1:n relationship, and also between LSOpenSubProjects and LSOpenJobs.
Also, it seems that you want to copy data from an attached database "c" 
to the main database.
So, if you want to preserve this relationship in your main database, you 
have to use one transaction, Command #2, and rollback in case of failure.

Martin

jose isaias cabrera wrote:
> Greetings.
>
> which command is safer to use:
>
> Command #1:
>
> BEGIN;
> INSERT OR REPLACE INTO LSOpenProjects
> SELECT * FROM c.LSOpenProjects
> WHERE ProjID = 2000;
> COMMIT;
> BEGIN;
> INSERT OR REPLACE INTO LSOpenSubProjects
> SELECT * FROM c.LSOpenSubProjects
> WHERE ProjID = 2000;
> COMMIT;
> BEGIN;
> INSERT OR REPLACE INTO LSOpenJobs
> SELECT * FROM c.LSOpenJobs
> WHERE ProjID = 2000;
> COMMIT;
>
>
> Command #2:
>
> BEGIN;
> INSERT OR REPLACE INTO LSOpenProjects
> SELECT * FROM c.LSOpenProjects
> WHERE ProjID = 2000;
> INSERT OR REPLACE INTO LSOpenSubProjects
> SELECT * FROM c.LSOpenSubProjects
> WHERE ProjID = 2000;
> INSERT OR REPLACE INTO LSOpenJobs
> SELECT * FROM c.LSOpenJobs
> WHERE ProjID = 2000;
> COMMIT;
>
> This latter one is failing every so often.  But, it should not matter much, 
> since it is writing to three different tables.  I should say that there is 
> always, just one LSOpenProject record; at least, one LSOpenSubProjects 
> records and, at least, one LSOpenJobs record.  But 99% of the time, there is 
> always more than one LSOpenJobs.
>
> Any ideas?
>
> thanks,
>
> josé
>
>
> ___
> 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] Is this possible?

[Martin Engelschalk]

Hi Mike,

Yes, this is possible, however, it is complicated.

Lets first create the tables an populate them:

create table Container (ContainerName text, ContainerId text primary key)
create table Contents (ContentName text, ContainerId text)

insert into Container values ('Fruit', 'ABC-0001');
insert into Container values ('Vegetable', 'ABC-0002');
insert into Container values ('Computer', 'ABC-0003');

insert into Contents values ('Orange (Navel)', 'ABC-0001');
insert into Contents values ('Apple (Baldwin)', 'ABC-0001');
insert into Contents values ('Apple (Granny Smith)', 'ABC-0001');
insert into Contents values ('Broccoli', 'ABC-0002');
insert into Contents values ('Carrot', 'ABC-0002');
insert into Contents values ('Picture 1.png', 'ABC-0003');
insert into Contents values ('Safari.app', 'ABC-0003');
insert into Contents values ('Portfolio.psd', 'ABC-0003');
insert into Contents values ('iTunes.app', 'ABC-0003');

The following select gets you the desired result. Note that your 
condition on the ContentName ( where ContentName like '%app%' ) appeares 
twice. I could not find a way to avoid this.

select Name, case OrderValue when 0 then NULL else ContainerId end as 
ContainerId
from
(
select distinct ContainerName as Name, Container.ContainerId as 
ContainerId, 0 as OrderValue
from Contents join Container on Container.ContainerId = Contents.ContainerId
 where ContentName like '%app%'
union
select ContentName as Name, Contents.ContainerId as ContainerId, 1 as 
OrderValue
  from Contents join Container on Container.ContainerId = 
Contents.ContainerId
 where ContentName like '%app%'
order by 2,3
)
 
Martin

Mike Yenco wrote:
> OK, let's see if I can clarify this.
>
> I'm using "blank", "Group", and "Code" as placeholders here.  Blank is  
> a column that contains nothing (An empty placeholder I need for  
> display purposes).  Group could be any text.  Code is some ID text.
>
> Table B is being written to by some third-party code which I can't  
> modify.  Ideally I would put the "blank" I want into Table B, but  
> unfortunately the third-party code can't cope with that.
>
> If it helps, let me fill in some example data:
>
> Table A ... let's call it "Container"
> "" | "Fruit" | "ABC-0001"
> "" | "Vegetable" | "ABC-0002"
> "" | "Computer" | "ABC-0003"
>
> Table B ... let's call it "Contents"
> "Orange (Navel)" | "ABC-0001"
> "Apple (Baldwin)" | "ABC-0001"
> "Apple (Granny Smith)" | "ABC-0001"
> "Broccoli" | "ABC-0002"
> "Carrot" | "ABC-0002"
> "Picture 1.png" | "ABC-0003"
> "Safari.app" | "ABC-0003"
> "Portfolio.psd" | "ABC-0003"
> "iTunes.app" | "ABC-0004"
>
> Now let's say I'm looking for as my search anything that contains  
> "app" in the first column of table B.  I'm looking for results that  
> group the matching items.
>
> "Fruit"
> "" | "Apple (Baldwin)" | "ABC-0001"
> "" | "Apple (Granny Smith)" | "ABC-0001"
> "Computer"
> "" | "Safari.app" | "ABC-0003"
> "" | "iTunes.app" | "ABC-003"
>
> The rest do not contain "app".  There is no need to display the  
> "Vegetable" container as there were no contents found for that one.
>
> My first inclination was to go with a repeat loop...  take the number  
> of lines in Table A and loop through each one doing a search of  
> contents for my search term... 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 then... I can probably work around that.  But I would  
> still need a way to get the following at a minimum:
>
> "Fruit"
> "Apple (Baldwin)" | "ABC-0001"
> "Apple (Granny Smith)" | "ABC-0001"
> "Computer"
> "Safari.app" | "ABC-0003"
> "iTunes.app" | "ABC-0003"
>
> If the blank has to be duplicated to make an equal number of items  
> returned that would also be fine:
>
> "Fruit" | ""
> "Apple (Baldwin)" | "ABC-0001"
> "Apple (Granny Smith)" | "ABC-0001"
> "Computer" | ""
> "Safari.app" | "ABC-0003"
> "iTunes.app" | "ABC-0003"
>
>
> Possible?
>
> -Mike
>
>
> On Mar 1, 2009, at 12:29 PM, Martin Engelschalk wrote:
>
>   
>> 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,
>> but not "blank" ?
>>
>> Martin
>> 
>
> ___
> 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] Is this possible?

[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, 
but not "blank" ?

Martin

Mike Yenco wrote:
> Ok, I guess this list doesn't support image attachments.  Let's see if  
> I can sort of illustrate what I'm looking to do in text without  
> requiring 1000 words.
>
> Table A
> blank | Group 1 | Code 1
> blank | Group 2 | Code 2
> blank | Group 3 | Code 3
>
> Table B
> Item 1 | Code 1
> Item 2 | Code 1
> Item 3 | Code 1
> Item 4 | Code 2
> Item 5 | Code 2
> Item 6 | Code 3
> Item 7 | Code 3
> Item 8 | Code 3
> Item 9 | Code 3
>
>
> Looking for a way to get a result something like below where a search  
> of Table B for items containing some particular text will return the  
> data in a format like this:
>
> Group 1
> blank | Item 2 | Code 1
> blank | Item 3 | Code 1
> Group 3
> blank | Item 7 | Code 3
> blank | Item 9 | Code 3
>
>
> -Mike
>
>
> On Mar 1, 2009, at 11:57 AM, 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 matching items within the result? (see attached image).
>>
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> 
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is this possible?

[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 matching items within the result? (see attached image).
>
>
> 
>
> ___
> 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] running sqlite with gcc C, continuation.....

[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 other sqlite function), because you already included sqlite3.h

Martin

mrobi...@cs.fiu.edu wrote:
> Sorry,
>
> Maybe I should have included the following details:
>
>
> This is the program:
>
> #include 
> #include 
> #include 
>
>
>
> int sqlite3_open(
>   const char *filename,   /* Database filename (UTF-8) */
>   sqlite3 **ppDb  /* OUT: SQLite db handle */
> );
>
>
> int sqlite3_close(sqlite3 *);
>
>
> int main()
> {
> printf("hi\n");
>
> sqlite3 *db;
> const char KDbName[] = "c:\\test\\Db1.db";
> int rc = sqlite3_open(KDbName, &db);
> if (rc)
> {
>  sqlite3_close (db);
>  return 0;
> }
>
>
> exit (0);
> }
>
>
> =
>
> and these are the errors when compiling
>
>
>
> C:\SQLite>\gcc\gcc -o s.exe s.c
> C:\DOCUME~1\Michael\LOCALS~1\Temp/ccigbaaa.o:s.c:(.text+0x71): undefined
> referen
> ce to `sqlite3_open'
> C:\DOCUME~1\Michael\LOCALS~1\Temp/ccigbaaa.o:s.c:(.text+0x85): undefined
> referen
> ce to `sqlite3_close'
> collect2: ld returned 1 exit status
>
>
> Thanks for your help,
>
> Michael Robinson
>
> ___
> 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] running sqlite with gcc C

[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 installation

Martin

mrobi...@cs.fiu.edu wrote:
> Dear sqlite users group,
>
> I am new at SQlite3 and I would be very thankful for your help.
>
> I read that SQlite does not require installation, so I downloaded the
> Precompiled Binaries For Windows and run sqlite3.exe in windows xp, no
> problem, however, I have some gcc C programs that I run in windows xp
> "dos" shell and in Linux and Unix, same code, just recompiled in its
> corresponding OS. I would like to use SQlite3 with these gcc C programs.
>
> Reading thru the users group postings and in google in general, I came to
> the conclusion that some kind of installation may be required, but I have
> not been able to find instructions about it. Please guide me to get this
> issued resolved so that I can start creating great apps with SQlite.
>
> Thank you very much
>
> Michael R
>
> ___
> 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] number of columns in a table

[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, 
&pStmnt, 0);
int NumberOfColumns = sqlite3_column_count(pStmnt);
sqlite3_finalize(pStmnt)

Martin

baxy77bax wrote:
> hi,
>
> my question is how to get the number of columns in an existing table.
> example if i can retrieve the number of rows with SELECT count(*) FROM table
> , is there also some simple query to do the same thing for counting the
> number of columns
>
> thnx
>
> r
>
>   

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Group by week

[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
> ___
> 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] segmentation violation in fulltest on Mac OS X

[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 full tests on Mac OS X:
>> The last test completed is consistently thread001.1.3.
>>
>> 
>
> This was a problem in the testing logic, not in the SQLite library  
> itself.  The test logic was trying to run a threading test with some  
> of the mutexes disabled.  Everything works correctly once mutexes are  
> enabled properly.
>
> Of course, I wasted 4 hours tracking the problem down.  This is  
> yet another episode that demonstrates how threads are a pernicious  
> evil that should be studiously avoided in any program that you  
> actually want to work.  Threads cause hard-to-trace bugs.  Threads  
> result in non-deterministic behavior.  Threads make programs run  
> slower.  Just say "No" to threads...
>
> D. Richard Hipp
> d...@hwaci.com
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>   
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users