[sqlite] Getting the row count when using the sqlite3 library

[Tim Streater]

On 22 Oct 2013 at 09:11, Simon Slavin  wrote: 

> If you are writing code specifically intended for use with SQLite (like your
> library was meant to be) then please use PHP's sqlite3 library rather than the
> PDO.  The advantage of using PDO is that if you transfer from one DBMS to
> another, you only need to make minimal changes to your code.  But the
> disadvantage is that it doesn't understand how SQLite works very well, and
> cannot take advantage of the things it does well or return the results it
> produces well.  The PDO library is useful if you are producing one set of code
> that must work with lots of different SQL engines but because it's not native
> to any of them it is its own layer that needs separate understanding and
> debugging.
>
> PHP's sqlite3 library, on the other hand, contains just enough code to let you
> handle sqlite3 databases using conventional PHP calls and objects, rather than
> having to write C code.  It doesn't need much documentation because you just
> look up the equivalent functions in the SQLite3 documentation.  It can do
> everything that SQLite3 does, and provide all the information that SQLite3
> does.

HmmmOK. I'm therefore making a second attempt to use the sqlite3 PHP interface 
in my application instead of PDO. There will be about 35 modules to alter so I 
hope I can work through any issues. One wrinkle is that with the PDO interface, 
I can fetchAll and get an array of the rows returned from a SELECT, which 
allows me to count how many there were. This allows me to make an early exit if 
there are none. Of course, with PDO, that has internally involved fetching all 
the rows, which I could do myself but that seems clumsy. If I need columns x, 
y, z, I had wondered about doing:

  select count(*),x,y,z from sometable where …;

or is that a bad idea?

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

Re: [sqlite] Problem with method numRows() in Sqlite3

[Tim Streater]

On 23 Sep 2013 at 04:50, pisey phon  wrote: 

> I got an error "Call to undefined method SQLite3Result::numRows()". and here
> is my code:
> $db = new Sqlite3("sample.db");
> $result = $db->query("select * from table");
> $rows = $result->numRows();

Sorry, ignore my last mail. DO this:

$resl = $db->query ("select * from table");
$regl = $resl->fetchAll (PDO::FETCH_ASSOC);
$numl = count ($regl);  // $numl has number of rows


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

Re: [sqlite] Problem with method numRows() in Sqlite3

[Tim Streater]

On 23 Sep 2013 at 04:50, pisey phon  wrote: 

> I got an error "Call to undefined method SQLite3Result::numRows()". and here
> is my code:
> $db = new Sqlite3("sample.db");
> $result = $db->query("select * from table");
> $rows = $result->numRows();

Don't do that. Do:

$rows = count ($result);

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

Re: [sqlite] A graphical tool to handle sqlite schema change(more than ALTER TABLE)

[Tim Streater]

On 18 Sep 2013 at 01:49, niubao  wrote: 

> Is there a tool that allows you to graphically change sqlite schema as simple
> as editing a spreadsheet? For example if I wanted to change a column name
> from "my_driving_hours" to "driving_time", instead of writing a line of
> code, I can just click on that column and type in the new name, letting the
> tool automatically create a new database with the new schema and migrate the
> old data into the new one. I found it very useful.
>
> I am not sure if there exists such a tool after a long google search. Could
> any pros give some thoughts? Many thanks.

Navicat for SQLite Lite appears to do this. I copied a db, and used it to 
rename a table. I then used the sqlite command tool on the db and entered 
.schema which showed that the table name was changed. The following had taken 
place:

Before:

  create table mytab1 ( … );
  create table mytab2 ( … );

After:

  create table mytab1 ( … );
  create table "wiggy" ( … );

Note the quotes around the altered name. What it's doing internally I don't 
know, and whether it's actually doing everything it would need to do to do the 
job properly, I don't know either. I didn't do extensive testing, but entering:

  select * from wiggy;

at the command line produced the expected results.

This was Navicat for SQLite Lite 9.1.5 under OS X.

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

Re: [sqlite] Question about date & time

[Tim Streater]

On 15 Sep 2013 at 18:13, William Drago  wrote: 

> All,
>
> Should I put date and time in separate columns if I want to
> select by time?
>
> For example:
>
> SELECT * FROM testresults WHERE (status != "Pass") AND
> (23:00 <= testtime) AND (testtime <= 01:00).
>
> I have been reading the documentation, but it just isn't
> clear to me how I should handle this.

I convert everything to seconds since the epoch and have a column with that. 
All comparisons are done against that value. This is not too difficult in PHP.



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

Re: [sqlite] Hints for the query planner

[Tim Streater]

On 10 Sep 2013 at 20:26, Richard Hipp  wrote:

> SURVEY QUESTION:
>
> The question for today is what to call this magic hint function:
>
> (1)  unlikely(EXPR)
> (2)  selective(EXPR)
> (3)  seldom(EXPR)
> (4)  seldom_true(EXPR)
> (5)  usually_not_true(EXPR)
>
> Please feel free to suggest other names if you think of any.

likelihood (EXPR, value)




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

Re: [sqlite] SQLite and integer division 1/2=0

[Tim Streater]

On 12 May 2013 at 19:15, Paul van Helden  wrote: 

> I can live with SELECT 1/2 vs SELECT 1.0/2. The problem is that there is no
> way to specify a float when you insert into a NUMERIC. 1.0 turns into an
> integer. Then you do a division on all rows with an SQL select and you get
> mixed results because some rows have floats and some rows have integers. In
> C, 1/2=0. In Pascal 1/2=0.5. Oracle/MSSQL/others act like C, MySQL acts
> like Pascal. This is not my main issue. Consistency throughout a table, is.

You need to read the documentation, seems to me. Here:

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

in particular, where it seems quite clear to me that what you need is to define 
your columns as REAL rather than NUMERIC. Then your data will always be of type 
REAL.

Note that there is no NUMERIC type, only a NUMERIC affinity. Your data will by 
default have NUMERIC affinity if you try to give it type NUMERIC, but that's 
just a coincidence, far as I can tell.

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

Re: [sqlite] Problem getting utf-8 text out of a text column

[Tim Streater]

On 21 Mar 2013 at 23:30, Richard Hipp <d...@sqlite.org> wrote: 

> On Thu, Mar 21, 2013 at 6:52 PM, Tim Streater <t...@clothears.org.uk> wrote:
>
>> I have a column defined as TEXT and I insert utf-8 text into it. In
>> particular, there are byte sequences that might look as follows:
>> ... 74 6F 20 C2 A3 32 35 30 ... (in hex)
>> which represents:
>> ... to £250 ...
>> I used Navicat for SQLite Lite to peer at the data as hex and text in the
>> column, and it looks as I've presented it.
>> The problem comes when I try to retrieve the data using a PHP script. I
>> want the data back as bytes because I'm going to encode it as quoted
>> printable, with decimal byte values greater than 127 being separately
>> encoded. This would give the above as:
>> ... to =C2=A3250 ...
>> But: the byte stream I get back on doing a SELECT is always *missing* the
>> C2 byte. What am I missing here? I'm using the PHP PDO interface.

> What do you get back when you do select "hex(column)" instead of just
> "column" using PHP.  If the C2 byte is there, then I'm going to put the
> blame on PHP in this case.

Having checked the db carefully, both using hex(col) (via the sqlite3 CLI 
program), and using the unix hexdump utility on the db, I'm now reasonably 
certain that the C2 bytes are simply not in the db. That they show up when I 
examine the db with Navicat for SQLite Lite is another matter altogether.

Sorry for the distraction.

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

Re: [sqlite] Problem getting utf-8 text out of a text column

[Tim Streater]

Simon,

Thanks for the suggestions. I should have mentioned that I'm running under OS X 
so I have the sqlite3 application and can easily do the tests you suggest. But 
that'll have to be tomorrow - time for shuteye now :-)

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

[sqlite] Problem getting utf-8 text out of a text column

[Tim Streater]

I have a column defined as TEXT and I insert utf-8 text into it. In particular, 
there are byte sequences that might look as follows:
... 74 6F 20 C2 A3 32 35 30 ... (in hex)
which represents:
... to £250 ...
I used Navicat for SQLite Lite to peer at the data as hex and text in the 
column, and it looks as I've presented it.
The problem comes when I try to retrieve the data using a PHP script. I want 
the data back as bytes because I'm going to encode it as quoted printable, with 
decimal byte values greater than 127 being separately encoded. This would give 
the above as:
... to =C2=A3250 ...
But: the byte stream I get back on doing a SELECT is always *missing* the C2 
byte. What am I missing here? I'm using the PHP PDO interface.
Thanks,
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Full covering index without table

[Tim Streater]

On 05 Mar 2013 at 15:05, Richard Hipp  wrote: 

> Both implementations allow for reading just the prefix of the content blob
> in order to access earlier fields of a table, leaving the tail of the blob
> unread on disk.  So in all cases, it pays to put your frequently accessed
> small fields early in your table, and your infrequently accessed
> multi-megabyte BLOB columns at the end of the table.  That way you won't
> have to read over a multi-megabyte BLOB just to get at the BOOLEAN value at
> the end.

This was interesting to read, and may result in me reordering some tables I 
have. But suppose one of my fields early in the tables is an integer whose 
value, so far, fits in 16 bits (say). What happens if a value in one row grows 
to require 24 or 32 bits to represent. Does that column get moved to the end of 
the row, past my large blobs?

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

Re: [sqlite] SQLite on a Mac: PHP doesn't know about :-(

[Tim Streater]

On 22 Feb 2013 at 20:51, Didier Morandi  wrote: 

> very reason why noone (but Geeks) will ever move from VBScript to
> PowerShell. Richard, I will not start learning OOP at 62 to be able to
> use SQLite. Sorry for that. I'll stick to MySQL and good old
> procedural PHP.

Not only do you not need to install anything at all on your Mac in order to use 
SQLite/PHP/apache/ajax, but you don't need to do anything OOP in PHP in order 
to use SQLite. I do a bit of OOP here and there but by and large not.



--
Cheers  --  Tim (older than you are)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Native in PHP in the future?

[Tim Streater]

On 13 Jan 2013 at 10:17, Tracy Rohan  wrote: 

> Hi, I read that SQLite may not be native in PHP in the future?  When I say
> native, I mean that platform/devices will need to install SQLite as an
> extension before it will function (as it was previously).  Is this true?

Why not ask on the PHP Users' List? (f'rinstance).



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

Re: [sqlite] sqlite3.dll missing, HELP please :)

[Tim Streater]

On 06 Jan 2013 at 22:41, Simon Slavin <slav...@bigfraud.org> wrote: 

> On 6 Jan 2013, at 7:39pm, Tim Streater <t...@clothears.org.uk> wrote:
>
>> On 06 Jan 2013 at 17:11, Simon Slavin <slav...@bigfraud.org> wrote:
>>
>>> On 6 Jan 2013, at 5:09pm, Richard Hipp <d...@sqlite.org> wrote:
>>>
>>>> I am a Windows VISTA Home Basic 2007 user who needs some help, please.
>>>> Every time I turn on my PC, Windows tells me the SQLITE3.DLL file is
>>>> missing.
>>>
>>> If you have iTunes or QuickTime installed, please reinstall it.
>>
>> Err, there's no point in telling *us* this, is there?
>
> If he posts that it works, then its an answer to a FAQ on this list and other
> people can post that answer in future.  The fact that the question shouldn't
> really be asked on this list is irrelevant.

In general yes, but AIUI, Richard just forwarded someone's mail to him, to this 
list. Therefore the OP won't see your reply.

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

Re: [sqlite] sqlite3.dll missing, HELP please :)

[Tim Streater]

On 06 Jan 2013 at 17:11, Simon Slavin  wrote: 

> On 6 Jan 2013, at 5:09pm, Richard Hipp  wrote:
>
>> I am a Windows VISTA Home Basic 2007 user who needs some help, please.
>> Every time I turn on my PC, Windows tells me the SQLITE3.DLL file is
>> missing.
>
> If you have iTunes or QuickTime installed, please reinstall it.

Err, there's no point in telling *us* this, is there?

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

Re: [sqlite] SQLite logo usage

[Tim Streater]

On 10 Dec 2012 at 14:40, Simon Slavin  wrote: 

> On 10 Dec 2012, at 2:38pm, Richard Hipp  wrote:
>
>> The "SQLite" name and the new SQLite Logo are registered trademarks.
>
> Let's ask it a different way.  If I write something and want to list SQLite as
> one of the technologies in it, how would you like me to list SQLite ?  Is
> there any small image to go with it which will encourage brand recognition ?

And in my case I want to document my app and list SQLite in an appendix.

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

Re: [sqlite] String not valid DateTime

[Tim Streater]

On 10 Dec 2012 at 14:05, William Drago  wrote:

> I am using SQLite with C# and am having trouble with SQLite
> DATETIME types. The following error occurs when trying to
> read rows from a table that contains dates (e.g. "12/09/2012
> 22:51:24"). (I am using a SQLiteDataReader to put query
> results into a C# DataTable.)
>
> "String was not recognized as a valid DateTime."
>
> Apparently SQLite stores dates as strings while C# is
> expecting an object of some sort. Any thoughts or solutions?
> As a work-around I changed all my DATETIME types to VARCHARs.

Suggest the OP reads this also:

http://sqlite.org/datatype3.html

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

Re: [sqlite] SQLite converts all unicode characters into ANSI

[Tim Streater]

On 19 Nov 2012 at 01:57, ZikO  wrote: 

> The script looks like this:
> CREATE TABLE IF NOT EXISTS imiona (
>   id INTEGER PRIMARY KEY,
>   data TEXT,
>   imie1 TEXT,
>   imie2 TEXT,
>   imie3 TEXT);
>
> INSERT INTO imiona (data,imie1,imie2,imie3) VALUES
> ('01/01/2012','Masława','Mieczysława','Mieszka'),
> ('16/01/2012','Marcelego','Walerii','Włodzimierza'),
> ('17/09/2012','Franciszka','Lamberty','Narcyza');

This works exactly as it should on my Mac, both inputting and displaying, so 
it's not sqlite.

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

Re: [sqlite] Right way to store binary data into a blob

[Tim Streater]

On 03 Oct 2012 at 10:50, Clemens Ladisch <clem...@ladisch.de> wrote:

> Tim Streater wrote:

>> What's the right way to get the data into the column?
>
> 1) Use SQLite blob literals, which are hex strings with an "x" prefix:
>
> ... set imagedata = x'ffd8ffe000104a46...' where ...
>
> 2) Use parameters (which is recommended in any case to avoid both
>   formatting and SQL injection problems):
>
> $stmt = $dbh->prepare('update imagetable set imagedata = ? where linkid =
> ?');
> $stmt->bindValue(1, $imgdata, PDO::PARAM_LOB);
> $stmt->bindValue(2, $linkid, PDO::PARAM_INT);
> $stmt->execute();
>
>   See also <http://php.net/manual/en/pdo.lobs.php>.

Thanks, and to Simon too. I did (2) above and the references pointed me at the 
right part of the doc to do the inverse. So I've now got a nice couple of 
blobRead and blobWrite functions that are nicely integrated with my error 
handling, too.

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

[sqlite] Right way to store binary data into a blob

[Tim Streater]

I've got a temporary database with a blob column. I'm using the PHP PDO 
interface, and I'd like to store some binary data into the blob; it's actually 
an image. Later I'm going to read it back and write it to disk. What's the 
right way to get the data into the column? At the moment I'm using str_replace 
to change any single-quote to two single-quotes, and then doing as follows:

  $dbh->query ("update imagetable set imagedata='" . $bd . "' where linkid=" . 
$linkid);

but this is giving me:

  SQLite error: general code: HY000 error: 1, unrecognized token: "'/�Exif"

Seems to me my approach is wrong.

Thanks,

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

Re: [sqlite] sqlite Unofficial poll

[Tim Streater]

On 23 Sep 2012 at 11:37, Baruch Burstein  wrote:

> I am curious about the usefulness of sqlite's "unique" type handling, and
> so would like to know if anyone has ever actually found any practical use
> for it/used it in some project? I am referring to the typeless handling,
> e.g. storing strings in integer columns etc., not to the non-truncating
> system e.g. storing any size number or any length string (which is
> obviously very useful in many cases).
> Has anyone ever actually taken advantage of this feature? In what case?

It's hard to say whether I use this feature or not. It's just one less thing to 
worry about. It means I don't have to have extra function calls to be sure that 
some value I'm about to shove into a field has the right type. Since I'm doing 
it from PHP, half the time I don't know anyway whether something is a string or 
an integer. The more one can get the Giant Brain to concern itself with this 
type of thing, so I don't have to, the better. Types - pah! Who needs 'em?

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

Re: [sqlite] sqlite select max(field1), field2 from table1

[Tim Streater]

On 12 Sep 2012 at 11:37, Richard Hipp  wrote: 

> On Wed, Sep 12, 2012 at 6:15 AM, Bart Smissaert
> wrote:
>
>> Had a look at the new option as in the SQL above.
>> Noticed it will only return one record, even if there are more records
>> where
>> field1 equals max(field1).
>> I suppose it returns the first record it finds where field1 = max(field1).
>> Is this indeed how it works?

> Yes.

Is there any means of influencing which record would be returned in this 
circumstance, such as by an ORDER BY? Or does that merely order the (one) 
returned record :-)

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

Re: [sqlite] How to build a new sqlite3.dylib?

[Tim Streater]

On 06 Aug 2012 at 22:49, Simon Slavin  wrote: 

> On 6 Aug 2012, at 10:29pm, Tobias Giesen  wrote:
>
>> would you be willing to share your dylib with me? I don't know how to
>> do this with Xcode.
>
> Do you absolutely need to use a dynamic library ?  The recommendation from the
> SQLite team is that people build sqlite3 into their application  by including
> the .c and .h files, rather than call an external library.

I'm inclined to agree with this. I did it largely as an exercise in the use of 
Xcode.

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

Re: [sqlite] How to build a new sqlite3.dylib?

[Tim Streater]

On 04 Aug 2012 at 11:54, Tobias Giesen  wrote: 

> I was able to compile the sqlite3 shell, but how to create the latest
> dylib, or where can I download it?

I use xcode for this purpose, using the amalgamation.

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

Re: [sqlite] How to compile and load the example fts4 rank function?

[Tim Streater]

On 21 Jul 2012 at 17:51, Pavel Ivanov  wrote: 

> On Sat, Jul 21, 2012 at 3:36 AM, AJ ONeal  wrote:
>> I naively tried
>>
>> wget
>> https://raw.github.com/gist/3154964/d570955d45580c095c99de6eb0c378395d4b076d/
>> sqlite3-fts4-rank.c
>> gcc -c sqlite3-fts4-rank.c -o sqlite3-fts4-rank.o
>>
>> sqlite3
>> .load sqlite3-fts4-rank.o
>>
>> But that didn't work.
>>
>> Can I get a link to the docs on this? I don't think I was using the right
>> search terms to find it.
>
> You cannot load an object file, you should load a shared library (*.so
> on Linux).

If it's OS X then I'd suggest making an Xcode project to create a dynamic 
library (.dylib) and try loading that. (I expect you can do that directly 
without needing Xcode but I've no idea how).

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

Re: [sqlite] EXT :Re: Can't create empty database

[Tim Streater]

On 25 Jun 2012 at 14:30, Niall O'Reilly  wrote: 

> On 25 Jun 2012, at 13:24, Black, Michael (IS) wrote:
>
>> Does the shell compile differently for Mac?
>
>   Sorry.  I've no idea whether it does.
>   SQLite comes bundled with OSX and I haven't had a need to build it from
> source.
>   Besides, I haven't needed either to upgrade (?) to current OSX.  You
> mentioned
>   a later version of SQLite than the one I have.  I guess that has 
> something to
>   do with the divergence in behaviour.

I've built it from the amalgamation, but only because I wanted to fiddle in a 
trivial way with some of the output. But in my case I just shove all the code 
in an Xcode project and let Xcode figure out how to compile and build it. Seems 
to work - so I don't know what Michael means by "differently".

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

Re: [sqlite] Using a select with 'where'

[Tim Streater]

On 27 Apr 2012 at 22:16, Simon Slavin <slav...@bigfraud.org> wrote: 

> On 27 Apr 2012, at 9:00pm, Tim Streater <t...@clothears.org.uk> wrote:

>>  delete from addressbook where absid=(select personnick from grouplinks where
>> groupnick='27')
>>
>> The 'select personnick ...' can return zero, one, or many results, and I'd
>> like to have the 'delete from ...' delete zero, one, or many rows from the
>> addressbook table. How can I do that with a single statement in SQLite, or is
>> it not possible?
>
> The sub-SELECT evaluates to a list, not an individual number.  And 'absid'
> will never equal a list.  You probably mean
>
> DELETE FROM addressbook WHERE absid IN (SELECT personnick FROM grouplinks
> WHERE groupnick = '27')
>
> or something like that.  The syntax tree for DELETE can be found here:
>
> <http://www.sqlite.org/lang_delete.html>

Simon,

Yes. I confess to being a mere hacker where SQL itself is concerned. I should 
really have looked up the syntax for expr:

<http://www.sqlite.org/lang_expr.html>



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

Re: [sqlite] Using a select with 'where'

[Tim Streater]

On 27 Apr 2012 at 21:03, Stephan Beal <sgb...@googlemail.com> wrote: 

> On Fri, Apr 27, 2012 at 10:00 PM, Tim Streater <t...@clothears.org.uk> wrote:
>
>>  delete from addressbook where absid=(select personnick from grouplinks
>> where groupnick='27')
>
> i think what you want is IN instead of =.

Stephan,

Yes indeed - thanks!

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

[sqlite] Using a select with 'where'

[Tim Streater]

I'd like to delete multiple rows using the following syntax, but experiment 
appears to show that at most one row is deleted. I tried the following:

  delete from addressbook where absid=(select personnick from grouplinks where 
groupnick='27')

The 'select personnick ...' can return zero, one, or many results, and I'd like 
to have the 'delete from ...' delete zero, one, or many rows from the 
addressbook table. How can I do that with a single statement in SQLite, or is 
it not possible?

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

Re: [sqlite] sqlite3 column widths

[Tim Streater]

On 25 Apr 2012 at 17:36, Pete  wrote: 

> So I guess that's a "No" then?
>
> I'm fully aware of how sqlite3 treats datatypes and column width
> specifications, but surely if someone defines a column with a specific max
> width, it's reasonable to assume that's the max width they want, otherwise
> why bother defining it?  That would seem to be a lot less arbitrary than
> the current logic for determining the default column width.

If you're talking about the CLI program called "sqlite3", then as has been 
stated, it has no information pertaining to how wide you want the column to be. 
As I understand it, classical SQL (if that's the term) allowed you in your 
table definition to define and thereby limit a column width. Personally I see 
no merit in that and am glad that SQLite doesn't enforce it, just as PHP, for 
example, doesn't expect you to pre-define the length of a string. I suppose 
SQLite allows you to give a width but then ignores it, as is well documented.

Nothing to stop you adding some code to the sqlite3 CLI program to have extra 
commands, allowing you to define column widths for display purposes. E.g:

sqlite3> .colwidth x 27

where x is the name of a column in some table.

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

Re: [sqlite] make sqlite3_analyzer No rule to make target `sqlite3_analyzer'. Stop.

[Tim Streater]

On 30 Mar 2012 at 04:11, vaiStardom  wrote: 

> I can't seem to use the sqlite3_analyzer on my MacBook Pro 2010 i7.
>
> Following the instructions in the book 'The Definitive Guide To Sqlite', to
> analyze the test.db all I have to do is issue the command 'sqlite3_analyzer
> test.db'. This command unfortunately gives me the response '-bash:
> sqlite3_analyzer: command not found'.
>
> The EXE sqlite3_analyzer is in the same directory as test.db, it was
> downloaded from sqlite.org:   'Precompiled Binaries For Mac OS X (x86)
> sqlite-analyzer-osx-x86-3071100.zip'.
>
> I downloaded the source code and untared it with 'tar -xzvf
> sqlite-autoconf-3071100.tar'.

Why not download the precompiled binary?

> I moved to the new directory and issued the command 'make sqlite3_analyzer',
> which gives me the output 'make: *** No rule to make target
> `sqlite3_analyzer'.  Stop.'.

I just downloaded the precompiled binary zip file to my Desktop, double-clicked 
it to unzip, and in a Terminal window did:

Second-Mini% cd ~/Desktop 
Second-Mini% sqlite3_analyzer 
Usage: sqlite3_analyzer database-name
Second-Mini%


Simples. Did you have a particular reason to want to rebuild it from source?

BTW, when I build the sqlite3 executable or this analyser or a cross-assembler 
for the 68000, I use Xcode - a lot easier than fiddling with make, IME.

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

Re: [sqlite] Manual

[Tim Streater]

On 03 Mar 2012 at 15:57, Mark Schonewille  
wrote: 

> Could you just tell me where I find a good on-line manual? Thanks.

I'm not aware of any similar to the mysql one - but perhaps I haven't looked 
particularly. But you didn't answer my question about the diagrams, or indeed 
whether my short example clarified matters.

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

Re: [sqlite] Manual

[Tim Streater]

On 03 Mar 2012 at 15:15, Mark Schonewille  
wrote: 

> MySQL has a really great manual with extensive explanations and dozens of
> examples for each command. SQLite is none of all this. It just has a limited
> number of pages listing a small number of commands and some special features.
> It also has a number of really weird diagrammes.
>
> How do I read diagrammes like http://sqlite.org/images/syntax/sql-stmt.gif ?

Do you mean that you find all the diagrams incomprehensible, or just some? Take 
this one:

   http://www.sqlite.org/lang_expr.html#litvalue

as an example. It is a pictorial way of describing how to form an SQLite-legal 
numeric value. Following the pathways in the diagram, one can see that 4, 
12.8e-7, 0.6, are all legal numerics. Interestingly, +3 and -66 appear not to 
be.

> Where can I find a good reference manual just like MySQL's?

There are books available, some even written by denizens of this list, such as 
"Using SQLite" by Jay Kreibich.

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

Re: [sqlite] sqlite3_step getting core dumbed.

[Tim Streater]

On 10 Feb 2012 at 07:55, bhaskarReddy  wrote: 

> PRAGMA table_info(yourtablename); will display  colNumber, colName, colType,
> 
> ex: 0|slotId|INTEGER|0||0
> 1|ponChannelId|INTEGER|0||0
> 2|onuType|INTEGER|0||0
> 3|onuSerialNumber|TEXT|0||0
> 4|onuId|INTEGER|0||0
> 5|plannedSwVersion|TEXT|0||0
> 6|adminStatus|INTEGER|0||0
>
> In that how can we extract INTEGER.

I expect that in the result set, third column (index 2) says INTEGER.

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

Re: [sqlite] Query on Sqlite3 in an Android app

[Tim Streater]

On 09 Feb 2012 at 15:40, Lavanya Ramanan  wrote: 

> And I would also like to know what GUI do people generally use for sqlite
> database.

Navicat for SQLite Lite, although I rarely need to use it. Very occasionally I 
want to see the hex of some database column.

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

Re: [sqlite] ALTER TABLE

[Tim Streater]

On 06 Feb 2012 at 19:31, Ryan Johnson  wrote: 

> On 06/02/2012 1:59 PM, Bill McCormick wrote:

>> The order is not important. What is important is that I come up with
>> some way to manage version updates. I've tried doing something similar
>> in the past using an "alter tables" script (using a different DB). The
>> script assumed some base version of schema was present, and then
>> proceeded adding new schema if it didn't already exist. It probably
>> seemed like a good idea at the time (to whomever started it), but as
>> time went on this script grew more and more unmanageable and I dreaded
>> having to use it.

> You might exploit #pragma user_version to help you track future changes,
> though that wouldn't necessarily help with the existing mess.

Can that be relied upon, though? The doc explicitly states: "Specific pragma 
statements may be removed and others added in future releases of SQLite. There 
is no guarantee of backwards compatibility".

I keep my own version number in a master table and use that to indicate that a 
table needs updating.

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

Re: [sqlite] Is there any API for counting number of rows in a particular table.

[Tim Streater]

On 30 Jan 2012 at 21:58, Bart Smissaert  wrote: 

> OK, so how you open those then with SQLiteRoot?

I use PHP, thus:

 $dbh = new PDO ("sqlite:" . $db);

where $db is a string like "/path/to/database".

I don't know what SQLiteRoot is although possibly I should do.

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

Re: [sqlite] Is there any API for counting number of rows in a particular table.

[Tim Streater]

On 30 Jan 2012 at 16:37, Bart Smissaert  wrote: 

> How do you make it open database files that have an extension other than .slt?

Eh? None of my SQLite databases has any extension at all.

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

Re: [sqlite] Incompatible versions of SQLite on same system

[Tim Streater]

On 24 Jan 2012 at 20:02, Joe Winograd  wrote: 

> Thanks for the idea, but it will not install. The way this group operates 
> with excessive trimming/snipping ...

No it doesn't. It doesn't do *enough* trimming and snipping, and as a result 
our inboxes grow exponentially. If I want to read a thread I can sort by 
subject and then read it through. But this is made harder by the excessive 
repetition due to inadequate trimming (particularly of .sigs).

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

Re: [sqlite] insert image into db - windows batch

[Tim Streater]

On 18 Jan 2012 at 15:24, Petr Lázňovský  wrote: 

>> Sorry, 'windows batch' doesn't mean anything to me.
>
> http://en.wikipedia.org/wiki/Batch_file
>
> but wikipedia is reasonlessly turned off today
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


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

Re: [sqlite] insert image into db - windows batch

[Tim Streater]

On 18 Jan 2012 at 15:24, Petr Lázňovský  wrote: 

>> Sorry, 'windows batch' doesn't mean anything to me.
>
> http://en.wikipedia.org/wiki/Batch_file
>
> but wikipedia is reasonlessly turned off today

Looks like you can get round this (at least on OS X / Safari 5.1) by pressing 
the escape key while the page is loading.

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

Re: [sqlite] sql/tcl script

[Tim Streater]

On 16 Jan 2012 at 18:15, Stephan Beal  wrote: 

> On Mon, Jan 16, 2012 at 7:12 PM, Bill McCormick wrote:
>
>> Is tcl the only scripting interface for SQLite? I'm just wondering what
>> the options are.

> There are few scripting languages which don't have an sqlite3 binding. Just
> to name a few:
>
> - Perl
> - PHP

I'm using the PDO interface from PHP for SQLite3 with no problems. What are you 
referring to?

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

Re: [sqlite] makefile for c

[Tim Streater]

On 15 Jan 2012 at 20:44, Bill McCormick  wrote: 

> What is the problem with the shared lib stuff?
>
> Thanks!!
> Black, Michael (IS) wrote, On 1/15/2012 2:27 PM:
>> A simple one -- and please compile sqlite3.c into your program and make
>> everybody happy.
>>
>> Forget the shared library stuff as we have just been talking about.

The problem is that the computer vendor installs a shared lib with a version of 
the library. Some 3rd party app installer then replaced that shared lib with 
another version, and existing apps don't like it.

SQLite is small enough that it can be compiled and linked in in its entirety.

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

Re: [sqlite] command line to get number of tables in sqlite

[Tim Streater]

On 21 Dec 2011 at 18:32, smallboat  wrote: 

> I have a sqlite file. I would like to open it and know how many tables in it.
>
> What is the command line to open a sqlite file and get to know how many tables
> in it? Thanks.

See:

http://www.sqlite.org/sqlite.html

and:

http://www.sqlite.org/faq.html#q7

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

[sqlite] Quickest way to get an answer

[Tim Streater]

My db has a column called "status". This can take one of 7 or so integer values 
from 0 to 7 or so. There are times when I need a quick answer to this question: 
are there any rows in the db for which status has value 0. I don't need to know 
how many, just whether there are any or not. What's the least expensive form of 
making this query?

So far I've tried:

  select count(status) from mytable where status=0;
  select count(status) from mytable where status=0 limit 1;
  select status from mytable where status=0 limit 1;

When doing this a number of times I see some seconds of CPU being taken; I 
haven't yet pinned it down to being an SQLite problem - I'm about to do some 
timings to see where the time is going. I've added an index:

  create index stat on mytable (status asc);

and using the third form above together with an index seems to improve matters 
a bit.

If all the forms above are roughly equivalent that would be helpful to know. 
There are 3000 or so rows in the table.

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

Re: [sqlite] WAL mode is reliable?

[Tim Streater]

On 03 Nov 2011 at 14:02, Simon Slavin  wrote: 

> On 3 Nov 2011, at 1:38pm, Paxdo Presse wrote:
>
>> How did you get concurrent connections? (about)
>
> This is related specifically to the web language I use (PHP) so it won't help
> you if you're using something else.
>
> Actually my system works like this: the web pages themselves are '.html' files
> do most of their work in JavaScript.  When they want to talk to the database
> they use XMLHTTPRequest to call other utility '.php' files which have the job
> of just a single query or execution.  Though they actually talk JSON to
> one-another, not XML.

This is pretty much what I do too. Except I don't even use JSON; I invented my 
own.

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

Re: [sqlite] Unique id

[Tim Streater]

On 27 Oct 2011 at 22:04, Kees Nuyt <k.n...@zonnet.nl> wrote: 

> On Thu, 27 Oct 2011 21:47:17 +0100, Simon Slavin
> <slav...@bigfraud.org> wrote:
>
>>
>> On 27 Oct 2011, at 8:12pm, Tim Streater wrote:
>>
>>> Is there a way to get a unique id from a database
>>> without actually creating a new row?
>>
>> Sure.  Do something like
>>
>> SELECT max(id) FROM mytab;
>>
>> Then in your own code, add 1 to it and use that as
>> the "id" for the row you're about to save.
>> Make sure you handle the NULL case (where mytab
>> doesn't have any rows in it yet) correctly.
>
> And wrap it all in an IMMEDIATE or EXCLUSIVE transaction, or
> you'll get a race condition if some other process tries to do the
> same at the same time.
> All processes have to behave, if there's one that does the SELECT
> above in uncommitted mode and proceeds to use it, you have a
> problem.
> So, better use AUTOINCREMENT.

Thanks for the various responses. I hadn't thought of the max(id) approach but 
I'm not sure I can put a BEGIN/COMMIT around everything, as there are network 
exchanges involved there too. Nothing at the SQLite web site gave me a better 
clue than my own approach, either, but it doesn't look like there's anything 
obvious I overlooked.

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

[sqlite] Unique id

[Tim Streater]

Is there a way to get a unique id from a database without actually creating a 
new row? If I have:

create table mytab (i integer primary key, j);

then I could do (pseudo-code):

insert into mytab (i) values (null);
x = result of (select last_insert_rowid() from mytab;);

for  (n=0;  n

Re: [sqlite] complete sqlite software

[Tim Streater]

On 22 Oct 2011 at 18:25, saeed ahmed  wrote: 

> i want a software,something like microsoft's Access but no microsoft.a
> software that can be used for making tables,queries and reports.

Try Navicat for SQLite Lite (free).

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

Re: [sqlite] DOMAIN new error code

[Tim Streater]

On 17 Oct 2011 at 10:00, Jean-Christophe Deschamps  wrote: 

> At least it would gives a fairly good hint as to what to look for and
> where to look.  You know that some extension function was passed an
> out-of-range argument during the course of the last operation.  From
> there, tracking down the culprit is much easier.
>
> Anyway the issue to solve is not "which library issued it" but "what
> extension function in the few last statements could have got invalid
> argument[s]".  The current situation is way too vague and leaves you
> dry about the cause.

With 350 calls in my code to query and exec, this is why I have a wrapper 
around them to include a location code, so that error logging can tell me 
straight away which call had the error and what was the SQL statement it was 
trying.

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

Re: [sqlite] Malformed database error when using FTS3/4

[Tim Streater]

On 13 Oct 2011 at 16:59, Filip Navara  wrote: 

> Reproduced on Windows, SQLite 3.7.8.

Seems OK here, OS X 10.7.2:

Second-Mini% sqlite3 wiggy
-- Loading resources from /Users/tim/.sqliterc
SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE VIRTUAL TABLE fts USING fts3( tags TEXT);
sqlite> INSERT INTO fts (tags) VALUES ('tag1');
sqlite> SELECT * FROM fts WHERE tags MATCH 'tag1';
tags  
--
tag1  
sqlite> ^D
Second-Mini% sqlite3 wiggy
-- Loading resources from /Users/tim/.sqliterc
SQLite version 3.7.5
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> UPDATE fts SET tags = 'tag1' WHERE rowid = 1;
sqlite> SELECT * FROM fts WHERE tags MATCH 'tag1';
tags  
--
tag1  
sqlite> 


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

[sqlite] Multiple statements

[Tim Streater]

Looking at the description text for sqlite3_exec (SQLite C interface), I see 
this text:

"The sqlite3_exec() interface runs zero or more UTF-8 encoded, 
semicolon-separate SQL statements passed into its 2nd argument, in the context 
of the database connection passed in as its 1st argument."

Now, Simon said that the PHP SQLite3 interface (which I would like to use), is 
a thin wrapper around the SQLite C interface. Can I conclude, then, that such 
multiple statements would be properly executed if presented via the PHP 
interface? What would happen if such a string contained more than one SELECT - 
or if the SELECT is not the first statement?

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

Re: [sqlite] Statement failing

[Tim Streater]

On 09 Oct 2011 at 17:00, Simon Slavin <slav...@bigfraud.org> wrote: 

> On 9 Oct 2011, at 4:52pm, Tim Streater wrote:
>
>> At present, I'm using PDO and setting it to throw exceptions. So I have a
>> try/catch around all my $dbh->query and in there, log what happened and
>> where, report to the user and then give up. I haven't looked closely at the
>> SQLite3 interface in PHP but it wasn't obvious whether I can use the same
>> exception mechanism or not. I'll have to see how to incorporate this in my
>> app but for now I'm giving up on multiple statements in one call to the
>> interface.
>
> When you move from toy software to professional software, it becomes all about
> the error-handling.

Quite. Which is why a data-aquisition package I wrote some 20 years ago in C 
typically ran for 11 months unattended at a time (only the annual site-wide 
power-outage took it down).

But closer investigation of the SQLite3 interface does not reveal an equivalent 
to the FetchAll method available under PDO. The salient point here is that I 
need to know the number of rows in the result set before I start processing it. 
So I guess I'm sticking with PDO. :-)

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

Re: [sqlite] Statement failing

[Tim Streater]

On 09 Oct 2011 at 02:02, Simon Slavin <slav...@bigfraud.org> wrote: 

> On 9 Oct 2011, at 1:39am, Tim Streater wrote:
>
>> On 08 Oct 2011 at 23:32, Simon Slavin <slav...@bigfraud.org> wrote:
>>
>>> I'm not clear whether you're using the PDO or the sqlite3 extension to PHP.
>>
>> By the way, is one to be preferred over the other?
>
> That is a great question, and I'd love to see answers from PHP programmers.
>
> My contribution: the thing the PDO API has going for it is that it makes
> changing from one SQL engine to another simple.  If you develop on a
> stand-alone computer for a big multi-server network, this is an advantage.

That won't apply in my case.

> On the other hand, the biggest advantage of using the SQLite3 API is that it's
> such a thin wrapper around the SQLite C API.  For someone who already knows
> SQLite it's very easy to pick up.  But that's only an advantage for
> experienced SQLite users, or those who want to be.

Having it be a thin wrapper suits me.

> I have had trouble using the PDO API in situations that demand proper
> error-handling.  If you have a situation where something either works or
> doesn't you're fine.  If you have to understand exactly what error you got, in
> order to handle several different situations, you pretty-much have to simulate
> all your error conditions and see what happens to write your program.  The
> advantage of the SQLite3 interface here is that it perfectly reflects the
> documentation for the SQLite3 C API, so you can probably figure out what to do
> just by reading the SQLite C documentation.

At present, I'm using PDO and setting it to throw exceptions. So I have a 
try/catch around all my $dbh->query and in there, log what happened and where, 
report to the user and then give up. I haven't looked closely at the SQLite3 
interface in PHP but it wasn't obvious whether I can use the same exception 
mechanism or not. I'll have to see how to incorporate this in my app but for 
now I'm giving up on multiple statements in one call to the interface.

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

Re: [sqlite] new user

[Tim Streater]

On 09 Oct 2011 at 10:40, saeed ahmed  wrote: 

> i am a new to sqlite. i want to know how can i make sqlite looking like
> microsoft access? similar working environment, like making tables, queries
> etc. actually i find it difficult to work in writing commands mode. any help?

Have you looked at Navicat for SQLite?

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

Re: [sqlite] Statement failing

[Tim Streater]

On 08 Oct 2011 at 23:32, Simon Slavin <slav...@bigfraud.org> wrote: 

> On 8 Oct 2011, at 10:37pm, Tim Streater wrote:
>
>> I have this string:
>>
>>   attach database ':memory:' as mem; create table mem.messages ( absid
>> integer, ..., replyto text );
>>
>> (where the ellipsis represents a number of other column declarations). In
>> PHP, I do this:
>>
>>   $dbh->query ($str);
>>
>> where $str contains the string from above.
>
> I'm not clear whether you're using the PDO or the sqlite3 extension to PHP.

By the way, is one to be preferred over the other? I did notice that the 
command line tool had no problem with being given multiple statements on a line 
and executing them sequentially (which was in fact what prompted me to try it 
in my app).

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

Re: [sqlite] Statement failing

[Tim Streater]

On 08 Oct 2011 at 23:32, Simon Slavin <slav...@bigfraud.org> wrote: 

> On 8 Oct 2011, at 10:37pm, Tim Streater wrote:
>
>> I have this string:
>>
>>   attach database ':memory:' as mem; create table mem.messages ( absid
>> integer, ..., replyto text );
>>
>> (where the ellipsis represents a number of other column declarations). In
>> PHP, I do this:
>>
>>   $dbh->query ($str);
>>
>> where $str contains the string from above.
>
> I'm not clear whether you're using the PDO or the sqlite3 extension to PHP.

I'm using PDO. And I've now knocked up a small test program allowing me to use 
either.

> But first I notice you're using ::query() and you should be using ::exec() .

OK I'll study these to see why :-)

> And second, the documentation says that both functions execute only one
> statement.  My guess is that they ignore everything after the ';' used as a
> statement separator.  But you should try it with ::exec() and find out for
> yourself.

Well, with my test program it looks like PDO does ignore anything after the 
semi-colon. The sqlite3 extension, by contrast, does appear to process after it.

> Refusing to process multiple SQL statements is probably going to be the best
> thing to do in today's security-sensitive times.  It acts against attempts to
> break your database using techniques like this:

Hmmm. There are places where I have a sequence of statements. I was trying to 
reduce overhead by combining them. But perhaps that's not possible, or, in the 
case of the sqlite3 extension, unreliable at best.

> <http://xkcd.com/327/>

I get a 404.

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

[sqlite] Statement failing

[Tim Streater]

I have this string:

   attach database ':memory:' as mem; create table mem.messages ( absid 
integer, ..., replyto text );

(where the ellipsis represents a number of other column declarations). In PHP, 
I do this:

   $dbh->query ($str);

where $str contains the string from above. It completes without apparent error, 
but any attempt to immediately access the table mem.messages (such as via 
insert into mem.messages ...) fails with: No such table mem.messages.

But:

If I take the string above and split it into two calls to $dbh->query - one to 
attach the memory database, the second to create the mem.messages table, then 
everything works as expected. Is there something I'm obviously doing wrong?

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

Re: [sqlite] Still chasing "database schema has changed"

[Tim Streater]

On 28 Sep 2011 at 00:25, Richard Hipp <d...@sqlite.org> wrote: 

> On Tue, Sep 27, 2011 at 7:16 PM, Tim Streater <t...@clothears.org.uk> wrote:
>
>> On 27 Sep 2011 at 18:15, Richard Hipp <d...@sqlite.org> wrote:
>>
>>> On Tue, Sep 27, 2011 at 1:13 PM, Tim Streater <t...@clothears.org.uk>
>> wrote:
>>>
>>>> The databases that get vacuumed tend to have a fair amount of traffic in
>>>> and out. So it's good to compress them from time to time.
>>>
>>> Really?  Have you actually measured this to see if it makes a difference?
>>> What happens if you never VACUUM?
>>
>> They'll get bigger and bigger. I imagine the users (if I ever have any)
>> would start complaining.

> You know that SQLite automatically reclaims and reuses space from rows you
> DELETE and tables you DROP, right?
>
> VACUUM repacks and defragments the database file.  The repacking might make
> the file a little smaller, but probably not that much.  Defragmenting might
> help performance, but again, probably not that much.  Hence I ask:  have you
> actually measured the difference?

No, I've done no tests. I'm not concerned that each database be down to its 
smallest possible size, merely that the app have a mechanism that, from time to 
time, compresses certain databases through which most of the apps traffic flows 
(so, plenty of rows being added and deleted).

I'm sure I'm doing the VACUUMing unnecessarily often, but for simplicity I 
simply put all the timer based housekeeping in one pot. I think now I'll 
arrange to do that one aspect much less frequently.

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

Re: [sqlite] Still chasing "database schema has changed"

[Tim Streater]

On 27 Sep 2011 at 18:15, Richard Hipp <d...@sqlite.org> wrote: 

> On Tue, Sep 27, 2011 at 1:13 PM, Tim Streater <t...@clothears.org.uk> wrote:
>
>> The databases that get vacuumed tend to have a fair amount of traffic in
>> and out. So it's good to compress them from time to time.
>
> Really?  Have you actually measured this to see if it makes a difference?
> What happens if you never VACUUM?

They'll get bigger and bigger. I imagine the users (if I ever have any) would 
start complaining.

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

Re: [sqlite] Still chasing "database schema has changed"

[Tim Streater]

On 27 Sep 2011 at 12:23, Simon Slavin <slav...@bigfraud.org> wrote: 

> On 27 Sep 2011, at 10:49am, Tim Streater wrote:
>
>> On 27 Sep 2011 at 00:19, Simon Slavin <slav...@bigfraud.org> wrote:
>>
>>> I believe that VACUUM is one of the statements which counts as changing the
>>> schema, because it does its work by rewriting entire tables and/or indexes.
>>> So don't do a VACUUM when you're doing multi-process access.  Cut out the
>>> VACUUMs and see whether you still get this result code.
>>
>> Ah, thanks, that's a good clue. I can do some work in that area to ensure
>> that the VACUUMs are done at a quiet moment.
>
> You might not need VACUUM at all.  I might use it just before I make a copy of
> the database file for transfer or archive, if it was important to me that the
> file was as small as possible.  But I have quite a few databases I've never
> bothered using VACUUM on at all.  Most of them shrink only by small amounts
> and probably start growing immediately afterwards anyway.

The databases that get vacuumed tend to have a fair amount of traffic in and 
out. So it's good to compress them from time to time. Not doing that when there 
is traffic has ended up being a couple of lines of PHP and a couple of lines of 
JavaScript, so if that avoids the issue in future I shall be well pleased.

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

Re: [sqlite] Still chasing "database schema has changed"

[Tim Streater]

On 27 Sep 2011 at 00:19, Simon Slavin <slav...@bigfraud.org> wrote: 

> On 26 Sep 2011, at 11:32pm, Tim Streater wrote:

>> But, very often the database on which the error occurs is :memory: - and I
>> guess each thread will have its own one of those.
>
> Are you doing concurrent access to your :memory: databases ?

Looking back through my logs to early July, there were three instances - and in 
each case a housekeeping script is doing a VACUUM. So that's a strong pointer. 
And in the case of :memory:, it may just appear (to my ill-informed eyes) to be 
related to :memory:. The statement is in fact:

  insert into mem.messages select * from main.messages where absid= some value;

Here, :memory: was attached as mem, and main was opened earlier but will be one 
of those that gets VACUUMed. I guess the select can then fail in the same way.

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

Re: [sqlite] Still chasing "database schema has changed"

[Tim Streater]

On 27 Sep 2011 at 00:19, Simon Slavin <slav...@bigfraud.org> wrote: 

> On 26 Sep 2011, at 11:32pm, Tim Streater wrote:
>
>> For the most part it's going to be SELECT, INSERT, UPDATE, BEGIN, COMMIT.
>> Less often, there's VACUUM. Let me have a look at my logs - OK, when I got
>> the error today, there was a thread doing a VACUUM on the db which got the
>> error (within a few seconds, in terms of the log timestamps). The SQL
>> statement being done when the error was reported was in fact:
>
>>  update uids set uid_date='1317028381',rec_date='1317002026' where
>> uid='UID3335-1298893761'
>
> I believe that VACUUM is one of the statements which counts as changing the
> schema, because it does its work by rewriting entire tables and/or indexes. 
> So don't do a VACUUM when you're doing multi-process access.  Cut out the
> VACUUMs and see whether you still get this result code.

Ah, thanks, that's a good clue. I can do some work in that area to ensure that 
the VACUUMs are done at a quiet moment.

>> But, very often the database on which the error occurs is :memory: - and I
>> guess each thread will have its own one of those.
>
> Are you doing concurrent access to your :memory: databases ?

No, that's the odd part. But I'll check back through my logs.

Thanks for the help.

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

Re: [sqlite] Still chasing "database schema has changed"

[Tim Streater]

On 26 Sep 2011 at 22:40, Simon Slavin <slav...@bigfraud.org> wrote: 

> On 26 Sep 2011, at 10:30pm, Tim Streater wrote:

>> My question now is, if two or more PHP scripts are competing for access to
>> the same database, is "database schema has changed" a possible outcome?
>
> Well, let's see what you're actually doing.  The following SQL commands should
> never cause that error: SELECT, INSERT, UPDATE, BEGIN, COMMIT.  What other SQL
> commands are you issuing ?  VACUUM or ANALYZE ?  CREATE or DROP ?

For the most part it's going to be SELECT, INSERT, UPDATE, BEGIN, COMMIT. Less 
often, there's VACUUM. Let me have a look at my logs - OK, when I got the error 
today, there was a thread doing a VACUUM on the db which got the error (within 
a few seconds, in terms of the log timestamps). The SQL statement being done 
when the error was reported was in fact:

  update uids set uid_date='1317028381',rec_date='1317002026' where 
uid='UID3335-1298893761'

But, very often the database on which the error occurs is :memory: - and I 
guess each thread will have its own one of those.

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

[sqlite] Still chasing "database schema has changed"

[Tim Streater]

Thinking some more about the occasional instances of "database schema has 
changed" that I experience, I took a look through the amalgamation to see where 
this error might be being detected. Reading the code and the comments I'm 
reminded of something I keep forgetting: that SQLite has no server. And the way 
my app operates, it's quite possible that it can ask apache to run three or 
four threads more or less simultaneously, each of which may want to modify the 
same set of SQLite databases. Each such thread is a PHP script using the PDO 
interface; this is under OS X 10.7.1 using whichever apache version/PHP/SQLite 
that comes with it (it's all pretty recent, IIRC).

My question now is, if two or more PHP scripts are competing for access to the 
same database, is "database schema has changed" a possible outcome?

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

Re: [sqlite] c-api document suggestion

[Tim Streater]

On 23 Sep 2011 at 11:18, Mirek Suk  wrote: 

> Dne 23.9.2011 4:41, Igor Tandetnik napsal(a):

>> Note that I didn't say it was wise to store NUL characters as part of the
>> string - I only said that you could do it if you wanted to. sqlite3_bind_text
>> takes the length parameter at face value, and stores exactly as many bytes as
>> you tell it to store. It's up to you to ensure that the values actually make
>> sense for your application. Garbage in/garbage out and all that.

> I just find entire nul handling in SQLite strange. it's C API why not
> expect C (that is nul terminated) strings.

Not in my case it's not. In my case it's the PHP API and I would be very 
annoyed if strings came back with unexpected NULs at the end, or if any NUL I 
insert in the middle of a string acted as a terminator.

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

Re: [sqlite] Does coalesce terminate early?

[Tim Streater]

On 15 Sep 2011 at 18:21, Jay A. Kreibich  wrote: 

> On Thu, Sep 15, 2011 at 11:13:57AM -0500, Puneet Kishor scratched on the wall:
>
>> While your suggested documentation won't harm, and will likely help,
>> actually the above does suggest to me a short-circuit-ish kind of
>> logic from the assertion that "The coalesce() function returns a
>> copy of its first non-NULL argument."
>
>  Why?  It is a function call.  One would expect all the parameters to
>  be evaluated, and then the function called.  In almost all languages,
>  short-circuit evaluation is reserved for operators, not function
>  parameters.

Really? I'd like to think that if a parameter is not used due to the particular 
logic of the function, then it's not evaluated but I wouldn't go into court on 
that :-)

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

Re: [sqlite] Database schema has changed?

[Tim Streater]

On 13 Sep 2011 at 17:03, Simon Slavin <slav...@bigfraud.org> wrote: 

> On 13 Sep 2011, at 4:38pm, Tim Streater wrote:
>
>> In general the row could get moved around any number of databases and then
>> back to the original one.
>
> That's one reason I think making absid unique then manually manipulating absid
> values is a bad idea.  Labelling which system each row originated in, and
> including that column in the unique key, is enough for you to avoid all
> collisions.  Then you no longer have to worry about changing any absids: leave
> the absid exactly as it is.  Move your rows from one database file to another
> with a simple INSERT, never worrying about changing anything in the record.

Mmmm. Looks like there's no elegant way to do it. I looked into this a couple 
of years ago when designing the setup. So:

1) Leave things as they are. Downside is the unexplained error every few months 
and it's a slightly clumsy method. Upside is if the schema changes there's no 
extra work to do.

2) Gerry suggests listing out the columns explicitly. Upside is this simplifies 
the move operation, downside is extra maintenance. I suppose I could get clever 
and store the column names in a Settings database I already have and use that 
to generate the SQL.

3) Use your suggestion. Upsides as you describe - simple move. Downside is an 
extra column with the same value in it for all rows. The irritating part is 
that there is a unique value for each database stored in another table in the 
same db. But it appears I can't do: PRIMARY KEY (OTHERTABLE.ORIG, ABSID) which 
would have been nice.

> I believe the latter.  Five columns will be copied into five columns in column
> order, regardless of column names.

If that's the case I can't see where the occasional error is coming from.

Thanks for your responses.

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

Re: [sqlite] Database schema has changed?

[Tim Streater]

On 13 Sep 2011 at 13:04, Simon Slavin <slav...@bigfraud.org> wrote: 

> On 13 Sep 2011, at 12:13pm, Tim Streater wrote:
>
>>  SQLite error: general code: HY000 error: 17, database schema has changed
>>
>> and since all the rest of the time the code works fine I'm having some
>> trouble pinning down whether it's my bug (more likely) and if so where, or an
>> SQLite bug (less likely).
>
> The schema of a database is not the data in it -- values of fields, new rows
> inserted, etc. -- but things like what columns there are in a database.

Yeah, I know.

>> What I have is two databases (each is a mailbox) and I wish, from time to
>> time, to move a row from one database to the other (the schemas are the same
>> - but see below). Each row has a unique id, (defined as: absid integer
>> PRIMARY KEY - but see below) and when the row is moved, I want to allocate a
>> new absid value to the row in its new location. I have to do this to avoid
>> clashing with absid values for already-existing rows.
>
> I don't see an easy way of solving conflicting absids.

The hard way of solving this is to select all the fields of the messages table 
explicitly (except absid), so I can then insert them into a new row in the 
destination table. But I'm trying to avoid this as a maintenance headache (I 
may wish to change the schema for messages from time to time).

> Whatever strategy you
> use, there's still a chance you'll get a crash.  And you have the problem of
> each copy of the database showing different absid numbers for the same data.

This is not a problem.

> I'd like to suggest an alternative strategy:
>
> Create a new column which indicates which database the row originated in.  For
> each of those two databases, when you create a new row, set the right value
> for this 'origin' column.  Make your primary key not just 'absid' but
> '(origin,absid)'.
>
> You can still define 'absid' as 'INTEGER AUTOINCREMENT', but not 'INTEGER
> PRIMARY KEY'.  Something like
>
> CREATE TABLE myTable (orig TEXT, absid INTEGER AUTOINCREMENT, a, b, c, d,
> PRIMARY KEY (orig, absid))

In general the row could get moved around any number of databases and then back 
to the original one.

>> I'm using the PDO interface in PHP and what I do is ($dbh is a handle to the
>> source database):
>>
>> $dbh->query ("attach database ':memory:' as mem");
>> $dbh->query ($create_messages);   // Create the messages table in the
>> memory database
>> $dbh->query ("attach database '" . $mailbox . "' as dst");
>> $dbh->query ("insert into mem.messages select * from main.messages where
>> absid='$absid'");// *** The failing statement ***
>> $dbh->query ("update mem.messages set absid=null");
>> $dbh->query ("insert into dst.messages select * from mem.messages");
>> $absid = $dbh->lastInsertId ();
>>
>> The only way I could find to do what I need regarding a new absid value is,
>> as above, to copy the row to a memory database, set its absid to null, and
>> then copy to the destination database. Even to do this I've had to define
>> absid in the memory database as "absid integer" rather than "absid integer
>> PRIMARY KEY". Is this the cause of the error message? If so, why does it work
>> 99.9% of the time?
>
> Two records with the same absid will violate your PRIMARY KEY contraint,
> because they break the UNIQUE requirement.  Perhaps the time they fail is when
> by coincidence both databases generate entries with the same absid.

I don't see how this can have an impact. The row is copied to an intermediate 
database (the memory one). Its absid is then set to null (in the memory 
database), so that when the second insert is done, the destination database can 
choose a new absid value.

Here's a simple question. If I do this:

insert into dst.messages select * from src.messages

do the databases as represented by src and dst have to have the same schema, or 
merely the same number of columns?

Meanwhile I think I'll refresh my understanding of INTEGER PRIMARY KEY and 
INTEGER AUTOINCREMENT.

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

[sqlite] Database schema has changed?

[Tim Streater]

About once every few months I get this error (under OS X 10.7.1):

  SQLite error: general code: HY000 error: 17, database schema has changed

and since all the rest of the time the code works fine I'm having some trouble 
pinning down whether it's my bug (more likely) and if so where, or an SQLite 
bug (less likely).

What I have is two databases (each is a mailbox) and I wish, from time to time, 
to move a row from one database to the other (the schemas are the same - but 
see below). Each row has a unique id, (defined as: absid integer PRIMARY KEY - 
but see below) and when the row is moved, I want to allocate a new absid value 
to the row in its new location. I have to do this to avoid clashing with absid 
values for already-existing rows.

I'm using the PDO interface in PHP and what I do is ($dbh is a handle to the 
source database):

 $dbh->query ("attach database ':memory:' as mem");
 $dbh->query ($create_messages);   // Create the messages table in the 
memory database
 $dbh->query ("attach database '" . $mailbox . "' as dst");
 $dbh->query ("insert into mem.messages select * from main.messages where 
absid='$absid'");// *** The failing statement ***
 $dbh->query ("update mem.messages set absid=null");
 $dbh->query ("insert into dst.messages select * from mem.messages");
 $absid = $dbh->lastInsertId ();

The only way I could find to do what I need regarding a new absid value is, as 
above, to copy the row to a memory database, set its absid to null, and then 
copy to the destination database. Even to do this I've had to define absid in 
the memory database as "absid integer" rather than "absid integer PRIMARY KEY". 
Is this the cause of the error message? If so, why does it work 99.9% of the 
time?

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

Re: [sqlite] Track DML (from certain clients only)

[Tim Streater]

On 02 Sep 2011 at 16:58, Stephan Beal  wrote: 

> On Fri, Sep 2, 2011 at 5:32 PM, Frans Knibbe  wrote:
>
>> If SQLite could log this statement including the comment, it would still be
>> possible to distinguish sources. Or reject the logging of statement with a
>> trigger similar to the one you proposed..
>>
>
> Why not just write a small wrapper function which does the logging and
> executes the statements? It could take additional arguments, e.g. a symbolic
> name for the source of the query (e.g. "app2.funcA()").

I use a wrapper function anyway, for these reasons:

1) So I can use try/catch. That allows me to handle any errors I may get in 
SQLite properly - logging the error and cleaning up.

2) So I can pass a small string which, when logged with any error message, can 
locate where the error occurred.


This would also allow me to implement a (circular) traceback buffer of 
statements.

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

Re: [sqlite] (no subject)

[Tim Streater]

On 31 Aug 2011 at 23:24, Igor Tandetnik <itandet...@mvps.org> wrote: 

> On 8/31/2011 5:56 PM, Tim Streater wrote:
>> In the above, each database is newly created as shown. What I had
>> forgotten to do was to create the "test" table in the second database
>> before copying the data. What seems to happen is that, lacking a
>> "test" table in the test2 database, SQLite appears to assume that I
>> must mean the "test" table in the test1 database - it tries to copy
>> data from the table into itself and so gets the error above.
>
> Yes. This is documented behavior - see http://sqlite.org/lang_attach.html.

Thanks for that helpful link. I'm sure I've looked at it before, but didn't 
register all the details of how it works. Now I see there's a way of referring 
to the original database as main, I'll use fully qualified table names wherever 
I have attached databases.

>> Is this reasonable behaviour? I might have expected to have a "no such table"
>> error.
>
> Which part of the documentation might have led you to expect that?

No part :-)

Sorry for the lack of subject line originally BTW.

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

[sqlite] (no subject)

[Tim Streater]

Today when trying to copy from one database to another, I had the following 
error (simplified example below):

  Second-Mini% sqlite3 test1
  sqlite> create table test (absid integer primary key, otherfield integer);
  sqlite> insert into test (absid,otherfield) values (null, 10);
  sqlite> insert into test (absid,otherfield) values (null, 20);
  sqlite> select * from test;
  absid | otherfield
  --+---
  1 | 10
  2 | 20
  sqlite> ^D

  Second-Mini% sqlite3 test2
  sqlite> attach database test1 as src;
  sqlite> insert into test select * from src.test;
  Error: PRIMARY KEY must be unique
  sqlite> 

In the above, each database is newly created as shown. What I had forgotten to 
do was to create the "test" table in the second database before copying the 
data. What seems to happen is that, lacking a "test" table in the test2 
database, SQLite appears to assume that I must mean the "test" table in the 
test1 database - it tries to copy data from the table into itself and so gets 
the error above.

Is this reasonable behaviour? I might have expected to have a "no such table" 
error.

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

Re: [sqlite] getting data by the column name.

[Tim Streater]

At 14:56 -0400 08/10/08, Igor Tandetnik wrote:
>Shaun R. <[EMAIL PROTECTED]> wrote:
>>  Anybody help me out here, trying to grab column info based on the
>>  columns name.  Examples i keep seeing people seam to use the colunm
>>  number but cant you access these values by the name?
>
>Not in general. Consider:
>
>select 1, 2, 3*5;
>
>What do you think the column names are here?
>
>However, see sqlite3_column_name[16], sqlite3_column_origin_name[16].
>With these, you can enumerate all the columns, get the name of each,
>figure out whether it's the one you want and thus obtain its number.

In PHP I'm doing something like this:

$dbh= new PDO ("sqlite:mydb");
$resorg = $dbh->query ("select * from my_table where absid='$PTR_org'");
$orgs   = $resorg->fetch (PDO::FETCH_ASSOC);
$organisation = $orgs["name"];
$address = $orgs["address"];

I never use the column number.
-- 
-- tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite garbage collection in PHP

[Tim Streater]

If I'm doing, as it might be, the following in PHP:

$dbh = new PDO ("sqlite:mydb");

$res1  = $dbh->query ("select ...");
$res11 = $res1->fetchAll (PDO::FETCH_ASSOC);

Does anyone know at what point the variable $res1 becomes free for 
re-use? Is it immediately after the assignment to $res11 above 
(assuming I don't want to fetch the result set again)?

In re-using these variables ($res1 and $res11), if I simply re-assign 
to them, does that cause a memory loss or is the garbage collection 
automatic? Or do I need to free up the space by setting them to null 
first, or via some function call?

(I had a look at php.net but the doc is a bit sketchy).

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

[sqlite] OS X precompiled command line binary

[Tim Streater]

The precompiled binary of the command line program for OS X appears not to have 
been built with a readline library. Is there any particular reason for this? 
(the version supplied with OS X Leopard (3.4.0), is so complied.

Thanks,

-- Tim

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

Re: [sqlite] Problem writing to database using PHP under OS X

[Tim Streater]

At 15:39 14/07/2008, John LeSueur wrote:
>On Mon, Jul 14, 2008 at 4:28 AM, Tim Streater <[EMAIL PROTECTED]>
>wrote:
>
> > I have a small script which shows this problem: if I run it from the
> > command line I can read from and write to the database. If I run it in a
> > browser it fails at the write with "General error: 14 unable to open
> > database file" (having done the read OK
> > first).
> >
> > The database has permissions ugo+rwx which ought to mean anyone can read or
> > write it. This is under OS X 10.5.4.
> >
> > Suggestions welcome - thanks,
> >
> > -- Tim
> >
>
>what about the directory the database is in? sqlite needs to write journal
>files

Yes. Eventually I googled for the entire error message and found some useful 
message threads.

Thanks,

-- Tim

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

[sqlite] Problem writing to database using PHP under OS X

[Tim Streater]

I have a small script which shows this problem: if I run it from the command 
line I can read from and write to the database. If I run it in a browser it 
fails at the write with "General error: 14 unable to open database file" 
(having done the read OK 
first).

The database has permissions ugo+rwx which ought to mean anyone can read or 
write it. This is under OS X 10.5.4.

Suggestions welcome - thanks,

-- Tim

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

[sqlite] Do I need to free results variables?

[Tim Streater]

If I have something like the following PHP:


$dbh = new PDO ("sqlite:" . $somename);

$resq = $dbh->query ("select * from some_table");
$rest = $resq->fetchAll ();


then do I need to do any cleanup on $resq before re-using it, such as setting 
to NULL (seems to be recommended for reuse of $dbh)? Or is that automatic (I 
know this happens at script-end, but I might re-use $resq many times).

I couldn't see anything in any docs on either the PHP or sqlite sites, about 
this.

Thanks,

-- Tim

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

Re: [sqlite] counting tables

[Tim Streater]

At 18:02 03/07/2008, Simon Davies wrote:
>Hi Tim,
>
>select count(*) from sqlite_master;
>
>will return 0 on a brand new db
>
>Rgds,
>Simon

Thanks - that's a handy pointer.


At 18:04 03/07/2008, Igor Tandetnik wrote:
>Tim Streater <[EMAIL PROTECTED]>
>wrote:
> > As a work around I thought I might check that the newly opened db has
> > no tables, and clean up if so. My question is, what query can I make
> > of the db to determine that it has no tables?
>
>select exists (select * from sqlite_master where type='table');
>
>Igor Tandetnik

Aha - another useful clue.


At 19:04 03/07/2008, Stephen Woodbridge wrote:

>Why don't you check for the existence of the file using PHP BEFORE you
>open it in? Trivial file exist test in PHP.

OK, OK, red faces on this side of the keyboard. Still, I'm glad I asked as the 
info I got back has allowed me to add extra sanity checks.

Thanks all.

-- Tim

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

[sqlite] counting tables

[Tim Streater]

Hi,

I have a PHP script that I run in Terminal under OS X, which opens an sqlite db 
and works on it. Trouble is, if the db doesn't exist then the PHP library 
silently creates an empty db with the given name. Given this, I need to detect 
that the empty db has 
been created, delete it, and exit.

As a work around I thought I might check that the newly opened db has no 
tables, and clean up if so. My question is, what query can I make of the db to 
determine that it has no tables? I couldn't find one (or perhaps I don't know 
SQL well enough). If I 
run sqlite3 I can give it the .tables command - I suppose I could have a call 
to this module from my script, but I'd prefer to avoid that. Alternatively is 
then another approach?

Thanks,

-- Tim

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