Re: [sqlite] UPDATE colomns based on their values
"P Kishor" wrote, On 3/12/07, jose isaias cabrera <[EMAIL PROTECTED]> wrote: Ok, I have figured out that I do not have null values on the data of my table, even though I do have empty strings. Here is my schema, sqlite> sqlite> .schema CREATE TABLE LSOpenJobs ( id integer primary key, ProjID integer, parent, children, login, cust, proj, PClass, PSubClass, bdate, ddate, edate, pm, pmuk, lang, vendor, vEmail, invoice, ProjFund, A_No, wDir, BiliDir, TMDir, DeliveryDir, paid, notes, status ); And here is a call for a SELECT for a null value: sqlite> select * from LSOpenJobs where notes IS NULL; sqlite> Now, I know for a fact that there are notes empty on some of the records there. Why is sqlite thinking that I do not have null values? you know for a fact that some of the notes are empty, but what makes you think they are null? Did you create them as null or did you get this db from someone? Well, at the beginning I created them as null values. However, I have been adding data to the records, so it may be that the library that I am using may not be setting those values to the correct null value. Here is a record with a few null values: sqlite> select * from LSOpenJobs where id = '166'; 166|166|1172158922388||us|MER|3.0 Cards|Technical| Project|2007-02-21|2007-02-21|2007-02-22|318||en2es,en2br||karina|||klmLD|D:\Projects\166||c the last column is the status, which has the value c and the previus column is notes, which is empty or null. So, my question is what is NULL for SQLite? This is a library call ddbi for the D language. Here is a simple test -- This test below works on a new created DB, but doing a SELECT on my database using your syntax for null value is not working. I can provide you the DB, if you want and you will see that I am not crazy. I have been spending two days on this and I am going to create a new program that shows this bug to the folks that wrote the library. I still would like to know what is a null value. It may have to do with this. thanks for the help. Lucknow:~ punkish$ sqlite3 SQLite version 3.3.8 Enter ".help" for instructions sqlite> CREATE TABLE foo (a, b); sqlite> .s CREATE TABLE foo (a, b); sqlite> INSERT INTO foo (a) VALUES ('blah'); sqlite> INSERT INTO foo (b) VALUES ('blech'); sqlite> .headers on sqlite> .mode columns sqlite> SELECT * FROM foo; a b -- -- blah blech sqlite> SELECT * FROM foo WHERE a IS NULL; a b -- -- blech sqlite> SELECT * FROM foo WHERE b IS NULL; a b -- -- blah sqlite> UPDATE foo SET b = '' WHERE a = 'blah'; sqlite> UPDATE foo SET a = '' WHERE b = 'blech'; sqlite> SELECT * FROM foo; a b -- -- blah blech sqlite> SELECT * FROM foo WHERE a IS NULL; sqlite> SELECT * FROM foo WHERE b IS NULL; sqlite> - Original Message - From: "jose isaias cabrera" <[EMAIL PROTECTED]> To: Sent: Saturday, March 10, 2007 11:40 PM Subject: [sqlite] UPDATE colomns based on their values > > Greetings! > > So, I would like to update some columns based on whether or not they > are > null. Some of you folks have helped me with ifnull and IS NULL, and I > happened to see coalesce, but none of these are working. If I set the > value, they will work, but with the checks, they do not get UPDATEd. > > Here is the call: > > BEGIN; > UPDATE LSOpenJobs SET bdate = '2007-03-02' WHERE ProjID = '215' AND > bdate > IS NULL; > UPDATE LSOpenJobs SET ddate = coalesce(ddate, '2007-03-05') WHERE > ProjID = > '215'; > UPDATE LSOpenJobs SET edate = ifnull(edate,'2007-03-05') WHERE ProjID = > '215'; > COMMIT; > > As you can see, I am using 3 different checks and none of these are > working. I know it's something simple, but what it is? > > Any ideas? > > Maybe the other question is, what defines "IS NULL" or "ifnull" or > "coalesce"? > > thanks, > > josé > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] - -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UPDATE colomns based on their values
On 3/12/07, jose isaias cabrera <[EMAIL PROTECTED]> wrote: Ok, I have figured out that I do not have null values on the data of my table, even though I do have empty strings. Here is my schema, sqlite> sqlite> .schema CREATE TABLE LSOpenJobs ( id integer primary key, ProjID integer, parent, children, login, cust, proj, PClass, PSubClass, bdate, ddate, edate, pm, pmuk, lang, vendor, vEmail, invoice, ProjFund, A_No, wDir, BiliDir, TMDir, DeliveryDir, paid, notes, status ); And here is a call for a SELECT for a null value: sqlite> select * from LSOpenJobs where notes IS NULL; sqlite> Now, I know for a fact that there are notes empty on some of the records there. Why is sqlite thinking that I do not have null values? you know for a fact that some of the notes are empty, but what makes you think they are null? Did you create them as null or did you get this db from someone? Here is a simple test -- Lucknow:~ punkish$ sqlite3 SQLite version 3.3.8 Enter ".help" for instructions sqlite> CREATE TABLE foo (a, b); sqlite> .s CREATE TABLE foo (a, b); sqlite> INSERT INTO foo (a) VALUES ('blah'); sqlite> INSERT INTO foo (b) VALUES ('blech'); sqlite> .headers on sqlite> .mode columns sqlite> SELECT * FROM foo; a b -- -- blah blech sqlite> SELECT * FROM foo WHERE a IS NULL; a b -- -- blech sqlite> SELECT * FROM foo WHERE b IS NULL; a b -- -- blah sqlite> UPDATE foo SET b = '' WHERE a = 'blah'; sqlite> UPDATE foo SET a = '' WHERE b = 'blech'; sqlite> SELECT * FROM foo; a b -- -- blah blech sqlite> SELECT * FROM foo WHERE a IS NULL; sqlite> SELECT * FROM foo WHERE b IS NULL; sqlite> - Original Message - From: "jose isaias cabrera" <[EMAIL PROTECTED]> To: Sent: Saturday, March 10, 2007 11:40 PM Subject: [sqlite] UPDATE colomns based on their values > > Greetings! > > So, I would like to update some columns based on whether or not they are > null. Some of you folks have helped me with ifnull and IS NULL, and I > happened to see coalesce, but none of these are working. If I set the > value, they will work, but with the checks, they do not get UPDATEd. > > Here is the call: > > BEGIN; > UPDATE LSOpenJobs SET bdate = '2007-03-02' WHERE ProjID = '215' AND bdate > IS NULL; > UPDATE LSOpenJobs SET ddate = coalesce(ddate, '2007-03-05') WHERE ProjID = > '215'; > UPDATE LSOpenJobs SET edate = ifnull(edate,'2007-03-05') WHERE ProjID = > '215'; > COMMIT; > > As you can see, I am using 3 different checks and none of these are > working. I know it's something simple, but what it is? > > Any ideas? > > Maybe the other question is, what defines "IS NULL" or "ifnull" or > "coalesce"? > > thanks, > > josé > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] - -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UPDATE colomns based on their values
Ok, I have figured out that I do not have null values on the data of my table, even though I do have empty strings. Here is my schema, sqlite> sqlite> .schema CREATE TABLE LSOpenJobs ( id integer primary key, ProjID integer, parent, children, login, cust, proj, PClass, PSubClass, bdate, ddate, edate, pm, pmuk, lang, vendor, vEmail, invoice, ProjFund, A_No, wDir, BiliDir, TMDir, DeliveryDir, paid, notes, status ); And here is a call for a SELECT for a null value: sqlite> select * from LSOpenJobs where notes IS NULL; sqlite> Now, I know for a fact that there are notes empty on some of the records there. Why is sqlite thinking that I do not have null values? - Original Message - From: "jose isaias cabrera" <[EMAIL PROTECTED]> To: Sent: Saturday, March 10, 2007 11:40 PM Subject: [sqlite] UPDATE colomns based on their values Greetings! So, I would like to update some columns based on whether or not they are null. Some of you folks have helped me with ifnull and IS NULL, and I happened to see coalesce, but none of these are working. If I set the value, they will work, but with the checks, they do not get UPDATEd. Here is the call: BEGIN; UPDATE LSOpenJobs SET bdate = '2007-03-02' WHERE ProjID = '215' AND bdate IS NULL; UPDATE LSOpenJobs SET ddate = coalesce(ddate, '2007-03-05') WHERE ProjID = '215'; UPDATE LSOpenJobs SET edate = ifnull(edate,'2007-03-05') WHERE ProjID = '215'; COMMIT; As you can see, I am using 3 different checks and none of these are working. I know it's something simple, but what it is? Any ideas? Maybe the other question is, what defines "IS NULL" or "ifnull" or "coalesce"? thanks, josé - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: Re: How do I know what DBs I have attached?
Sorry, not all connections. Just attached dbs. I am still new and learning about dbs, so... - Original Message - From: "Igor Tandetnik" <[EMAIL PROTECTED]> To: "SQLite" Sent: Sunday, March 11, 2007 12:35 AM Subject: [sqlite] Re: Re: Re: How do I know what DBs I have attached? jose isaias cabrera <[EMAIL PROTECTED]> wrote: jose isaias cabrera <[EMAIL PROTECTED]> wrote: Perhaps, this should be the case. It would not be so hard to keep a table of connections that are attached or open with a table. You don't want an otherwise read-only connection have to write to the database. This will harm concurrency. Oh, I agree. I'm confused. Didn't you just say that it would be a good idea for every connection to be recorded in some table in the database? Wouldn't that require every connection to write to the database as part of opening or attaching? The reason why I want to do it, is to not attach, if there one attached, already. For the exact reason that you point out. Since I have a few users that will update this DB at any moment, meaning that could do it at the same time, I want to have a catch for that problem. As soon as you try to update, if another update is already in progress, you'll get an error from SQLite. Wouldn't that be sufficient to "catch the problem"? Let us say that I have a few users that would connect to this DB to update it at any moment. Say user one connects to do an update to his data. At millisecond later, user2 connects to do an update to his data, and here is the question, can user say say something like, UPDATE TAble after database is not busy. Not quite, but you can retry again some time later. See also sqlite3_busy_handler, sqlite3_busy_timeout. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: How do I know what DBs I have attached?
Wow! Tough guy. :-o - Original Message - From: "John Stanton" <[EMAIL PROTECTED]> To: Sent: Sunday, March 11, 2007 1:00 PM Subject: Re: [sqlite] Re: How do I know what DBs I have attached? Sqlite is a library used to realize an embedded RDBMS. Nowhere does it claim to be a nanny. jose isaias cabrera wrote: "Igor Tandetnik" wrote, jose isaias cabrera <[EMAIL PROTECTED]> wrote: What I would like to know is, how do I know if I have a db attached already? Realize that a set of attached DBs is a property of a connection (a sqlite3* handle), not some kind of persistent state of the database. You seem to be under impression that when two processes open the same DB file, and one process attaches another DB file, the other process can somehow query SQLite and find out that this happened. This is not the case. Perhaps, this should be the case. It would not be so hard to keep a table of connections that are attached or open with a table. So, if you want to know if _you_ have attached a DB, just keep track of ATTACH DATABASE commands you have issued on your connection. If you want to know if someone else attached a DB to one you also happen to have open, SQLite can't help you there. Perhaps it should. Also, can different clients ATTACH to the same DB and REPLACE unique RECORDS without any problem? Well, only one connection can modify a particular database file at the same time. It doesn't matter if you open the file directly, or attach it to an existing connection. If two processes attempt the modifying operation at the same time, one of them will proceed and the other will be locked out. Is there a way of telling the DB to UPDATE records after the actual connection has completed its processing? Something like UPDATE queue? - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Meta Information: How to retrieve the column names of a table ?
Igor Tandetnik schrieb: Marten Feldtmann wrote: How can I get all the names of a table without doing a query against the table ? PRAGMA table_info(table-name); Thats it ! Thanks ! What's sqlite3_column_meta_data? It doesn't seem to be mentioned in documentation. Sorry, this function call is not available - it was mentioned in the book "The Definitive Guide to SQLite" - but only, when specific preprocessor settings were set - actually it seems, that the official dlls have no call like this ... Marten - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sin and similar functions
Hi Dear Friens I have found this advice in mailing list history: I think you can compile the functions into a loadable library (follow the instructions on creating a sqlite loadable extension), and then load them in the console app. In linux: gcc myLoadableLibs.c -shared -o myLoadableLibs.so then sqlite3> .load /home/jp/myLoadableLibs.so Do someone knows which files from Mikey C's zip archive needs to be compiled into loadable extension? Where are the instructions on creating loadable extension? Thank You Jakub Ladman Dne sobota 10 březen 2007 00:48 Clark Christensen napsal(a): > A poster here, "Mikey C", wrote some math functions and posted them a month > or so ago. You might have a look at > http://www.mail-archive.com/sqlite-users@sqlite.org/msg21791.html > > -Clark > > - Original Message > From: Jakub Ladman <[EMAIL PROTECTED]> > To: sqlite-users@sqlite.org > Sent: Friday, March 9, 2007 3:00:22 PM > Subject: Re: [sqlite] sin and similar functions > > Thank you very much. > Not only my english is poor, but my coordinated geometry too :-( > But my software will make much more geometrical computations, so probably > some in queries too. > Please let You (or someone) direct me to the relevant part of documentation > for defining new functions. > > Thank You > Jakub > > Dne pátek 09 březen 2007 23:35 Dennis Cote napsal(a): > > Jakub Ladman wrote: > > > I will have table with sequence of coordinates (two dimensional space) > > > and corresponding radiuses, so sequence of circles. And i need to use a > > > sqlite query to detect if a actual coordinates (after their > > > measurement) match some of the circle's square or not. And which > > > circle, if match. And this must be for low CPU consumption optimised, > > > so i am not sure, if separate sin table queries will be enough as fast > > > as i need at needed precission. > > > > > > The whole algorithm is proven on mssql by my colegue, but he is using > > > the native math functions. > > > > Jakub, > > > > I may not understand your problem completely, but it seems to me you can > > solve your problem without using any trigonometric functions. > > > > If you have a table of circles like this > > > > create table circle ( > > id integer primary key, > > cx real, > > cy real, > > r real > > ); > > > > You can find all the circles that contain a given point (px,py) using a > > simple query based in the distance between the point and the center of > > the circle. > > > > select id from circle > > where (px-cx)*(px-cx)+(py-cy)*(py-cy) < r*r; > > > > If you want to create a user defined distance function you could > > possibly speed up the calculation somewhat. You could then use a query > > like: > > > > select id from circle > > where distance(cx, cy, px, py) < r; > > > > where > > > > distance(cx, cy, px, py) = sqrt((px-cx)^2 + (py-cy)^2) > > > > HTH > > Dennis Cote > > > > > > - > >-- -- To unsubscribe, send email to [EMAIL PROTECTED] > > - > >-- -- > > --- >-- To unsubscribe, send email to [EMAIL PROTECTED] > --- >-- > > > > > > --- >-- To unsubscribe, send email to [EMAIL PROTECTED] > --- >-- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: How do I know what DBs I have attached?
Sqlite is a library used to realize an embedded RDBMS. Nowhere does it claim to be a nanny. jose isaias cabrera wrote: "Igor Tandetnik" wrote, jose isaias cabrera <[EMAIL PROTECTED]> wrote: What I would like to know is, how do I know if I have a db attached already? Realize that a set of attached DBs is a property of a connection (a sqlite3* handle), not some kind of persistent state of the database. You seem to be under impression that when two processes open the same DB file, and one process attaches another DB file, the other process can somehow query SQLite and find out that this happened. This is not the case. Perhaps, this should be the case. It would not be so hard to keep a table of connections that are attached or open with a table. So, if you want to know if _you_ have attached a DB, just keep track of ATTACH DATABASE commands you have issued on your connection. If you want to know if someone else attached a DB to one you also happen to have open, SQLite can't help you there. Perhaps it should. Also, can different clients ATTACH to the same DB and REPLACE unique RECORDS without any problem? Well, only one connection can modify a particular database file at the same time. It doesn't matter if you open the file directly, or attach it to an existing connection. If two processes attempt the modifying operation at the same time, one of them will proceed and the other will be locked out. Is there a way of telling the DB to UPDATE records after the actual connection has completed its processing? Something like UPDATE queue? - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Compiling the linux package on windows using LCC ?
There is a source distribution which has been configured for Windows on the Sqlite website. Try starting with that. If you use the regular source distribution you need to have an environment where you can run the configure shell script to customize the distribution to your OS. Gussimulator wrote: I generated this make with LCC ide, thats why I dont understand the reason of my problem! I tried using GCC but when I run the make with the included makefile I get "nothing to be done for..." . Im getting quite frustrated by now. - Original Message - From: "John Stanton" <[EMAIL PROTECTED]> To: Sent: Saturday, March 10, 2007 10:52 PM Subject: Re: [sqlite] Compiling the linux package on windows using LCC ? From my recollection the lcc make is not syntax compatible with standard make. I have always used the lcc make within its own IDE so that the make file is generated by the lcc IDE. You could use a regular make and just subsitute the CC definition for the lcc compiler. You might find the need to substitute link library definitions as well. GCC holds is the solution to your problem. Gussimulator wrote: Thanks for the info. Im trying with the LCC ide but I get odd errors while trying to compile: "c:\lcc\bin\make.exe: Commands defined twice for target where.obj near line 87" I was getting a similar error with another file, I remade the project and now Im getting the same error on a different file. What could be causing it? Thanks. - Original Message - From: "John Stanton" <[EMAIL PROTECTED]> To: Sent: Saturday, March 10, 2007 4:02 PM Subject: Re: [sqlite] Compiling the linux package on windows using LCC ? GCC will compile you a library usable with a broad range of C compilers. If you find the gcc library unusable use the makefile as the basis for compiling it using lcc. I use lcc and gcc (mingw) interchangably. GCC has optimization but if you write elegant and efficient C lcc will generate excellent code and compiles very fast. If you get the options correct you can compile each module using lcc or use the lcc IDE to compile them all at once then use ar to build a link library. The makefile generated by configure and gcc or the one in the Sqlite Windows zip file will give you a template to follow. I recollect that compiling Sqlite under lcc was not a big deal, but it was a long time back for me and I cannot recall the precise details. Now I use gcc to build the static library and have a compiler which works identically on all flavors of Windows, Linux, AIX and Solaris. If you want dynamic linking to a DLL you need to look at the lcc documentation to see precisely how it is done. Gussimulator wrote: GCC?, I need to use the resulting DLL on normal C under windows later on, without having strange dependencies, etc. I'd like to know what am I doing wrong when I try to compile the sources... Do I have to output obj for each source and then somehow link this into the final library? - Original Message - From: "John Stanton" <[EMAIL PROTECTED]> To: Sent: Friday, March 09, 2007 2:10 PM Subject: Re: [sqlite] Compiling the linux package on windows using LCC ? Gussimulator wrote: Hi, I would like to compile the tar version of the sources using LCC under Windows. Since I'm not a *nix guy, I don't know where to start, because my little linux knowledge played against me. Heres the problem, I tried using the makefile with make.exe from LCC but that didnt work out (it said it couldn't open the makefile...) so I tried the config but I dont know which tool handles this, so I was back to square one... So I began questioning myself... Would it even be possible to compile this package under windows with LCC? I want to perform a static compilation, so I can statically link the library on my application. However I'd still be happy if I could compile as a dynamic lib. Now that I'm at it, I would like to suggest for the windows build, that the required .exp or .lib be added on the package as well, since this allows for easier linking on applications - at least under my environment (I'm sure others would like this as well - However, being able to compile the source by myself would take care of this issue). Thanks. A simple way is to compile it under gcc then use LCC. After you have compiled with gcc you may discover that to use LCC does not make a lot of sense since my experience with both compilers is that lcc is very well implemented but lacks the level of optimization available with gcc. A static library created with gcc will work with your lcc programs if you are using lcc for your applications to take advantage of some of its clever extensions to regular C. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: Meta Information: How to retrieve the column names of a table ?
Marten Feldtmann wrote: How can I get all the names of a table without doing a query against the table ? PRAGMA table_info(table-name); I need all the names of columns within tables/views, the column index within the raw table. What's "column index" and "raw table"? I'm not familiar with the terms. With that information I may use sqlite3_column_meta_data to get additional information about the coumns ... What's sqlite3_column_meta_data? It doesn't seem to be mentioned in documentation. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] import operation - primary key need to be automatically generated by SQLite (not from csv file)
Martin Jenkins wrote: RohitPatel wrote: /* SQLite 3.3.8 (Windows) used */ /* table t1 */ /* only two columns are given because other columns are irrelevant here */ create table t1 (id INTEGER PRIMARY KEY, name TEXT); /* few sample records from csv file data.csv */ 1,'name_text_1' 2,'name_text_2' 3,'name_text_3' 4,'name_text_4' /* import statement */ .import imp3.csv t1 If your CSV files' id columns are unwanted you could delete them rather than edit them. Writing a simple script to clean up your data would be trivial on 'nix but I see you're on Windows so how about something like: create table t1 (id INTEGER PRIMARY KEY, name TEXT); alternatively create table t1 (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT); if "id" is not important as long as it is unique. create table t1raw (id INTEGER, name TEXT); .import imp3.csv t1raw insert into t1(name) select name from t1raw; drop table t1raw; Martin -- Regards John McMahon [EMAIL PROTECTED] - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Meta Information: How to retrieve the column names of a table ?
How can I get all the names of a table without doing a query against the table ? I need all the names of columns within tables/views, the column index within the raw table. With that information I may use sqlite3_column_meta_data to get additional information about the coumns ... Marten - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: What is wrong with this UPDATE?
Yes, thanks, that works indeed. RBS -Original Message- From: Igor Tandetnik [mailto:[EMAIL PROTECTED] Sent: 11 March 2007 02:55 To: SQLite Subject: [sqlite] Re: What is wrong with this UPDATE? RB Smissaert <[EMAIL PROTECTED]> wrote: > UPDATE > A3SQLADC_J > SET > ADDED_DATE = '' WHERE ADDED_DATE = 0 OR ADDED_DATE IS NULL, > START_DATE = '' WHERE START_DATE = 0 OR START_DATE IS NULL > > near ",": syntax error Make it UPDATE A3SQLADC_J SET ADDED_DATE = (case when ADDED_DATE = 0 OR ADDED_DATE IS NULL then '' else ADDED_DATE end), START_DATE = (case when START_DATE = 0 OR START_DATE IS NULL then '' else START_DATE end); Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -