Re: [sqlite] Problem with floating point fields, and a feature request
Cariotoglou Mike wrote: > > I see again that you all miss the point. I DO know how to handle floating > point. My point is : > ... > > 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. > Now you are missing the point :). The solution is not all that simple. Perhaps the "best" approach is to allow for "fuzzy" comparisons. I refer you to http://ftp.cac.psu.edu/pub/ger/fortran/hdk/eps.f90 (or .for) for more information and an implementation of an algorithm that has proven its usefulness. (Don't take it too lightly: according to the author/transcriber it is the result of long discussions and disputes in at least one language community). Your proposal could be used to set the tolerance for the fuzzy comparison. Scaling issues are then taken care of by that algorithm (I should have thought of it before) Regards, Arjen
Re: [sqlite] Using time and date values
hi maybe the GMT is obsolete. The new time-standart becomes BMT - Biel Mean Time, which is linked up to the Central European Winter/Standard time - which is UTC + 1 hour (aka internet time). regards e. Цитат на писмо от John Stanton <[EMAIL PROTECTED]>: > Brad wrote: > >> I am looking for a simple way of using date and time > values with > >> sqlite3. I have some Ctime classes (VC++.net) which > have to be stored in > >> the database and they should be compared. > > > > > > If the class you're using has .Value property, or a way > to easily > > transform the date from one format to another, you > should probably store > > that. For instance, I like to store dates in string > format, using > > something like "CCYYMMDDHHmmSS.SS". If I expect to > need to deal with > > time zones, I'll store all the dates/times as GMT, or > Universal time, > > and do the conversions when I create the datetime > objects. > > > > > You might consider storing dates and times in ISO9601 > format, and doing > as suggested above, using Zulu time (also known as UTC, > Universal > Co-ordinated Time, formerly GMT). By complying with a > standard and > using UTC you can present the time according to time zone > and daylight > saving status. In a widely distributed system the > complication is > keeping track of daylight saving, but there are databases > and tools > which help. > > The other way to store time is an offset from an epoch. > The problem > there is that there are arbitrary epochs. The best one > is some date BC > which has the magic property that it can be used to > transform the offset > into all of the major world date systems, like Gregorian, > Arabic, > Hebrew, Japanese etc. > > If you application is fairly straightforward the ISO9601 > format has the > great advantage that it is human readable in its raw > form. > JS > > - Коледа е - всеки заслужава подарък. Тази Коледа с всеки хостинг пакет SuperHosting.BG Ви подарява книга и дава шанс за спечелване на 3x MP3 плейъра и мобилен телефон. http://www.superhosting.bg/promo2.adv
RE: [sqlite] Problem with floating point fields, and a feature request
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] Trying to compile under VC.NET
Also be sure to #define THREADSAFE in your project settings if you'll be using multiple threads. From: Mike Marshall [mailto:[EMAIL PROTECTED] Sent: Tue 12/13/2005 2:17 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Trying to compile under VC.NET Just switch off the Using Precompiled Headers directive in the project you are compiling Project -> Properties -> C/C++ -> Precompiled Headers And everything will be AOK Mike M -Original Message- From: Henning Folger [mailto:[EMAIL PROTECTED] Sent: 13 December 2005 08:08 To: sqlite-users@sqlite.org Subject: [sqlite] Trying to compile under VC.NET Hello, I am happy to find sqlite. This backend fully matches my need regarding to a projekt of mine. But i have to make some changes to fit into my project. So i tried to recompile the sources under VC.NET. On the web page i found a howto to do it, but i have no luck with it. I always get the error C1010, unexpected end of file while looking for precompiled headers. Is there anyone out who can help me or can send me an an howto or maybe a dsw-File? Thanks for your help Henning To find out more about Reuters visit www.about.reuters.com Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the views of Reuters Ltd.
Re: [sqlite] Using time and date values
Dennis Cote wrote: John Stanton wrote: Brad wrote: I am looking for a simple way of using date and time values with sqlite3. I have some Ctime classes (VC++.net) which have to be stored in the database and they should be compared. If the class you're using has .Value property, or a way to easily transform the date from one format to another, you should probably store that. For instance, I like to store dates in string format, using something like "CCYYMMDDHHmmSS.SS". If I expect to need to deal with time zones, I'll store all the dates/times as GMT, or Universal time, and do the conversions when I create the datetime objects. You might consider storing dates and times in ISO9601 format, and doing as suggested above, using Zulu time (also known as UTC, Universal Co-ordinated Time, formerly GMT). By complying with a standard and using UTC you can present the time according to time zone and daylight saving status. In a widely distributed system the complication is keeping track of daylight saving, but there are databases and tools which help. The other way to store time is an offset from an epoch. The problem there is that there are arbitrary epochs. The best one is some date BC which has the magic property that it can be used to transform the offset into all of the major world date systems, like Gregorian, Arabic, Hebrew, Japanese etc. If you application is fairly straightforward the ISO9601 format has the great advantage that it is human readable in its raw form. JS This is a good idea, but you have the standard number wrong. It should be ISO 8601. See http://www.iso.org/iso/en/prods-services/popstds/datesandtime.html for more details. HTH Dennis Cote Thankyou for the correction. Here is how you can get ISO8601 time. long clock; char tmstr[128]; struct tm *tma; clock = time(0); tma = localtime(); strftime(tmstr, 128, "%Y-%m-%d", tma);
Re: [sqlite] Using time and date values
John Stanton wrote: Brad wrote: I am looking for a simple way of using date and time values with sqlite3. I have some Ctime classes (VC++.net) which have to be stored in the database and they should be compared. If the class you're using has .Value property, or a way to easily transform the date from one format to another, you should probably store that. For instance, I like to store dates in string format, using something like "CCYYMMDDHHmmSS.SS". If I expect to need to deal with time zones, I'll store all the dates/times as GMT, or Universal time, and do the conversions when I create the datetime objects. You might consider storing dates and times in ISO9601 format, and doing as suggested above, using Zulu time (also known as UTC, Universal Co-ordinated Time, formerly GMT). By complying with a standard and using UTC you can present the time according to time zone and daylight saving status. In a widely distributed system the complication is keeping track of daylight saving, but there are databases and tools which help. The other way to store time is an offset from an epoch. The problem there is that there are arbitrary epochs. The best one is some date BC which has the magic property that it can be used to transform the offset into all of the major world date systems, like Gregorian, Arabic, Hebrew, Japanese etc. If you application is fairly straightforward the ISO9601 format has the great advantage that it is human readable in its raw form. JS This is a good idea, but you have the standard number wrong. It should be ISO 8601. See http://www.iso.org/iso/en/prods-services/popstds/datesandtime.html for more details. HTH Dennis Cote
Re: [sqlite] Using time and date values
Brad wrote: I am looking for a simple way of using date and time values with sqlite3. I have some Ctime classes (VC++.net) which have to be stored in the database and they should be compared. If the class you're using has .Value property, or a way to easily transform the date from one format to another, you should probably store that. For instance, I like to store dates in string format, using something like "CCYYMMDDHHmmSS.SS". If I expect to need to deal with time zones, I'll store all the dates/times as GMT, or Universal time, and do the conversions when I create the datetime objects. You might consider storing dates and times in ISO9601 format, and doing as suggested above, using Zulu time (also known as UTC, Universal Co-ordinated Time, formerly GMT). By complying with a standard and using UTC you can present the time according to time zone and daylight saving status. In a widely distributed system the complication is keeping track of daylight saving, but there are databases and tools which help. The other way to store time is an offset from an epoch. The problem there is that there are arbitrary epochs. The best one is some date BC which has the magic property that it can be used to transform the offset into all of the major world date systems, like Gregorian, Arabic, Hebrew, Japanese etc. If you application is fairly straightforward the ISO9601 format has the great advantage that it is human readable in its raw form. JS
Re: [sqlite] Using time and date values
I am looking for a simple way of using date and time values with sqlite3. I have some Ctime classes (VC++.net) which have to be stored in the database and they should be compared. If the class you're using has .Value property, or a way to easily transform the date from one format to another, you should probably store that. For instance, I like to store dates in string format, using something like "CCYYMMDDHHmmSS.SS". If I expect to need to deal with time zones, I'll store all the dates/times as GMT, or Universal time, and do the conversions when I create the datetime objects.
Re[2]: [sqlite] Problem with floating point fields, and a feature request
Hello Dave, Perhaps I'd have said it's "poor practice" to compare floating point numbers that way. I think you're right but, the wording changes make an attack into valid criticism. C Wednesday, December 14, 2005, 11:58:11 AM, you wrote: >>> >>>select * from test where f=13.06; -- returns no data DD> Pardon me for throwing a bomb, but no good programmer DD> would ever use = to compare floating point numbers. DD> Choose a more appropriate representation for your data. -- Best regards, Tegmailto:[EMAIL PROTECTED]
Re: [sqlite] Using time and date values
Hello Henning, CTime will give you access to the "time_t" field. I just store the time_t in the DB. It's an unsigned long that represents seconds since Jan 1, 1970. You can feed a time_t back into CTime to initialize it. Be aware though that Microsoft is changing what "time(NULL)" returns. I read a warning in the SDK that says, they're changing it to seconds since some time in the 1500's (no joke). It apparently only affects VC7 and above. I'm speaking of the MFC CTime class. Hopefully .net implements the same interface. C. Wednesday, December 14, 2005, 11:15:27 AM, you wrote: HF> Hi, HF> I am looking for a simple way of using date and time values with HF> sqlite3. I have some Ctime classes (VC++.net) which have to be stored in HF> the database and they should be compared. HF> Anyone has an idea? HF> Henning -- Best regards, Tegmailto:[EMAIL PROTECTED]
RE: [sqlite] Problem with floating point fields, and a feature request
>> >>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.
Re: [sqlite] Final Year Project/Dissertation help required!!!!
Eric Bohlman wrote:
m christensen wrote:
What you are doing is needs analysis and by definition requires
'help' or input from others. This is not doing YOUR work for you.
On the other hand needs analysis is much more complex than just
asking users what they want.
Most of the time they simply don't know.
Sometimes, and much lest often than some arrogant developers think,
they are wrong about what they really need.
Sometimes you need to stir the pot some to get people thinking.
Sometimes need to show them potential options to get them thinking.
Definitely true. Developers often fall into the false dichotomy of
assuming that software design means implementing either a
user/marketing wishlist or a lonergeek's personal idea of what's
best. The former almost never works and the latter works well in some
very limited domains but poorly everywhere else.
Proper needs analysis requires:
-- Identifying the users ("customers").
-- *Understanding* the *tasks* the users need to accomplish, and
understanding them first in task-oriented terms ("business processes")
rather than implementation-oriented terms. Otherwise you wind up with
"XY problems" where what's really needed is a way to accomplish task
X, but the developer/users/both prematurely decide that the way to do
it is with tool/implementation Y, and the focus shifts away from the
actual tasks.
-- Learning how the users currently accomplish their tasks.
-- Learning in what ways the users' currently method of accomplishing
their tasks fails to meet their needs. This is more than just asking
for wishlists. It requires working with the users, who may not be
able to immediately articulate their needs. One of the most important
aspects of this phase is recognizing what *doesn't* need to be
changed. It is also an almost certainty that simply trying to
automate an existing manual process will be unproductive and merely
increase complexity.
Note that all this does require some "social skills" such as listening
and perspective taking, which puts off some geeks. But it does *not*
require a bubbly, glib, extroverted personality, and the assumption
that it does is really just an excuse for not doing the work. It's
really just a matter of disciplining one's mind, comparable to
disciplining oneself to finish designing an interface before diving
into the implementation. The lack of such discipline leads to
interfaces that are organized around the implementation rather than
vice-versa.
In Mr. Newby's case, the first step really should be to see what's
currently being done with GUIs for SQLite; what's out there, and how
do they differ? Some of the acrimony in this thread came about
because he skipped that step. Then the next step should be asking
who's using what tools, what tasks do they use them for, why did they
choose them, what do they do well, what do they do poorly, etc.
Along those lines
I do use SQLiteSpy. It is handy for quick high level poking around in
the database.
Honestly my major use is to hand it and SQLite databases off to end-users.
I'm the QA manager on an Oracle Project.
I have test scenarios that hit almost distinct 2,000 columns in a large
automated test scheme.
I use the Issue tracking system 'Trac' which used SQLite as the database.
Most of my test harness is written in Perl, the data is actually pulled
from Oracle into packed records in SQLite and the tests
hammer on them locally. This is about 4 times faster that working in
oracle ;-).
The test results and logs are written into SQLite databases and are then
checked into a Subversion revision control repository.
Trac Tickets are built that point to those database files in the repository.
This gives me a static and self contained snapshot of the data, logs and
test results.
My end-users are analysists and developers.
Our project is a database-centric back-office app and EVERYONE knows and
uses SQL heavily on a
daily basis.
BUT most of them use TOAD as a client and although they type complex SQL
statements in all day long,
they are used to the results coming back in a table format they can
scroll thru and cut-and-paste.
Some of them almost had a stroke when I told them Yes, it's in a
database, but it's not Oracle.
SQLiteSpy gave me the ability to tell them to open the Trac ticket and
double click on the logs and up
they come with a GUI. (No retraining required ;-) )
Marc
[sqlite]about sqlite3_get_table()
Hi,
what is the difference between sqlite3.exe (the command-line
shell) and an application which calls sqlite3_get_table()?
Because the same sql script gives different results! :-)
the script:
In sqlite327.exe:
CREATE TABLE accounts (us TEXT, pw TEXT, md5 TEXT, UNIQUE
(us));
INSERT INTO accounts VALUES('guest','guest',NULL);
INSERT INTO accounts VALUES('ivan','alpha',NULL);
INSERT INTO accounts VALUES('Administrator','',NULL);
and now:
(in sqlite327.exe)
SELECT us, pw FROM accounts WHERE us == 'ivan' OR pw ==
'alpha';
gives
ivan|alpha
but the app (sqlite3_get_table()) called with the same sql
cmd
returns only the header
us|pw
and with sql := "SELECT us, pw FROM accounts WHERE us ==
\'ivan\';"
it works ok (same as sqlite327.exe), gives:
us|pw
ivan|alpha
so why the OR breaks the query (usualy .true. OR .any. ==
.true.) ???
any idea?
regards
emily
-
Коледа е - всеки заслужава подарък. Тази Коледа с всеки хостинг пакет
SuperHosting.BG
Ви подарява книга и дава шанс за спечелване на 3x MP3 плейъра и мобилен телефон.
http://www.superhosting.bg/promo2.adv
Re: [sqlite] Final Year Project/Dissertation help required!!!!
m christensen wrote:
What you are doing is needs analysis and by definition requires 'help'
or input from others. This is not doing YOUR work for you.
On the other hand needs analysis is much more complex than just asking
users what they want.
Most of the time they simply don't know.
Sometimes, and much lest often than some arrogant developers think, they
are wrong about what they really need.
Sometimes you need to stir the pot some to get people thinking.
Sometimes need to show them potential options to get them thinking.
Definitely true. Developers often fall into the false dichotomy of
assuming that software design means implementing either a user/marketing
wishlist or a lonergeek's personal idea of what's best. The former
almost never works and the latter works well in some very limited
domains but poorly everywhere else.
Proper needs analysis requires:
-- Identifying the users ("customers").
-- *Understanding* the *tasks* the users need to accomplish, and
understanding them first in task-oriented terms ("business processes")
rather than implementation-oriented terms. Otherwise you wind up with
"XY problems" where what's really needed is a way to accomplish task X,
but the developer/users/both prematurely decide that the way to do it is
with tool/implementation Y, and the focus shifts away from the actual tasks.
-- Learning how the users currently accomplish their tasks.
-- Learning in what ways the users' currently method of accomplishing
their tasks fails to meet their needs. This is more than just asking
for wishlists. It requires working with the users, who may not be able
to immediately articulate their needs. One of the most important
aspects of this phase is recognizing what *doesn't* need to be changed.
It is also an almost certainty that simply trying to automate an
existing manual process will be unproductive and merely increase complexity.
Note that all this does require some "social skills" such as listening
and perspective taking, which puts off some geeks. But it does *not*
require a bubbly, glib, extroverted personality, and the assumption that
it does is really just an excuse for not doing the work. It's really
just a matter of disciplining one's mind, comparable to disciplining
oneself to finish designing an interface before diving into the
implementation. The lack of such discipline leads to interfaces that
are organized around the implementation rather than vice-versa.
In Mr. Newby's case, the first step really should be to see what's
currently being done with GUIs for SQLite; what's out there, and how do
they differ? Some of the acrimony in this thread came about because he
skipped that step. Then the next step should be asking who's using what
tools, what tasks do they use them for, why did they choose them, what
do they do well, what do they do poorly, etc.
[sqlite] Using time and date values
Hi, I am looking for a simple way of using date and time values with sqlite3. I have some Ctime classes (VC++.net) which have to be stored in the database and they should be compared. Anyone has an idea? Henning
RE: [sqlite] equivalent of linked tables in sqlite ?
I missed any reference to Pocket PC. Although there is a look alike Delphi product for PalmOS there is no support for the Pocket PC. Unfortunately your only options for Pocket PC seem to be something from Mickeysoft probably using ODBC or some other kludge for some of your data (anything non Microsoft.) That has been one of Delphi's strengths in the past, connectivity to ANY data source, but Microsoft is slowly wearing Borland down and that is not a "good thing", IMHO. Fred > -Original Message- > From: gl demoor [mailto:[EMAIL PROTECTED] > Sent: Tuesday, December 13, 2005 6:47 PM > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] equivalent of linked tables in sqlite ? > > > I did some preliminary and extensive reseach (cough google cough :) on > delphi and pocketpc > > unless I'm missing something, delphi is not use for pocketpc > programming > > google results were of the following nature: > >I'm pretty sure no one supports *Delphi* for *Pocket PC*, so > you will need > >to switch to another language. Your choices are C, C++, C#, and VB > (Excerpt usenet message aug 2 05, by a Robert Zaret, eMVP) > > I hope to use the same (type) language for desktop and ppc > programmation. > The most likely candidate seems evc++ & vc++. (I still have > to learn it) > even more so because the intended tool calls for lowlevel > access to the > serial port on the pocketpc (Dallas Ibutton interfacing). > ... >
Re: [sqlite] Problem with floating point fields, and a feature request
On 12/14/05, Cariotoglou Mike <[EMAIL PROTECTED]> wrote: > 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 found that same bug when comparing dates that microsoft was storing as floating point numbers. It's a problem with the programmer not understanding how floating point works, it's not a problem with any particular language.
RE: [sqlite] weird parser crash
> If you would just submit the particular query that is causing
> the problem, that would be an enormous help. If you can also
> include the schema for your database, so much the better.
Statement failing in sqlite3_prepare is:
"UPDATE SDDSTORE SET SDD = ?, TIMESTAMP = DATETIME('NOW','LOCALTIME') WHERE
ECORDERID = ?"
Schema is:
CREATE TABLE AMENDS (KEY VARCHAR PRIMARY KEY, VALUE INTEGER);
CREATE TABLE EXECID (KEY VARCHAR PRIMARY KEY, VALUE INTEGER);
CREATE TABLE PERSISTEDID (NAME VARCHAR, VALUE VARCHAR);
CREATE TABLE QUOTES (KEY VARCHAR PRIMARY KEY, VALUE INTEGER);
CREATE TABLE SDDSTORE (ECORDERID VARCHAR PRIMARY KEY, TIMESTAMP DATE, SDD BLOB);
I've added these to ticket 1557.
Strangely this only occurs the first time the statement is prepared. When the
process
crashes and re-starts, subsequent prepares/steps are fine. I'm guessing its
thread related
so I'll try it with a debug version of the sqlite3 library.
Thanks,
Daniel.
==
Please access the attached hyperlink for an important electronic communications
disclaimer:
http://www.csfb.com/legal_terms/disclaimer_external_email.shtml
==
Re: [sqlite] equivalent of linked tables in sqlite ?
thank you both Arjen Markus & John Oliva I'll try VB - appforge as I know vb reasonably well. Tcl/tk and others will have to wait glenn John Oliva wrote: You might want to look at using AppForge (www.appforge.com) which takes a slightly restricted version of VB6, VB.NET and C# and can produce output for many handheld devices (including PPC). Also, you can program in .NET for the desktop and .NET compact framework for the Pocket PC. Regards, John Oliva www.danicsystems.com I hope to use the same (type) language for desktop and ppc programmation. The most likely candidate seems evc++ & vc++. (I still have to learn it) even more so because the intended tool calls for lowlevel access to the serial port on the pocketpc (Dallas Ibutton interfacing). I'm however concerned about the database connectivity. On a previous attempt, I bought Sybase PocketBuilder on sybase's reputation of being proficient at different db formats. Only to find out dbase, foxpro support and such was scrapped from pocketbuilder. glenn
Re: [sqlite] weird parser crash
"Shields, Daniel" <[EMAIL PROTECTED]> wrote: > > I will try and provide a script to reproduce the problem but > I can't see how to script a prepare/step construct when it is > not available in the SQL syntax. > If you would just submit the particular query that is causing the problem, that would be an enormous help. If you can also include the schema for your database, so much the better. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Problem with floating point fields, and a feature request
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 Using MS SQL Server 2000, substituting the "float" type (with a range of -1.79E+308 through 1.79E+308) for your double, I get the same results, i.e. no data returned, because the total ends up being 13.059 instead of 13.06. can you imagine how many bugs waiting to happen are out there, because of code like this ? Quite a few, for programmers who don't understand the nature of computers and floating point numbers. however, these are not applicable to people using SQLITE via wrappers that generate their own UUPDATE code. pragma floating_accuracy=0.001 What about people using MS SQL Server via a wrapper? Should SQL Server, and every other database that exhibts this problem, implement your fix? Am I the only one that sees the problem? if not, please speak up, and maybe we can get a neat solution! I'm not convinced that a wrapper should be hiding this type of thing from the user. It's a part of the underlying DB engine, and the user needs to be aware of it. YMMV.
RE: [sqlite] Problem with floating point fields, and a feature request
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] weird parser crash
"Shields, Daniel" <[EMAIL PROTECTED]> wrote: > I have a process that consistently crashes preparing a fairly innocuous > statement. > Has anyone seen anything similar? Any suggestions for a fix/workaround? The > stack > trace of the problem follows. > You are, it seems, the person who posted ticket #1557. A stack trace is of little to no help in fixing a problem like this. What is needed is a script that when feed into the "sqlite" command-line shell will reproduce the problem. Send in such a script (or added it to ticket #1557) and you will find the problem will get addressed *much* faster. -- D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] weird parser crash
> You are, it seems, the person who posted ticket #1557. > > A stack trace is of little to no help in fixing a problem > like this. What is needed is a script that when feed into > the "sqlite" command-line shell will reproduce the problem. > Send in such a script (or added it to ticket #1557) and you > will find the problem will get addressed *much* faster. > I will try and provide a script to reproduce the problem but I can't see how to script a prepare/step construct when it is not available in the SQL syntax. Daniel. == Please access the attached hyperlink for an important electronic communications disclaimer: http://www.csfb.com/legal_terms/disclaimer_external_email.shtml ==
RE: [sqlite] Final Year Project/Dissertation help required!!!!
Hi Clay, thanks for this, I've found it on the net and will certainly have a look at the features it provides, as for purchasing it, I don't have two pennies to rub together at the moment but should be able to get enough information regarding the product without purchasing it though. Thanks again for your time and comments. John. -Original Message- From: Clay Dowling [mailto:[EMAIL PROTECTED] Sent: 14 December 2005 15:29 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Final Year Project/Dissertation help required John, There's a package often found in the discount aisles of computer stores called My Database. My father (who doesn't pretend to know anything about databases or programming) uses it for his business and personal uses. The tool looked absolutely brilliant from the perspective of making it easy for a non-database person to store their data in a logical way. You might want to examine that project as well. Clay -- Simple Content Management http://www.ceamus.com -- This message has been scanned for viruses and dangerous content by the NorMAN MailScanner Service and is believed to be clean. The NorMAN MailScanner Service is operated by Information Systems and Services, University of Newcastle upon Tyne. This e-mail is intended solely for the addressee. It may contain private and confidential information. If you are not the intended addressee, please take no action based on it nor show a copy to anyone. Please reply to this e-mail to highlight the error. You should also be aware that all electronic mail from, to, or within Northumbria University may be the subject of a request under the Freedom of Information Act 2000 and related legislation, and therefore may be required to be disclosed to third parties. This e-mail and attachments have been scanned for viruses prior to leaving Northumbria University. Northumbria University will not be liable for any losses as a result of any viruses being passed on.
Re: [sqlite] Problem with floating point fields, and a feature request
Cariotoglou Mike wrote: > > 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! Well, I have come across it in many other contexts and there is no general solution, unfortunately - well, using decimal floating-point arithmetic might in some cases solve it (at least give more predictable results, though not always). What about the scale? I assume that the above accuracy refers to the range [value-0.001,value+0.001]. That would fail with numbers smaller than 1/100 - not that uncommon. On the other hand a relative accuracy of say 1.0e-4% would fail if you have numbers around zero: -0.001 could well be approximately 0 or 0.001. I think you will need (at least?) two measures of tolerance: an absolute value and a relative one ... Regards, Arjen
RE: [sqlite] Final Year Project/Dissertation help required!!!!
Hi Marc, yes you were clear in your post, I know that you came to my defence and if it wasn't for yours and Juan's comments this thread would most certainly have died a death by now but I am continually receiving excellent comments from people. I would like to thank you personally aswell Marc, your comments have been invaluable to the direction in which my project has taken over the past few days. I am currently looking at GUIs to other SQL databases apart from SQLite, namely MySQL, PostgreSQL and MS Access(this is my non-open-source, non-cross-platform database that I will evaluate). Thank you for suggesting Toad, it is a GUI to MySQL which is one I am investigation so will come in handy. I'd like to take this opportunity to thank you all again and any other suggestions/comments would be gratefully appreciated. Thanks. John. -Original Message- From: m christensen [mailto:[EMAIL PROTECTED] Sent: 13 December 2005 18:02 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Final Year Project/Dissertation help required I hope I was clear in my post. 'I' didn't see your post as asking US to do YOUR homework. What you are doing is needs analysis and by definition requires 'help' or input from others. This is not doing YOUR work for you. On the other hand needs analysis is much more complex than just asking users what they want. Most of the time they simply don't know. Sometimes, and much lest often than some arrogant developers think, they are wrong about what they really need. Sometimes you need to stir the pot some to get people thinking. Sometimes need to show them potential options to get them thinking. Go back and look at the thread. It was about to die out until Juan called you a cheat and I 'defended' you by saying you just didn't know what you were doing.. ;-). (Sorry Juan, I know you didn't say 'cheat', that's MY emphasis...) Very often the process of getting user input is just like this. Sometimes we spend more time trying to engage the users in a dialog and playing politics that actual technical analysis. It goes with the job, those skills are just as important in the real world as the technical analysis. Looking at existing or similar products is also more than just an exercise. You need to USE them. Figure out What Works, What Sucks and Why and learn from man-years of other peoples work AND mistakes. I'd suggest you look at OTHER database GUI interfaces as well. I'd highly suggest you look as a tool called 'TOAD'. There are versions for Oracle, SQL Server and I think one of the open-source databases. Steal ideas, concepts and copy 'what works' that is how the industry works, and why not. Is is 'Stealing' to start with work done by Newton or Pascal rather then reinvent the wheel??? IF it were me, and IF there is a requirement (or suggestion) that you build something that doesn't exist. I'd finish your analysis of existing GUIs. DOCUMENT, in your paper What you did and why. Which of the following do you think would get high marks and which one tags you as a slacker... I was assigned a project to build a GUI for SQLite, but there was one, so I did X instead. or Initial needs analysis shows 5 existing GUI interfaces for SQLite. They are A, B, C, D, E. (With REAL details, authors, vendors, etc...) These products range in price from free for products A, C & D to $129.95 for product B. They run under the following operating systems... They have the following common and distinct features... They appear to have a following or market penetration of X Analysis shows the market/need/niche for a GUI for SQLite to be less than initially expected. Investigation and analysis related to the initial product direction DID however turn up several needs/gaps/potential product niches that warranted further investigation. Further investigation showed a real need for a GUI reporting tool to enable developers and end-users to quickly and accurately develop enterprise class reports and logs from embedded applications. I decided to provide a library and framework to allow an integrated or stand-alone reporting tool for SQLite databases. Even IF there is a 'rule' you need to write something unique and had to switch because a similar tool exists, I would make no mention of the fact. I WOULD talk about 'the product', needs, demand, market and existing products instead. It shows you are looking at this from a real-world perspective rather than 'a class assignment'. Then again some profs. think university IS the real-world... ;-). I classify this a the difference between a student vs. a professional, a coder vs. an application developer. Just like in the real world, I'd keep your supervisor 'in the loop' if you need to change direction, I'd also talk to him in terms as shown above for the reasoning behind the switch and get 'sign-off'. I'd also let him know it took 'a lot of searching, but you found some GUIs already exist', sounds better than 'there are like a hundred already, what
RE: [sqlite] Final Year Project/Dissertation help required!!!!
Hi Gerry, thanks for you comments and words of luck, I will certainly need them. Keep all your comments coming people. Thanks again. John. -Original Message- From: Gerry Snyder [mailto:[EMAIL PROTECTED] Sent: 13 December 2005 18:07 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Final Year Project/Dissertation help required Robert L Cochran wrote: > It's better to learn something new than to sit in a lawn chair > swatting flies! This thread has been interesting to follow, since one of my main uses for computers is finding projects to work on in a futile but enjoyable attempt to keep my brain from ossifying. The latest has been writing a GUI for sqlite in Tcl/Tk. It started when in my musings with sqlite I realized I couldn't remember the names of my tables and the fields in them, and always had to have two instances of the CLI program and/or Tcl open; one for the work at hand and one to show the structure of the file. So the first need was for a window to show that. Of course it had to be able to open an existing file or create a new one. Since my understanding of my needs kept growing so did my data tables, and I added a complete Alter Table function. This is one of the biggest hunks of the script. A much smaller one is used to add a new table. Several of my tables have optional fields at the end, not always supplied by the input source, so I wrote an Import function, with user-defined delimiters, which has the option of rejecting or padding input lines which have an insufficient number of fields. As I did things in various directories I kept losing little Tcl routines I used for various functions, so I added the capability to store them in and execute them from the database. [puts] and [gets] were redefioned to work in the Tk environment. There are also windows for executing single lines of SQL or Tcl. When I got tired of the data displays not being aligned I started using tktable. This allowed spreadsheet-like editing of data--before that I had used stand-alone editing windows. When I decided to bite the bullet and make the move from SQLite2 to SQLite3 I added a conversion function (bidirectional). About the only other thing it does, and very poorly at that, is a Help function, which currently merely lists a text file. I wrote my own because I hate using a tool that does _almost_ everything I want, but needs help from another program or two to finish the task. So, my job has been much easier than Mr. Newby's. I had the luxury of creating things as needed, no need to implement the desire of others, and, at least most of the time, doing it on my own schedule. Good luck Mr. Newby, and have fun. SQLite is a wonderful environment, and this list is a great resource. Gerry -- This message has been scanned for viruses and dangerous content by the NorMAN MailScanner Service and is believed to be clean. The NorMAN MailScanner Service is operated by Information Systems and Services, University of Newcastle upon Tyne. This e-mail is intended solely for the addressee. It may contain private and confidential information. If you are not the intended addressee, please take no action based on it nor show a copy to anyone. Please reply to this e-mail to highlight the error. You should also be aware that all electronic mail from, to, or within Northumbria University may be the subject of a request under the Freedom of Information Act 2000 and related legislation, and therefore may be required to be disclosed to third parties. This e-mail and attachments have been scanned for viruses prior to leaving Northumbria University. Northumbria University will not be liable for any losses as a result of any viruses being passed on.
RE: [sqlite] Final Year Project/Dissertation help required!!!!
John, There's a package often found in the discount aisles of computer stores called My Database. My father (who doesn't pretend to know anything about databases or programming) uses it for his business and personal uses. The tool looked absolutely brilliant from the perspective of making it easy for a non-database person to store their data in a logical way. You might want to examine that project as well. Clay -- Simple Content Management http://www.ceamus.com
[sqlite] weird parser crash
I have a process that consistently crashes preparing a fairly innocuous
statement.
Has anyone seen anything similar? Any suggestions for a fix/workaround? The
stack
trace of the problem follows.
Thanks,
Daniel.
[EMAIL PROTECTED] ([EMAIL PROTECTED]) terminated by signal SEGV (no
mapping at the fault address)
Current function is nameResolverStep
1039 if( ExprHasAnyProperty(pExpr, EP_Resolved) ) return 1;
(dbx 1) where
current thread: [EMAIL PROTECTED]
=>[1] nameResolverStep(pArg = 0xfda0b5ec, pExpr = 0x70), line 1039 in
"expr.c"
[2] walkExprTree(pExpr = 0x70, xFunc = 0xff2b6a48 =
&`libsqlite3.so.0`expr.c`nameResolverStep(void *pArg, struct Expr *pExpr), pArg
= 0xfda0b5ec), line 615 in "expr.c"
[3] walkExprList(p = 0x675610, xFunc = 0xff2b6a48 =
&`libsqlite3.so.0`expr.c`nameResolverStep(void *pArg, struct Expr *pExpr), pArg
= 0xfda0b5ec), line 632 in "expr.c"
[4] walkExprTree(pExpr = 0x67c538, xFunc = 0xff2b6a48 =
&`libsqlite3.so.0`expr.c`nameResolverStep(void *pArg, struct Expr *pExpr), pArg
= 0xfda0b5ec), line 619 in "expr.c"
[5] nameResolverStep(pArg = 0xfda0b5ec, pExpr = 0x685df0), line 1136
in "expr.c"
[6] walkExprTree(pExpr = 0x685df0, xFunc = 0xff2b6a48 =
&`libsqlite3.so.0`expr.c`nameResolverStep(void *pArg, struct Expr *pExpr), pArg
= 0xfda0b5ec), line 615 in "expr.c"
[7] sqlite3ExprResolveNames(pNC = 0xfda0b5ec, pExpr = 0x685df0), line
1188 in "expr.c"
[8] sqlite3Update(pParse = 0xfda0b898, pTabList = 0x67a4f0, pChanges
= 0x67a2c0, pWhere = 0x685ec8, onError = 99), line 165 in "update.c"
[9] yy_reduce(yypParser = 0x6855c0, yyruleno = 158), line 549 in
"parse.y"
[10] sqlite3Parser(yyp = 0x6855c0, yymajor = 10, yyminor = RECORD,
pParse = 0xfda0b898), line 3270 in "parse.c"
[11] sqlite3RunParser(pParse = 0xfda0b898, zSql = 0xfda0b9f8 "UPDATE
SDDSTORE SET SDD = ?, TIMESTAMP = DATETIME('NOW','LOCALTIME') WHERE ECORDERID =
?", pzErrMsg = 0xfda0b894), line 399 in "tokenize.c"
[12] sqlite3_prepare(db = 0x10a590, zSql = 0xfda0b9f8 "UPDATE
SDDSTORE SET SDD = ?, TIMESTAMP = DATETIME('NOW','LOCALTIME') WHERE ECORDERID =
?", nBytes = -1, ppStmt = 0xfda0bd28, pzTail = (nil)), line 435 in "prepare.c"
[13] 0xfeb9ea88(0xfec45cf8, 0xfda0bd20, 0x67f0f0, 0xfec45c98, 0x3000,
0xfec45c80), at 0xfeb9ea87
[14] 0xfeb9d4fc(0x0, 0xfec45d30, 0x, 0x67f0f0, 0xfec45cc0,
0x1), at 0xfeb9d4fb
Daniel Shields
+44(0)207 888 9248
[EMAIL PROTECTED]
CREDIT | FIRST
SUISSE | BOSTON
==
Please access the attached hyperlink for an important electronic communications
disclaimer:
http://www.csfb.com/legal_terms/disclaimer_external_email.shtml
==
RE: [sqlite] equivalent of linked tables in sqlite ?
You might want to look at using AppForge (www.appforge.com) which takes a slightly restricted version of VB6, VB.NET and C# and can produce output for many handheld devices (including PPC). Also, you can program in .NET for the desktop and .NET compact framework for the Pocket PC. Regards, John Oliva www.danicsystems.com > I hope to use the same (type) language for desktop and ppc > programmation. The most likely candidate seems evc++ & vc++. > (I still have to learn it) even more so because the intended > tool calls for lowlevel access to the serial port on the > pocketpc (Dallas Ibutton interfacing). > > I'm however concerned about the database connectivity. On a > previous attempt, I bought Sybase PocketBuilder on sybase's > reputation of being proficient at different db formats. Only > to find out dbase, foxpro support and such was scrapped from > pocketbuilder. > > glenn
