Re: [sqlite] Do source updates effect DB file compatiblity?
>If your application works fine with the version that you've embedded, why change? Ng, the application is not written yet, I'm still in the research stage. I'm looking towards the future and any problems I may possibly encounter. >The policy so far has been that file formats within a major >version (i.e 3) are forwards compatible - files created by older versions >can be read/written by newer versions. Dan, thanks for letting me know how things have been done in the past.
[sqlite] Re: - [sqlite] How to do NULL Handling in SELECT Statement?
Try: SELECT * FROM t1 WHERE b IS NULL; A column value is null or not null; it cannot be said to equal null as null has no value; rayB |-+---> | | Stefan Finzel | | | <[EMAIL PROTECTED]| | | -Online.de> | | | | | | 23/03/2005 04:20| | | Please respond to | | | sqlite-users| | | | |-+---> >--| | | | To: sqlite-users@sqlite.org | | cc: | | Subject: - [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 *** Confidentiality and Privilege Notice *** This e-mail is intended only to be read or used by the addressee. It is confidential and may contain legally privileged information. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone, and you should destroy this message and kindly notify the sender by reply e-mail. Confidentiality and legal privilege are not waived or lost by reason of mistaken delivery to you. Qantas Airways Limited ABN 16 009 661 901 Visit Qantas online at http://qantas.com
Re: [sqlite] Should Unary + Cast to a Number?
On Mar 17, 2005, at 2:08 PM, Clay Dowling wrote: strftime(buffer, size, "%Y-%m-%sT%H:%M:%S", now); SOAP uses the same format for date/time information, and I've been neck deep in SOAP for the last few weeks. I note that the newly released SQLite 3.2.0 addresses this issue. The "T" is now allowed. Yay for progress! Regards, David
Re: [sqlite] How to do NULL Handling in SELECT Statement?
what is the correct way to query for NULL-values? I use SQLite version 3.2.0 try the following: ... WHERE field IS NULL; or ... WHERE field IS NOT NULL; Marcel
Re: [sqlite] Table alias in sqlite
Anirban Sarkar wrote: Hi all, Please refer to the sqlite query below: set connection_no { } set stmt "select m.*, h.* from mri m,hp_con h where m.rdg_flg = '0' and h.hp_id = $hp_no and m.con_no = h.con_no order by m.con_no;" db1 eval $stmt {} { lappend connection_no $con_no .f.l insert end $con_no } I have used two aliases for the two tables, m and h respectively. My problem is how do I access the field con_no in the mri table. What should be the syntax? The portion of the code where this is required is highlighted in red. Any early help will be highly appreciated. Regards, Anirban Sarkar I'm not sure I understand your question, so this might be completely off topic. If you are asking what I think you are, then the answer is simply to replace the m.* and h.* with explicit field references, perhaps using aliases as well. Try something like: set stmt "select m.con_no as con_no, m.field1 as field1, h.field2 as field2 from mri m,hp_con h where m.rdg_flg = '0' and h.hp_id = $hp_no and m.con_no = h.con_no order by con_no;" This will return 3 fields for each row: con_no (from the mri table, which is exactly the same as the hp_con table because of the join), field1 (from the mri table), and field2 (from the hp_con table). HTH Dennis Cote P.S. your red highlighting didn't survive the trip through the mailing list manager.
Re: [sqlite] How to do NULL Handling in SELECT Statement?
Stefan Finzel wrote: 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 select * from t1 where b is null John
[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] Bind Blob in Version 2 Api
Is there an similiar function to version 2 API to sqlite3_bind_blob function? Version 2 doesn't handle blobs. You must store blobs as encoded strings. Look at the file src/enocde.c to see how to encode strings. Marcel
[sqlite] Bind Blob in Version 2 Api
Is there an similiar function to version 2 API to sqlite3_bind_blob function ? Thanks Vitor
RE: [sqlite] Newbe question on collations
On Tue, 2005-03-22 at 08:28 -0500, Griggs, Donald wrote: > For example, a select statement might end with: > ORDER BY lastname COLLATE NOCASE; > > I think NOCASE and BINARY are built-in collation sequences, though I'm not > sure where those are listed. > You can use the NOCASE collation if you want. But be warned that it only works for ASCII text - it does not know how to do a case-insensitive comparison of UTF characters. NOCASE is really intended for testing purposes only. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Newbe question on collations
> I can't seem to find any information on the web site. Would any person > have some information on how to change the default collation, this would > be extremely useful. Unfortunately you can't change the default collation. You can set an individual column to compare using a case insensitive collation function by adding 'COLLATE NOCASE' to it's definition. http://www.sqlite.org/lang_createtable.html __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/
RE: [sqlite] Newbe question on collations
Hi Ben, >From the syntax page: http://www.sqlite.org/lang.html There are mentions of how to use the COLLATE keyword for CREATE TABLE, CREATE INDEX, and SELECT. For example, a select statement might end with: ORDER BY lastname COLLATE NOCASE; I think NOCASE and BINARY are built-in collation sequences, though I'm not sure where those are listed. Donald Griggs Opinions are not necessarily those of Misys Healthcare Systems nor its board of directors. -Original Message- From: Ben Clewett [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 22, 2005 6:37 AM To: sqlite-users@sqlite.org Subject: [sqlite] Newbe question on collations Dear SQLite, Thanks for providing the application of the decade. SQLite is simply excellent. Thanks for all the work. I am using SQLite as a bastion database between an application and MySQL. Which works very well, accept that the collations differ. MySQL is case-insensitive. I can't seem to find any information on the web site. Would any person have some information on how to change the default collation, this would be extremely useful. Kind regards, Ben Clewett.
[sqlite] Version 3.2.0
SQLite version 3.2.0 is now available on the website. http://www.sqlite.org/ Version 3.2.0 adds support for the ALTER TABLE ADD COLUMN command. The ADD COLUMN command operates in constant time. It takes the same amount of time regardless of how many rows of data exist in the database ahead of time. However, older versions of the SQLite may not be able to read a database that has had the ADD COLUMN command applied until after the database has been VACUUMed. That is why this release increments the second term in the version number. Except for the inability of older versions of SQLite to read or write databases that have had ADD COLUMN applied, the new release of SQLite is fully forwards and backwards compatible, both in the database file format and in the library API. The new ADD COLUMN functionality is made possible by AOL developers supporting and embracing great open- source software. Thanks AOL! Just prior to the release of 3.2.0, an important bug was discovered and fixed in the rollback logic. If you start a multi-statement transaction and a large INSERT or UPDATE within that transaction fails due to a constraint error, then you rollback the whole transaction, the rollback might not occur correctly and parts of the database might be changed. This is a corner case, but it is still and important bug. All users should upgrade to version 3.2.0 as soon as practical. As always, please let me know if you find any other problems. -- D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] Newbe question on collations
Dear SQLite, Thanks for providing the application of the decade. SQLite is simply excellent. Thanks for all the work. I am using SQLite as a bastion database between an application and MySQL. Which works very well, accept that the collations differ. MySQL is case-insensitive. I can't seem to find any information on the web site. Would any person have some information on how to change the default collation, this would be extremely useful. Kind regards, Ben Clewett.
Re: [sqlite] error query???
ok but if I put the "begin" before query me it says: DatabaseError: cannot start a transaction within a transaction if I do not put it the "begin", it makes the query correctly me but it gives back the following message to me: Exception _sqlite.DatabaseError: 'cannot rollback - no transaction is active' in > ignored if instead it does not put not even "commit" the operations nonm they come truly carried out on the database!!? what I must make then? Dan Kennedy ha scritto: This is just a guess. SQLite has no implicit transaction like Oracle and some other systems. So to do the 'commit', you have to first do 'begin'. Otherwise each SQL command is in it's own little transaction, automatically committed. Dan. --- SKORPIO-INFO <[EMAIL PROTECTED]> wrote: ah!! opsss . this is the query # Esecuzione delle modifiche selezionate def ExecuteMixChange(self, event): # Effettuo la connessione al database DATABASE = sqlite.connect('CF_BSS.db') # Assegnazione del cursore DB_BSS = DATABASE.cursor() for x in self.QUERY_MIX: query_command = self.QUERY_MIX[x] <<<=== questo è un dizionario che contiene le query che ho raccolto e che faccio fare tutte insieme cliccando il pulsante di applicazuione modifiche :):) DB_BSS.execute( query_command ) print query_command # Effettuo la conferma delle query eseguite DB_BSS.execute('commit') ###<<<=== questo è il commit che do io .è sbagliato??? ... non mi sembra però ?? # Effettuo la chiusura della connessione al database DB_BSS.close() # Messaggio di avviso per il riavvio del programma all_function.WindowMessageInfo("info", FILE_LINGUA, 1002, self) hi!! SKORPIO-INFO ha scritto: hi!! sorry fo my english. UPDATE mission_name SET ability = '0' WHERE id_mission = '1' why if I make this query me it goes to good aim but it gives back this error to me? --- Exception _sqlite.DatabaseError: 'cannot rollback - no transaction is active' in > ignored --- the table is this: create table mission_name(id_mission integer(11) primary key, name_mission varchar(40), ability varchar(2)) an insertion: insert into mission_name(id_mission, name_mission, ability) values(\'1\', \'ACQUARIUS\', \'1\') thanks!! :) __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/
[sqlite] Table alias in sqlite
Hi all, Please refer to the sqlite query below: set connection_no { } set stmt "select m.*, h.* from mri m,hp_con h where m.rdg_flg = '0' and h.hp_id = $hp_no and m.con_no = h.con_no order by m.con_no;" db1 eval $stmt {} { lappend connection_no $con_no .f.l insert end $con_no } I have used two aliases for the two tables, m and h respectively. My problem is how do I access the field con_no in the mri table. What should be the syntax? The portion of the code where this is required is highlighted in red. Any early help will be highly appreciated. Regards, Anirban Sarkar