[sqlite] indefinite database lock

[Ronnel P. Maglasang]

Hi All,

Has anyone encountered an indefinite database lock?
This is a condition where the database is in a locked
state and no process is connected to it. The database
cannot be unlocked by any means and is no longer usable.

My environment is FreeBSD 6.2, SQLite 3.3.7. Several processes
are reading/writing data to the database. This appears to be
intermittent. I'm still figuring out how to replicate it.

Is this a known bug in 3.3.7?

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

Re: [sqlite] easy question: using fully qualified table name

[P Kishor]

On 12/4/08, Julian Bui <[EMAIL PROTECTED]> wrote:
> Hi all,
>
>  I can't seem to use fully qualified table names (dbName.tableName).
>
>  For instance, I create a database file inventory.db, and create a table
>  food.  "SELECT * FROM inventory.food" does not work, nor does "SELECT * FROM
>  inventory.db.food" bc it says "no such table ...".   Since there is no
>  CREATE DATABASE statement, I'm wondering what qualifies as a database in
>  sqlite.  I expect it to be the name of the database file.

You are inside a database (inventory.db). Once inside it, its name
doesn't matter (by inside, I mean, you have opened the db in the
sqlite3 shell, or you have connected to the database have have a db
handle to work with). You say "SELECT * FROM food" and you get your
food. Couldn't be simpler.

Once in the db, you ATTACH another db. In that case you can use the
name of the ATTACHed db. See the syntax in the docs.

>
>  This seems like a simple question, and I bet I'm just being a bonehead.
>  Please help me out.
>
>  Thanks,
>  jb
>  ___
>  sqlite-users mailing list
>  sqlite-users@sqlite.org
>  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how do this stuff in sqlite

[Rachmat Febfauza]

anyone can help me?



- Original Message 
From: Igor Tandetnik <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Thursday, November 27, 2008 12:07:31 PM
Subject: Re: [sqlite] how do this stuff in sqlite

"Rachmat Febfauza" <[EMAIL PROTECTED]>
wrote in message news:[EMAIL PROTECTED]
> i have query that don't work in sqlite but in mysql work and make
> good result.

Define "don't work". Do you get an error? What's the error text?

> CREATE TABLE hasil1 (Code char(5), Level vachar(8), Category varchar
> (50), Product varchar(60), Location varchar(50), Begin datetime, End
> datetime, Difference integer, PRIMARY KEY
> (Code,Level,Category,Product,Location,Begin,End));

Begin and End are keywords in SQLite (SQL is case insensitive). If you 
insist on naming your columns this way, you have to enclose the names in 
double quotes, just as you did when creating awal1 and akhir1.

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] rtree - compiler options

[Oyvind Idland]

Hi,

as far as I can see from source code, there are support for various R-tree
variants:
/*
** Exactly one of the following must be set to 1.
*/
#define VARIANT_GUTTMAN_QUADRATIC_SPLIT 0
#define VARIANT_GUTTMAN_LINEAR_SPLIT0
#define VARIANT_RSTARTREE_SPLIT 1


My issue is the speed of populating the rtree, and doing so in memory while
the
application starts. As far as I can remember when I once studied rtrees,
the various R-/R+/R* -variants performs differently on inserts (though there
is a win on lookup).

It could be interesting to try out the various implementations in sqlite,
but
has the other variants been tested and can be relied on ?

Anyways, the sqlite package is a neat tool !

Regards,


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

Re: [sqlite] easy question: using fully qualified table name

[Igor Tandetnik]

Julian Bui <[EMAIL PROTECTED]> wrote:
> I can't seem to use fully qualified table names (dbName.tableName).
>
> For instance, I create a database file inventory.db, and create a
> table food.

Database name has nothing to do with file name. The main database (one 
you open with sqlite3_open) is always named "main", and a temporary 
database is named "temp". In an ATTACH statement, you specify names for 
additional databases.

Igor Tandetnik 



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

[sqlite] easy question: using fully qualified table name

[Julian Bui]

Hi all,

I can't seem to use fully qualified table names (dbName.tableName).

For instance, I create a database file inventory.db, and create a table
food.  "SELECT * FROM inventory.food" does not work, nor does "SELECT * FROM
inventory.db.food" bc it says "no such table ...".   Since there is no
CREATE DATABASE statement, I'm wondering what qualifies as a database in
sqlite.  I expect it to be the name of the database file.

This seems like a simple question, and I bet I'm just being a bonehead.
Please help me out.

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

[sqlite] Mac OS X PowerPC Binary Available

[Jeffrey Thompson]

I have successfully compiled the source for version 3.6.6.2 for Mac OS  
X PowerPC that I have placed in a compressed tar file that I wouldn't  
mind making available to be placed in the download section of the web  
site.  You only have Mac OS X Intel binary.

I created the tar flie from /usr and it contains:

./bin/sqlite3
./include/sqlite3.h
./include/sqlite3ext.h
./lib/libsqlite3.0.dylib
./lib/libsqlite3.a
./lib/libsqlite3.dylib
./lib/libsqlite3.la
./lib/libulockmgr.0.0.0.dylib
./lib/libulockmgr.dylib
./lib/pkgconfig/sqlite3.pc

And the compressed tar file is about 2.5 MB.

-rw-r--r--  1 jeffrey  staff  2508800 Dec  4 13:29 src/ 
sqlite3.6.6.2.osx-powerpc.tar.gz

If anyone is interested in it, just let me know what I need to do to  
upload the compressed tar file.

Jeffrey Thompson
[EMAIL PROTECTED]
http://itdiscuss.org

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

Re: [sqlite] Struggling with datetime("now") > MAX(dtEndDate) query - Please Help

[bondington]

Brad, Puneet,

Thanks for getting back to me so quickly. I feel like a fool ;-)

The error of "misuse of aggregate function MAX()" confused me. I ran the
same query in SQL Server, got an error message that I understood and
realised instantly that my brain wasn't switched on and that I needed to do
it exactly as shown in Puneet's example.

Lesson of the day for me is:

"If something doesn't work in Sqlite, try the same thing in SQL Server
before assuming the problem is with Sqlite as opposed to me!"

Cheers

Keith



P Kishor-3 wrote:
> 
> On 12/4/08, Brad Stiles <[EMAIL PROTECTED]> wrote:
>> > SELECT *
>>  > FROM MyTableWithDates
>>  > WHERE datetime("now") > MAX(dtEndDate)
>>
>>
>> What is it that you are actually trying to do with this query?  As
>>  formulated (even if it were syntactically correct, which I don't think
>>  it is), you are either going to get every row in the table, or no rows
>>  at all.  Since the current date ("now") is either greater than the
>>  maximum date in the table, or it is not, and you're not comparing to a
>>  column in each row, only the aggregate, the resulting condition will
>>  either be true for every row in the table, or false for every row.
>>
>>
>>  Brad
> 
> 
> As Brad said, your query doesn't make much sense as is, but you can
> still do it like so --
> 
> [10:15 PM] ~/Sites$sqlite3
> SQLite version 3.5.9
> Enter ".help" for instructions
> sqlite> create table foo (a);
> sqlite> insert into foo values ('2008-12-01');
> sqlite> insert into foo values ('2008-12-02');
> sqlite> insert into foo values ('2008-12-03');
> sqlite> select * from foo;
> 2008-12-01
> 2008-12-02
> 2008-12-03
> sqlite> select max(a) from foo;
> 2008-12-03
> sqlite> select min(a) from foo;
> 2008-12-01
> sqlite> select * from foo where date('now') > (select max(a) from foo);
> 2008-12-01
> 2008-12-02
> 2008-12-03
> sqlite> select * from foo where date('now') < (select max(a) from foo);
> sqlite>
> 
> 
> -- 
> Puneet Kishor http://www.punkish.org/
> Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Struggling-with-datetime%28%22now%22%29-%3E-MAX%28dtEndDate%29-query---Please-Help-tp20837020p20838039.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] SQLite performance woe

[Brown, Daniel]

Hello Donald,

I have managed to speed up some of my SQLite queries by about 300% by
writing them from joins to sub queries.  I am comparing SQLite against a
custom SQL implementation we have here. It looks like it is our cursor
implementation that is so much faster than SQLite the regular queries
are roughly comparable.  I'm going to step through our custom SQL cursor
code and see if how it is managing that sort of speed.

I can't really post my table schema without first obscuring like I did
the original test queries it as it contains sensitive information but
I'll try taking a look at the schema output.

Daniel

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Griggs, Donald
Sent: Wednesday, December 03, 2008 3:51 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQLite performance woe

Hi again, Daniel,

So I guess you're still having certain queries that take about 200x
longer than with your custom code, right?

There's nothing magical about sqlite, so it's not surprizing that code
customized for an application can outperform a generalized sql engine,
but a factor of 200 does seems pretty large.

If it's neither especially proprietary nor private, I'd again urge you
to post here your schema (and repost the slow queries).

You can dump this easily using the command-line utility program, e.g.:

 sqlite3 myDatabase.db
 .output mySchema
 .schema
 .quit

The schema should then be in the file "mySchema"

If the database itself is not too private, you may want to compress it
(with something like 7zip) and upload it somewhere on the web and
provide a link to it.
___
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] Struggling with datetime("now") > MAX(dtEndDate) query - Please Help

[P Kishor]

On 12/4/08, Brad Stiles <[EMAIL PROTECTED]> wrote:
> > SELECT *
>  > FROM MyTableWithDates
>  > WHERE datetime("now") > MAX(dtEndDate)
>
>
> What is it that you are actually trying to do with this query?  As
>  formulated (even if it were syntactically correct, which I don't think
>  it is), you are either going to get every row in the table, or no rows
>  at all.  Since the current date ("now") is either greater than the
>  maximum date in the table, or it is not, and you're not comparing to a
>  column in each row, only the aggregate, the resulting condition will
>  either be true for every row in the table, or false for every row.
>
>
>  Brad


As Brad said, your query doesn't make much sense as is, but you can
still do it like so --

[10:15 PM] ~/Sites$sqlite3
SQLite version 3.5.9
Enter ".help" for instructions
sqlite> create table foo (a);
sqlite> insert into foo values ('2008-12-01');
sqlite> insert into foo values ('2008-12-02');
sqlite> insert into foo values ('2008-12-03');
sqlite> select * from foo;
2008-12-01
2008-12-02
2008-12-03
sqlite> select max(a) from foo;
2008-12-03
sqlite> select min(a) from foo;
2008-12-01
sqlite> select * from foo where date('now') > (select max(a) from foo);
2008-12-01
2008-12-02
2008-12-03
sqlite> select * from foo where date('now') < (select max(a) from foo);
sqlite>


-- 
Puneet Kishor http://www.punkish.org/
Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Struggling with datetime("now") > MAX(dtEndDate) query - Please Help

[Brad Stiles]

> SELECT *
> FROM MyTableWithDates
> WHERE datetime("now") > MAX(dtEndDate)

What is it that you are actually trying to do with this query?  As
formulated (even if it were syntactically correct, which I don't think
it is), you are either going to get every row in the table, or no rows
at all.  Since the current date ("now") is either greater than the
maximum date in the table, or it is not, and you're not comparing to a
column in each row, only the aggregate, the resulting condition will
either be true for every row in the table, or false for every row.

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

[sqlite] Struggling with datetime("now") > MAX(dtEndDate) query - Please Help

[bondington]

Hi,

I've been using SQL for years in SQL Server but I've only recently started
using Sqlite. I've got to grip with most of the little differences but I
can't for the life of me figure out how to do a query where I compare the
current date with the maximum date of a field in a table. E.g.

SELECT *
FROM MyTableWithDates
WHERE datetime("now") > MAX(dtEndDate)

I keep getting an error of "misuse of aggregate function MAX()"

I've defined dtEndDate as DATE and as TIMESTAMP but its made no difference.
I've also tried changing the line to things like WHERE datetime("now") >
MAX(datetime(dtEndDate)) but that hasn't worked.

Please help because this driving me mad!

Cheers
-- 
View this message in context: 
http://www.nabble.com/Struggling-with-datetime%28%22now%22%29-%3E-MAX%28dtEndDate%29-query---Please-Help-tp20837020p20837020.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] rtree insert performance

[Stephen Woodbridge]

Oyvind Idland wrote:
> Thanks for responses :)
> 
> Whereby "objects" you mean "rows," no? You are getting upward of 5500
>> sustained inserts per second. That sounds pretty good. That said, are
>> you using transactions? See what difference that makes.
>>
> 
> Yep, I meant rows. Inserting rows in the data table is much faster
> (1.000.000 in 20 secs or so).
> I am of course not expecting that inserts into a tree is as fast as a flat
> table, but a little
> boost wouldnt hurt. Its probably worth mentioning, that I am using a memory
> resident
> db, which I create at startup. The idea is to simply have a fast memory
> cache.
> 
> I am have tried to wrap it inside a transaction, my pattern is basically
> 
> sqlite3_exec(db, "BEGIN TRANSACTION;", 0, 0, 0);
> sqlite3_prepare_v2( );
> sqlite3_prepare_v2( );
> for(.)
> {
>   sqlite3_bind();
>   sqlite3_step();
>   sqlite3_reset();
>   sqlite3_bind();
>   sqlite3_step();
>   sqlite3_reset();
> }
> sqlite3_finalize();
> sqlite3_finalize();
> sqlite3_exec(db, "COMMIT TRANSACTION;", 0, 0, 0);
> 
> One thing I havent figured out, is, how is a transaction related to prepared
> statements ?
> I mean, using bind(), step() etc, there is a transaction handle, while
> BEGIN/END seems to be
> "global" ?  (thinking of threading etc)
> 
> I'll try to fiddle with Julian's idea.
> 

Transactions are global on a database connection. Once you issue a BEGIN 
TRANSACTION; on a database connection then all work on that same 
connection is part of that transaction untill you COMMIT or ROLLBACK the 
transaction.

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

Re: [sqlite] ODBC test

[Griggs, Donald]

Regarding: "Does there any test case exist for ODBC testing of sqlite,
how one can be sure about sqlite working with ODBC."

=

Sqlite itself does not interface directly with ODBC.  Some nice folks
have written ODBC wrappers, though, and it is those folks you'd want to
contact regarding their testing.

I don't know what wrapper you're using, but one nice ODBC implementation
is provided by Christian Werner at:
   http://www.ch-werner.de/sqliteodbc/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite3Explorer Sqlite Report Designer

[Rajesh Nair]

Dear Cariotoglou Mike,

I am using VC++ for last 6 years to develop utilities and some small
projects. Formerly I was using MS-ACCESS and/or some database classes in my
projects. Once I felt the power of sqlite I started using it from 2003 /
2004. After that I have not used any ADO / ODBC for my databases. I have
felt the easyness of the moto "NO CONFIGURATION" of sqlite. I have developed
some very use full VC++ classes to handle the sqlite database file. I was
producing reports with my-own list-view control. Now some simple formated
reports are developed but if any changes in the report will result a total
EXE replace. That's why I am trying some external utility that could support
my EXE to produce simple formated reports.
I noticed that your DLL can open some FR3 or some FRF extention files.

THE NEED IS THAT REPORT FORMAT WILL BE SAVED AS AN EXTERNAL FILE OF THE
FORMAT THAT YOUR DLL CAN READ AND ALLIGN THE REPORT-VIEWER WINDOW FOR
PRINTING.

The dataset that you specified can be XML or sqlite database itself. If you
are planning to find some time to support sqlite ( as you found some time to
develop the Sqlite3Explorer ) then please keep some points in mind while
rebuilding the DLL.

   1) The dll can be used for both DESIGNING and PRE-VIEWING.
   2) The dll can be used to either DESIGNING or PRE-VIEWING
according to
   some parameters passed in to it. ( ie. If the HOST EXE needs
only pre-viewing the
   report then the DESIGNER window must not appear. The designer
mode will
   be shown on demand specified by a param passed to the DLL)

If I get such a DLL then I will be using the same for my further projects
and will also gradualy replace the method that I was using to show-report.
If some others those who are using sqlite with VC++ / VB or some other WIN32
platform programs may try to use it.
PLEASE INFORM

Thanks
Rajesh Nair


On Mon, Nov 17, 2008 at 5:18 AM, Cariotoglou Mike <[EMAIL PROTECTED]> wrote:

> here is an idea : I might be able to modify the dll to accept datasets in
a
> different, "standard" format, like XML.
> this would imply that your product would need to retrieve data in whatever
> format it needs, transform it to my format,
> pass it to the dll which will do the design and reporting. it will not as
> fast as it is now, but it is definately a solution. you would not have the
> source of the dll, but you could use it freely, no IR issues.
>
> for this to work , the following assumptions must prove correct:
>
> 1. I originally designed the DLL with portability in mind, so it *coul* be
> transformed as I described. since that time, otuehr developers have
improved
> it. I need to check that they have not introduced dependencies and design
> decisions that would nullify its portability.
>
> 2. that you (or somebody else) actually would benefit from this approach.
> f.e do you *like* the report designer (as seen and used from
> sqlite3Explorer), or is it just something you came across and thought to
> give a try?
>
> I only point this out, because it will take me at least 10-20 hours to do
> this conversion, and it would be a big waste of time (mine) to do this
just
> as an intellectual exercise. I am not asking for financial compensation
> here, just to know that I am actually doing something useful...
>
>
> PS another format that would work, besides XML, and that I already have a
> lot of code to support would be the ADO recordset. is this an option for
you
> ? what language are you planning to use in order to host the DLL, and what
> database ?
>
>
> 
>
> From: [EMAIL PROTECTED] on behalf of Rajesh Nair
> Sent: Sat 15/11/2008 5:53 AM
> To: General Discussion of SQLite Database
>  Subject: Re: [sqlite] Sqlite3Explorer Sqlite Report Designer
>
>
>
> Thanks
>
> So I can't use it... Ok ... But I got some source code of  FastReport (
> some
> ealier version ) from the internet and it is in Dephi/Pascal. I don't know
> both of the tools. Can you use the same code to build a dll which can be
> used with other windows programing languages? ( So that no one can raise
> their finger aganist me or YOU ).
>
> I don't want a very efficient-high-end report designer like FastReport. My
> clients require very simple reports, which does not contain much
> caculations
> etc If you can help me  please. It won't be just for me. Those who are
> using SqLite with windows will be happy with a simple report deisgner and
> viewer. Also my company is not willing to puchase any product for just
> reporting, since our programs are supplied freely along with some of our
> products. So an extra cost is not feasible.
>
> A SIMPLE DISIGNER/VIEWER FOR WINDOWS USERS
>
>
> - Original Message -
> From: "Cariotoglou Mike" <[EMAIL PROTECTED]>
> To: "General Discussion of SQLite Database" 
> Sent: Friday, November 14, 2008 7:14 PM
> Subject: Re: [sqlite] Sqlite3Explorer Sqlite Report Designer
>
>
> >I am 

[sqlite] Please test unix builds

[D. Richard Hipp]

In the latest CVS code, the Unix interface for SQLite has been  
extensively reorganized and cleaned up.  It passes all regression  
tests on Linux and MacOSX and so we have high confidence in it.   
Nevertheless, we would appreciate it
if people could test out the latest code from CVS on Unix systems  
other than Linux and MacOSX.

Thanks.

D. Richard Hipp
[EMAIL PROTECTED]



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

Re: [sqlite] detecting which column matched a like clause

[Igor Tandetnik]

"Hernan Eguiluz" <[EMAIL PROTECTED]>
wrote in message news:[EMAIL PROTECTED]
> The problem is that a
> query like this
>
>
> SELECT column1, column2 from TABLE WHERE column1 LIKE "%PATTERN%" or
> column2 LIKE "%PATTERN%"
>
>
> won't tell me what column matched the LIKE.

SELECT column1, column1 LIKE '%PATTERN%',
column2, column2 LIKE '%PATTERN%'
from mytable
WHERE column1 LIKE '%PATTERN%' or column2 LIKE '%PATTERN%';

The two new columns would contain 0 or 1.

If you don't want to repeat each pattern twice, you could try something 
like this:

select column1, matches1, column2, matches2 from
(select column1, column1 LIKE '%PATTERN%' matches1,
column2, column2 LIKE '%PATTERN%' matches2
 from mytable)
where matches1 or matches2;

Igor Tandetnik



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

[sqlite] detecting which column matched a like clause

[Hernan Eguiluz]

Hi, I need to search for matches of a string in two different fields of a 
database table (column1, column2) and need to know which on of the two matched. 
I can do this (ignoring duplicates) with the following code: 

SELECT column1, 1 FROM TABLE WHERE column1 LIKE "%PATTERN%"
UNION
SELECT column2, 2 FROM TABLE WHERE column2 LIKE "%PATTERN%" 

the 1 and 2 indicate which column matched and will be by a higher level of my 
program. But, for performance reasons, I would like to scan the table once (it 
can have many records.) The problem is that a query like this


SELECT column1, column2 from TABLE WHERE column1 LIKE "%PATTERN%" or column2 
LIKE "%PATTERN%"


won't tell me what column matched the LIKE.

Is there a way to achieve this? 

Thanks in advance, Hernan.


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

Re: [sqlite] UPDATE - to simplify the code...

[Igor Tandetnik]

"Ernany" <[EMAIL PROTECTED]> wrote in
message news:[EMAIL PROTECTED]
>> Is it possible to simplify the code? I have ten fields to UPDATE.
>> (STATUS, Responsavel, Country,..)
>>
>> UPDATE Bens SET *STATUS *=
>>  CASE (SELECT COUNT(BENSBK.STATUS) FROM BENSBK WHERE BENSBK.BEM =
>> Bens.BEM)
>>  WHEN 0 THEN Bens.STATUS
>>  ELSE (SELECT BENSBK.STATUS FROM BENSBK WHERE BENSBK.BEM = Bens.BEM)
>>  END
> [similar statements snipped]
>
> Try this:
>
> update Bens
> set STATUS=coalesce(
>   (SELECT BENSBK.STATUS FROM BENSBK WHERE BENSBK.BEM = Bens.BEM),
>   STATUS),
> set Responsavel=coalesce(
>   (SELECT BENSBK.Responsavel FROM BENSBK WHERE BENSBK.BEM = Bens.BEM),
>   Responsavel),
> set Country=coalesce(
>   (SELECT BENSBK.Country FROM BENSBK WHERE BENSBK.BEM = Bens.BEM),
>   Country);
>
> *
> ERROR: near "set": syntax error*

Keep the first instance of "set", remove the other two. As in

update Bens set STATUS=...,
 Responsavel=...,
 Country=...;

> *another question: can I update tables of two different files?*

See ATTACH statement: http://sqlite.org/lang_attach.html

Igor Tandetnik



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

Re: [sqlite] rtree insert performance

[Oyvind Idland]

Thanks for responses :)

Whereby "objects" you mean "rows," no? You are getting upward of 5500
> sustained inserts per second. That sounds pretty good. That said, are
> you using transactions? See what difference that makes.
>

Yep, I meant rows. Inserting rows in the data table is much faster
(1.000.000 in 20 secs or so).
I am of course not expecting that inserts into a tree is as fast as a flat
table, but a little
boost wouldnt hurt. Its probably worth mentioning, that I am using a memory
resident
db, which I create at startup. The idea is to simply have a fast memory
cache.

I am have tried to wrap it inside a transaction, my pattern is basically

sqlite3_exec(db, "BEGIN TRANSACTION;", 0, 0, 0);
sqlite3_prepare_v2( );
sqlite3_prepare_v2( );
for(.)
{
  sqlite3_bind();
  sqlite3_step();
  sqlite3_reset();
  sqlite3_bind();
  sqlite3_step();
  sqlite3_reset();
}
sqlite3_finalize();
sqlite3_finalize();
sqlite3_exec(db, "COMMIT TRANSACTION;", 0, 0, 0);

One thing I havent figured out, is, how is a transaction related to prepared
statements ?
I mean, using bind(), step() etc, there is a transaction handle, while
BEGIN/END seems to be
"global" ?  (thinking of threading etc)

I'll try to fiddle with Julian's idea.

Thanks,


Oyvind




>
> >
> >
> >  Oyvind
> >  ___
> >  sqlite-users mailing list
> >  sqlite-users@sqlite.org
> >  http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
> --
> Puneet Kishor http://www.punkish.org/
> Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/
> Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ODBC test

[goldy]

Hi,

Does there any test case exist for ODBC testing of sqlite, how one can be
sure about sqlite working with ODBC.

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

Re: [sqlite] Problems with 'references'

[Tony Mc]

On Thu, 04 Dec 2008 10:34:59 +1100, you wrote:

> Tks.  It would help if the doco made that known.

It is documented in the section that deals with omitted features of
SQL, which you can find at: http://www.sqlite.org/omitted.html

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

Re: [sqlite] UPDATE - to simplify the code...

[Ernany]

Hello Igor,

> Is it possible to simplify the code? I have ten fields to UPDATE.
> (STATUS, Responsavel, Country,..)
>
> UPDATE Bens SET *STATUS *=
>  CASE (SELECT COUNT(BENSBK.STATUS) FROM BENSBK WHERE BENSBK.BEM =
> Bens.BEM)
>  WHEN 0 THEN Bens.STATUS
>  ELSE (SELECT BENSBK.STATUS FROM BENSBK WHERE BENSBK.BEM = Bens.BEM)
>  END
[similar statements snipped]

Try this:

update Bens
 set STATUS=coalesce(
   (SELECT BENSBK.STATUS FROM BENSBK WHERE BENSBK.BEM = Bens.BEM),
   STATUS),
 set Responsavel=coalesce(
   (SELECT BENSBK.Responsavel FROM BENSBK WHERE BENSBK.BEM = Bens.BEM),
   Responsavel),
 set Country=coalesce(
   (SELECT BENSBK.Country FROM BENSBK WHERE BENSBK.BEM = Bens.BEM),
   Country);

Igor Tandetnik
***
*
ERROR: near "set": syntax error*

Someone help me?

***
*another question: can I update tables of two different files?*

UPDATE Bens SET *STATUS * ?


*Patrim.db3*
TABLE [Bens] (
  [Situacao] NVARCHAR(1),
  [Uso] NVARCHAR(1),
  [Country] NVARCHAR(1),
  [Bem] NUMERIC(9) NOT NULL ON CONFLICT ROLLBACK PRIMARY KEY ON CONFLICT
ROLLBACK,
  [Status] NVARCHAR(1),
  [Responsavel] NVARCHAR(10));

*PatrimBK.db3*
TABLE [Bens] (
  [Situacao] NVARCHAR(1),
  [Uso] NVARCHAR(1),
  [Country] NVARCHAR(1),
  [Bem] NUMERIC(9) NOT NULL ON CONFLICT ROLLBACK PRIMARY KEY ON CONFLICT
ROLLBACK,
  [Status] NVARCHAR(1),
  [Responsavel] NVARCHAR(10));



Thanks a lot

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

Re: [sqlite] extremely slow join on an fts3 table

[Alexandre Courbot]

> On these tables this query is very slow (about 1 row per second)
>
> select g.id  from general g, general_text gt where g.id = gt.id;
>
> and these ones have a normal speed:
>
> select g.id  from general g, general_text_content gt where g.id = gt.docid;

I think the point is that you cannot declare the ID field of
general_text as an primary key. In FTS 3 tables, the docid member is
already a primary key, so it is highly possible that your join does
not use an index and parses all the rows of the table.

A solution would be to insure that general.id and general_text.docid
have the same id, which can probably be achieved by using
last_insert_rowid() after inserting to general_text (warning, there
were a bug in that case if you happen to delete and insert rows again
in general_text - you'd need to do that within a transaction to have
the correct rowid).

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