Re: [sqlite] Re: Multiple SELECTs (and single SELECT) and TRANSACTION ?

[vidushi tandon]

i Want to Unsubscribe my Account from SQLite


--- Kurt Welgehausen <[EMAIL PROTECTED]> wrote:

> "Igor Tandetnik" <[EMAIL PROTECTED]> wrote:
> 
> > RohitPatel
>

> > wrote:
> > > Scenario 1
> > > If action of some user needs to execute multiple
> SELECT statements
> > > (read-only, no plan to write), it needs to start
> explicit transaction
> > > to get consistent reads across read-only
> multiple statements.
> > >
> > > Q1. Which one is preferable -> BEGIN TRANSACTION
>  or  BEGIN IMMEDIATE
> > > ?
> >
> > BEGIN IMMEDIATE would block other readers. Don't
> use it unless you plan 
> > to write.
> 
> BEGIN IMMEDIATE blocks writers, not readers.  I
> believe it's
> what he wants.
> 
> Regards
> 


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] C precompiler to bytecode

[Joe Wilson]

--- Bill KING <[EMAIL PROTECTED]> wrote:

> Daniel Önnerby wrote:
> > Hi everyone!
> >
> > I'm just a bit curios if it would be possible to make like a C
> > precompiler or a macro of some kind that compiles/interpret the
> > SQL-statements to bytecode just like the sqlite_prepare does but does
> > this when compiling/precompiling your application instead of at
> > runtime. Since most application written in C/C++ use static
> > SQL-statements (unless you are building your SQL-strings on the fly)
> > and then bind the values, I guess there would be several benefits for
> > your compiled application:
> > * Faster: Since the SQL-statement is already interpreted.
> > * Smaller (not that sqlite needs to be smaller): The executable does
> > not need to contain the part of sqlite that interprets the
> > SQL-statements since this was made at compile time.
> >
> > Just a thought :)
> >
> > Best regards
> > Daniel Önnerby
> >
> I second this. I use a lot of different database handles (due to a lot
> of multi-threading), so there's no way I've seen yet to compile an sql
> query that i can distribute to the different handles, so every statement
> gets prepared just before run. Huge performance hit. If we could even
> just pre-compile once per run, and attach to a database handle afterwards...

I recall reading on this mailing list that DRH sells a version of 
SQLite that can be compiled without a SQL parser that works with 
precompiled statements.

> 
> -- 
> Bill King, Software Engineer
> Trolltech, Brisbane Technology Park
> 26 Brandl St, Eight Mile Plains, 
> QLD, Australia, 4113
> Tel + 61 7 3219 9906 (x137)
> Fax + 61 7 3219 9938
> mobile: 0423 532 733
> 
> 


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

Re: [sqlite] [ANN]SQLtNoCase - convert SQLite TEXT columns to TEXT COLLATE NOCASE

[Me]

Probably.
Then again, it's was done.

- Original Message - 


I don't mind receiving occasional announcements on new programs using
SQLite (even if I'll never use them), but don't you think one per day
is just too much?


Regards,
~Nuno Lucas

Re: [sqlite] random access table row

[Jens Miltner]

Am 08.08.2006 um 13:15 schrieb Lijia Jin:


Hi,

I am new to sqlite and need some help for random accessing a table  
row using

Sqlite C API.

The project I am working on supports network users to query the sqlite
database from remote sites. We like to provide an interface so that  
user can
access any row in a result table without going through all the  
prior rows. I
know this can be implemented by the sqlite3_get_table function but  
this
requires too much memory and it's not that easy to handle database  
update. I
like to use sqlite3_step and the only solution I can come up with  
is to use

the offset value:

select string from string where ... limit 1 offset ? ;

and replace this offset value with the row number user supplied. This
solution can work but it requires modifying the SQL query and calling
sqlite3_reset for each get-row request.

Essentially what we needed is a random access iterator because the
sqlite3_step is just a one direction, single step iterator. I did  
search the
mailing list but can't find anything I am looking for. Did I miss  
something

and there is already a simple solution for this?


In any case you'll have to run one query per row since you don't want  
to cache the entire dataset.
Another alternative to using LIMIT 1 OFFSET   would be to have the  
initial query just return the rowID (assuming all your data is from a  
single table), e.g.


SELECT rowID FROM MyData WHERE name LIKE 'john';

Cache the rowIDs and run your SELECT data query for each row, passing  
the rowID:


SELECT name, address FROM MyData WHERE rowID=?

Not much difference to the LIMIT 1 OFFSET ? query, except if you have  
a complex condition to evaluate, accessing rows by ID will be much  
faster than using an offset, which essentially needs to re-evaluate  
the condition on each query.

Re: [sqlite] C precompiler to bytecode

[Bill KING]

Daniel Önnerby wrote:
> Hi everyone!
>
> I'm just a bit curios if it would be possible to make like a C
> precompiler or a macro of some kind that compiles/interpret the
> SQL-statements to bytecode just like the sqlite_prepare does but does
> this when compiling/precompiling your application instead of at
> runtime. Since most application written in C/C++ use static
> SQL-statements (unless you are building your SQL-strings on the fly)
> and then bind the values, I guess there would be several benefits for
> your compiled application:
> * Faster: Since the SQL-statement is already interpreted.
> * Smaller (not that sqlite needs to be smaller): The executable does
> not need to contain the part of sqlite that interprets the
> SQL-statements since this was made at compile time.
>
> Just a thought :)
>
> Best regards
> Daniel Önnerby
>
I second this. I use a lot of different database handles (due to a lot
of multi-threading), so there's no way I've seen yet to compile an sql
query that i can distribute to the different handles, so every statement
gets prepared just before run. Huge performance hit. If we could even
just pre-compile once per run, and attach to a database handle afterwards...

-- 
Bill King, Software Engineer
Trolltech, Brisbane Technology Park
26 Brandl St, Eight Mile Plains, 
QLD, Australia, 4113
Tel + 61 7 3219 9906 (x137)
Fax + 61 7 3219 9938
mobile: 0423 532 733

Re: [sqlite] C precompiler to bytecode

[Nuno Lucas]

On 8/8/06, Daniel Önnerby <[EMAIL PROTECTED]> wrote:

I'm just a bit curios if it would be possible to make like a C
precompiler or a macro of some kind that compiles/interpret the
SQL-statements to bytecode just like the sqlite_prepare does but does
this when compiling/precompiling your application instead of at runtime.
Since most application written in C/C++ use static SQL-statements
(unless you are building your SQL-strings on the fly) and then bind the
values, I guess there would be several benefits for your compiled
application:
* Faster: Since the SQL-statement is already interpreted.
* Smaller (not that sqlite needs to be smaller): The executable does not
need to contain the part of sqlite that interprets the SQL-statements
since this was made at compile time.


Now the cons:

* Hardcoded to a specific SQLite version.
  Even if the *undocumented* API doesn't change on the next version,
  there's a very good chance it will latter.

* Probably not smaller on the easy way..
  If you have two pre-compiled statements, it would need a smart pre-compiler
  to know what can be shared and what can't, implying a level of
complexity that
  maybe doesn't gain nothing.

* You can already do the same (with the same problems).
  By looking at the explain output you can hardcode the same yourself, without
  the need of a pre-compiler that will need to be constantly up-to-date

* If your queries are known at start, you can even forget about SQLite.
  If you know what you need to do (so, don't need the sqlite library
linked, but just
  a subset of it), maybe it would be best to just call functions and
optimize them
  with a specific data structure that fits you better than sqlite.

* Forget about using your program concurrently with others.
  Future sqlite versions may decide to use different low-level
implementations that
  can clash with your hardcoded functions (this is the same as
statically linking
  the sqlite library with your application).


Just my .02 cents...

Regards,
~Nuno Lucas

[sqlite] C precompiler to bytecode

[Daniel Önnerby]

Hi everyone!

I'm just a bit curios if it would be possible to make like a C 
precompiler or a macro of some kind that compiles/interpret the 
SQL-statements to bytecode just like the sqlite_prepare does but does 
this when compiling/precompiling your application instead of at runtime. 
Since most application written in C/C++ use static SQL-statements 
(unless you are building your SQL-strings on the fly) and then bind the 
values, I guess there would be several benefits for your compiled 
application:

* Faster: Since the SQL-statement is already interpreted.
* Smaller (not that sqlite needs to be smaller): The executable does not 
need to contain the part of sqlite that interprets the SQL-statements 
since this was made at compile time.


Just a thought :)

Best regards
Daniel Önnerby

Re: [sqlite] [ANN]SQLtNoCase - convert SQLite TEXT columns to TEXT COLLATE NOCASE

[Nuno Lucas]

I don't mind receiving occasional announcements on new programs using
SQLite (even if I'll never use them), but don't you think one per day
is just too much?


Regards,
~Nuno Lucas


On 8/8/06, Me <[EMAIL PROTECTED]> wrote:

FREE - SQLtNoCase - convert TEXT columns to TEXT COLLATE NOCASE

SQLite handles text columns different, depending on how COLLATE is defined
when the column is created. The default is COLLATE BINARY.

TEXT or TEXT COLLATE BINARY - columns will be treated case sensitive for
ORDER BY and WHERE comparisons.

TEXT COLLATE NOCASE - columns will be handled case insensitive. 'smith' =
'Smith'

For the most part, TEXT COLLATE NOCASE will give the preferred results on
short text columns such as [Name].

While SQL can be used to force case insensitive comparisons - converting a
column to TEXT COLLATE NOCASE is a permanent solution.

Converting a column from TEXT, to TEXT COLLATE NOCASE, isn't easily done
with SQL - that's where SQLtNoCase comes in.

http://www.sqlight.com/sqltnocase/

sd _ at _ sqlight _ dot _ com

[sqlite] [ANN]SQLtNoCase - convert SQLite TEXT columns to TEXT COLLATE NOCASE

[Me]

FREE - SQLtNoCase - convert TEXT columns to TEXT COLLATE NOCASE

SQLite handles text columns different, depending on how COLLATE is defined 
when the column is created. The default is COLLATE BINARY.


TEXT or TEXT COLLATE BINARY - columns will be treated case sensitive for 
ORDER BY and WHERE comparisons.


TEXT COLLATE NOCASE - columns will be handled case insensitive. 'smith' = 
'Smith'


For the most part, TEXT COLLATE NOCASE will give the preferred results on 
short text columns such as [Name].


While SQL can be used to force case insensitive comparisons - converting a 
column to TEXT COLLATE NOCASE is a permanent solution.


Converting a column from TEXT, to TEXT COLLATE NOCASE, isn't easily done 
with SQL - that's where SQLtNoCase comes in.


http://www.sqlight.com/sqltnocase/

sd _ at _ sqlight _ dot _ com

Re: [sqlite] random access table row

[Jay Sprenkle]

On 8/8/06, Lijia Jin <[EMAIL PROTECTED]> wrote:


select string from string where ... limit 1 offset ? ;

and replace this offset value with the row number user supplied. This
solution can work but it requires modifying the SQL query and calling
sqlite3_reset for each get-row request.


This works very well, but your point is also correct.
The code is pretty trivial though:

bool function( int ImageIndex )
   {
  int i;
  char* sql = "SELECT Path FROM Display ORDER BY
SortOrder LIMIT 1 OFFSET";
  char sz[290];
  i = sprintf( sz, "%s %d", sql, ImageIndex );
  //Debug( sz );

  sqlite3_stmt* pStmt;
  if ( sqlite3_prepare( db, sz, i, , NULL ) != SQLITE_OK )
 return false;

  bool Result = false;
  for ( i = 0; i < 8; ++i )
 {
// execute select
int rc = sqlite3_step( pStmt );

// got a result?
if ( rc == SQLITE_ROW )
   {
  const unsigned char* p =
sqlite3_column_text( pStmt, 0 );
  // nulls changed to blank string
  p = p ? p : (const unsigned char*)"";
  ImagePath = (char*) p;
  Result = true;
  break;
   }

// nothing returned
if ( rc == SQLITE_DONE )
   {
  ImageIndex = 0;
  break;
   }

// retry errors
 }

  // clean up when finished
  sqlite3_finalize( pStmt );

  return Result;
   }




Essentially what we needed is a random access iterator because the
sqlite3_step is just a one direction, single step iterator. I did search the
mailing list but can't find anything I am looking for. Did I miss something
and there is already a simple solution for this?


SQL is designed to operate on sets. That's what it does most efficiently.

It's much better to write
  update mytable set flag = 0 where index < 1000;
than to write
 for ( i = 0; i < 1000; i++ )
update mytable set flag = 0 where index = i;

A random iterator is useful for some problems, but keep in mind the
right way to use it.

Re: [sqlite] Insert from select statement - Weird behavior

[Sandeep Suresh]

Thanks for the reply Richard... I"ll try this !

-Sandeep

[EMAIL PROTECTED] wrote:

Sandeep Suresh <[EMAIL PROTECTED]> wrote:
  

Hi all,

The problem is for one of the columns that have values ( 8209454, 
1254234, 1245663 ) in db_old, the values in the new database are changed 
to ( 8209454.001, 1254234.001 , 1245663.001 ).  This column 
is not defined as 'primary key' or integer.


I'm using version 2.8.14 and the weird thing is there is no problem when 
I use LiteQuery which uses 2.8.12. That means, there is no problem with 
my query.  I also checked with version 2.8.17 and the problem still 
exists. And yeah, at this time I'm not in a position to switch to 
version 3.


Is this a known isssue with sqlite version 2  ? Does anyone know what's 
happening ? 




This is not an issue known to me.  I do not know what is happening.

Try using round() on the values.
--
D. Richard Hipp   <[EMAIL PROTECTED]>


  

[sqlite] random access table row

[Lijia Jin]

Hi,

I am new to sqlite and need some help for random accessing a table row using
Sqlite C API.

The project I am working on supports network users to query the sqlite
database from remote sites. We like to provide an interface so that user can
access any row in a result table without going through all the prior rows. I
know this can be implemented by the sqlite3_get_table function but this
requires too much memory and it's not that easy to handle database update. I
like to use sqlite3_step and the only solution I can come up with is to use
the offset value:

select string from string where ... limit 1 offset ? ;

and replace this offset value with the row number user supplied. This
solution can work but it requires modifying the SQL query and calling
sqlite3_reset for each get-row request.

Essentially what we needed is a random access iterator because the
sqlite3_step is just a one direction, single step iterator. I did search the
mailing list but can't find anything I am looking for. Did I miss something
and there is already a simple solution for this?

Thanks for reading this.

Lijia Jin

[sqlite] Re: Re: Multiple SELECTs (and single SELECT) and TRANSACTION ?

[Igor Tandetnik]

RohitPatel 
wrote:

As I understand, it means after issuing BEGIN IMMIDIATE for read-only
SELECT(s), no other process can do BEGIN IMMIDIATE even if its for
read-only purpose. Even new reader will be blocked if it tries to do
BEGIN IMMEDIATE.


Right. Readers aren't supposed to do BEGIN IMMEDIATE - it's pointless 
and harmful. Writers may, but don't really have to either.



If this is correct, for multiple SELECT statements (read-only, no
plan to write),

Which one is preferable -> BEGIN TRANSACTION  or  BEGIN IMMEDIATE ?


BEGIN TRANSACTION

Igor Tandetnik 

Re: [sqlite] Re: Multiple SELECTs (and single SELECT) and TRANSACTION ?

[RohitPatel9999]

Thanks for the answer and clarification.

> BEGIN IMMEDIATE blocks writers, not readers.

I think, BEGIN IMMEDIATE surely blocks writers. 
And also blocks new reader(s) if any new reader tries to do BEGIN IMMEDIATE. 

Is this correct ?


Ref: Quote from SQLite Document
(http://www.sqlite.org/lang_transaction.html)
"After a BEGIN IMMEDIATE, you are guaranteed that no other thread or process
will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN
EXCLUSIVE."


Rohit
-- 
View this message in context: 
http://www.nabble.com/Multiple-SELECTs-%28and-single-SELECT%29-and-TRANSACTION---tf2072083.html#a5713686
Sent from the SQLite forum at Nabble.com.

Re: [sqlite] Re: Multiple SELECTs (and single SELECT) and TRANSACTION ?

[RohitPatel9999]

Thanks a lot for the answers. 

Still I have a doubt (and a question).

Quote from SQLite Document (http://www.sqlite.org/lang_transaction.html)

"After a BEGIN IMMEDIATE, you are guaranteed that no other thread or process
will be able to write to the database or do a BEGIN IMMEDIATE or BEGIN
EXCLUSIVE."

As I understand, it means after issuing BEGIN IMMIDIATE for read-only
SELECT(s), no other process can do BEGIN IMMIDIATE even if its for read-only
purpose. Even new reader will be blocked if it tries to do BEGIN IMMEDIATE. 

So if I use BEGIN IMMEDIATE for multiple SELECTs, no other reader
thread/process can do BEGIN IMMEDIATE even if it has read-only SELECTs

If this is correct, for multiple SELECT statements (read-only, no plan to
write),

Which one is preferable -> BEGIN TRANSACTION  or  BEGIN IMMEDIATE ? 


Rohit
-- 
View this message in context: 
http://www.nabble.com/Multiple-SELECTs-%28and-single-SELECT%29-and-TRANSACTION---tf2072083.html#a5713541
Sent from the SQLite forum at Nabble.com.

Re: [sqlite] Insert from select statement - Weird behavior

[drh]

Sandeep Suresh <[EMAIL PROTECTED]> wrote:
> Hi all,
> 
> The problem is for one of the columns that have values ( 8209454, 
> 1254234, 1245663 ) in db_old, the values in the new database are changed 
> to ( 8209454.001, 1254234.001 , 1245663.001 ).  This column 
> is not defined as 'primary key' or integer.
> 
> I'm using version 2.8.14 and the weird thing is there is no problem when 
> I use LiteQuery which uses 2.8.12. That means, there is no problem with 
> my query.  I also checked with version 2.8.17 and the problem still 
> exists. And yeah, at this time I'm not in a position to switch to 
> version 3.
> 
> Is this a known isssue with sqlite version 2  ? Does anyone know what's 
> happening ? 
> 

This is not an issue known to me.  I do not know what is happening.

Try using round() on the values.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

[sqlite] Insert from select statement - Weird behavior

[Sandeep Suresh]

Hi all,

 To give a brief context :  I'm trying to perform an upgrade from 
db_old to db_new. db_old and db_new have almost the same tables with a 
few modified columns.


Steps to reproduce the issue :

1. Opened database file db_new
2. Attached database file db_old as 'db1'
3. Perform insert into table_a in db_new using a select statement as 
follows :


  " Insert into table_a values ( column_a, column_b,. )
select distinct ( db1.column_a, column_b)
from db1.table_a as T1, table_b as T2
where T1.id = T2.u_id; "

ps : Please ignore any typos in this query. I just wrote it on the fly.

The problem is for one of the columns that have values ( 8209454, 
1254234, 1245663 ) in db_old, the values in the new database are changed 
to ( 8209454.001, 1254234.001 , 1245663.001 ).  This column 
is not defined as 'primary key' or integer.


I'm using version 2.8.14 and the weird thing is there is no problem when 
I use LiteQuery which uses 2.8.12. That means, there is no problem with 
my query.  I also checked with version 2.8.17 and the problem still 
exists. And yeah, at this time I'm not in a position to switch to 
version 3.


Is this a known isssue with sqlite version 2  ? Does anyone know what's 
happening ? 


Any help would be greatly appreciated.

Thanks,
Sandeep.

[sqlite] Re: Re: Multiple SELECTs (and single SELECT) and TRANSACTION ?

[Igor Tandetnik]

Kurt Welgehausen <[EMAIL PROTECTED]> wrote:

"Igor Tandetnik" <[EMAIL PROTECTED]> wrote:


RohitPatel

wrote:

Scenario 1
If action of some user needs to execute multiple SELECT statements
(read-only, no plan to write), it needs to start explicit
transaction to get consistent reads across read-only multiple
statements.

Q1. Which one is preferable -> BEGIN TRANSACTION  or  BEGIN
IMMEDIATE ?


BEGIN IMMEDIATE would block other readers. Don't use it unless you
plan
to write.


BEGIN IMMEDIATE blocks writers, not readers.  I believe it's
what he wants.


You are correct, BEGIN IMMEDIATE does not block readers. Still, it 
acquires a RESERVED lock which is overkill for a read-only transaction. 
A SELECT statement acquires a SHARED lock which block writers anyway.


Normally, a writer acquires RESERVED lock, then writes modifications 
into the memory cache. Only when a transaction is committed, or when the 
memory cache becomes full and the data needs to be spilled to disk, does 
the writer need to acquire EXCLUSIVE lock before actually writing to a 
physical file.


By having a reader transaction grab RESERVED lock, you would prevent the 
writer from even starting its work with the memory cache. This could 
hurt performance, for no apparent reason.


Igor Tandetnik 

[sqlite] Re: correlated subqueries

[Igor Tandetnik]

Greg Wilson <[EMAIL PROTECTED]> wrote:


-- Try to update the final table in place
update Result set AffilId = (
 select Places.Ident from Places, People, Result
 where (People.Ident = Result.Ident)
   and (People.Affil = Places.Name)
);


Yours is not a correlated subquery: it uses its own reference to Result 
table, rather than correlate with the Result's record you are trying to 
update. Thus, the subselect produces the same resultset over and over, 
and you are just picking the top row from this resultset.


Just remove Result from the FROM clause of the nested select.

Igor Tandetnik 

[sqlite] correlated subqueries

[Greg Wilson]

Hi,

I'm refactoring a small database to remove some redundancy in the original
design.  As part of this, I'm trying to update the values in a table using
a correlated subquery, but can't get it to work with SQLite.  Here's what
I'm doing:

-- Create the 'People' table, and show its contents
create table People(Ident integer not null, Surname text not null, Affil text 
not null);
insert into People values(123, "Newton", "Cambridge");
insert into People values(456, "Darwin", "London");
insert into People values(789, "Turing", "Cambridge");
select distinct 'People' from People;
select * from People;

People
123|Newton|Cambridge
456|Darwin|London
789|Turing|Cambridge

-- Create the 'Places' table and show its contents
create table Places(Ident integer not null, Name text not null);
insert into Places values(0, "Cambridge");
insert into Places values(1, "London");
select distinct '' from Places;
select distinct 'Places' from Places;
select * from Places;

Places
0|Cambridge
1|London

-- Create the table that will hold the refactored data
create table Result(Ident integer not null, Surname text not null, AffilId 
integer not null);
insert into Result select Ident, Surname, 999 from People;
select distinct '' from Result;
select distinct 'Initial Result' from Result;
select * from Result;

Initial Result
123|Newton|999
456|Darwin|999
789|Turing|999

-- Test the intended subquery
select distinct '' from Result;
select distinct 'Nested Query' from Result;
select People.Surname, Places.Ident from Places, People, Result
where (People.Ident = Result.Ident)
  and (People.Affil = Places.Name);

Nested Query
Newton|0
Turing|0
Darwin|1

-- Try to update the final table in place
update Result set AffilId = (
  select Places.Ident from Places, People, Result
  where (People.Ident = Result.Ident)
and (People.Affil = Places.Name)
);
select distinct '' from Result;
select distinct 'Final Result' from Result;
select * from Result;

Final Result
123|Newton|0
456|Darwin|0
789|Turing|0

Whoops --- 'Darwin' should have a location ID of '1', not '0'.  Can anyone
tell me what I've done wrong?

Thanks,
Greg

p.s. I'm not a regular reader of this list, so I'd be grateful if you
could reply directly to [EMAIL PROTECTED]

Re: [sqlite] Re: Multiple SELECTs (and single SELECT) and TRANSACTION ?

[Kurt Welgehausen]

"Igor Tandetnik" <[EMAIL PROTECTED]> wrote:

> RohitPatel 
> wrote:
> > Scenario 1
> > If action of some user needs to execute multiple SELECT statements
> > (read-only, no plan to write), it needs to start explicit transaction
> > to get consistent reads across read-only multiple statements.
> >
> > Q1. Which one is preferable -> BEGIN TRANSACTION  or  BEGIN IMMEDIATE
> > ?
>
> BEGIN IMMEDIATE would block other readers. Don't use it unless you plan 
> to write.

BEGIN IMMEDIATE blocks writers, not readers.  I believe it's
what he wants.

Regards

Re: [sqlite] unsupported file format

[Craig Morrison]

Py Olivier wrote:

Hello,

After compiling I'm testing the C example program provided in the quick start 
page, just to get into the C/C++ interface for SQLite.
./exprog testdb 'select * from tbl1' 
The database file opens normaly, but I get an error coming from the sqlite3_exec method : SQL error: unsupported file format.

As if there was a problem with the table names or query.
The same SQL query on the same database works fine with the command-line 
sqlite3 program.

What am I misunderstanding ?


Not trying to be a jerk, but please search the archives for the past 
week or so. This subject has been beat to death..


--
Craig Morrison
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
http://pse.2cah.com
  Controlling pseudoephedrine purchases.

http://www.mtsprofessional.com/
  A Win32 email server that works for You.

[sqlite] unsupported file format

[Py Olivier]

Hello,

After compiling I'm testing the C example program provided in the quick start 
page, just to get into the C/C++ interface for SQLite.
./exprog testdb 'select * from tbl1' 
The database file opens normaly, but I get an error coming from the 
sqlite3_exec method : SQL error: unsupported file format.
As if there was a problem with the table names or query.
The same SQL query on the same database works fine with the command-line 
sqlite3 program.

What am I misunderstanding ?

Re: [sqlite] Replace of substring in sqlite-table - how can I do this?

[Jay Sprenkle]

On 8/8/06, Olaf Beckman Lapré <[EMAIL PROTECTED]> wrote:

How would this compare to the performance of simply (progammatically) doing
an UPDATE and changing the values in a callback hook. If this is impossbile
than even a SELECT with a callback where one does an UPDATE  for each
returned row would be possible.


If this was a task that needed to be done many times I would create a program
to do it. If it is a one time fix the manual solution suggested would
be quicker.
Sed/ed/awk/vi/elm work very well if you know how to use them.

You might also try to write a user defined function that edits your
text as desired.
Then you can simply use one update statement:

UPDATE myTable SET MyField = MyFunction( MyField );

Re: [sqlite] Replace of substring in sqlite-table - how can I do this?

[Olaf Beckman Lapré]

How would this compare to the performance of simply (progammatically) doing
an UPDATE and changing the values in a callback hook. If this is impossbile
than even a SELECT with a callback where one does an UPDATE  for each
returned row would be possible.

Olaf

- Original Message - 
From: "Jay Sprenkle" <[EMAIL PROTECTED]>
To: 
Sent: Tuesday, August 08, 2006 3:35 PM
Subject: Re: [sqlite] Replace of substring in sqlite-table - how can I do
this?


> On 8/8/06, wqual <[EMAIL PROTECTED]> wrote:
> > Hi Donald,
> > thanks for this valuable comment. Regarding the regexp-function, I am
not
> > really sure whether I understood it entirely or not: is it correct that
I can
> > use the regexp *only* in external programs (e.g. a perl programme), but
not
> > on the sqlite command line? There is no possibility to activate regexp
also
> > for the sqlite-command line? Sorry for asking these questions
>
> I think he was suggesting this:
>
> 1. sqlite command line exports from the database to a file.
> 2. edit the file using perl, vi, elm, sed, ed, awk, etc.
> 3. reload the file to the database
>
>
> > I am using sqlite on the command-line and via sqlitebrowser  - and I am
no
> > programmer (hm.).
> > Unfortunately, the substr-function won't do the job in my case, as the
> > position of substrings may vary within my tables.
>
> regular expressions can probably do this, though it might take someone
> very familiar with them to make it work. It's fairly simple to edit the
> Nth substring in a string.
>

RE: [sqlite] Recovery tool ?

[zze-ContentLab MARTINEAU Y ext RD-SIRP-REN]

> What OS are you using?

Linux 2.6.15-23-server #1 SMP Tue May 23 15:10:35 UTC 2006 i686
GNU/Linux

It's an ubuntu 6.06 server distribution.

> What kind of mass storage?  (Disk or flash?)

It's just the disk of the computer on which I installed sqlite, I don't
use any mass storage device. The potential target is a flash memory on a
set top box... But it's ok with the dump solution for me, don't worry.

Merci ;-)

yohann

Re: [sqlite] Replace of substring in sqlite-table - how can I do this?

[Jay Sprenkle]

On 8/8/06, wqual <[EMAIL PROTECTED]> wrote:

Hi Donald,
thanks for this valuable comment. Regarding the regexp-function, I am not
really sure whether I understood it entirely or not: is it correct that I can
use the regexp *only* in external programs (e.g. a perl programme), but not
on the sqlite command line? There is no possibility to activate regexp also
for the sqlite-command line? Sorry for asking these questions


I think he was suggesting this:

1. sqlite command line exports from the database to a file.
2. edit the file using perl, vi, elm, sed, ed, awk, etc.
3. reload the file to the database



I am using sqlite on the command-line and via sqlitebrowser  - and I am no
programmer (hm.).
Unfortunately, the substr-function won't do the job in my case, as the
position of substrings may vary within my tables.


regular expressions can probably do this, though it might take someone
very familiar with them to make it work. It's fairly simple to edit the
Nth substring in a string.

RE: [sqlite] Recovery tool ?

[zze-ContentLab MARTINEAU Y ext RD-SIRP-REN]

I have identified the only table which is corrupted.

>  2) See if the "vacuum" command will run, and if all tables remain
afterwards.
>   http://www.sqlite.org/lang_vacuum.html

It does not work, I have the same message:
SQL error: database disk image is malformed

>  3) Try a ".dump" command on the entire database.

I have made a dump of the entire database but when I read the file
generated, I only have two tables created. But I consider this is OK
because I can dump other tables. I don't know if have lost many entries
in my database, but all data is updated (or re-created if unexisting)
permanently, so it's OK for me.

Thanks again for your help.

Re: [sqlite] Recovery tool ?

[drh]

"zze-ContentLab MARTINEAU Y ext RD-SIRP-REN" <[EMAIL PROTECTED]> wrote:
> Sorry, I meant a power failure, or a power loss if you prefer.

No worries.  I got to learn a new French word!

> 
> I have generated a new data file, this is not a problem in a development =
> environment, but what should I do in a production environment ?
> 

SQLite is suppose to survive an alimentation interruption.  In
fact, the standard regression test suite does extensive testing
to verify that an alimentation interruption will not harm the
database.

But this feature depends to some extent on your operating system.
If your operating system does not properly flush information
to the disk surface when requested, or if it lies about it,
then bad things can happen.  What OS are you using?  What
kind of mass storage?  (Disk or flash?)
--
D. Richard Hipp   <[EMAIL PROTECTED]>

RE: [sqlite] Recovery tool ?

[zze-ContentLab MARTINEAU Y ext RD-SIRP-REN]

Ok, I'll try those steps and I keep you informed.


-Message d'origine-
De : Griggs, Donald [mailto:[EMAIL PROTECTED] 
Envoyé : mardi 8 août 2006 14:54
À : sqlite-users@sqlite.org
Objet : RE: [sqlite] Recovery tool ?

 "...alimentation interruption..." --- Power interruption -- fascinating how 
languages work!

Yohann,

Sqlite tends to be pretty good in the face of power interruptions on most OS's, 
sorry you are having trouble.

I think you'll want to:
  1) copy your database in its current state (for safekeeping).
  2) See if the "vacuum" command will run, and if all tables remain afterwards.
   http://www.sqlite.org/lang_vacuum.html
  3) Try a ".dump" command on the entire database.
  4) If the above .dump aborts on a particular table, try individual .dump of
 all OTHER tables.
  5) Now gauge how much data, if any, is missing, and plan your remediation.

-Original Message-
From: zze-ContentLab MARTINEAU Y ext RD-SIRP-REN [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 08, 2006 4:48 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Recovery tool ?

Hello,

I had an alimentation interruption on my sqlite database running on linux.
And now the data file seems to be corrupted. Is there any tool to cleanup this 
file ? Or is it necessary to restart from a blank new data file ?

Thank you,

yohann

RE: [sqlite] Recovery tool ?

[zze-ContentLab MARTINEAU Y ext RD-SIRP-REN]

Sorry, I meant a power failure, or a power loss if you prefer.

I have generated a new data file, this is not a problem in a development 
environment, but what should I do in a production environment ?

Thanks

-Message d'origine-
De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
Envoyé : mardi 8 août 2006 13:38
À : sqlite-users@sqlite.org
Objet : Re: [sqlite] Recovery tool ?

"zze-ContentLab MARTINEAU Y ext RD-SIRP-REN" <[EMAIL PROTECTED]> wrote:
> Hello,
> 
> I had an alimentation interruption on my sqlite database running on 
> linux. And now the data file seems to be corrupted. Is there any tool 
> to cleanup this file ? Or is it necessary to restart from a blank new 
> data file ?
> 

What is an "alimentation interruption" and how might such a thing corrupt the 
database?
--
D. Richard Hipp   <[EMAIL PROTECTED]>

RE: [sqlite] Recovery tool ?

[Griggs, Donald]

 "...alimentation interruption..." --- Power interruption -- fascinating how
languages work!

Yohann,

Sqlite tends to be pretty good in the face of power interruptions on most
OS's, sorry you are having trouble.

I think you'll want to:
  1) copy your database in its current state (for safekeeping).
  2) See if the "vacuum" command will run, and if all tables remain
afterwards.
   http://www.sqlite.org/lang_vacuum.html
  3) Try a ".dump" command on the entire database.
  4) If the above .dump aborts on a particular table, try individual .dump
of
 all OTHER tables.
  5) Now gauge how much data, if any, is missing, and plan your remediation.

-Original Message-
From: zze-ContentLab MARTINEAU Y ext RD-SIRP-REN
[mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 08, 2006 4:48 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] Recovery tool ?

Hello,

I had an alimentation interruption on my sqlite database running on linux.
And now the data file seems to be corrupted. Is there any tool to cleanup
this file ? Or is it necessary to restart from a blank new data file ?

Thank you,

yohann

Re: [sqlite] Recovery tool ?

[C.Peachment]

Alimentation is the French word and alimentacion is the
Spanish word for "food" or "feed".

In this case the poster is referring to an electrical power failure
on the computer.

On Tue, 08 Aug 2006 11:38:09 +, [EMAIL PROTECTED] wrote:

>"zze-ContentLab MARTINEAU Y ext RD-SIRP-REN" <[EMAIL PROTECTED]> wrote:
>> Hello,
>> 
>> I had an alimentation interruption on my sqlite database running on
>> linux. And now the data file seems to be corrupted. Is there any tool to
>> cleanup this file ? Or is it necessary to restart from a blank new data
>> file ?
>> 

>What is an "alimentation interruption" and how might such a thing
>corrupt the database?
>--
>D. Richard Hipp   <[EMAIL PROTECTED]>

[sqlite] Re: Multiple SELECTs (and single SELECT) and TRANSACTION ?

[Igor Tandetnik]

RohitPatel 
wrote:

Scenario 1
If action of some user needs to execute multiple SELECT statements
(read-only, no plan to write), it needs to start explicit transaction
to get consistent reads across read-only multiple statements.

Q1. Which one is preferable -> BEGIN TRANSACTION  or  BEGIN IMMEDIATE
?


BEGIN IMMEDIATE would block other readers. Don't use it unless you plan 
to write.



Q2. What is preferable After Multiple SELECT statements gets over ->
COMMIT or ROLLBACK ? (note: no change is made since only SELECTs)


I don't think it matters.


Scenario 2
If action of some user needs to execute only single SELECT statement
(read-only, no plan to write)...

As what I understand, there is no point in wrapping such single
SELECT in transaction.


Right. There's no harm either.


Q3. What is preferable, whether to start explicit transaction or not
for single SELECT ?


Doesn't matter.

Igor Tandetnik 

Re: [sqlite] Recovery tool ?

[drh]

"zze-ContentLab MARTINEAU Y ext RD-SIRP-REN" <[EMAIL PROTECTED]> wrote:
> Hello,
> 
> I had an alimentation interruption on my sqlite database running on
> linux. And now the data file seems to be corrupted. Is there any tool to
> cleanup this file ? Or is it necessary to restart from a blank new data
> file ?
> 

What is an "alimentation interruption" and how might such a thing
corrupt the database?
--
D. Richard Hipp   <[EMAIL PROTECTED]>

[sqlite] Multiple SELECTs (and single SELECT) and TRANSACTION ?

[RohitPatel9999]

Hi All,

While using SQLite dll Version 3.3.4 on Windows
- Multiple threads/processes access SQLite database, 
- Each thread does some SELECTs, INSERTs or UPDATEs.

Scenario 1 
If action of some user needs to execute multiple SELECT statements
(read-only, no plan to write), it needs to start explicit transaction to get
consistent reads across read-only multiple statements.

Q1. Which one is preferable -> BEGIN TRANSACTION  or  BEGIN IMMEDIATE ?

Q2. What is preferable After Multiple SELECT statements gets over -> COMMIT
or ROLLBACK ? (note: no change is made since only SELECTs)



Scenario 2
If action of some user needs to execute only single SELECT statement
(read-only, no plan to write)...

As what I understand, there is no point in wrapping such single SELECT in
transaction.

Q3. What is preferable, whether to start explicit transaction or not for
single SELECT ?


I truly appreciate any help/guidance.
Rohit

-- 
View this message in context: 
http://www.nabble.com/Multiple-SELECTs-%28and-single-SELECT%29-and-TRANSACTION---tf2072083.html#a5704541
Sent from the SQLite forum at Nabble.com.

Re: [sqlite] About Triggers

[Roberto]

On 07/08/06, chetana bhargav <[EMAIL PROTECTED]> wrote:

  Can you elobarate more on this. ( For me if two threads register for the same 
trigger condition, with different callback functions, do both the functions get 
called)


The short answer to your question is no. (But I could be wrong) If I
remember  correctly, when Thread A and B both register a custom
function in a trigger, say for an update, Then when Thread A does an
update, only the trigger in Thread A will be 'notified'.

RE: [sqlite] Problems opening db in win9x and utf8 filename

[Costas Stergiou]

> 
> Irony aside, you wouldn't believe how many systems claiming to be i18n
> aware fail miserably when handling other than Latin-1 charsets
> (especially in filenames).
> 
> If Costas can provide a patch, I think it'd be a useful addition to
> the SQLite's Win32 file handling. I'm not sure that opening a database
> from a filehandle or FILE structure would be a good idea.
> 
Unfortunately, I am not a c developer, I work with Delphi. But I can read c
very well and give pseudo-code of the corrections needed (actually it is an
if-then-else, nothing more).
The problem is in the logic behind the way things are handled, the changes
are trivial.

Costas

[sqlite] Recovery tool ?

[zze-ContentLab MARTINEAU Y ext RD-SIRP-REN]

Hello,

I had an alimentation interruption on my sqlite database running on
linux. And now the data file seems to be corrupted. Is there any tool to
cleanup this file ? Or is it necessary to restart from a blank new data
file ?

Thank you,

yohann

Re: [sqlite] Problems opening db in win9x and utf8 filename

[Peter Cunderlik]

On 8/8/06, John Stanton <[EMAIL PROTECTED]> wrote:

Our Sqlite applications work not only on Win98 and Win2000 but also on
Linux, AIX and Solaris.  Where did we go wrong?


Irony aside, you wouldn't believe how many systems claiming to be i18n
aware fail miserably when handling other than Latin-1 charsets
(especially in filenames).

If Costas can provide a patch, I think it'd be a useful addition to
the SQLite's Win32 file handling. I'm not sure that opening a database
from a filehandle or FILE structure would be a good idea.

Peter

Re: [sqlite] Problems opening db in win9x and utf8 filename

[John Stanton]

Obviously one has to have file names which do not clash with the rules 
of the underlying file system.  If you need to map a name to suit the OS 
you can detect the Windows OS version in your application and enforce 
compatibility by having a lookup table or by mangling.  As the old 
saying goes "In computer science any problem can be solved by yet 
another level of indirection".


Costas Stergiou wrote:

Hi John,
Have you tried to use sqlite3_open with a path that contains non-ascii chars
and make it work at the same time in Win9x and win2K? 
The 2 apps I mentioned before (sqLiteExplorer and SQLiteSpy) both fail the

above test (and for a good reason)
Costas

P.S. As I said, you can make an app work on both of these OSs, but with
external manipulation. 




-Original Message-
From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 08, 2006 11:13 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Problems opening db in win9x and utf8 filename

Our Sqlite applications work not only on Win98 and Win2000 but also on
Linux, AIX and Solaris.  Where did we go wrong?

Costas Stergiou wrote:


Hi all,
I saw no more comments on this suggestion. It is very simple to program
around this issue in user code, but I would like to see it fixed in the
library level. Unless someone has made this work-around in his code, an
application cannot work at the same time in Win9x and Win2k if there is


any


ansii char in the filepath.
Costas





-Original Message-
From: Costas Stergiou [mailto:[EMAIL PROTECTED]
Sent: Saturday, August 05, 2006 11:47 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Problems opening db in win9x and utf8 filename





I no longer have a win98 system to test with, but based on my
understanding...

os_win.c attempts to convert the filename from UTF-8 to UTF-16.  If it
succeeds, it calls CreateFileW;


Actually, there is a flag there that caused the convertion to UTF-16 to
'fail' (it doesn't really fail, just that the utf8ToUnicode returns 0).




if it fails, it calls CreateFileA with
the original string.


Exactly




CreateFileW is a nonfunctional stub on win98, so
when you pass a UTF-8 filename sqlite takes that codepath and fails.
An ANSI filename won't pass the UTF-8 conversion, so CreateFileA is
called with the ANSI string.


Actually, in Win98 it will pass the conversion, but as I said above, the
function fails by a check: "if (!isNT())"




That doesn't necessarily explain win2k though.  Perhaps the current
user locale does not match the ANSI encoding of the filename you're
passing in?  Internally win2k only uses the Unicode version, so
CreateFileA must do an implict conversion to Unicode using the current
user codepage.


Now that I checked the code, it actually does.
Unfortunately, the way the code is setup makes it necessary for the


caller


to check in which OS it runs and either use UTF8 paths or ansii ones. I
think this is not a good technique (and not actually intended from what


I


have read in the docs) since the sqlite3_open does not give a truly
uniform
interface to the caller.

My suggestion is this:
The sqlite3_open should always expect a utf8 path (as the docs say). If


in


win2k everything works fine. If in win98 it should convert the path to
utf16
and THEN convert it to ansii using the CP_ACP (current ansii code page).
This will work for 99.9% cases since in non-English win9x OS, almost


99.9%


ansii strings are in the system's locale.
I think this is also the expected behavior (and what I have programmed


my


app to do, until I tested it in win98).

To make these changes, all the logic of os_win.c should change to
accommodate the above. I would certainly say that the way it currently
works
is wrong (bug).
Of course, there is the problem of breaking existing code (since many
win9x
user will not have read the docs, or else someone would have mentioned
this
behavior looong time agoe).
To maintain compatibility (e.g. to accept ansii non-utf8 paths), a check
can
be made on whether the supplied path is in utf8 (heuristically this has
almost 100% success) and then do the above.

Costas






MSLU does provide a functional CreateFileW wrapper for win9x, but I
don't believe the stock sqlite binaries are built with it.


On 8/5/06, Peter Cunderlik <[EMAIL PROTECTED]> wrote:




I think you will never succeed using UTF-8 encoded filenames on those
systems. I don't know how it can be done programmatically, but each
file or directory name has its 8.3 name as well, i.e. "Program Files"
would be "progra~1". I think this is the safest way how to pass
filenames to SQLite. It should work on Win 9x as well as 2K and XP.


NTFS can have 8.3 shortname creation disabled.  Systems running
without it are not common but do exist, so you should avoid relying on
them if at all possible.

RE: [sqlite] Problems opening db in win9x and utf8 filename

[Costas Stergiou]

Hi John,
Have you tried to use sqlite3_open with a path that contains non-ascii chars
and make it work at the same time in Win9x and win2K? 
The 2 apps I mentioned before (sqLiteExplorer and SQLiteSpy) both fail the
above test (and for a good reason)
Costas

P.S. As I said, you can make an app work on both of these OSs, but with
external manipulation. 

> -Original Message-
> From: John Stanton [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, August 08, 2006 11:13 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Problems opening db in win9x and utf8 filename
> 
> Our Sqlite applications work not only on Win98 and Win2000 but also on
> Linux, AIX and Solaris.  Where did we go wrong?
> 
> Costas Stergiou wrote:
> > Hi all,
> > I saw no more comments on this suggestion. It is very simple to program
> > around this issue in user code, but I would like to see it fixed in the
> > library level. Unless someone has made this work-around in his code, an
> > application cannot work at the same time in Win9x and Win2k if there is
> any
> > ansii char in the filepath.
> > Costas
> >
> >
> >
> >>-Original Message-
> >>From: Costas Stergiou [mailto:[EMAIL PROTECTED]
> >>Sent: Saturday, August 05, 2006 11:47 PM
> >>To: sqlite-users@sqlite.org
> >>Subject: RE: [sqlite] Problems opening db in win9x and utf8 filename
> >>
> >>
> >>
> >>>I no longer have a win98 system to test with, but based on my
> >>>understanding...
> >>>
> >>>os_win.c attempts to convert the filename from UTF-8 to UTF-16.  If it
> >>>succeeds, it calls CreateFileW;
> >>
> >>Actually, there is a flag there that caused the convertion to UTF-16 to
> >>'fail' (it doesn't really fail, just that the utf8ToUnicode returns 0).
> >>
> >>
> >>>if it fails, it calls CreateFileA with
> >>>the original string.
> >>
> >>Exactly
> >>
> >>
> >>>CreateFileW is a nonfunctional stub on win98, so
> >>>when you pass a UTF-8 filename sqlite takes that codepath and fails.
> >>>An ANSI filename won't pass the UTF-8 conversion, so CreateFileA is
> >>>called with the ANSI string.
> >>
> >>Actually, in Win98 it will pass the conversion, but as I said above, the
> >>function fails by a check: "if (!isNT())"
> >>
> >>
> >>>That doesn't necessarily explain win2k though.  Perhaps the current
> >>>user locale does not match the ANSI encoding of the filename you're
> >>>passing in?  Internally win2k only uses the Unicode version, so
> >>>CreateFileA must do an implict conversion to Unicode using the current
> >>>user codepage.
> >>
> >>Now that I checked the code, it actually does.
> >>Unfortunately, the way the code is setup makes it necessary for the
> caller
> >>to check in which OS it runs and either use UTF8 paths or ansii ones. I
> >>think this is not a good technique (and not actually intended from what
> I
> >>have read in the docs) since the sqlite3_open does not give a truly
> >>uniform
> >>interface to the caller.
> >>
> >>My suggestion is this:
> >>The sqlite3_open should always expect a utf8 path (as the docs say). If
> in
> >>win2k everything works fine. If in win98 it should convert the path to
> >>utf16
> >>and THEN convert it to ansii using the CP_ACP (current ansii code page).
> >>This will work for 99.9% cases since in non-English win9x OS, almost
> 99.9%
> >>ansii strings are in the system's locale.
> >>I think this is also the expected behavior (and what I have programmed
> my
> >>app to do, until I tested it in win98).
> >>
> >>To make these changes, all the logic of os_win.c should change to
> >>accommodate the above. I would certainly say that the way it currently
> >>works
> >>is wrong (bug).
> >>Of course, there is the problem of breaking existing code (since many
> >>win9x
> >>user will not have read the docs, or else someone would have mentioned
> >>this
> >>behavior looong time agoe).
> >>To maintain compatibility (e.g. to accept ansii non-utf8 paths), a check
> >>can
> >>be made on whether the supplied path is in utf8 (heuristically this has
> >>almost 100% success) and then do the above.
> >>
> >>Costas
> >>
> >>
> >>
> >>
> >>>MSLU does provide a functional CreateFileW wrapper for win9x, but I
> >>>don't believe the stock sqlite binaries are built with it.
> >>>
> >>>
> >>>On 8/5/06, Peter Cunderlik <[EMAIL PROTECTED]> wrote:
> >>>
> >>>
> I think you will never succeed using UTF-8 encoded filenames on those
> systems. I don't know how it can be done programmatically, but each
> file or directory name has its 8.3 name as well, i.e. "Program Files"
> would be "progra~1". I think this is the safest way how to pass
> filenames to SQLite. It should work on Win 9x as well as 2K and XP.
> >>>
> >>>NTFS can have 8.3 shortname creation disabled.  Systems running
> >>>without it are not common but do exist, so you should avoid relying on
> >>>them if at all possible.
> >
> >
> >
> >

Re: [sqlite] Problems opening db in win9x and utf8 filename

[John Stanton]

Our Sqlite applications work not only on Win98 and Win2000 but also on 
Linux, AIX and Solaris.  Where did we go wrong?


Costas Stergiou wrote:

Hi all,
I saw no more comments on this suggestion. It is very simple to program
around this issue in user code, but I would like to see it fixed in the
library level. Unless someone has made this work-around in his code, an
application cannot work at the same time in Win9x and Win2k if there is any
ansii char in the filepath.
Costas




-Original Message-
From: Costas Stergiou [mailto:[EMAIL PROTECTED]
Sent: Saturday, August 05, 2006 11:47 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] Problems opening db in win9x and utf8 filename




I no longer have a win98 system to test with, but based on my
understanding...

os_win.c attempts to convert the filename from UTF-8 to UTF-16.  If it
succeeds, it calls CreateFileW;


Actually, there is a flag there that caused the convertion to UTF-16 to
'fail' (it doesn't really fail, just that the utf8ToUnicode returns 0).



if it fails, it calls CreateFileA with
the original string.


Exactly



CreateFileW is a nonfunctional stub on win98, so
when you pass a UTF-8 filename sqlite takes that codepath and fails.
An ANSI filename won't pass the UTF-8 conversion, so CreateFileA is
called with the ANSI string.


Actually, in Win98 it will pass the conversion, but as I said above, the
function fails by a check: "if (!isNT())"



That doesn't necessarily explain win2k though.  Perhaps the current
user locale does not match the ANSI encoding of the filename you're
passing in?  Internally win2k only uses the Unicode version, so
CreateFileA must do an implict conversion to Unicode using the current
user codepage.


Now that I checked the code, it actually does.
Unfortunately, the way the code is setup makes it necessary for the caller
to check in which OS it runs and either use UTF8 paths or ansii ones. I
think this is not a good technique (and not actually intended from what I
have read in the docs) since the sqlite3_open does not give a truly
uniform
interface to the caller.

My suggestion is this:
The sqlite3_open should always expect a utf8 path (as the docs say). If in
win2k everything works fine. If in win98 it should convert the path to
utf16
and THEN convert it to ansii using the CP_ACP (current ansii code page).
This will work for 99.9% cases since in non-English win9x OS, almost 99.9%
ansii strings are in the system's locale.
I think this is also the expected behavior (and what I have programmed my
app to do, until I tested it in win98).

To make these changes, all the logic of os_win.c should change to
accommodate the above. I would certainly say that the way it currently
works
is wrong (bug).
Of course, there is the problem of breaking existing code (since many
win9x
user will not have read the docs, or else someone would have mentioned
this
behavior looong time agoe).
To maintain compatibility (e.g. to accept ansii non-utf8 paths), a check
can
be made on whether the supplied path is in utf8 (heuristically this has
almost 100% success) and then do the above.

Costas





MSLU does provide a functional CreateFileW wrapper for win9x, but I
don't believe the stock sqlite binaries are built with it.


On 8/5/06, Peter Cunderlik <[EMAIL PROTECTED]> wrote:



I think you will never succeed using UTF-8 encoded filenames on those
systems. I don't know how it can be done programmatically, but each
file or directory name has its 8.3 name as well, i.e. "Program Files"
would be "progra~1". I think this is the safest way how to pass
filenames to SQLite. It should work on Win 9x as well as 2K and XP.


NTFS can have 8.3 shortname creation disabled.  Systems running
without it are not common but do exist, so you should avoid relying on
them if at all possible.

RE: [sqlite] Problems opening db in win9x and utf8 filename

[Costas Stergiou]

Hi all,
I saw no more comments on this suggestion. It is very simple to program
around this issue in user code, but I would like to see it fixed in the
library level. Unless someone has made this work-around in his code, an
application cannot work at the same time in Win9x and Win2k if there is any
ansii char in the filepath.
Costas


> -Original Message-
> From: Costas Stergiou [mailto:[EMAIL PROTECTED]
> Sent: Saturday, August 05, 2006 11:47 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Problems opening db in win9x and utf8 filename
> 
> 
> >
> > I no longer have a win98 system to test with, but based on my
> > understanding...
> >
> > os_win.c attempts to convert the filename from UTF-8 to UTF-16.  If it
> > succeeds, it calls CreateFileW;
> Actually, there is a flag there that caused the convertion to UTF-16 to
> 'fail' (it doesn't really fail, just that the utf8ToUnicode returns 0).
> 
> > if it fails, it calls CreateFileA with
> > the original string.
> Exactly
> 
> > CreateFileW is a nonfunctional stub on win98, so
> > when you pass a UTF-8 filename sqlite takes that codepath and fails.
> > An ANSI filename won't pass the UTF-8 conversion, so CreateFileA is
> > called with the ANSI string.
> Actually, in Win98 it will pass the conversion, but as I said above, the
> function fails by a check: "if (!isNT())"
> 
> > That doesn't necessarily explain win2k though.  Perhaps the current
> > user locale does not match the ANSI encoding of the filename you're
> > passing in?  Internally win2k only uses the Unicode version, so
> > CreateFileA must do an implict conversion to Unicode using the current
> > user codepage.
> Now that I checked the code, it actually does.
> Unfortunately, the way the code is setup makes it necessary for the caller
> to check in which OS it runs and either use UTF8 paths or ansii ones. I
> think this is not a good technique (and not actually intended from what I
> have read in the docs) since the sqlite3_open does not give a truly
> uniform
> interface to the caller.
> 
> My suggestion is this:
> The sqlite3_open should always expect a utf8 path (as the docs say). If in
> win2k everything works fine. If in win98 it should convert the path to
> utf16
> and THEN convert it to ansii using the CP_ACP (current ansii code page).
> This will work for 99.9% cases since in non-English win9x OS, almost 99.9%
> ansii strings are in the system's locale.
> I think this is also the expected behavior (and what I have programmed my
> app to do, until I tested it in win98).
> 
> To make these changes, all the logic of os_win.c should change to
> accommodate the above. I would certainly say that the way it currently
> works
> is wrong (bug).
> Of course, there is the problem of breaking existing code (since many
> win9x
> user will not have read the docs, or else someone would have mentioned
> this
> behavior looong time agoe).
> To maintain compatibility (e.g. to accept ansii non-utf8 paths), a check
> can
> be made on whether the supplied path is in utf8 (heuristically this has
> almost 100% success) and then do the above.
> 
> Costas
> 
> 
> 
> > MSLU does provide a functional CreateFileW wrapper for win9x, but I
> > don't believe the stock sqlite binaries are built with it.
> >
> >
> > On 8/5/06, Peter Cunderlik <[EMAIL PROTECTED]> wrote:
> >
> > > I think you will never succeed using UTF-8 encoded filenames on those
> > > systems. I don't know how it can be done programmatically, but each
> > > file or directory name has its 8.3 name as well, i.e. "Program Files"
> > > would be "progra~1". I think this is the safest way how to pass
> > > filenames to SQLite. It should work on Win 9x as well as 2K and XP.
> >
> > NTFS can have 8.3 shortname creation disabled.  Systems running
> > without it are not common but do exist, so you should avoid relying on
> > them if at all possible.

Re: [sqlite] Replace of substring in sqlite-table - how can I do this?

[wqual]

Hi Donald,
thanks for this valuable comment. Regarding the regexp-function, I am not 
really sure whether I understood it entirely or not: is it correct that I can 
use the regexp *only* in external programs (e.g. a perl programme), but not 
on the sqlite command line? There is no possibility to activate regexp also 
for the sqlite-command line? Sorry for asking these questions
I am using sqlite on the command-line and via sqlitebrowser  - and I am no 
programmer (hm.).
Unfortunately, the substr-function won't do the job in my case, as the 
position of substrings may vary within my tables. 

Best regards,
Wolfgang

-

Am Montag 07 August 2006 pH:35:17 nachmittags/abends schrieb Griggs, Donald:
> Hi Wolfgang,
>
> Regarding: "...thank you very much for your reply. But is there no command
> within sqlite to do this? In my case, only substrings of the cells of one
> column need to be changed!"
>
>
> Sqlite was designed as a small SQL library -- extremely small, considering
> its capabilities -- implementing most of the SQL92 standard.
>
> The SQL commands are designed to store and retrieve data.
>
> The idea is that one incorporates this library into one's own program,
> using a compatible programming language of one's choice.  Using your chosen
> programming language, you perform the sorts of data transformations you
> write about.
>
> So I don't think many would think it a shortcoming of sqlite that it
> doesn't provide general programming capabilities, but rather that it avoids
> *duplication* by *not* including such things.
>
> Not only can you include sqlite in a program written in C, perl, VB, etc.,
> but you can link an sqlite database to Open Office or Microsoft Access as
> "front ends" to your database in order to manipulate data.
>
> Note that there is ALSO an sqlite command line *utility*, which is a useful
> program allowing one to enter sqlite commands without writing a program or
> linking another "front end", but it's not intended to be a general data
> manipulation package.
>
> That being said, you may want to be sure to look at page:
> http://www.sqlite.org/lang_expr.html
> including the "substr()" and concatenate expressions.   The "regex()"
> function is not included in the command-line utility, but you can link in
> one of your choice.
>
> Sqlite also allows you to define your own SQL-callable functions within
> your program.