Re: [sqlite] Bug when uUsing Parameters with views

[Simon Davies]

2009/12/15 D. Richard Hipp :
>
>>
>
> Because string '2' is not the same thing as integer 2.
>
> sqlite3> select 2='2';
> 0
> sqlite3>
>

Why
sqlite> select cast( 2 as integer ) = '2';
1

>
> D. Richard Hipp
> d...@hwaci.com
>

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

Re: [sqlite] Bug when uUsing Parameters with views

[Simon Davies]

2009/12/15 Cariotoglou Mike :
> Simon, thanks for the answer. your code helped track down the issue,
> which I *still* believe to be a bug:
>
.
.
.
>
> select * from
> (select *,"
> cast((select count(*) from ITEM_ARTIST where
> ARTIST_id=artists.artist_id) as int) CNT
> from ARTISTS
> )
>
> now, any WHERE clause works as expected.
>
> Ideas ?
>

No -

essence of problem:

why is there a difference in output between the following selects?

SQLite version 3.6.20
Enter ".help" for instructions
sqlite> create table t1( id integer );
sqlite> create table t2( id integer );
sqlite>
sqlite> insert into t1 values( 1 );
sqlite> insert into t1 values( 2 );
sqlite> insert into t2 values( 1 );
sqlite> insert into t2 values( 2 );
sqlite> insert into t2 values( 2 );
sqlite>
sqlite> select * from t1 where (select count(*) from t2 where
t2.id=t1.id) = '2';
sqlite> select * from t1 where cast((select count(*) from t2 where t2.id=t1.id)
 as integer)= '2';
2

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

Re: [sqlite] Bug when uUsing Parameters with views

[Simon Davies]

2009/12/15 Cariotoglou Mike :
> I checked in the bug database, and this does not seem to have been
> reported, and IMHO it is definitely a bug.
> workarounds exist,as pointed out by me and others. still, I would like
> to hear from the core team whether this is
> recognized as a bug, and will be dealt with at some point in time.
> btw, I would have liked to post a script demonstrating the bug, but I do
> not think this is possible, due to the fact that the
> command-line sqlite does not handle parametric statements, or at least I
> don't know how to write one :)
>

I am unable to reproduce your problem (version 3.6.11):

//
// create table item_artist( artist_id integer, data text );
// create table artists( artist_id integer, data text );
// insert into artists values( 1, 'a1_pic1' );
// insert into artists values( 2, 'a1_pic2' );
// insert into item_artist values( 1, 'a1_item1' );
// insert into item_artist values( 2, 'a2_item1' );
// insert into item_artist values( 2, 'a2_item2' );

#include "stdafx.h"
#define SQLITE_PRIVATE
#include "sqlite3.c"

int _tmain(int argc, _TCHAR* argv[])
{
int cnt;
int dbStatus;
sqlite3* dbH;
sqlite3_stmt* stmt;
dbStatus = sqlite3_open( "tstBind.db",  );

while( fscanf( stdin, "%d",  ) )
{
char* sql = "select * from ( select *,"
"(select count(*) from ITEM_ARTIST where "
"ARTIST_id=artists.artist_id) CNT from ARTISTS ) where "
"cnt = :a;";

const char* tail;

if( 0 == cnt )
{
exit(0);
}

dbStatus = sqlite3_prepare_v2( dbH, sql, strlen( sql ), , 
 );
if( SQLITE_OK != dbStatus )
{
printf( "%s\n", sqlite3_errmsg( dbH ) );
}

dbStatus = sqlite3_bind_int( stmt, 1, cnt );
if( SQLITE_OK != dbStatus )
{
printf( "%s\n", sqlite3_errmsg( dbH ) );
}

while( SQLITE_ROW == ( dbStatus = sqlite3_step( stmt ) ) )
{
printf( "%s ", sqlite3_column_text( stmt, 0 ) );
printf( "%s ", sqlite3_column_text( stmt, 1 ) );
printf( "%s\n", sqlite3_column_text( stmt, 2 ) );
}
sqlite3_finalize( stmt );
}
sqlite3_close( dbH );

return 0;
}

On executing if I enter 1 I get
1
1 a1_pic1 1

and if 2, then
2
2 a2_pic1 2

which all looks ok

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

Re: [sqlite] EPC Based Search

[Simon Davies]

2009/12/15 Ram Mandavkar :
>
> Hello All,
>
>      I am New to SQL-Lite DB,
> I had one ASSET table having EPC as varchar(50) field and 5000 records in
> it.
>
> If i want to search
> (
>   CREATE TRIGGER "main"."TR_MULTIREAD_BEFORE" BEFORE INSERT ON READMODE
>       BEGIN
>            SELECT CASE
>                WHEN M.a = NEW.EPC THEN RAISE( IGNORE )
>                ELSE 1
>                END
>            FROM
>                ( SELECT EPC AS a FROM READMODE where EPC=NEW.EPC ) AS M;
>      END
>  )

Why not just make epc column "unique on conflict ignore"?
i.e.
CREATE TABLE readmode( epc text unique on conflict ignore, col2 text... );

> one EPC ( say:'e2003411b802010994095761' i.e. 24char ) out of 5000 records.
> and select query take 2-3 sec. to give result output.
>
> By any means i can reduce this search time with EPC as search criteria.
>
> Regards
>

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

Re: [sqlite] rowid of the current insert

[Simon Davies]

2009/12/10 Yuzem :
>
> CREATE TABLE movies(id integer,title text,unique(id))
>
> Generally I have an unique id from imdb but some times the movie isn't
> available.
> I understand that there is a column called rowid that is the primary key.
> I would like to insert the rowid on the id column when I don't have an id
> for the movie.
>
> Example:
> insert into movies values(rowid,'title1');
>
> How can I do that?

create trigger moviesTrig after insert on movies when new.id is null
begin update movies set id=rowid where rowid=new.rowid; end;

and insert via:
insert into movies( title ) values( xxx );

But there is no guarantee that the new rowid will not clash with any
existing id...

>
>

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

Re: [sqlite] compiling Sqlite with ICU

[Simon Davies]

2009/12/10 Sylvain Pointeau :
> Hi,
>
> I would like to use ICU with sqlite, I am on mac os x 10.6.2
> How should I do? I installed ICU but sqlite3 seems to not check ICU when
> compiling.
> I would like to use ICU via the sqlite shell.
>
> Please could someone explain me how to use ICU with sqlite3 ?

Have you found this compilation option?

http://www.sqlite.org/compile.html#enable_icu

>
> Cheers,
> Sylvain

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

Re: [sqlite] how to show blob data while select in sqlite3 shell?

[Simon Davies]

2009/12/9 liubin liu <7101...@sina.com>:
>
> sqlite> INSERT OR REPLACE INTO periods_value VALUES (0, 1,
> x'000102030400a0afaabbaa');
> sqlite>
> sqlite> SELECT * FROM periods_value;
> 0|1|
> sqlite>
>
> how to show the blob data?

Select id, valid, hex( value ) from periods_value;

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

Re: [sqlite] Multiples natural left joins problem

[Simon Davies]

2009/12/6 Yuzem :
>
> Is this a bug in new versions that will be fixed or it is the new behavior?

In 3.6.20, so long as you include the alias, you will get your expected results:
SELECT movies.id, title, files, icon_modified, icon_width
FROM ( movies NATURAL LEFT JOIN files) AS movies NATURAL LEFT JOIN icons_movies
LIMIT 25;

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

Re: [sqlite] Multiples natural left joins problem

[Simon Davies]

2009/12/4 Yuzem :
>
>> What query are you trying with the above tables, and how do the
>> results differ from what you expect?
>>
> This is the query:
> SELECT movies.id,title,files,icon_modified,icon_width
> FROM (movies natural left join files) natural left join icons_movies
> LIMIT 25 ;
>
> The difference with your last query is that I have to specify the tables or
> it says "ambiguous column name" and the problem with the result is that if a
> movie doesn't have an entry in the table "files" it doesn't show the icon
> properties.

Note that I have been using version 3.4.2, in which your query works fine.

You have not specified your version, but I can repeat your
difficulties when using version 3.6.19 and 3.6.20

You can get 'correct' results if you modify your query to
SELECT movies.id, title, files, icon_modified, icon_width
FROM ( movies natural left join files) as movies natural left join icons_movies
LIMIT 25;

Can anyone explain this behavioural difference?

Regards,
Simon

SQLite version 3.6.20
Enter ".help" for instructions
sqlite>
sqlite> create table t1( id integer primary key, t1data text );
sqlite> insert into t1( t1data ) values( 't1data1' );
sqlite> insert into t1( t1data ) values( 't1data2' );
sqlite>
sqlite> create table t2( id integer primary key, t2data text );
sqlite> insert into t2( t2data ) values( 't2data1' );
sqlite>
sqlite> create table t3( id integer primary key, t3data text );
sqlite> insert into t3( t3data ) values( 't3data1' );
sqlite> insert into t3( t3data ) values( 't3data2' );
sqlite>
sqlite> SELECT t1.id, t1data, t2data, t3data FROM (t1 natural left
join t2) as t1 natural left join t3;
1|t1data1|t2data1|t3data1
2|t1data2||t3data2
sqlite> SELECT t1.id, t1data, t2data, t3data FROM (t1 natural left
join t2) natural left join t3;
1|t1data1|t2data1|t3data1
2|t1data2||
sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiples natural left joins problem

[Simon Davies]

2009/12/3 Yuzem <naujnit...@gmail.com>:
>
>
.
.
.
> Mine is a little more complex actually:
> CREATE TABLE movies(id INTEGER,year INTEGER,rating INTEGER,votes
> INTEGER,runtime INTEGER,iconWidth INTEGER,iconHeight INTEGER,iconAlpha
> INTEGER,iconModified INTEGER,title TEXT,type TEXT,plot TEXT,color
> TEXT,unique(id))
>
> CREATE TABLE files(id INTEGER,modified INTEGER,size INTEGER,files
> TEXT,unique(files))
>
> CREATE TABLE icons_movies(icon_width integer,icon_height integer,icon_alpha
> integer,icon_modified integer,id text,unique(id))
>
> Movies with no files will not display the icons. Don't know why. Maybe
> because id is text in icons and integer in the others? But it joins, just,
> not as expected...
>

So I change:
sqlite> drop table tag;
sqlite> create table tag( id text, tag text );
sqlite> insert into tag( id, tag ) values( '1', 'tag1' );
sqlite> insert into tag( id, tag ) values( '2', 'tag2' );
sqlite> insert into tag( id, tag ) values( '3', 'tag3' );
sqlite> insert into tag( id, tag ) values( '4', 'tag4' );
sqlite>
sqlite> SELECT id,title,rating,tag FROM (movies natural left join user) natural
left join tag;
id|title|rating|tag
1|movie1|rating1|tag1
2|movie2|rating2|tag2
3|movie3||tag3

text v integer appears to make no difference

What query are you trying with the above tables, and how do the
results differ from what you expect?

>
> SimonDavies wrote:

Simon Slavin actually...

>>
>> You need to read up on the different types of JOIN that SQL allows.  No
>> reason to use a LEFT JOIN if you want a RIGHT JOIN, an INNER JOIN or a
>> CROSS JOIN.
>>
> It says that right join is not supported and inner and cross join don't give
> the expected result, they duplicate movies if there are more than one tag
> for every movie.

Regards,
Simon (Davies)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multiples natural left joins problem

[Simon Davies]

2009/12/3 Simon Davies <simon.james.dav...@googlemail.com>:
> 2009/12/3 Yuzem <naujnit...@gmail.com>:
>>> SELECT a.id,a.title,a.rating,tag.tag FROM (movies natural left join
>>> user) as a natural left join tag;
>>>
>> It doesn't work, it says: no such column a.id
>> If I use movies.id I have the same problem as before: movies with no rating
>> don't get any tags.
>>
>
> This is what I see:
>
> C:\>sqlite3 yuzem.db
> SQLite version 3.4.2
> Enter ".help" for instructions
.
.
.
> sqlite> SELECT a.id,a.title,a.rating,tag.tag FROM (movies natural left join 
> user
> ) as a natural left join tag;
> 1|movie1|rating1|tag1
> 2|movie2|rating2|tag2
> 3|movie3||tag3
> sqlite>
>
> Regards,
> Simon
>

Simpler still:
SELECT id,title,rating,tag FROM (movies natural left join user)
natural left join tag;

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

Re: [sqlite] Multiples natural left joins problem

[Simon Davies]

2009/12/3 Yuzem :
>
> Thanks both for the replies.
.
.
.
.
> SimonDavies wrote:
>>
>> SELECT a.id,a.title,a.rating,tag.tag FROM (movies natural left join
>> user) as a natural left join tag;
>>
> It doesn't work, it says: no such column a.id
> If I use movies.id I have the same problem as before: movies with no rating
> don't get any tags.
>

This is what I see:

C:\>sqlite3 yuzem.db
SQLite version 3.4.2
Enter ".help" for instructions
sqlite>
sqlite>
sqlite> create table movies( id integer primary key, title text );
sqlite> insert into movies( title ) values( 'movie1' );
sqlite> insert into movies( title ) values( 'movie2' );
sqlite> insert into movies( title ) values( 'movie3' );
sqlite>
sqlite> create table user( id integer primary key, rating text );
sqlite> insert into user( rating ) values( 'rating1' );
sqlite> insert into user( rating ) values( 'rating2' );
sqlite>
sqlite> create table tag( id integer primary key, tag text );
sqlite> insert into tag( tag ) values( 'tag1' );
sqlite> insert into tag( tag ) values( 'tag2' );
sqlite> insert into tag( tag ) values( 'tag3' );
sqlite>
sqlite> SELECT a.id,a.title,a.rating,tag.tag FROM (movies natural left join user
) as a natural left join tag;
1|movie1|rating1|tag1
2|movie2|rating2|tag2
3|movie3||tag3
sqlite>

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

Re: [sqlite] Multiples natural left joins problem

[Simon Davies]

2009/12/3 Pavel Ivanov :
> If using "natural left join" is not a requirement for you then this
> works as you expect it:
>
> select movies.id, title, rating, tag
> from movies left join user on movies.id = user.id
>        left join tag on movies.id = tag.id;
>

Or:

SELECT a.id,a.title,a.rating,tag.tag FROM (movies natural left join
user) as a natural left join tag;

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

Re: [sqlite] Doubts about usage of sqlite3.exe and foreign keys (enable this feature)

[Simon Davies]

2009/12/2 Ubirajara Marques da Cruz :
> To Support,
>
>
>
> I am trying to develop a data base what need to specify  foreign key to 
> preserve problems with insertion of datas.
>
>
>
> I have four tables like i describe below:
>
.
.
.
>
> When i start sqlite3 and inside sqlite prompt line i create data base with 
> this conditions and enable foreign key feature data base return error when i 
> try to put some datas out of range and return fail because foreign key isn´t 
> respected. But when i create data base right with sqlite3.exe dB.db  "SQL 
> STATMENT" command, foreign key is not respected.
>
>
> Why this happen?
>

http://www.sqlite.org/pragma.html#pragma_foreign_keys indicates that
the setting applies to the connection, not the database.

Each time you issue  command, a new
connection is created.

Try 

>
> Thanks in advance.
>
> Eng° Ubirajara Marques da Cruz
>

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

Re: [sqlite] open transaction

[Simon Davies]

2009/11/27  :
> How could I know if a transaction is already open? Does a specific
> command exist?
> Cheers

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

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

Re: [sqlite] New sqlite bug report: Problem with /* */ comment followed by Non-SQL (sqlite-specific) command

[Simon Davies]

2009/11/20 Norbert :
> Hi,
>
> I use SQLite 3.6.16 on Ubuntu Linux Karmic Koala, installation via
> normal Ubuntu repository. At home (Linux) and also in the office
> (Windows XP), I encountered the following problem.
>
> Source of test.sql:
>
> *BEGIN OF SOURCECODE  (next line is line 1)
> .headers off
>
>
> /*
> Here comes the comment. Source is reduced to show core of the problem.
> */
>
> .mode column
>
> SELECT * from tab1;
>
>
>
> *END OF SOURCECODE**
>
>
> Now, I have a sqlite database with one table tab1. When I do the
> following:
> .read test.sql
>
> I get the following error:
> SQL error near line 4: near ".": syntax error
>
> The ".mode column" after the comment block can be replaced by other
> Non-SQL statements, and you will get the same error. When after the
> comment block, there is directly a SQL statement, there is no error.
>
> I have the feeling that there is a problem of the SQLite parser dealing
> with a mix of comments and SQLite-specific commands.
>

comment lines in sql begin with --

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

Re: [sqlite] execute or prepare+step+finalize

[Simon Davies]

2009/11/11 T :
>
> i`ve done it, and i think i understand it :)
>
> thank you..
>
>
> few more question to be sure that i`m not missing something important..
>
> if i use prepared statements only (dont use exec function in program at all)
> i dont need callback function, do i?

No

>  somehow after i replaced exec (i.e. select_statement function) and i dont
> need select_statement function, neither callback function, evetything seems
> much more simple (which is why i`m bit suspicious if i`ve done everything
> correct)..
>
> are there any cases when it is better to use exec ?


perhaps simpler/quicker to code for sql that returns no data (INSERT etc)


>
>
> does using prepared statement slow down my program since prepare function is
> used every time when i want to execute some command ?

No - sqlite3_exec() is a wrapper around sqlite3_prepare()/sqlite3_step()

> here is how my
> function looks like now:
>
> bool create2 (char * command)
> {
>
> sqlite3_stmt * statement2;
>
> if ( sqlite3_prepare (db, command, -1, , 0) != SQLITE_OK )
>        {
>                int err = sqlite3_prepare (db, command, -1, , 0);

no need to call prepare a second time - assign to err in your if
statement, or use sqlite3_errcode()

>                const char * pErr = sqlite3_errmsg (db);
>                printf ("\nError %d occured! \n %s", err, pErr  );
>                return false;
>        }
>
> int iCol = sqlite3_column_count (statement2);
>
> int smth, i;
> //int rows=1;
> smth=sqlite3_step(statement2);
>
> while (smth == SQLITE_ROW)
> {
>        printf ("\n");
>        //printf ("\n Row %d:\t", rows);   // doesnt give right number of 
> column
> for other select statements except select all
>        for (i=0; i        {
>                const char *txt = (const char*)sqlite3_column_text(statement2, 
> i); // save
> it into dynamical multidimensional array
>                printf (" %s = %s \t", sqlite3_column_name (statement2,i), txt 
> );
>        }
>        printf ("\n");
>        //rows++;
>        smth=sqlite3_step (statement2);
>
> }
>
>        sqlite3_reset (statement2);

Use sqlite3_reset if you are going to bind new values to your prepared
statement and restep. If you are finalizing there is no need to reset.

>        sqlite3_finalize (statement2);
>
>        return true;
> }
>
>
> main:
>
>
> create2 ("CREATE TABLE two (ID INTEGER PRIMARY KEY ASC, a,b,c)");
> create2 ("INSERT INTO two (a,b) VALUES (3, 4)");
> create2 ("INSERT INTO two (a,b,c) VALUES (2, 8, 9)");
> create2 ("INSERT INTO two (a,c) VALUES (4, 1)");
> create2 ("INSERT INTO two (a,b,c) VALUES (1, 4, 9)");
> create2 ("INSERT INTO two (a,b,c) VALUES (1, 2, 8)");
> create2 ("SELECT * FROM two");
> create2 ("SELECT * FROM two WHERE b=4");
>
>
> if you have some advices or suggestions please let me know..
>

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

Re: [sqlite] sqlite3_free()

[Simon Davies]

2009/11/11 T :
>
> is this right way how to do it:
>
> sqlite_free (errmsg);
>
> ???
>
>
> or this:
>
>
> sqlite3_free (NULL);

>From http://www.sqlite.org/c3ref/free.html:
"The sqlite3_free() routine is a no-op if is called with a NULL pointer."

>
> or how should i do it?

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

Re: [sqlite] execute or prepare+step+finalize

[Simon Davies]

2009/11/11 T :
>
.
.
.
>
>> and finaly i get SQLITE_DONE but it still doesnt show me table i have
>> selected...
>
> See http://www.sqlite.org/c3ref/column_blob.html
>
>
> i dont understand what exactly do you mean..
>
> combination of these:
>
> sqlite3_value *sqlite3_column_value(sqlite3_stmt*, int iCol);
> typedef struct Mem sqlite3_value;
> void sqlite3_result_value(sqlite3_context*, sqlite3_value*);

>From http://www.sqlite.org/c3ref/column_blob.html:
"These routines form the "result set query" interface."

If you want to see the data, you need to get it using the
sqlite3_column_xxx() routines after each call to sqlite3_step(). If
your column contains an integer, use sqlite3_column_int(), for real
values use sqlite3_column_double(), and so on.

>
> maybe?
>
>

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

Re: [sqlite] execute or prepare+step+finalize

[Simon Davies]

2009/11/11 T :
>
> i think i understand why it doesnt work for select *..
>
> because sqlite_step executes one row at time.. so after i prepare SELECT * ,
> i need to use sqlite_step as many times as table i`m selecting from has rows
> (in this case 3 times)..
> so i made another function that looks like this:
>
>
>
> bool create2 (char * command)
>
> {
> sqlite3_stmt * statement2;
>
> if ( sqlite3_prepare (db, command, -1, , 0) != SQLITE_OK )
>        {
>                int err = sqlite3_prepare (db, command, -1, , 0);
>                const char * pErr = sqlite3_errmsg (db);
>                printf ("\nError %d occured! \n %s", err, pErr  );
>                return 1;
>        }
> int i;
> for (i=0; i<=3; i++)
> {
>        int smth= sqlite3_step (statement2);
>        printf ("\n command= %s result code = %d \n",command, smth);
> }

why not:
int smth = sqlite3_step (statement2);
while( smth == SQLITE_ROW )
{
printf( "\n command= %s result code = %d \n", command, smth );
smth = sqlite3_step (statement2);
}
so that it will work no matter how many rows

>        sqlite3_reset (statement2);
>        sqlite3_finalize (statement2);
>
>        return 0;
> }
>
>
>
> and finaly i get SQLITE_DONE but it still doesnt show me table i have
> selected...

See http://www.sqlite.org/c3ref/column_blob.html

>
>
> do i need to use prepare function for each command? if so, isnt then
> function select_statement better to use?

You are using prepare for each command...

>
>

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

Re: [sqlite] 3.6.20 NATURAL self-join still not fixed

[Simon Davies]

2009/11/7 Kristoffer Danielsson :
>
> Using SQLite 3.6.20 (SQLITE_ENABLE_STAT2=1).
>
> PRAGMA foreign_keys=OFF;
>
> BEGIN TRANSACTION;
>
> CREATE TABLE Test
> (
>  TestID INTEGER PRIMARY KEY,
>  T1 INTEGER NOT NULL,
>  T2 INTEGER NOT NULL,
>  T3 INTEGER NOT NULL,
>  T4 INTEGER NOT NULL,
>  T5 INTEGER NOT NULL,
>  T6 INTEGER NOT NULL,
>  T7 INTEGER NOT NULL,
>  DT DATE NOT NULL,
>  T8 INTEGER NOT NULL,
>  T9 INTEGER NOT NULL,
>  T10 INTEGER NOT NULL,
>  T11 INTEGER NOT NULL,
>
>  UNIQUE (T2, T1)
> );
>
> -- Fill with random data!
>
> COMMIT TRANSACTION;
>
>
> SELECT COUNT(*) FROM Test; -- Blistering fast!
>
> SELECT COUNT(*) FROM Test NATURAL JOIN Test; -- "Never" terminates
>
> I have over 50,000 entries...

I used 10,000 entries, and it returned in a few seconds... (using 3.6.19)

But there does appear to be a problem:

sqlite> CREATE TABLE Test1
   ...> (
   ...>  T1 INTEGER NOT NULL,
   ...>  T2 INTEGER NOT NULL
   ...> );
sqlite>
sqlite> CREATE TABLE Test2
   ...> (
   ...>  T1 INTEGER NOT NULL,
   ...>  T2 INTEGER NOT NULL
   ...> );
sqlite>
sqlite> insert into test1 values( 1, 1 );
sqlite> insert into test1 values( 2, 2 );
sqlite> insert into test1 values( 3, 3 );
sqlite> insert into test1 values( 4, 4 );
sqlite> insert into test1 values( 5, 5 );
sqlite> insert into test1 values( 6, 6 );
sqlite> insert into test1 values( 7, 7 );
sqlite> insert into test1 values( 8, 8 );
sqlite> insert into test1 values( 9, 9 );
sqlite> insert into test1 values( 10, 10 );
sqlite>
sqlite> insert into test2 values( 1, 1 );
sqlite> insert into test2 values( 2, 2 );
sqlite> insert into test2 values( 3, 3 );
sqlite> insert into test2 values( 4, 4 );
sqlite> insert into test2 values( 5, 5 );
sqlite> insert into test2 values( 6, 6 );
sqlite> insert into test2 values( 7, 7 );
sqlite> insert into test2 values( 8, 8 );
sqlite> insert into test2 values( 9, 9 );
sqlite> insert into test2 values( 10, 10 );
sqlite>
sqlite>
sqlite> select count(*) from test1 natural join test2;
10
sqlite> select count(*) from test1 natural join test1;
100
sqlite> select count(*) from test1 as t1 natural join test1;
10
sqlite>

>
> Thanks.
>
>> From: danielk1...@gmail.com
>> To: sqlite-users@sqlite.org
>> Date: Sat, 7 Nov 2009 11:36:52 +0700
>> Subject: Re: [sqlite] 3.6.20 NATURAL self-join still not fixed
>>

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

Re: [sqlite] A constraint bug?

[Simon Davies]

2009/10/31 Mick :
> This is more FYI than needing it (as I have already worked around it), but I
> have discovered that an IGNORE constraint on an insert, when one of the
> fields in the constraint is NULL, will insert a duplicate record into the
> database.
>
> i.e.
>
> CREATE TABLE mytable (
> ID1 INTEGER NOT NULL,
> ID2 INTEGER NOT NULL,
> SomeText VARCHAR(100) COLLATE NOCASE,
> PRIMARY KEY (ID1, ID2, SomeText) ON CONFLICT IGNORE);
>
> INSERT INTO mytable VALUES (1, 1, NULL);
> INSERT INTO mytable VALUES (1, 1, NULL);
>
> Creates 2 records with the same primary key. It makes no difference whether
> you use "OR IGNORE" in the insert statements, either.

NULL != NULL, so the primary key is not conflicting

>
> Cheers,
>
> Mick O'Neill
>

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

Re: [sqlite] .import on a .csv file

[Simon Davies]

2009/10/22 Scott Baker :
> I'm trying to .import a CSV file and I can't quite figure out the syntax.
>
> I created a table, and then did:
>
> .separator ,
> .import /tmp/foo.csv mytable
>
> This works sort of, unless my data has , in it. Something like "last,
> first". Because it tries to split at that , and then the number of rows
> doesn't match my table.
>
> Then I tried setting the separator to
>
> .separator \",\"
>
> Which works correctly (i.e. it splits the data properly). However, now my
> first and last columns have " on the beginning/end of them. Is there a way
> to import a well formed CSV.
>
> My CSV data looks like this:
>
> "38665","101977","Deadly Sparrows Inc.","1435 S. Doolis
> Ln","Donkville","OR","90210","Doolis, Jason","5032349422","Active"
>
> Help!

There was a discussion on csv import a little while ago:
http://www.nabble.com/Tedious-CSV-import-question-to25601326.html#a25601326

In essence, you need to do some work if your field separator character
appears inside your data fields.

One solution is to replace your existing separators  (,) with a
character that does not exist in your data, specify that character as
the separator to sqlite, and you should be good to go .import.

>
> --
> Scott Baker - Canby Telcom
> System Administrator - RHCE - 503.266.8253

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

Re: [sqlite] Select * from table where field = "value" does not work when "value" is also a field....

[Simon Davies]

2009/10/13 Hillebrand Snip :
> I have a database with the following fields:
> 1) Status (it will hold values like "Open", "Closed", "Submitted"... etc...)
> 2) Closed (boolean field which contains 1 or 0)
>
> If i enter a query like:  Select * from Issues where Status != "Closed"  i
> get all records (even the ones with Status=Closed).
> If i replace "Closed" by "Submitted" which is not also a field name the
> results are as expected.
>
> I think this is a bug.

... in your select statement.

Double quotes are used to delimit identifiers. Use single quotes to
delimit string literals, i.e.
Select * from Issues where Status != 'Closed'

Where SQLite cannot find a column match for a double-quote delimited
string, then it will assume that a string literal is intended.

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

>
> I use Apache.
>
> SQLITE Information from phpinfo():
>
>
> SQLiteSQLite supportenabledPECL Module version2.0-dev $Id: sqlite.c,v
> 1.166.2.13.2.10 2007/12/31 07:20:11 sebastian Exp $SQLite Library2.8.17SQLite
> Encodingiso8859

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

Re: [sqlite] Datetime mystery

[Simon Davies]

2009/10/8 Fredrik Karlsson :
> Hi,
>
>
>
> On Thu, Oct 8, 2009 at 12:04 AM, P Kishor  wrote:
>> On Wed, Oct 7, 2009 at 5:02 PM, Fredrik Karlsson  wrote:
>>> Dear list,
>>>
>>> I am sorry if I am asking a FAQ, but what is differnent with
>>> datetime() and time()?
>>>
 date # This is the correct time on the system
>>> Ons  7 Okt 2009 23:56:36 CEST
 sqlite3 temp.sqlite "SELECT datetime();"
>>> 2009-10-07 21:56:58
 sqlite3 temp.sqlite "SELECT datetime('now);"
>>> SQL error: unrecognized token: "'now);"
 sqlite3 temp.sqlite "SELECT datetime('now');"
>>> 2009-10-07 21:57:13
 sqlite3 temp.sqlite "SELECT time('now');"
>>> 21:59:05
>>>
>>> What happened here? How come the time functions are off 2 hours?
>>> (I am using sqlite version 3.5.9 on a Mac OS Leopard machine)
>>>
>>
>>
>> time zones. The sqlite returned times, by default, are UTC.
>>
>
> Yes, that would have been my guess too, but I am on CET, which I
> understand is UTC+1. So, I am still getting one hour less than I
> should from SQLite. Or, am I doing something stupid?

>From above, you are currently on CEST, which is UTC+2

>
> /Fredrik
> "Life is like a trumpet - if you don't put anything into it, you don't
> get anything out of it."


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

Re: [sqlite] Strange File Import Error

[Simon Davies]

2009/9/22 Rich Shepard <rshep...@appl-ecosys.com>:
> On Tue, 22 Sep 2009, Simon Davies wrote:
>
>>> 117172|Engineered Structures Inc.|Brockway Center|10875 SW Herman
>>> Rd|Tualatin|97062-8033|Washington|NWR|45.3834|-122.7882|1542|Nonresidential
>>> Construct NEC|Gen12c(Agent)|Stormwater; NPDES Construction More Than 1 Acre
>>> Disturbed Ground|Issued By Agent|Minor|STM|Legal Contact|Engineered
>>> Structures Inc.|Gary|Ross|15940 SW 72nd
>>> Ave|Portland|OR|97224-7936|503-968-6639|
>>
>> But I count 26 pipe chars in your text above...
>
> Simon,
>
>   With the terminal one removed there are 25. That's how it was before I
> tried adding a terminating pipe to see where SQLite is seeing the 26th
> column.

25 separators -> 26 columns?

>
> Rich

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

Re: [sqlite] Strange File Import Error

[Simon Davies]

2009/9/22 Rich Shepard <rshep...@appl-ecosys.com>:
> On Tue, 22 Sep 2009, Simon Davies wrote:
>
>> Remove the trailing pipe character
>
>   Did that as soon as I learned it made no difference. Each line should be
> clean and there are 25 columns defined in it. Quite frustrating.

> 117172|Engineered Structures Inc.|Brockway Center|10875 SW Herman
> Rd|Tualatin|97062-8033|Washington|NWR|45.3834|-122.7882|1542|Nonresidential
> Construct NEC|Gen12c(Agent)|Stormwater; NPDES Construction More Than 1 Acre
> Disturbed Ground|Issued By Agent|Minor|STM|Legal Contact|Engineered
> Structures Inc.|Gary|Ross|15940 SW 72nd
> Ave|Portland|OR|97224-7936|503-968-6639|

But I count 26 pipe chars in your text above...

>
> Rich

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

Re: [sqlite] Strange File Import Error

[Simon Davies]

2009/9/22 Rich Shepard :
> On Tue, 22 Sep 2009, Simon Slavin wrote:
>
>> Use a text editor on the file and change all occurrences of (including the
>> quotes)
>>
>> ","
>>
>> to
>>
>> "|"
>>
>> then set .separator to the single character | before you import the
>> file.
>
> Simon,
>
>   The default separator is '|' (as confirmed by the .show command) so I
> changed all commas to the pipe. Makes no difference. Line 1 is still seen by
> SQlite as 26 columns for the expected 25 columns. Yet, when I count them I
> see only the 25.
>
>   Just for the heck of it I added a pipe symbol to the end-of-line, but that
> doesn't make a difference either.
>
>   Can't see what's causing the problem for some reason. Here's line 1 again
> (wrapped by alpine when pasted in):
>
> 117172|Engineered Structures Inc.|Brockway Center|10875 SW Herman
> Rd|Tualatin|97062-8033|Washington|NWR|45.3834|-122.7882|1542|Nonresidential
> Construct NEC|Gen12c(Agent)|Stormwater; NPDES Construction More Than 1 Acre
> Disturbed Ground|Issued By Agent|Minor|STM|Legal Contact|Engineered
> Structures Inc.|Gary|Ross|15940 SW 72nd
> Ave|Portland|OR|97224-7936|503-968-6639|

Remove the trailing pipe character

>
>   There are no longer any commas in the file.
>
> Rich

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

Re: [sqlite] Strange File Import Error

[Simon Davies]

2009/9/22 Pavel Ivanov :
>>   If I understand correctly, I can remove all quotes as long as the only
>> commas delineate columns. Or, I can use the pipe as a separator and remove
>> all quotes, too. Correct?
>
> AFAIK, you can do only the first - remove all quotes and make sure
> that no commas met in field values.

You can use .separator to specify pipe (or any other character of
choice) as the field separator for .import

Your separator needs to be a character that does not appear anywhere
in your data

>
>
> Pavel
>

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

Re: [sqlite] Date comparisons

[Simon Davies]

2009/9/21 Barton Torbert :
> Hello,
>
> I am having trouble doing a rather odd data comparison.
>
> I have two table, each with a DateTime field.  The timestamps in these fields 
> do not match exactly.  I want to find the row in the second table that is 
> within a specific time period around the time period to the DateTime in the 
> first table.

Note that SQLite has no native DateTime type. A DateTime could be a
text string, or a real Julian day. What is your data?

>
> I have tried various combinations of reformatting the data to do a 
> comparison.  None of these works.  In particular I had hoped that the 
> strftime function (converting using the '%s' format to seconds since 
> 1970-01-01 would work but it does not.  The comparison with = is okay, but I 
> need to use some combination of < and > or a BETWEEN.  Nothing seems to work.

I can't tell what is wrong because you have provided no examples.

>
> Does anybody have a suggestion?

(Re)read http://www.sqlite.org/lang_datefunc.html

Provide more information on what you are doing...

>
> Bart
>

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

Re: [sqlite] Why are allowed to keep a text of 3 characters in a field that has been set to 2?

[Simon Davies]

2009/9/21 Guillermo Varona Silupú :
> Hi
> In these SQL commands:
>
> CREATE TABLE "test" ("code" char(2));
> INSERT INTO test (code) VALUES("123")
>
> Why are allowed to keep a text of 3 characters in a field that has been
> set to 2?
> Is a bug?

No - I suggest that you have a look at http://www.sqlite.org/datatype3.html

>
> TIA
> BestRegards
> GVS
>

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

Re: [sqlite] Viewer for blobs in hex?

[Simon Davies]

2009/9/21 Yan Bertrand :
>                Hi all,
>
>
>
> I would like to display the contents of blobs in my table as
> hexadecimal. I have not found any easy way of doing this. I tried :
>
> -          wxSQLitePlus, but it does not display blobs contents (or I
> could not make it do so)
>
> -          SQLiteManager plugin (for Mozilla Firefox), but it does not
> do this by default. The website says it can but the explaination for it
> is still < to be done >.
>
> -          SQLiteStudio does not display anything (it says < NULL > in
> italic, but it is not a NULL content.)
>
> -          SQLite2009 Pro but it does not dosplay blobs either
>
> -          I could dump the table but it really gets tedious.
>
>
>
> Note: I tried exporting to other formats but the blobs are replaced by a
> string (either < NULL > or < NONE >). I know my blobs are not empty
> because C-code can read them.
>
>
>
> Any idea?
>

>From sqlite shell command line (or equivalent in you GUI of choice) execute

select hex( yourBlobColumnName ) from yourTable;

>
> Thank you for your support and merry continued use of SQLite!
>
> Yan
>

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

Re: [sqlite] How can I do to build this query with a text that contains ' and "?

[Simon Davies]

2009/9/20 Guillermo Varona Silupú :
> Hi
> I want to insert this text:
>
> 1' equivale a 12"
>
> cQry := "INSERT INTO Tabla1 (Code,Equiv) VALUES (10, "1' equivale a 12"")

INSERT INTO Tabla1 (Code,Equiv) VALUES (10, '1'' equivale a 12"');

>
> TIA
> Best Regards
> GVS
>
> P.D.: Sorry for the post past and my bad english
>
>

single quotes to delimit string literals. Double them up to escape
them inside a string literal.
See http://www.sqlite.org/lang_expr.html#litvalue

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

Re: [sqlite] Creating custom function for recursive queries

[Simon Davies]

2009/9/16 Marcel Strittmatter :
> Hi
>
> I implemented a custom function that returns a comma separated list of
> primary keys as a string by making recursive queries. This works well
> if I don't use subqueries. But I like to use subqueries like this
>
> SELECT * FROM users WHERE id IN (SELECT parents('relations', 3));
>
> Below some example data I used to test my custom function...
>
> Is it possible to implement a custom function where the result can be
> used in a subquery like above?

Yes - you seem to have achieved that.

>
> Regards
> Marcel
>
>
> Here a dump of an example database:
>
> CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
> INSERT INTO "users" VALUES(1,'Marge');
> INSERT INTO "users" VALUES(2,'Homer');
> INSERT INTO "users" VALUES(3,'Lisa');
> INSERT INTO "users" VALUES(4,'Bart');
> INSERT INTO "users" VALUES(5,'Maggie');
> CREATE TABLE relations (parent INTEGER, child INTEGER);
> INSERT INTO "relations" VALUES(1,3);
> INSERT INTO "relations" VALUES(1,4);
> INSERT INTO "relations" VALUES(1,5);
> INSERT INTO "relations" VALUES(2,3);
> INSERT INTO "relations" VALUES(2,4);
>
> And here the output of my custom function (parents), which is similar
> to the builtin function group_concat():
>
> sqlite> SELECT name,parents('relations', id) FROM users WHERE id = 3;
> name        parents('relations', id)
> --  
> Lisa        1,2
>
> But the following query returns nothing
>
> SELECT name FROM users WHERE id IN (SELECT parents('relations', id)
> FROM users WHERE id = 3);
>
> because the result of the custom function is a string ("1,2")...

Indeed. But your custom function is working just fine.

Why not just use

SELECT name FROM users WHERE id IN (SELECT parent from relations WHERE
child = 3);

??

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

Re: [sqlite] JOIN (SELECT o.pid, SUM(o.qty) 'qty_sold' FROM ORDERS o) qs

[Simon Davies]

009/9/15 Gert Cuykens :
>   SELECT t.pid,
>          t.txt,
>          t.price,
>          t.qty - IFNULL(qs.qty_sold, 0) 'onhand_qty'
>     FROM PRODUCTS t
> LEFT JOIN (SELECT o.pid,
>                  SUM(o.qty) 'qty_sold'
>             FROM ORDERS o) qs ON qs.pid = t.pid
>    WHERE t.pid = ?
>
> i have trouble running this statement on sqlite3
> It tels me qs.pid does not exist, when i rename it to pid it subtracts
> the wrong values when ORDERS contains one record and PRODUCTS more the
> one?

It's late, so I am not putting in the time to generate test data in
your schema, but you should probably ensure that your result column in
your result table is aliased properly:

  SELECT t.pid,
 t.txt,
 t.price,
 t.qty - IFNULL(qs.qty_sold, 0) 'onhand_qty'
FROM PRODUCTS t
LEFT JOIN (SELECT o.pid as pid, -- alias this column
 SUM(o.qty) 'qty_sold'
FROM ORDERS o) qs ON qs.pid = t.pid
   WHERE t.pid = ?;

it certainly resolves error messages about "qs.pid does not exist".

>
>
> CREATE TABLE PRODUCTS (
>    pid     INTEGER PRIMARY KEY,
>    txt     VARCHAR(64),
>    price   BIGINT UNSIGNED,
>    qty     BIGINT
> );
>
> CREATE TABLE ORDERS (
>    oid     INTEGER,
>    pid     BIGINT UNSIGNED,
>    qty     BIGINT UNSIGNED,
>    time   DATETIME,
>    PRIMARY KEY(oid,pid)
> );

You should also be aware that single quotes are delimiters for
literals, not for identifiers. For identifiers use double quotes.
(SQLite is tolerant of the usage in the case above).

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

Re: [sqlite] One more SQL statement question

[Simon Davies]

2009/9/10 Dennis Volodomanov :
> Hello,
>
> I have one more SQL query question - the people on this list have been very 
> helpful in the past, so thank you!
>
> I'm trying to create a trigger that would delete unreferenced rows from a 
> table.
>
> Let's say the schema is like this:
>
> TABLEA ( ID INTEGER PRIMARY KEY, REFB INTEGER );
> TABLEB ( ID INTEGER PRIMARY KEY, DATA );
>
> And let's say the data is like this:
>
> TABLEA:
> 1|1
>
> TABLEB:
> 1|dataA
> 2|dataB
>
> Now, I update the record in TABLEA and change REFB to be REFB=2
>
> I've got the following trigger, which doesn't work:
>
> CREATE TRIGGER UpdateData AFTER UPDATE ON TABLEA
> BEGIN
>        DELETE FROM TABLEB WHERE TABLEB.ID IN ( SELECT TABLEB.ID FROM TABLEB 
> LEFT JOIN TABLEA ON TABLEA.REFB=TABLEB.ID WHERE TABLEB.ID=OLD.REFB AND 
> TABLEB.ID NOT IN ( TABLEA.REFB ) );
> END
>
> If I run just the SELECT statement within the brackets above (supplying 1 
> instead of OLD.REFB of course), it seems to produce the correct row number 
> (1), but if I run the complete DELETE statement, nothing happens.

This is simpler and appears to do what you want:


sqlite> create table TABLEA ( ID INTEGER PRIMARY KEY, REFB INTEGER );
sqlite> create table TABLEB ( ID INTEGER PRIMARY KEY, DATA );
sqlite> insert into tableb values( 1, 'data1' );
sqlite> insert into tableb values( 2, 'data2' );
sqlite> insert into tablea values( 1, 1 );
sqlite>
sqlite> select * from tableb;
1|data1
2|data2
sqlite>
sqlite> select * from tablea;
1|1
sqlite>
sqlite> create trigger UpdateData after update on tablea begin
   ...> delete from tableb where id=old.refb and id not in ( select
refb from tablea );
   ...> end;
sqlite>
sqlite>
sqlite> update tablea set refb=2;
sqlite>
sqlite>
sqlite> select * from tableb;
2|data2
sqlite>


>
> Thank you in advance,
>
>   Dennis

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

Re: [sqlite] Integer Storage class

[Simon Davies]

2009/9/2 Sebastian Bermudez :
> Ok. my problem is my SQLITE front end (SQLITE ADMINISTRATOR v 0.8.3.2) ... 
> show me 0 (cero) in that column.

Looks like SQLITE ADMINISTRATOR v 0.8.3.2 only deals with signed 32
bit values. 2147483647 it accepts as a valid value, 2147483648 it
rejects, saying that it is not a valid integer.

>
> thanks
>

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

Re: [sqlite] Integer Storage class

[Simon Davies]

2009/9/2 Sebastian Bermudez :
> hi! i have an table ( articles ) with a column for EAN13 Barcodes like (
> 7790080066784).
> I have created the column with Integer data type... ( i have chose that data 
> type after read the SQLITE DOC where it' say: "INTEGER. The value is a signed 
> integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of 
> the value.") but when i insert this value for that column... i get 0 (cero) 
> stored in that place...

SQLite version 3.6.16
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>
sqlite> create table tst( c1 integer );
sqlite>
sqlite> insert into tst values( 7790080066784 );
sqlite>
sqlite> select * from tst;
7790080066784
sqlite>

No apparent problem...

>
>
> Have any idea ?

You need to provide more information on what you are doing.

>
> Att.
> Sebastian
>
> PD: Sorry i don't study english
>

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

Re: [sqlite] triggers question, OLD reference is not available after SET statment

[Simon Davies]

2009/8/27 Jarod Tang :
> Hi List users,
>
> In my code, i create two triggers (Trigger_A, Trigger_B) on a table (
> sample_db), but after execution, it's found that Trigger_A is not executed
> correctly ( old.msg_box hasnt be put into table log ). And it seems to me
> that SqlLite invalids the OLD reference after it found it be referred in as
> SET statement, so that it cant be get used by later triggers, or are there
> else explanation for this?
>
> Thanks,
> -- Jarod
>
> code snips as follows
> A:
> db.execSQL("CREATE TRIGGER Trigger_A + AFTER UPDATE OF msg_box ON sample_db
> WHEN ( condition) +
>                "BEGIN " +
>                "  INSERT INTO Log ( old.msg_box )"

invalid syntax ( no "VALUES" keyword ).

>                "END;");
> B:
> db.execSQL("CREATE TRIGGER Trigger_B + AFTER UPDATE OF msg_box ON sample_db
> WHEN ( condition) +
>                "BEGIN " +
>                "  UPDATE sample_db SET msg_box = -old.msg_box " +
>                "  WHERE _id = old._id; " +
>                "END;");
>
>

Using the sqlite shell:

SQLite version 3.6.16
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

sqlite> create table tst( id integer primary key, data text );
sqlite>
sqlite> create table log( data text );
sqlite>
sqlite> create trigger trig2 after update on tst begin update tst set
data = old.data where id=old.id; end;
sqlite> create trigger trig1 after update on tst begin insert into log
values( old.data ); end;
sqlite>
sqlite> insert into tst( data ) values( 'tstRow1' );
sqlite> insert into tst( data ) values( 'tstRow2' );
sqlite> insert into tst( data ) values( 'tstRow3' );
sqlite>
sqlite> update tst set data='updatedTstRow2' where id=2;
sqlite>
sqlite>
sqlite> select * from log;
tstRow2
updatedTstRow2
sqlite>
sqlite>
sqlite> select * from tst;
1|tstRow1
2|tstRow2
3|tstRow3
sqlite>


All seems to work ok.

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

Re: [sqlite] how to make journal file roll back to my database file?

[Simon Davies]

2009/8/26 Zhanjun You :
> hello all,
>        I have read the article "
> http://www.sqlite.org/fileformat.html#journal_file_formats; and "
> http://www.sqlite.org/fileformat.html#rollback_journal_method",but I am even
> more confused, anyone have some sample code for rollback_journal_method.

>From http://www.sqlite.org/docs.html
"SQLite File Format
A description of the format used for SQLite database and journal
files, and other details required to create software to read and write
SQLite databases without using SQLite. "

Your subject line suggests that you are just concerned with rollback
from the journal, not writing your own SQLite file writer.

SQLite takes care of all the details for you. It will use the journal
should it be necessary to do so when it opens the database file.

See section 4.0 (Rollback) in http://www.sqlite.org/atomiccommit.html

>
> thanks.
>
> simon

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

Re: [sqlite] Re triving the database name from the sqlite pointer ....

[Simon Davies]

2009/8/18 Atul_Vaidya :
>
> Hi,
>   I have a Sqlite3 pointer. Is there any way to get the filename of this
> pointer ?

Execute "PRAGMA database_list;"

See http://www.sqlite.org/pragma.html#pragma_database_list

> Regards,
> Atul

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

Re: [sqlite] Error 14 - SQLITE_CANTOPEN

[Simon Davies]

2009/8/13 Simon Slavin :
>
> On 13 Aug 2009, at 12:47pm, Otto Grunewald wrote:
>
>> on the website at the following address:
>>
>> http://www.sqlite.org/c3ref/exec.html
>>
>> The second paragraph states the following:
>>
>> The error message passed back through the 5th parameter is held in
>> memory obtained from sqlite3_malloc(). To avoid a memory leak, the
>> calling application should call sqlite3_free() on any error message
>> returned through the 5th parameter when it has finished using the
>> error
>> message.
>
> I raised this question a couple of weeks ago.  It would be nice to
> have a very clear definition of when you need to use _free.  For
> instance is it exactly when _exec doesn't return SQLITE_OK ?  If _step
> returns SQLITE_BUSY, does that mean there's an error message to be
> freed ?  If I call _free when there's no error, would that be a
> problem ?
>
> Simon.

I set errmsg arg passed in to _exec to 0. If it is changed after exec,
then I call _free

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

Re: [sqlite] Merging blobs on disk without taking up memory???

[Simon Davies]

2009/8/3 sorka :
>
> Hi. I have a table that stores pieces of an image as a bunch of blobs. I get
> the pieces out of order and store them in a table until I get all of the
> pieces I need. I then want to assemble them in order and store the resulting
> complete image in in another table entirely.
>
> Is there a smart way to go about this so that memory use is limited? A
> select and order by the part number will return each blob in order. Is there
> a way to use concat to build up the final blob in the other table without
> having to store all the pieces in memory first?

use blob incremental io:

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

>
> Thanks.
> --

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

Re: [sqlite] Some SQL statements are not executing

[Simon Davies]

2009/7/20 Gary Verhaegen :
> Hi everybody,
.
.
.
> I have narrowed the problem to the few following lines :
>
>
> #define DEBUG
>
> int db_execute(sqlite3 *db, char stmt[STR_LEN]) {
> DEBUG   PRINT("\nentering db_execute with statement %s.", stmt);
>       int ret=0;
>       sqlite3_stmt *s;
>       int err = sqlite3_prepare_v2(db, stmt, -1,,0);
> DEBUG   if(err != SQLITE_OK) {
> DEBUG           PRINT("\nSQLite error %d.\n", err);
> DEBUG           PRINT(stmt);
> DEBUG           exit(err);
> DEBUG   }
>       if(sqlite3_step(s) == SQLITE_ROW)

no error checking here

>               ret = sqlite3_column_int(s,0);
>       sqlite3_finalize(s);
.
.
.
> So, am I doing something wrong with my db_execute wrapper ? Does
> anyone know of such a bug as a statement that does not get executed
> while the returned error code is SQLITE_OK ?

As indicated inline, you are not handling error returns from sqlite3_step()

> Should I step more than
> one time through a DELETE or an UPDATE statement ?

No

>
> Thanks in advance to anyone who took the time to read this, and my
> apologies for my bad grammar - I am not a native English speaker.

This may not be the whole story, but ensure that you are not getting
an error returned.

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

Re: [sqlite] Is there a substitute for Sqlite3_get_table ?

[Simon Davies]

2009/7/17 Atul_Vaidya :
>
> Hi,
.
.
.
> My question is, is it possible in
> SQlite to retrieve the data stored in the database in the format in which it
> is stored when the data was inserted in it ? for eg: if I store the data as
> an Int, will I get it back as an Int, from what i get from the get_table
> function is a char**.If yes,
> then will this enhance the speed of querying and how to achieve this,without
> using sqlite3_get_table ?
> Regards,
> Atul.
>

http://www.sqlite.org/cintro.html will help.

Also see http://www.sqlite.org/cvstrac/wiki?p=SimpleCode for example of use.

Speed should be better, since some type conversions are avoided; but
type conversions are a minimal time cost compared to that of disk
reads.

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

Re: [sqlite] How to create a table that has a field that can hold a 64-bit interger

[Simon Davies]

2009/7/15 Dieter Dasberg :
> Hello,
>
.
.
.
>
> This
>
> create table test (Afield INTEGER)
>
> leads always to a data-size of 4 byte,

What makes you think this?

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

> what I need is a data-size of 8 byte.
>
> What can I do?
>
> Best regards,
>
> Dieter

SQLite version 3.4.2
Enter ".help" for instructions
sqlite>  create table tst( data integer );
sqlite> insert into tst( data ) values( 1 );
sqlite> insert into tst( data ) values( 9223372036854775807 );
sqlite> insert into tst( data ) values( 9223372036854775808 );
sqlite>
sqlite>
sqlite> select data, typeof( data ) from tst;
1|integer
9223372036854775807|integer
9.22337203685478e+18|real
sqlite>

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

Re: [sqlite] SQLITE is not working in pthread

[Simon Davies]

2009/7/10 Pramoda M. A :
>
> Hi,
>
>  We r using Fedora Linux 10. Our application is, when any device is inserted, 
> we will detect that create thread using pthread_create API. Which will find 
> all MP3 files in the device and extract the metadata present in the mp3 files 
> and fill a structre and send it to sqlite.
>
>
>
> Before that, I will create database like;
>
.
.
.
>
> This will run in thread. But here, "entered"  and "entered 2"is printing.  
> But "entered 3" and "printing 4" both are not printing.
>
> After that, if I tried to query the database using command line tools, 
> "Select * from File", it is giving error as
>
> "encrypted file or is not a database"
>
>
>
> Please help me.
>
> Thanks & Regards
>
> Pramoda.M.A
>

Hi Pramoda,

Your code as posted fails to compile because of "error C2181: illegal
else without matching if": look at 'printf("entered 3");'

Does your code work perfectly if not run in a thread?

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

Re: [sqlite] Query by Day

[Simon Davies]

2009/7/6 Rick Ratchford :
> Greetings!
>

Hi Rick,

> I'm having trouble with what I thought would be a simple SQL query.
>
>    SQLString = "SELECT strftime('%d', Date) as Day, IsSwingTop1 as Tops,
> IsSwingBtm1 as Btms " & _
>                "FROM TmpTable WHERE Day = 11"
>
> I'm trying to create a recordset where the only records returned are those
> that fall on the 11th of the month.
>
> When I run this without the 'WHERE Day = 11", it returns all the records in
> 3 columns, Day, IsSwingTop1 and IsSwingBtm1. Under the Day column, I can see
> all the days as 01, 02...31.

That's good...

>
> I'm not sure where I'm making my logic error.

...and you think that there is a problem because?

It would really help if you explained what leads you to believe that
there is a logic error.

>
> Thanks.
>
> Rick
>

If you use typeof() around "strftime('%d',date)", it shows type TEXT.
You are comparing this with an INTEGER; never true.

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

Re: [sqlite] Column headers of result

[Simon Davies]

2009/7/5 BareFeet :
>>>
> Hi Simon,
>
>>> How can I get just the column headers without all the result rows?
>>
>> Turn headers on, then perform a search which gives no results.
>
> Unfortunately, the sqlite3 command line tool does not show the headers
> when there are no result rows. In any case, it's nit possible to
> convert an ad hoc query (ie not known beforehand) into one that
> returns no rows.

Add "limit 0" to the end of the select statement

>
>> It's difficult in the command-line tool, but easy using function calls
>> since there's a function call specially intended for it:
>>
>> 
>
> It would be great to see a pragma or built in SQL function that
> returned the column headers of a given query.

This is only a limitation in the shell (or using sqlite3_exec); column
headers can be queried from prepared statements as already pointed
out.

>
>>
> Thanks,
> Tom
> BareFeet

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

Re: [sqlite] Value returned by sqlite3_column_bytes for strings

[Simon Davies]

2009/7/3 chandan :
> Hi all,
>    Consider the following scenario,
>    1. A table contains a column of type "text".
>    2. The value of this column for the first row is say "linux".
>
> If we execute the SQL statement: "select name from some_tbl where id = ?"
> using sqlite3_step() API, then what is the value returned by
> sqlite3_column_bytes(). Does the count include the '\0' byte (count ==
> 6). If it does not include the '\0' byte the count should be 5.
>
> I executed the following program:
>
> /**/
> #include 
> #include 
> #include 
> #include 
> #include "sqlite3.h"
>
> const char *update_db = "update some_tbl set name = ? where id = ?";
> const char *read_db = "select name from some_tbl where id = ?";
>
> int32_t main(int argc, char *argv[])
> {
>    sqlite3_stmt *stmt;
>    sqlite3 *db;
>    int32_t num_bytes;
>    char buf[100];
>    int32_t ret;
>
>    if (argc != 2) {
>        fprintf(stderr, "Usage: %s \n", argv[0]);
>        goto out1;
>    }
>
>    ret = sqlite3_initialize();
>    if (ret != SQLITE_OK) {
>        fprintf(stderr, "Unable to initialize db.\n");
>        goto out1;
>    }
>
>    ret = sqlite3_open(argv[1], );
>    if (ret != SQLITE_OK) {
>        fprintf(stderr, "Unable to open database.\n");
>        goto out2;
>    }
>
>    stmt = NULL;
>    ret = sqlite3_prepare_v2(db, update_db, strlen(update_db) + 1,
>                 , NULL);
>    if (ret != SQLITE_OK) {
>        fprintf(stderr, "sqlite3_prepare_v2: %s.\n",
>            sqlite3_errmsg(db));
>        goto out3;
>    }
>
>    ret = sqlite3_bind_text(stmt, 1, "linux", strlen("linux") + 1,
>                SQLITE_TRANSIENT);

This will insert 6 bytes into the db - includes the trailing '\0'

>    if (ret != SQLITE_OK) {
>        fprintf(stderr, "sqlite3_bind_text: %s.\n",
>            sqlite3_errmsg(db));
>        goto out4;
>    }
>
>    ret = sqlite3_bind_int64(stmt, 2, 1);
>    if (ret != SQLITE_OK) {
>        fprintf(stderr, "sqlite3_bind_int64: %s.\n",
>            sqlite3_errmsg(db));
>        goto out4;
>    }
>
>    ret = sqlite3_step(stmt);
>    if (ret != SQLITE_DONE) {
>        fprintf(stderr, "sqlite3_step: %s.\n",
>            sqlite3_errmsg(db));
>        goto out4;
>    }
>
>    ret = sqlite3_finalize(stmt);
>    if (ret != SQLITE_OK) {
>        fprintf(stderr, "sqlite3_finalize: %s.\n",
>            sqlite3_errmsg(db));
>    }
>
>    stmt = NULL;
>    ret = sqlite3_prepare_v2(db, read_db, strlen(read_db) + 1, ,
>                 NULL);
>    if (ret != SQLITE_OK) {
>        fprintf(stderr, "sqlite3_prepare_v2: %s.\n",
>            sqlite3_errmsg(db));
>        goto out3;
>    }
>
>    ret = sqlite3_bind_int64(stmt, 1, 1);
>    if (ret != SQLITE_OK) {
>        fprintf(stderr, "sqlite3_bind_int64: %s.\n",
>            sqlite3_errmsg(db));
>        goto out4;
>    }
>
>    ret = sqlite3_step(stmt);
>    if (ret != SQLITE_ROW) {
>        fprintf(stderr, "sqlite3_step: %s.\n",
>            sqlite3_errmsg(db));
>        goto out4;
>    }
>
>    num_bytes = sqlite3_column_bytes(stmt, 0);
>    printf("*** num_bytes = %d ***\n", num_bytes);
>
>    memcpy(buf, sqlite3_column_text(stmt, 0), num_bytes);
>    printf("*** buf = %s ***\n", buf);
>
>    exit(0);
>
>  out4:
>    ret = sqlite3_finalize(stmt);
>  out3:
>    ret = sqlite3_close(db);
>  out2:
>    ret = sqlite3_shutdown();
>  out1:
>    exit(1);
> }
> /*/
>
>
> The output shows that sqlite3_column_bytes() returns a count value that
> includes the '\0'. Please correct me if I am arriving at the wrong
> conclusion.

This is what I would expect given that you are inserting a string that
includes the '\0'. Your initial statement that the db contains 'linux'
is wrong; it contains 'linux\0'.

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

Re: [sqlite] Sqlite-3.5.9: getting sqlite_autoindex error

[Simon Davies]

2009/6/19 hiral :
> Hi Simon / John,
>
> Thank you for replies.
>
>> You are able to repeatably corrupt the db under SQLite 3.5.9? It would
>> be worthwhile publishing a script that can do that.
>  > "was getting error often" or "did get error ONCE"??
> I am running application which uses the db over the NFS. If I move the db
> over the local drive then it's working fine.
> So I m observing this bug in NFS environment frequently.
> In the same test environment (in which I am getting db corrupted), if I use
> the sqlite-3.6.4 and above it works perfectly fine.
>
>
.
.
snip
.
.
> Yes, 'disk image malformed' message I am observing on NFS.
> Environment:
> CentOS 4.4
> nfs-utils-lib-1.0.6-3
> system-config-nfs-1.2.8-1
> nfs-utils-1.0.6-70.EL4
> nfs-utils-lib-devel-1.0.6-3
> nfs-utils-1.0.6-70.EL4
> - db is on NFS drive, but not shared by thread/processes/users
> - not using thread
>
> Some interesting information:
> When I debuged the sqlite-3.5.9 code, and observed that it's getting
> corrupted at following condition...

This is where corruption is detected, not where caused.

> - in sqlite3BtreeInitPage() it returns with SQLITE_CORRUPT_BKPT based on
> some condition (see below)...
>
> SQLITE_PRIVATE int sqlite3BtreeInitPage(
>  MemPage *pPage,        /* The page to be initialized */
>  MemPage *pParent       /* The parent.  Might be NULL */
> ){
>     ...
>     if( pPage->nCell==0 && pParent!=0 && pParent->pgno!=1 ){
>     /* All pages must have at least one cell, except for root pages */
>     return SQLITE_CORRUPT_BKPT;
>     }
>     ...
> }
>
> I would appreciate your help in debuging this bug.

If you have found a version that does not manifest the 'bug' why not
use that? (It has already been debugged!)

> Thank you.
> -Hiral
>

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

Re: [sqlite] Sqlite-3.5.9: getting sqlite_autoindex error

[Simon Davies]

2009/6/18 hiral :
> Hi Simon,
>
> Thank you for your quick reply.
>
> I am sorry for more general questions.
>
> As I mentioned I was getting corrupted db error with sqlite-3.5.9, but when
> I tried with sqlite-3.6.4 it is no more corrupting the db.
> -- so was it a bug with sqlite-3.5.9 ? and got fixed in sqlite-3.6.4

You are able to repeatably corrupt the db under SQLite 3.5.9? It would
be worthwhile publishing a script that can do that.

I see nothing in the change list (http://www.sqlite.org/changes.html)
concerning such a major bugfix going from 3.5.9 to 3.6.4

> -- is it safe to upgrade from sqlite-3.5.9  to sqlite-3.6.14.2 ? as I am
> working on critical application how safe it is ?

I am in no position to speak authoritatively; I think you should get
the latest version and test thoroughly.

>
> Thank you.
> -Hiral

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

Re: [sqlite] Sqlite-3.5.9: getting sqlite_autoindex error

[Simon Davies]

2009/6/17 hiral :
> Hi Simon,
>
>  I looked into it but couldn't find the exact reason for my bug.
>
>  If you have any idea please let me know.

Sorry, other than the link, I can not help.

>
>  Also I would appreciate, if you can let me know what does this error mean
> in terms of btree/pager module of sqlite
>
>>> sqlite> PRAGMA integrity_check;
>>> *** in database main ***
>>> Page 5275 is never used
>>> wrong # of entries in index sqlite_autoindex__1

Other than that the db is corrupt, I could not say.
If I ever get a corrupted db (from users with unsafe environments), I
extract whatever data it is possible to extract ( see .dump in sqlite3
command line utility), and reconstruct a new db using that data.
It has not seemed worthwhile trying to analyse/repair a corrupt db.
Identifying where the db has been corrupted will not necessarily
indicate the event that caused it.

> Thank you.
> -Hiral

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

Re: [sqlite] Sqlite-3.5.9: getting sqlite_autoindex error

[Simon Davies]

2009/6/17 hiral :
> Any suggestion on this !!!

Your database has been corrupted.
http://www.sqlite.org/lockingv3.html#how_to_corrupt

Rgds,
Simon

>
> On Tue, Jun 16, 2009 at 4:34 PM, h o  wrote:
>
>> Hi,
>>
>> I am using sqlite-3.5.9 and observing a 'disk image malformed' error
>> nfs, on doing 'PRAGMA integrity_check' I got following messages...
>>
>> SQLite version 3.5.9
>> Enter ".help" for instructions
>> sqlite> PRAGMA integrity_check;
>> *** in database main ***
>> Page 5275 is never used
>> wrong # of entries in index sqlite_autoindex__1
>> sqlite>
>>
>> Can you please let me know what is the problem here.
>>
>> Thank you.
>> -Hiral
>>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How does SQLite handle newlines in values?

[Simon Davies]

2009/6/13 Florian v. Savigny :
>
>
> I'm very sorry if this is a very stupid question. Intuitively, I would
> assume that of course, any TEXT or BLOB field may contain
> newlines. I'm, however, puzzled about two things (it all refers to the
> commandline interface):
>
> - while I can insert values with newlines by using the X'ABCD'
>  notation, i.e. saying sth like
>
>  INSERT INTO my_table VALUES (x'0a');
>
>  it does not seem possible to use a newline literally in a string,
>  e.g. like this:
>
>  INSERT INTO my_table VALUES ('
> ');
>

What problems does it cause you?

I have no problems:

SQLite version 3.4.2
Enter ".help" for instructions
sqlite>
sqlite>
sqlite> create table tst( id integer primary key, data text );
sqlite>
sqlite> insert into tst( data ) values( 'line1
   ...> line2
   ...> line3
   ...> line4' );
sqlite>
sqlite>
sqlite>
sqlite> select data from tst;
line1
line2
line3
line4
sqlite> select hex( data ) from tst;
6C696E65310A6C696E65320A6C696E65330A6C696E6534
sqlite>

>  But does this mean you can only specify newlines in x'abcd'
>  notation? Or even, that SQLite stores any string with newlines as a
>  BLOB?

No

>
> - (Almost the same question) When I use quote() to SELECT some
>  columns, is it conceivable that SQLite returns something like this
>  (assuming line mode):
>
>  quote(text) = '
> '
>  Or will it always use the X'ABCD' notation to output newlines?

See above

>
>  (I have already found out that newlines stored as blobs are returned
>  literally if you do not use quote(), but as I said, I haven't been
>  able to store newlines as text, so I could not test how they are
>  returned.)
>
>
> The background of my question is not curiosity, but the fact that I
> have designed an Elisp interface that uses SQLite's commandline
> interface with quote() and parses its line output. As long as the
> values cannot contain newlines, that's quite straightforward, but if
> they can, it's suddenly very intricate. Thus, it's the output that I'm
> chiefly concerned about, but also the fact whether I always have to
> use the X'ABCD' notation to insert a value that contains newlines (and
> perhaps also carriage returns, which I haven't tested).
>
> I'm sorry if I merely haven't found the pertinent documentation, but I
> have rummaged the docs.
>
> Thanks for any help here!
>
> Florian
>

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

Re: [sqlite] sqlite programmed in C++

[Simon Davies]

2009/6/2 Sylvain Pointeau :
> Hello,
> I would like to know if someone already though about to introduce C++ in
> SQLite?
> I just think about a minimal subset of C++ that will not make any
> performance penalty
> (like C with classes)

Prob good idea to look through these:
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers

>
> is it a performance issue?
> is it a deployment/compiler issue?
> or any issue?
>
> Please don't make any aggressive reply, I am a very nice guy :-)
>
> Cheers,
> Sylvain

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

Re: [sqlite] Select on foreign key NULL

[Simon Davies]

2009/5/26 Leo Freitag :
> Hallo,
>
> I got some problems with a select on a foreign key with value null.
> I want to filter all male singers.
>
> CREATE TABLE 'tblsinger' ('id' INTEGER PRIMARY KEY, 'name' TEXT,
> 'fkvoice' INTEGER, 'sex' TEXT);
> INSERT INTO "tblsinger" VALUES(1,'Anna Netrebko',1, 'f');
> INSERT INTO "tblsinger" VALUES(2,'Hanna Schwarz',2, 'f');
> INSERT INTO "tblsinger" VALUES(3,'Luciano Pavarotti', 3, 'm');
> INSERT INTO "tblsinger" VALUES(4,'Robert Lloyd', 4, 'm');
> INSERT INTO "tblsinger" VALUES(5,'Robby Williams', null, 'm');
>
> CREATE TABLE 'tblvoice' ('id' INTEGER PRIMARY KEY, 'voice' TEXT);
> INSERT INTO "tblvoice" VALUES(1,'sopran');
> INSERT INTO "tblvoice" VALUES(2,'alt');
> INSERT INTO "tblvoice" VALUES(3,'tenor');
> INSERT INTO "tblvoice" VALUES(4,'bass');
>
> SELECT tblsinger.name, tblsinger.sex, tblvoice.voice FROM tblsinger,
> tblvoice
> WHERE tblsinger.sex = 'm' AND tblsinger.fkvoice = tblvoice.id;
>
> -- Result
>
> Luciano Pavarotti | m | tenor
> Robert Lloyd      | m | bass
>
> -- How do I have to modify the select statement to get the result below:
>
> Luciano Pavarotti | m | tenor
> Robert Lloyd      | m | bass
> Robby Williams    | m |

Use a left join:

 SELECT tblsinger.name, tblsinger.sex, tblvoice.voice
 FROM tblsinger left join tblvoice
 ON tblsinger.fkvoice = tblvoice.id
 WHERE tblsinger.sex = 'm';

>
> Thanks in advance
> Leo
>

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

Re: [sqlite] setting a date in a BEFORE INSERT trigger

[Simon Davies]

2009/5/9 Sam Carleton :
> This is my first BEFORE INSERT trigger in SQLite and I am getting an error:
>
> SQL error: near "new": syntax error
>
> My goal is that on an insert only the insertedby value is provide.
> The trigger will set that to the updatedby, insertedon and updatedon
> fields.  I searched the web and the only examples I could find was of
> an AFTER INSERT, am I better off with that approach?  I would think
> not.

An UPDATE on a record can only work AFTER it has been inserted.

>
> Here is SQL for the table and trigger:
> ---
> CREATE TABLE Customer (
>        CustomerId INTEGER PRIMARY KEY AUTOINCREMENT,
>        IsInSlideShow INTEGER NOT NULL,
>        Username CHAR(50) NOT NULL,
>        Password CHAR(50),
>        insertedby CHAR(50) NOT NULL,
>        instertedon DATE NOT NULL,
>        updatedby CHAR(50) NOT NULL,
>        updatedon DATE NOT NULL,
>        UNIQUE (username));
>
> CREATE TRIGGER ti_Customer BEFORE INSERT ON Customer
> BEGIN
>  new.instertedon = DATETIME('NOW');
>  new.updatedon = new.instertedon;
>  new.updatedby = new.insertedby;
> END;

The trigger must be a valid sql statement.

As in:

sqlite> create table tst( id integer primary key, data real,
insertDate real, updateDate real );
sqlite> create trigger tstTrig after insert on tst begin update tst
set updateDate=insertDate where id=new.id; end;
sqlite>
sqlite> insert into tst( data, insertDate ) values( 1, julianday( '2009-05-09 12
:00:00' ) );
sqlite> insert into tst( data, insertDate ) values( 2, julianday( '2009-05-09 13
:00:00' ) );
sqlite>
sqlite> select * from tst;
1|1.0|2454961.0|2454961.0
2|2.0|2454961.0417|2454961.0417
sqlite>

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

Re: [sqlite] Newbie trying to list resultSet with C

[Simon Davies]

2009/5/8 Simon Davies <simon.james.dav...@googlemail.com>:
> 2009/5/8 Nuno Magalhães <nunomagalh...@eu.ipp.pt>:
>> Greetings.
>>
>> I've managed to compile the example, after installing the amalgamation
>> and using -lsqlite3 in gcc, otherwise it'll complain about undefined
>> references.
>>
>> I can't figure out how to read a simple result set. I know i shoud use
>> sqlite3_exec and/or sqlite3_step and i'm required to have a
>> sqlite3_stmt* somewhere, but i can't find good examples and lots of
>> the ones i find use sqlite3_prepare_v2, which i think is deprecated
>> for SQLite3...
>
> No - see http://www.sqlite.org/c3ref/prepare.html
>
>>
>> Can someone please give me some nice RTFM links will good basic
>> tutorials for the C API? Ones that include the aforementioned task
>> preferably ;)
>
> See http://www.sqlite.org/cintro.html
>
> The following gives an idea of how to use the prepare/step api:
>
>
> int get_telnr( char** c_telnr, sqlite3* db, char* name )
> {
>   char* sql = "SELECT telnr FROM contacts WHERE name=?;";
>   char* tail;
>   const char* data;
>   sqlite3_stmt* stmt;
>   int rc = sqlite3_prepare_v2( db,
>                                           sql,
>                                           strlen( sql ),
>                                           ,
>                                            );
>   if( SQLITE_OK == rc )
>   {
>       rc = sqlite3_bind_text( stmt, 1, name, strlen( name ), SQLITE_STATIC );
>       if( SQLITE_OK == rc )
>       {
>           rc = sqlite3_step( stmt );
>           if( SQLITE_ROW == rc )
>           {
>               data = sqlite3_column_text( stmt, 0 );
>               if( data )
>               {
>                   *c_telnr = (char*)malloc( strlen( data ) + 1 );
>                   strcpy( *c_telnr, data );
>               }
>           }
>       }
Sorry - don't forget to finalize!
 rc = sqlite3_finalize( stmt );
>   }
>   return( rc );
> }
>
>>
>> TIA,
>> Nuno Magalhães
>>
>
> Rgtds,
> Simon
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Newbie trying to list resultSet with C

[Simon Davies]

2009/5/8 Nuno Magalhães :
> Greetings.
>
> I've managed to compile the example, after installing the amalgamation
> and using -lsqlite3 in gcc, otherwise it'll complain about undefined
> references.
>
> I can't figure out how to read a simple result set. I know i shoud use
> sqlite3_exec and/or sqlite3_step and i'm required to have a
> sqlite3_stmt* somewhere, but i can't find good examples and lots of
> the ones i find use sqlite3_prepare_v2, which i think is deprecated
> for SQLite3...

No - see http://www.sqlite.org/c3ref/prepare.html

>
> Can someone please give me some nice RTFM links will good basic
> tutorials for the C API? Ones that include the aforementioned task
> preferably ;)

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

The following gives an idea of how to use the prepare/step api:


int get_telnr( char** c_telnr, sqlite3* db, char* name )
{
   char* sql = "SELECT telnr FROM contacts WHERE name=?;";
   char* tail;
   const char* data;
   sqlite3_stmt* stmt;
   int rc = sqlite3_prepare_v2( db,
   sql,
   strlen( sql ),
   ,
);
   if( SQLITE_OK == rc )
   {
   rc = sqlite3_bind_text( stmt, 1, name, strlen( name ), SQLITE_STATIC );
   if( SQLITE_OK == rc )
   {
   rc = sqlite3_step( stmt );
   if( SQLITE_ROW == rc )
   {
   data = sqlite3_column_text( stmt, 0 );
   if( data )
   {
   *c_telnr = (char*)malloc( strlen( data ) + 1 );
   strcpy( *c_telnr, data );
   }
   }
   }
   }
   return( rc );
}

>
> TIA,
> Nuno Magalhães
>

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

Re: [sqlite] Combining .output and .read in a batch file

[Simon Davies]

2009/5/7 Leo Freitag :
> Hallo,
>
> I try to get run the following in a batch file
>
> Open database "test.db"
> Set output to "o.txt"
> Read sql-statement form "r.sql"
>
> === r.sql - Start ===
> select * from table1;
> === r.sql - End ===
>
> Thinks like the following didn't work:
> sqlite3 test.db .output o.txt .read r.sql

Only 1 dot command can be supplied in the first command string.

Make the first line of r.sql ".output o.txt"

Then:
   sqlite3 test.db ".read r.sql"

>
>
> Thanks for your help.
> Leo
>

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

Re: [sqlite] in memory database and jdbc

[Simon Davies]

2009/4/29 Tom van Ees :
> Hi,
>
> my java application uses a 80Mb reference database (read-only) that needs to 
> be consulted app. 4M times during a batch run. I would like to use the 
> in-memory capabilities of sqlite3 to improve the performance of my app.
>
> In order to do so, I setup a Connection to the -in-memory- sqlite database 
> and next I would like to load the file-version of the reference database as 
> efficiently as possible into the in-memory version. What would be the 
> practical approach? The .restore/.backup commands do not seem to agree with 
> the jdbc-driver.

Perhaps jdbc-driver needs update to implement  .restore/.backup?

Anyway, the following tcl script should give an idea of what would work...

# create memory db connection
#
sqlite3 db :memory:

# attach to the source file
#
db eval "attach 'mySource.db' as srcDb;"

# copy schema tables
#
db eval "
select sql from srcDb.sqlite_master
where   type = 'table';
" {
db eval "$sql"
  }

# copy the data for all tables
#
db eval "
select name from srcDb.sqlite_master
where type = 'table';
" {
db eval "insert into $name select * from srcDb.$name;"
  }

# copy schema views, indexes and triggers (+any other non-tables that
I have not thought of)
#
db eval "
select sql from srcDb.sqlite_master
where   type <> 'table';
" {
db eval "$sql"
  }

#  tidy up
#
db eval "detach srcDb;"

>
> regards
>
> Tom
>

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

Re: [sqlite] Error Binding Parameter to Compiled Statement

[Simon Davies]

2009/4/17 jonwood :
>
> Greetings,
>
> I'm getting an error compiling a parameter to a compiled statement.
> Unfortunately, since I'm using a customer wrapper class around sqlite,
> posting my code won't be much help. Here's what I'm doing:
>
> I create a compiled statement (two, actually).
>
> Then, each time through a loop, I bind an argument to the compiled
> statement, and then execute it.
>
> The first time through the loop, it works fine. But the second time through
> the loop, I get the following error when I attempt to bind the argument to
> the compiled statement.
>
> "Error binding integer parameter : library routine called out of sequence
> (error 21)"
>
> It may be something else wrong with my code but I just wanted to get a
> lucidity check on the approach I'm taking.
>
> Thanks.
> --

Is sqlite3_reset() being called before looping around?

viz:

sqlite3_prepare_v2()
while( moreWork )
{
int status = SQLITE_ROW;
sqlite3_bind_xxx()
while( SQLITE_ROW == status )
{
status = sqlite3_step()
processRowData()
}
sqlite3_reset()
}

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

Re: [sqlite] select with a like containing a line feed

[Simon Davies]

2009/4/3 Sylvain Pointeau :
> re-hi,
> but how do we do if we are on unix and there is some CR on those field?
> I cannot insert CR in my statement (only line feed)
>
> do you have any suggestion?
>
> Cheers,
> Sylvain
>

Something like this:

SQLite version 3.4.2
Enter ".help" for instructions
sqlite>
sqlite>  create table tst( id integer primary key, data text );
sqlite>
sqlite> insert into tst( data ) values( '123
   ...> 456' );
sqlite>
sqlite> insert into tst( data ) values( 'abc' );
sqlite>
sqlite> select * from tst where data like '%'||X'0A'||'%';
1|123
456
sqlite> select * from tst where data not like '%'||X'0A'||'%';
2|abc
sqlite>


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

Re: [sqlite] Source code position out of sync debugging in VS 2008

[Simon Davies]

2009/3/28 Vinnie :
>
> I have added sqlite.c to my Visual Studio 2008 project and everything seems 
> to be working in terms of database calls. However, when I step into an actual 
> sqlite routine using the debugger, the source code position is out of sync 
> with the actual location. For example, I step into sqlite3_open_v2() and it 
> takes me to a completely unrelated source code line.
>
> I've already tried rebuilding everything, checked the settings, etc... but 
> nothing seems to help. I get the feeling this is a problem with the file 
> being so large and containing so many symbols. Has anyone else experienced 
> this problem?

Yes

I also get the feeling that it is due to the file size.

Functions near the start of the file are ok to debug, those towards
the end are not.

>
> Thanks
>
> My amalgamation was created on 2009-02-17 21:53:46 UTC


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

Re: [sqlite] Unnecessary line breaks in .dump output

[Simon Davies]

2009/3/27 Simon Davies <simon.james.dav...@googlemail.com>:
> 2009/3/27 Francois Botha <igitur+sql...@gmail.com>:
>>
>> Uhm, sorry. I'm new here.  I don't want to paste the SQL script in an email,
>> because it contains line breaks and I don't want the formatting to change
>> during email transport.
>>
>> Try www.vwd.co.za/sqlitedump.zip
>
> Confirm I get same result you are reporting.
>
> Don't believe it is sqlite issue tho.
>
> shell.c about line 704:
>    fprintf(p->out, "%s;\n", zSql);
>
> At this point zSql contains exactly the right characters. However, in
> the file resulting from the printf, extra '0D' characters appear.
>
> Note that this only occurs with redirection on the command line. Using
> ".output t.sql" at the sqlite shell prompt gives the result I would
> expect.
>
> Maybe people more familiar with micros*t internals could explain...
>
>>
>> regards,
>> Francois
>
> Rgds,
> Simon
>

Forgot to mention:
The extra '0D' characters appear where there are embeded newlines in
the CREATE statements.

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

Re: [sqlite] Unnecessary line breaks in .dump output

[Simon Davies]

2009/3/27 Francois Botha :
> (my apologies if this is a duplicate)
>
>
>> Attachments don't make it through the mailing list,
>>
>
> Uhm, sorry. I'm new here.  I don't want to paste the SQL script in an email,
> because it contains line breaks and I don't want the formatting to change
> during email transport.
>
> Try www.vwd.co.za/sqlitedump.zip

Confirm I get same result you are reporting.

Don't believe it is sqlite issue tho.

shell.c about line 704:
fprintf(p->out, "%s;\n", zSql);

At this point zSql contains exactly the right characters. However, in
the file resulting from the printf, extra '0D' characters appear.

Note that this only occurs with redirection on the command line. Using
".output t.sql" at the sqlite shell prompt gives the result I would
expect.

Maybe people more familiar with micros*t internals could explain...

>
> regards,
> Francois

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

Re: [sqlite] Unnecessary line breaks in .dump output

[Simon Davies]

2009/3/27 Francois Botha :
>
> The example you used, works 100% for me.  I attach a sql script for which I
> experience the problem.
>

Attachments don't make it through the mailing list,

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

Re: [sqlite] Getting field data back from SQLite db

[Simon Davies]

2009/3/16  :
> Hello,
>
> I am new on SQLite so bear with me :-)
>
> Can someone give me a simple c solution on following:
>
> I execute select telnr from contacts where name="David"
>
> I just want to get from the found record the content of field telnr back to 
> my c
> program in variable c_telnr.
>
> Thanks in advance,
>
> Danny
> Belgium
>

Hi Danny,

See http://www.sqlite.org/c3ref/prepare.html
   http://www.sqlite.org/c3ref/bind_blob.html,
   http://www.sqlite.org/c3ref/step.html
   http://www.sqlite.org/c3ref/column_blob.html

Experiment with

int get_telnr( char** c_telnr, sqlite3* db, char* name )
{
char* sql = "SELECT telnr FROM contacts WHERE name=?;";
char* tail;
const char* data;
sqlite3_stmt* stmt;
int rc = sqlite3_prepare_v2( db,
sql,
strlen( sql ),
,
 );
if( SQLITE_OK == rc )
{
rc = sqlite3_bind_text( stmt, 1, name, strlen( name ), SQLITE_STATIC );
if( SQLITE_OK == rc )
{
rc = sqlite3_step( stmt );
if( SQLITE_ROW == rc )
{
data = sqlite3_column_text( stmt, 0 );
if( data )
{
*c_telnr = (char*)malloc( strlen( data ) + 1 );
strcpy( *c_telnr, data );
}
}
}
}
return( rc );
}

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

Re: [sqlite] How to interrupt a running query in SQLite?

[Simon Davies]

2009/3/13 manohar s :
> Thanks for that. I Should have searched that :).
>
> Now I am Executing  the Vacuum query, and sqlite3_interrupt() is called from
> a different thread, It is interrupted.
> But once interrupted query execution is failing with SQLITE_ERROR. It has to
> fail with SQLITE_INTERRUPT right?
>
> sqlite3_errmsg() is returning proper error "[DB: C:\Documents and
> Settings\All Users.WINDOWS\Application Data/data/sqlitedb.data]
> interrupted".
>
> Am I missing anything here?
>
> I am testing with SQLite 3.6.11.
>
> Regards,
> Manohar.S

Are you using the v2 version of prepare?
http://www.sqlite.org/c3ref/prepare.html

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

Re: [sqlite] datetime as integer

[Simon Davies]

2009/3/13 John Machin :
> On 12/03/2009 12:21 AM, Nicolás Solá wrote:
>> Hi I’m using Trac software and it is implemented using SQLITE3. In Trac DB
>> schema there is a table called “milestone”. It has a field called “due” and
>> it means due date. The problem is that it uses an integer data type to store
>> the datum and I don’t know how to show it in a SELECT query as datetime. Can
>> anyone help me please?
>
> Help us to help you: tell us more about this date stored as integer.
> (1) the integer is the number of days since what date?
> or (2) some examples
> e.g. 2008-12-31 is represented as 123456
> 2009-01-01 is represented as 123457
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

We use Trac; it seems to me that the number is in unixepoch. Use
"DATE( due, 'unixepoch' )" to display the date in yyy-mm-dd format.

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

Re: [sqlite] Read back the specified triggers

[Simon Davies]

2009/3/3 Spitzer, Thomas :
> I plan to implement the parametrisation of an embedded device with
> sqlite.
> Therfore I need the triggers heavily, for writing the changed values
> into the hardware.
>
> Testing workes fine, so far. But I did not find a possibility to read
> back the triggers I defined.
>
> There is a possibility to read them back, isn't it?
>
> Greetings
> Thomas
>

Hi Thomas,

Does
select * from sqlite_master where type='trigger';
give you what you want?

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

Re: [sqlite] Unab to close data base due to unfinalized statements

[Simon Davies]

2009/2/11  :
> Hi ,
> i am using sqlite with vc++, i tried to insert a row into data base with 
> prepared statement using bind operations, i reset the statement after using 
> it but when i try to close DB it is giving error "Unable to close data base 
> due to unfinalized statements"

Are you calling sqlite3_finalize on the prepared statement?

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

Re: [sqlite] Programmed backup & restore

[Simon Davies]

2009/2/10 Gorshkov :
> I am developing an application, and I need the ability to create
> backups, and restore, from withing the program.
>
> Telling the user "download sqllite.exe, open a dos box, cd to XXX, and
> then .." isn't an option, but I don't see any easy, straightforward
> way get the information I need regarding fields, data types, etc from
> sqlite_master without having to parse the creation sql - which would be
> a major pain in the butt.
>
> Is there an equivalent to the .dump and .restore commands accessable via
> the C interface, or any data structures/functions exposed to the
> programmer that would allow me to do this?

http://www.sqlite.org/draft/c3ref/backup_finish.html

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


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

Re: [sqlite] Resolving some compiler warnings when closing db

[Simon Davies]

2009/2/3 Billy Gray :
> Hi all,
>
.
.
.
> #import 
> ...
> - (void) closeDb {
>// first loop thru any existing statements and kill'em
>sqlite3_stmt *pStmt;
>while( (pStmt = sqlite3_next_stmt(db, 0)) != 0 ){
>sqlite3_finalize(pStmt);
>}
>
>int result = sqlite3_close(db);
>if (result != SQLITE_OK) {
>NSAssert1(0, @"Failed to close database, returned error code %d",
> result);
>}
>db = nil;
> }
>
> This produces two warnings for the sqlite3_next_stmt line:
>
> - Implicit declaration of sqlite3_next_stmt (which is bizarre...)

The compiler is telling you that there is no declaration of
sqlite3_next_stmt. Have a search in sqlite3.h and see if it is lying.

Supplementary question: are you using a version of sqlite that
implements sqlite3_next_stmt?

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

Re: [sqlite] sqlite3_bind_int returns SQLITE_RANGE

[Simon Davies]

2009/2/2 hussainfarzana :
>
> Dear All,
>
> We are working with SQLite Version 3.6.10.
> We tried to insert or update the records in the database using
> sqlite3_prepare and binding the values using sqlite3_bind functions.We have
> started with the index 1.We have a table with 14 columns and when we use
> sqlite3_bind_int,for the first 13 columns its returning 0 and for the last
> 14th column which is also an integer datatype, but the function is returning
> value 25(SQLITE_RANGE) error.

Your table may have 14 columns, but has the SQL for your prepared
statement got 14 parameter placeholders?

>
> Please help us how to proceed further.
>
> Regards,
> Farzana.

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

Re: [sqlite] Transfer data between databases

[Simon Davies]

2009/1/9 Pierre Chatelier :
> Hello,
>
> This may be a question with a very short answer...
> I have two separate SQLite database files, but containing the same
> kind of tables. Is there a quick way to copy rows from one table of a
> file to the same table of the other file ?
> I suppose "no", and I will have to perform SELECT on one side and
> INSERT on the other. But since one of my column is a blob type, it is
> a little more pain than a simple string copy, because for performance
> I should have to handle the blob with the read/write functions. Right ?
>
> Anyway, are there tricks to know to make such a row transfert simple
> and efficient ?
>

Something like

 attach 'tst2.db' as db2;
 insert into db2.tbl1 select * from main.tbl1;

> Regards,
>
> Pierre Chatelier

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

Re: [sqlite] confusing with how to to this in sqlite

[Simon Davies]

2008/12/23 Rachmat Febfauza :
> =
>
> thanks to Simon. it works. but i have some question. i hope u can help me 
> to explain these :
>
> 1. is it necessary or not to specify min(awal1.begin).

No. "Begin" is part of the 'group by' clause. Each distinct value of
"Begin" will be represented in the result set.

> 2. is "min(strftime("%s",akhir1."End")-strftime("%s",awal1."Begin")) as 
> Difference" is more time consuming to compute than 
> "strftime("%s",min(akhir1."End"))-strftime("%s",awal1."Begin") as 
> Difference". i think these give the same result.

Not sure. Can see no reason for there to be a significant performance
difference.


> 3. akhir1.Category like awal1.Category || '%'. what does it mean? why is use 
> || '%'???. why not to write "awal1.category = akhir1.category". it also give 
> the same result.

It does not give the same result with your originally posted data set.
The like operator matches similar text values; '%' is a wildcard.

See http://www.sqlite.org/lang_expr.html#like

>
> once again, i would say thank you for ur solution. sory for my bad english.
>

You're welcome,
Rgds,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] confusing with how to to this in sqlite

[Simon Davies]

2008/12/21 Rachmat Febfauza :
>
> It looks like you are using the sqlite3 shell, so experiment with .separator
>
> Have you used .help?
>
> Rgds,
> Simon
>
>
> yes i am using sqlite3 shell, i mean not to make display like mysql does, but 
> the difference column that i want like mysql does.
>
> take a look at this different
> A1221|SMALL|FOOD|CAKE|HOMS 1|2007-05-06 11:31:57|2007-05-06 11:42:46|649 
> (this is done with sqlite)
>
> A1221SMALLFOODCAKEHOMS 12007-5-6 11:31:572007-5-6 
> 11:31:570 (this is done with mysql)
>
> why in sqlite we got (649) : 2007-05-06 11:31:57|2007-05-06 11:42:46|649
> and in mysql we got (0) : 2007-5-6 11:31:572007-5-6 11:31:570
>
> or i think the reason is like i found in mysql documentation like this :
> "If you use LEFT JOIN to find rows that do not exist in some table and you 
> have the following test: col_name IS NULL in the WHERE part, where col_name 
> is a column that is declared as NOT NULL, MySQL stops searching for more rows 
> (for a particular key combination) after it has found one row that matches 
> the LEFT JOIN condition. "
>
> i also have test with left join but the result don't like mysql does.
>
> sory for my less knowledge
>
> thanks
>

Without the group by clause the result set is:
sqlite> select awal1.Code, awal1.Level, awal1.Category, awal1.Product,
awal1.Location, awal1."Begin",akhir1."End", strftime("%s",akh
ir1."End")-strftime("%s",awal1."Begin") as Difference from awal1,
akhir1 where awal1.Code = akhir1.Code and akhir1.Category like awa
l1.Category || '%' and awal1.Product = akhir1.Product and
awal1.Location = akhir1.Location and akhir1."End" >= awal1."Begin";
A1220SMALLFOODMARGARINEHOMS 12007-05-06 11:42:46
 2007-05-06 11:42:460
A1221SMALLFOODCAKEHOMS 22007-05-06 11:31:57
2007-05-06 11:31:570
A1221SMALLFOODCAKEHOMS 12007-05-06 11:31:57
2007-05-06 11:31:570
A1221SMALLFOODCAKEHOMS 12007-05-06 11:31:57
2007-05-06 11:42:46649
A1221SMALLFOODCAKEHOMS 12007-05-06 11:42:46
2007-05-06 11:42:460
A1222SMALLFOODWAFERHOMS 22007-05-06 11:20:34
2007-05-06 11:31:57683
A1222SMALLFOODWAFERHOMS 12007-05-06 11:20:34
2007-05-06 11:31:57683
A1222SMALLFOODWAFERHOMS 12007-05-06 11:20:34
2007-05-06 11:42:461332
A1222SMALLFOODWAFERHOMS 12007-05-06 11:42:46
2007-05-06 11:42:460
A1236MEDIUMFOODSNACKHOMS 22007-05-06 10:48:57
2007-05-06 11:19:211824
A1236MEDIUMFOODSNACKHOMS 12007-05-06 10:48:57
2007-05-06 11:19:251828
A1269SMALLCLOTHESBELTHOMS 32007-05-07 17:28:25
2007-05-07 17:28:272

The group by clause combines rows
A1221SMALLFOODCAKEHOMS 12007-05-06 11:31:57
2007-05-06 11:31:570
A1221SMALLFOODCAKEHOMS 12007-05-06 11:31:57
2007-05-06 11:42:46649
into 1 row. The values in the columns not included in the group by
clause ("Begin", "End" and Difference) could be from any of the
combined rows (which rows is not, I believe, specified in any
standard). MySql and Sqlite seem to result in different selections. If
you want specific rows, then you need to modify the query to control
the data selection. In this case it appears that:
select  awal1.Code,
awal1.Level,
awal1.Category,
awal1.Product,
awal1.Location,
awal1."Begin",
min( akhir1."End" ),
min( strftime("%s",akhir1."End")-strftime("%s",awal1."Begin")  ) as 
Difference
fromawal1, akhir1
where   awal1.Code = akhir1.Code and
akhir1.Category like awal1.Category || '%' and
awal1.Product = akhir1.Product and
awal1.Location = akhir1.Location and
akhir1."End" >= awal1."Begin"
group byawal1."Begin",
awal1.Code,
awal1.Category,
awal1.Product,
awal1.Location;

gives the result you want.

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

Re: [sqlite] confusing with how to to this in sqlite

[Simon Davies]

2008/12/20 Rachmat Febfauza :
>
> i have to change 'FOOD ' to 'FOOD', but the result is not that i hope.
>
> Rachmat:  If what Simon says is correct (and I have no reason to doubt
> it) you might also get your query to work by specifying a collating
> sequence of RTRIM on the category column.  
> http://www.sqlite.org/datatype3.html#collation
>
> i also spesifiy collate in create table awal1 and akhir1 like this :
>
> CREATE TABLE awal1(Code char(5) collate RTRIM,Level varchar(8) collate RTRIM, 
> Category varchar(50) collate RTRIM, Product varchar(60) collate RTRIM, 
> Location varchar(50) collate RTRIM, "Begin" datetime);
>
> CREATE TABLE akhir1(Code char(5) collate RTRIM,Level varchar(8) collate 
> RTRIM,Category varchar(50) collate RTRIM,Product varchar(60) collate RTRIM, 
> Location varchar(50) collate RTRIM,"End" datetime);
>
> but the result is not my hope.
>
> the results are :
>
> A1236|MEDIUM|FOOD|SNACK|HOMS 1|2007-05-06 10:48:57|2007-05-06 11:19:25|1828
> A1236|MEDIUM|FOOD|SNACK|HOMS 2|2007-05-06 10:48:57|2007-05-06 11:19:21|1824
> A1222|SMALL|FOOD|WAFER|HOMS 1|2007-05-06 11:20:34|2007-05-06 11:42:46|1332
> A1222|SMALL|FOOD|WAFER|HOMS 2|2007-05-06 11:20:34|2007-05-06 11:31:57|683
> A1221|SMALL|FOOD|CAKE|HOMS 1|2007-05-06 11:31:57|2007-05-06 11:42:46|649
> A1221|SMALL|FOOD|CAKE|HOMS 2|2007-05-06 11:31:57|2007-05-06 11:31:57|0
> A1220|SMALL|FOOD|MARGARINE|HOMS 1|2007-05-06 11:42:46|2007-05-06 11:42:46|0
> A1221|SMALL|FOOD|CAKE|HOMS 1|2007-05-06 11:42:46|2007-05-06 11:42:46|0
> A1222|SMALL|FOOD|WAFER|HOMS 1|2007-05-06 11:42:46|2007-05-06 11:42:46|0
> A1269|SMALL|CLOTHES|BELT|HOMS 3|2007-05-07 17:28:25|2007-05-07 17:28:27|2
>
> take a look at this different
> A1221|SMALL|FOOD|CAKE|HOMS 1|2007-05-06 11:31:57|2007-05-06 11:42:46|649 
> (this is done with sqlite)
>
> A1221SMALLFOODCAKEHOMS 12007-5-6 11:31:572007-5-6 
> 11:31:570 (this is done with mysql)
>
> how to solve this?

It looks like you are using the sqlite3 shell, so experiment with .separator

Have you used .help?

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

Re: [sqlite] confusing with how to to this in sqlite

[Simon Davies]

2008/12/20 Rachmat Febfauza :
> I have problem with executing this query in sqlite. to reconstruct problem 
> please follow the following steps.
>
>
> 1. create table awal1, akhir1 and hasil1 first.
>
> CREATE TABLE awal1(Code char(5),Level varchar(8), Category varchar(50), 
> Product varchar(60), Location varchar(50), "Begin" datetime);
>
> INSERT INTO `awal1` VALUES ('A1220', 'SMALL', 'FOOD', 'MARGARINE', 'HOMS 1', 
> '2007-05-06 11:42:46');
> INSERT INTO `awal1` VALUES ('A1221', 'SMALL', 'FOOD', 'CAKE', 'HOMS 2', 
> '2007-05-06 11:31:57');
> INSERT INTO `awal1` VALUES ('A1221', 'SMALL', 'FOOD', 'CAKE', 'HOMS 1', 
> '2007-05-06 11:31:57');
> INSERT INTO `awal1` VALUES ('A1221', 'SMALL', 'FOOD', 'CAKE', 'HOMS 1', 
> '2007-05-06 11:42:46');
> INSERT INTO `awal1` VALUES ('A1222', 'SMALL', 'FOOD', 'WAFER', 'HOMS 2', 
> '2007-05-06 11:20:34');
> INSERT INTO `awal1` VALUES ('A1222', 'SMALL', 'FOOD', 'WAFER', 'HOMS 1', 
> '2007-05-06 11:20:34');
> INSERT INTO `awal1` VALUES ('A1222', 'SMALL', 'FOOD', 'WAFER', 'HOMS 1', 
> '2007-05-06 11:42:46');
> INSERT INTO `awal1` VALUES ('A1236', 'MEDIUM', 'FOOD', 'SNACK', 'HOMS 2', 
> '2007-05-06 10:48:57');
> INSERT INTO `awal1` VALUES ('A1236', 'MEDIUM', 'FOOD', 'SNACK', 'HOMS 1', 
> '2007-05-06 10:48:57');
> INSERT INTO `awal1` VALUES ('A1269', 'SMALL', 'CLOTHES', 'BELT', 'HOMS 3', 
> '2007-05-07 17:28:25');
>
>
> CREATE TABLE akhir1(Code char(5),Level varchar(8),Category 
> varchar(50),Product varchar(60), Location varchar(50),"End" datetime);
>
> INSERT INTO `akhir1` VALUES ('A1220', 'SMALL', 'FOOD ', 'MARGARINE', 'HOMS 
> 1', '2007-05-06 11:42:46');
> INSERT INTO `akhir1` VALUES ('A1221', 'SMALL', 'FOOD ', 'CAKE', 'HOMS 2', 
> '2007-05-06 11:31:57');
> INSERT INTO `akhir1` VALUES ('A1221', 'SMALL', 'FOOD ', 'CAKE', 'HOMS 1', 
> '2007-05-06 11:31:57');
> INSERT INTO `akhir1` VALUES ('A1221', 'SMALL', 'FOOD ', 'CAKE', 'HOMS 1', 
> '2007-05-06 11:42:46');
> INSERT INTO `akhir1` VALUES ('A1222', 'SMALL', 'FOOD ', 'WAFER', 'HOMS 2', 
> '2007-05-06 11:31:57');
> INSERT INTO `akhir1` VALUES ('A1222', 'SMALL', 'FOOD ', 'WAFER', 'HOMS 1', 
> '2007-05-06 11:31:57');
> INSERT INTO `akhir1` VALUES ('A1222', 'SMALL', 'FOOD ', 'WAFER', 'HOMS 1', 
> '2007-05-06 11:42:46');
> INSERT INTO `akhir1` VALUES ('A1236', 'MEDIUM', 'FOOD ', 'SNACK', 'HOMS 2', 
> '2007-05-06 11:19:21');
> INSERT INTO `akhir1` VALUES ('A1236', 'MEDIUM', 'FOOD ', 'SNACK', 'HOMS 1', 
> '2007-05-06 11:19:25');
> INSERT INTO `akhir1` VALUES ('A1269', 'SMALL', 'CLOTHES', 'BELT', 'HOMS 3', 
> '2007-05-07 17:28:27');
>
>
>
> 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"));
>
>
> 2. then execute this query
>
> insert or ignore into hasil1 select awal1.Code, awal1.Level, awal1.Category, 
> awal1.Product, awal1.Location, awal1."Begin",akhir1."End", 
> strftime("%s",akhir1."End")-strftime("%s",awal1."Begin") as Difference from 
> awal1, akhir1 where awal1.Code = akhir1.Code and awal1.Category = 
> akhir1.Category and awal1.Product = akhir1.Product and awal1.Location = 
> akhir1.Location and akhir1."End" >= awal1."Begin" group by awal1."Begin", 
> awal1.Code, awal1.Category, awal1.Product, awal1.Location;
>
>
> 3. check the result
>
> the result that i hope is like this
>
> A1236MEDIUMFOODSNACKHOMS 12007-5-6 10:48:572007-5-6 
> 11:19:251828
> A1236MEDIUMFOODSNACKHOMS 22007-5-6 10:48:572007-5-6 
> 11:19:211824
> A1222SMALLFOODWAFERHOMS 12007-5-6 11:20:342007-5-6 
> 11:31:57683
> A1222SMALLFOODWAFERHOMS 22007-5-6 11:20:342007-5-6 
> 11:31:57683
> A1221SMALLFOODCAKEHOMS 12007-5-6 11:31:572007-5-6 
> 11:31:570
> A1221SMALLFOODCAKEHOMS 22007-5-6 11:31:572007-5-6 
> 11:31:570
> A1220SMALLFOODMARGARINEHOMS 12007-5-6 11:42:46
> 2007-5-6 11:42:460
> A1221SMALLFOODCAKEHOMS 12007-5-6 11:42:462007-5-6 
> 11:42:460
> A1222SMALLFOODWAFERHOMS 12007-5-6 11:42:462007-5-6 
> 11:42:460
> A1269SMALLCLOTHESBELTHOMS 32007-5-7 17:28:252007-5-7 
> 17:28:272
>
>
> how to make result like i hope?

The result should not be as you are expecting because column category
in table awal1 contain values 'FOOD' and column category in table
akhir1 contains values 'FOOD ' (an additional space character). If the
category columns (containijng 'FOOD') contain exactly the same text
then you would get your expected result.

>
> thanks for advanced
>
>
> note : the result that i hope is mysql result's, so i confuse how to do this 
> in sqlite. Why i prefer sqlite other than mysql, coz if i execute about 12000 
> rows in mysql server, my computer starts not responding 

Re: [sqlite] sqlite3_exec - statement length limit?

[Simon Davies]

2008/11/25 SQLiter <[EMAIL PROTECTED]>:
>
> Is there a limit on the length of the SQL that can be executed using this
> function.  I mean stmt1;stmt2;...stmtn upto what length.  Even if there is
> no limit is it nevertheless prudent not to exceed a certain maximum length?
> --

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

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

Re: [sqlite] Foreign Key

[Simon Davies]

2008/11/25 Satish <[EMAIL PROTECTED]>:
> Hi All!
>
>
>
>  I have a small question that Foreign key Constraint is now supported
> by SQLite or not.I had this question because in an SQLite table for a
> Foreign key "ON DELETE  CASCADE or ON UPDATE CASCADE " are not working.Is
> there any problem with my database or with SQLite.

See first section in
http://www.sqlite.org/omitted.html

>
>
>
> Regards,
>
> Satish.G
>

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

Re: [sqlite] Describe [table];

[Simon Davies]

2008/11/21 Arigead <[EMAIL PROTECTED]>:
>.
>.
> Question is given that "describe" isn't available, can I interrogate any
> of the system tables in order to work out what a table contains?

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

or use "SELECT * FROM sqlite_master;"

>
> Thanks for any help

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

Re: [sqlite] What query should I use?

[Simon Davies]

2008/11/19 wojtek <[EMAIL PROTECTED]>:
> Hiall,
> I have a problem with a query that I thought is easy. I have a database
> containing a following data:
> CREATE TABLE [Magazyn]
> (
>[IdMagazyn] integer PRIMARY KEY AUTOINCREMENT ,
>[IdMat] integer,
>[Partia] varchar (10),
>[Ilosc] integer,
>[DelK] integer DEFAULT 0,
>[Mdata] integer
> )
>  Then,  I have the following data in this Table:
> IdIdMat Partia  Ilosc  DelK
> 1614577149001
> 5614577149001
> 6614577144801
> 7614760519000
> .
> .
> .
> and as a result of the query I need to select a list of 'Partia'  for a
> choosen IdMat, that meens (example IdMat=6):
> Id  IdMat  Partia
> 1 6 1457714
> 7 61476051
> 14   61479230
> and of course 'Partia' number is selected only once.
>
> How should this query look like?

select IdMagazyn, IdMat, Partia from Magazyn where IdMat=6 group by Partia;

> Regards
> WojciechW.

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

Re: [sqlite] Enforcing Uniqueness for tables created using a select statement?

[Simon Davies]

2008/11/20 Christophe Leske <[EMAIL PROTECTED]>:
> Hi,
>
> i am creating my table as such:
>
> create temp table idlookup as select id from ...
>
> I would like ID to be unique in my idlookup table. How would I do this
> using this construct?

create temp table  idlookup as select distinct id from ...
create unique index idlookup_index on idlookup( id );

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

Re: [sqlite] HELP: prep'ed query ... LF function to return column_isNull??

[Simon Davies]

2008/11/7 Rob Sciuk <[EMAIL PROTECTED]>:
>
> I don't see in the documentation a function to return whether or not the
> database value returned by the sqlite3_step() function is NULL.  Surely
> there should be such a beast, no?
>
>  Something like:
>int sqlite3_column_isNull( stmt, i ) ;
> or even:
>int sqlite3_column_isDefined( stmt, i ) ;
>
>
> This should work across all datatypes, and simply allow proper null
> handling -- not relying upon affinity dependant conversions ... hopefully,
> I've simply overlooked something really obvious, but at this moment, is
> opaque to me ... any pointers??
>
> Cheers,
> Rob Sciuk
>

Hi Rob,

I haven't tried this, but sqlite3_column_type(sqlite3_stmt*, int iCol)
should do what you want:
http://www.sqlite.org/c3ref/column_blob.html

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

Re: [sqlite] create table default expr

[Simon Davies]

2008/11/3 John <[EMAIL PROTECTED]>:
> Hi
>
> I want to (if possible) create a table with a default timestamp in a
> format other than "-MM-DD ..." per example below. I have tried a few
> variants but always get same error.
>
> Can this be done and if so, how?
>
> create table (
>custnum integer primary key not null,
>note text,
>stamp default (strftime('%s', current_timestamp))
> );
> SQL error: near "(": syntax error
>
> --
> Regards
>John McMahon
>   [EMAIL PROTECTED]
>
>

>From http://www.sqlite.org/lang_createtable.html,
"The DEFAULT constraint specifies a default value to use when doing an
INSERT. The value may be NULL, a string constant or a number. "; using
a function as a default is not covered.

A trigger can probably achieve what you want :

  create table tst( id integer primary key, ts integer default
current_timestamp );
  create trigger tst_update_ts after insert on tst begin
  update tst set ts = case strftime( '%s', ts ) not null when 1
then strftime( '%s', ts ) else ts end where id=new.id;
  end;

  insert into tst( id ) values( null );
  insert into tst( id ) values( null );
  select * from tst;
1|1225703251
2|1225703259

  insert into tst values( null, 'my birthday' );
  select * from tst;
1|1225703251
2|1225703259
3|my birthday

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

Re: [sqlite] rowid increment

[Simon Davies]

2008/10/29 Shaun R. <[EMAIL PROTECTED]>:
> The rowid looks to reuse numbers when a row is deleted, how can i stop this?
>
> For example, i insert 3 rows, then i delete row 2, when entering a new row
> after that the rowid is 2 rather than 4.   I want the rowid to be 4.

use AUTOINCREMENT keyword:

CREATE TABLE tst( col1 INTEGER PRIMARY KEY AUTOINCREMENT );
>
> ~Shaun
>

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

Re: [sqlite] About select records from table

[Simon Davies]

Hi yoky,

2008/10/29 yoky <[EMAIL PROTECTED]>:
> Hi all,
>I create a table with 250 columns and this table has 3 records,  I
> want to select the records from the table which satisfy certain conditions.
> the SQL statement like this:
>select * from tablename  where (conditions);
>Then use sqlite3_get_table() function to get the contents. If the record
> set I get is a little more, my system has not enough memory to hold them.
>So changed the SQL statement:
>select ID from tablename where (conditions);
>ID is a primary key in the table, first I save all of ID into a buffer,
> then select them through ID one by one.
> select * from tablename where ID=id;
>Here is my problems:
>1. The time to get all of the ID in the table (has 3 records) is
> about 40 seconds. It's too long to meet the performance required.
>Are there some efficient way to select a lot of records satisfied
> some conditions  from the table in the embedded system with not
>so fast CPU and not enough memory? Or select certain numbers records
> satisfied some conditions  one time, and then select several
>times.

Don't use sqlite3_get_table(). Use

sqlite3_prepare_v2()
//
//  bind any relevant parameters here, sqlite3_bind_int() etc...
//
while( SQLITE_ROW == sqlite3_step() )
{
//
//  process row of data - get columns using sqlite3_column_int() etc
//
}
sqlite3_finalize()

>
>   2. I found the memory malloced by sqlite3_get_table() is more large than
> the database file when use "select * from tablename",why?

sqlite3_get_table() returns all data as strings. Any column data that
was of integer or real type would probably require more bytes in the
return from sqlite3_get_table() than the original storage format.
It is difficult to be certain without your source data and more
infomation on how big the difference is.

Rgds,
Simon


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

Re: [sqlite] Join criteria referencing case select result in 3.6.2 produces different results from 3.4.2

[Simon Davies]

2008/9/17 Dan <[EMAIL PROTECTED]>:
>
>
>>
>> On 3.4.2 we get:
>> 3.0|3660.5|3
>> 6.0|1360.3|6
>>
>> On 3.6.2 we get:
>> |5020.8|3
>>
>
> 3.6.2 has a bug involving DISTINCT or GROUP BY queries that use
> expression aliases (AS clauses) in the select-list. Problem is fixed
> in cvs:
>
>   http://www.sqlite.org/cvstrac/chngview?cn=5712
>
> Dan.
>

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

[sqlite] Join criteria referencing case select result in 3.6.2 produces different results from 3.4.2

[Simon Davies]

Hi All,
We have been using SQLite 3.4.2 for some time. On investigating
upgrading to 3.6.2, we found that different results were produced for
one query.
The following illustrates:

CREATE TABLE tst1( tst1Id INTEGER, width REAL, thickness REAL );
CREATE TABLE tst2( tst2Id INTEGER, tst3Id INTEGER, setType INTEGER,
length REAL, number INTEGER );
CREATE TABLE tst3( tst3Id INTEGER, setIdA INTEGER, setIdB INTEGER,
setIdC INTEGER, setIdD INTEGER );

INSERT INTO tst1 VALUES( 1, 1.0, 1.1 );
INSERT INTO tst1 VALUES( 2, 2.0, 2.1 );
INSERT INTO tst1 VALUES( 3, 3.0, 3.1 );
INSERT INTO tst1 VALUES( 4, 4.0, 4.1 );
INSERT INTO tst1 VALUES( 5, 5.0, 5.1 );
INSERT INTO tst1 VALUES( 6, 6.0, 6.1 );
INSERT INTO tst1 VALUES( 9, 7.0, 7.1 );

INSERT INTO tst2 VALUES( 1, 1,  1, 101.1, 1 );
INSERT INTO tst2 VALUES( 2, 1, 2, 102.1, 2 );
INSERT INTO tst2 VALUES( 4, 3, 4, 104.1, 2 );
INSERT INTO tst2 VALUES( 5, 4, 3, 105.1, 1 );
INSERT INTO tst2 VALUES( 6, 4, 1, 106.1, 6 );

INSERT INTO tst3 VALUES( 1, 1, 1, 2, 1 );
INSERT INTO tst3 VALUES( 2, 6, 2, 9, 4 );
INSERT INTO tst3 VALUES( 3, 2, 1, 3, 6 );
INSERT INTO tst3 VALUES( 4, 3, 5, 9, 9 );

SELECT
tst1.width,
SUM( tst2.length * tst2.number ) AS totLength,
CASE tst2.setType
WHEN 1 THEN tst3.setIdA
WHEN 2 THEN tst3.setIdB
WHEN 3 THEN tst3.setIdC
ELSE tst3.setIdA
END AS theSetId
FROM
tst2 LEFT OUTER JOIN
tst3 ON tst2.tst3Id=tst2.tst3Id LEFT OUTER JOIN
tst1 ON tst1.tst1Id=theSetId
GROUP BY
tst1.width>4;

On 3.4.2 we get:
3.0|3660.5|3
6.0|1360.3|6

On 3.6.2 we get:
|5020.8|3

We have determined that the query above can be rewritten as:
SELECT
tst1.width,
SUM( tst2.length * tst2.number ) AS totLength
FROM
tst2 LEFT OUTER JOIN
tst3 ON tst2.tst3Id=tst2.tst3Id LEFT OUTER JOIN
tst1 ON tst1.tst1Id=
CASE tst2.setType
WHEN 1 THEN tst3.setIdA
WHEN 2 THEN tst3.setIdB
WHEN 3 THEN tst3.setIdC
ELSE tst3.setIdA
END
GROUP BY
tst1.width>4;

and both versions then give the same result.
However, the SQL from which this is adapted joins several more tables
on the 'theSetId' value, and we wanted only one place to perform
updates.

Anyway, is the behaviour in 3.6.2 correct, or was the behaviour in
3.4.2 incorrect?

Thanking you in advance,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [sqlite-announce] Foreign key trigger in transactiontriggers wrongly

[Simon Davies]

2008/9/17 Bjorn Rauch <[EMAIL PROTECTED]>:
>> From: Simon Davies <[EMAIL PROTECTED]>> Date: 2008/9/16> Subject: Re: 
>> [sqlite-announce] Foreign key .
.
.
.
> Yes, indeed it works this way. But I am executing these commands from a .NET 
> application using SQLite.NET (SQLite for ADO.NET 2.0, 
> http://sqlite.phxsoftware.com) to make these calls. Transaction handling 
> seems to work there, but the trigger is raised as I explained.
>
> Of course, maybe the ADO.NET provider implementation might be the culprit.
>
> Regards,
> Bjorn
>

Are you sure that the data is as you expect?

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

Re: [sqlite] Update command working from command line but not from a C program

[Simon Davies]

2008/9/16 Aravinda babu <[EMAIL PROTECTED]>:
> Hi all,
>
.
.
.
> static const char *updateCmd = "UPDATE cert_store_table set lastRowFlag =
> :lastRowFlag where lastRowFlag = :lastRowFlag;" ; 
>  <  same named parameter twice
>
.
.
.
> Is there any wrong in the above code ?
>
>
> If i update on the command line using the command , it is working.What is
> the issue ??
>
.
.
.
>
> Waiting for your reply,
>
> Aravind.

Hi Aravind,

You are using the same parameter name - only the first bind value will be used.
See http://www.sqlite.org/c3ref/bind_blob.html.

I suspect that you are ignoring an error from your second call to
sqlite3_bind_int.

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

Re: [sqlite] SQL statement to get min/max values

[Simon Davies]

Not sure about replacing the collation sequence - does not sound
easier than recreating the table.

You could just add a view:

sqlite> CREATE TABLE test_table (ID INTEGER PRIMARY KEY,
   ...> ExternalID2 INTEGER,
   ...> ExternalID INTEGER,
   ...> Value );
sqlite> INSERT INTO "test_table" VALUES(1007,1,37,'-5');
sqlite> INSERT INTO "test_table" VALUES(1044,4,37,'-10');
sqlite> INSERT INTO "test_table" VALUES(1081,2,37,'-20');
sqlite> INSERT INTO "test_table" VALUES(1118,3,37,'-1');
sqlite> INSERT INTO "test_table" VALUES(1155,5,37,'-7');
sqlite> INSERT INTO "test_table" VALUES( 2044,4,37,'fred');
sqlite> INSERT INTO "test_table" VALUES( 3044,4,37,'bill');
sqlite>
sqlite> create view test_view as
   ...> select ID, ExternalID2, ExternalID, cast( value as integer ) as Value
   ...> from test_table where cast( Value as text)=cast(Value as integer);
sqlite>
sqlite> select * from test_view;
1007|1|37|-5
1044|4|37|-10
1081|2|37|-20
1118|3|37|-1
1155|5|37|-7
sqlite>
sqlite> select min( Value ) from test_view;
-20
sqlite> select max( Value ) from test_view;
-1

Rgds,
Simon


2008/8/15 Dennis Volodomanov <[EMAIL PROTECTED]>:
>
>> Declaring the column as integer does not prevent you from storing strings:
>>
>>
> Yes, except for a small problem of updating all live databases with the
> new column type. I don't think I can update the column type without
> recreating the table, right? It's not hard, so if it comes down to this,
> then I guess I'll do it. Or if replacing the collation sequence is not
> too hard, I'd rather go that route.
>
> Thank you,
>
>   Dennis
>
> ___
> 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] SQL statement to get min/max values

[Simon Davies]

Hi Dennis,

Declaring the column as integer does not prevent you from storing strings:

SQLite version 3.6.0
Enter ".help" for instructions
sqlite> BEGIN TRANSACTION;
sqlite> CREATE TABLE test_table (ID INTEGER PRIMARY KEY, ExternalID2 INTEGER,
   ...> ExternalID
   ...>  INTEGER, Value INTEGER);
sqlite> INSERT INTO "test_table" VALUES(1007,1,37,'-5');
sqlite> INSERT INTO "test_table" VALUES(1044,4,37,'-10');
sqlite> INSERT INTO "test_table" VALUES(1081,2,37,'-20');
sqlite> INSERT INTO "test_table" VALUES(2081,2,37,'fred');
sqlite> INSERT INTO "test_table" VALUES(3081,2,37,'bill');
sqlite> INSERT INTO "test_table" VALUES(1118,3,37,'-1');
sqlite> INSERT INTO "test_table" VALUES(1155,5,37,'-7');
sqlite> COMMIT;
sqlite>
sqlite>
sqlite>
sqlite> select max( value ) from test_table;
fred
sqlite> select min( value ) from test_table;
-20
sqlite> select *, typeof( value ) from test_table;
1007|1|37|-5|integer
1044|4|37|-10|integer
1081|2|37|-20|integer
1118|3|37|-1|integer
1155|5|37|-7|integer
2081|2|37|fred|text
3081|2|37|bill|text
sqlite> select max( value ) from test_table where typeof( value ) = 'integer';
-1
sqlite>

Rgds,
Simon

2008/8/15 Dennis Volodomanov <[EMAIL PROTECTED]>:
>
>> Works just fine with 3.6.1 if you declare the Value column to be
>> INTEGER. As it is, I have no idea what collation is used, but the
>> Value column will be declared to default to TEXT values, as shown by
>>
>> select typeof(value) from test_table;
>>
> I haven't tried that, but I cannot declare it as INTEGER, because it
> contains strings as well (of course they're not selected to get min/max
> values). Would I need to write my own min/max functions to handle this?
>
> Thanks,
>
>  Dennis
>
>
> P.S. Sorry for the message I sent to you personally - the Reply-to
> button is too smart :)
>
> ___
> 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] db vs shell

[Simon Davies]

2008/7/29 Robert Citek <[EMAIL PROTECTED]>:
> On Tue, Jul 29, 2008 at 2:35 AM,  <[EMAIL PROTECTED]> wrote:
>> On Tue, Jul 29, 2008 at 02:29:53AM -0500, Robert Citek wrote:
>>> $ sqlite3 -version
>>> 3.4.2
>>
>> On 3.4.0 and 3.5.9 here, the pure-SQL version is -much- faster than the shell
>> pipe. Could you tell us more about the contents of your database?
>
> The column contains a list of text items.  This script demonstrates
> the phenomenon:
>
> true && ( set -x
> sqlite3 sample.db 'create table bar (foo text)'
> seq -w 1 200 | sed 's/^/id/' > list.txt
> sqlite3 sample.db '.imp "list.txt" "bar"'
> time -p sqlite3 sample.db 'select foo from bar ; ' | uniq | sort | uniq | wc 
> -l
> time -p sqlite3 sample.db 'select count(distinct foo) from bar ; '
> )
>
> Output:
>
> + sqlite3 sample.db 'create table bar (foo text)'
> + seq -w 1 200
> + sed 's/^/id/'
> + sqlite3 sample.db '.imp "list.txt" "bar"'
> + sqlite3 sample.db 'select foo from bar ; '
> + uniq
> + sort
> + uniq
> + wc -l
> 200
> real 3.25
> user 3.71
> sys 0.47
> + sqlite3 sample.db 'select count(distinct foo) from bar ; '
> 200
> real 22.48
> user 20.98
> sys 0.28
>
> Regards,
> - Robert

Hi Robert,

on my XP machine I get:

> sqlite3 -version
3.6.0

> true && ( set -x
> sqlite3 sample.db 'create table bar (foo text)'
> seq -w 1 200 | sed 's/^/id/' > list.txt
> sqlite3 sample.db '.imp "list.txt" "bar"'
> time -p sqlite3 sample.db 'select foo from bar ; ' | uniq | sort | uniq | wc 
> -l
> time -p sqlite3 sample.db 'select count(distinct foo) from bar ; '
> )
+ sqlite3 sample.db 'create table bar (foo text)'
+ seq -w 1 200
+ sed 's/^/id/'
+ sqlite3 sample.db '.imp "list.txt" "bar"'
+ sqlite3 sample.db 'select foo from bar ; '
+ uniq
+ sort
+ uniq
+ wc -l
200
real 12.67
user 10.63
sys 3.91
+ sqlite3 sample.db 'select count(distinct foo) from bar ; '
200
real 13.59
user 0.01
sys 0.01

> alias sqlite3=sjd_old_sqlite3.exe
> sqlite3 -version
3.4.2
> time sqlite3 sample.db 'select foo from bar ; ' | uniq | sort | uniq | wc -l
200

real0m12.883s
user0m10.870s
sys 0m3.856s

> time sqlite3 sample.db 'select count( distinct foo ) from bar ;'
200

real0m14.888s
user0m0.015s
sys 0m0.015s

-Nothing like the discrepancy you are seeing...
Rgds,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite Endianness

[Simon Davies]

2008/7/25 Dave Gierok <[EMAIL PROTECTED]>:
> Does anyone know what endianness a Sqlite database is stored in?  Or does 
> that depend on the endianness of the machine it was created on?  I am 
> wondering because we ship a game that runs on Xbox 360, which uses the 
> PowerPC architecture and has a different endianness than the PC.  We run PC 
> tools to create our database for the game.  I wonder if we are paying a 
> performance overhead on the Xbox if the database essentially needs to be 
> 'converted' runtime.
>
> Thanks,
> Dave

see heading "Stable Cross-Platform Database File" on
http://www.sqlite.org/different.html

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

Re: [sqlite] Format of the data

[Simon Davies]

2008/7/22 Joanne Pham <[EMAIL PROTECTED]>:
> Hi All,
> I am current using sqlite 3.5.9 and below is command to get the data from my 
> database as:
> .output '/opt/phoenix/monitor/exportData'
> .mode csv
> select '#Monitored applications' , group_concat(appName) from appMapTable;
> The output is:
> "#Monitored 
> applications","new1,nsc1,Oracle,MSSQL,Sybase,Exchange,Informix,DB2,SQLite3,MySql,nsc2,nsc3"
> but I would like to have the format below:
> #Monitored applications, 
> new1,nsc1,Oracle,MSSQL,Sybase,Exchange,Informix,DB2,SQLite3,MySql,nsc2,nsc3
> (without ")
> Can I get the output without double quote.
> Thanks,
> JP


Don't use .mode csv,

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

Re: [sqlite] Problem with Inner Join

[Simon Davies]

2008/7/14 Ralf <[EMAIL PROTECTED]>:
> I'm having problems with Inner Joins on m:n relations
>
> book <--> bookauthor <--> author
>
>  SELECT authorname FROM author INNER JOIN book INNER JOIN bookauthor ON
> book.Id_book = bookauthor.Id_book ON author.Id_author = bookauthor.Id_author
> WHERE bookltitle='title'
>
> I receive an error when I execute this Query saying
>
> "SQL Error"
> Is it me or is it SQLite?
>
> Or a bit of both?
> Pls help me
> Thanks
> Ralf

Ralf,

You just get the syntax of the joins right...

SQLite version 3.5.9
Enter ".help" for instructions
sqlite>
sqlite> create table book( bookId integer, bookName text );
sqlite> create table author( authorId integer, authorName text );
sqlite> create table bookAuthor( bookId integer, authorId integer );
sqlite> insert into book values( 1, 'book1' );
sqlite> insert into book values( 2, 'book2' );
sqlite> insert into book values( 3, 'book3' );
sqlite> insert into book values( 4, 'book4' );
sqlite>
sqlite> insert into author values( 1, 'author1' );
sqlite> insert into author values( 2, 'author2' );
sqlite> insert into author values( 3, 'author3' );
sqlite>
sqlite> insert into bookAuthor values( 1, 1 );
sqlite> insert into bookAuthor values( 2, 1 );
sqlite> insert into bookAuthor values( 3, 2 );
sqlite> insert into bookAuthor values( 4, 3 );
sqlite>
sqlite> select authorName from author inner join bookAuthor on
author.authorId=bookAuthor.authorId inner join book on
book.bookId=bookAuthor.bookId where bookName='book1';
author1
sqlite> select authorName from author inner join bookAuthor on
author.authorId=bookAuthor.authorId inner join book on
book.bookId=bookAuthor.bookId where bookName='book4';
author3
sqlite>

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