[sqlite] incorrect where clause does not throw error
Porting an application to sqlite3.6.13 on Linux i made a mistake creating a illegal query on a character field: select * from Test where Remark = NULL select * from Test where Remark NULL I was confused as there were neither data nor an error. Shouldn't this cause at least an error message? With the correct queries select * from Test where Remark is NULL select * from Test where Remark not NULL everything is fine -- _ Stefan G. R. Finzel, Dipl. Phys. (Univ.) Ochsenkopfstr. 5, D-95478 Kemnath, +49 (0)9642 704448 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] no download link to tclsqlite-3_3_0.zip
Hi, there is no link on the SQLite download page for *Precompiled Binaries For Windows to download tclsqlite-3_3_0.zip (sqlitedll with TCL bindings). Please can anyone provide the community / me with **tclsqlite-3_3_0.zip as ** I have no access to a Microsoft OS/Compiler? TIA Stefan * -- Stefan G. R. Finzel, Dipl. Phys. (Univ.) Ochsenkopfstr. 5, D-95478 Kemnath, 09642/704448
Re: [sqlite] Re: philosophy behind public domain?
As a german citizen I'll try you explain my understanding of my countries law. The basic concept should be similar within central Europe (Austria, France, Italy, Spain ... but not Great Britain) as most countries laws evolved from the Roman law . Sorry i am not a lawyer, just a programmer concerned with this question while living from his work but also giving parts of this work back to the community. We have two different parts. One is called 'Urheberrecht' (right of author) and the other Entscheidungs- and/or Verwertungsrecht (right to decide of usage and right to use) . First one just handles the mental ownership of a piece of work. This can not be given to another party. In many cases this is worth nothing as is just bundles your name with your work. Very often this right is incorrectly translated as Copyright even in European countries. Second one handles the commercial and economical aspects. Of cause this is something total different. If you get paid for your work, you sometimes loose this rights to your customer or employer immediatly. If you still have this rights by your own you are able to give/license/sell them like every material thing. As I unterstand the american way the customer or employer get the unrestricted usage rights under almost all circumstances. Additionally the author seems to have no right to be mentioned at all. Do not worry in casse an author tells you he gives you the right to use, to decide how to use AND(!!!) the right to modify it. You have all neccessary rights, except to remove the authors name (if it was there before!!!). Although this is just for the authors reputation/prestige even big companies have been accuessed to put back the name, to pay for unauthorized removal or stop usage immediatly. Once again note, in Germany the right to modify code does not include removing the authors name. Now most germans seems to accept the common GPL and BSD like copyrights. But I have problems understanding many restrictions/variatons of proprietary copyrights and just do not accept and use them. In cases german citizens accepted a foreign license model ot contribute software, it would be nearly impossible to involve a German court whether for license nor for warranty aspects. Stefan Finzel D. Richard Hipp wrote: On Fri, 2005-06-03 at 21:01 +0200, Andreas Rottmann wrote: There is no such thing as disclaiming copyright in Europe (or at least Germany and Austria). Rotty This would be a problem for any citizen of Germany or Austria that wanted to contribute code to the SQLite project. I cannot see that this would ever be a problem for an SQLite users. Can citizens of Germany and Austria assign their copyright interest to third parties? If so, then if you want to contribute code to SQLite, just assign the copyright to me and I will then dedicate the code to the public domain, which I can do since I am not a citizen of Austria or Germany. If citizens of Germany and Austria are not allowed to assign copyright, then you will not be allowed to contribute code to SQLite regardless of what license SQLite uses. Either way, the fact that SQLite has been dedicated to the public domain seems unimportant.
Re: [sqlite] 50MB Size Limit?
What about the os shells limit? Look at commands limit/ulimit/unlimit G. Roderick Singleton wrote: On Mon, 2005-04-11 at 12:05 -0400, Jonathan Zdziarski wrote: D. Richard Hipp wrote: Are you sure your users are not, in fact, filling up their disk drives? nope, plenty of free space on the drives. The 50MB limit seems to be very exact as well...exactly 51,200,000 bytes. I'm stumped too. quotas?
Re: [sqlite] NULL representation/empty value in query results?
Yeah! I've missed the default option. But is this SQL standard? And it does not seem to work for me at all. I still can't differ empty and NULL; create table deftest (k integer primary key, i integer default 'NULL', s char default 'NULL'); sqlite insert into deftest (i, s) values (NULL,''); sqlite select * from deftest; 1|| sqlite So we are able to create empty values and NULL by an INSERT. Why shouldn't we a simple way to get it back by an SELECT too? AlthoughTcl itself has no NULL value it would be nice to define a string that is given back by sqlite itself in cases where NULL values exist. I am still hoping there is something usable and/or configurable like PRAGMA null_string='NULL'; or PRAGMA null_string='NAN'; which can be set before executing a select statement. Regards Kurt Welgehausen wrote: Is there a way to change the NULL representation ... ? No. Tcl has no null value. Usually this is not a problem, but if you really need to distinguish between a missing value and an empty string, you can use default values. sqlite create table deftest (k integer primary key, ... i integer default '?', ... s char default '??'); sqlite insert into deftest (i) values (11); sqlite insert into deftest (i, s) values (22, ''); sqlite select * from deftest; k i s -- -- -- 1 11 ?? 2 22 Regards
Re: [sqlite] NULL representation/empty value in query results?
Hello Jay, Yes, that is the expected behaviour. But that is not my problem. Within sqlite console there is a a command .nullvalue NULL All I want is to know how I can set and use this mechanismen from my Tcl interpreter too. Of cause it would be possible to misuse the default setting to get the required NULL. But there are plenty thousands of lines of codes and backups of dynamic tables containing NULLs all over the world. And some of them just need to differ NULL and empty value while iterating of datas containing NULLs in several columns. To get really paranoic there are even columns containing NULL and the string 'NULL'. Now this seems to be the last step porting from mSQL to SQLite. For my code it is no problem to use the default option, it could be done in a few minutes. But nor the world wide code base neither the backups can't be changed. And so far, we already used a mechanismen setting the NULL string and it worked well for more than ten years and therefore is established. Ok , can this be done regularly or do I have to make a customized version of SQLite? Regards
Re: [sqlite] NULL representation/empty value in query results?
Although Tcl does not know NULL at all, sqlite does. From my current unterstanding there are five different fundamental internal datatypes. One of them is SQLITE_NULL. But SQLITE_NULL is not used at all while iterating over the results of dbcmd eval SELECT ... From my current unterstanding the result of the query is still a C array during this iteration within DbObjCmd and than gets converted to Tcl objects. So it should be simple to extend to use SQLITE_NULL and return a predefined value e.g. if a special PRAGMA is set. In the moment i am to tired to figure out of whether there is an already usable relation to combine it with the sqlite shells .nullvalue command. It's the first time i've looked in the sqlite code base. Maybe i am wrong. Is there any experience? TIA
[sqlite] How to do NULL Handling in SELECT Statement?
Hi, what is the correct way to query for NULL-values? I use SQLite version 3.2.0 create table t1(a int, b char); insert into t1 values(1, '2'); insert into t1 values(3,NULL); insert into t1 values(NULL,'4'); select * from t1 where b=NULL; -- this gives no result at all select * from t1 where b=''; -- this also gives no result select * from t1 where b''; -- this gives the expected result (but i wanted the reverse data set) 1|2 |4 -- so i tried select * from t1 where not b''; -- and still i get not the result i was looking for. TIA Stefan
Re: [sqlite] thoughts on a web-based front end to sqlite3 db?
Tcl is really fine for web interfaces. Porting an almost 10 year old web application to sqlite2 and sqlite3 was a charme. It is supporting Linux, SunOS,Windows and maybe HP-UX so far. If you are interested in an generic example using SQLite 3.1.3 (but also supporting 2.1.16) look at http://softguard.dyndns.org:8015 or https://softguard.dyndns.org:8016 user is 'testIt' (uppercase i) password is '$4SBS' for unlimited access. Please clean up any changes you make after testing. As SQLite needs no further administration creating databases and tables on the fly is as simple as using the command line. You only need a standard tcl distribution, tclhttpd3.5.1 and sqlite/tclsqlite itself to run it. PS.: This site is only for demonstration and in work. Internationalization does only support English and German. Also cloning and copying records does not work so far. The API is still limited and mostly directed to msql2/3. Andrew Piskorski wrote: On Mon, Mar 07, 2005 at 04:22:50PM -0500, Eli Burke wrote: I qualify as opinionated, so: Tcl. The fact that Dr. Hipp supports Tcl directly for SQLite is yet another bonus. running apache although I'm open to alternatives. The app itself uses AOLserver. Among other things, it goes very nicely with Tcl.
Re: [sqlite] how to get result of eval as list of lists?
Kurt Welgehausen wrote:
proc lpartition {recsize data} {
set ret {}
set datalen [llength $data]
for {set i 0; set j [expr {$recsize-1}]} \
{$i $datalen} {incr i $recsize; incr j $recsize} {
lappend ret [lrange $data $i $j]
}
set ret}
So is there another way to determine the number of columns/or results of
a query to calculate recsize?
A query can be quite complicate like combined SELECTs or something like
{SELECT*,rowid from...}
db eval {SELECT*,rowid from t1} data {set columns $data(*); lappend
records }]
set recsize [llength ${columns}]
set recordslist [lpartition ${recsize} ${records}]
This still requires the eval script and has the drawback duplicating the
used memory.
There seems to be only two simple and fast solutions for me.
- First one is creating another db subcommand (e.g.: db evallist ...}
- Second one would be easier (but slower?) creating another element (a
pedant to data(*)) holding exactly one row of the result array
data(*)= a b rowid
data(a)= 3
data(b)= howdy!
data(rowid)= 3
data(typeof:a) = text
data(typeof:b) = text
data(typeof:rowid) = INTEGER
proposed:
data(_) = {3 howdy! 3}
TIA
Stefan
