Re: [sqlite] first few characters of varchar() corrupted when SELECTing from a C++ program?

[freshie2004-sqlite]

(embarrassed)

printf("testValue=(%s)\n", testValue);





From: John Machin 
To: General Discussion of SQLite Database 
Sent: Tuesday, 30 June, 2009 4:51:09 PM
Subject: Re: [sqlite] first few characters of varchar() corrupted when 
SELECTing from a C++ program?

On 30/06/2009 2:56 PM, freshie2004-sql...@yahoo.com.au wrote:

> printf("testValue=(%s)\n");

I've always been afraid to use those new-fangled mind-reading C 
compilers lest they were easily shocked ;-)

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



  

Access Yahoo!7 Mail on your mobile. Anytime. Anywhere.
Show me how: http://au.mobile.yahoo.com/mail
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] first few characters of varchar() corrupted when SELECTing from a C++ program?

[John Machin]

On 30/06/2009 2:56 PM, freshie2004-sql...@yahoo.com.au wrote:

> printf("testValue=(%s)\n");

I've always been afraid to use those new-fangled mind-reading C 
compilers lest they were easily shocked ;-)

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

Re: [sqlite] ATTACH Database

[Robert Simpson]

What is D2.Table?

Also what is the _fileName referred to in the code?

And finally ... what platform?  Mono or Windows?  I'm just wondering about
the use of slashes instead of backslashes in the attach filename.

Oh, and http://sqlite.phxsoftware.com is more suited for handling
.Net-related questions on the SQLite engine.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Carlton Guc
Sent: Monday, June 29, 2009 9:54 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] ATTACH Database

ATTACH database

I have a unique problem where ATTACH database seems to work fine when I'm
using a GUI tool, however when I pass the SQL string to the SQLite engine
using SqLiteCommand under .NET, it doesn't seem to work.I get an error
saying that D2.Table is not found.

SQL String:

ATTACH DATABASE 'F:/Users/name/Documents/ShowBuilder6/Database/2.sbx' AS
XYZ;
SELECT * from [EquipmentCatalog] union select * from XYZ.EquipmentCatalog


Code:
// SQLite database builder
using (SQLiteConnection cn = new
SQLiteConnection(SBConnection.GetDatabaseConnection(_fileName)))
{
cn.Open();
SQLiteCommand cmd;

ResourceManager resourceManager = new
ResourceManager("ShowBuilder6.Library.Properties.StoredProcedures",
Assembly.GetExecutingAssembly());
for (int i = versions.IndexOf(_currentVersion); i <
versions.Count - 1; i++)
{
try
{
cmd = new
SQLiteCommand(resourceManager.GetString(///  the above Attach string, cn);
cmd.ExecuteNonQuery();
}
catch
{
}
}
}


Does anyone have the "ATTACH" command working from a .NET interface?
Again, I can get it to work using SQLite Developer, but doesn't seem to work
in code.

Thoughts?

Carlton
SR




___
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] concurrency differences between in-memory and on disk?

[pierr]

Igor Tandetnik wrote:
> 
> Daniel Watrous wrote:
>> I've developed an application that has very high concurrency.  In my
>> initial testing we used SQLite 3 from python, but we experienced too
>> many locks and the database always fell behind.
> 
> What precisely is the nature of the concurrency? Are you opening 
> multiple connections to the same database, or sharing a single 
> connection between threads? Are you trying to write concurrently, and if 
> so, is it to the same table or to different tables?
> 
> Basically, there are three ways SQLite can be used (they can also be 
> combined):
> 
> 1. Many connections to the same database. In this case, there's a 
> many-readers-single-writer lock at the database level, so at any point 
> in time only one connection can write.
> 
> 2. A single connection shared by multiple threads. A connection 
> maintains a mutex that every API call acquires on entry and releases on 
> return, so all calls are serialized. However, one thread can, say, step 
> through a select resultset row-by-row, while another inserts row after 
> row into some table: these calls can interleave, and would appear almost 
> concurrent.
>  
Hi Igor,
So, for "A single connection shared by multiple threads" case, is the
statement "there's a 
many-readers-single-writer lock at the database level" still ture? At least
, the read and write 
would appear concurrent as you said.

Is there any examples showing the difference between these 2 different usage
model?
Thanks.

- Pierr

-- 
View this message in context: 
http://www.nabble.com/concurrency-differences-between-in-memory-and-on-disk--tp24201096p24266195.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Trigger execution sequence/order

[freshie2004-sqlite]

I should have looked harder...

"The order of arbitrary."
http://www.mail-archive.com/sqlite-users@sqlite.org/msg17641.html





From: "freshie2004-sql...@yahoo.com.au" 
To: sqlite-users@sqlite.org
Sent: Tuesday, 30 June, 2009 2:31:40 PM
Subject: [sqlite] Trigger execution sequence/order

Hi All,

I was wondering if there was a definite way of determining what sequence or 
order that triggers are executed in sqlite3. Have searched this list and the 
internet and the only thing I have found suggested that triggers are executed 
in alphabetical order, which is wrong.

The following SQL:

CREATE TABLE Test(s TEXT);
CREATE TABLE Log(s TEXT);
CREATE TRIGGER btest_1 BEFORE INSERT ON Test
BEGIN
INSERT INTO Log(s) VALUES('btest_1');
END;
CREATE TRIGGER btest_3 BEFORE INSERT ON Test
BEGIN
INSERT INTO Log(s) VALUES('btest_3');
END;
CREATE TRIGGER btest_2 BEFORE INSERT ON Test
BEGIN
INSERT INTO Log(s) VALUES('btest_2');
END;
CREATE TRIGGER atest_1 AFTER INSERT ON Test
BEGIN
INSERT INTO Log(s) VALUES('atest_1');
END;
CREATE TRIGGER atest_3 AFTER INSERT ON Test
BEGIN
INSERT INTO Log(s) VALUES('atest_3');
END;
CREATE TRIGGER atest_2 AFTER INSERT ON Test
BEGIN
INSERT INTO Log(s) VALUES('atest_2');
END;
INSERT INTO Test(s) VALUES('Test');
SELECT rowid,* FROM log;

Returns the following on both windows (3.6.14) and linux (3.4.2):

1|btest_2
2|btest_3
3|btest_1
4|atest_2
5|atest_3
6|atest_1

So sqlite seems to run triggers LIFO. However, there seems to be no 
specification for this.

Cheers!


  

Access Yahoo!7 Mail on your mobile. Anytime. Anywhere.
Show me how: http://au.mobile.yahoo.com/mail
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



  Access Yahoo!7 Mail on your mobile. Anytime. Anywhere.
Show me how: http://au.mobile.yahoo.com/mail
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] first few characters of varchar() corrupted when SELECTing from a C++ program?

[freshie2004-sqlite]

(Replying to pierr as I joined the list after uralmazamog sent original email)


uralmazamog,

The code is incomplete. You are not showing us how you are determining what 
testValue points to.

What is returned if you use the following?

sqlite3_open_v2( "testdat", &sqlDB, SQLITE_OPEN_READWRITE |
SQLITE_OPEN_CREATE, NULL );
sqlite3_prepare_v2( sqlDB, "SELECT b FROM whee WHERE a='bing';", 
-1,&sqlStat, NULL );
sqlite3_step( sqlStat );
const unsigned char *testValue = sqlite3_column_text( sqlStat, 0 );
printf("testValue=(%s)\n");

Cheers!






From: pierr 
To: sqlite-users@sqlite.org
Sent: Tuesday, 30 June, 2009 2:42:16 PM
Subject: Re: [sqlite] first few characters of varchar() corrupted when 
SELECTing from a C++ program?




uralmazamog wrote:
> 
> Greetings,
> 
> maybe it's just me being stupid, I'll best jump right to the code:
> 
> sqlite3_open_v2( "testdat", &sqlDB, SQLITE_OPEN_READWRITE |
> SQLITE_OPEN_CREATE, NULL );
> sqlite3_prepare_v2( sqlDB, "SELECT b FROM whee WHERE a='bing';", -1,
> &sqlStat, NULL );
> sqlite3_step( sqlStat );
> const unsigned char *testValue = sqlite3_column_text( sqlStat, 0 );
> 
> both a and b are varchar(20)s
> 
> calling the query from the command-line tool returns the proper result
> "bang", however, running this code the value testValue shows up as ""
> for longer strings only the first four characters are corrupted, and the
> rest reads okay, what am I doing wrong?
> 
> 
Try this:
char testValue[20];
memcpy(testValue,sqlite3_column_text(sqlStat,0),sqlite3_column_bytes(sqlStat,0));


-- 
View this message in context: 
http://www.nabble.com/first-few-characters-of-varchar%28%29-corrupted-when-SELECTing-from-a-C%2B%2B-program--tp24237176p24266020.html
Sent from the SQLite mailing list archive at Nabble.com.

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



  

Access Yahoo!7 Mail on your mobile. Anytime. Anywhere.
Show me how: http://au.mobile.yahoo.com/mail
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ATTACH Database

[Carlton Guc]

ATTACH database

I have a unique problem where ATTACH database seems to work fine when I'm using 
a GUI tool, however when I pass the SQL string to the SQLite engine using 
SqLiteCommand under .NET, it doesn't seem to work.I get an error saying 
that D2.Table is not found.

SQL String:

ATTACH DATABASE 'F:/Users/name/Documents/ShowBuilder6/Database/2.sbx' AS XYZ;
SELECT * from [EquipmentCatalog] union select * from XYZ.EquipmentCatalog


Code:
// SQLite database builder
using (SQLiteConnection cn = new 
SQLiteConnection(SBConnection.GetDatabaseConnection(_fileName)))
{
cn.Open();
SQLiteCommand cmd;

ResourceManager resourceManager = new 
ResourceManager("ShowBuilder6.Library.Properties.StoredProcedures", 
Assembly.GetExecutingAssembly());
for (int i = versions.IndexOf(_currentVersion); i < 
versions.Count - 1; i++)
{
try
{
cmd = new 
SQLiteCommand(resourceManager.GetString(///  the above Attach string, cn);
cmd.ExecuteNonQuery();
}
catch
{
}
}
}


Does anyone have the "ATTACH" command working from a .NET interface?  
Again, I can get it to work using SQLite Developer, but doesn't seem to work in 
code.

Thoughts?

Carlton
SR




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

Re: [sqlite] first few characters of varchar() corrupted when SELECTing from a C++ program?

[pierr]

uralmazamog wrote:
> 
> Greetings,
> 
> maybe it's just me being stupid, I'll best jump right to the code:
> 
> sqlite3_open_v2( "testdat", &sqlDB, SQLITE_OPEN_READWRITE |
> SQLITE_OPEN_CREATE, NULL );
> sqlite3_prepare_v2( sqlDB, "SELECT b FROM whee WHERE a='bing';", -1,
> &sqlStat, NULL );
> sqlite3_step( sqlStat );
> const unsigned char *testValue = sqlite3_column_text( sqlStat, 0 );
> 
> both a and b are varchar(20)s
> 
> calling the query from the command-line tool returns the proper result
> "bang", however, running this code the value testValue shows up as "
"
> for longer strings only the first four characters are corrupted, and the
> rest reads okay, what am I doing wrong?
> 
> 
Try this:
char testValue[20];
memcpy(testValue,sqlite3_column_text(sqlStat,0),sqlite3_column_bytes(sqlStat,0));


-- 
View this message in context: 
http://www.nabble.com/first-few-characters-of-varchar%28%29-corrupted-when-SELECTing-from-a-C%2B%2B-program--tp24237176p24266020.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] Trigger execution sequence/order

[freshie2004-sqlite]

Hi All,

I was wondering if there was a definite way of determining what sequence or 
order that triggers are executed in sqlite3. Have searched this list and the 
internet and the only thing I have found suggested that triggers are executed 
in alphabetical order, which is wrong.

The following SQL:

CREATE TABLE Test(s TEXT);
CREATE TABLE Log(s TEXT);
CREATE TRIGGER btest_1 BEFORE INSERT ON Test
BEGIN
INSERT INTO Log(s) VALUES('btest_1');
END;
CREATE TRIGGER btest_3 BEFORE INSERT ON Test
BEGIN
INSERT INTO Log(s) VALUES('btest_3');
END;
CREATE TRIGGER btest_2 BEFORE INSERT ON Test
BEGIN
INSERT INTO Log(s) VALUES('btest_2');
END;
CREATE TRIGGER atest_1 AFTER INSERT ON Test
BEGIN
INSERT INTO Log(s) VALUES('atest_1');
END;
CREATE TRIGGER atest_3 AFTER INSERT ON Test
BEGIN
INSERT INTO Log(s) VALUES('atest_3');
END;
CREATE TRIGGER atest_2 AFTER INSERT ON Test
BEGIN
INSERT INTO Log(s) VALUES('atest_2');
END;
INSERT INTO Test(s) VALUES('Test');
SELECT rowid,* FROM log;

Returns the following on both windows (3.6.14) and linux (3.4.2):

1|btest_2
2|btest_3
3|btest_1
4|atest_2
5|atest_3
6|atest_1

So sqlite seems to run triggers LIFO. However, there seems to be no 
specification for this.

Cheers!


  

Access Yahoo!7 Mail on your mobile. Anytime. Anywhere.
Show me how: http://au.mobile.yahoo.com/mail
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Time format

[Harold Wood]

try DATE('now','localtime')

--- On Tue, 6/30/09, Goldstein, Ian  wrote:


From: Goldstein, Ian 
Subject: [sqlite] Time format
To: sqlite-users@sqlite.org
Date: Tuesday, June 30, 2009, 12:07 AM




Hello, I am one day into sqlite and have probably a very simple problem
involving datetime function.
It seems, there is a 4 hour difference between what is in the db and my
real time.
For example:
date;sqlite3 ian.db "select (datetime('now'))"
Tue Jun 30 00:05:35 EDT 2009
2009-06-30 04:05:35

Thanks 
Ian


  


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

[sqlite] Time format

[Goldstein, Ian]

Hello, I am one day into sqlite and have probably a very simple problem
involving datetime function.
It seems, there is a 4 hour difference between what is in the db and my
real time.
For example:
date;sqlite3 ian.db "select (datetime('now'))"
Tue Jun 30 00:05:35 EDT 2009
2009-06-30 04:05:35

Thanks 
Ian


  


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

Re: [sqlite] View error

[Kees Nuyt]

On Mon, 29 Jun 2009 10:12:25 +0200, Bruno Carlus
 wrote:

>Hi,
>
>when I try to execute
>SELECT num_id_cycle FROM vw_last_cycles_mapping WHERE num_cycle = 10
>
>in a c function it issues a column does not exixt error for num_cycle 
>... but it works when I execute the same request in the sqlite3 shell ...
>
>vw_last_cycles is a view listing the 10 last inserted rows of a table.
>
>any idea ?

Yes, you say you SELECT ... FROM vw_last_cycles , 
but the code above references vw_last_cycles_mapping.

>Thanks,
>Bruno.
-- 
  (  Kees Nuyt
  )
c[_]
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug in SQLite 3.6.14 / wrong rowid returned

[Wanadoo Hartwig]

Hi Simon,

I am not complaining about the autoincrement's result but that  
sqlite3_insert_rowid returns the wrong rowid.

Hartwig

Am 29.06.2009 um 23:09 schrieb Simon Slavin:

>
> On 29 Jun 2009, at 8:54pm, Wanadoo Hartwig wrote:
>
>> I have posted a while ago a bug (at least I think that it is a bug)
>> but only in a very abstract form. Now, I have written a C-program
>> showing the bug.
>
> I assume that if you type those commands into the sqlite3 command-line
> tool, you get the same result.  As to the use of rowid, do your
> results agree with
>
> http://www.sqlite.org/autoinc.html
>
> ?
>
> 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] Building sqlite

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Doug wrote:
> You may need SQLITE_THREADSAFE depending on how you're using the library

SQLITE_THREADSAFE defaults to 1 (meaning that SQLite is fully threadsafe
no matter what you do).  See

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

IF for some reason you want the library to not bother with mutexes and
being threadsafe then you can use sqlite3_config with an appropriate
config option:

  http://sqlite.org/c3ref/config.html
  http://sqlite.org/c3ref/c_config_getmalloc.html

ie as long you use the defaults for compilation you will be fine and can
change them at runtime anyway.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkpJL4UACgkQmOOfHg372QRlegCgknctHB61XIeFVFS7CK0uiLzM
VAYAniskhUHMJe0LwpTxPC2JBpf+yC06
=SmvA
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Anyway to set initial value of autoincrement primary key to 0?

[Shaun Seckman (Firaxis)]

Is there any way to set the initial value for an integer primary key
auto-increment column to 0? Currently it always defaults to 1.

 

-Shaun

 

 

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

Re: [sqlite] Bug in SQLite 3.6.14 / wrong rowid returned

[Simon Slavin]

On 29 Jun 2009, at 8:54pm, Wanadoo Hartwig wrote:

> I have posted a while ago a bug (at least I think that it is a bug)
> but only in a very abstract form. Now, I have written a C-program
> showing the bug.

I assume that if you type those commands into the sqlite3 command-line  
tool, you get the same result.  As to the use of rowid, do your  
results agree with

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

?

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

Re: [sqlite] Memory leak with sqlite3_exec on qnx 6.4.1

[Greg Morehead]

Thanks Eric, you nailed it on the head!

With this parameter I am able to configure the page usage and stop the memory 
growth where I want it!  


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org]on Behalf Of Eric Minbiole
Sent: Monday, June 29, 2009 12:06 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Memory leak with sqlite3_exec on qnx 6.4.1


> So it seems by best bet is to close and open the connection once every 5 
> minutes or so?

While this might "work", I don't think you should resort to that.  As 
Kees noted earlier, there will be performance drawbacks if you 
close/re-open the database.

As others have indicated, the heap growth is likely due to SQLite 
intentionally caching frequently used disk pages in order to improve 
performance.  Rather than closing/re-opening, I suggest that you set the 
page cache size to a limit that is reasonable for your application:

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

The default is around 2MB.  You can decrease to as little as 10KB.  Note 
that there are some other buffers used by SQLite (Scratch, Lookaside, 
etc).  However, these are typically small compared to the page cache. 
Additional info here:

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

~Eric
___
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] Bug in SQLite 3.6.14 / wrong rowid returned

[Wanadoo Hartwig]

Hi,

actually, I also tested it with 3.6.16 and the bug still exists.

Hartwig

Am 29.06.2009 um 21:54 schrieb Wanadoo Hartwig:

> Hello,
>
> I have posted a while ago a bug (at least I think that it is a bug)
> but only in a very abstract form. Now, I have written a C-program
> showing the bug.
>
> Brief description:
>
> An FTS related trigger combination leads to a wrong determination of
> the last inserted row id. Triggers that are not related to FTS are
> working fine.
>
>
> #include 
> #include 
>
> #include "sqlite3.h"
>
> void OnErrorExit(sqlite3* database, int rc, char* errorMessage)
> {
>   if (rc != SQLITE_OK)
>   {
> if (errorMessage != NULL)
> {
>   fprintf(stderr,"SQL error: %s\n",errorMessage);
>   sqlite3_free(errorMessage);
> } /* if */
> sqlite3_close(database);
> exit(1);
>   } /* if */
> }
>
> void ShowDatabaseContents(sqlite3* database)
> {
>   char* errorMessage;
>   char** result;
>   char* sqlStatement;
>
>   int noOfColumns, noOfRows;
>   int rc;
>
>   sqlite3_int64 lastInsertedID;
>
>
>   lastInsertedID = sqlite3_last_insert_rowid(database);
>   printf("Last inserted ID: %d\n",(int) lastInsertedID);
>   sqlStatement = "SELECT * FROM Simple;";
>   rc =
> sqlite3_get_table
> (database,sqlStatement,&result,&noOfRows,&noOfColumns,&errorMessage);
>   OnErrorExit(database,rc,errorMessage);
>
>   printf("Number of rows: %d\n",noOfRows);
>   printf("Number of columns: %d\n",noOfColumns);
>   for (int i=0; i   {
> printf("Row: %2d ",i);
> for (int j=0; j   printf("  Column[%d]: %s",j,result[i*noOfColumns+j]);
> printf("\n");
>   } /* for */
> }
>
> int main (int argc, const char * argv[])
> {
>   char* errorMessage;
>   char* sqlStatement;
>
>   int rc;
>
>   sqlite3* database;
>
>
>  // prepare database
>   rc = sqlite3_open(NULL,&database);
>   if (rc != SQLITE_OK)
>   {
> fprintf(stderr,"Can't open database in memory: %s
> \n",sqlite3_errmsg(database));
> exit(1);
>   } /* if */
>   sqlStatement = "CREATE TABLE Simple (ID integer primary key, Name
> text);";
>   rc = sqlite3_exec(database,sqlStatement,NULL,NULL,&errorMessage);
>   OnErrorExit(database,rc,errorMessage);
>   sqlStatement = "CREATE VIRTUAL TABLE SimpleFTS USING FTS3 (Name);";
>   rc = sqlite3_exec(database,sqlStatement,NULL,NULL,&errorMessage);
>   OnErrorExit(database,rc,errorMessage);
>   sqlStatement = "CREATE TRIGGER DeleteTrigger AFTER DELETE ON Simple
> FOR EACH ROW BEGIN DELETE FROM SimpleFTS WHERE (rowid=OLD.ID); END;";
>   rc = sqlite3_exec(database,sqlStatement,NULL,NULL,&errorMessage);
>   OnErrorExit(database,rc,errorMessage);
>   sqlStatement = "CREATE TRIGGER InsertTrigger AFTER INSERT ON Simple
> FOR EACH ROW BEGIN INSERT INTO SimpleFTS (rowid,Name) VALUES
> (NEW.ID,NEW.Name); END;";
>   rc = sqlite3_exec(database,sqlStatement,NULL,NULL,&errorMessage);
>   OnErrorExit(database,rc,errorMessage);
>  // insert and delete items
>   sqlStatement = "INSERT INTO Simple (Name) VALUES('one');";
>   rc = sqlite3_exec(database,sqlStatement,NULL,NULL,&errorMessage);
>   OnErrorExit(database,rc,errorMessage);
>   sqlStatement = "INSERT INTO Simple (Name) VALUES('two');";
>   rc = sqlite3_exec(database,sqlStatement,NULL,NULL,&errorMessage);
>   OnErrorExit(database,rc,errorMessage);
>   ShowDatabaseContents(database);
>   sqlStatement = "DELETE FROM Simple WHERE (ID = 1);";
>   rc = sqlite3_exec(database,sqlStatement,NULL,NULL,&errorMessage);
>   OnErrorExit(database,rc,errorMessage);
>   ShowDatabaseContents(database);
>   sqlStatement = "INSERT INTO Simple (Name) VALUES('one');";
>   rc = sqlite3_exec(database,sqlStatement,NULL,NULL,&errorMessage);
>   OnErrorExit(database,rc,errorMessage);
>   ShowDatabaseContents(database);
>   sqlStatement = "INSERT INTO Simple (Name) VALUES('one');";
>   rc = sqlite3_exec(database,sqlStatement,NULL,NULL,&errorMessage);
>   OnErrorExit(database,rc,errorMessage);
>   ShowDatabaseContents(database);
>
>   sqlite3_close(database);
>
>   printf("Done!\n");
>   return 0;
> }
>
> This is the output after the last insert:
>
> Last inserted ID: 5
> Number of rows: 3
> Number of columns: 2
> Row:  0   Column[0]: ID  Column[1]: Name
> Row:  1   Column[0]: 2  Column[1]: two
> Row:  2   Column[0]: 3  Column[1]: one
> Row:  3   Column[0]: 4  Column[1]: one
>
> Actually, I would expect this:
>
> Last inserted ID: 4
> Number of rows: 3
> Number of columns: 2
> Row:  0   Column[0]: ID  Column[1]: Name
> Row:  1   Column[0]: 2  Column[1]: two
> Row:  2   Column[0]: 3  Column[1]: one
> Row:  3   Column[0]: 4  Column[1]: one
>
> Interestingly the output is like a like to have it but then I have to
> use any trigger but not an FTS related trigger!
>
> Hartwig
>
>
>
> ___
> 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-

[sqlite] Bug in SQLite 3.6.14 / wrong rowid returned

[Wanadoo Hartwig]

Hello,

I have posted a while ago a bug (at least I think that it is a bug)  
but only in a very abstract form. Now, I have written a C-program  
showing the bug.

Brief description:

An FTS related trigger combination leads to a wrong determination of  
the last inserted row id. Triggers that are not related to FTS are  
working fine.


#include 
#include 

#include "sqlite3.h"

void OnErrorExit(sqlite3* database, int rc, char* errorMessage)
{
   if (rc != SQLITE_OK)
   {
 if (errorMessage != NULL)
 {
   fprintf(stderr,"SQL error: %s\n",errorMessage);
   sqlite3_free(errorMessage);
 } /* if */
 sqlite3_close(database);
 exit(1);
   } /* if */
}

void ShowDatabaseContents(sqlite3* database)
{
   char* errorMessage;
   char** result;
   char* sqlStatement;

   int noOfColumns, noOfRows;
   int rc;

   sqlite3_int64 lastInsertedID;


   lastInsertedID = sqlite3_last_insert_rowid(database);
   printf("Last inserted ID: %d\n",(int) lastInsertedID);
   sqlStatement = "SELECT * FROM Simple;";
   rc =  
sqlite3_get_table 
(database,sqlStatement,&result,&noOfRows,&noOfColumns,&errorMessage);
   OnErrorExit(database,rc,errorMessage);

   printf("Number of rows: %d\n",noOfRows);
   printf("Number of columns: %d\n",noOfColumns);
   for (int i=0; ihttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database exception question

[D. Richard Hipp]

On Jun 29, 2009, at 3:13 AM, Wenton Thomas wrote:

> When executing   "pragma integrity_check" ,sqlite will return a   
> error message if  there exist something wrong
> with  the database file.
> Now I wonder  what's the max length of the error message?
> Is it possiable  that the length  exceeds  255?


It is easily possible if the database is badly corrupted.

You can limit the amount of error text returned by integrity_check by  
adding an argument.  For example:

 PRAGMA integrity_check(2);

Will return at most two database structure errors before it gives up  
and quits.   This is not a promise, but I do not think 2 errors are  
likely to exceed 255 characters in length.

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



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

Re: [sqlite] Building sqlite

[Doug]

You may need SQLITE_THREADSAFE depending on how you're using the library

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Robert Dailey
> Sent: Monday, June 29, 2009 1:51 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Building sqlite
> 
> On Mon, Jun 29, 2009 at 12:58 PM, Eric Minbiole
> wrote:
> 
> > > I'm currently on Windows and I've set up a python script to
> download the
> > > sqlite3 amalgamation. However, the ZIP file contains no build
> system for
> > > sqlite. I had to create a custom CMake script to build sqlite3 into
> a
> > > library. I do not wish to compile the C file with my source, it
> needs to
> > be
> > > a static library.
> > >
> > > Do you guys have a package that contains a build system for sqlite3
> for
> > > Windows?
> >
> > Can you use a dynamic library instead of static?  If so, there is a
> > precompiled Windows dll for download on the SQLite site.  Otherwise,
> you
> > will probably need to build manually, as you have done.
> >
> > (Another option might be to use one of the myriad of Dll -> Static
> lib
> > converters available, though this seems like more work than it's
> worth.)
> 
> 
> Thanks for the response. I actually have no need to use a shared
> library,
> since it isn't going to be shared. A static library is what I need and
> what
> I'm currently building. As long as there are no preprocessor
> definitions or
> other compilation flags that I need to be aware of for sqlite3, then
> what I
> have now will work just fine and it can be automated. It's a very
> simple
> CMake script and I'd be willing to contribute it to the project if you
> would
> like. You can package this up with the ZIP file and the tarball
> amalgamations so that people have the option of building a static
> library.
> 
> Let me know. Thanks again for the help.
> ___
> 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] Building sqlite

[Robert Dailey]

On Mon, Jun 29, 2009 at 12:58 PM, Eric Minbiole
wrote:

> > I'm currently on Windows and I've set up a python script to download the
> > sqlite3 amalgamation. However, the ZIP file contains no build system for
> > sqlite. I had to create a custom CMake script to build sqlite3 into a
> > library. I do not wish to compile the C file with my source, it needs to
> be
> > a static library.
> >
> > Do you guys have a package that contains a build system for sqlite3 for
> > Windows?
>
> Can you use a dynamic library instead of static?  If so, there is a
> precompiled Windows dll for download on the SQLite site.  Otherwise, you
> will probably need to build manually, as you have done.
>
> (Another option might be to use one of the myriad of Dll -> Static lib
> converters available, though this seems like more work than it's worth.)


Thanks for the response. I actually have no need to use a shared library,
since it isn't going to be shared. A static library is what I need and what
I'm currently building. As long as there are no preprocessor definitions or
other compilation flags that I need to be aware of for sqlite3, then what I
have now will work just fine and it can be automated. It's a very simple
CMake script and I'd be willing to contribute it to the project if you would
like. You can package this up with the ZIP file and the tarball
amalgamations so that people have the option of building a static library.

Let me know. Thanks again for the help.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Building sqlite

[Eric Minbiole]

> I'm currently on Windows and I've set up a python script to download the
> sqlite3 amalgamation. However, the ZIP file contains no build system for
> sqlite. I had to create a custom CMake script to build sqlite3 into a
> library. I do not wish to compile the C file with my source, it needs to be
> a static library.
> 
> Do you guys have a package that contains a build system for sqlite3 for
> Windows?

Can you use a dynamic library instead of static?  If so, there is a 
precompiled Windows dll for download on the SQLite site.  Otherwise, you 
will probably need to build manually, as you have done.

(Another option might be to use one of the myriad of Dll -> Static lib 
converters available, though this seems like more work than it's worth.)

~Eric

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

[sqlite] Building sqlite

[Robert Dailey]

I'm currently on Windows and I've set up a python script to download the
sqlite3 amalgamation. However, the ZIP file contains no build system for
sqlite. I had to create a custom CMake script to build sqlite3 into a
library. I do not wish to compile the C file with my source, it needs to be
a static library.

Do you guys have a package that contains a build system for sqlite3 for
Windows?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] importing data to sqlite from stdin

[Tguru]

Hi there!
Absolutely, you can manage all of your databases with the same tool. Because
of the large amount of connectors with databases, you can work with many
databases.

These are all the connectors:
http://www.talendforge.org/components/index.php



pablopico wrote:
> 
> 
> Hi there,
> This Talend you mentioned caught my eye. Can one use this tool as a
> general database browser tool.
> Palmer
> 
>> Date: Mon, 29 Jun 2009 08:56:58 -0700
>> From: g...@talend.com
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] importing data to sqlite from stdin
>> 
>> 
>> What you could use is an open source ETL that has connectors with Sqlite.
>> 
>> Talend Open Studio is an open source ETL tool for data integration and
>> migration experts. It's easy to learn for a non-technical user. What
>> distinguishes Talend, when it comes to business users, is the tMap
>> component. It allows the user to get a graphical and functional view of
>> integration processes. 
>> 
>> For more information: http://www.talend.com/
>> 
>> 
>> 
>> Robert Citek-2 wrote:
>> > 
>> > On occasion I have had a need to import large amounts of data from
>> > standard input via a pipe.  Here's an example of how to import from a
>> > pipe using sqlite3 on Ubuntu:
>> > 
>> > $ { grep -v '^#' <> > # create the table
>> > create table foo (bar int) ;
>> > eof
>> > } | sqlite3 foo.sqlite
>> > 
>> > $ seq 1 100 | sqlite3 foo.sqlite '.imp "/dev/stdin" "foo"'
>> > 
>> > $ sqlite3 foo.sqlite 'select count(*) from foo; '
>> > 100
>> > 
>> > Regards,
>> > - Robert
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> > 
>> > 
>> 
>> -- 
>> View this message in context:
>> http://www.nabble.com/importing-data-to-sqlite-from-stdin-tp24256696p24256851.html
>> Sent from the SQLite mailing list archive at Nabble.com.
>> 
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> _
> Hotmail® has ever-growing storage! Don’t worry about storage limits. 
> http://windowslive.com/Tutorial/Hotmail/Storage?ocid=TXT_TAGLM_WL_HM_Tutorial_Storage_062009
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/importing-data-to-sqlite-from-stdin-tp24256696p24257964.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] Memory leak with sqlite3_exec on qnx 6.4.1

[Simon Slavin]

On 29 Jun 2009, at 1:11pm, Greg Morehead wrote:

> So it seems by best bet is to close and open the connection once  
> every 5 minutes or so?

No.  The library is unlikely to have a leak of that kind in.  Are you  
certain that what you're seeing is a real leak ?  If so, the leak may  
be in your own code.  Otherwise, check to see that SQLite is not  
staying entirely within the expected cache memory usage:

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

It's possible to manage the cache yourself

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

but frankly this is above my head and I wouldn't know where to start.

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 Slavin]

On 29 Jun 2009, at 1:05pm, BareFeet wrote:

> How can I get just the column headers without all the result rows?

Turn headers on, then perform a search which gives no results.

It's difficult in the command-line tool, but easy using function calls  
since there's a function call specially intended for it:



Note that if your SELECT uses expressions, rather than direct  
references to columns, the results may not be what you expected.

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

Re: [sqlite] Exact Match Syntax with FTS3?

[Raeldor]

Hi,

Thank you for your reply.  By exact, I mean "this is a test case" should
only be returned as a match if the search term is "this is a test case" and
not "test case" or "this is a" or anything such like.  I added a simple AND
clause as per your suggestion and it seems to work great.  It's still very
fast, which means it must be doing the  '=' comparison after the result has
been confirmed from the FTS.

Thank you!

Thanks
Ray


Scott Hess wrote:
> 
> On Mon, Jun 29, 2009 at 8:52 AM, Raeldor wrote:
>> Does anyone know if there is a syntax in FTS3 to perform an exact match?
>>  I
>> couldn't see any examples in the sqlite documentation for this, but I
>> think
>> there are some operators (*) that are not covered in that documentation.
> 
> Not sure what you mean by "exact match".  "SELECT docid FROM fts_table
> WHERE col MATCH 'string'" will return docids for rows which contain
> 'string'.  It's not "exact" in the sense that by default matching is
> case-insensitive.
> 
> If you need to find "eXact", then fts can't do it directly, but you
> could do post-processing.  Maybe "SELECT docid, col FROM fts_table
> WHERE col MATCH 'eXact' HAVING col LIKE '%eXact%'".  That would also
> match cases where "exact" is present as a word and "eXact" is a
> substring of a larger string.  My syntax might be wrong, but the
> overall picture should be something like that.  Also, you could of
> course use AND instead of HAVING (I used HAVING mainly because I want
> the fts index for finding the possibilities then the expression to
> filter them).
> 
> -scott
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/Exact-Match-Syntax-with-FTS3--tp24256776p24257664.html
Sent from the SQLite mailing list archive at Nabble.com.

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

Re: [sqlite] importing data to sqlite from stdin

[palmer ristevski]

Hi there,
This Talend you mentioned caught my eye. Can one use this tool as a general 
database browser tool.
Palmer

> Date: Mon, 29 Jun 2009 08:56:58 -0700
> From: g...@talend.com
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] importing data to sqlite from stdin
> 
> 
> What you could use is an open source ETL that has connectors with Sqlite.
> 
> Talend Open Studio is an open source ETL tool for data integration and
> migration experts. It's easy to learn for a non-technical user. What
> distinguishes Talend, when it comes to business users, is the tMap
> component. It allows the user to get a graphical and functional view of
> integration processes. 
> 
> For more information: http://www.talend.com/
> 
> 
> 
> Robert Citek-2 wrote:
> > 
> > On occasion I have had a need to import large amounts of data from
> > standard input via a pipe.  Here's an example of how to import from a
> > pipe using sqlite3 on Ubuntu:
> > 
> > $ { grep -v '^#' < > # create the table
> > create table foo (bar int) ;
> > eof
> > } | sqlite3 foo.sqlite
> > 
> > $ seq 1 100 | sqlite3 foo.sqlite '.imp "/dev/stdin" "foo"'
> > 
> > $ sqlite3 foo.sqlite 'select count(*) from foo; '
> > 100
> > 
> > Regards,
> > - Robert
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > 
> > 
> 
> -- 
> View this message in context: 
> http://www.nabble.com/importing-data-to-sqlite-from-stdin-tp24256696p24256851.html
> Sent from the SQLite mailing list archive at Nabble.com.
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_
Hotmail® has ever-growing storage! Don’t worry about storage limits. 
http://windowslive.com/Tutorial/Hotmail/Storage?ocid=TXT_TAGLM_WL_HM_Tutorial_Storage_062009
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Comparison of numbers as text

[Tim Largy]

>> Can someone explain what is going on in the third select statement
>> below? I would have expected it to return a row because the number is
>> quoted.
>>
>> sqlite> select 'foo' where 1 in (1, '2', 'three');
>> foo
>> sqlite> select 'foo' where 2 in (1, '2', 'three');
>> sqlite> select 'foo' where '2' in (1, '2', 'three');
>> sqlite> select 'foo' where 'three' in (1, '2', 'three');
>> foo
>
>  The third select does return 'foo' for me.
>
>  Tested on 3.6.11 and 3.4.0, Mac OS X Intel.

I'm using SQLite version 3.3.7 on a Debian-based OS. I'm stuck with
this version, so I hope I can find a work around.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Exact Match Syntax with FTS3?

[Scott Hess]

On Mon, Jun 29, 2009 at 8:52 AM, Raeldor wrote:
> Does anyone know if there is a syntax in FTS3 to perform an exact match?  I
> couldn't see any examples in the sqlite documentation for this, but I think
> there are some operators (*) that are not covered in that documentation.

Not sure what you mean by "exact match".  "SELECT docid FROM fts_table
WHERE col MATCH 'string'" will return docids for rows which contain
'string'.  It's not "exact" in the sense that by default matching is
case-insensitive.

If you need to find "eXact", then fts can't do it directly, but you
could do post-processing.  Maybe "SELECT docid, col FROM fts_table
WHERE col MATCH 'eXact' HAVING col LIKE '%eXact%'".  That would also
match cases where "exact" is present as a word and "eXact" is a
substring of a larger string.  My syntax might be wrong, but the
overall picture should be something like that.  Also, you could of
course use AND instead of HAVING (I used HAVING mainly because I want
the fts index for finding the possibilities then the expression to
filter them).

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

Re: [sqlite] Memory leak with sqlite3_exec on qnx 6.4.1

[Eric Minbiole]

> So it seems by best bet is to close and open the connection once every 5 
> minutes or so?

While this might "work", I don't think you should resort to that.  As 
Kees noted earlier, there will be performance drawbacks if you 
close/re-open the database.

As others have indicated, the heap growth is likely due to SQLite 
intentionally caching frequently used disk pages in order to improve 
performance.  Rather than closing/re-opening, I suggest that you set the 
page cache size to a limit that is reasonable for your application:

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

The default is around 2MB.  You can decrease to as little as 10KB.  Note 
that there are some other buffers used by SQLite (Scratch, Lookaside, 
etc).  However, these are typically small compared to the page cache. 
Additional info here:

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

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

Re: [sqlite] importing data to sqlite from stdin

[Tguru]

What you could use is an open source ETL that has connectors with Sqlite.

Talend Open Studio is an open source ETL tool for data integration and
migration experts. It's easy to learn for a non-technical user. What
distinguishes Talend, when it comes to business users, is the tMap
component. It allows the user to get a graphical and functional view of
integration processes. 

For more information: http://www.talend.com/



Robert Citek-2 wrote:
> 
> On occasion I have had a need to import large amounts of data from
> standard input via a pipe.  Here's an example of how to import from a
> pipe using sqlite3 on Ubuntu:
> 
> $ { grep -v '^#' < # create the table
> create table foo (bar int) ;
> eof
> } | sqlite3 foo.sqlite
> 
> $ seq 1 100 | sqlite3 foo.sqlite '.imp "/dev/stdin" "foo"'
> 
> $ sqlite3 foo.sqlite 'select count(*) from foo; '
> 100
> 
> Regards,
> - Robert
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 

-- 
View this message in context: 
http://www.nabble.com/importing-data-to-sqlite-from-stdin-tp24256696p24256851.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] Exact Match Syntax with FTS3?

[Raeldor]

Hi All,

Does anyone know if there is a syntax in FTS3 to perform an exact match?  I
couldn't see any examples in the sqlite documentation for this, but I think
there are some operators (*) that are not covered in that documentation.

Thanks
Rael
-- 
View this message in context: 
http://www.nabble.com/Exact-Match-Syntax-with-FTS3--tp24256776p24256776.html
Sent from the SQLite mailing list archive at Nabble.com.

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

[sqlite] importing data to sqlite from stdin

[Robert Citek]

On occasion I have had a need to import large amounts of data from
standard input via a pipe.  Here's an example of how to import from a
pipe using sqlite3 on Ubuntu:

$ { grep -v '^#' 

Re: [sqlite] SQL result to struct

[Shaun Seckman (Firaxis)]

I also wanted a similar functionality but in the end decided to use a
prepared statement w/ sqlite3_column_* methods.  My two main reasons
were a) it didn't seem like there were any methods to dumping the data
to a struct like that and b) I was able to do any sort of validation I
desired such as ensuring equivalent types and validating against column
names.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik
Sent: Monday, June 29, 2009 8:17 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQL result to struct

Mark Flipphi wrote:
> I need some help with storing the result from a query into a struct.
>
> I have a query like :
>
> SELECT Echo.Position, Echo.Depth, Echo.dBLevel, Echo.Time,
> Echo.CommonIdx, Common.ChanNr
> FROM Echo JOIN Common ON Echo.CommonIdx = Common.CommonIdx
> ORDER BY Echo.Position ASC
>
> and i need to store the information in a struct like :
>
> typedef struct
> {
>UINT64 nPosition;
>UINT16 nDepth;
>UINT8   ndBLevel;
>UINT64 nTime;
>UINT64 nIdx;
>UINT8   nChannel;
> }
>
> now i use the function :
> rc = sqlite3_exec(m_SqLiteDB, sQuery, callback, 0, &zErrMsg);

You may find it easier to use sqlite3_prepare, sqlite3_step, 
sqlite3_column_* et al. For one thing, you'll avoid conversion from 
integers to strings and back.

Igor Tandetnik



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

Re: [sqlite] Memory leak with sqlite3_exec on qnx 6.4.1

[Jay A. Kreibich]

On Mon, Jun 29, 2009 at 08:10:20AM -0400, Greg Morehead scratched on the wall:
> I'm looking specifically at the heap for the process I wrote.

  Well, that's where it would be

   -j

> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org]on Behalf Of Jay A. Kreibich
> Sent: Friday, June 26, 2009 5:32 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Memory leak with sqlite3_exec on qnx 6.4.1
> 
> 
> On Fri, Jun 26, 2009 at 05:07:16PM -0400, Greg Morehead scratched on the wall:
> > 
> > If I close then reopen the database all my memory is recovered.  
> > 
> > Is this by design???  I was intending on keeping a connection open most of 
> > time.
> 
>   Are you sure you're not looking at the page cache?
> 
>-j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory leak with sqlite3_exec on qnx 6.4.1

[Greg Morehead]

I'm looking specifically at the heap for the process I wrote.

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org]on Behalf Of Jay A. Kreibich
Sent: Friday, June 26, 2009 5:32 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Memory leak with sqlite3_exec on qnx 6.4.1


On Fri, Jun 26, 2009 at 05:07:16PM -0400, Greg Morehead scratched on the wall:
> 
> If I close then reopen the database all my memory is recovered.  
> 
> Is this by design???  I was intending on keeping a connection open most of 
> time.

  Are you sure you're not looking at the page cache?

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
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 result to struct

[Igor Tandetnik]

Mark Flipphi wrote:
> I need some help with storing the result from a query into a struct.
>
> I have a query like :
>
> SELECT Echo.Position, Echo.Depth, Echo.dBLevel, Echo.Time,
> Echo.CommonIdx, Common.ChanNr
> FROM Echo JOIN Common ON Echo.CommonIdx = Common.CommonIdx
> ORDER BY Echo.Position ASC
>
> and i need to store the information in a struct like :
>
> typedef struct
> {
>UINT64 nPosition;
>UINT16 nDepth;
>UINT8   ndBLevel;
>UINT64 nTime;
>UINT64 nIdx;
>UINT8   nChannel;
> }
>
> now i use the function :
> rc = sqlite3_exec(m_SqLiteDB, sQuery, callback, 0, &zErrMsg);

You may find it easier to use sqlite3_prepare, sqlite3_step, 
sqlite3_column_* et al. For one thing, you'll avoid conversion from 
integers to strings and back.

Igor Tandetnik



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

Re: [sqlite] Memory leak with sqlite3_exec on qnx 6.4.1

[Greg Morehead]

So it seems by best bet is to close and open the connection once every 5 
minutes or so?


-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org]on Behalf Of Kees Nuyt
Sent: Friday, June 26, 2009 5:31 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Memory leak with sqlite3_exec on qnx 6.4.1


On Fri, 26 Jun 2009 17:07:16 -0400, "Greg Morehead"
 wrote:

>
>If I close then reopen the database all my memory is recovered.  
>
>Is this by design???  

Yes, what you see is probably the page cache.


>I was intending on keeping a connection open most of time.

That's a good idea, for at least two reasons:

- opening a connection has to parse the schema, 
  and though it's fast code, it should be avoided.

- the contents of the page cache aren't wasted, 
  it may be re-used by subsequent statements.

-- 
  (  Kees Nuyt
  )
c[_]
___
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] Column headers of result

[BareFeet]

Thanks Jens and John for your replies.

Yes, I am familiar with the "headers on" option. Sorry, I should have  
mentioned.

However, that prepends the headers to the result rows. It would also  
be ambiguous if a column header contains a pipe or quote or newline  
(which is unlikely but possible).

How can I get just the column headers without all the result rows?

Thanks,
Tom
BareFeet

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

[sqlite] SQL result to struct

[Mark Flipphi]

Hello,

I need some help with storing the result from a query into a struct.

I have a query like :

SELECT Echo.Position, Echo.Depth, Echo.dBLevel, Echo.Time, 
Echo.CommonIdx, Common.ChanNr
FROM Echo JOIN Common ON Echo.CommonIdx = Common.CommonIdx
ORDER BY Echo.Position ASC

and i need to store the information in a struct like :

typedef struct
{
UINT64 nPosition;
UINT16 nDepth;
UINT8   ndBLevel;
UINT64 nTime;
UINT64 nIdx;
UINT8   nChannel;
}

now i use the function :
rc = sqlite3_exec(m_SqLiteDB, sQuery, callback, 0, &zErrMsg);

static int callback(void *NotUsed, int argc, char **argv, char **azColName)
{
//print to screen
}

The query runs, and the results are shown in the callback function, but 
i'm sure there is a better way
to strore the data in the struct then converting the strings back to 
values.

Are there any examples ?

With kind regards,

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

Re: [sqlite] Column headers of result

[John Machin]

On 29/06/2009 2:57 PM, BareFeet wrote:
> Hi,
> 
> Is there any way in the command line to get the columns in a query  
> result?
> 
> For example, given an ad-hoc SQL command, such as:
> 
> begin;
> insert into MyTableOrView select * from SomeSource;
> select * from MyTableOrView join SomeOtherTableOrView where condition;
> end;
> 
> how can I get the column headers in the result?
> 
> I know I can get the column info of a table using pragma table_info,  
> but I don't think that works for an ad-hoc query.
> 

SQLite version 3.6.14
Enter ".help" for instructions<<<=== ever noticed this before?
Enter SQL statements terminated with a ";"
sqlite> .help
[snip]
.header(s) ON|OFF  Turn display of headers on or off
[snip]
sqlite> select 1 as one, 2 as two;
1|2
sqlite> .header on
sqlite> select 1 as one, 2 as two;
one|two
1|2
sqlite> select 1 as one, 2 as two, 3;
one|two|3
1|2|3
sqlite> create table foo (bar int);
sqlite> insert into foo values(42);
sqlite> select * from foo;
bar
42
sqlite> select bar as rab from foo;
rab
42
sqlite>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database triggers

[Dan]

On Jun 29, 2009, at 2:44 PM, Kris Groves wrote:

> Given the trigger creation statement :
> CREATE TRIGGER triggername DELETE ON tablename BEGIN statements; END
>
> When does it happen ? BEFORE or AFTER ?
>
> I've read http://sqlite.org/lang_createtrigger.html, but it is  
> unclear to me.  If I had to guess, I would say the trigger is a  
> BEFORE trigger, but I'd rather not guess, so I'm asking here.
>

You guess correctly.


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

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

[sqlite] View error

[Bruno Carlus]

Hi,

when I try to execute
SELECT num_id_cycle FROM vw_last_cycles_mapping WHERE num_cycle = 10

in a c function it issues a column does not exixt error for num_cycle 
... but it works when I execute the same request in the sqlite3 shell ...

vw_last_cycles is a view listing the 10 last inserted rows of a table.

any idea ?

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

Re: [sqlite] Column headers of result

[Jens Miltner]

Am 29.06.2009 um 06:57 schrieb BareFeet:

> Hi,
>
> Is there any way in the command line to get the columns in a query
> result?
>
> For example, given an ad-hoc SQL command, such as:
>
> begin;
> insert into MyTableOrView select * from SomeSource;
> select * from MyTableOrView join SomeOtherTableOrView where condition;
> end;
>
> how can I get the column headers in the result?
>
> I know I can get the column info of a table using pragma table_info,
> but I don't think that works for an ad-hoc query.

use ".headers on" in the commandline (or run the tool with the -header  
option) - the first line will contain the column names.

HTH,


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

[sqlite] Database triggers

[Kris Groves]

Given the trigger creation statement :
CREATE TRIGGER triggername DELETE ON tablename BEGIN statements; END

When does it happen ? BEFORE or AFTER ?

I've read http://sqlite.org/lang_createtrigger.html, but it is unclear to me.  
If I had to guess, I would say the trigger is a BEFORE trigger, but I'd rather 
not guess, so I'm asking here.

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

Re: [sqlite] database exception question

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Wenton Thomas wrote:
> Now I wonder  what's the max length of the error message? 
> Is it possiable  that the length  exceeds  255?

In theory the message could be up to 2GB in length (eg if generated by a
VFS).  In practise it is likely to be about a sentence long.  However
your question indicates some other underlying issue.  If you are copying
the message into a buffer then you should do so safely.  If your buffer
is limited in size then you'll have to truncate the message.

If the character limit is due to displaying the message to a user, then
it doesn't really matter either since the message will be meaningless
technobabble anyway.

In general you cannot recover from a corrupt database.  (To do so would
require redundant copies of information in the database so that you
could pick information from a "good" copy on error.)  Your efforts are
best spent not having a corrupt database in the first place :-)

BTW if you do want to corrupt a database, here are some methods:

  http://www.sqlite.org/lockingv3.html  (section 6)
  http://www.sqlite.org/pragma.html#modify (eg journal=memory,
synchronous=off)

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkpIbxgACgkQmOOfHg372QQDNQCdHM7jn7KTZE4eJgPuWKR7Ofi+
yxwAn2/8EaJ0HTfhyf/VFT1xvxgsrYy7
=d9Sh
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] database exception question

[Wenton Thomas]

When executing   "pragma integrity_check" ,sqlite will return a  error message 
if  there exist something wrong
with  the database file.
Now I wonder  what's the max length of the error message? 
Is it possiable  that the length  exceeds  255?



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