Re: [sqlite] Different ROWID and PRIMARY KEY values

[Luca Moratto]

Thanks Gerry,

but I can Create my table in memory from an attached db, I can Insert 
new rows and I can Select the new rows and all values are correct, 
except for my key field that is 0.

My Insert statement is INSERT INTO myTable VALUES (?,?,?);
I prepare this statement with sqlite3_prepare
I bind all values with sqlite3_bind_int, sqlite3_bind_text, ...
I bind my key field with sqlite3_bind_null
I exec with sqlite3_step

It works because if I exec e SELECT rowid,* FROM myTable I can find all 
the new rows and all values except for my key field (INTEGER PRIMARY 
KEY, I have tried also AUTOINCREMENT) that is 0, but rowid is correct


I need to use a SELECT *, I can't get rowId so how and when my key field 
has the same value of rowId, why it does't works?


Thanks in advanced
Luke




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] two questuins about memory usage and precompiled sqlite_stmt in memory db

[dszhang]

hi all,
first of all,thank to everyone who read this mail.
i use the 3.3.12 version of sqlite in my program base flash fs and OS20 os in 
my DYV terminate.
the first questuin about memory usage in my memory db is that,previously  i do 
sqlite3_open(:memory:)  and  sqlite_exec(CREATE TABLE  ) and insert about 2 
records in the db,after that ,each time i use sqlite_exec(SELECT xxx ,xxx FROM 
xxx WHERE xxx AND xxx ORDER BY xxx),the memory take by sqlite  increas about 
135K BYTE. I think that's because when sqlite do sqlite_exec(SELECT xxx ,xxx 
FROM xxx WHERE xxx AND xxx ORDER BY xxx) ,it take some memory as cache for data 
pulled from DB,but it not free these memory after retrun from 
sqlite_exec(SELECT xxx ,xxx FROM xxx WHERE xxx AND xxx ORDER BY xxx), just want 
to speed up the next sqlite_exec(SELECT xxx ,xxx FROM xxx WHERE xxx AND xxx 
ORDER BY xxx) call. that's right? if that's right,is there are any way to limit 
the cache sqlite taked? i read PRAGMA cache_size = Number-of-pages in PRAGMA 
document,it's the answer?
the next question is  i use precompiled sqlite_stmt as follow code, is there 
something wrong in it? in my test program i haven't find the bug, but i'm not 
sure about it.
static sqlite3_stmt *stmt_insert_schedule=NULL;

static int Eit_Insert_ScheduleEvent_IntoTable( SQLITE_EVENT *sqlite_event)
{
 int result=0;
 int rc=0;

 
 if(OS_MutSemTake ( mutexForDB))
  printf("OS_MutSemTake ( mutexForDB)  8 error\n"); 
 
 if(stmt_insert_schedule==NULL)
  {
   rc = sqlite3_prepare_v2( db,  "INSERT INTO " EIT_SCHEDULE" ( " TABLE_ID_DB 
","SECTION_NUMBER ","VERSION_NUMBER","
 SERVICE_ID","TS_ID","ON_ID","CRC_32","EVENT_ID","START_TIME_UTC_M","
 START_TIME_UTC_L","DURATION_3 ","CONTENT_NIBBLE_LEVEL","
 PARRENT_RATE_COUNTRY_CODE_3","PARRENT_RATE_RATE","
 TSE_DES_REFERENCE_SERVICE_ID","TSE_DES_REFERENCE_EVENT_ID","
 ISO_639_LANGUAGE_CODE_SHORT_3","NAME_LENGTH","EVENT_NAME","SHORT_LENGTH","
 SHORT_EVENT","DESCRIPTOR_NUMBER","LAST_DESCRIPTOR_NUMBER","
 ISO_639_LANGUAGE_CODE_EXTEND_3","LENTH_OF_ITEM","ITEM","
 TEXT_LENGTH","TEXT_EVENT
 ")VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);", -1, 
_insert_schedule, NULL);

  if ((rc != SQLITE_OK) && (rc != SQLITE_ROW) && (rc != SQLITE_DONE))
  {
 printf( "%s(%d): Failed with error code 0x%x = 
%s\n",__FILE__,__LINE__, rc, sqlite3_errmsg(db));
  if (stmt_insert_schedule)
   {
   rc = sqlite3_finalize(stmt_insert_schedule);
stmt_insert_schedule=NULL;
   }
 if(OS_MutSemGive ( mutexForDB))
   printf("OS_MutSemGive ( mutexForDB) 9 error\n");
  return 1;
  }
  }

 rc=sqlite3_bind_int(stmt_insert_schedule, 1, sqlite_event->table_id);
 rc|=sqlite3_bind_int(stmt_insert_schedule, 2, sqlite_event->section_number);
 rc|=sqlite3_bind_int(stmt_insert_schedule, 3, sqlite_event->version_number);
 rc|=sqlite3_bind_int(stmt_insert_schedule, 4, sqlite_event->service_id);
 rc|=sqlite3_bind_int(stmt_insert_schedule, 5, sqlite_event->ts_id);
 rc|=sqlite3_bind_int(stmt_insert_schedule, 6, sqlite_event->on_id);
 rc|=sqlite3_bind_int(stmt_insert_schedule, 7, sqlite_event->crc_32);
 rc|=sqlite3_bind_int(stmt_insert_schedule, 8, sqlite_event->event_id);
 rc|=sqlite3_bind_int(stmt_insert_schedule, 9, sqlite_event->utc_m);
 rc|=sqlite3_bind_int(stmt_insert_schedule, 10, sqlite_event->utc_l);
 rc|=sqlite3_bind_int(stmt_insert_schedule, 11, 
(sqlite_event->duration[0]<<16)|(sqlite_event->duration[1]<<8)|
(sqlite_event->duration[2]));
 rc|=sqlite3_bind_int(stmt_insert_schedule, 12, 
sqlite_event->content_nibble_level);
 rc|=sqlite3_bind_int(stmt_insert_schedule, 13, 
(sqlite_event->country_code[0]<<16)|
(sqlite_event->country_code[1]<<8)|(sqlite_event->country_code[2]));
 rc|=sqlite3_bind_int(stmt_insert_schedule, 14, sqlite_event->rate);
 rc|=sqlite3_bind_int(stmt_insert_schedule, 15, 
sqlite_event->reference_service_id);
 rc|=sqlite3_bind_int(stmt_insert_schedule, 16, 
sqlite_event->reference_event_id);
 rc|=sqlite3_bind_int(stmt_insert_schedule, 17, 
(sqlite_event->ISO_639_language_code_short[0]<<16)|

(sqlite_event->ISO_639_language_code_short[1]<<8)|(sqlite_event->ISO_639_language_code_short[2]));
 rc|=sqlite3_bind_int(stmt_insert_schedule, 18, sqlite_event->name_length);
 rc|=sqlite3_bind_text(stmt_insert_schedule, 19, sqlite_event->event_name, 
sqlite_event->name_length,  SQLITE_STATIC );
 rc|=sqlite3_bind_int(stmt_insert_schedule, 20, sqlite_event->short_length);
 rc|=sqlite3_bind_text(stmt_insert_schedule, 21, sqlite_event->short_event, 
sqlite_event->short_length,  SQLITE_STATIC );

 rc|=sqlite3_bind_int(stmt_insert_schedule, 22, 
sqlite_event->descriptor_number);
 rc|=sqlite3_bind_int(stmt_insert_schedule, 23, 
sqlite_event->last_descriptor_number);
 rc|=sqlite3_bind_int(stmt_insert_schedule, 24, 
(sqlite_event->ISO_639_language_code_extend[0]<<16)|

Re: [sqlite] Fastest way of UPDATE'ing

[jose isaias cabrera]

"Daniel Önnerby" wrote,

If you attach both databases I guess you can use the replace-command since 
the uniqueID exists in both databases.


REPLACE INTO server.table SELECT * FROM client.table;

It would also be helpful to keep a local track of what ID has not been 
updated on the server yet, so you do not need to update all records:


REPLACE INTO server.table SELECT * FROM client.table WHERE id>[update from 
ID];


Wow!  This is fast!  I knew that there had to be an easier and faster way, 
but this one is amazing!


Thanks guys.





jose isaias cabrera wrote:


Greetings!

I have this scenario...

6 users with local dbs
1 network db to backup all those DBs and to share info.

Every local DB unique record id based on the network DB.  So, before each 
user creates a new record, the tool asks the network ID for the next 
available sequential record and creates a new record on the local DB 
using that unique id from the network DB.


The question is, what is the fastest way to UPDATE the main DB?  Right 
now, what I am doing is a for each record and UPDATE all the values where 
id=.  Is there a faster way?


Thanks,

josé




-

To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] ISO8601 8-byte packed date type

[Tom Olson]

I was pretty tires when I wrote the original post and realize that I should
have been more specific.  So here goes!

I have created an 8-byte packed data type that stores date, time and
timezone information.  It has an effective date range of:

-142399-01-01 00:00:00.000 (BCE) through +143027-10-14 08:59:00.991 (CE)

All dates in this range are accurate to 1/1000 of a second

I have helper routines for date arithmetic and converting to/from Julian
dates, UNIX dates and Delphi TDateTime.  There is also a parser for ISO8601
formatted strings for the following formats:

General dates - 2007-03-05 12:34:56.789
Day of Year dates - 2007-365
Week dates - 2007-W51-7

If anyone would find this useful/helpful please let me know.

Tom
tolson at whamware.com


Tom Olson wrote:
> 
> Hello to all,
> 
> I am working on an 8-byte data type that stores date time and timezone
> information and provides helper routines for date arithmatic as well as
> conversion to/from strings, julian dates, UNIX date times, etc...  The
> type can be stored in a 64-bit integer or as an 8 byte blob.  The current
> implementation is in Delphi.  Would anyone be interested in such a
> feature?
> 
> Tom
> 

-- 
View this message in context: 
http://www.nabble.com/ISO8601-8-byte-packed-date-type-tf3364823.html#a9388503
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] import operation - primary key need to be automatically generated by SQLite (not from csv file)

[RohitPatel9999]

Friends, for value of fist column in csv, any of the following do not work
(e.g.   '', null, 'null' )

,'name1' 
'','name2' 
null,'name3' 
'null','name4' 

Table is
Create table t1
{
 id INTEGER PRIMARY KEY,
 name TEXT
}


No workaround to import null value from csv file for INTEGER PRIMARY KEY ?


Thanks for helping.
Rohit

-- 
View this message in context: 
http://www.nabble.com/import-operation---primary-key-need-to-be-automatically-generated-by-SQLite-%28not-from-csv-file%29-tf3360094.html#a9388070
Sent from the SQLite mailing list archive at Nabble.com.


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] database is locked error on Mac OS X

[T]

I recently installed SQLite 3.3.13, after having used previous
versions. I now get an error:

Error: database is locked


This is not a bug in SQLite - it is a bug in Apple's implementation  
(or more precisely their lack of implementation) of POSIX advisory  
locks for AppleShare mounted volumes.  The SQLite sources include  
an (Apple-supplied) patch to work around the problem.


Thank you :-)


Recompile with

   -DSQLITE_ENABLE_LOCKING_STYLE=1


Pardon my ignorance, but where/how exactly do I enter this before  
recompiling?


Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] stupid man's manual to sqlite?

[Mike Owens]

More of a half is about SQL generalities, an a quarter a copy-paste of the
on-line manual without more comment.  At the end, the horrific index i've
ever seen.


For the record, Apress had the index generated by a third party. I've
used the index myself recently, and frankly it worked for me -- I
found what I was looking for.

The SQL and C API references to which you refer --- taken from the
on-line documentation --- start on page 365. Furthermore, the
inclusion of this material was and has been considered useful by
everyone who reviewed the book, as it serves as an authoritative and
succinct reference. Many technical books include a reference similar
to this as a convenience to the reader.

I devoted 100 pages on the subject of SQL. I tried to systematically
and progressively describe SQL so that someone completely unfamiliar
could read the chapter in order, and by the end of the chapter
understand topics such as 3NF, left outer joins, and the intricacies
of NULL.

For an average programmer who needs to use SQLite, I hope this book
can save a lot of time whether you are a unfamiliar to or experienced
with SQL or the SQLite API. I wrote this book purely because I like
SQLite, wanted to help get the word out, and someone presented me with
the opportunity to write a book. Once I decided to write it, I went to
great lengths to write a book that would be genuinely useful. Apress
has put a lot of time and effort into making this a useful book as
well.

As I said in the book, and I think on this list, I am always open to
suggestions and feedback. If you really feel the book has failed you
in some way, or missed an important topic, I would like to know about
it so I can try to make it better should it ever make it to a second
edition. While I have many other things going on in life just like
everybody else, it is important to me that the book serves its
purpose, and I am more than happy to take whatever efforts required to
do so.

-- Mike

On 3/8/07, A.J.Millan <[EMAIL PROTECTED]> wrote:



> Thank you i will try my possibilities buying from amazon, while i am based
in
> the Czech republic.
> Unfortunately it is little bit expensive for me, to buy it without looking
> into it before.
> Jakub

I would recommend not to waste your's money in that book.

More of a half is about SQL generalities, an a quarter a copy-paste of the
on-line manual without more comment.  At the end, the horrific index i've
ever seen.

A.J.Millan
ZATOR Systems.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] 回复： [sqlite] C++ Framework

[Linker M Lin]

It's something like ACE Framework？
 
--
Linker M Lin
[EMAIL PROTECTED]



-
To unsubscribe, send email to [EMAIL PROTECTED]
-












___ 
Mp3疯狂搜-新歌热歌高速下
http://music.yahoo.com.cn/?source=mail_mailbox_footer

Re: [sqlite] Different ROWID and PRIMARY KEY values

[Gerry Snyder]

Luca Moratto wrote:

Hi,

I'm using c++ interface for SQLite 3.3.8.

1) I open a db in memory,
2) attach a file with one table with a INTEGER PRIMARY KEY (ATTACH 
DATABASE 'SQliteFile.s3db' AS 'attachedDb'),
3) copy in memory the tble from attached db table (CREATE TABLE 
'myTable' AS SELECT * FROM 'attachedDb.myTable')


I don't think this is your problem, but the docs say:

You cannot create a new table with the same name as a table in an 
attached database


It would seem that perhaps this restriction has been removed (or does 
not apply to :memory: databases).


But, the CREATE TABLE AS command does not do everything you (or I) would 
wish:


$ sqlite3 test.sq3
SQLite version 3.3.13
Enter ".help" for instructions
sqlite> create table mytable(a integer primary key);
sqlite> insert into mytable values('1');
sqlite> insert into mytable values('2');
sqlite> select * from mytable;
1
2
sqlite> .exit

Gerald [EMAIL PROTECTED] ~
$ sqlite3 :memory:
SQLite version 3.3.13
Enter ".help" for instructions
sqlite> attach "test.sq3" as attached;
sqlite> create table mynewtable as select * from attached.mytable;
sqlite> select sql from sqlite_main;
SQL error: no such table: sqlite_main
sqlite> select sql from sqlite_master;
CREATE TABLE mynewtable(a integer)
sqlite> select sql from attached.sqlite_master;
CREATE TABLE mytable(a integer primary key)

The "primary key" part of the field definition gets dropped. And 
therefore the table does not behave as you expect. You need to create 
the :memory: table differently.


HTH,

Gerry

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Argh, this must be a very stupid question ...

[Stef Mientki]

Yeah.  Or, if you are alergic to typing, drag the "mydb.db"
icon over top of the "sqlite3.exe" icon and let it go.
  

thanks for the tip,
and indeed I'm alergic to typing,
I want to run it from a GUI ;-)

btw. if I wasn't by accident bounced into your video, I would now still 
be manipulating ini-files, thanks for SQLite!

--
D. Richard Hipp  <[EMAIL PROTECTED]>


  


--
cheers,
Stef Mientki
http://pic.flappie.nl


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Re: Argh, this must be a very stupid question ...

[Stef Mientki]

thanks Igor,

so it was indeed a stupid question !

--
cheers,
Stef Mientki
http://pic.flappie.nl


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Argh, this must be a very stupid question ...

[drh]

"Igor Tandetnik" <[EMAIL PROTECTED]> wrote:
> Stef Mientki <[EMAIL PROTECTED]> wrote:
> > In the (windows) commandline version of sqlite:
> > how do I open / connect an existing database ?
> 
> Run it with database file name on the command line:
> 
> sqlite3.exe mydb.db
> 

Yeah.  Or, if you are alergic to typing, drag the "mydb.db"
icon over top of the "sqlite3.exe" icon and let it go.

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Argh, this must be a very stupid question ...

[Michael Hooker]

I had the same trouble a while ago.  The answer I was given here by Kees 
Nuyt was:


C:\sqlite3\sqlite3.exe C:\sqlite3\DBName.sq3 where "C:\sqlite3\DBName.sq3"
is the full pathname of the database file.  If you don't quote the whole 
pathname and the current directory is not the right one, you create a new 
database in the current directory - probably C:\.


All credit to Kees.

Michael Hooker


- Original Message - 
From: "Stef Mientki" <[EMAIL PROTECTED]>

To: 
Sent: Thursday, March 08, 2007 11:09 PM
Subject: [sqlite] Argh, this must be a very stupid question ...




In the (windows) commandline version of sqlite:
how do I open / connect an existing database ?

--
cheers,
Stef Mientki
http://pic.flappie.nl


-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Argh, this must be a very stupid question ...

[Igor Tandetnik]

Stef Mientki <[EMAIL PROTECTED]> wrote:

In the (windows) commandline version of sqlite:
how do I open / connect an existing database ?


Run it with database file name on the command line:

sqlite3.exe mydb.db

Igor Tandetnik

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Last ID from Primary Key through ODBC

[Mitchell Vincent]

On 3/4/07, Christian Werner <[EMAIL PROTECTED]> wrote:

Dan Kennedy wrote:
>
> On Sat, 2007-03-03 at 19:18 -0500, Mitchell Vincent wrote:
> > I found the nifty ODBC driver for SQLite and have been trying to use
> > it to replace a 2.8 SQlite implementation in some desktop software.
> > It's working very well except for a rather large problem of not being
> > able to call the last_id API functions. I need to get the primary keys
> > assigned to parent tables in order to preserve referential integrity.
> >
> > SELECT MAX(id) FROM TABLE is one option but it's pretty dangerous
> > seeing as multipl people *could* be accessing this database file at
> > the same time..
>
> What if you wrap the INSERT and "SELECT MAX(id)" statements together
> in a transaction?

Or what about issuing a "SELECT last_insert_rowid()" ?



As long as it was inside a transaction (and that works), that's the
ticket! Thanks!

--
- Mitchell Vincent
- K Software - Innovative Software Solutions
- Visit our website and check out our great software!
- http://www.ksoftware.net

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Precompiled download of sqlite3 (v3.3.13) for Linux

[Dennis Cote]

Friedman, Eluzor wrote:

There is one for windows.  Where is the one for Linux?

Thanks

PS I am a newbie. 



  


You should check with your distribution's package manager and/or package 
repository if you don't wan't to build from the source files at the 
sqlite site. Almost all distributions have an install package for sqlite 
but it may not be the most recent version.


For more specific help you will need to tell us what distribution you 
are using.


HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] stupid man's manual to sqlite?

[Dennis Cote]

Jakub Ladman wrote:

I am searching for some "stupid man's manual to sqlite".
I can not found any basic's in documentation section of sqlite.org

  

You can try some of the sqlite articles available online:

Linux Journal has a couple of articles at 
http://www.linuxjournal.com/article/7803 and 
http://www.linuxjournal.com/article/6650.


Linux Magazine has an article at http://www.linux-mag.com/id/1484/ (this 
site requires free registration to access all its content).


Or any of the articles located by this google search 
http://www.google.com/search?q=sqlite+tutorial

such as http://freshmeat.net/articles/view/1428/

HTH
Dennis Cote

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Precompiled download of sqlite3 (v3.3.13) for Linux

[William F Pearson III]

Which distro and version are you using?

On 3/8/07, Friedman, Eluzor <[EMAIL PROTECTED]> wrote:

Hi,

There is one for windows.  Where is the one for Linux?

Thanks

PS I am a newbie.

Lou Friedman
CA
410-696-9610
[EMAIL PROTECTED]



-
To unsubscribe, send email to [EMAIL PROTECTED]
-





--
William F Pearson III

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Precompiled download of sqlite3 (v3.3.13) for Linux

[G. Roderick Singleton]

On Thu, 2007-03-08 at 12:14 -0500, Friedman, Eluzor wrote:
> Hi,
> 
> There is one for windows.  Where is the one for Linux?
> 
> Thanks
> 
> PS I am a newbie. 
> 

Same page as for the windows versions, http://sqlite.org/download.html
-- 
G. Roderick Singleton <[EMAIL PROTECTED]>
PATH tech



smime.p7s
Description: S/MIME cryptographic signature

Re: [sqlite] Case Insensitive Equality Searches

[drh]

[EMAIL PROTECTED] wrote:
> My first suggestion was using upper around both operands in the equation, but 
> the person who needed it said that didn't work, which actually leads me to 
> another question.  Is any of this stuff (upper, collate nocase, etc) 
> available in 2.x, or are they 3.x innovations?
> 

The collation stuff is all new to 3.x.  I don't recall with
certainty if upper() was available in 2.x or not, but I think 
it was.
--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] stupid man's manual to sqlite?

[G. Roderick Singleton]

On Thu, 2007-03-08 at 17:43 +0100, A.J.Millan wrote:
> 
> > Thank you i will try my possibilities buying from amazon, while i am based
> in
> > the Czech republic.
> > Unfortunately it is little bit expensive for me, to buy it without looking
> > into it before.
> > Jakub
> 
> I would recommend not to waste your's money in that book.
> 
> More of a half is about SQL generalities, an a quarter a copy-paste of the
> on-line manual without more comment.  At the end, the horrific index i've
> ever seen.
> 

I have found SQL in a Nutshell very useful over the past 5 years. You
might try http://safari.oreilly.com/0596004818
-- 
G. Roderick Singleton <[EMAIL PROTECTED]>
PATH tech



smime.p7s
Description: S/MIME cryptographic signature

Re: [sqlite] Re: Case Insensitive Equality Searches

[epankoke]

My first suggestion was using upper around both operands in the equation, but 
the person who needed it said that didn't work, which actually leads me to 
another question.  Is any of this stuff (upper, collate nocase, etc) available 
in 2.x, or are they 3.x innovations?

--
Eric Pankoke
Founder / Lead Developer
Point Of Light Software
http://www.polsoftware.com/

 -- Original message --
From: "Igor Tandetnik" <[EMAIL PROTECTED]>
> [EMAIL PROTECTED] wrote:
> > If I want to do "SELECT * FROM table WHERE field = value", how can I
> > do this matching text only and not case?
> 
> If you always need case insensitive comparison, the easiest way is to 
> assign NOCASE collation to the field when creating the table:
> 
> create table mytable (field char collate NOCASE, ...)
> 
> If you need only this comparison to be case insensitive, you can do
> 
> SELECT * FROM table WHERE upper(field) = upper(value);
> 
> Reportedly, the latest CVS code for SQLite also supports
> 
> SELECT * FROM table WHERE field=value collate NOCASE;
> 
> This will probably make it into the next release.
> 
> Note that NOCASE collation in SQLite only recognizes letters A through Z 
> as being equal to a through z. It doesn't support any other characters, 
> e.g. accented Latin characters or characters from other scripts. If you 
> need any kind of linguistically correct collation, you need to provide 
> one yourself (luckily SQLite supports custom collations).
> 
> Igor Tandetnik 
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Case Insensitive Equality Searches

[drh]

"P Kishor" <[EMAIL PROTECTED]> wrote:
> On 3/8/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> > [EMAIL PROTECTED] wrote:
> > > If I want to do "SELECT * FROM table WHERE field = value",
> > > how can I do this matching text only and not case?
> > >
> >
> >SELECT * FROM table
> > WHERE field COLLATE nocase = value;
> 
> 
> ahhh, I didn't know this could be done at SELECT time. I thought this
> had to be defined at CREATE TABLE time. Thanks for the clarification.
> 
> Once again, however, it perplexes me how one could have found this out
> from the existing docs. The more that can be found from existing docs,
> the less folks will have to rely on the mailing list for help
> (hopefully).
> 

It's a new feature, introduced with 3.3.13 and still experimental,
so undocumented.  :-)
--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] stupid man's manual to sqlite?

[A.J.Millan]

> Thank you i will try my possibilities buying from amazon, while i am based
in
> the Czech republic.
> Unfortunately it is little bit expensive for me, to buy it without looking
> into it before.
> Jakub

I would recommend not to waste your's money in that book.

More of a half is about SQL generalities, an a quarter a copy-paste of the
on-line manual without more comment.  At the end, the horrific index i've
ever seen.

A.J.Millan
ZATOR Systems.



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Fastest way of UPDATE'ing

[jose isaias cabrera]

[EMAIL PROTECTED] wrote,



"jose isaias cabrera" <[EMAIL PROTECTED]> wrote:

Greetings!

I have this scenario...

6 users with local dbs
1 network db to backup all those DBs and to share info.

Every local DB unique record id based on the network DB.  So, before each
user creates a new record, the tool asks the network ID for the next
available sequential record and creates a new record on the local DB 
using

that unique id from the network DB.


I think I would want to avoid going to the remote DB
every time an insert was needed.  I'd work around that
in one of two ways:

 (1) Each client can check out a range of unique IDs
 in advance and use new IDs from its assigned
 range.

 (2) Clients select unique IDs at random. If the space
 of random IDs is large enough, and you have
 a good source of randomness, the probability
 of collisions can be made vanishingly small.


Didn't think about this.  H...


The question is, what is the fastest way to UPDATE the main DB?  Right 
now,

what I am doing is a for each record and UPDATE all the values where
id=.  Is there a faster way?



If the id is declared to be UNIQUE in the master database,
then you can just do:

  INSERT OR IGNORE INTO masterdb SELECT * FROM localdb;

That statement will copy into "masterdb" all records in
"localdb" that are not already in "masterdb".


Yep, this is the way to go, for now.  Though, I am going to think about the 
statement above.  It could be done.


thanks,

josé


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Fastest way of UPDATE'ing

[jose isaias cabrera]

Thanks.

- Original Message - 
From: "Dennis Cote" <[EMAIL PROTECTED]>

To: 
Sent: Thursday, March 08, 2007 10:24 AM
Subject: Re: [sqlite] Fastest way of UPDATE'ing



jose isaias cabrera wrote:


I have this scenario...

6 users with local dbs
1 network db to backup all those DBs and to share info.

Every local DB unique record id based on the network DB.  So, before each 
user creates a new record, the tool asks the network ID for the next 
available sequential record and creates a new record on the local DB 
using that unique id from the network DB.


The question is, what is the fastest way to UPDATE the main DB?  Right 
now, what I am doing is a for each record and UPDATE all the values where 
id=.  Is there a faster way?




Jose,

If I understand your application, I think you should be able to merge the 
local databases into the network database using either an insert or 
replace (if you want to replace existing records in the network DB with 
potentially modified records from the local DB), or an insert or ignore 
(if you want to leave the records that already exist in the network DB 
alone). Since each record has an ID that is unique across the entire 
network you can insert the local records into the network DB with worrying 
about ID conflicts, there is no need to update records in the network DB. 
If you are only storing new records in the local databases then you can 
just do an insert into the network DB.


HTH
Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Fastest way of UPDATE'ing

[jose isaias cabrera]

Thanks.

- Original Message - 
From: "Daniel Önnerby" <[EMAIL PROTECTED]>

To: 
Sent: Thursday, March 08, 2007 10:22 AM
Subject: Re: [sqlite] Fastest way of UPDATE'ing


If you attach both databases I guess you can use the replace-command since 
the uniqueID exists in both databases.


REPLACE INTO server.table SELECT * FROM client.table;

It would also be helpful to keep a local track of what ID has not been 
updated on the server yet, so you do not need to update all records:


REPLACE INTO server.table SELECT * FROM client.table WHERE id>[update from 
ID];



regards
Daniel

jose isaias cabrera wrote:


Greetings!

I have this scenario...

6 users with local dbs
1 network db to backup all those DBs and to share info.

Every local DB unique record id based on the network DB.  So, before each 
user creates a new record, the tool asks the network ID for the next 
available sequential record and creates a new record on the local DB 
using that unique id from the network DB.


The question is, what is the fastest way to UPDATE the main DB?  Right 
now, what I am doing is a for each record and UPDATE all the values where 
id=.  Is there a faster way?


Thanks,

josé




-

To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-




-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] Case Insensitive Equality Searches

[Samuel R. Neff]

Actually converting everything to upper-case (or lower-case) does not
achieve case-insensitive comparison in all locales.

Here's a thread on the subject with examples in C#, but assuming consistent
i18n support the issue would affect all programming environments.

http://groups.google.com/group/microsoft.public.dotnet.framework.aspnet/brow
se_thread/thread/23b40b19185821d9/9a60db731adb2159
 
Sam


---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: P Kishor [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 08, 2007 11:06 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Case Insensitive Equality Searches

On 3/8/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
> If I want to do "SELECT * FROM table WHERE field = value", how can I do
this matching text only and not case?
>


you can do it in your application using the language of your choice by
upcasing or downcasing your field and value. Here is a Perl example

SELECT * FROM table WHERE upper(field) = uc($value)


or, you can define your column (done at CREATE TABLE time) with a
COLLATE NOCASE option. See


-- 
Puneet Kishor http://punkish.eidesis.org/


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Case Insensitive Equality Searches

[epankoke]

Thank you.  I'm sure this has been addressed before, but I couldn't remember 
where the list archive was.  You'd think as long as I've been on this list I'd 
have known the answer to this anyway :)

--
Eric Pankoke
Founder / Lead Developer
Point Of Light Software
http://www.polsoftware.com/

 -- Original message --
From: [EMAIL PROTECTED]
> [EMAIL PROTECTED] wrote:
> > If I want to do "SELECT * FROM table WHERE field = value",
> > how can I do this matching text only and not case?
> > 
> 
>SELECT * FROM table
> WHERE field COLLATE nocase = value;
> 
> --
> D. Richard Hipp  <[EMAIL PROTECTED]>
> 
> 
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
> 


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Case Insensitive Equality Searches

[P Kishor]

On 3/8/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

[EMAIL PROTECTED] wrote:
> If I want to do "SELECT * FROM table WHERE field = value",
> how can I do this matching text only and not case?
>

   SELECT * FROM table
WHERE field COLLATE nocase = value;



ahhh, I didn't know this could be done at SELECT time. I thought this
had to be defined at CREATE TABLE time. Thanks for the clarification.

Once again, however, it perplexes me how one could have found this out
from the existing docs. The more that can be found from existing docs,
the less folks will have to rely on the mailing list for help
(hopefully).

--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] stupid man's manual to sqlite?

[Martin Jenkins]

Jakub Ladman wrote:
Thank you i will try my possibilities buying from amazon, while i am based in 
the Czech republic.
Unfortunately it is little bit expensive for me, to buy it without looking 
into it before.


I don't know if would help, but Apress sell an e-book (PDF) version for 
about half the cost of the printed version.


http://www.apress.com/ecommerce/cart.html?act=add=10130

The printed contains details of how to get a PDF version for $10US, but 
that's a bit Catch 22 here. ;)


Martin

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Re: Case Insensitive Equality Searches

[Igor Tandetnik]

[EMAIL PROTECTED] wrote:

If I want to do "SELECT * FROM table WHERE field = value", how can I
do this matching text only and not case?


If you always need case insensitive comparison, the easiest way is to 
assign NOCASE collation to the field when creating the table:


create table mytable (field char collate NOCASE, ...)

If you need only this comparison to be case insensitive, you can do

SELECT * FROM table WHERE upper(field) = upper(value);

Reportedly, the latest CVS code for SQLite also supports

SELECT * FROM table WHERE field=value collate NOCASE;

This will probably make it into the next release.

Note that NOCASE collation in SQLite only recognizes letters A through Z 
as being equal to a through z. It doesn't support any other characters, 
e.g. accented Latin characters or characters from other scripts. If you 
need any kind of linguistically correct collation, you need to provide 
one yourself (luckily SQLite supports custom collations).


Igor Tandetnik 



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Case Insensitive Equality Searches

[P Kishor]

On 3/8/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:

If I want to do "SELECT * FROM table WHERE field = value", how can I do this 
matching text only and not case?




you can do it in your application using the language of your choice by
upcasing or downcasing your field and value. Here is a Perl example

SELECT * FROM table WHERE upper(field) = uc($value)


or, you can define your column (done at CREATE TABLE time) with a
COLLATE NOCASE option. See


--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] notice: embedded system and locked database

[Martin Jenkins]

Jakub Ladman wrote:
Now i have discovered, that this appears only if the database file is stored 
on the nfs filesystem (posibly too slow, or something similar)


There have been many threads on this list about NFS locking being 
broken, but few reports of it actually happening. Could you post a few 
details about the OS versions and file systems in your environment for 
future reference?



I will try to togle some nfs parameters as sync/async and so...


Judging by earlier threads this may not help. :( The basic problem is 
that some NFS implementations have faulty locking, so tweaking might 
just mask the problem until later.


Martin

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] In-Memory Dump/Serialization ?

[Guy Rabiller]

Hi again,

as I've not seen any response to my previous message, I suspect I've 
been unclear in explaining what I'm after.


Or perhaps there is another list ( sqlite-devel or something ? ) more 
suited to discuss modifications to the library, or could this be the 
kind of questions needing commercial support ?


Not sure wich one, please enlight me.

Thanks in advance.

--
guy rabiller | President, CEO & CTO | m3factory





Guy Rabiller a écrit :


Hi,

after reading the wiki entry "In Memory Database" ( 
http://www.sqlite.org/cvstrac/wiki/wiki?p=InMemoryDatabase ) I 
understand now that we can load a database file, work on it 
'in-memory' then save the result back to a database file ( 
ATTACH/DETACH DATABASE ).


For my current project, I exactly need this feature except that in 
place of loading/saving the result from/to a file, I'd need to 
load/save the result as a binary blob, like a serialization of the 
database itself if you like. So I could reconstruct the database from 
this chunk of datas, then use it in-memory, then 'dump' the database 
as a data chunck wich can be saved indirectly.


The reason for doing this is that the project is a plugin in a context 
of an application host, wich support loading and saving of 'user 
datas' that the application host loads/saves on its own. The plugin 
just need to put/get the datas from the application host api.


Of course, I could dump the content of the database and reconstruct 
the database from SQL queries.


I was thinking about something more straightforward, like a binary 
'snapshot' of the database that SQLite could use/dump directly.


Is this allready possible somehow ?

If not, what would be the best approach to handle such case ?

Thanks a lot in advance for any help.





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Case Insensitive Equality Searches

[drh]

[EMAIL PROTECTED] wrote:
> If I want to do "SELECT * FROM table WHERE field = value",
> how can I do this matching text only and not case?
> 

   SELECT * FROM table
WHERE field COLLATE nocase = value;

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Fastest way of UPDATE'ing

[drh]

"jose isaias cabrera" <[EMAIL PROTECTED]> wrote:
> Greetings!
> 
> I have this scenario...
> 
> 6 users with local dbs
> 1 network db to backup all those DBs and to share info.
> 
> Every local DB unique record id based on the network DB.  So, before each 
> user creates a new record, the tool asks the network ID for the next 
> available sequential record and creates a new record on the local DB using 
> that unique id from the network DB.

I think I would want to avoid going to the remote DB
every time an insert was needed.  I'd work around that
in one of two ways:

  (1) Each client can check out a range of unique IDs
  in advance and use new IDs from its assigned
  range.

  (2) Clients select unique IDs at random. If the space
  of random IDs is large enough, and you have
  a good source of randomness, the probability
  of collisions can be made vanishingly small.

> 
> The question is, what is the fastest way to UPDATE the main DB?  Right now, 
> what I am doing is a for each record and UPDATE all the values where 
> id=.  Is there a faster way?
> 

If the id is declared to be UNIQUE in the master database,
then you can just do:

   INSERT OR IGNORE INTO masterdb SELECT * FROM localdb;

That statement will copy into "masterdb" all records in
"localdb" that are not already in "masterdb".

--
D. Richard Hipp  <[EMAIL PROTECTED]>


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Different ROWID and PRIMARY KEY values

[Luca Moratto]

Hi,

I'm using c++ interface for SQLite 3.3.8.

1) I open a db in memory,
2) attach a file with one table with a INTEGER PRIMARY KEY (ATTACH 
DATABASE 'SQliteFile.s3db' AS 'attachedDb'),
3) copy in memory the tble from attached db table (CREATE TABLE 
'myTable' AS SELECT * FROM 'attachedDb.myTable')
4) exec in memory some INSERT with null value for the key 
(sqlite3_prepare,sqlite3_bind_... and sqlite3_bind_null for key field, 
sqlite3_step)


Now if I Select both rowId and the key field I notice that the rowId has 
a correct value, but the key field has always a value of 0, while all 
other fileds are correct.
But if I "write to file" using a DELETE FROM 'attachedDb.myTable' and 
INSERT INTO 'attachedDb.myTable' SELECT * FROM 'myTable' I can see rowId 
and key field with the same values in the attached database using SQLite 
Administrator.


Why key filed hasn't the same value of rowId just after my Insert?

Thanks in advance
Luke


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] Case Insensitive Equality Searches

[epankoke]

If I want to do "SELECT * FROM table WHERE field = value", how can I do this 
matching text only and not case?

--
Eric Pankoke
Founder / Lead Developer
Point Of Light Software
http://www.polsoftware.com/

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] stupid man's manual to sqlite?

[Jakub Ladman]

So i will try it.

But what to do before the book will be here?

  rc = sqlite3_exec(db, argv[2], callback, 0, );
  if( rc!=SQLITE_OK ){
fprintf(stderr, "SQL error: %s\n", zErrMsg);
sqlite3_free(zErrMsg);
  }


What does the code if argument is 'select * from tbl1'?
I see, that the results are printed on the screen, but how to fill variables 
from the found values for example.

Is there some  well arranged manual on the internet?

Thank you

Jakub


Dne čtvrtek 08 březen 2007 16:08 Anderson, James H (IT) napsal(a):
> You won't be disappointed , it's a terrific book!
>
> -Original Message-
> From: Jakub Ladman [mailto:[EMAIL PROTECTED]
> Sent: Thursday, March 08, 2007 9:51 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] stupid man's manual to sqlite?
>
> Thank you i will try my possibilities buying from amazon, while i am based
> in the Czech republic.
> Unfortunately it is little bit expensive for me, to buy it without looking
> into it before.
> Jakub
>
> Dne čtvrtek 08 březen 2007 15:25 Samuel R. Neff napsal(a):
> > I'd suggest Mike Owens book "The Definitive Guid to SQLite".  We just
> > started working with SQLite and are very impressed by the book.  It's a
> > very easy read, well written, and covers a ton of details of internals on
> > SQLite. There are several chapters with examples on using the C API (as
> > well as other language extensions).
> >
> > http://www.amazon.com/Definitive-Guide-SQLite/dp/1590596730/ref=pd_bbs_sr
> >_1 / 002-0953141-3838416
> >
> > HTH,
> >
> > Sam
> >
> >
> >
> > ---
> > We're Hiring! Seeking a passionate developer to join our team building
> > products. Position is in the Washington D.C. metro area. If interested
> > contact [EMAIL PROTECTED]
> >
> > -Original Message-
> > From: Jakub Ladman [mailto:[EMAIL PROTECTED]
> > Sent: Thursday, March 08, 2007 7:26 AM
> > To: sqlite-users@sqlite.org
> > Subject: [sqlite] stupid man's manual to sqlite?
> >
> > Hi (much smarter) friends.
> > I need to start with sqlite (interfaced via C language) very quickly,
> > unfortunately.
> > I have not any experiences with any SQL, only some books.
> > I am searching for some "stupid man's manual to sqlite".
> > I can not found any basic's in documentation section of sqlite.org
> >
> >
> > -
> >-- -- To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> >-- --
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>-- 
>
> NOTICE: If received in error, please destroy and notify sender. Sender does
> not intend to waive confidentiality or privilege. Use of this email is
> prohibited when received in error.
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Fastest way of UPDATE'ing

[Dennis Cote]

jose isaias cabrera wrote:


I have this scenario...

6 users with local dbs
1 network db to backup all those DBs and to share info.

Every local DB unique record id based on the network DB.  So, before 
each user creates a new record, the tool asks the network ID for the 
next available sequential record and creates a new record on the local 
DB using that unique id from the network DB.


The question is, what is the fastest way to UPDATE the main DB?  Right 
now, what I am doing is a for each record and UPDATE all the values 
where id=.  Is there a faster way?




Jose,

If I understand your application, I think you should be able to merge 
the local databases into the network database using either an insert or 
replace (if you want to replace existing records in the network DB with 
potentially modified records from the local DB), or an insert or ignore 
(if you want to leave the records that already exist in the network DB 
alone). Since each record has an ID that is unique across the entire 
network you can insert the local records into the network DB with 
worrying about ID conflicts, there is no need to update records in the 
network DB. If you are only storing new records in the local databases 
then you can just do an insert into the network DB.


HTH
Dennis Cote


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] Fastest way of UPDATE'ing

[Daniel Önnerby]

If you attach both databases I guess you can use the replace-command 
since the uniqueID exists in both databases.


REPLACE INTO server.table SELECT * FROM client.table;

It would also be helpful to keep a local track of what ID has not been 
updated on the server yet, so you do not need to update all records:


REPLACE INTO server.table SELECT * FROM client.table WHERE id>[update 
from ID];



regards
Daniel

jose isaias cabrera wrote:


Greetings!

I have this scenario...

6 users with local dbs
1 network db to backup all those DBs and to share info.

Every local DB unique record id based on the network DB.  So, before 
each user creates a new record, the tool asks the network ID for the 
next available sequential record and creates a new record on the local 
DB using that unique id from the network DB.


The question is, what is the fastest way to UPDATE the main DB?  Right 
now, what I am doing is a for each record and UPDATE all the values 
where id=.  Is there a faster way?


Thanks,

josé




- 


To unsubscribe, send email to [EMAIL PROTECTED]
- 





-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] database is locked error with 3.3.13

[drh]

T <[EMAIL PROTECTED]> wrote:
> 
> I recently installed SQLite 3.3.13, after having used previous  
> versions. I now get an error:
> 
> Error: database is locked
> 
> when I use the sqlite3 command line tool to access a database on a  
> shared volume.
> 
> But opening the same file with an earlier version works fine.
> 
> I'm not sure what version introduced this problem. I suspect it's  
> after 3.3.9. It's definitely after 3.1.3.
> 
> I'm using Mac OS X 10.4.8, with the database file on an AppleShare  
> mounted volume.
> 
> What's the issue here? Is it a known bug? Will it be fixed?
> 

This is not a bug in SQLite - it is a bug in Apple's implementation
(or more precisely their lack of implementation) of POSIX advisory
locks for AppleShare mounted volumes.  The SQLite sources include
an (Apple-supplied) patch to work around the problem. Recompile
with

   -DSQLITE_ENABLE_LOCKING_STYLE=1

We are working toward turning on this patch by default, but we are not
quite there yet.

--
D. Richard Hipp  <[EMAIL PROTECTED]>



-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] notice: embedded system and locked database

[Jakub Ladman]

Few days ago i have had problem with "SQL error: database is locked"
Now i have discovered, that this appears only if the database file is stored 
on the nfs filesystem (posibly too slow, or something similar)

I will try to togle some nfs parameters as sync/async and so...

Jakub

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] stupid man's manual to sqlite?

[Anderson, James H \(IT\)]

You won't be disappointed , it's a terrific book!

-Original Message-
From: Jakub Ladman [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 08, 2007 9:51 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] stupid man's manual to sqlite?

Thank you i will try my possibilities buying from amazon, while i am based in 
the Czech republic.
Unfortunately it is little bit expensive for me, to buy it without looking 
into it before.
Jakub


Dne čtvrtek 08 březen 2007 15:25 Samuel R. Neff napsal(a):
> I'd suggest Mike Owens book "The Definitive Guid to SQLite".  We just
> started working with SQLite and are very impressed by the book.  It's a
> very easy read, well written, and covers a ton of details of internals on
> SQLite. There are several chapters with examples on using the C API (as
> well as other language extensions).
>
> http://www.amazon.com/Definitive-Guide-SQLite/dp/1590596730/ref=pd_bbs_sr_1
>/ 002-0953141-3838416
>
> HTH,
>
> Sam
>
>
>
> ---
> We're Hiring! Seeking a passionate developer to join our team building
> products. Position is in the Washington D.C. metro area. If interested
> contact [EMAIL PROTECTED]
>
> -Original Message-
> From: Jakub Ladman [mailto:[EMAIL PROTECTED]
> Sent: Thursday, March 08, 2007 7:26 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] stupid man's manual to sqlite?
>
> Hi (much smarter) friends.
> I need to start with sqlite (interfaced via C language) very quickly,
> unfortunately.
> I have not any experiences with any SQL, only some books.
> I am searching for some "stupid man's manual to sqlite".
> I can not found any basic's in documentation section of sqlite.org
>
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--

-
To unsubscribe, send email to [EMAIL PROTECTED]
-


NOTICE: If received in error, please destroy and notify sender. Sender does not 
intend to waive confidentiality or privilege. Use of this email is prohibited 
when received in error.

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] stupid man's manual to sqlite?

[William F Pearson III]

I bought the book and I'd recommend it to anyone who was beginning to
learn SQL or the Relational Database model as well. It was a very
helpful refresher to me.

On 3/8/07, Jakub Ladman <[EMAIL PROTECTED]> wrote:

Thank you i will try my possibilities buying from amazon, while i am based in
the Czech republic.
Unfortunately it is little bit expensive for me, to buy it without looking
into it before.
Jakub


Dne čtvrtek 08 březen 2007 15:25 Samuel R. Neff napsal(a):
> I'd suggest Mike Owens book "The Definitive Guid to SQLite".  We just
> started working with SQLite and are very impressed by the book.  It's a
> very easy read, well written, and covers a ton of details of internals on
> SQLite. There are several chapters with examples on using the C API (as
> well as other language extensions).
>
> http://www.amazon.com/Definitive-Guide-SQLite/dp/1590596730/ref=pd_bbs_sr_1
>/ 002-0953141-3838416
>
> HTH,
>
> Sam
>
>
>
> ---
> We're Hiring! Seeking a passionate developer to join our team building
> products. Position is in the Washington D.C. metro area. If interested
> contact [EMAIL PROTECTED]
>
> -Original Message-
> From: Jakub Ladman [mailto:[EMAIL PROTECTED]
> Sent: Thursday, March 08, 2007 7:26 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] stupid man's manual to sqlite?
>
> Hi (much smarter) friends.
> I need to start with sqlite (interfaced via C language) very quickly,
> unfortunately.
> I have not any experiences with any SQL, only some books.
> I am searching for some "stupid man's manual to sqlite".
> I can not found any basic's in documentation section of sqlite.org
>
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--

-
To unsubscribe, send email to [EMAIL PROTECTED]
-





--
William F Pearson III

Re: [sqlite] database is locked error with 3.3.13

[Ken]

I have the following sources which one would you like?
 
 sqlite-2.8.16.tag.gz  sqlite-3.3.8.tar.gz
 sqlite-3.2.2.tar.gz   sqlite-3.3.9.tar.gz
 sqlite-3.2.8.tar.gz 
 sqlite-3.3.10.tar.gz
 sqlite-3.3.12.tar.gz 
 sqlite-3.3.13.tar.gz 
 sqlite-3.3.5.tar.gz   
 sqlite-3.3.7.tar.gz
 
 Ken
 

T <[EMAIL PROTECTED]> wrote: Hi all,

Following up:

> I recently installed SQLite 3.3.13, after having used previous  
> versions. I now get an error:
>
> Error: database is locked
>
> when I use the sqlite3 command line tool to access a database on a  
> shared volume.
>
> But opening the same file with an earlier version works fine.
>
> I'm not sure what version introduced this problem. I suspect it's  
> after 3.3.9. It's definitely after 3.1.3.
>
> I'm using Mac OS X 10.4.8, with the database file on an AppleShare  
> mounted volume.

I tried using sqlite 3.3.10 (which I had on another machine) and  
still have the problem. I looked for 3.3.9 to re-install it, to try  
that version, but couldn't find it on the sqlite.org web site.

Have others experienced a locking error on remote volumes?

Where can I get 3.3.9 and earlier source code?

Thanks,
Tom


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] stupid man's manual to sqlite?

[Jakub Ladman]

Thank you i will try my possibilities buying from amazon, while i am based in 
the Czech republic.
Unfortunately it is little bit expensive for me, to buy it without looking 
into it before.
Jakub


Dne čtvrtek 08 březen 2007 15:25 Samuel R. Neff napsal(a):
> I'd suggest Mike Owens book "The Definitive Guid to SQLite".  We just
> started working with SQLite and are very impressed by the book.  It's a
> very easy read, well written, and covers a ton of details of internals on
> SQLite. There are several chapters with examples on using the C API (as
> well as other language extensions).
>
> http://www.amazon.com/Definitive-Guide-SQLite/dp/1590596730/ref=pd_bbs_sr_1
>/ 002-0953141-3838416
>
> HTH,
>
> Sam
>
>
>
> ---
> We're Hiring! Seeking a passionate developer to join our team building
> products. Position is in the Washington D.C. metro area. If interested
> contact [EMAIL PROTECTED]
>
> -Original Message-
> From: Jakub Ladman [mailto:[EMAIL PROTECTED]
> Sent: Thursday, March 08, 2007 7:26 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] stupid man's manual to sqlite?
>
> Hi (much smarter) friends.
> I need to start with sqlite (interfaced via C language) very quickly,
> unfortunately.
> I have not any experiences with any SQL, only some books.
> I am searching for some "stupid man's manual to sqlite".
> I can not found any basic's in documentation section of sqlite.org
>
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] bug in the cmd shell

[Griggs, Donald]

Regarding:

The .import cmd has a problem when it encounters binary zeros
embedded in data.

I should think this would be considered a bug. 

Hi Jim,

While you can file a ticket for this, I wouldn't be shocked if it others
disagreed.
The command line utility requires ASCII input, and binary zeroes are not
allowed.   (Similarly, skipping just one field separator out of millions
will prevent an import -- one must supply clean data to achieve an
import.)

It should be quick and easy to remove all nulls from your input.  One
option is the GNU utility "gsar"

gsar -s":x00" -r" "  input.txt output.txt



[opinions are my own, not those of my company]


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] crosscompiling dynamic library

[Jakub Ladman]

Now i have complete answer, every object file must be compiled with -fPIC 
parameter, now it seems to be good.
Jakub

Dne čtvrtek 08 březen 2007 15:33 Jakub Ladman napsal(a):
> I have found that it may be problem with something called TEXTREL
> I do not know what is it, but:
>
> [EMAIL PROTECTED] ~/src/shsqlite $ sh4-pc-linux-uclibc-gcc -shared *.o -o
> libsqlite3.so
> /usr/libexec/gcc/sh4-pc-linux-uclibc/ld: warning: creating a DT_TEXTREL in
> object.
> [EMAIL PROTECTED] ~/src/shsqlite $
>
> How do I disable creation of DT_TEXTREL?
>
> Thank You
>
> Jakub
>
> Dne čtvrtek 08 březen 2007 15:03 Jakub Ladman napsal(a):
> > Hi there
> >
> > I have discovered problem while trying to run crosscompiled simple test
> > program for my target embedded system.
> >
> >
> >
> > /mnt/nfs/home/jakub/shsqlite $ ./testsql test.db 'create table tbl1(one
> > varchar(10), two smallint)'
> > Can't modify /usr/lib/libsqlite3.so's text section. Use GCC option -fPIC
> > for shared objects, please.
> > /mnt/nfs/home/jakub/shsqlite $
> >
> > program was compiled as:
> > ~/src/shsqlite/testsql $
> > sh4-pc-linux-uclibc-gcc -lpthread -lsqlite3 -ldl -L../ testsql.c -fPIC -o
> > testsql
> >
> > library was linked as:
> > sh4-pc-linux-uclibc-gcc -fPIC -shared *.o -o libsqlite3.so
> > from directory with .o files from linux makefile
> >
> >
> > What's wrong?
> >
> > I tried both -fPIC enabled and disablen on even library and executable.
> >
> > Thank you for your help
> >
> > Jakub
> >
> > -
> >-- -- To unsubscribe, send email to [EMAIL PROTECTED]
> > -
> >-- --
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] crosscompiling dynamic library

[Jakub Ladman]

I have found that it may be problem with something called TEXTREL
I do not know what is it, but:

[EMAIL PROTECTED] ~/src/shsqlite $ sh4-pc-linux-uclibc-gcc -shared *.o -o 
libsqlite3.so
/usr/libexec/gcc/sh4-pc-linux-uclibc/ld: warning: creating a DT_TEXTREL in 
object.
[EMAIL PROTECTED] ~/src/shsqlite $ 

How do I disable creation of DT_TEXTREL?

Thank You

Jakub


Dne čtvrtek 08 březen 2007 15:03 Jakub Ladman napsal(a):
> Hi there
>
> I have discovered problem while trying to run crosscompiled simple test
> program for my target embedded system.
>
>
>
> /mnt/nfs/home/jakub/shsqlite $ ./testsql test.db 'create table tbl1(one
> varchar(10), two smallint)'
> Can't modify /usr/lib/libsqlite3.so's text section. Use GCC option -fPIC
> for shared objects, please.
> /mnt/nfs/home/jakub/shsqlite $
>
> program was compiled as:
> ~/src/shsqlite/testsql $
> sh4-pc-linux-uclibc-gcc -lpthread -lsqlite3 -ldl -L../ testsql.c -fPIC -o
> testsql
>
> library was linked as:
> sh4-pc-linux-uclibc-gcc -fPIC -shared *.o -o libsqlite3.so
> from directory with .o files from linux makefile
>
>
> What's wrong?
>
> I tried both -fPIC enabled and disablen on even library and executable.
>
> Thank you for your help
>
> Jakub
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] table Attachments already exists at dbdimp.c line 271

[P Kishor]

On 3/8/07, Mariusz Stakowski <[EMAIL PROTECTED]> wrote:

Hello list,

I am totally new to SQLite. I try to use it with RT
(Request Tracker). I tried  to initialize dabase. The first try failed but
it was not SQLite problem.
I have corrected the error and I tried again. This time I have got this
error:

table Attachments already exists(1) at dbdimp.c line 271


Could you possibly tell me how could I reinitialize my database ?
Or it is more serious ?




don't know anything about RT, but the first time you tried it, in
spite of the error, the table "Attachments" got created. The second
time you tried it again, the code is trying to create that table
again. Just comment that bit of code, or just don't run that method.
Look into the RT code and you will find your problem. You are correct
in your assessment that this is not a SQLite problem.

--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
=

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

RE: [sqlite] stupid man's manual to sqlite?

[Samuel R. Neff]

I'd suggest Mike Owens book "The Definitive Guid to SQLite".  We just
started working with SQLite and are very impressed by the book.  It's a very
easy read, well written, and covers a ton of details of internals on SQLite.
There are several chapters with examples on using the C API (as well as
other language extensions).

http://www.amazon.com/Definitive-Guide-SQLite/dp/1590596730/ref=pd_bbs_sr_1/
002-0953141-3838416

HTH,

Sam



---
We're Hiring! Seeking a passionate developer to join our team building
products. Position is in the Washington D.C. metro area. If interested
contact [EMAIL PROTECTED]
 
-Original Message-
From: Jakub Ladman [mailto:[EMAIL PROTECTED] 
Sent: Thursday, March 08, 2007 7:26 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] stupid man's manual to sqlite?

Hi (much smarter) friends.
I need to start with sqlite (interfaced via C language) very quickly, 
unfortunately.
I have not any experiences with any SQL, only some books.
I am searching for some "stupid man's manual to sqlite".
I can not found any basic's in documentation section of sqlite.org


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] crosscompiling dynamic library

[Jakub Ladman]

Hi there

I have discovered problem while trying to run crosscompiled simple test 
program for my target embedded system.



/mnt/nfs/home/jakub/shsqlite $ ./testsql test.db 'create table tbl1(one 
varchar(10), two smallint)'
Can't modify /usr/lib/libsqlite3.so's text section. Use GCC option -fPIC for 
shared objects, please.
/mnt/nfs/home/jakub/shsqlite $

program was compiled as:
~/src/shsqlite/testsql $ 
sh4-pc-linux-uclibc-gcc -lpthread -lsqlite3 -ldl -L../ testsql.c -fPIC -o 
testsql

library was linked as:
sh4-pc-linux-uclibc-gcc -fPIC -shared *.o -o libsqlite3.so
from directory with .o files from linux makefile


What's wrong?

I tried both -fPIC enabled and disablen on even library and executable.

Thank you for your help

Jakub 

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] database is locked error with 3.3.13

[BardzoTajneKonto]

> Where can I get 3.3.9 and earlier source code?

http://www.sqlite.org/sqlite-source-3_3_0.zip
...
http://www.sqlite.org/sqlite-source-3_3_9.zip

But it would be nice to have direct link on the website, or even better to 
have tags for all releases in CVS.

-
Aparat cyfrowy, odtwarzacz mp3 i inne nagrody. Sprawd¼ nowy konkurs na 
TeleInteria!>> http://link.interia.pl/f1a2d


-
To unsubscribe, send email to [EMAIL PROTECTED]
-

[sqlite] table Attachments already exists at dbdimp.c line 271

[Mariusz Stakowski]

Hello list,

I am totally new to SQLite. I try to use it with RT 
(Request Tracker). I tried  to initialize dabase. The first try failed but 
it was not SQLite problem. 
I have corrected the error and I tried again. This time I have got this 
error:

table Attachments already exists(1) at dbdimp.c line 271


Could you possibly tell me how could I reinitialize my database ? 
Or it is more serious ?



I am at DBD-SQLite-1.13

Greetings

Mariusz

Re: [sqlite] stupid man's manual to sqlite?

[Jakub Ladman]

Dne čtvrtek 08 březen 2007 13:34 Pavan napsal(a):
> Hi Jakub,
>
> Check this link
> http://sqlite.org/quickstart.html
> There is a sample program. compile it and run executable in this form
>
> $./a.out test.db 'create table tbl1(one varchar(10), two smallint)'
> $./a.out test.db 'insert into tbl1 values('hello!',10)'
> $./a.out test.db 'select * from tbl1'
>
Thank you, very very very much.
Jakub

> Thanks,
> Pavan.
>
> On 3/8/07, Jakub Ladman <[EMAIL PROTECTED]> wrote:
> > Hi (much smarter) friends.
> > I need to start with sqlite (interfaced via C language) very quickly,
> > unfortunately.
> > I have not any experiences with any SQL, only some books.
> > I am searching for some "stupid man's manual to sqlite".
> > I can not found any basic's in documentation section of sqlite.org
> >
> > For example: I do understand this:
> >
> > $ sqlite3 ex1
> > SQLite version 3.3.10
> > Enter ".help" for instructions
> > sqlite> create table tbl1(one varchar(10), two smallint);
> > sqlite> insert into tbl1 values('hello!',10);
> > sqlite> insert into tbl1 values('goodbye', 20);
> > sqlite> select * from tbl1;
> > hello!|10
> > goodbye|20
> > sqlite>
> >
> >
> > but not this:
> > rc = sqlite3_exec(db, argv[2], callback, 0, );
> >
> > What format of the command line argument should i write?
> >
> > Thank you for any advice.
> >
> > Jakub Ladman
> >
> >
> > -
> > To unsubscribe, send email to [EMAIL PROTECTED]
> >
> > -
> >

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] stupid man's manual to sqlite?

[Jakub Ladman]

Oh, wikipedia says " It also serves as an example for writing applications 
that use the SQLite library." 
Of course, why do i not get it myself?
Jakub

Dne čtvrtek 08 březen 2007 13:26 Jakub Ladman napsal(a):
> Hi (much smarter) friends.
> I need to start with sqlite (interfaced via C language) very quickly,
> unfortunately.
> I have not any experiences with any SQL, only some books.
> I am searching for some "stupid man's manual to sqlite".
> I can not found any basic's in documentation section of sqlite.org
>
> For example: I do understand this:
>
> $ sqlite3 ex1
>  SQLite version 3.3.10
>  Enter ".help" for instructions
>  sqlite> create table tbl1(one varchar(10), two smallint);
>  sqlite> insert into tbl1 values('hello!',10);
>  sqlite> insert into tbl1 values('goodbye', 20);
>  sqlite> select * from tbl1;
>  hello!|10
>  goodbye|20
>  sqlite>
>
>
> but not this:
>  rc = sqlite3_exec(db, argv[2], callback, 0, );
>
> What format of the command line argument should i write?
>
> Thank you for any advice.
>
> Jakub Ladman
>
> ---
>-- To unsubscribe, send email to [EMAIL PROTECTED]
> ---
>--

-
To unsubscribe, send email to [EMAIL PROTECTED]
-

Re: [sqlite] stupid man's manual to sqlite?

[Pavan]

Hi Jakub,

Check this link
http://sqlite.org/quickstart.html
There is a sample program. compile it and run executable in this form

$./a.out test.db 'create table tbl1(one varchar(10), two smallint)'
$./a.out test.db 'insert into tbl1 values('hello!',10)'
$./a.out test.db 'select * from tbl1'

Thanks,
Pavan.





On 3/8/07, Jakub Ladman <[EMAIL PROTECTED]> wrote:


Hi (much smarter) friends.
I need to start with sqlite (interfaced via C language) very quickly,
unfortunately.
I have not any experiences with any SQL, only some books.
I am searching for some "stupid man's manual to sqlite".
I can not found any basic's in documentation section of sqlite.org

For example: I do understand this:

$ sqlite3 ex1
SQLite version 3.3.10
Enter ".help" for instructions
sqlite> create table tbl1(one varchar(10), two smallint);
sqlite> insert into tbl1 values('hello!',10);
sqlite> insert into tbl1 values('goodbye', 20);
sqlite> select * from tbl1;
hello!|10
goodbye|20
sqlite>


but not this:
rc = sqlite3_exec(db, argv[2], callback, 0, );

What format of the command line argument should i write?

Thank you for any advice.

Jakub Ladman


-
To unsubscribe, send email to [EMAIL PROTECTED]

-





--
'
Always finish stronger than you start
*

[sqlite] stupid man's manual to sqlite?

[Jakub Ladman]

Hi (much smarter) friends.
I need to start with sqlite (interfaced via C language) very quickly, 
unfortunately.
I have not any experiences with any SQL, only some books.
I am searching for some "stupid man's manual to sqlite".
I can not found any basic's in documentation section of sqlite.org

For example: I do understand this: 

$ sqlite3 ex1
 SQLite version 3.3.10
 Enter ".help" for instructions
 sqlite> create table tbl1(one varchar(10), two smallint);
 sqlite> insert into tbl1 values('hello!',10);
 sqlite> insert into tbl1 values('goodbye', 20);
 sqlite> select * from tbl1;
 hello!|10
 goodbye|20
 sqlite> 


but not this:
 rc = sqlite3_exec(db, argv[2], callback, 0, );

What format of the command line argument should i write?

Thank you for any advice.

Jakub Ladman

-
To unsubscribe, send email to [EMAIL PROTECTED]
-