[sqlite] ANN: New version of Sqlite3Explorer available

[Cariotoglou Mike]

Some small but usefull changes implemented, as per user request:

get latest version here : http://www.singular.gr/sqlite/

Mike Cariotoglou
Disclaimer Notice: This communication may be confidential. If you are not an 
intended recipient please note that any form of distribution, copying or use of 
this communication or the information in it, is prohibited. Please inform the 
sender appropriately and delete or destroy any copies of it from your system. 
SingularLogic SA & SingularLogic Integrator SA cannot accept any responsibility 
for the accuracy or completeness of this message as it has been transmitted 
over a public network.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug when uUsing Parameters with views

[Cariotoglou Mike]

Just an idea :

Parameters bound via sql_bind... *should* have affinity, since they are 
manifestly typed.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug when uUsing Parameters with views

[Cariotoglou Mike]

Quoting from the documentation on Type Affinity:

"
SQLite may attempt to convert values between the numeric storage classes
(INTEGER and REAL) and TEXT before performing a comparison. Whether or
not any conversions are attempted before the comparison takes place
depends on the nominal affinity assigned to the expressions on either
side of the binary operator. Affinities are assigned to expressions in
the following cases: 

An expression that is a simple reference to a column value has the same
affinity as the column it refers to. Note that if X and Y.Z are column
names, then +X and +Y.Z are considered expressions.

An expression of the form "CAST( TO )" is assigned an
affinity as if it were a reference to a column declared with type 


Conversions are applied before the comparison as described below. In the
following bullet points, the two operands are refered to as expression A
and expression B. Expressions A and B may appear as either the left or
right operands - the following statements are true when considering both
"A B" and "B A". 

When two expressions are compared, if expression A has INTEGER or REAL
or NUMERIC affinity and expression B does not, then NUMERIC affinity is
applied to the value of expression B before the comparison takes place. 

When two expressions are compared, if expression A has been assigned an
affinity and expression B has not, then the affinity of expression A is
applied to the value of expression B before the comparison takes place. 

Otherwise, if neither of the above applies, no conversions occur. The
results are compared as is. If a string is compared to a number, the
number will always be less than the string. 
"

since, in the examples posted, (select count(*) from ...) should be an
expression, and since it is compared to a value, it should take on the
affinity
of the value, no ? AH, GOT IT. values do NOT have affinity. so I would
either need to cast the expression OR the value. 

is this correct ?
 

> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov
> Sent: Tuesday, December 15, 2009 5:38 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Bug when uUsing Parameters with views
> 
> Apparently result of count() and probably all other aggregate 
> functions (as well as result of any function at all) has no affinity.
> So when you compare it to anything having no affinity too you 
> have no type transformation during comparison. And thus 
> values have to be exactly the same including their types...
> 
> Everything is explainable and probably is impossible to fix 
> unless core aggregate functions become specially recognizable 
> part of the core or SQLite team figures out how to make 
> functions return their results with some affinity assigned...
> 
> Pavel
> 
> On Tue, Dec 15, 2009 at 10:25 AM, Simon Davies 
>  wrote:
> > 2009/12/15 D. Richard Hipp :
> >>
> >>>
> >>
> >> Because string '2' is not the same thing as integer 2.
> >>
> >> sqlite3> select 2='2';
> >> 0
> >> sqlite3>
> >>
> >
> > Why
> > sqlite> select cast( 2 as integer ) = '2';
> > 1
> >
> >>
> >> D. Richard Hipp
> >> d...@hwaci.com
> >>
> >
> > Simon
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Bug when uUsing Parameters with views

[Cariotoglou Mike]

Simon, thanks for the answer. your code helped track down the issue,
which I *still* believe to be a bug:

The issue is not with parameters, after all, but with comparison of
values, and something "magic" about count(), I suspect.
It has to do with the fact that the layer I am using (PHP PDO) actually
binds parameters as strings by defult.
in general, this should not be an issue, since sqlite seems to be quite
relaxed wrt to typing.
However, in this case, it matters. The "bug" is now reproducible easily:

give the bas SQL statmenent:

select * from 
(select *,"
(select count(*) from ITEM_ARTIST where 
ARTIST_id=artists.artist_id) CNT 
from ARTISTS
)

if you apply this WHERE : ' where artist_id=1' it works
if you quote the value (making it a string), it still works : where
artist_id='1'
if you supply a value for CNT as integer, it works : where CNT=1
if you supply the value for CNT as string, it BREAKS: where CNT='1'


Further investigation shows that the issue is somehow related to how
sqlite compares values,
and affinity. It seems that, while the rules defined in the
documentation for comparison operators
work as expected in most cases, something breaks in the case of
pseudo-columns that are the result of
a sub-select (like the case above, for CNT). 

If I use CAST to define an explicit affinity for the expression, it
starts working :

select * from 
(select *,"
cast((select count(*) from ITEM_ARTIST where
ARTIST_id=artists.artist_id) as int) CNT 
from ARTISTS
)

now, any WHERE clause works as expected.

Ideas ?

> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Davies
> Sent: Tuesday, December 15, 2009 1:58 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Bug when uUsing Parameters with views
> 
> 2009/12/15 Cariotoglou Mike <m...@singular.gr>:
> > I checked in the bug database, and this does not seem to have been 
> > reported, and IMHO it is definitely a bug.
> > workarounds exist,as pointed out by me and others. still, I 
> would like 
> > to hear from the core team whether this is recognized as a bug, and 
> > will be dealt with at some point in time.
> > btw, I would have liked to post a script demonstrating the 
> bug, but I 
> > do not think this is possible, due to the fact that the 
> command-line 
> > sqlite does not handle parametric statements, or at least I 
> don't know 
> > how to write one :)
> >
> 
> I am unable to reproduce your problem (version 3.6.11):
> 
> //
> // create table item_artist( artist_id integer, data text ); 
> // create table artists( artist_id integer, data text ); // 
> insert into artists values( 1, 'a1_pic1' ); // insert into 
> artists values( 2, 'a1_pic2' ); // insert into item_artist 
> values( 1, 'a1_item1' ); // insert into item_artist values( 
> 2, 'a2_item1' ); // insert into item_artist values( 2, 'a2_item2' );
> 
> #include "stdafx.h"
> #define SQLITE_PRIVATE
> #include "sqlite3.c"
> 
> int _tmain(int argc, _TCHAR* argv[])
> {
>   int cnt;
>   int dbStatus;
>   sqlite3* dbH;
>   sqlite3_stmt* stmt;
>   dbStatus = sqlite3_open( "tstBind.db",  );
> 
>   while( fscanf( stdin, "%d",  ) )
>   {
>   char* sql = "select * from ( select *,"
>   "(select count(*) from ITEM_ARTIST where "
>   "ARTIST_id=artists.artist_id) CNT from 
> ARTISTS ) where "
>   "cnt = :a;";
> 
>   const char* tail;
> 
>   if( 0 == cnt )
>   {
>   exit(0);
>   }
> 
>   dbStatus = sqlite3_prepare_v2( dbH, sql, 
> strlen( sql ), ,  );
>   if( SQLITE_OK != dbStatus )
>   {
>   printf( "%s\n", sqlite3_errmsg( dbH ) );
>   }
> 
>   dbStatus = sqlite3_bind_int( stmt, 1, cnt );
>   if( SQLITE_OK != dbStatus )
>   {
>   printf( "%s\n", sqlite3_errmsg( dbH ) );
>   }
> 
>   while( SQLITE_ROW == ( dbStatus = sqlite3_step( 
> stmt ) ) )
>   {
>   printf( "%s ", sqlite3_column_text( stmt, 0 ) );
>   printf( "%s ", sqlite3_column_text( stmt, 1 ) );
>   printf( "%s\n", sqlite3_column_text( 
> stmt, 2 ) );
>   }
>   sqlite3_finalize( stmt );
>   }
>   sqlite3_close( dbH );
> 
>   return 0;
> }
> 
> On executing if I enter 1 I get
> 1
> 1 a1_pic1 1
> 
> and if 2, then
> 2
> 2 a2_pic1 2
> 
> which all looks ok
> 
> Regards,
> Simon
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Bug when uUsing Parameters with views

[Cariotoglou Mike]

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

> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Tim Romano
> Sent: Tuesday, December 15, 2009 2:37 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Using Parameters with views
> 
> I know the original question was about views and parameters, 
> but if the OP just needs the answer to this kind of query and 
> a view isn't mandatory, I've been able to use a parameter 
> with the HAVING clause.  
> The query could be rewritten:
> 
> select artistname
> from artist
> join ITEM_ARTIST on artist.artistid = ITEM_ARTIST.artistid 
> group by artistname having count(ITEM_ARTIST.artistid) = 
> @desireditemcount
> 
> Regards
> Tim Romano
> 
> Cariotoglou Mike wrote:
> > I thought of that, and the answer is NO.
> > actually, since my post, I did a little investigation :
> > it is not the view that is the problem, it is the parameter 
> when used 
> > on a column that contains the COUNT function. Weird, it 
> seems like a genuine bug.
> >
> > if you replace
> >
> > WHERE CNT LIKE :PARAM
> >
> > in my original code, or in your example, it will work !
> >
> > I tried this : (the subselect was originally the view)
> >
> > select * from
> > (
> > select
> > *,(select count(*) from ITEM_ARTIST where 
> ARTIST_id=artists.artist_id) 
> > CNT from ARTISTS
> > ) where cnt like :a
> >
> > and it works.
> > changing the operator from "LIKE" to "=" breaks it.
> >
> >
> > -Original Message-
> > From: sqlite-users-boun...@sqlite.org on behalf of Darren Duncan
> > Sent: Mon 12/14/2009 2:44 AM
> > To: General Discussion of SQLite Database
> > Subject: Re: [sqlite] Using Parameters with views
> >  
> > Do bind parameters work for you if you replace the view 
> with a subselect?
> >
> > Does this work for you?
> >
> > select * from (
> >select t1.*,
> >(select count(*) from song_artist t2 where 
> t1.artist_id=t2.artist_id) as CNT
> >from artists
> > ) where CNT=:PARAM
> >
> > -- Darren Duncan
> >
> > Cariotoglou Mike wrote:
> >   
> >> I don't know if this has come up before, is so please 
> point me to the 
> >> right direction :)
> >>
> >> I believe that using parameterized queries with views does 
> not work 
> >> as expected.
> >>
> >> consider this (more or less self-explanatory) schema:
> >>
> >> create table artists(artist_id)
> >> create table songs(song_id)
> >> create table song_artist(song_id,artist_id)
> >>
> >> create view VARTISTS as
> >>  select t1.*,
> >>  (select count(*) from song_artist t2 where 
> >> t1.artist_id=t2.artist_id) as CNT  from artists
> >>  
> >>  The sql may be a little off as I am typing this from scratch, but 
> >> you get the  idea.
> >>  
> >>  now, if I do this:
> >>  
> >>  select * from VARTISTS where CNT=10
> >>  
> >>  it works.
> >>  
> >>  This, however, always returns an empty result set :
> >>  
> >>  select * from VARTISTS where CNT=:PARAM,
> >>  
> >>  for any value I bind to :PARAM.
> >>  
> >>  is this known/documented ?
> >> 
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> >   
> > 
> --
> > --
> >
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >   
> > 
> --
> > --
> >
> >
> > No virus found in this incoming message.
> > Checked by AVG - www.avg.com
> > Version: 8.5.427 / Virus Database: 270.14.106/2563 - Release Date: 
> > 12/13/09 19:47:00
> >
> >   
> 
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Using Parameters with views

[Cariotoglou Mike]

I thought of that, and the answer is NO.
actually, since my post, I did a little investigation :
it is not the view that is the problem, it is the parameter when used on a 
column that contains
the COUNT function. Weird, it seems like a genuine bug.

if you replace 

WHERE CNT LIKE :PARAM

in my original code, or in your example, it will work !

I tried this : (the subselect was originally the view)

select * from
(
select
*,(select count(*) from ITEM_ARTIST where ARTIST_id=artists.artist_id) CNT
from ARTISTS
) where cnt like :a

and it works.
changing the operator from "LIKE" to "=" breaks it.


-Original Message-
From: sqlite-users-boun...@sqlite.org on behalf of Darren Duncan
Sent: Mon 12/14/2009 2:44 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Using Parameters with views
 
Do bind parameters work for you if you replace the view with a subselect?

Does this work for you?

select * from (
   select t1.*,
   (select count(*) from song_artist t2 where t1.artist_id=t2.artist_id) as CNT
   from artists
) where CNT=:PARAM

-- Darren Duncan

Cariotoglou Mike wrote:
> I don't know if this has come up before, is so please point me to the
> right direction :)
> 
> I believe that using parameterized queries with views does not work
> as expected.
> 
> consider this (more or less self-explanatory) schema:
> 
> create table artists(artist_id)
> create table songs(song_id)
> create table song_artist(song_id,artist_id)
> 
> create view VARTISTS as
>  select t1.*,
>  (select count(*) from song_artist t2 where t1.artist_id=t2.artist_id)
> as CNT
>  from artists
>  
>  The sql may be a little off as I am typing this from scratch, but you
> get the
>  idea.
>  
>  now, if I do this:
>  
>  select * from VARTISTS where CNT=10
>  
>  it works.
>  
>  This, however, always returns an empty result set :
>  
>  select * from VARTISTS where CNT=:PARAM,
>  
>  for any value I bind to :PARAM.
>  
>  is this known/documented ?

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

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

[sqlite] Using Parameters with views

[Cariotoglou Mike]

I don't know if this has come up before, is so please point me to the
right direction :)

I believe that using parameterized queries with views does not work
as expected.

consider this (more or less self-explanatory) schema:

create table artists(artist_id)
create table songs(song_id)
create table song_artist(song_id,artist_id)

create view VARTISTS as
 select t1.*,
 (select count(*) from song_artist t2 where t1.artist_id=t2.artist_id)
as CNT
 from artists
 
 The sql may be a little off as I am typing this from scratch, but you
get the
 idea.
 
 now, if I do this:
 
 select * from VARTISTS where CNT=10
 
 it works.
 
 This, however, always returns an empty result set :
 
 select * from VARTISTS where CNT=:PARAM,
 
 for any value I bind to :PARAM.
 
 is this known/documented ?
 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] how to represent a tree in SQL

[Cariotoglou Mike]

just to throw in my two bits:

I have done a lot of work with trees in SQL, and IMHO, the best method BY FAR 
is the one described in the link below (mysql article), mainly due to its 
capability to handle siblings and descendants.
for example, the self-join, parent_node method described elsewhere in this list 
is failr ok for simple requirements, but is completely useles in the following 
cases:

select all_descendans_on_any depth for a particular node

find out if a node "belongs" to a parent which is not its immediate parent.

in other words, SET operations are quite difficult in the node-parent relation, 
but very easy and efficient in the adjacent list model.

I personally stopped looking for a better solution once I came across and 
comprehended the power of this method...

> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of 
> Sebastian Bermudez
> Sent: Wednesday, October 14, 2009 5:10 PM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] how to represent a tree in SQL
> 
> 
> 
> look this:
> 
> http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
> 
> is for mysql but aplies to every sql DB
> 
> 
> - Mensaje original 
> De: Michael Chen 
> Para: sqlite-users@sqlite.org
> Enviado: mié, octubre 14, 2009 10:40:45 AM
> Asunto: [sqlite] how to represent a tree in SQL
> 
> Dear there,
> 
> I am developing a numerical application, where a single 
> rooted dynamic tree is the main data structure. I intended to 
> use SQLite for this purpose and also for other data as well. 
> However I have no reference how to represent a tree using 
> tables. I need these functionalities:
> (1) basic tree: single root, multi-levels, arbitrary number 
> of branches, index each node, index each path (from root to a 
> leaf), lookup parent, lookup descendants
> (2) dynamics: delete a path, add a path; maintain parent and 
> descendants table; maintain history of tree; lookup history
> (3) each node has lots of  matrix and vectors, which will be 
> updated with dynamics, and should be tracked
> 
> As you see, it is nontrivial to write a tree structure to 
> support all these functions, while keep the code clean and 
> neat. That's why I want to use SQLite to keep things 
> straight. Is there a good reference on this?
> 
> Michael Chen
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
> 
> 
>   Yahoo! Cocina
> 
> Encontra las mejores recetas con Yahoo! Cocina.
> 
> 
> http://ar.mujer.yahoo.com/cocina/
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Question regarding BCC32

[Cariotoglou Mike]

Great. thanks for the info. however, this means that at least part of the 
runtime library actually comes from the VCL, and not the BCC32 libraries, is 
that not so ?
 
and, BTW, would you share your code to embed the .obj file ? I know I have done 
it in the past, and all it takes is to implement the imports, but I dont have 
that code around anymore, so I would be obliged...



From: sqlite-users-boun...@sqlite.org on behalf of John Elrick
Sent: Mon 12/10/2009 9:29 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Question regarding BCC32



Cariotoglou Mike wrote:
> Has anybody had experience using Borland's free compiler (bcc32) to
> build the sqlite dll ?

We use bcc32 to create an .obj which is linked into our Delphi
application.  No issues here and we've been using it for three years on
a project deployed to several hundred thousand people.


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


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

[sqlite] Question regarding BCC32

[Cariotoglou Mike]

Has anybody had experience using Borland's free compiler (bcc32) to
build the sqlite dll ?
I gave it a try, and was surprised to see that I got a file that is 100k
smaller than the usual MS visual studio dll I build.
I assume this is due to a different size of the run-time library, so
what I am asking is, has anybody used it before, and have you noticed
any issues with the resulting code ? it *seems* to run fine here,
but

my compile options were :

set cf=-jb -O2 -w- -K -DTHREADSAFE=1 -DNDEBUG -DNO_TCL -DWIN32 -DLIB
-DSQLITE_ENABLE_COLUMN_METADATA -DSQLITE_ENABLE_FTS3
-DSQLITE_ENABLE_FTS3_PARENTHESIS -DSQLITE_ENABLE_RTREE

bcc32 %cf% -c sqlite3.c

and link options:

ilink32 /Tpd /Gn /x C0D32.OBJ sqlite3.obj,sqlite3.dll,,CW32mt.LIB
IMPORT32.LIB,sqlite3.bcc.def,sqlite3.res

(I use separate link stage in order to link a version resource in, which
I do not know how to do otherwise)
(also, the .def file needs to be different than the usual MS file, due
to the habit of this compiler to generate underscores in public names)

Any response will be much appreciated


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

Re: [sqlite] Sqlite3Explorer Sqlite Report Designer

[Cariotoglou Mike]

No I am not in vaccation, I wish! I am rather busy this period, so no, I will 
not be able to do wthat you wanted, for the next couple of months or so...



From: sqlite-users-boun...@sqlite.org on behalf of Rajesh Nair
Sent: Thu 11/12/2008 8:55 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Sqlite3Explorer Sqlite Report Designer



Dear Cariotoglou Mike,
Are you in vaccation? I am attaching the previous emails that I have send.
If you are trying or planning to work on the same please inform, so that I
can hope that some thing is going to happen

-- Forwarded message --
From: Rajesh Nair <rajesh.avit...@gmail.com>
Date: Thu, Dec 4, 2008 at 8:13 PM
Subject: Re: [sqlite] Sqlite3Explorer Sqlite Report Designer
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>


Dear Cariotoglou Mike,

I am using VC++ for last 6 years to develop utilities and some small
projects. Formerly I was using MS-ACCESS and/or some database classes in my
projects. Once I felt the power of sqlite I started using it from 2003 /
2004. After that I have not used any ADO / ODBC for my databases. I have
felt the easyness of the moto "NO CONFIGURATION" of sqlite. I have developed
some very use full VC++ classes to handle the sqlite database file. I was
producing reports with my-own list-view control. Now some simple formated
reports are developed but if any changes in the report will result a total
EXE replace. That's why I am trying some external utility that could support
my EXE to produce simple formated reports.
I noticed that your DLL can open some FR3 or some FRF extention files.

THE NEED IS THAT REPORT FORMAT WILL BE SAVED AS AN EXTERNAL FILE OF THE
FORMAT THAT YOUR DLL CAN READ AND ALLIGN THE REPORT-VIEWER WINDOW FOR
PRINTING.

The dataset that you specified can be XML or sqlite database itself. If you
are planning to find some time to support sqlite ( as you found some time to
develop the Sqlite3Explorer ) then please keep some points in mind while
rebuilding the DLL.

   1) The dll can be used for both DESIGNING and PRE-VIEWING.
   2) The dll can be used to either DESIGNING or PRE-VIEWING
according to
   some parameters passed in to it. ( ie. If the HOST EXE needs
only pre-viewing the
   report then the DESIGNER window must not appear. The designer
mode will
   be shown on demand specified by a param passed to the DLL)

If I get such a DLL then I will be using the same for my further projects
and will also gradualy replace the method that I was using to show-report.
If some others those who are using sqlite with VC++ / VB or some other WIN32
platform programs may try to use it.
PLEASE INFORM

Thanks
Rajesh Nair


On Mon, Nov 17, 2008 at 5:18 AM, Cariotoglou Mike <[EMAIL PROTECTED]> wrote:

> here is an idea : I might be able to modify the dll to accept datasets in
a
> different, "standard" format, like XML.
> this would imply that your product would need to retrieve data in whatever
> format it needs, transform it to my format,
> pass it to the dll which will do the design and reporting. it will not as
> fast as it is now, but it is definately a solution. you would not have the
> source of the dll, but you could use it freely, no IR issues.
>
> for this to work , the following assumptions must prove correct:
>
> 1. I originally designed the DLL with portability in mind, so it *coul* be
> transformed as I described. since that time, otuehr developers have
improved
> it. I need to check that they have not introduced dependencies and design
> decisions that would nullify its portability.
>
> 2. that you (or somebody else) actually would benefit from this approach.
> f.e do you *like* the report designer (as seen and used from
> sqlite3Explorer), or is it just something you came across and thought to
> give a try?
>
> I only point this out, because it will take me at least 10-20 hours to do
> this conversion, and it would be a big waste of time (mine) to do this
just
> as an intellectual exercise. I am not asking for financial compensation
> here, just to know that I am actually doing something useful...
>
>
> PS another format that would work, besides XML, and that I already have a
> lot of code to support would be the ADO recordset. is this an option for
you
> ? what language are you planning to use in order to host the DLL, and what
> database ?
>
>
> 
>
> From: [EMAIL PROTECTED] on behalf of Rajesh Nair
> Sent: Sat 15/11/2008 5:53 AM
> To: General Discussion of SQLite Database
>  Subject: Re: [sqlite] Sqlite3Explorer Sqlite Report Designer
>
>
>
> Thanks
>
> So I can't use it... Ok ... But I got some source code of  FastReport (
> some
> ealier version ) from the internet and it is in 

Re: [sqlite] Sqlite3Explorer Sqlite Report Designer

[Cariotoglou Mike]

here is an idea : I might be able to modify the dll to accept datasets in a 
different, "standard" format, like XML.
this would imply that your product would need to retrieve data in whatever 
format it needs, transform it to my format,
pass it to the dll which will do the design and reporting. it will not as fast 
as it is now, but it is definately a solution. you would not have the source of 
the dll, but you could use it freely, no IR issues.
 
for this to work , the following assumptions must prove correct:
 
1. I originally designed the DLL with portability in mind, so it *coul* be 
transformed as I described. since that time, otuehr developers have improved 
it. I need to check that they have not introduced dependencies and design 
decisions that would nullify its portability.
 
2. that you (or somebody else) actually would benefit from this approach. f.e 
do you *like* the report designer (as seen and used from sqlite3Explorer), or 
is it just something you came across and thought to give a try?
 
I only point this out, because it will take me at least 10-20 hours to do this 
conversion, and it would be a big waste of time (mine) to do this just as an 
intellectual exercise. I am not asking for financial compensation here, just to 
know that I am actually doing something useful... 
 
 
PS another format that would work, besides XML, and that I already have a lot 
of code to support would be the ADO recordset. is this an option for you ? what 
language are you planning to use in order to host the DLL, and what database ?

 


From: [EMAIL PROTECTED] on behalf of Rajesh Nair
Sent: Sat 15/11/2008 5:53 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Sqlite3Explorer Sqlite Report Designer



Thanks

So I can't use it... Ok ... But I got some source code of  FastReport ( some
ealier version ) from the internet and it is in Dephi/Pascal. I don't know
both of the tools. Can you use the same code to build a dll which can be
used with other windows programing languages? ( So that no one can raise
their finger aganist me or YOU ).

I don't want a very efficient-high-end report designer like FastReport. My
clients require very simple reports, which does not contain much caculations
etc If you can help me  please. It won't be just for me. Those who are
using SqLite with windows will be happy with a simple report deisgner and
viewer. Also my company is not willing to puchase any product for just
reporting, since our programs are supplied freely along with some of our
products. So an extra cost is not feasible.

A SIMPLE DISIGNER/VIEWER FOR WINDOWS USERS


- Original Message -----
From: "Cariotoglou Mike" <[EMAIL PROTECTED]>
To: "General Discussion of SQLite Database" <sqlite-users@sqlite.org>
Sent: Friday, November 14, 2008 7:14 PM
Subject: Re: [sqlite] Sqlite3Explorer Sqlite Report Designer


>I am the author of sqlite3Explorer. Sorry I did not answer you previous
> post, reason is I did not motice it... :(
>
> I don't think what you want is feasible. the reason is this: the Dll
> expects a "dataset" which sqlite3Explorer builds internally, and the
> report engine takes it from there (Design, run, export etc). the dll
> itself does not contain data retrieval code, and relies on the host for
> this.
>
> this is ok, but the catch is here: the dataset format that the dll
> understands is a proprietary format developed by and only used by the
> company I work for. the dll engine is one the prime design/execute
> engines for our commercial projects.
> thus I cannot open-source it. and, since the dataset format in question
> cannot be produced without having access  to the proprietary libraries
> we use here, what you want cannot be done.
>
> sorry...
>
>> -Original Message-
>> From: [EMAIL PROTECTED]
>> [mailto:[EMAIL PROTECTED] On Behalf Of Rajesh Nair
>> Sent: Friday, November 14, 2008 2:12 PM
>> To: General Discussion of SQLite Database
>> Subject: [sqlite] Sqlite3Explorer Sqlite Report Designer
>>
>> Hi all
>>
>> How can I use the mkFrxEngine.DLL supplied along with the
>> Sqlite3Explorer.
>> The dll is a report designer and viewer. Please help me to
>> use that dll if any one knows. I asked the same before 10-14
>> days ago, but didn't get any reply. If even the author of the
>> same is reading this please help me. I want to use it as a
>> report viewer for my sqlite3 database.
>>
>>
>> --
>> Regards
>> Rajesh Nair
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sql

Re: [sqlite] Sqlite3Explorer Sqlite Report Designer

[Cariotoglou Mike]

I am the author of sqlite3Explorer. Sorry I did not answer you previous
post, reason is I did not motice it... :(

I don't think what you want is feasible. the reason is this: the Dll
expects a "dataset" which sqlite3Explorer builds internally, and the
report engine takes it from there (Design, run, export etc). the dll
itself does not contain data retrieval code, and relies on the host for
this. 

this is ok, but the catch is here: the dataset format that the dll
understands is a proprietary format developed by and only used by the
company I work for. the dll engine is one the prime design/execute
engines for our commercial projects.
thus I cannot open-source it. and, since the dataset format in question
cannot be produced without having access  to the proprietary libraries
we use here, what you want cannot be done.

sorry...

> -Original Message-
> From: [EMAIL PROTECTED] 
> [mailto:[EMAIL PROTECTED] On Behalf Of Rajesh Nair
> Sent: Friday, November 14, 2008 2:12 PM
> To: General Discussion of SQLite Database
> Subject: [sqlite] Sqlite3Explorer Sqlite Report Designer
> 
> Hi all
> 
> How can I use the mkFrxEngine.DLL supplied along with the 
> Sqlite3Explorer.
> The dll is a report designer and viewer. Please help me to 
> use that dll if any one knows. I asked the same before 10-14 
> days ago, but didn't get any reply. If even the author of the 
> same is reading this please help me. I want to use it as a 
> report viewer for my sqlite3 database.
> 
> 
> --
> Regards
> Rajesh Nair
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite version 3.6.4 planned for 2008-10-15

[Cariotoglou Mike]

well, since you asked, BNF is indeed more difficult to read, BUT it is
machine-readable, which means validation tools and parsers can be built.
so, if you did have the BNF grammar *available* (as opposed to part of
the web documentation), I personally would be happier.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] request for information

[Cariotoglou Mike]

Hi all, and DRH :

I am the author of sqlite3Explorer, a windows Gui management tool. One
of the users of this program has contacted me wrt to supporting
encrypted databases. in the past, Sqlite3Explorer has supported
encrypted databases (version 2 of sqlite), even though I do not own a
copy of the encrypted source
(see), because (in the past, at least), it was not necessary, as long as
the sqlite3.dll was compiled with SEE enabled. I just made the proper
calls to sqlite3_key, and it worked.

I am now told that this does not work anymore, and I assume that
something has changed between version 2 and version 3 of sqlite, which
breaks my implementation.  It fails with an error 21 (Improper use of
API).
I would like to fix this, but I am unable to do so, unless I have access
to (some part) of the commercial version, and an understanding of the
encryption initialization (key format, how to select which of the 4
encryption methods is used, etc).

This request is aimed mainly towards DRH :

is it possible to send me the documentation (as a minimum) of the SEE
extension (and CEROD, if relevant), so that I can implement the corect
calling sequence to open such databases ? The alternative would be for
me to buy the source for these, which is a little over my budget for now
:)

I would like to assure you that I personally have no use for encrypted
databases, I am only asking for this in order to support people that use
my (freeware) utility...

of course, if you are feeling generous, you could also consider giving
me a free copy :), since I *do* contribute to sqlite, at least
indirectly.

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

RE: [sqlite] Casting bug

[Cariotoglou Mike]

well, first of all you must have a typo, since the sql you show will
return 14170, not 0.69 or anything like it.
however, one thing springs out:
 
 Total * 100 + 100 is wrong IMHO, unless you are looking for CEILING
functionallity. 
"round" would need : total * 100 +50 (which rounds to nearest integer at
two decimal points, not to the nearest LARGER integer,
which is what your sample does)


> -Original Message-
> From: T [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, December 13, 2007 4:27 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Casting bug
> 
> When I try:
> 
> select cast( 141.70 * 100 as integer)
> 
> I get 0.69, but should get 0.70
> 
> What's the problem? Seems like a bug.
> 
> I tried some other numbers in place of 141.70, and they 
> worked OK, though I imagine there are others that have the 
> bug that I just haven't tried.
> 
> The above is the isolated buggy part of a formula I use to 
> round of real amounts to two fixed decimal places:
> 
> select
>   substr(
>   '  $' || cast( Total as integer ) || '.'
|| substr( cast( Total * 100 + 100 as 
> integer ), -2, 2 )
>   , -10, 10
>   )
> from ( select 141.70 as Total )
> ;
> 
> which gives $141.69 but should give $141.70
> 
> Is there a more reliable way, using only SQLite?
> 
> Thanks,
> Tom
> 
> 
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
> 
> 
> 


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

RE: [sqlite] Sqlite3Explorer Version 3.0 is available

[Cariotoglou Mike]

will do asap 

> -Original Message-
> From: Miha Vrhovnik [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, November 29, 2007 5:15 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Sqlite3Explorer Version 3.0 is available
> 
> Hi Mike,
> 
> Thanks for updated version, but still no drag n drop support 
> for opening database files :( ....
> 
> Regards,
> Miha
> 
> "Cariotoglou Mike" <[EMAIL PROTECTED]> wrote on 29.11.2007 9:09:59:
> >New version with a lot of enhancements to the user interface, also 
> >updated to be compatible with the latest sqlite dll.
> >
> >information, change list and download here :
> >
> >http://www.singular.gr/sqlite
> >
> >(New features are described in the link near the top).
> >
> >Documentation, such as it is,  is out of date :)
> >
> >
> >-
> --
> >-- To unsubscribe, send email to 
> >[EMAIL PROTECTED]
> >-
> --
> >--
> >
> >
> 
> 
> --
> It's time to get rid of your current e-mail client ...
> ... and start using si.Mail.
> 
> It's small & free. ( http://simail.sourceforge.net/ )
> 
> --
> ---
> To unsubscribe, send email to [EMAIL PROTECTED]
> --
> ---
> 
> 
> 


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

[sqlite] Sqlite3Explorer Version 3.0 is available

[Cariotoglou Mike]

New version with a lot of enhancements to the user interface, also
updated to be compatible with
the latest sqlite dll.

information, change list and download here :

http://www.singular.gr/sqlite

(New features are described in the link near the top).

Documentation, such as it is,  is out of date :)


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

[sqlite] sqlite icon

[Cariotoglou Mike]

DRH : do you mind if I usurp the favicon.ico from the sqlite site, to
use in my application ?
thanks, mike


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

RE: [sqlite] sqlite3Explorer

[Cariotoglou Mike]

maybe... otoh, much of the thing's perceived value comes from things like the 
datagrid functionality. this is DevExpress, and somehow I doubt they have a 
version for lazarus. still, might have a look...



From: John Elrick [mailto:[EMAIL PROTECTED]
Sent: Mon 11/26/2007 1:56 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] sqlite3Explorer



Joe Wilson wrote:
> --- Cariotoglou Mike <[EMAIL PROTECTED]> wrote:
>  
>> I wish I could "make it for Unix", but it uses a lot of windows-specific 
>> things, plus it is done
>> in Delphi, and since
>> Kylix is practically dead, wlll...
>>
>
> I didn't realize it was written in Delphi.
> Yes, that would be a difficult port to UNIX - more like a complete rewite.
>  

Mike,

It "might" be worth looking at Lazarus.  Depending on the VCL components
you are using, the port might be easier than you think...albeit
non-trivial


John

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





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

RE: [sqlite] [OT] "encrypted" e-mail address (was Re: [sqlite] sqlite3Explorer)

[Cariotoglou Mike]

no, a mistake. the address IS :
mikecar at singular dot gr (mike will also work)
thanks for pointing out the header problem. I was hoping the mailing list was 
"hiding" this info, obviously not

An unencrypted  address was plainly visible in the message headers
("From" and "X-Return-Path"). Following the decryption instructions
produces a result which has no "@" character in it; where the header
address has "@", the decrypted version has "car&.". Is this an elaborate
joke, or what?



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

RE: [sqlite] sqlite3Explorer

[Cariotoglou Mike]

ooops mail obfuscation was wrong! it is actually :
 
mike atsign singular dot gr (I used ampersand in the original post which was 
wrong. well, english is not my native lang...)

 

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

RE: [sqlite] sqlite3Explorer

[Cariotoglou Mike]

you can't ?! I overdid it, then :)
I wish I could "make it for Unix", but it uses a lot of windows-specific 
things, plus it is done in Delphi, and since
Kylix is practically dead, wlll...



From: Joe Wilson [mailto:[EMAIL PROTECTED]
Sent: Sat 11/24/2007 7:00 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] sqlite3Explorer



> the address follows, obfuscated for the usual reasons. to e-mail me, remove 
> any
> numeric digits and punctuation from the address that follows, and do the 
> obvious substitutions.
> hopefully spammers will not...
>
> m6_i_ke$car(ampersand)(dot)s#i#n#g#u#l#a#r(dot)gr

Nevermind the spammers - I can't break that encryption.

Nice software, that sqlite3Explorer, by the way.
Do you plan to make a version for UNIX?




  

Never miss a thing.  Make Yahoo your home page.
http://www.yahoo.com/r/hs

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





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

[sqlite] sqlite3Explorer

[Cariotoglou Mike]

Hi. I am the author of the Sqlite3Explorer GUI front end. I have been inactive 
on this tool for some time now,

frankly because I initially created it to cover my own needs, and I havent had 
any new needs lately :)

In any case, since I see that people still use it, and some even like it, I am 
willing to put some effort to bring it up to speed. So, I would like the 
community to make suggestions as to how I can improve the utility. I am not 
promising I will implement every suggestion, of course.

In order to reduce clutter in the mailing list, I suggest that you send me your 
ideas to my personal e-mail. the address follows, obfuscated for the usual 
reasons. to e-mail me, remove any numeric digits and punctuation from the 
address that follows, and do the obvious substitutions. hopefully spammers will 
not...

m6_i_ke$car(ampersand)(dot)s#i#n#g#u#l#a#r(dot)gr


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

[sqlite] PROBLEMS BUILDING 3.4.2 using Ms Visual Studio 2005

[Cariotoglou Mike]

I am having problems building 3.4.2 from the amalgamated source,using
Microsoft Visual Studio 2005

 (this is the first time I am trying to use
the amalgamated source). I get the following errors:

Error   1   error C2133: 'sqlite3UpperToLower' : unknown size
Error   37  error C2133: 'sqlite3OpcodeNames' : unknown size
Error   184 error C2133: 'sqlite3IsIdChar' : unknown size

strangely, I can build fine When using the separate source files. I do
not know enough C/C++ to understand
why the error occurs. I suspect it has to do with order of declarations
in the source file.


Note that all three errors have to do with internally declared arrays.
in the separate source, they are declared
as "extern", but in the amalgamated source they become "static".

can somebody help please ?


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

[sqlite] Unique Index not working properly

[Cariotoglou Mike]

> > Seems that there is a problem on unique key fields when null
> > values are allowed
> >
> > CREATE TABLE z (
> >   id VARCHAR(32) NOT NULL,
> >   f1 VARCHAR(32) NOT NULL,
> >   f2 VARCHAR(20),
> >   PRIMARY KEY (id)
> > );
> > CREATE UNIQUE INDEX z_I1 ON z (f1, f2)
> >
> > insert into z values ('1', '1', null);
> > insert into z values ('2', '1', null);
> >
this should give a unique constraint error, but does not.

testing with sql server and oracle shows that they *will* give an error. in 
this case, NULL = NULL seems to be true

can this be fixed ? it is quite a deviation from standard behavior.

 


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

RE: [sqlite] Singular Sqlite3Explorer 2.0 question

[Cariotoglou Mike]

suppose you have a text file that contains :
 
Myname,yourname hisname
Myname,yourname hisname
Myname,yourname hisname
Myname,yourname hisname
Myname,yourname hisname
Myname,yourname hisname
Myname,yourname hisname

 
notice that this is an irregular format, because the first column
delimiter is a comma, and the second is a space. this kind of file
cannot be parsed easily using "standard" formats like CSV. A regular
expression allows you to break down the lines into columns in an ad hoc
fashion. the expression :
 
(\w+),(\w+) (\w+)
 
will break lines into three columns, for example, based on a comma and
space delimiter. the parentheses are essential, to provide subgroups
that map to the columns. try the example above, it should become
clearer.
if you do not now how to use Perl regular expressions, you need to do
some background reading first.
 
regards,mike
 



From: Noah Hart [mailto:[EMAIL PROTECTED]
Sent: Tue 27/2/2007 6:59 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] Singular Sqlite3Explorer 2.0 question



Has anyone used this program?

I am trying to use the "Import using Regular Expression" option.
Itis looking for some type of regular expression to parse the columns.

Any suggestions or sample expressions you can offer?

Regards,

Noah Hart



CONFIDENTIALITY NOTICE:
This message may contain confidential and/or privileged information. If
you are not the addressee or authorized to receive this for the
addressee, you must not use, copy, disclose, or take any action based on
this message or any information herein. If you have received this
message in error, please advise the sender immediately by reply e-mail
and delete this message. Thank you for your cooperation.





-
To unsubscribe, send email to [EMAIL PROTECTED]

-





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

RE: [sqlite] weird (and dangerous) bug in Microsoft Compiler

[Cariotoglou Mike]

Robert, I re-verified using your option set, and I still get the same problem. 
there is clearly something wrong here,
and it is not easy to track down. I wonder if you could help me with this. 
Shall we take this off-line, so that we dont clutter the list, and report back 
when done ? I would really appreciate your help.
since your e-mail is not visible, I cannot contact you directly. would you drop 
me a line
at 
mikecarSingular.gr
 
btw, I noticed something in your configuration : the option THREADSAFE is set, 
but NOT set to 1
AFAIK, the source uses this :
#if defined(THREADSAFE) && THREADSAFE

which probably means you are compiling with no thread safety (does not make any 
difference for my issue,
but you may want to know)
 



From: Robert Simpson [mailto:[EMAIL PROTECTED]
Sent: Wed 08-Nov-06 6:27 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] weird (and dangerous) bug in Microsoft Compiler



I compiled everything from bare source myself.  I maintain the ADO.NET 2.0
wrapper.

> -Original Message-
> From: mike cariotoglou [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, November 08, 2006 9:18 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] weird (and dangerous) bug in Microsoft Compiler
>
> did you also compile the ado .net wrapper ?
>
> - Original Message -
> From: "Robert Simpson" <[EMAIL PROTECTED]>
> To: 
> Sent: Wednesday, November 08, 2006 4:57 PM
> Subject: RE: [sqlite] weird (and dangerous) bug in Microsoft Compiler
>
>
> >I tried that same SELECT statement in 3 builds of SQLite on
> VS2005.  The
> > code was called from C# using the ADO.NET 2.0 wrapper, and
> I got 10 all 3
> > times.  I tried /fp:fast, /fp:strict and /fp:precise
> >
> > The rest of the build options were:
> >
> >AdditionalOptions="/GS-"
> >Optimization="2"
> >FavorSizeOrSpeed="1"
> >
> >
> PreprocessorDefinitions="WIN32;NDEBUG;_WINDOWS;_USRDLL;_CRT_SE
> CURE_NO_DEPREC
> >
> ATE;NO_TCL;THREADSAFE;SQLITE_HAS_CODEC;SQLITE_ENABLE_COLUMN_ME
> TADATA;SQLITE_
> > ENABLE_FTS1"
> >StringPooling="true"
> >ExceptionHandling="0"
> >BufferSecurityCheck="false"
> >EnableFunctionLevelLinking="true"
> >RuntimeTypeInfo="false"
> >
> >
> >> -Original Message-
> >> From: mike cariotoglou [mailto:[EMAIL PROTECTED]
> >> Sent: Wednesday, November 08, 2006 5:27 AM
> >> To: sqlite-users@sqlite.org
> >> Subject: [sqlite] weird (and dangerous) bug in Microsoft Compiler
> >>
> >> hello to all. I wish to report some quirks I discovered with
> >> floating point
> >> and
> >> ROUND() function, while looking into a problem reported by my
> >> development
> >> team wrt
> >> to sqlite handling of above function.
> >>
> >> first of all, let me state that I understand the issues
> with inexact
> >> floating point
> >> representations, so let us not go into discussions about
> which is the
> >> "correct"
> >> interpretation of 9.95. however, a given implementation
> >> should at least be
> >> consistent
> >> wrt to this.
> >>
> >> my tests have shown that, for the following statement:
> >>
> >>  select ROUND(9.95,1)
> >>
> >> the command-line sqlite3.exe (v 3.3.8) returns 10.0
> >> OTOH, the compiled DLL that can be downloaded from the sqlite
> >> site returns
> >> 9.9 !
> >> (as a reference, both MS SQL and ORACLE return 10.0)
> >>
> >> What gives ? is the result dependent on compilation options,
> >> and if so,
> >> which ?
> >>
> >> trying to investigate this issue, I compiled the dll locally
> >> (3.3.8), using
> >> Microsoft
> >> Visual Studio 2005, and came across a beauty :
> >>
> >> the dll compiled with MSVC, using default options more or
> >> less, gives 0.0
> >> (yes, zero)
> >>
> >> I pulled my hair out over this for some hours, and
> discovered that :
> >>
> >> a. the floating point optimizer in MSVC has a bug, which is
> >> triggered when
> >> you use the
> >> optimization setting /fp:precice (which is the default), and
> >> gives the above
> >> erroneous
> >> result.
> >>
> >> b. you can get the correct behavior by speifying optimization
> >> as /fp:strict
> >>
> >> Clearly, this is a problem with the microsoft compiler.
> >> however, trying to
> >> avoid future
> >> issues, I suggest that somebody which is conversant in C
> >> (which is not me),
> >> try to find
> >> the sqlite3 source construct that triggers this bug, and
> >> re-writes the code
> >> so that it
> >> is not optimization-sensitive. I tracked the problem down
> >> somewhere in the
> >> vxprintf function
> >> in the print.c source file. It is quite difficult to pin the
> >> problem down,
> >> because:
> >>
> >> the problem goes away when you build in debug mode, because
> >> optimizations
> >> are turned off.
> >> Even if you force some 

RE: [sqlite] Stored procedures in triggers

[Cariotoglou Mike]

> 
> Thoughts?  Would making recursive triggers an error rather 
> than just silently ignoring them break anybody's code?

even if it does, it should. otherwise, people may assume that the
functionality exists,and rely on it.
 
> I'm also looking at making DELETE triggers recursive.  I can 
> do that because recursive DELETE triggers are guaranteed to 
> terminate (you will eventually run out of rows to delete.)  
> But INSERT or UPDATE triggers might go on forever.  There are 
> also technical issues that make recursive INSERT and UPDATE 
> triggers more difficult so that I would prefer to delay 
> implementing them.
> 
> Comments?  Would it be useful to have recursive DELETE 
> triggers even without recursive INSERT or UPDATE triggers?
not much IMHO

RE: [sqlite] Triggers and TEMP tables: ticket #1689

[Cariotoglou Mike]

I am not sure how this would work. in order for a trigger to "See"
another database, somebody (obviously not the trigger) must ATTACH the
database first. is there an automatic ATTACH capability that I am not
aware of ? if not, specifying triggers than span databases is looking
for trouble IMHO. as for allowing it, well, guns are allowed also, so
you can shoot yourself in the foot, if you really want to. 

> -Original Message-
> From: Brad [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, February 28, 2006 3:28 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Triggers and TEMP tables: ticket #1689
> 
> > Should I remove the tests from SQLite that prevent triggers in one 
> > database from referring to tables in a different database?
> 
> Well, I have a couple of triggers in my SQL Server database 
> that refer to tables in another database, though they are on 
> the same server. 
> Obviously, I can see some utility in that.  I will admit, 
> however, that if I had to implement the system that uses that 
> scheme in SQLite, then I'd probably have the tables in the 
> same database file, rather than different ones.
> 
> 
> 
> 

RE: [sqlite] SQLite acces from vbScript

[Cariotoglou Mike]

all you need is an OLEDB or ODBC wrapper for sqlite, and there are some around, 
check the wiki.
once you install one, your sqlite is accesible from ADO, just like any other 
database (almost)



From: John Latimer [mailto:[EMAIL PROTECTED]
Sent: Mon 30-Jan-06 12:06 AM
To: sqlite-users@sqlite.org
Subject: [sqlite] SQLite acces from vbScript



I am programming in vbScript within the framework of a larger program on a PC 
platform and need to have access to SQLite for my database needs.
  
  I am an inexperienced programmer, so I wouldn't be surprised if the answer is 
very obvious to you , but oblivious to me...
  
  I require use of an SQLite database with full database query functionallity 
using vbScript.
  
  John L>




John A. Latimer



The Hologenic Man





Discovery: The more I learn, the more I learn how little I know...


Goal: There's strength in simplicity.

Note: Goal not always achieved.

RE: [sqlite] implementing editable result sets

[Cariotoglou Mike]

one idea. run an EXPLAIN fist, and then analyze the query plan. it will
tell you if there are more than one tables,
and maybe you can get info about aggregate functions and such. of
course, there is a cost to this...

> -Original Message-
> From: Will Leshner [mailto:[EMAIL PROTECTED] 
> Sent: Monday, December 19, 2005 11:12 PM
> To: Forum SQLite
> Subject: [sqlite] implementing editable result sets
> 
> Hi. I apologize in advance for the length of this question, 
> but it is a little involved.
> 
> I am the author of a wrapper for SQLite and in that wrapper 
> there is an object called a RecordSet that represents the 
> results of a query.  
> One of the things you can do with a RecordSet is edit 
> records. The way I've implemented editing a RecordSet is to 
> construct an UPDATE statement based on the new values for the 

RE: [sqlite] Problem with floating point fields, and a feature request

[Cariotoglou Mike]

I see again that you all miss the point. I DO know how to handle floating 
point. My point is :
 
a. a lot of people will make the error indicated. I am sure that they are poor 
programmers. I am also sure (judging from some of the questions posted in this 
list), that there is a lot of them...
 
b. some databases (ORACLE is the only one that comes to mind) support this 
properly, by allowing for fixed point types.
so, a type declared as NUMERIC(10,3), which is ANSI-92, will be handled 
properly in comparisons. Most other engines will use floating point only, and 
will fail.So, to the question : " should all databases implement your fix" the 
answer is YES if they need it.
 
c. the REAL danger with wrappers is this:
 
you are thinking of wrappers that hide the SQLITE api. Ok, I agree that those 
do not need to handle the problem, as the programmer can do it themselves. but 
consider:
 
a lot of people out there, I suspect, use SQLITE through a higher-level API. 
take a look at the ODBC, OLEDB and .NET providers. They allow point-and-click 
programmers (yes, mum, they do exist, they are probably idiots, but they 
outnumber us "real" programmers by a factor of 10:1 at least) to use niceties 
as data binding, data-aware grids and so on. Now all these data providers do 
not just hide the API, they also add functionality. One very important one, is 
, to generate SQL to update the database when a data-aware control changes. The 
generated SQL is never under the control of the programmer, so he cannot do 
anything about floating point comparisons or anything else. In particular, 
generated UPDATE statements (and DELETE, for that matter), will add a WHERE 
clause, where ALL fields will be compared to their "original" value. why is 
this ? In order to achieve optimistic row locking, in other words to detect 
changes by other users in a multi-user environment. now THIS is the situation I 
am talking about, and there is simply no fix for this problem, unless the db 
engine itself can handle it.
 
Of course, one can avoid the problem by not using floats, which also implies 
not using DATES since a lot of programming environments and DB apis use floats 
for dates (OLEDB does, Delphi does, Visual Basic does etc etc). But this will 
seriously reduce the usability of SQLITE with RAD environments, which try to 
hide the DB access from the programmer. 
 
Again, I am not advocating that point-and-click programming is good, or that 
RAD environments are good, or that data-aware controls are a cool idea. I am 
aware of the issues with all these, having used them for decades. What I am 
saying is that there a lot of poor sods out there that dont know any better, so 
they DO use these things, and they are in for a big surprise...
 
since a solution to this issue is fairly simple, and the applicable audience is 
large, why not provide one? 
the fact that MSSQL will not be able to do the same is not an argument that has 
stopped drh before, has it.
 
 
 
 
 
 
 
 



From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Wed 14-Dec-05 7:35 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Problem with floating point fields, and a feature request



Dave Dyer wrote:
>>>select * from test where f=13.06; -- returns no data
>
>
> Pardon me for throwing a bomb, but no good programmer
> would ever use = to compare floating point numbers.
>
> Choose a more appropriate representation for your data.
>
It is not a bomb, just something novice programmers have to learn.
There is no = in floating point, it is only possible with integers.

RE: [sqlite] Problem with floating point fields, and a feature request

[Cariotoglou Mike]

as you may remember, some time ago I raised an issue with floating point
accuracy, and how this may affect sqlite.

I now have a concrete example, which actually happened in an
installation, and helps to demonstrate the severity of the issue:

try this code:

create table test(f double);
insert into test values(13.04);
update test set f=f+0.02;
select * from test where f=13.06; -- returns no data

can you imagine how many bugs waiting to happen are out there, because
of code like this ?

I know that there are a number of solutions to this problem, all
involving changing the sql involved. 
however, these are not applicable to people using SQLITE via wrappers
that generate their own UUPDATE code.

what I have been trying to say is that, there is also a neat solution to
the problem, and one that can be implemented easily :

pragma floating_accuracy=0.001

setting the threshold for float comparisons to some predictable value.

Am I the only one that sees the problem? if not, please speak up, and
maybe we can get a neat solution!

RE: [sqlite] how can I import CSV file into SQLite quickly

[Cariotoglou Mike]

sqlite3Explorer does that



From: John Stanton [mailto:[EMAIL PROTECTED]
Sent: Wed 07-Dec-05 8:00 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] how can I import CSV file into SQLite quickly



Someone somwhere must have a simple Perl script which does what you want.
JS

Robert L Cochran wrote:
> I create an SQL file that has contents like this:
>
> [EMAIL PROTECTED] elections]$ cat insert_precinct.sql
> BEGIN TRANSACTION;
> INSERT INTO "precinct" VALUES(1, 'Community Center 15 Crescent Road', 3,
> 'Greenbelt', 'Maryland', 0);
> INSERT INTO "precinct" VALUES(2, 'Police Station 550 Crescent Road', 6,
> 'Greenbelt', 'Maryland', 0);
> INSERT INTO "precinct" VALUES(3, 'Springhill Lake Recreation Center 6111
> Cherrywood Lane', 8, 'Greenbelt', 'Maryland', 0);
> COMMIT;
>
> Then I fire up sqlite3 on the command line, and issue
>
> .read insert_precinct.sql
>
> I realize this will probably make you unhappy because it means editing
> your CSV file so that each line is transformed into an sql statement.
> This can be done most easily with sed (if you are a Linux or Unix
> person), but you need to know sed commands and you need to be willing to
> patiently experiment until the sed script applies exactly the right edits.
> Bob Cochran
>
>
> ronggui wong wrote:
>
>> I have a very large CSV file with 1 rows and 100 columns.and the
>> file looks like the following:
>> "a","b","c","d",
>> "1","2","1","3" ,
>> "3","2","2","1",
>> ..
>>
>> If I use .import,It seems I have to set the variable names manually .
>> Is there any way to import the whole data file into SQLite quickly?
>> Thank you!
>>
>> ronggui
>>
>>
>> 
>>
>

[sqlite] Feature request

[Cariotoglou Mike]

Hi all. I would like to propose an enchancement in sqlite. I am not sure 
whether the issue
has come up before, if it has, and has been shot down, pls let me know.

The concept of in-memory database would be greatly enhanced if there was a 
mechanism to
serialize the internal cache to/from a stream (not necessarily a disk stream). 
In-memory
databases are extremely useful for a number of applications, but ar a PITA to 
initalize/persist.

one might argue why such a mechanism would be useful. If I need a persistent 
database, you could
say, then I would make it a disk-based one. Not so. Consider the case where you 
would like to
store the contents of an entire "database" as part of another storage structure.
consider the case where an entire sqlite database was a field in another RDBMS. 
consider
OO databases. Consider case tool implementation. The applications would be 
endless.

I took a look at the ":memory:" implementation, and it seems to me that this 
enchancement
would be trivial, consisting of maybe 10-20 c code lines, for somebody who 
understands the
internal structures involved. unfortunately, I do not do C, so I cannot do this 
myself.
OTOH, given the extreme usefuleness of what I propose (IMHO), could you 
consider this as a
permament addition in sqlite?

the design I have in mind would be something like this:

int sqlite3_loadMemDb(sqlite3 * db,reader)
int sqlite3_saveMemDb(sqlite3 * db,writer)

where "reader" and "writer" are function pointers with a signature like :
 int reader(void mem, int size)

(excuse my attempt at C syntax, this is meant only as a tip)

I suspect that the "load" function might need to know the number of pages 
beforehand,
so some kind of overloaded definiton of "reader" would be required, that would 
return this
information. if we agree that the idea has merit, the details can be worked out 
easily.

what does the community, and especially DRH, think about this ?

RE: [sqlite] Request for comment: Proposed SQLite API changes

[Cariotoglou Mike]

Clay, I like stick-shifts, I drive motorcycles *and* stick-shifts, and I
was about to buy a Z4, but the wife stopped me (judging, correctly, that
it is a girl-trap). 
I even write assembly now and then, when absolutely needed. I cant say
it is fun, though. 
the thing is, I never have seen a single task implemented in C (not C++
mind you), that can not be implemented in Delphi, with the SAME or
better performance,
in a way that is 10 times more elegant, much better for maintainance and
clarity, and less effort. afaik, the only down-side is that you don't
get the delphi compiler in anything else than windows (ok, and linux).
other than that, I see absolutely no advantage in the C language, and
certainly no "fun" in it... for me, it is as fun as trying to start a
fire by rubbing two sticks together. it can be done, and people have
been doing it for thousands of years. but that was only because they did
not have matches, not because they liked it.
so, assuming that you had an efficient portable implementation of object
pascal, do you really think *new* developers would opt to use C anyway ?

> -Original Message-
> From: Clay Dowling [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, November 03, 2005 7:53 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Request for comment: Proposed SQLite API changes
> 
> 
> Cariotoglou Mike said:
> 
> > a crappy .H file. these people (c programmers) live in wasteland, I 
> > really admire them for the constructive way they use header files, 
> > include files, defines,  make files, configure files and 
> what not,and 
> > still manage to write code that is write-once, compile everywhere. 
> > this is something not easily achieved, given the tools they have to 
> > work with. otoh, I live in delphi land from day 1, and I 
> *know* what 
> > they are missing...
> 
> As somebody who lives in both worlds, C land isn't such a bad 
> place.  It's a little like the difference between driving a 
> nice comfortable Ford Taurus (Delphi) and a BMW Z3 with a 
> stick shift (if you haven't done it,
> do: you probably never realized that driving could be so 
> fun).  I like driving both.  But there are certain situations 
> where one is a lot better than the other, as I'm sure you're aware.
> 
> Clay Dowling
> --
> Simple Content Management
> http://www.ceamus.com
> 
> 
> 
> 

RE: [sqlite] Request for comment: Proposed SQLite API changes

[Cariotoglou Mike]

since you work in D7, as I do, you already have namespaces (in the form
of units), so this was never an issue, even if you wanted to have two
versions of the same code built-in. as to why, well, consider a database

managent tool that has to open both 2.x and 3.x databases, and the only
tool to structure your code and isolate interface from implementation is
a crappy .H file. these people (c programmers) live in wasteland, I
really admire them for the constructive way they use header files,
include files, defines,  make files, configure files and what not,and
still manage to write code that is write-once, compile everywhere. this
is something not easily achieved, given the tools they have to work
with. otoh, I live in delphi land from day 1, and I *know* what they are
missing...

> -Original Message-
> From: Fred Williams [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, November 03, 2005 5:10 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Request for comment: Proposed SQLite API changes
> 
> Thanks.  I guess I never considered using two different 
> releases of any product within the same executable.  Wonder 
> how many use this feature and why?
> 
> > -Original Message-
> > From: Joe Wilson [mailto:[EMAIL PROTECTED]
> > Sent: Thursday, November 03, 2005 8:54 AM
> > To: sqlite-users@sqlite.org
> > Subject: RE: [sqlite] Request for comment: Proposed SQLite 
> API changes
> >
> >
> > It's a primitive form of namespaces in C.
> > Renaming the function calls allows Sqlite2 and Sqlite3 to coexist 
> > within the same executable/binary.
> >
> > Mind you, if the functionality of a documented function changes (as 
> > opposed to merely extended) I would think it would warrent a major 
> > revision number increase.  Sqlite versions 3.1.0 and 3.2.0 did not 
> > change as radically as is planned for this upcoming release.
> >
> > --- Fred Williams <[EMAIL PROTECTED]> wrote:
> >
> > > Just curious, why is this being done anyway?  I think this
> > is the only
> > > software product I have used that has this "feature."  I
> > fail to see the
> > > usefulness from way up here above the source code, and I
> ...
> 
> 
> 
> 

RE: [sqlite] Request for comment: Proposed SQLite API changes

[Cariotoglou Mike]

ok, the sqlite_Schema thing can (and has) been wrapped. however, the
error code issue is there, I believe that we have agreed in the past
that it was bad design, but it could not be changed because of
compatibility issues. I,for one, am willing to comb my code and re-code
for this, so yes, please do it. 

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, November 03, 2005 4:05 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Request for comment: Proposed SQLite API changes
> 
> Dan Kennedy <[EMAIL PROTECTED]> wrote:
> > > Another proposal:  Suppose that when creating an 
> sqlite3_stmt using 
> > > sqlite3_prepare, the original SQL text was stored in the 
> > > sqlite3_stmt.  Then when a schema change occurred, the 
> statement was 
> > > automatically recompiled and rebound.
> > 
> > The authorization callback
> > would have to be reinvoked from within sqlite3_step() too.
> 
> Yikes!  I didn't think of that.  This is pretty big negative 
> and will likely scuttle plans to do automatic re-prepare.
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 
> 
> 

RE: [sqlite] Page size problem

[Cariotoglou Mike]

FYI, I tried the same script on windows xp2 sqlite 3.2.7, and it worked
fine also 

> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, November 01, 2005 7:13 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Page size problem
> 
> "Anton Kuznetsov" <[EMAIL PROTECTED]> wrote:
> > Hello!
> > 
> > Did anybody try to create an SQLite3 database with a custom 
> page_size (e.g.
> > 8192) and fill it with data of more than 1Gb? As for me I didn't 
> > manage (using tclsqlite-3.2.7). It says "database disk 
> image is malformed".
> > 
> 
> I just testing the script shown below.  It generates a 2 GiB 
> database that seems to work fine.
> 

RE: [sqlite] SQL logic error when running vacuum;

[Cariotoglou Mike]

as a matter of fact, I have also noticed that using the vaccum command
from the command-line interface does bring this error up sometimes.
however, since I use the graphical UI most of the time (being its
author:) I don't use the command line interface that much. 
I suspect it has nothing to do with the engine itself, rather with the
command-line tool. 

> -Original Message-
> From: Preston Zaugg [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, October 04, 2005 6:35 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] SQL logic error when running vacuum;
> 
> I have a database (encrypted w/ encryption extensions from 
> drh). After run a sql file on this database it gives the 
> error "SQL error: SQL logic error or missing database" after 
> running a vacuum. it also seems to give this error 
> sporadically on other statements.
> 
> the sql file that i run does the following:
> drops a table
> creates a new table (same name different structure from the 
> the table that was just dropped) adds a unique constraint 
> runs 7000+ insert statements
> 
> the file is executed using the .read of the command line 
> utility, and runs without error/warning.
> 
> As a side note i am able to run this same script against a MS 
> SQL Server database without error.
> 
> Between the fact that i can run this script on sql server and 
> don't get any errors on the .read, makes me think that my 
> script is valid/well formed.
> 
> Oh.. other important stuff... i was on 3.2.2 when i started 
> running into this problem. Upgraded to 3.2.7 with the same 
> results. I'm on Windows 2003 and using a local database.
> 
> Any suggestions?
> --Preston
> 
> 
> 
> 
> 

RE: [sqlite] ANN: Sqlite3Explorer version 2.0 released

[Cariotoglou Mike]

> 
> Interesting tool. Are sources available for porting to other 
> OSes? I would like to try on FC4.
> --
> G. Roderick Singleton <[EMAIL PROTECTED]> PATH tech
> 
unfortunately not, as it uses a lot of commercial components. plus, it
is Delphi :)

RE: [sqlite] ANN: Sqlite3Explorer version 2.0 released

[Cariotoglou Mike]

Yes, this is an issue with a number of menu selections, which I forgot
to fix :) will do on the next release. For now, just open a db first.. 

> -Original Message-
> From: Dennis Cote [mailto:[EMAIL PROTECTED] 
> Sent: Monday, October 03, 2005 8:57 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] ANN: Sqlite3Explorer version 2.0 released
> 
> Cariotoglou Mike wrote:
> 
> >this is a major release, with a lot of changes. please see 
> the readme 
> >at www.singular.gr/sqlite.
> >Pls read the whole page carefully, as support for datatypes is now a 
> >lot more powerful, but slightly different than the previous versions.
> >Report users : you will need to download an extra dll, it is 
> bookmarked 
> >in the above link.
> >
> >you can see the changes at a glance in
> >www.singular.gr/sqlite/changes.htm
> >
> >
> >
> >  
> >
> Hi Mike,
> 
> Trying out your new version I ran into a crash when I clicked 
> the Query Builder button on the toolbar with no database 
> open. It works correctly with a database open.
> 
> ---
> sqlite3explorer2
> ---
> Access violation at address 004B68E8 in module 
> 'sqlite3Explorer2.exe'. 
> Read of address 002C.
> ---
> OK
> ---
> 
> Dennis Cote
> 
> 
> 

[sqlite] ANN: Sqlite3Explorer version 2.0 released

[Cariotoglou Mike]

this is a major release, with a lot of changes. please see the readme at
www.singular.gr/sqlite.
Pls read the whole page carefully, as support for datatypes is now a lot
more powerful, but slightly different than
the previous versions.
Report users : you will need to download an extra dll, it is bookmarked
in the above link.

you can see the changes at a glance in
www.singular.gr/sqlite/changes.htm

RE: [sqlite] about SQLite Explore with dll(3.2.7) problem

[Cariotoglou Mike]

hi. I am the author of this program. I have not seen what you mentioned, but in 
any case, I am about to release a
new major release 2.0 in a few days, which has been tested with 3.2.7 and does 
not seem to have any problems.



From: Huanghongdong [mailto:[EMAIL PROTECTED]
Sent: Wed 28-Sep-05 5:31 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] about SQLite Explore with dll(3.2.7) problem



I notic if I change the SQLiteExplore's dll to version 3.2.7 and use it to open 
the database wich created by old version dll then I get the a erro message,it 
saying something like "can't open the database file",anyone have idea?

RE: [sqlite] Problem with floating point fields, and a feature request

[Cariotoglou Mike]

also, it kills the usage of indexes, whereas what I propose would not. 

> -Original Message-
> From: Jay Sprenkle [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, September 20, 2005 5:04 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Problem with floating point fields, and 
> a feature request
> 
> On 9/20/05, Cariotoglou Mike <[EMAIL PROTECTED]> wrote:
> >
> > There is an issue with floating point fields, which exists in every 
> > database I have dealt with, and of course exists in sqlite as well, 
> > all versions.
> > Essentially, the issue is this:
> >
> > "When are two floating point values considered equal ?"
> 
> 
> what's wrong with using round(n,2)?
> 
> 
> ---
> The Castles of Dereth Calendar: a tour of the art and 
> architecture of Asheron's Call
> http://www.lulu.com/content/77264
> 
> 

RE: [sqlite] Problem with floating point fields, and a feature request

[Cariotoglou Mike]

nothing, when you hand-code. everything, when the code is
auto-generated, which very frequently it is 

> -Original Message-
> From: Jay Sprenkle [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, September 20, 2005 5:04 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Problem with floating point fields, and 
> a feature request
> 
> On 9/20/05, Cariotoglou Mike <[EMAIL PROTECTED]> wrote:
> >
> > There is an issue with floating point fields, which exists in every 
> > database I have dealt with, and of course exists in sqlite as well, 
> > all versions.
> > Essentially, the issue is this:
> >
> > "When are two floating point values considered equal ?"
> 
> 
> what's wrong with using round(n,2)?
> 
> 
> ---
> The Castles of Dereth Calendar: a tour of the art and 
> architecture of Asheron's Call
> http://www.lulu.com/content/77264
> 
> 

RE: [sqlite] Problem with floating point fields, and a feature request

[Cariotoglou Mike]

collating sequences do not apply to floating point comparisons, do they
? 

> -Original Message-
> From: Dan Kennedy [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, September 20, 2005 5:12 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Problem with floating point fields, and 
> a feature request
> 
> 
> > two floats A and B should be compared with this algorithm :
> > 
> > diff=A-B
> > if (diff>tolerance) then A>B
> > else if (diff<-tolerance) then A > else A = B
> 
> You could define a new collation sequence to do all that. 
> However, it's difficult to say what will happen when you have 
> three numbers A, B and C such that A==B and B==C but A!=C. 
> This will cause the occasional oddity.
> 
> But if you know for some reason this will never happen, 
> everything will work fine. "Know"
> 
> 
> 
> 
> 
>   
> __
> Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com
> 
> 
> 

[sqlite] Problem with floating point fields, and a feature request

[Cariotoglou Mike]

There is an issue with floating point fields, which exists in every
database I have dealt with, and of course exists in sqlite as well, all
versions.
 Essentially, the issue is this:

"When are two floating point values considered equal ?"

Why is this an issue ?
Floating point values are some times used as part of keys. Even worse,
sql resolvers (engines that construct sql statements based on old/new
values of client data) frequently construst statements like this one:

update table
 set a=
where b=

such a statement will result from a user selecting , editing, and
posting an update. Sqlite needs to find the relevant row, (either with
an index or not), which at some point will involve comparing
a float value stored in the database with a user-supplied float value.

Now, depending on how floats are stored, and how they are inputted, this
may or may not be an issue. in sqlite 2, where all is stored as text,
one could choose to store floats with a known precision,
and work around the inherently inaccurate comparison of floats. in
sqlite 3, we have a FLOAT storage class , which means that we now store
floats in binary form (which is good), and also means that
equality is based on comparing floats in a binary way ( all 8 bytes
being equal, in other words). 

This can/will be disastrous in many cases, one such was raised some time
ago by another user (the '9.95' issue). The above update can fail, for
no reason apparent to the user.

It also happens with people working in Delphi, and using native date
formats, which are actually floats. Dates are very often part of keys,
sorting, grouping etc, so all kinds of rounding errors can and will
manifest.

Of course, one can work around this problem, by NOT storing floats in
binary format, but in a well-defined text format. however, this would be
a pity, as a lot of time would be taken by sqlite and user code in
comverting back and forth, plus, in some cases, sqlite will try to
promote values stored as text to binary, re-introducing the problem. not
to mention that, floats stored as text will collate incorrectly, unless
they are right-justified and padded with '0', which increases the
storage requirements. 

This is not new, however, in sqlite we *do* have access to the people
that write the code, so a fix *can* be found, if there is a will and
understanding of the problem :) :)

All possible solutions to this issue have to do with the way that float
equality is established. It boils down to this:

two floats A and B should be compared with this algorithm :

diff=A-B
if (diff>tolerance) then A>B
else if (diff<-tolerance) then A

or

adding a user-defined callback (as you did with the REGEXP operator),
such as 

sqlite_compareFloat(a,b), which defaults to whatever code is now used to
compare two floats, but can be overriden by the user.

the  should be in terms of SIGNIFICANT digits, not number of
decimals, due to the way floating point works.

Having a capability like this would be of immence value to the stability
of database applications, and , I suspect, would not require a major
re-work. 

what do you think, DRH, is this doable ?

RE: [sqlite] problems compiling 3.2.6

[Cariotoglou Mike]

drh already fixed it. 

see
http://www.sqlite.org/cvstrac/chngview?cn=2720 

> -Original Message-
> From: Maurizio Ferraris [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, September 20, 2005 4:13 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] problems compiling 3.2.6
> 
> I also have the same problems with VC6, and unfortunately I 
> cannot switch to
> VC7 for other reasons.
> At the moment I reverted back to 3.2.5.
> When I will have some spare time I will try to fix it myself 
> and I will inform the list.
> My guess is: update too the latest SDK (that unfortunately 
> does not integrate well with VC6) for the first problem and 
> add another (__int64) cast before the conversion to double.
> Of course this is just a hack and I hope someone will find a 
> better fix.
> Regards.
> Mau.
> 
> Drew, Stephen wrote:
> > Further update:
> > 
> > I get both these errors in Visual Studio 6.
> > 
> > As you say, I think the first is just that Visual Studio 6 has a 
> > missing definition in winbase.h. I agree with you that it is in the 
> > documentation (perhaps a check could be performed and it 
> defined if it 
> > doesn't exist...)
> > 
> > The second seems to be a limitation of VS6 - it certainly isn't a 
> > problem in VS7. Can't really think of any neat solution around it 
> > either...
> > 
> > This doesn't concern me, as - as I mention - I use VS7...
> > 
> > Steve
> > 
> > -Original Message-
> > From: Drew, Stephen
> > Sent: 19 September 2005 17:13
> > To: sqlite-users@sqlite.org
> > Subject: RE: [sqlite] problems compiling 3.2.6
> > 
> > Mike,
> > 
> > 3.2.6 compiles fine in Visual Studio 7 (.NET 2003).  I can 
> give it a 
> > go in my copy of Visual Studio 6 if you like...
> > 
> > Steve
> > 
> > -Original Message-
> > From: Cariotoglou Mike [mailto:[EMAIL PROTECTED]
> > Sent: 19 September 2005 10:14
> > To: sqlite-users@sqlite.org
> > Subject: [sqlite] problems compiling 3.2.6
> > 
> > I tried to compile 3.2.6 locally, using visual c 6, as I do 
> with all 
> > sqlite releases. this version introduces a couple of 
> changes that do 
> > not
> > compile:
> > 
> > os_win.c(482) : error C2065: 'INVALID_SET_FILE_POINTER' : 
> undeclared 
> > identifier
> > vdbeapi.c(237) : error C2520: conversion from unsigned __int64 to 
> > double not implemented, use signed __int64
> > 
> > the first error has to do with an old version of winbase.h, 
> which for 
> > some reason omits the definition of 
> INVALID_SET_FILE_POINTER (although 
> > the documentation mentions it). this is probably a local 
> problem, and 
> > I will try to fix locally (although I would like to hear from other 
> > people about it. I am compiling WITHOUT mfc).
> > 
> > the second I have no idea, as I don't know C.
> > 
> > anybody help ?
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> > 
> 
> 
> 

RE: [sqlite] query problem

[Cariotoglou Mike]

You are correct. I just run a test on 3.2.6 release, and it does handle
joins incorrectly. I cant get the files from cvs, so I am not sure
whether the fix also
Handles the reverse situation:

Select *
>From T1 left join t2 on (t1.ref=t2.id) and (t2.kind=1)

Ie if there is a join term that restricts the RIGHT hand table only.

Just a thought. 

> -Original Message-
> From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, September 20, 2005 12:07 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] query problem
> 
> I've changed my mind.  I think instead that there is a bug in 
> SQLite that caused LEFT JOINs to be computed incorrectly if 
> one of the terms in the ON clause restricts only the left 
> table in the join.  
> 
> Check-in [2725] at http://www.sqlite.org/cvstrac/chngview?cn=2725
> contains
> a fix for this problem for version 3.x.  The problem has 
> existed in SQLite forever (because it originates from a 
> conceptual misunderstanding by the code author :-)) so 
> version 2.8.16 is still broken.  Because the problem is 
> obscure, I am not inclined to fix it in the 2.8.x series...
> 
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 
> 
> 

RE: [sqlite] Using date fields in SQLiteExplorer

[Cariotoglou Mike]

it is definitely possible. 
how have you defined the data type in the CREATE statement ?
how did the data was inserted in the table initially ?

have you enabled the "use datatypes" option ?

dump the contents of the table with the "datatypes" disabled, so you see
raw data. are they really floating point values ?

remember, you can use the "show only" check box, to see the SQL that the
resolver will produce, without actually running it.
 
if you cannot figure it out, send me the db.

> -Original Message-
> From: Zibetti Paolo [mailto:[EMAIL PROTECTED] 
> Sent: Monday, September 19, 2005 1:07 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Using date fields in SQLiteExplorer
> 
> Here is another question about dates and SQLiteExplorer (v 1.7).
> I'm storing dates in the native Delphi format, i.e. as 
> floating point numbers ("dates as text" = FALSE).
> Whenever I try to update a record that contains such a date, 
> SQLiteExplorer displays the message "unexpected count of 
> affected records:0" and modifications are not written to the database.
> Is it possible that SQLiteExplorer is trying to update the 
> record on the database using the date fields in a "where" 
> clause but fails because of rounding errors in the floating 
> point rapresentation of dates ?
> How can I work around this problem ?
> 
> Thank you
> Bye
> 
> 
> 
> 

[sqlite] problems compiling 3.2.6

[Cariotoglou Mike]

I tried to compile 3.2.6 locally, using visual c 6, as I do with all
sqlite releases. this version introduces a couple of changes that do not
compile:

os_win.c(482) : error C2065: 'INVALID_SET_FILE_POINTER' : undeclared
identifier
vdbeapi.c(237) : error C2520: conversion from unsigned __int64 to double
not implemented, use signed __int64

the first error has to do with an old version of winbase.h, which for
some reason omits the definition of INVALID_SET_FILE_POINTER (although
the documentation mentions it). this is probably a local problem,
and I will try to fix locally (although I would like to hear from other
people about it. I am compiling WITHOUT mfc).

the second I have no idea, as I don't know C.

anybody help ?

[sqlite] sqlite3_errCode and error handling

[Cariotoglou Mike]

when a sqlite3_Step call fails, say on a unique key violation of an
INSERT statement, 
the returned error code is SQLITE_ERROR, which is documented. 
calling sqlite3_errcode at this point, however, also gives the same
error, which is not. 
the correct error, which is SQLITE_CONSTRAINT, is only returned when
calling sqlite3_finalize, which may happen at a much later stage in the
code. 

I believe the error code should be returned right at the point where the
error happens, because if it is not, the structure of the code suffers,
since the finalization code for a
statement needs to be modal.

RE: [sqlite] SUM and NULL values

[Cariotoglou Mike]

There is one other thing, though. Allthough the sql standard is 
(insert your favorite 4-letter word), and although I personally hate
nulls, I try to write applications where the sql is as portable as it
can be. So, in the name of portability, you should follow the sql
standard.

[sqlite] ANN: Sqlite3Explorer 1.8 released

[Cariotoglou Mike]

Minor fix to work around a bug in pragma empty_result_callbacks, which
makes Explorer incompatible with the ANALYZE command 

RE: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE

[Cariotoglou Mike]

ok, I pinned it down. it is a genuine bug, and has nothing to do with
dlls and wrappers.

to reproduce, do this:

1. open the command-line utility on a database (or even with no
database, it does not matter)
2. type this:


SQLite version 3.2.5
Enter ".help" for instructions
sqlite> pragma empty_Result_callbacks=1;
sqlite> analyze; 

this will crash immediately. 
in general, if empty_Result_callbacks is set, AND the database is
analyzed, sqlite crashes consistently. 

the reason it appeared in sqlite3Explorer is that it sets the pragma by
default.

DRH, pls check this.

> -Original Message-
> From: Miha Vrhovnik [mailto:[EMAIL PROTECTED] 
> Sent: Monday, September 05, 2005 7:14 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE
> 
> "Cariotoglou Mike" <[EMAIL PROTECTED]> je ob 5.9.2005 
> 14:49:54 napisal(a):
> 
> >ok.
> >I use sqlite3Explorer (which should not matter), I create a new 
> >database, then I run :
> >
> >create table test1(id integer);
> >analyze
> >
> >this crashes immediately, with dll 3.2.5. it works fine from the 
> >command-line.
> > 
> I can confirm that. But it seems that the problem is in 
> Sqlite explorer or your sqlite3.dll wrapper implementation. 
> When I try that with my own Delphi wrapper implementation it 
> works. But opening database with sqlite explorer results in crash.
> 
> Regards,
> Miha
> 
> 
> 

RE: [sqlite] Problems with threadsafe opt correction #2623

[Cariotoglou Mike]

I hope that this restriction is not enforced on windows, ever! I have an 
application that will be completely broken by this:

it is an application server, that serves multiple clients, pools db 
connections, and  hands them out on demand, protecting the pool with a number of
mechanisms that have never failed. so, the application is quite thread-safe. on 
windows, the option to use processes instead of threds,is not a very good one, 
as you know.so, any scheme that uses threads, AND resource pooling, will fail 
if you enforce above restriction, which is meaningless in windows, anyway. so, 
please don't!

> -Original Message-
> From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
> Sent: Monday, September 05, 2005 9:19 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Problems with threadsafe opt correction #2623
> 
> On Mon, 2005-09-05 at 19:58 +0200, René Tegel wrote:
> 
> > As far as i can tell now the windows version of 3.2.5 seems 
> not affected.
> > 
> 
> The restriction that a DB handle can only be used from a 
> single thread is currently only enforced on Unix, because 
> Unix boxes are the only place where using DB handles in 
> multiple threads is a problem.
> 
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 
> 
> 

RE: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE

[Cariotoglou Mike]

ok.
I use sqlite3Explorer (which should not matter), I create a new
database, then I run :

create table test1(id integer);
analyze

this crashes immediately, with dll 3.2.5. it works fine from the
command-line.
 

> -Original Message-
> From: Ned Batchelder [mailto:[EMAIL PROTECTED] 
> Sent: Monday, September 05, 2005 3:10 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE
> 
> Perhaps you could provide the exact SQL that crashed.  The 
> code seems to be very well tested automatically, so it is 
> very unlikely that all ANALYZE executions fail.
> 
> --Ned.
> http://nedbatchelder.com
>  
> -Original Message-
> From: Cariotoglou Mike [mailto:[EMAIL PROTECTED]
> Sent: Monday, 05 September, 2005 5:46 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] bug in sqlite 3.2.5 compilation and ANALYZE
> 
> I tried the ANALYZE statement with sqlite 3.2.5 in dll form, 
> (both the pre-compiled version downloaded from the site, AND 
> a local compilation).
> in both
> cases, the statement fails with an ACCESS VIOLATION. however, 
> the same statement, when run from the pre-compiled 
> sqlite3.exe, works. 
> further, having succesfully ANALYZED a database with the 
> command-line program, I can no longer work with the database 
> using the DLL api. 
> I think there is something very wrong with the compilation 
> defines of the DLL. 
> 
> DRH: can you pls check and fix ?
> ALL: can you verify that the DLL API crashes on ANALYZE ? (WINDOWS,
> obviously)
> 
> 
> 
> 
> 
> 
> 

[sqlite] bug in sqlite 3.2.5 compilation and ANALYZE

[Cariotoglou Mike]

I tried the ANALYZE statement with sqlite 3.2.5 in dll form, (both the
pre-compiled version downloaded from the site, AND a local compilation).
in both
cases, the statement fails with an ACCESS VIOLATION. however, the same
statement, when run from the pre-compiled sqlite3.exe, works. 
further, having succesfully ANALYZED a database with the command-line
program, I can no longer work with the database using the DLL api. 
I think there is something very wrong with the compilation defines of
the DLL. 

DRH: can you pls check and fix ?
ALL: can you verify that the DLL API crashes on ANALYZE ? (WINDOWS,
obviously)

RE: [sqlite] ANN: sqlite3Explorer V 1.7

[Cariotoglou Mike]

good to know... 

> -Original Message-
> From: Dennis Jenkins [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, July 27, 2005 4:26 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] ANN: sqlite3Explorer V 1.7
> 
> Dennis Jenkins wrote:
> 
> > Cariotoglou Mike wrote:
> >
> >> *. new feature : support for encrypted databases (WITH the 
> licenced 
> >> version of sqlite)
> >>
> >> http://www.singular.gr/sqlite/
> >>  
> >>
> >
> > Awesome!  Thanks!  I was hoping that you would add 
> encryption support. 
> > :)  (We purchased our license a few weeks ago).
> >
> > However, I am unable to get it to work.  I am 100% sure that I am 
> > entered the encryption key when prompted for it.
> >
> 
> :)  Never mind.  I solved my problem.  I forgot to define 
> 'SQLITE_HAS_CODEC=1' when compiling the DLL.  It works great now.  
> (previously I was getting error '26', "not a database, or encrypted").
> 
> 
> 
> 

[sqlite] ANN: sqlite3Explorer V 1.7

[Cariotoglou Mike]

*. new feature : support for encrypted databases (WITH the licenced
version of sqlite)

http://www.singular.gr/sqlite/

RE: [sqlite] Multi-threading.

[Cariotoglou Mike]

which gives me the opportunity to repear my oft-ignored reply :)

what you say is true, provided the OS is geared towards multiple
processes. which is not true for windows, but is true for *ix, as400 and
other environments. if you need, say, a server that handles 500
sessions, and attempt to do this with spawning processes on windows, you
are probably dead,
memory and cpu-wise. on Linux, this is nothing, it can handle it easily.
otoh, 500 threads for windows is business as usual, but threading on
Linux, is , I hear, iffy at best.

so, although this is good advice, it is not unconditionally applicable.

> -Original Message-
> From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
> Sent: Friday, July 15, 2005 3:27 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Multi-threading.
> 
> On Fri, 2005-07-15 at 16:41 +0530, Roushan Ali wrote:
> > Hello all,
> >   Can we use single sqlite_open handle(global) 
> across threads( 
> > if all database operations are serialized by using 
> semaphore) ? Please 
> > help.
> >
> 
> Opening a database connection in one thread and using it in 
> another will work on some operating systems but not on 
> others.  You are advised not to do it.  See 
> http://www.sqlite.org/cvstrac/tktview?tn=1272
> and http://www.sqlite.org/cvstrac/chngview?cn=2521.
> 
> Actually, this seems like a good opportunity to repeat my 
> oft-ignored advice to not use more than one thread in a 
> single address space.  If you need multiple threads, create 
> multiple processes.  This has nothing to do with SQLite = it 
> is just good programming advice.  I have worked on countless 
> multi- threaded programs over the years, and I have yet to 
> see a single one that didn't contain subtle, hard to 
> reproduce, and very hard to troubleshoot bugs related to 
> threading issues.
> 
> I am constantly amazed at the prevailing idea (exemplified by 
> Java) that software should be strongly typed and should not 
> use goto statement or pointers - all in the name of reducing 
> bugs - but that it is OK to use multiple threads within the 
> same address space.  Strong typing helps prevent only bugs 
> that are trivially easy to locate and fix.  The use of goto 
> statements and pointers likewise results in deterministic 
> problems that are easy to test for and relatively easy to 
> track down and correct.  But threading bugs tend to manifest 
> themselves as timing-dependent glitches and lock-ups that are 
> hardware and platform dependent, that never happen the same 
> way twice, and that only appear for customers after 
> deployment and never in a testing environment.
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 
> 
> 

RE: [sqlite] ANN: Sqlite3Explorer.exe version 1.6

[Cariotoglou Mike]

which version of the dll are you using ? do you have REFERENCES
declarations ? can you send me a sample of the data ? 

> -Original Message-
> From: Hugh Gibson [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, June 22, 2005 4:22 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] ANN: Sqlite3Explorer.exe version 1.6
> 
> Just got a crash when viewing table data using SQLite3Explorer version
> 1.6:
> 
> ---
> sqlite3explorer
> ---
> Access violation at address 00402E29 in module 'sqlite3Explorer.exe'. 
> Write of address 01357AAE.
> ---
> OK   
> ---
> 
> Pressing OK enabled me to scroll the data, but when reaching 
> near the bottom I got:
> 
> ---
> sqlite3explorer
> ---
> RecordIndex out of range.
> ---
> OK   
> ---
> 
> Integrity check on the database gave:
> 
> ---
> sqlite3explorer
> ---
> Database seems ok
> ---
> OK   
> ---
> 
> Scrolling up and down gave more errors, and eventually I had 
> to kill the app.
> 
> The only thing that could affect this is that I had some 
> fields with lots of data, probably around 100k.
> 
> Hugh
> 
> 
> 

RE: [sqlite] Sqlite3explorer can't open my DB

[Cariotoglou Mike]

Could you please let me know what the problem was ? I am the author of
sqlite3Explorer, and perhaps I could fix it.  

> -Original Message-
> From: Downey, Shawn [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, May 17, 2005 11:45 PM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] Sqlite3explorer can't open my DB
> 
> Thanks anyway.  I solved this myself.  
> 
> Sqlite3Explorer is more particular about the table schema 
> syntax than Sqlite.
> 
> Shawn M. Downey
> MPR Associates
> 632 Plank Road, Suite 110
> Clifton Park, NY 12065
> 518-371-3983 x3 (work)
> 860-508-5015 (cell)
> 
> 
> -Original Message-
> From: Downey, Shawn
> Sent: Tuesday, May 17, 2005 12:28 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Sqlite3explorer can't open my DB
> 
> Version 1.4 of Sqlite3explorer could not open my database.  
> The error message is:
> 
>  
> 
> 4:malformed database schema - near "to": syntax error
> 
>  
> 
> An older version of Sqlite3explorer worked fine on this 
> database.  I am using sqlite.dll version 3.2.1.  Does anyone 
> else have problems with Sqlite3explorer?
> 
>  
> 
> Shawn M. Downey
> 
> MPR Associates
> 
> 632 Plank Road, Suite 110
> 
> Clifton Park, NY 12065
> 
> 518-371-3983 x3 (work)
> 
> 860-508-5015 (cell)
> 
>  
> 
> 
> 
> 

RE: [sqlite] SqliteExplorer and ISO8601 dates

[Cariotoglou Mike]

new version 1.4, adds support for date formats, when storing dates as
text.
to support the format -MM-DD HH:MM:SS, all you need to do is change
the date separator to "-",
and change the time format (remove the .zzz)
do NOT change the separator used in the actual date format, the "/" you
see there is a meta-character. 

http://www.singular.gr/sqlite/ 

RE: [sqlite] SqliteExplorer and ISO8601 dates

[Cariotoglou Mike]

Will fix and let you know 

> -Original Message-
> From: Brad Schick [mailto:[EMAIL PROTECTED] 
> Sent: Saturday, April 30, 2005 9:31 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] SqliteExplorer and ISO8601 dates
> 
> Is it possible to get SqliteExplorer to parse textual dates in the
> ISO8601 format of:
> 
> -MM-dd HH-mm-ss
> 
> I believe this is the format that sqlite uses itself for 
> CURRENT_TIMESTAMP, et. al.
> 
> I'm running SqliteExplorer 1.3 on WinXP with "Use Datatypes" 
> and "Dates as Text" enabled and it only seems to parse dates 
> slash seperators like 2005/04/27. Of course I can work around 
> this by disabling datatypes, but I like the extra error 
> checking provided by datatypes.
> 
> Thanks,
> -Brad
> 
> 
> 

RE: [sqlite] Trouble with column names

[Cariotoglou Mike]

Imho, naming scheme should be like this:

1. column names as returned from sqlite3_column_name should always be a
single word,
NOT qualified by origin.
So, all below should return "field1" as column name.
If there is a column alias in the select, the *alias* should be returned
as the single word.
Case should be irrelevant for column names, as sql is supposed to be
case-insensitive.

2. In case where an expression is given as column, say select field1+5
from..., it is
Customary to return a pseudo name such as ExprN, where n=1..n. in any
case, this is not
Important, so use any scheme you find suitable.


3. there *should* be (nice to have) an sqlite3_column_origin function,
which attempts to
Return the column origin, in the form:
Database.table.column In all cases, the *real* names should be returned,
and not the aliases

column_name
column_origin

--
>   select field1 from test;  field1
test.field1
>   select "field1" from test;field1
''
>   select FIELD1 from test;  field1 (or FIELD1)
''
>   select "FIELD1" from test;field1
''
>   select [field1] FROM test;field1
''
>   select test.field1 from test; field1
''
>   select a.field1 from test as a;   field1
''
>   select "a".field1 from test as a; field1
''
>   select "a"."field1" from test as a;   field1
''
>   select main.test1.field1 from test;   field1
main.test.field1
>   select [main]."a".[field1] from test as a;field1
test.field1
select field1 as a from test  a
test.field1
etc


> 
> This problem is related to the naming of columns in views.
> Please tell me what the column names for the following views 
> should be:
> 
>   create view v1 as select field1 from test;
>   create view v2 as select FIELD1 from test;
>   create view v3 as select a.field1 from test as a;
>   create view v4 as select test1.field1 from test;
>   create view v5 as select "FIELD1" from test;
>   create view v6 as select "A"."FIELD1" from test as a;
>   create view v7 as select "a".field1 + 5 FROM test as a;
>   create view v8 as select "a".field1+5 from test as a;

I believe in all cases, the names for sqlite3_column_name, and
sqlite3_column_origin should be exactly the same as if the
View was not present, ie as if the select was given directly.
 
> Another place where naming is an issue is on CREATE TABLE AS 
> statements.  What is the name of the column in each of the 
> following tables:
> 
>   create table t2 as select field1 from test;
>   create table t3 as select FIELD1 from test;
>   create table t4 as SELECT a.field1 from test as a;
>   -- and so forth

Same comment as above, the column names should be the same for the
equivalent SELECT (and data types as well)

> For the above, what is the column name reported out when I do:
> 
>   select * from t2;
>   select * from t3; -- and so forth
> 
> or
> 
>   select * from v1;
>   select * from v2; -- and so forth
> 

> If someone will come up with a coherent set of column naming 
> rules - rules which other popular SQL database engines follow 
> - then I will be happy to implement them in SQLite.  Doing so 
> will probably break a lot of existing code.  But as the 
> column naming rules have never been specified or documented 
> before, I'd be willing to do it since any code that breaks 
> would have been depending on undefined behavior to begin with.
> 
> Does the SQL standard have anything to say about this?
> Does anybody know?

I don't believe so. So, really, it is a matte of choice. However, let us
examine the rationale of column naming requests you have been getting
all this time. They come under two categories :

A. people that do not like the current scheme / cannot make it
compatible with some other database etc etc
IMHO this is a lot of noise about a non-significant issue. Column names
are retrieved from the sql statement, and people have the chance to map
them to whatever they think is significant for the end-user. Programs
can handle this easily, so I don't understand what it all the fuss
about. My only comment here is that column names should be "short" (like
pragma short_column_name), because this is what databases normally do.

B. People who need to use column names to do generic processing, like
machine-generated UPDATES, discovery of relations etc
In this case, it is *ESSENTIAL* that :
 a. column names are consistent, and as informative as possible
 b. the origin of data can be retrieved as clearly as possible, since it
is *this* which is needed by tools writers, and not any aliasing that
the programmer has used in order to resolve ambiguities or for prettying
up the sql statement.

So, this is why I propose a second api, sqlite3_column_origin, which can
return the real origin of the data, skipping 

RE: [sqlite] NFS Query Performance

[Cariotoglou Mike]

Just a thought. If transactions speed up the access, try this:

Begin exclusive
Select 
Select ...
Select ...
Commit

Ie use a transaction around READS.
This may acquire the lock once, and give you the same performance as
inserts. 

> -Original Message-
> From: William Hachfeld [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, April 19, 2005 10:18 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] NFS Query Performance
> 
> On Tue, Apr 19, 2005 at 11:58:11AM -0700, Ted Unangst wrote:
> > If you don't need locking, and it sounds like you don't, 
> just neuter 
> > the fcntl calls in os_unix.c.  In fact, there's a #define 
> and comment 
> > for DJGPP that does exactly what you want.
> 
> Hmmm. Thanks for the suggestion, Ted. I think I'll try this 
> out. If nothing else, this will help confirm whether the 
> majority of the slowdown is due to NFS locking performance.
> 
> -- William Hachfeld
> 
> 
> 

[sqlite] ANN: new version of sqlite3Explorer (1.2)

[Cariotoglou Mike]

Adds a simple report generator.

RE: [sqlite] what is the difference between TEXT and BLOB data type?

[Cariotoglou Mike]

It is a limitation of sqliteExplorer. 

> -Original Message-
> From: jack wu [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, April 07, 2005 7:05 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] what is the difference between TEXT and 
> BLOB data type?
> 
> 
> other than encoded and not encoded.
> 
> i did not find the length limitations in the documentation 
> but seems like TEXT can only hold less than 250 chars. is it 
> true or is it SqliteExplorer that only shows 250 chars. Thanks.
> 
> 
> 
> 

RE: [sqlite] ANN: Sqlite3Explorer

[Cariotoglou Mike]

good idea. so, let me see if I understood correctly. you are suggesting
that I rewrite the whole program in C++, plus any additional library
code I might be using, so that I can use the widgets. does not sound as
very practical advice to me.. 

> -Original Message-
> From: Guru Kathiresan [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, April 05, 2005 8:21 AM
> To: sqlite-users@sqlite.org
> Subject: RE: [sqlite] ANN: Sqlite3Explorer
> 
> Hi,
> For designing wxWidgets forms like Delphi, you can use my 
> program IDE wx-devcpp from http://wxdsgn.sf.net . Although it 
> is still in beta stage, you can still able to create a decent 
> application with it.
> 
> -Guru Kathiresan
> 
> > -Original Message-
> > From: Leif Jensen [mailto:[EMAIL PROTECTED]
> > Sent: Monday, April 04, 2005 9:53 PM
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] ANN: Sqlite3Explorer
> > 
> > Have you heard about wxWidgets (www.wxWidgets.org) ? A 
> great free 
> > GUI library (and much more) that works on Windows, Linux 
> and Mac (at 
> > least). Using that and your app. would do too  ;-). I have 
> just made a 
> > database application with these that runs on both Linux and Windows 
> > using SQLite3.
> > 
> >  Leif
> > 
> > 
> > Cariotoglou Mike wrote:
> > 
> > >[EMAIL PROTECTED] it seems the new controls I used require this function, 
> > >which 
> > >only exists in win2k+. sorry for this, I think I will rebuild 
> > >*without* the devexpress controls, for more genral purpose use..
> > >
> > >
> > >
> > >>-Original Message-
> > >>From: Serge Liber [mailto:[EMAIL PROTECTED]
> > >>Sent: Monday, April 04, 2005 8:30 PM
> > >>To: sqlite-users@sqlite.org
> > >>Subject: Re: [sqlite] ANN: Sqlite3Explorer
> > >>
> > >>Hi,
> > >>
> > >>When starting sqlite3Explorer an error message appears:
> > >>"The procedure entry point GdiGradientFill could not be 
> located in 
> > >>the dll GDI32.dll"
> > >>
> > >>How can I proceed?
> > >>
> > >>I use NT4+sp6
> > >>
> > >>Serge Liber
> > >>
> > >>
> > >>New version is out. Since the problem with size limits was not 
> > >>lifted from contrib, I created a home site for the 
> program. You will 
> > >>now be able to get latest version from
> > >>
> > >>http://www.singular.gr/sqlite
> > >>
> > >>Latest is 1.1 , 24/3/2005 (ignore the typo in the date)
> > >>
> > >>Regards,mike
> > >>
> > >>
> > >>
> > >>
> > >>
> > >
> > >
> > >
> > >
> 
> 
> 
> 

RE: [sqlite] ANN: Sqlite3Explorer

[Cariotoglou Mike]

I found the offending call and removed it. it was *not* in the
devExpress code, but the visual query builder code. as a result, table
captions are no longer "nice"... pls download again. 

> -Original Message-
> From: Serge Liber [mailto:[EMAIL PROTECTED] 
> Sent: Monday, April 04, 2005 8:30 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] ANN: Sqlite3Explorer
> 
> Hi,
> 
> When starting sqlite3Explorer an error message appears:
> "The procedure entry point GdiGradientFill could not be 
> located in the dll GDI32.dll"
> 
> How can I proceed?
> 

RE: [sqlite] ANN: Sqlite3Explorer

[Cariotoglou Mike]

[EMAIL PROTECTED] it seems the new controls I used require this function, which 
only
exists in win2k+. sorry for this, I think I will rebuild *without* the
devexpress controls, for more genral purpose use..

> -Original Message-
> From: Serge Liber [mailto:[EMAIL PROTECTED] 
> Sent: Monday, April 04, 2005 8:30 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] ANN: Sqlite3Explorer
> 
> Hi,
> 
> When starting sqlite3Explorer an error message appears:
> "The procedure entry point GdiGradientFill could not be 
> located in the dll GDI32.dll"
> 
> How can I proceed?
> 
> I use NT4+sp6
> 
> Serge Liber
> 
> 
> New version is out. Since the problem with size limits was 
> not lifted from contrib, I created a home site for the 
> program. You will now be able to get latest version from
> 
> http://www.singular.gr/sqlite
> 
> Latest is 1.1 , 24/3/2005 (ignore the typo in the date)
> 
> Regards,mike
> 
> 
> 

RE: [sqlite] ANN: Sqlite3Explorer

[Cariotoglou Mike]

I am afraid not. the components I am using do not port to Linux. 

> -Original Message-
> From: Leif Jensen [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, March 30, 2005 7:56 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] ANN: Sqlite3Explorer
> 
>Any chance for  a Linux version ?   Or a web version ?
> 
>Greetings,
> 
>  Leif
> 
> 
> Cariotoglou Mike wrote:
> 
> >New version is out. Since the problem with size limits was 
> not lifted 
> >from contrib, I created a home site for the program. You will now be 
> >able to get latest version from
> >
> >http://www.singular.gr/sqlite
> >
> >Latest is 1.1 , 24/3/2005 (ignore the typo in the date)
> >
> >Regards,mike
> >
> >
> >  
> >
> 
> 
> 

[sqlite] ANN: Sqlite3Explorer

[Cariotoglou Mike]

New version is out. Since the problem with size limits was not lifted
from contrib, I created a home site for the program. You will now be
able to get latest version from

http://www.singular.gr/sqlite

Latest is 1.1 , 24/3/2005 (ignore the typo in the date)

Regards,mike

RE: [sqlite] Exporting MS Access MDB into SQLite

[Cariotoglou Mike]

This may be an issue with the odbc driver. In the meanwhile, you could
try importing with the gui tool, sqlite3explorer
(www.sqlite.org/contrib). Afaik, it should import access ok. 

> -Original Message-
> From: RAY BORROR [mailto:[EMAIL PROTECTED] 
> Sent: Monday, March 28, 2005 9:54 AM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] Exporting MS Access MDB into SQLite
> 
> SQLite Users,
>  
> I started to export an Access MDB file into a DB file that 
> was created with SQLite.
> 
> I was using the Visual Basic, Visual Data Manager tool to 
> export tables to the SQLite DB using the SQLite ODBC driver.
> 
> The first table went OK.
> All other tables after that came up with the following error:
> VisData
> The following Error occurred
> OBDC-call failed
> Number 3146
> 
> If I create a new DB in SQLite, I am able to export one table 
> without a problem.
> It doesn't seem to matter which table it is.
> 
> Please review and advise of any possible solutions.
> 
> 
> Thanks,
>  
> Ray Borror
> 
> 

RE: [sqlite] RegEx w/ sqlite, yet?

[Cariotoglou Mike]

If you are working with delphi, I could have a solution for you. 

> -Original Message-
> From: Jay [mailto:[EMAIL PROTECTED] 
> Sent: Saturday, March 26, 2005 3:41 AM
> To: sqlite-users@sqlite.org; Win a 2
> Subject: Re: [sqlite] RegEx w/ sqlite, yet?
> 
> 
> > Hello sqlite-users,
> > 
> >   any news on that?
> 
> It's not part of the sql standard, so I would bet the answer 
> is going to be 'you have to add that yourself'. I did for my project.
> 
> __
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam protection 
> around http://mail.yahoo.com 
> 
> 
> 

RE: [sqlite] Contrib uploads

[Cariotoglou Mike]

it is already UPX'ed AND zipped. still...



From: Eugene Wee [mailto:[EMAIL PROTECTED]
Sent: Fri 3/25/2005 6:24 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Contrib uploads



Hi,

have you considered using UPX to reduce the executable filesize?
http://upx.sourceforge.net

RE: [sqlite] getting table column names and types programaticly

[Cariotoglou Mike]

Pragma table_info(tablename). For God's name, do read the documentation,
somebody spent good time to write it! 

> -Original Message-
> From: Gerry Snyder [mailto:[EMAIL PROTECTED] 
> Sent: Friday, March 25, 2005 1:19 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] getting table column names and types 
> programaticly
> 
> Jim Dodgen wrote:
> > Anyone know how I can query the field names and types for a 
> given table? 
> 
> Jim,
> 
> select sql from sqlite_master where type="table" and name="gigo"
> 
> will get something like:
> 
> create table gigo(a,b,c)
> 
> which includes the field names, and would include the types 
> if I had used any.
> 
> Gerry
> 
> 
> 

RE: [sqlite] Contrib uploads

[Cariotoglou Mike]

1.1 mb I used the [EMAIL PROTECTED] devExpress grid, which is great
functionality-wise but bloats the 
Exe. 

> -Original Message-
> From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, March 24, 2005 11:44 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Contrib uploads
> 
> On Thu, 2005-03-24 at 11:24 +0200, Cariotoglou Mike wrote:
> > I tried to upload a new version of sqlite3Explorer, and I 
> got back the
> > error:
> > "Too much POST data". 
> 
> How big of an upload are we talking about?
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 
> 

RE: [sqlite] Proposal: limit the number of columns in a table to 2000.

[Cariotoglou Mike]

as a suggestion to the list, this issue should be answered only by those
that disagree, else we will get 2k messages saying why not..

RE: [sqlite] [ANN] SQLite Analyzer 3

[Cariotoglou Mike]

>From  a quick look at sqlite analyzer, I would say the following :

1. it is not free
2. it has better looks than sqlite3Explorer
3. it cannot update tables using an editable grid approach
4. it does not have a visual query builder
5. I am not sure it has ADO import/export capabilities 

-Original Message-
From: Eugene Wee [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, March 16, 2005 7:57 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] [ANN] SQLite Analyzer 3

Hi people,

I'm relatively new to SQLite, and would like some comments as to the GUI
tools listed in the wiki.
In particular, does anyone actually use SQLite Analyzer 3 around here?
If so, how does it compare to sqlite3Explorer?

For reference sqlite3Explorer is the one on sqlite.org/contrib, and
SQLite Analyzer at http://www.kraslabs.com/sqlite_analyzer.html

Thanks,
Eugene Wee

RE: [sqlite] Database Version 2 or 3, can you query for it?

[Cariotoglou Mike]

The only way is to query one of the exported functions (sqlite3_version
or something like this). I agree, however, that it would be nice to have
a
version resource in the DLL. Also, If somebody would take the trouble to
do it, I would very much appreciate it if a proper MsVC project file ,
with appropriate config options (and version resource) could be part of
the distribution. I would do it, only I am not a good user of MSVC so
would probably botch it.

> Is it possible to determine what the version number of the DLL
> (Windows) is? The DLL doesn't seem to contain any version information
> 
> 
> 

RE: [sqlite] ticket 1147

[Cariotoglou Mike]

Sure, an API for returning result set info would be fine, and more convenient 
than these pragmas, which for one thing are stateful, and thus hell for wrapper 
writers, which need to assume that only the *wrapper* may set these pragmas.
Also, in order to avoid API explosion, I feel that a single api function, named 
, say, sqlite3_column_origin, could return all relevant information, such as 
database, table, column names, and as a bonus, primary key and "required" (not 
NULL) flags.
 
Still, and in order not to lose focus on ticket 1147, I reported a *bug* in the 
current implementation. The bug *is* there, and very easy to reproduce. I feel 
that this should either be fixed, or the pragmas removed altogether. 



From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
Sent: Mon 2/28/2005 9:24 PM
To: sqlite-users@sqlite.org
Subject: RE: [sqlite] ticket 1147



On Mon, 2005-02-28 at 11:54 -0700, Robert Simpson wrote: 
> Column Name - The name of the column as specified in the SELECT clause and 
> what SQLite already generates 
> Base Table - The base table the column came from or NULL if the column was 
> computed 
> Base Column - The base column of the table the column came from or NULL if 
> the column was computed 
> Catalog - The database the column came from or NULL if the column was 
> computed. 
> 

OK.  This is progress.  Now I understand that people need the database, 
table, and column that resultset values originate from in order to 
automatically construct an appropriate UPDATE statement.  That makes 
sense. 

Wouldn't it be better to provide this information with a new API 
rather than depend on a column naming convention?  That would avoid 
ambiguity in cases where users create dodgy column names that contain 
characters like space and '.'  If such a new API appears soon, would 
people (please!) stop using those short_column_names and 
long_column_names pragmas? 
-- 
D. Richard Hipp <[EMAIL PROTECTED]> 

[sqlite] ticket 1147

[Cariotoglou Mike]

I have opened a ticket (#1147) for the full_column_names issue, which is back 
in 3.1.3. pls check it out.

also, I noticed the following :

when selecting from a view, and duplicate column names exist, there is an 
attempt to de-dupe them, by adding a sequence number, like this:

ID ID:1 ID:2  etc

this however does not happen with duplicate columns that are returned from a 
query. so,

the statement:

select * from master,detail where detail.masterID=master.ID

and,

create view v1 as  select * from master,detail where detail.masterID=master.ID;
select * from v1;

do not have the same result wrt to column names.

I feel this is inconsistent. you should either de-dupe all result sets (IMHO a 
bad idea), or leave all result sets alone, as far as names is concerned (my 
suggestion).

I understand that this "column names" issue is becoming a pain for the sqlite 
authors, but OTOH, it is very important for wrapper authors...

 

RE: [sqlite] Version 3.1.3 is a headache

[Cariotoglou Mike]

The fact is, the pragmas regarding column names now seem completely
broken, as they do absolutely nothing. Was this by design, or a new bug
?

-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
Sent: Sunday, February 27, 2005 1:08 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Version 3.1.3 is a headache

On Sat, 2005-02-26 at 22:43 +0100, Jakub Adamek wrote:
> Hi, I really love SQLite, but upgrading to 3.1.3 was not a good idea. 
> I have already posted 3 tickets with rather serious problems with 
> column names.
> 
> The last one is really annoying and I can't believe the auto-tests 
> could have missed it ...
> 
>create table a (id, x);
>create table b (id, y);
>insert into a values (1,1);
>insert into b values (1,2);
>select * from a inner join b;
> 
>  column names returned: id,x,id,y How am I supposed to use such 
> column names? Ouwey. No wonder that my C++ wrapper does not want to 
> work with such a result set.
> 

Your tickets are unhelpful and will likely be ignored.  Rather than
complain about the column names, perhaps you can present alternative
suggestions.  Posting what PostgreSQL, Oracle, and MySQL do with the
same queries would be a good start.  Explaining why you think the
current names are undesirable (instead of just saying "Ouwey") would
also be a positive step toward getting the problem addressed.
--
D. Richard Hipp <[EMAIL PROTECTED]>

Re: [sqlite] Feature request

[Cariotoglou Mike]

> At 11:20 AM -0700 2/18/05, Robert Simpson wrote:
> >PRAGMA real_column_names=0|1

YES, PLEASE. I also need this, as I am writing an OLEDB provider

RE: [sqlite] BLOB versus table storage

[Cariotoglou Mike]

I agree. I have done similar work for GIS data. Unless you absolutely
need access to inidvidual x,y data at the *sql* level, it is much better
to use memory storage for these. And, if you plan to do operations like
point-in-polygon, which I am sure you will, you need access to all the
points at the same time, which means that streaming them into a BLOB
will always be much better. One tip: if you can, use a binary storing
technqiue instead of a text//based one. Converting to/from floating
point numbers is a rather expensive operation. So, serialize the points
to a data structure, and write it with one operation.

It helps if you violate some design rules, when you do this. Consider
this:

In Delphi, which is my native language, the *good* design (theoretically
speaking) would be this :

Type

Tpoint=class
 x,
 y:double
End;

Tpolygon=class
 points:tlist;
... Other properties and methods
End;

Or even :

Tpolygon=class
 points:array of tpoint;
End;

However, this design does not serialize wellm as each TPOINT element is
not just data, but has "hidden" pointer that cannot be serialized

Now, consider this:

Tpoint=RECORD
 x,y:double
End;

Tpolygon=class
 points:array of tpoint;
End;

Now, "points" is a sequencial array of records in memory, so you can
stream to/from a blob with a single operation... 

> -Original Message-
> From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, February 17, 2005 5:30 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] BLOB versus table storage
> 
> On Thu, 2005-02-17 at 10:12 -0500, [EMAIL PROTECTED] wrote:
> > Does anyone have a recommendation as to which solution 
> would be more 
> > optimal, both for space and processing time?  In a worst case 
> > scenario, I could insert over 1 million polygons, resulting 
> in a max 
> > 20 million point list table.
> > 
> 
> I think storing the points in a BLOB will likely be much 
> faster and more compact too.  The downside is that you cannot 
> query for polygons by point values (unless, perhaps, you 
> create some custom SQL functions to scan the
> BLOBs.)
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 
> 

RE: [sqlite] VACUUM question

[Cariotoglou Mike]

It has been my experience that, when two processes have the same db
open, and one of them does a VACUUM, and the other tries to update,
The database gets corrupted. Not easy to simulate, but has happened at
least twice, in fact it has been the only way I managed to corrupt a
database.
Comments, DRH ? 

> -Original Message-
> From: D. Richard Hipp [mailto:[EMAIL PROTECTED] 
> Sent: Thursday, February 10, 2005 12:14 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] VACUUM question
> 
> On Wed, 2005-02-09 at 14:56 -0500, Luc Vandal wrote:
> > if my app has a sqlite3* pointer to the database and 
> another app does 
> > a VACUUM on the same database, will the pointer still be valid?
> 
> Yes.
> 
> 
> > 
> > Also, what if, while doing a VACUUM, a query executes on 
> the database?
> > Will it just fail?
> 
> You will get SQLITE_BUSY.
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
> 
> 
> 

RE: [sqlite] Not getting the speed I think is possoble. Basic select statment slow?

[Cariotoglou Mike]

out of curiocity, can you give a try to my delphi wrappers? 
http://www.sqlite.org/contrib  
(not Tdataset replacement, though, but they should be quite fast for this 
reason. also try the "serverCursor" setting, which allows you to step thorugh 
the result set one at a time, meaning there is no double-buffering required:
 
rs:=db.createStatement;
rs.serverCursor:=true;
rs.open
while not rs.eof do..
 
let me know how they fare speed-wise, as I have not tested any other wrappers..



From: Chris Schirlinger [mailto:[EMAIL PROTECTED]
Sent: Thu 3/2/2005 6:06 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Not getting the speed I think is possoble. Basic select 
statment slow?



No, the DLL wasn't the issue (heck the entire program is a DLL anyway
and that's worked well for ages)

We eventually discoverd through profiling the Delphi componants we
had started using were the slow point, tossed them, and tried someone
elses version (some stuff adapted by someone called Tim A.) and they
work MUCH better

It seems the chokepoints where in the wrapper code itself, perhaps in
the mechanisms calling the various SQLite functions. I'd dig deeper
but frankly the wrapper we have now lets me get to the bare bones
easier.

I am pleasently surprised about SQLites power now, for a large single
user DB it works nicely.
Sure, when we tossed a further 8 million rows into the DB it is slows
down, but still ~1 secondish for a 3k select statement

Still trying to wrench every piece of speed I can from the DB, and
some functions don't seem to be working? (or I am misunderstanding
them)

For example:

 CREATE UNIQUE INDEX pk ON myTable (Field1, Field2) ON CONFLICT
REPLACE;

runs but doesn't create the ON CONFLICT part. When you examine the
schema you see :

CREATE UNIQUE INDEX pk ON myTable (Field1, Field2);

Also any INSERT statement evaluate as if the default is still FAIL

Still not really required, INSERT OR REPLACE works well. Was hoping
setting the default CONFLICT handeler may speed things up a bit

> I wouldn't think DLL calling overhead would be significant
> when dealing with things as slow (relatively) as a database.
>
> Is it really necessary for it to be a DLL?
> You might be able to statically link it and remove that overhead.
> Are you using COM or ActiveX to call it? If I remember right
> they had a lot more overhead than a vanilla DLL.
>
> Sounds like a job for the profiler!

RE: [sqlite] foreign keys? (sqlite3)

[Cariotoglou Mike]

Sqlite parser DOES parse foreign key constraints. Try this:

create table t1(
id integer,
id1 integer,
id2 integer,
id3 integer,
foreign key (id1,id2) references anotherTable(id,id1),
foreign key (id3) references somethingElse(id)
);

pragma foreign_key_list(t1);

However, this does not mean that it actually DOES anything with this
info, it is just there for you to act, not for the engine (yet)

 

> -Original Message-
> From: Dick Davies [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, February 01, 2005 3:52 PM
> To: SQLite
> Subject: [sqlite] foreign keys? (sqlite3)
> 
> 
> The docs seem to say sqlite3 does'nt support foreign keys, is 
> that correct?
> 
> If so, I don't understand what 
> 
> ---8<-
> ---
> #
> PRAGMA foreign_key_list(table-name);
> 
> For each foreign key that references a column in the argument 
> table, invoke the callback function with information about 
> that foreign key.
> The callback function will be invoked once for each column in 
> each foreign key.
> ---8<-
> ---
> 
> actually does?
> 
> --
> 'The pie is ready. You guys like swarms of things, right?'
>   -- Bender
> Rasputin :: Jack of All Trades - Master of Nuns
> 
> 
> 

RE: [sqlite] default CURRENT_TIMESTAMP status?

[CARIOTOGLOU MIKE]

Indeed. Searching the source, this has never been implemented. 

> 
> Hrm.  In that case, perhaps I should say "The documentation 
> is broken":
> 
> [ http://www.sqlite.org/lang.html#createtable ]
> 
>   The DEFAULT constraint specifies a default value to use when
>   doing an INSERT. The value may be NULL, a string constant, a
>   number, or one of the special case-independant keywords
>   CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP.
> 

RE: [sqlite] question regarding sqliteExplorer 3.08

[CARIOTOGLOU MIKE]

No, your USER locale must be in hebrew. Is it ? What os are you using ?
Would you mind mailing me the mdb file ?
Also, if you put UTF in the db, sqliteexploirer may gag. But do try it. 

> -Original Message-
> From: Ofer Chesler [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, January 18, 2005 2:53 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] question regarding sqliteExplorer 3.08
> 
> Hi,
> My System locale is already in hebrew, and the string in 
> the sqlite apears as '?', meaning the conversion didn't go well.
>Currently I use only hebrew, but I might use some more 
> languages, so I think I'm going to read it as Unicode, and 
> use the WideCharToMultiByte function with CP_UTF8, and store 
> this string in the DB.
> 
> Ofer
> 
> _
> FREE pop-up blocking with the new MSN Toolbar - get it now! 
> http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/
> 

RE: [sqlite] question regarding sqliteExplorer 3.08

[CARIOTOGLOU MIKE]

Hi. I am the author of this program. I regret to say that this version is
NOT unicode-enabled, so, handling non-ascii data is a bit tricky, but
possible.

1. Since any string read from the MDB (be it ansii or unicode) will be
converted to ANSI (which means 8-bit characters), you can only handle
single-byte character sets. Is hebrew one of them ?
 
2. do you have more than one non-engliesh languages  in the db ? If so, you
cannot do anything. Only one language will work at a time.

3. if only one language, the following should do the trick :
 
 make sure that the USER locale is set to Hebrew. In windows XP , this is
done via Regional options, first tab, "standards and formats". In win2k, the
phrasing is a little different.

If this does not work, go to the Advanced tab, and the SYSTEM locale to
Hebrew also. (requires reboot).

Pls let me/us know what worked and what did not, I am very interested.

BTW, if many people have this issue, I could add the capability to the input
conversion locale in the program. If this is the case, pls send me a sample
database (small) with hebrew in it, and
The expected ansi sequences for a couple of strings, so I can test.

> -Original Message-
> From: Ofer Chesler [mailto:[EMAIL PROTECTED] 
> Sent: Monday, January 17, 2005 2:55 PM
> To: sqlite-users@sqlite.org
> Subject: [sqlite] question regarding sqliteExplorer 3.08
> 
> Hi,
> I tried to use the tool, in order to import my data from mdb file.
> It all worked fine, except from the fact that my strings are 
> in Hebrew, and I got garbage inside the sqlite.
> 
> is the sqliteExplorer uses/can use some uft-8 conversion ?
> 
> 10x,
> 
> Ofer
> 
> _
> Express yourself instantly with MSN Messenger! Download today 
> it's FREE! 
> http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
> 

[sqlite] Bug in subselect

[CARIOTOGLOU MIKE]

This bug is a beauty: text column comparison in joins may follow NUMERIC
rules, even if
the column(s) have text affinity!

consider this case. Tables A,B are a typical one-to-many relation, where B
has N records
for each A, and one field (key) relates them.

CREATE TABLE a(
  key VARCHAR(2),
  data VARCHAR(30)
);
-- this table contains N records for each A, with period being 1..N
CREATE TABLE b(
  key VARCHAR(2),
  period INTEGER
);

insert into a values('01','data01');
insert into a values('+1','data+1');

insert into b values ('01',1);
insert into b values ('01',2);
insert into b values ('+1',3);
insert into b values ('+1',4);

select a.*,a1.*
from a,
(select key,sum(period) from b group by key) as a1
where a.key=a1.key

this select creates a temporary table because of the subselect, and then
joins the two.
the subselect, when run by itself, creates a table of two rows, as expected.
thus, one would
expect to get a result of two rows, like this:

01 data01 01 3
+1 data+1 +1 7

however, 4 rows are returned, like this :

01  data01  +1  7
01  data01  01  3
+1  data+1  +1  7
+1  data+1  01  3

the reason seems to be that '01' and '+1' are considered equal, something
which would be
correct IF column(s) 'key' were of numeric affinity, which they are NOT.

However, if the subselect is saved in a table, and the join is done after, 
the correct result will be returned:

create table x as select key,sum(period) from b group by key

select a.*,x.*
from a,x
where a.key=x.key

01  data01  01  3
+1  data+1  +1  7

I suspect that this bug arises from the fact that the intermediate table
that is created due to
the subselect has no type information, for some reason.

I have created ticket #1047 on this.

RE: [sqlite] What's the difference of "select * from tb" and "sel ect "ID" from tb"?

[CARIOTOGLOU MIKE]

I can verify this :  

Select "columnName" from someTable gives columns with quoted names !
("ColumnName") instead of ColumnName

This *feels* like a bug

> -Original Message-
> From: red forks [mailto:[EMAIL PROTECTED] 
> Sent: Wednesday, December 22, 2004 6:07 AM
> To: [EMAIL PROTECTED]
> Subject: Re: [sqlite] What's the difference of "select * from 
> tb" and "select "ID" from tb"?
> 
> I'm using SQLite 3.08 for Windows, SQLite.Net.
> Sqlite3Explorer(developed by delphi) can also see this situation.
> 

RE: [sqlite] Join function in select statement

[CARIOTOGLOU MIKE]

> -Original Message-
> From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, November 02, 2004 4:49 PM
> To: [EMAIL PROTECTED]
> Subject: Re: [sqlite] Join function in select statement
> 
> 
> CARIOTOGLOU MIKE wrote:
> > Why not provide some extra functionality on one platform ? 
> > 
> 
> Because that's not what SQLite does.  SQLite works the same
> on all platforms.
> 
> Note also that were this policy to change and SQLite were
> allowed to have extra features on selected platforms, it is
> most likely that Unix would get the extra features and windows
> users would be left to fend for themselves, not the other way
> around.
> -- 
> D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565

ok, point taken! I will not argue about this or any similar matter again :)
:)
 

RE: [sqlite] Join function in select statement

[CARIOTOGLOU MIKE]

true. OTOH, since, as you say, it *is* simple, why not provide some extra
functionality on one platform ? it does not affect other platforms, and it
is s nice to have.. it would be an IFDEF thingy, anyway.. of course, you
know best.

> -Original Message-
> From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, November 02, 2004 3:44 PM
> To: [EMAIL PROTECTED]
> Subject: Re: [sqlite] Join function in select statement
> 
> 
> CARIOTOGLOU MIKE wrote:
> > write a user-defined function in your hosting code. the 
> only problem is,
> > people that use your database (as opposed to your code), 
> will not be able to
> > use the function. DRH, what about DLL-loadable functions ? 
> this should be
> > easy to arrange, and would be of enormous help.
> > example :
> > 
> > pragma load_Functions "c:\myfuncs.dll"
> > 
> 
> Loading shared libraries is easy enough on windows and
> Linux, but throw AIX and HPUX and Solaris into the mix
> and suddenly things become more complicated.  SQLite is
> a cross-platform library.  It has to work on more than
> just windows.
> 
> 
> -- 
> D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
> 

[sqlite] new uploads

[CARIOTOGLOU MIKE]

I have uploaded the new version of sqlite3Explorer, which fixes a bug with
the schema treeview.