Re: [sqlite] Sqlite querie delete bug
This doesn't sound like an SQLite problem to me. Instead it sounds like a problem with whatever visualization tool you are using. What tool are you using? If you can find a user's group for that tool, you may get more helpful answers there. RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Oto _ Sent: Wednesday, July 30, 2014 8:53 AM To: sqlite-users@sqlite.org Subject: [sqlite] Sqlite querie delete bug There is an issue that if entry is selected in Table view then editing in Query view and clicking delete key then it doesn't delete query but asks to delete database entry which is not expected behavior. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to write this commands?
Igor, It took me a bit of looking, but I think I understand your query. One question remains: why did you use the max() function? Thanks! RobR -Original Message- update adla1 set PFLOPF=( select case count(*)=1 then max(adl.pflopf) else adla1.pflopf end from adl where adla1.ref=adl.ref); This says: for each row in adla1, if adl has exactly one row with the same ref value, then set adla1.pflopf to adl.pflopf taken from that one matching row. Otherwise, leave adla1 row unchanged. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug in division?
I don't know if it's in the SQL standard or not, but the C, C++ and C# languages all act this way. The result of mathematical operations on integers is always an integer. If you want the result to be a floating-point number, you have to force at least one of the operands to be a floating-point number. RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Colons (was: RE: Groups and members)
In Igor's post below, what is the meaning of the colon in front of mypid? RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Wednesday, November 06, 2013 8:45 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Groups and members On 11/6/2013 6:55 AM, Ulrich Goebel wrote: Now my problem: For a given person (that is a given p_id) I would like to get a list of all (!) groups, marked wether p_id is a member of it or not. If p_id=1 and this person is member of groups 4, 8 and 9, SELECT g_id, p_id, p_is_member_of_g ... should give g_id p_id is_member 4 1 yes 8 1 yes 9 1 yes 1 1 no 2 1 no 3 1 no Note that the groups with p_id in it come first and are sorted, then come the groups with p_id not in it, sorted too. select g_id, :mypid, (case when r.p_id is null then 'no' else 'yes' end) is_member from g left join r on (g.g_id = r.g_id and r.m_id = :mypid); order by (r.p_id is null), g_id; On an unrelated note, your text fields should have the type of text, not string. The latter has no special meaning to SQLite, but the former does. For details, see http://sqlite.org/datatype3.html -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
Your query looks good to me, which probably means I'm missing the same thing you are. What happens when you run this query? RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of dean gwilliam Sent: Monday, October 07, 2013 7:48 AM To: sqlite-users@sqlite.org Subject: [sqlite] updating using a value from another table Here it is tbl itms (std_nm text, raw_nm text) tbl aliases (std_nm text, raw_nm text) Id like to whip through table itms which has a blank col std_nm and fill in as many fields as I can by reading each itms' raw_nm fld finding any matching rec (with the same raw_nm value) in aliases updating the std_nm fld in itms with corresponding std_nm value in the matching fld in aliases Here's my miserable attempt gDb eval UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x WHERE raw_nm = x.raw_nm); Any help much appreciated ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
Thank you. Now, can you show us sample data from your tables before this query is run? RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of dean gwilliam Sent: Monday, October 07, 2013 10:45 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] updating using a value from another table Simon, Rob Thanks very much for your responses. I downloaded and used the shell...it gives the same results i.e. the first std_itm fld in aliases irrespective of what the value of raw_fld is in itms SQLite version 3.8.0.2 2013-09-03 17:11:13 Enter .help for instructions Enter SQL statements terminated with a ; sqlite UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x WHERE raw_nm = x .raw_nm); sqlite select std_nm from itms limit 10 ... ; sales sales sales sales sales sales sales sales sales sales sqlite ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] GUI for SQLite
Not all of us. Thanks for the list. RobR, SQLiteSpy user and about to find Navicat. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite NULL or 0-Length
Simon, Is there a danger here if firstname is NULL and the LENGTH() function is called first? RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Thursday, May 23, 2013 2:54 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLite NULL or 0-Length On 23 May 2013, at 7:06pm, Jill Rabinowitz jillrabinow...@gmail.com wrote: Does anyone know how I can check the column and set it to a value if it has nothing in it? UPDATE myTable SET firstname='xxx' WHERE LENGTH(firstname) = 0 OR firstname IS NULL Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
First idea: include a subject line. I'm not 100% clear on your message. You said: For those entries in table1 where there is a null in t2... I'm guessing you wanted to say: For those entries in table1 where there is a null in t1... Is that right? RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] looking up records with terms in a table
I think you need wildcards: SELECT Sentences FROM T1 JOIN T2 ON T1.Sentences LIKE %T2.Terms% RobR, not guaranteeing correct syntax -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Gert Van Assche Sent: Tuesday, March 26, 2013 12:53 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] looking up records with terms in a table Clemens, doesn't seem to work... The terms are just a part of the sentence, not a full match. Your query does find full matches. thanks gert ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Mapping Sqlite INTEGER field to C# Nullablelong
Seems like a strange thing for VS Designer to do. NULL is not 0. RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Quanren Xiong Sent: Thursday, January 03, 2013 12:30 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Mapping Sqlite INTEGER field to C# Nullablelong Hi Joe, Just curious. Do you know how the Visual Studio Designer works? In designer, the Null value is converted to 0. I would like to something similar. Thanks ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Simple SQLite-based spreadsheet?
There's several GUI-based SQLite tools available now. Maestro was already mentioned. My favorite is SQLiteSpy because it's fast, even though you can't insert or update data in its grid view (you have to write insert or update queries to do that, which I admit is a pain, but most of the time I'm just looking at data). There are others. What would this new tool do that those don't? RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] memory leak in transactions
Do you have your inserts wrapped in a single transaction? It used to be that I wasn't worrying about transactions in my projects, but I noticed things were very slow. I realized that it was creating and committing one transaction for each insert I was doing. When I wrapped all inserts into a single transaction, speed jumped dramatically. But, of course, this doesn't address your memory issue. Unless the increased memory is somewhere in the transaction management logic. RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of rui Sent: Thursday, November 15, 2012 12:21 PM To: sqlite-users@sqlite.org Subject: [sqlite] memory leak in transactions Hi, I am seeing explosive memory growth when i am using transactions using System.Data.SQLite. I see this when the transactions which were before taking fraction of second, taking 15 sec for 50 row insert into a table. I have tried to use sqlite3_db_release_memory but that only frees upto 2mb after every transaction. After some time say half an hour - i do see memory usage drop but that's not acceptable. All the object are properly disposed from trasnsaction to command etc. The SQLiteConnection is kept open for the life time of the session, which could span hours. I would really appreciate if somebody can help me in getting the reason for such excessive memory usage, i have seen working set(memory)go up from 70 mb to 400 mb in 1 minute where three transactions only doing 50 insert in a table. Regards, Raj ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] VC++ and SQLite
I always turn pre-compiled headers off for every VC++ project. In my opinion, they are artifacts from a time when processors were a few hundred times slower than they are now. The benefit in time saved now is far less than the confusion they cause when something goes wrong. RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Adam DeVita Sent: Monday, November 12, 2012 10:49 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] VC++ and SQLite It isn't VS2010 specific. Even going back to VS6 writing your own C++ wrapper and including the.c file you had to tell it to not use precompiled headers for that file. (Both Debug and Release builds) You should tell VS that this file will not ever be using precompiled headers. On VS2012 Professional Edition one can: Right click on the file within VS10, select Properties. Open the C/C++ tree. Select Precompiled Headers. Set Precompiled Header to Not Using Precompiled Headers. Adam On Mon, Nov 12, 2012 at 10:17 AM, John Drescher dresche...@gmail.com wrote: I know this question is not a SQLite question, but I am hoping that someone here has had a similar experience and/or can point me to the right place to ask this question. After years or using Code::Blocks and Dev-Cpp, I have recently installed Visual Studio 10 Express; it is the first time I am using it, in my Windows 7 machine. I have written, with the help of this mailing list a wrapper class for the latest SQLite3 library using C::B as my development platform, now that I want to switch to VS10, there were a lot of gcc specific code that I had to repair and after clearing all the C++ discrepancies between MinGW's g++ and MS's VC++ I have been left with this error message: fatal error C1853: 'Debug\sql.pch' precompiled header file is from a previous version of the compiler, or the precompiled header is C++ and you are using it from C (or vice versa Does anyone know how to resolve this issue or perhaps a VS10 specific You can like the other poster said disable PCH in visual studio or just delete all the PCH files and have VS rebuild them. The second is what I do in Visual Studio retail versions when I get this error. John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- VerifEye Technologies Inc. 905-948-0015x245 151 Whitehall Dr, Unit 2 Markham ON, L3R 9T1 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] newest 3 entries
Select id, eventdate, eventtype, FROM eventlog WHERE eventtype in
('special') order by eventdate desc limit 3
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of deltagam...@gmx.net
Sent: Thursday, November 08, 2012 3:10 PM
To: sqlite-users
Subject: [sqlite] newest 3 entries
I have a select statement like:
Select id, eventdate, eventtype, FROM eventlog WHERE eventtype in
('special')
Now I like to receive only the newest 3 entries in the table eventlog where the
eventtype is 'special'
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] c++ - Tell SQLite3, read the subsequent rows
Igor, you didn't read ArbolOne's incomprehensible code correctly. Here it is
with reasonable formatting:
void mySQLite3::setStmt(const Glib::ustring s)
{
SQLStatement = s;
if (mystmt == NULL)
{
rc = sqlite3_prepare_v2(db,
SQLStatement.c_str(), -1, mystmt,NULL);
}
if(rc != SQLITE_OK)
{
...
}
rc = sqlite3_step(mystmt);
}
const int mySQLite3::read_int(int pos)
{
if(rc == SQLITE_ROW )
{
apint = sqlite3_column_int(mystmt,pos);
}
return apint;
}
const Glib::ustring mySQLite3::read_str(const int pos)
{
if(rc == SQLITE_ROW )
{
apstr = (const char*)sqlite3_column_text(mystmt,pos);
}
return apstr;
}
You don't show where these functions are being called, but you are doing one of
two things. You are calling setStmt inside a loop, in which case you are
rebuilding the statement every time and then reading the first row each time
(completely ignoring Igor's message about opening a book, reading the first
page, closing the book, repeating that entire sequence, and wondering why you
never finish the book), or you are calling setStmt once, in which case you open
the book, read the first page, close the book, and wonder why you never finish
the book.
RobR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Reducing size
Why are you concerned about the size of sqlite3.o? RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C++ - WHERE clause - update
No, it is absolutely not a trick question. Just tell us the number that
sqlite3_step() returned.
It is a question about what the actual value was that was returned by the call
to sqlite3_step(). The function returns a number that indicates whether it
worked or not. There are values defined in sqlite3.h (or whatever the header
file is) that tell you what the numbers mean. One number (I don't know off
hand which) is labeled SQLITE_ROW, which you are using. Another is
SQLITE_DONE. Another is SQLITE_MISUSE. There are several of them. Your
program should be able to handle all of them. Your program should also be able
to tell you which one it received.
RobR, trying hard to be tactful
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Arbol One
Sent: Thursday, September 06, 2012 9:19 PM
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] C++ - WHERE clause - update
Is this a tricky question?
int sqlite3_step(sqlite3_stmt*);
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rob Richardson
Sent: Thursday, September 06, 2012 12:21 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] C++ - WHERE clause - update
What is the value returned from sqlite3_step()?
RobR
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Arbol One
Sent: Thursday, September 06, 2012 12:14 PM
To: 'General Discussion of SQLite Database'
Subject: [sqlite] C++ - WHERE clause - update
rc = sqlite3_step(mystmt);
if(rc == SQLITE_ROW ) {
The code, in this case, does not process this statement!!?? --
apstr = (const char*)sqlite3_column_text(mystmt,pos);
std::cout apstr std::endl; //-- this is not executed }
Table:
id | tile | fname | mname | lname |
---
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] classic update join question
Many thanks to all of you who took the time to correct my misunderstanding of basic SQL. I ran a little test in PostgreSQL (which is the quickest thing I have to play with), and of course, you are all correct and the query does work as designed. I was trying to figure out how to think about it, and I think I understand now. In general, the database engine will walk through the target table, evaluate the right side of the equal sign, and assign the result to the target column specified in the left side. Simple. I don't know why it didn't seem so simple yesterday. RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] C++ - WHERE clause - update
What is the value returned from sqlite3_step()?
RobR
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Arbol One
Sent: Thursday, September 06, 2012 12:14 PM
To: 'General Discussion of SQLite Database'
Subject: [sqlite] C++ - WHERE clause - update
rc = sqlite3_step(mystmt);
if(rc == SQLITE_ROW ) {
The code, in this case, does not process this statement!!?? --
apstr = (const char*)sqlite3_column_text(mystmt,pos);
std::cout apstr std::endl; //-- this is not executed
}
Table:
id | tile | fname | mname | lname |
---
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] classic update join question
Well, I think you want a where clause on your main UPDATE query. What you wrote will set the frequency of every record in the alpha table to the value from the beta table, for every record in the beta table that matches an alpha record. (It's late, I'm tired and that's incoherent. I hope you followed it.) If you have 1000 records in the two tables that match each other, every record in the alpha table will be updated 1000 times. Or am I merely demonstrating my ignorance? RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of E. Timothy Uy Sent: Wednesday, September 05, 2012 4:11 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] classic update join question Dear Igor, Are you saying that UPDATE alpha SET frequency = (SELECT frequency FROM beta WHERE beta.term = alpha.term) is just as efficient as it gets and equivalent to an update using join (in other dbs)? That would helpful to know. I do kind of imagine some kind of black magic... ;) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] why no such column in sqlite3 ?
Put single quotes around Testitem: sprintf( sqlquery, INSERT INTO tblTest ( CINDEX, CDATE, CDESCR, CAMOUNT ) VALUES ( 5, 2012-08-29, 'Testitem', 300 )); RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] to table update
Are you certain there exist rows in tb1 and tb2 that satisfy the condition? What happens when you try? Is any error message or number returned? Can you run the same query inside an SQLite management tool like SQLite Spy? Does it work there? Please provide us ALL of the relevant information when you post. But in this case, it's fairly easy. (Easy enough for me to get it right? We'll see.) The subquery is returning more than one value. That's illegal. And I hope you mean to update every single row in tb1, because that's what your query will do. And your main query references tb2 without defining it. RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of yanhong.ye Sent: Thursday, August 23, 2012 7:32 AM To: sqlite-users@sqlite.org Subject: [sqlite] to table update update tb1 set col1=(select col1 from tb2 ) where tb1.co2=tb2.co2; it couldn't work ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Select rows where a column is not unique
Igor, Which of those would be fastest? Or don't you have enough information to tell? RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Monday, August 06, 2012 9:14 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Select rows where a column is not unique Tilsley, Jerry M. jmtils...@st-claire.org wrote: I'm sure this is a pretty lame question, but my thinking hat is malfunctioning this morning. How can I select all rows from a table where a specific column is NOT UNIQUE? select * from MyTable where SpecificColumn in ( select SpecificColumn from MyTable group by SpecificColumn having count(*) 1); -- or select * from MyTable t1 where exists ( select 1 from MyTable t2 where t2.SpecificColumn = t1.SpecificColumn and t2.rowid != t1.rowid); -- or select * from MyTable t1 where 1 ( select count(*) from MyTable t2 where t2.SpecificColumn = t1.SpecificColumn); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] AUTO_INCREMENT error
Don't you have to specify a column type for test_1? RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Brandon Pimenta Sent: Friday, August 03, 2012 9:09 AM To: sqlite-users@sqlite.org Subject: [sqlite] AUTO_INCREMENT error I cannot use AUTO_INCREMENT. Here's my query: CREATE TABLE test ( test_1 NOT NULL AUTO_INCREMENT ); Running this query gives me SQL error: near AUTO_INCREMENT: syntax error. What does this mean? SQLite 3.6.12 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_stat problem (YAN HONG YE)
OK for now, but for how long? I still think that 140 is going to come back and bite you in the rear end sometime when you're not expecting it. As I understand it, you have a short collection of bytes that is the result of encrypting a short string. You have much less than 140 bytes of data to store. You are storing four bytes (or whatever) of encrypted data, followed by 136 bytes of random crud. When it finally comes time to extract the encrypted data from the database and unencrypt it, your unencryption algorithm isn't going to know what to with those extra 136 bytes, and you'll get junk. And as I said in my last message, NULL is not a valid choice for the fifth argument. The fact that it works does not change the fact that it is not valid. Please choose an appropriate value as listed in the documentation for that parameter. Always write code as though the next person to look at it just graduated from college and never wrote a line of professional code in his life. For one thing, you may come back to it in two years and ask yourself, Why the heck did I do THAT? RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of YAN HONG YE Sent: Thursday, August 02, 2012 3:20 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite3_stat problem (YAN HONG YE) sqlite3_bind_text(stat,1,uu2,140,NULL); change to sqlite3_bind_blob(stat,1,uu2,140,NULL); it's ok! thank you! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_stat problem
Return values are your friends. Use them. Store the error code from every sqlite function call, and if the error code is not SQLITE_OK (NOTE: Check that that is the correct name.), then display what the error code is. In particular, what is the return value of your sqlite3_bind_text() function call? If the documentation of a function states that a parameter requires a named special value, use that name. The fifth argument should be a destructor function pointer, SQLITE_STATIC or SQLITE_TRANSIENT. Read the documentation to understand what each one means. Learn to cringe every time you put a constant value into your code. People often call hard-coded constants magic numbers because they apparently showed up magically, since there's no other explanation of where they came from. Where did 140 come from? Why do you need it? The parameter requires the length of a string. So use strlen() (again, check the correctness. It's been so long since I've used these functions, the name could be wrong) to calculate the actual length of the string. The documentation states that if the fourth argument is not negative, it is the byte offset where the null terminator should be, and any null terminators before that are included in the bound string, and if any null terminators are included in the bound string, behavior is undefined. That's exactly the situation you have. And when you use strlen() or whatever you use to calculate the length of the string, be careful to take into account the difference between bytes and characters. The sqlite3_bind_text() call requires bytes, but your characters may be one or two bytes. RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLITE_STATIC and temporary data
Is acceptable good enough? I admit I haven't played with this function (actually, I never heard of it until today), but from what I read in the documentation, the case described looked dangerous to me. SQLITE_STATIC seemed to me to imply that the contents of the memory used by the sqlite statement would never change over the life of the statement. But please keep in mind that in this case (as in many other cases), my opinion likely to be worth exactly what you have paid for it. RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jay A. Kreibich Sent: Wednesday, August 01, 2012 12:16 PM To: General Discussion of SQLite Database Cc: c...@iki.fi Subject: Re: [sqlite] SQLITE_STATIC and temporary data On Wed, Aug 01, 2012 at 08:49:19PM +1000, Yose Widjaja scratched on the wall: Dear Friends, So SQLITE_STATIC is meant to be used for data that is static. However, would it still be safe when it is used with data that expires after the sqlite3_step() function? For example: string hello = hello world; sqlite3_bind(statement, 1, hello.c_str(), hello.size(), SQLITE_STATIC); sqlite3_step(statement); hello = moo; Would there be anything that can potentially go wrong? I mean, since SQLITE_STATIC is meant to imply static stuff, would sqlite cache the value in such a way that subsequence SELECT_ statements actually use this static value that was passed in through the bind function? It would be better to keep the value valid until _reset() or _finalize() is called, but, yes... this use of SQLITE_STATIC is acceptable (and somewhat common). -j -- Jay A. Kreibich J A Y @ K R E I B I.C H Intelligence is like underwear: it is important that you have it, but showing it to the wrong people has the tendency to make them feel uncomfortable. -- Angela Johnson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] no such function problem
This is a C question, not an SQLite question. But I'll answer it anyway. You included the function call in your quoted string, so your compiler thinks it's just random text. You need: sprint(sql, insert into student select 3, %s, 22;, ldll(bb)); RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] MIN() for a timedelta?
See below.
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Simon Slavin
Sent: Thursday, July 26, 2012 8:47 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] MIN() for a timedelta?
On 27 Jul 2012, at 12:04am, C M cmpyt...@gmail.com wrote:
On Thu, Jul 26, 2012 at 6:45 PM, Nico Williams n...@cryptonector.com wrote:
Just use CASE to add the missing zero as necessary, something like this:
SELECT strftime('%s', (SELECT CASE WHEN '9:12:32' LIKE '0%' THEN
'9:12:32' ELSE '0' || '9:12:32' END));
Replace '9:12:32' there with whatever expression, probably a column name.
Thanks but I can't figure out how to use that to get the MIN()
timedelta. For example, if I try this (the table is called
Durations...the column is duration):
SELECT MIN(CASE WHEN duration LIKE '0%' THEN duration ELSE '0' ||
duration END) FROM Durations WHERE duration != ''
it returns:
01:00:00:00
which is definitely not the min timedelta in the table.
As near as I can tell, your query should have worked. What is the minimum
timedelta in the table? Or, if the table's too big to answer that, can you
please give us an example a timedelta that should have been smaller than this?
Also, can you get rid of the MIN and add ORDER BY Durations and see what data
your CASE statement generates?
RobR
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Syatem.Data.SQLite Exception/Error handling viaTry...Catch
I have some vague memory that exception handling is screwy inside constructors, and it's better to create a naked object and then fill it in in an Init() method. But I have no idea where I read that, if it's correct, or even which language it applies to. Good luck! RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] cann't work
Does SQLite care about the use of double quotes instead of single quotes? RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Davies Sent: Wednesday, July 11, 2012 5:10 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] cann't work On 11 July 2012 10:00, YAN HONG YE yanhong...@mpsa.com wrote: two same structure tables, when use this sql: insert into table2 values(select * from table1 where filename like %55); but show error: sqlite error 1 - near select: syntax error insert into table2 select * from table1 where filename like %55; http://www.sqlite.org/lang_insert.html Regards, Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] ADO.NET Provider, targeting any cpu
-Original Message- The System.Data.SQLite managed-only assembly, when the native library pre-loading code is enabled (which it is by default starting with release 1.0.80.0), will now attempt to detect the processor architecture of the process it is being loaded into and then it will attempt to load the native interop library from an appropriately named subdirectory (e.g. x86, x64, etc). In order to take advantage of this feature, the System.Data.SQLite managed-only assembly should be deployed with the other managed binaries in your application and the native interop libraries should be deployed in platform-specific sub-directories underneath that directory, as follows: appDir\YourApp.exe appDir\System.Data.SQLite.dll (managed-only assembly) appDir\x86\SQLite.Interop.dll (x86 native-only interop library) appDir\x64\SQLite.Interop.dll (x64 native-only interop library) If this feature does not work properly in your environment, it can be disabled by setting the No_PreLoadSQLite environment variable prior to loading and/or using the System.Data.SQLite assembly. -- Joe Mistachkin = end of original message This is the first I have heard of this feature or requirement or whatever this is. This statement seems to be saying that the System.Data.SQLite managed-only assembly is different from the System.Data.SQLite assembly. Is that true? When I download a new version of the System.Data.SQLite installation package, how will I tell the difference between the two? RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How do you access a specific row of a resultset?
The other thing you are not thinking about is that you never know the order of data in a set. You may think that you want the 5th record that was ever inserted into the table, but you have no guarantee that a select statement will return records in the order in which they were inserted. The 5th oldest record could come up first in the result set, or last, or anywhere else. So, you either have to walk through the set from the beginning, checking every record to see if it's the one you want, or you're going to have to build a more precise select statement. RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Details on New Features
Gabor Grothendieck mentioned a new feature of SQLite in 3.7.11: Queries of the form: SELECT max(x), y FROM table returns the value of y on the same row that contains the maximum x value. Is that standard SQL behavior? I'd have expected that to return one row for every row in the table. To get the behavior described above, I'd use SELECT x, y FROM table WHERE x = (SELECT max(x) FROM table). RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] auto-incrementing integer in composite primary key
If you deleted record on New Year's Day, you want a query for data on New Year's Eve to find the record but you don't want a query for data on January 2nd to find it. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Pavel Ivanov Sent: Monday, April 16, 2012 4:43 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] auto-incrementing integer in composite primary key 1. Why is `:provided_date BETWEEN valid_from AND valid_to` better than `created_on = :provided_date`? What if there are several versions created before your provided_date? Not all queries will allow to add `order by created_on desc limit 1`. Pavel On Mon, Apr 16, 2012 at 4:37 PM, Puneet Kishor punk.k...@gmail.com wrote: On Apr 16, 2012, at 2:45 PM, Petite Abeille wrote: On Apr 16, 2012, at 8:29 PM, Puneet Kishor wrote: I am trying to create a data versioning system so that a query done at a particular time can be reproduced identically as to the original query even if the data have been modified in the interim time. My 2¢ worth. (1) Proper historization/versioning is not a piece of cake (2) Most constraint mechanisms do not help with it Regarding (1), I would suggest a relatively straightforward setup where all you versioned tables include a date range specifying the point in time a record is valid. This is more conveniently expressed as two fields, along the lines of valid_from and valid_to, so you can then query it with a between clause. Each DML operations need to maintain that date range so it stays logically consistent (e.g. no overlaps, not gaps, no delete, etc). At the end of the day, you should be able to query your data for any point in time consistently: select * from foo join bar on bar.bar_key = foo.bar_key where foo.foo_key = 1 and julianday( ... ) between foo.valid_from and foo.valid_to and julianday( ... ) between bar.valid_from and bar.valid_to Regarding (2), I would suggest to forgo traditional integrity constraint mechanisms (primary, unique, referential, etc) as they simply don't play well with (1). For example, one cannot express a meaningful, and useful, primary, nor unique key on versioned data. Ditto for referential constraints. Which also means you have to re-implement all of the above by yourself. Which is a pain and rather error prone. Thanks for your wise words. I am not at all under any illusion that this is going to be easy, but it is worthy of an honest try. Two reactions -- 1. Why is `:provided_date BETWEEN valid_from AND valid_to` better than `created_on = :provided_date`? The latter requires storing only a single date value for every row. Although, after some studying of the Pg timetravel docs, seems like they too use a start and stop date. 2. Yes, most constraint mechanisms might be useless or difficult to implement, but I do need a PK. -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Integer Truncation Issue
Disclaimer: I am not in any way an expert! As I understand things, in SQLite, any value can be stored in any column, regardless of type. But in C#, the System.Data.SQLite library isn't that flexible. If the table definition says the column contains integers, then all data in the table is processed as integers. I'm sure others will give more complete and more accurate responses. RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Jay Howard Sent: Friday, April 06, 2012 9:30 AM To: 'sqlite-users@sqlite.org' Subject: [sqlite] Integer Truncation Issue I have a strange issue, I have a table from a 3rd part piece fo software that I am trying to debug... The schema is below CREATE TABLE [energy_history] ( [addr] INT NULL, [year] INT NULL, [month] INT NULL, [day] INT NULL, [hour] INT NULL, [min] INT NULL, [ch1_amps_avg] INT NULL, [ch1_kw_avg] INT NULL, [ghg] INT NULL, [cost] INT NULL, [ch1_amps_min] INT NULL, [ch1_amps_max] INT NULL, [ch1_kw_min] INT NULL, [ch1_kw_max] INT NULL, [dt] DATETIME NULL, PRIMARY KEY ([addr],[year],[month],[day],[hour],[min]) ) When I use the sqllite data browser tools to view the data is stored as numeric values eg .3092 (the values I am looking for) When I get the values out of the db using c# the dataset is created with a datatype of int32 so all the data truncates. Am I missing something? Hope someone can help Thanks, Jay ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] table names
In SQLite Expert, I created table 12-345 with no problem, but I noted that when the new table was displayed, its name was wrapped in square brackets: [12-345]. Perhaps you could wrap numeric names in brackets similarly. Good luck! RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Visual Studio 2008 Express and sqlite3.c
This leads me to agree with the person who suggested that a #define someplace
is telling the precompiler to change not to something else. There is a
compiler setting that will generate a file with an extension of .i that is
the output of the precompiler. You could try doing that and seeing what that
line is in there. Unfortunately, I don't remember what that compiler setting
is.
I also like the idea of compiling sqlite3.c into a separate library.
RobR
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org]
On Behalf Of Marco Bambini
Sent: Friday, March 30, 2012 8:33 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Visual Studio 2008 Express and sqlite3.c
Please note that if I use the same compiler to compile sqlite3.c as a single c
file than everything is compiled without errors.
Errors occurs only when sqlite3.c is part of a c++ project.
I also manually set sqlite3.c to be compiled as C file instead of Default
without any luck.
Seems like something confuse the Visual C++ compiler.
Any idea?
--
Marco Bambini
http://www.sqlabs.com
On Mar 30, 2012, at 1:08 PM, Nick Shaw wrote:
-Original Message-
I am trying to compile the latest sqlite 3.7.11 amalgamation C file within a
Visual Studio 2008 Express C++ project.
sqlite3.c is correctly set as to be compiled as C code but I am unable to
find out a way to fix some compilation errors:
Compiling...
sqlite3.c
..\Sources\sqlite3.c(107831) : error C2143: syntax error : missing ':'
before '!'
..\Sources\sqlite3.c(107831) : error C2059: syntax error : ';'
..\Sources\sqlite3.c(107832) : error C2059: syntax error : '}'
..\Sources\sqlite3.c(107994) : error C2079: 'yy318' uses undefined struct
'LikeOp'
..\Sources\sqlite3.c(110530) : error C2224: left of '.eOperator' must have
struct/union type
..\Sources\sqlite3.c(110530) : error C2059: syntax error : '!'
..\Sources\sqlite3.c(110534) : error C2224: left of '.eOperator' must have
struct/union type
..\Sources\sqlite3.c(110534) : error C2059: syntax error : '!'
..\Sources\sqlite3.c(110538) : error C2275: 'ExprList' : illegal use of this
type as an expression
..\Sources\sqlite3.c(8133) : see declaration of 'ExprList'
..\Sources\sqlite3.c(110541) : error C2224: left of '.eOperator' must have
struct/union type
..\Sources\sqlite3.c(110541) : error C2198: 'sqlite3ExprFunction' : too few
arguments for call
..\Sources\sqlite3.c(110542) : error C2059: syntax error : '!'
..\Sources\sqlite3.c(110554) : error C2224: left of '.eOperator' must have
struct/union type
..\Sources\sqlite3.c(110554) : error C2198: 'sqlite3ExprFunction' : too few
arguments for call
..\Sources\sqlite3.c(110555) : error C2059: syntax error : '!'
Looks like something's not right with the definition of the LikeOp struct in
your copy of the file. I've got the exact same amalgamation in my VS2008
project and it builds fine. Can you check what you have for the LikeOp
structure definition? Mine looks like this, and starts on line 107829:
struct LikeOp {
Token eOperator; /* like or glob or regexp */
int not; /* True if the NOT keyword is present */
};
Thanks,
Nick.
--
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Sqlite error message
You may want to add FailIfMissing=true to your connection string. It always surprises me when I successfully connect to a database and then an attempt to read from a table that I know durn well is there fails. RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin Sent: Friday, March 23, 2012 2:32 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Sqlite error message On 23 Mar 2012, at 5:46pm, Waltênio de Bessa Mendes walteniobe...@gmail.com wrote: I got a error message could not prepare statement (1 no such table: LivroDaBiblia) (Code 5) Make sure you're opening the correct database file. Try specifying the full path to the file. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No error message generated by sqlite_exec()
Thank you, Dr. Hipp. I understand. RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Tuesday, March 13, 2012 7:07 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] No error message generated by sqlite_exec() SQLite3_exec() should always generate an error message, but with two exceptions: (1) When malloc() fails trying to allocate space to hold the error message (2) In an SQLITE_MISUSE situation because the malloc() subsystem might not even be initialized. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] No error message generated by sqlite_exec()
Hello! I am sending a badly formed query string to sqlite_exec(). It is giving me error 21 (misuse of library), but it is not giving me an error message. The value of the pointer sent as the fifth argument of sqlite_exec() is not changed. Is this expected behavior? Is there something I have to do to tell sqlite that I want the error message to be generated? Thank you very much. RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] No error message generated by sqlite_exec()
Thank you very much for your quick reply, Dr. Hipp, and I will fix my code. Probably I was trying to execute a statement before opening the database. But the question remains: Should sqlite_exec() always generate an error message, or should I be prepared to handle the case in which it does not? (Which I've already done: not hard and cleaner code anyway.) RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Richard Hipp Sent: Tuesday, March 13, 2012 6:47 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] No error message generated by sqlite_exec() On Tue, Mar 13, 2012 at 6:43 PM, Rob Richardson rdrichard...@rad-con.comwrote: Hello! I am sending a badly formed query string to sqlite_exec(). It is giving me error 21 (misuse of library), but it is not giving me an error message. The value of the pointer sent as the fifth argument of sqlite_exec() is not changed. Is this expected behavior? Is there something I have to do to tell sqlite that I want the error message to be generated? If you are getting back SQLITE_MISUSE, that probably means you are calling sqlite3_exec() with a database connection that has previously been closed. SQLite tries to catch that situation and report the SQLITE_MISUSE error, but there are no guarantees that doing so will work. Depending on how malloc() is implemented on your system, it might segfault instead. So: Don't call sqlite3_exec() with a closed database connection. General rule: You should never do anything to SQLite that causes it to return SQLITE_MISUSE. If you do, there is an error in your code. Fix your code. Thank you very much. RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Best LINQ provider for SQLite?
Greetings! What is the best LINQ provider for SQLite? I'm sorry for posting a question that must have been asked several times in the past, but the archives of this group do not appear to be searchable. If there is a way to search the archives, could someone please show me? RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] What do people think of SQLite Root?
Our company typically uses SQLite Spy for managing SQLite databases. I keep hoping to find something better, because SQLite Spy does not offer the ability to edit a table inside a grid. The only way to update data is to use an SQL UPDATE statement. But nothing else offers the speed of displaying data for a decent-sized table. The table we most often have to show contains three columns, and data taken once a minute and saved for thirty days. I've seen a couple of announcements recently about SQLite Root, and it looks like it may be worth trying next time I get a little time. But in the meantime, I'm wondering what others think. Is it a good product? RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Fabio Spadaro Sent: Monday, March 05, 2012 3:51 AM To: General Discussion of SQLite Database Subject: [sqlite] Sqlite Root also available for Linux Announcement of the release Sqlite Root http://www.sqliteroot.com/ now available for Linux. Any feedback is appreciated. -- Fabio Spadaro ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What do people think of SQLite Root?
With the latest version, I think you can. But I haven't been able to get the latest version. The link you provided does not work for me. Do you know any site other than yunqa.de where the latest SQLite can be downloaded? RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ralf Junker Sent: Monday, March 05, 2012 9:48 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] What do people think of SQLite Root? On 05.03.2012 14:50, Rob Richardson wrote: I keep hoping to find something better, because SQLiteSpy does not offer the ability to edit a table inside a grid. Not quite true. SQLiteSpy can edit table cells inside a grid. Double-click the table-icon in the schema treeview to display the table. When you then select a table cell in the grid and press F2, the edit window will pop up. See feature item 2 and the screenshot at http://yunqa.de/delphi/doku.php/products/sqlitespy/index Ralf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What do people think of SQLite Root?
We're running 1.5.5. We don't keep up with updates very well. We're lucky we're not using MS Visual Studio 6 any more. We're up to 2008! Woohoo! I can't get to www.yunqa.de now. I tried in IE8 and FireFox. RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Ralf Junker Sent: Monday, March 05, 2012 10:37 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] What do people think of SQLite Root? On 05.03.2012 16:11, Rob Richardson wrote: With the latest version, I think you can. SQLiteSpy grid editing has been around for years, it is available since version 1.6.0., 7 Feb 2007. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Views and Performance
What kind of JOIN is used when it a type (INNER, OUTER, etc.) is not specified? RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Duquette, William H (318K) Sent: Friday, March 02, 2012 11:23 AM To: Discussion of SQLite Database Subject: [sqlite] Views and Performance Howdy! Suppose I have two related tables, t1 and t2, and I write a view like this: CREATE VIEW myview AS SELECT * FROM t1 JOIN t2 USING (some_column); If I am querying data just from t1, is there a performance penalty for using myview in the query? Or will the query planner generate approximately the same bytecode as it would if I'd simply queried t1? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] accessing multiple databases
IIRC, there's a connection string option that will choose between creating an empty database and throwing an exception if you try opening a database that doesn't exist. Perhaps if that option is set to throw an exception, then the ATTACH command would fail. Or not. RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLiteDataAdaptor Missing
In another library, I had to specify x86 processor because it wasn't designed for 64-bit machines. Might you need to do that? RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Electric Eddy Sent: Monday, February 20, 2012 10:26 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQLiteDataAdaptor Missing I performed the following steps: 1) Checked out the latest local copy using the command 'fossil update' 2) I opened a Visual Studio command prompt, changed into the tools installer folder and executed the command MSBuild Installer.2008.csproj 3) I changed into the root/bin/2008/Debug/bin folder and executed the following one line command: Installer.exe -install true -wow64 true -installFlags All -tracePriority Lowest -verbose true -noCompact true -noNetFx40 true -noVs2008 true -noVs2010 true -whatIf false -confirm true This then popped up an error message dialogue box stating: Invalid core file image runtime version. On Mon, Feb 20, 2012 at 1:59 PM, Joe Mistachkin sql...@mistachkin.comwrote: Electric Eddy wrote: Maybe not going as far as including updates in the installer for support for 2005 but at least a manual process for getting it working in 2005 would be very helpful. I've just checked-in preliminary support to the design-time component installer for Visual Studio 2005; however, I currently have no way to test it. If you wish to use and/or test it, here is the command line you will need after building Installer.exe (which can be built using MSBuild on the command line): Installer.exe -install true -wow64 true -installFlags All -tracePriority Lowest -verbose true -noCompact true -noNetFx40 true -noVs2008 true -noVs2010 true -whatIf false -confirm true All of that needs to be on one line. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Able to differentiate between No query and empty results?
-Original Message Also, Stephan is quite right: it's not the columns but the values which have datatypes, and if you're looking at some random SQLite database that wasn't carefully created the value in r1c1 may be an integer but the value in r2c1 may be text. Simon. -- A fact that has driven me up nearby walls on several occasions, especially when working with date/times. RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] A faster way to insert into a keyless table?
Greetings! I am working on updating an application that has been around for years, originally written by someone who knew SQLite exists but had very little idea of how to use it. The application monitors OPC information for 124 bases, with 7-10 tags per base. (Don't worry about what a base is.) Each base has a separate SQLite database file associated with it. Every minute, I have to write the latest tag value for each tag into the correct database. Here is the create statement for the table into which I am writing data: CREATE TABLE trend_data( tag_key integer, value integer, value_timestamp datetime ); There is no primary key specified for this table. The application is written in C#, and uses a .Net provider for SQLite. The code to insert tag data is very crude. I build an INSERT query as a string, and then I call the database object's Execute() method. The average time to update a base is half a second. Since I've got 124 bases, it takes almost exactly a minute to update each one. But I would like the entire update process, for all bases, to be repeated every minute. Therefore, I'd like to get the update time per base down to at most a quarter second. What would be the best way to speed this up? Thanks very much! RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] A faster way to insert into a keyless table?
Many thanks for your response. That solved the problem. Now I update data for all 124 bases in about eight seconds. RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Petite Abeille Sent: Tuesday, February 14, 2012 2:18 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] A faster way to insert into a keyless table? On Feb 14, 2012, at 8:01 PM, Rob Richardson wrote: What would be the best way to speed this up? wrap all your inserts in one transaction. commit at the end. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Database locked in multi process scenario
Isn't it almost a requirement of a transaction that only one be open at a time in a database? If there could be more than one transaction, then transaction 1 might start, transaction 2 starts, transaction 1 fails, transaction 1 is rolled back, and what happens to transaction 2? One could imagine one transaction working an table 1 and a second working on table 2 which has no connection, but then someone comes along and adds a trigger to table 1 that updates table 2. Now we have two simultaneous independent transactions working on table 2. RobR, who has been struggling for months with a program that might open the same SQLite file at the same time from two points in the program, and who has realized that the program is not well designed. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Sreekumar TP Sent: Friday, February 10, 2012 8:52 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Database locked in multi process scenario In the real code, there is no sleep/wait or pause. It so happens that the write of the app2 is scheduled in between. What you are suggesting is that at any point of time only one process can have a transaction open in a database? Sreekumar On Feb 10, 2012 7:12 PM, Simon Slavin slav...@bigfraud.org wrote: On 10 Feb 2012, at 1:32pm, Sreekumar TP wrote: well, the 'wait' is a simulation of what happens in the real code. The error is fatal to the application as it never ever recovers from it even though the writer has finalized and terminated. In a multi-process environment I recommend that you do not pause for such a long time between the first _step() and the _reset() or _finalize(). You can _bind() a statement then wait a long time to execute it, but once you have done your first _step() you want to get through the data and release the database for other processes. If you still have the database locked and another process tries to modify it, one process or the other will have to deal with a BUSY, or a LOCKED, or something like that. In your own setup, it turns out to be process 1. But a slightly different setup would make process 2 see a BUSY instead. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Can I check the auto-vacuum setting on an SQLite database?
My deepest apologies for forgetting to change the subject line in my last post, and thus accidentally hijacking a thread. Here is the message again, this time with the correct subject. On Wed, Feb 8, 2012 at 8:44 AM, Rob Richardson cedriccic...@gmail.com wrote: Greetings! Is there a way to verify the auto-vacuum state of an SQLite database? I am hoping that auto-vacuum will replace the need for periodic VACUUM calls. If the files are getting too big, I need to be able to verify the auto-vacuum setting before trying to come with some other scheme to prevent unbridled file growth. Thank you very much! RobR -- Please do not copy or forward this message or any attachments without my permission. Remember, asking permission is a great way to get me to visit your site! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How complicated can trigger statements be?
The example of a CREATE TRIGGER statement from the help page is: CREATE TRIGGER update_customer_address UPDATE OF address ON customers BEGIN UPDATE orders SET address = new.address WHERE customer_name = old.name; END; The use of BEGIN and END to wrap the statement leads me to believe that it's possible to have more than one statement between them: CREATE TRIGGER update_customer_address UPDATE OF address ON customers BEGIN UPDATE orders SET address = new.address WHERE customer_name = old.name; INSERT INTO orders_history (customer_name, address) VALUES (old.customer_name, old.address); END; And is it possible to have a conditional statement? CREATE TRIGGER record_big_order AFTER INSERT ON orders BEGIN IF new.value 100 THEN INSERT INTO big_orders (customer_name, salesman_id, value) VALUES (new.customer_name, new.salesman_id, new.value) END IF; END; Thank you very much. RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Major memory leak
I'm thinking whether this is a memory leak or not sort of depends on your definition. If a process is designed to remain open for long periods of time with little activity, and it ends up taking up 1 gigabyte of memory, that looks an awful lot like a leak to me. There are likely to be at least three instances of this application running, and after they all run for a month, they're likely to be consuming 5 gigabytes of memory. This is not acceptable. If SQLite's sorted query is taking up 2.5 megabytes of memory every time this piece of the application is invoked, I need to know how to ensure that that memory is released. Here's a brief description of the application. My company, Rad-Con, Inc., is a major supplier of annealing furnaces and related equipment and software to metal processors worldwide. The application monitors the annealing process on a customer's site. There could be well over a hundred annealing bases. The applicaton's first screen displays an overview of all of the bases, whether they have furnaces, if the furnaces are turned on, and so on. A user can double-click on base to see details. A button on the detail screen calls up a trend display. Trend data is stored in SQLite database files, one per base. The application executes the query I described to find when the last row was written to the table, and uses that to calculate the times that will be displayed on the graph. Then, the application reads the entire table and plots the data. When the user is finished, he closes the trend screen. My requirement is to ensure that the amount of memory allocated to my application before the trend screen is displayed and after the trend screen is closed is the same. If more memory is allocated after it is closed, that is a leak, by my definition. RobR On 3/23/08, Christian Smith [EMAIL PROTECTED] wrote: On Fri, Mar 21, 2008 at 10:41:10AM -0400, Rob Richardson wrote: My SQLite library is built from the single translation unit sqlite.c/sqlite.h. That file contains the version number 3.3.17. I do not have valgrind, but circumstantial evidence that this is a SQLite problem is strong. When stepping through my code, I see that my application's memory jumps by over 2.5 megabytes when the sqlite3_step() method is called when using either the sorted query or the query using max(). The unsorted query doesn't show any memory jump. Also, the difference in memory consumption before this part of the code is executed and after it is left is the same size as the jump in memory when sqlite3_step() is called. When doing a sorted query, the result set is formed in a temporary database somewhere defined by the environment. In your case, it sounds like the temporary database is memory based. Once the result set is done with, SQLite may return the memory to the OS using free, but that will show under the process's virtual memory footprint. You can tell SQLite to use a disk based temporary database using: http://sqlite.org/pragma.html#pragma_temp_store Using this, your memory usage will probably be more stable. However, this certainly isn't a memory leak. RobR Christian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Please do not copy or forward this message or any attachments without my permission. Remember, asking permission is a great way to get me to visit your site! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Major memory leak
My SQLite library is built from the single translation unit sqlite.c/sqlite.h. That file contains the version number 3.3.17. I do not have valgrind, but circumstantial evidence that this is a SQLite problem is strong. When stepping through my code, I see that my application's memory jumps by over 2.5 megabytes when the sqlite3_step() method is called when using either the sorted query or the query using max(). The unsorted query doesn't show any memory jump. Also, the difference in memory consumption before this part of the code is executed and after it is left is the same size as the jump in memory when sqlite3_step() is called. RobR On 3/20/08, Ken [EMAIL PROTECTED] wrote: It might be helpful to include the version of sqlite. Have you run your code through a memory analysis routine such as valgrind, to validate that the leak is not occuring in your application code? HTH, Ken ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Major memory leak
Greetings! I must be doing something wrong. I've got a simple table with three columns, a key column, a value column and a timestamp column. There are 357,000 rows. The timestamps are stored as floating-point numbers (Julian dates), and the other two fields contain integers. I open the table, read one record, and close it. If I do not sort the data, there is no memory loss. Here's the query: select datetime(value_timestamp) AS latest_time from trend_data If I sort the data and ask for only the first record, I leak over 2 megabytes of data. Here's the query: select datetime(value_timestamp) AS latest_time from trend_data order by value_timestamp desc limit 1 I got the same result when the query used the max() function instead of ordering the recordset. My program use sqlite3_prepare16_v2, followed by sqlite3_step, followed by sqlite3_finalize. Is there something I've left out? The application in which these calls are made is designed to be started and left alone. At one point, I checked TaskManager on the customer's computer and found that one instance of this program was using over 950 megabytes of memory! At that point, I advised the customer to restart the program once a day, but I'd really like to be able to tell them they don't have to do that any more. Thanks very much! RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Major memory leak
Greetings! I must be doing something wrong. I've got a simple table with three columns, a key column, a value column and a timestamp column. There are 357,000 rows. The timestamps are stored as floating-point numbers (Julian dates), and the other two fields contain integers. I open the table, read one record, and close it. If I do not sort the data, there is no memory loss. Here's the query: select datetime(value_timestamp) AS latest_time from trend_data If I sort the data and ask for only the first record, I leak over 2 megabytes of data. Here's the query: select datetime(value_timestamp) AS latest_time from trend_data order by value_timestamp desc limit 1 I got the same result when the query used the max() function instead of ordering the recordset. My program use sqlite3_prepare16_v2, followed by sqlite3_step, followed by sqlite3_finalize. Is there something I've left out? The application in which these calls are made is designed to be started and left alone. At one point, I checked TaskManager on the customer's computer and found that one instance of this program was using over 950 megabytes of memory! At that point, I advised the customer to restart the program once a day, but I'd really like to be able to tell them they don't have to do that any more. Thanks very much! RobR ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Wrong column type being returned
Greetings! I have an application that writes a small SQLite database (well, several small databases), and an ActiveX control that reads them. One of the tables has three columns: a foreign key into another table, a value (which should be a double, but in the file I looked at, they're all integers for some unknown reason), and a timestamp. In early versions of these applications, the timestamp was written as a floating-point number containing a date in Julian form (number of days since 1/1/1601, I think it is). But with the latest SQLite library, the writing application suddenly started writing the dates in human-readable form (2008-02-01 11:35:00). I don't know why. Of course, this broke the reading control. But since I had cleverly written the reading application using classes that wrap the various field types, all I had to do was create a JulianDate class to wrap a date field. In that class, I'd check the column type I'm reading. If it's a double, I'd just return that, and if it's text, I'd convert it into a Julian date and return that. This worked just fine when I first wrote it a week or two ago. But now it's not working. My code uses sqlite3_column_type to determine the column type of the date column. If it's SQLITE_FLOAT, then sqlite3_column_double() is used to retrieve the value. Otherwise, I assume the column is text and use sqlite3_column_text16() to retrieve the data. The problem I'm having is that my sqlite3_column_type() call is returning 1, which means the column is an integer. It isn't. When I open the file in SQLiteSpy, I see that all values in the date column are floating point numbers, as expected. And as I step through my code, since it tries to call sqlite3_column_text16(), I see that the data to the right of the decimal point is getting truncated when I read it. Can anyone tell me why SQLite suddenly thinks this column of floating-point data holds only integers? Thanks very much! Rob Richardson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Sharing an in-memory database between applications
Greetings! We are using an SQLite database to store process data that will eventually be displayed on a graph. The database design is simple, including only six tables, but the table containing the data points for the graph could contain a few million records. By using the simplest possible query and asking for the bare minimum of data I need at any one point, I've managed to get the time to display the graph down from a few minutes to about 15 seconds for a sample database with 1.3 million records. But I'm wondering if I can use an in-memory database to improve this dramatically. The data is collected by a Windows service that collects data and adds it to the database once a minute. If the service would also store the data into an in-memory database, and the graphing application could somehow read the same database, I ought to be able to get unbelievable speed. Is this feasible? If so, how would I set it up? Another possibility might be to read the entire database from disk into an in-memory database when the graphing application starts up, if there's a way to do that that is much faster than a set of INSERT INTO newtable SELECT * FROM oldtable (or whatever -- you get the idea) statements. Thank you very much. Rob Richardson RAD-CON INC. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] How can I convert from Julian time to a tm structure?
Greetings! I am trying to speed up an application that queries a table with three columns and around a million records, and one of the fields is a timestamp. One thing I want to do is to move the conversion of the timestamp from a Julian time to a human-readable time from the query to my application. (By the way, this is a C++ app written in MS Visual Studio 6.) I could build a query and have SQLite execute it, something like SELECT datetime(123456.789) AS timestring, but that has the overhead of preparing the query, executing it and finalizing it, plus the overhead of converting from a string representation into the tm structure once I get the result of the query. I didn't see any little utility function in the SQLite library that just exposes whatever routine SQLite uses to do the conversion. Does one exist? There must be plenty of algorithms out there to do this conversion. A quick search revealed a few, but they were obviously faulty (assuming every year has 365.25 days, for instance) or not precise enough (returning only the day). I need an algorithm that is accurate to the nearest second. What should I use? Or is the SQLite query the best I'm going to do? Thank you very much. Rob Richardson - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How can I convert from Julian time to a tm structure?
All right. Smack me upside the head again. I deserve it. SQLite is open source, so I just had to look in the source code. RobR - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How can I convert from Julian time to a tm structure?
Dr. Hipp, Thank you very much for the link to the source for the Julian date calculations. I did pull the code out of date.c, and I'm using it. But the code seems to rely on implicit conversions between floating-point numbers and integers that I assume must be intentional, but it looks error-prone to me. While I am quite confident that you and your collaborators checked this code carefully, I would like to see an explanation of this algorithm to understand it more fully. Maybe I'll see if I can get the book through an inter-library loan someplace. Rob Richardson - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] What happens when a table with an active statement is changed?
Greetings! In the ADO world, I can have a recordset object that holds data from a table. I can call AddNew() to add a record to the table, Update() to change data in the current record, or Delete() to delete the current record. But in the SQLite world, insertions, updates and deletions can only be done by preparing the appropriate SQL statement and calling sqlite3_exec() on the database (or sqlite3_prepare()/step()/finalize()). So, what happens if I need to change a record as I am walking through a result set? If I have one sqlite statement that is pointing to a record and a different sqlite statement deletes that record, will sqlite3_step() on the first statement have a problem? What if the second sqlite statement deletes the record that the next sqlite3_step() on the first statement would have retrieved on its next call? What if the second sqlite statement adds a record? Will the repeated sqlite3_step() calls on the first statement eventually get the new record? Or do I just need to be careful that I never have two active sqlite statements referencing the same table? Rob Richardson - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] What happens when a table with an active statement is changed?
It seems this was discussed just a few days ago. A recent upgrade to SQLite allows inserts, updates and deletes on tables that are also open for selection. The changes may or may not appear as I call sqlite3_step(), but I can live with that. RobR - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Why is there no sqlite3_exec16() method?
Igor, Thank you very much for your reply. My naïve impression was that sqlite3_prepare/step/finalize are used for SELECT statements, where there would be a result set one would want to step through, and that one would use sqlite3_exec() for statements where no result set is expected, such as UPDATE, DELETE or INSERT. So, let's say we want to delete a record using DELETE FROM my_table WHERE my_key = my_unwanted_value. I would just pass that string into sqlite3_prepare16(), then call sqlite3_step() to actually do the deletion (and return SQLITE_DONE), and then clean up by calling sqlite_finalize? Rob Richardson - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Why is there no sqlite3_exec16() method?
Greetings! Our shop writes in Visual C++ 6, and our applications are all developed with _UNICODE defined. But if we want to use sqlite3_exec(), we are forced to convert the SQL statements to single-byte characters. Why is there no sqlite3_exec16() method? Or is there a version of the SQLite API that does have that method? Thank you very much. Rob Richardson - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] DSN-less connection string
Greetings! I am trying to connect to a SQLite database from inside a C++ program (written in Visual Studio 6) using ADO without using a DSN. The database file is c:\program files\wincaps\trend01.trd. The library developed in house to wrap ADO calls contains an Open() method that accepts a string of the form DSN=SomeDSNName. The method prepends Provider=MSDASQL; to that and sends it to an ADO Connection object. I tried to pass in Data Source=c:\Program Files\WinCaps\Trend01.trd to it instead. I got an error complaining that the data source name is too long. So I copied the file into my C:\Misc folder and passed in Data Source=c:\Misc\Trend01.trd. The final connection string was Provider=MSDASQL;Data Source=c:\misc\trend01.trd. The Microsoft ODBC Manager complained that the data source was not found and no default driver was specified. I downloaded and installed the ADO.Net provider, but I did not see anywhere anything telling me what provider name to use with it. I can't use a DSN because the database to be opened must be selected by the user at run time. How do I do this? Thank you very much. Rob Richardson - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Why does SELECT julianday('some_date') AS dateNumber get me a string via ODBC?
Greetings!
I am trying to talk to a small SQLite database through ADO and an ODBC
driver. I want to convert a date to a Julian day before using the date
in a query of a table that could have over a million records. In
SQliteSpy, the query SELECT julianday('2007-06-05 12:34:56',
'localtime') AS dateNumber gives me a floating-point number. But in
ODBC, I use that query to create a recordset and examine the first value
in the recordset, I see that it is a variant of type VT_BSTR containing
2345678.9123 (or whatever), and I have to use strtod() to convert it
to the number I want. Is there a way to write the query to ensure that
it will give me a number instead of a string?
RobR
P.S. Since I am going to turn around and use the value in another
query, I think I actually do want it in a string, but I'd still like to
know the answer for future reference.
[sqlite] How can I get my query to run as fast as SQLiteSpy?
Greetings! I have a simple table with five columns and 450,000 rows. In SQLiteSpy, I can run SELECT * FROM trend_data and get all 450,000 rows in 4.5 seconds. But in my program, if I use sqlite3_prepare() and sqlite3_step() until I run out of data, it takes 55 seconds to get through all rows. A test with hard-coded junk data showed that my program is account for only 2 seconds of that. If I use sqlite3_get_table(), I can cut my time in half, which is nice, but I'm still taking 25 seconds to get the same data SQLiteSpy is getting in 4. How is SQLiteSpy doing it, and can I use the same trick? Also, my version of the sqlite library code only has an sqlite3_get_table() method that uses single-byte characters. Does the latest code have a Unicode version of this method? Thank you very much. Rob Richardson -Original Message- From: Will Leshner [mailto:[EMAIL PROTECTED] Sent: Thursday, May 24, 2007 11:39 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] building/upgrading on Mac OS X On 5/23/07, Alessandro de Manzano [EMAIL PROTECTED] wrote: That's my question too, and I'm glad to hear it works fine for you :) (may I ask you which version of OS X are you using ? the latest 10.4.9 ?) Yes. I'm building on a MacBook Pro running 10.4.9. I don't understand why on the Wiki's building instructions, Mac OS X section, it esplicity says to use --disable-shared for some problem with libtool. Maybe some old issue no more applicable ? I'm not sure about that either. I've been building SQLite out of the box on Mac OS X for several years now and I don't think I've ever had to do anything more than simply configure and make. yep, I tried building normally but not tried to install that dynlib yet, I'll try as soon as possible :) I think it should work. At least I hope so :) - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?
I can't get SQLiteExplorer to work with my databases. It always gives me an unknown file format error. I believe it's been quite a while since it was updated. RobR -Original Message- From: Griggs, Donald [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 06, 2007 3:14 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid? Regarding: If I want to change data . Sqlite3Explorer is free software, and works as you describe. I imagine there are several others. http://www.singular.gr/sqlite/ (Not sure if there will be future releases of this or not) - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?
For the life of me, I can't figure out how to open a database in DBManager. I looked at it before, tried for an hour, and erased it. I just tried again, with the same result. If I can't figure out how to do such an easy thing, I can't trust the program. RobR -Original Message- From: COS [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 07, 2007 12:26 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid? Hi Rob, You can try DBManager Standard or Enterprise Editions. The first one is free for personal use, the second is a commercial product. The complete list of features can be found at http://www.dbtools.com.br/EN/dbmanagerpro. You can edit your tables data and also manipulate images in blob fields. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQlite3.exe .dump doesn't do anything for me
Greetings! I have a small database (seven tables with no more than 20 rows per table) that I want to dump, since I want to create similar databases. However, sqlite3 mydatabase.db .dump just gives me: BEGIN TRANSACTION; COMMIT; What would cause sqlite3 not to be able to see any tables in my database? Thank you very much. Rob Richardson - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Is there a SQLiteSpy-like thing that will let me change data from a grid?
Greetings! The only tool I have for examining and manipulating SQLite databases is SQLiteSpy. If I want to change data in that program, I have to write an SQL statement. Every other database editor I've seen lets a use do simple things from a grid. Open a table and data is loaded into a grid, click on a cell and type in a value and it gets written to the table, select a row and click a Delete button and the row is deleted from the table, and so on. There must be a tool somewhere that will do that for a SQLite database, or there's some feature of SQLiteSpy I don't know about. Can somebody please point me to one or the other? Thank you very much. Rob Richardson - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Creating a database from inside a program
Greetings! I need to be able to create a database with a known schema from inside a program. I used sqlite3.exe's .schema command to export the SQL needed to create the schema for my new database. I can create the database by the following steps: C:\: sqlite3 newdatabase.db Sqlite3 .read schemafile.txt At that point, my database is ready to go. But, sqlite3 is supposed to be able to accept commands on its command line. For example, the schema file was generated using this command: C:\: sqlite3 existingdatabase.db .schema schemafile.txt So, I thought I could just issue the following command from inside my program: Sqlite3 newdatabase.db .read schemafile.txt But, when I issue this command from the DOS prompt, it gives me an error message saying that there is no command named read. (Note the lack of a leading period.) If this won't work from a DOS prompt, I'm sure it won't work from my program. So, what is the recommended way to create a new database and its schema from inside a program? In case it matters, I'll be using Visual C# 2005 and the SQLite.net.dll file from SourceForge. Thank you very much! Rob Richardson RAD-CON INC.
[sqlite] Can primary key columns be altered to use autoincrement?
I have a small database in which tables have columns that were created as integer primary key, but not autoincrement. I would like to make sure keys are never reused, so I want to add autoincrement to the column definition. Is that possible? As near as I could tell from the docs, it's not possible to alter a column at all. There's no ALTER COLUMN clause available for the ALTER TABLE statement. Is that correct? Thank you. Rob Richardson RAD-CON INC. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Bug in SQlite ?
What assertion failure are you seeing? What is the exact message? Can you use a debugger to step into the code where the assertion failure happens? RobR - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Stripping a newline character
Please forgive my idiocy. I was more tired than I thought. First, I posted this message to the wrong mailing list (I wanted a Python list), and second, I made a dumb, silly assumption about how the method worked. RobR - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Stripping a newline character
Greetings!
I am using a serial communications package that includes a readline()
method to talk to a bar-code scanner. Readline() gives me a string
whose last character is a newline ('\n'). I am trying to get rid of the
newline. MyString.strip('\n') isn't working. It has no effect. How
should I do this?
Thanks very much!
Rob Richardson
RAD-CON INC.
[sqlite] sqlite3_interrupt() works
Greetings! After installing SQLite 3.3.7 and making sure I was passing the correct pointer to sqlite3_interrupt(), I got it to work. Rob Richardson RAD-CON INC.
[sqlite] Invalid or corrupt file when building .lib file for version 3.3.7 dll
Greetings! I am using MS Visual Studio 6 under the Windows XP pro operating system. I downloaded the dll for version 3.3.7. I tried to run Lib to build the .lib file for the dll. Here's the command line: lib /machine:i386 sqlite3.def This was run with the folder containing sqlite3.def as the current folder. When I ran it, I got: sqlite3.def : fatal error LNK1136: invalid or corrupt file What did I do wrong? RobR, about to build the DLL from the source files RAD-CON INC.
[sqlite] How can I tell what version of SQLite a database was created with?
Greetings! I have an SqLite database file that cannot be opened in my application or with SQLiteExplorer. Both of them report Unsupported file format. However, it can be opened with SQLiteSpy. If I open it in Visual Studio's hex editor, I see that the first few bytes of the database file identify it as a version 3 database. The only thing I can think of is that the database and SQliteSpy were built with a newer version of SQLite than I have. My library is version 3.2.1. So, is there a way to find out exactly what SQLite library built this database file? Thanks very much! RobR - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] sqlite3_interrupt()
I have a thread that executes a query that takes about 30 seconds on my test setup. In the field, the query could take several minutes. The user needs to be able to stop this query if it was started by accident. I have a pointer to the sqlite3 object that is running the query inside my thread. In the main GUI thread of the application, I call sqlite3_interrupt(), passing it the stored sqlite3 pointer. But after I do that, the query still stops executing 30 seconds after it started. The interruption seems to have no effect. Is this expected? How soon after I issue sqlite3_interrupt() should a long query stop executing? Or am I doing something wrong? Thank you very much. RobR
RE: [sqlite] sqlite3_interrupt()
Dr. Hipp, Thanks for your reply. The question of which version I'm using is up in the air right now. I threw a call to sqlite3_libversion() into my application, and it returned 3.2.1. I asked our lead developer (who's in Indiana while the rest of us are just west of Cleveland, OH) what version we should have, and he said 3.3.5. He sent the source code. I opened my existing copy of sqlite3.h and saw that SQLITE_VERSION was set to 3.3.5. Yet, when I my application, I get 3.2.1. I deleted all copies of sqlite3.lib and sqlite3.dll from my machine, rebuilt sqlite3 from Visual Studio, and still I get 3.2.1. I have no idea where it's coming from, but it seems to be telling me I'm using an old version of SQLite. RobR - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] sqlite3_interrupt()
Dr. Hipp,
Thank you very much for your earlier assistance. As you suggested, I
downloaded the version 3.3.7 source code and built it into a static
library. My application now correctly reports that it is using version
3.3.7.
But sqlite3_interrupt() still seems to be doing nothing. I added TRACE
statements to my code to show the times things happened, and I included
the value of the pointer to the sqlite3 object that was being used when
the query starts and as the argument to sqlite3_interrupt():
GetTrendData() started at Thu Aug 24 16:03:54 2006
; sqlite object is at 056B89E0.
select tag_key,value,datetime(value_timestamp, 'localtime') from
trend_view where trend_key=1 and value_timestamp =
julianday('2006-08-22 16:01:41.000', 'utc') order by value_timestamp
desc
Interrupt requested at Thu Aug 24 16:03:57 2006
for object at 056B89E0.
GetTrendData() finished at Thu Aug 24 16:05:07 2006
Rob Richardson
RAD-CON INC.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] sqlite3_interrupt()
I think I've found the problem.
The next step in debugging was to find the actual sqlite3 library calls
I was making, just to make sure the delay wasn't somewhere else in my
code. Here's the trace statements:
GetTrendData() started at Thu Aug 24 16; sqlite object is at 056B9570.
select tag_key,value,datetime(value_timestamp, 'localtime') from
trend_view where trend_key=1 and value_timestamp =
julianday('2006-08-22 16C
SqlOpen(): First sqlite3_step() called at Thu Aug 24 16:30:52 2006
; sqlite object is at 056C20E0.
Interrupt requested at Thu Aug 24 16:30:54 2006
for object at 056B9570.
CSqlOpen(): Back from first sqlite3_step() call at Thu Aug 24 16:32:04
2006
; sqlite object is at 056C20E0.
GetTrendData() finished at Thu Aug 24 16
The object pointer reported in the second statement is not the same as
the one in the first statement. The second statement's pointer is the
one that was used in the call to sqlite3_prepare(). The
sqlite3_interrupt() pointer had better be the same as the one in
sqlite3_prepare(), and it wasn't.
Bottom line: I was trying to interrupt the wrong sqlite3 object.
Once I figure out how to get the correct sqlite3 object to use in the
interrupt call, I'll let you know how it works.
Rob Richardson
RAD-CON INC.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] starting with unicode
No, you don't need sqlite3_reset() inside the loop. The pseudocode should be: open prepare loop while not at end of file step read repeat finalize close For the read portion, use the sqlite_column_xxx() methods. And wrap every single string in your code in the _T() macro, or you'll get errors everywhere when you finally move to a Unicode build. RobR - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Can I use internal variables in SQL scripts?
In SQL Server, I can write a stored procedure that looks something like this: CREATE PROCEDURE MyProc AS DECLARE @someVariable INT SELECT @someVariable = someColumn FROM someTable WHERE someConditionThatReturnsOneRow SELECT someOtherColumn FROM someOtherTable WHERE thePrimaryKey = @someVariable END PROCEDURE Other features available in SQL Server stored procedures include while loops, temporary tables, and the FETCH command to retrieve data from a resultset one row at a time. I have SQLite Explorer and SQLiteSpy. Does either one have similar capabilities? Thanks very much! Rob Richardson RAD-CON, Incv.
RE: [sqlite] Can I use internal variables in SQL scripts?
Christian, Thank you for your reply. I will be happy to develop stored procedure capability for SQLite in my copious spare time. :-) Don't hold your breath. RobR -Original Message- From: Christian Smith [mailto:[EMAIL PROTECTED] Sent: Friday, July 28, 2006 10:10 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Can I use internal variables in SQL scripts? No, because SQLite has no stored procedure capabilities. SQLite has only simple SQL statements. I'm sure the community would welcome such an addition, should you or anyone else fancy contributing them:)
[sqlite] The meaning of times in julianday()
Greetings!
My test query is:
select tag_key,value,
datetime(value_timestamp, 'localtime') AS localtime,
datetime(value_timestamp) AS UTCtime
from trend_view
where trend_key=1
and value_timestamp = julianday('2006-07-27 10:08:32.000')
order by value_timestamp desc
My table has records recorded every hour, roughly on the hour. This
program that generated this query wanted to get the last 24 hours' worth
of data. It was run at 10:08 on June 28th.
The earliest data returned was recorded at 11:01 UTC on June 27th, or
7:01 AM EDT on June 27th. That's too far back by four hours (the
difference between EDT and UTC time).
When I changed the query to use julianday('2006-07-27 10:08:32.000',
'utc'), the earliest data returned was recorded at 15:01 UTC on June
27th, which is 11:01 EDT on June 27th, which is what I want.
When I changed the query to use julianday('2006-07-27 10:08:32.000',
'localtime'), the earliest data returned was recorded at 07:01 UTC on
June 27th, which is 03:01 AM EDT on June 27th, which is far too much.
So, I conclude the following:
If no modifier is provided to the julianday() method, the given date is
assumed to be UTC.
If the 'utc' modifier is used, the given date is assumed to be local
time. It is converted to UTC before the comparison is made.
If the 'localtime' modifier is used, the given date is assumed to be UTC
time, and it is converted to local time before the comparison is made.
Are those conclusions correct? And I presume the datetime() method
operates the same way?
Thanks again!
Rob Richardson
RAD-CON INC.
[sqlite] Reading the same table from two threads
Greetings! I am starting two threads in quick succession that read the same table. Each thread calls sqlite3_open(), so they are using separate database pointers. The first thread asks for records recorded in the last 24 hours. The second thread asks for records from the same table recorded in the previous 24 hours. The second thread is probably started only a few milliseconds after the first one. I am getting an access violation inside sqlite3.dll when the second thread calls sqlite3_prepare(). Am I trying to do something I shouldn't? Thanks very much! Rob Richardson Rad-Con, Inc.
RE: [sqlite] Reading the same table from two threads
Jay, After the first thread, a graph will be displayed and the user can start doing things on it while the next batch of data is being loaded. Rob -Original Message- From: Jay Sprenkle [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 26, 2006 9:14 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Reading the same table from two threads On 7/26/06, Rob Richardson [EMAIL PROTECTED] wrote: few milliseconds after the first one. I am getting an access violation inside sqlite3.dll when the second thread calls sqlite3_prepare(). Am I trying to do something I shouldn't? Bugs aside, that should work. It doesn't sound like a very efficient design though. Why are two threads reading the same data? -- SqliteImporter and SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite Cthulhu Bucks! http://www.cthulhubucks.com
RE: [sqlite] Reading the same table from two threads
I found the spot where I was telling the two threads to use the same database pointer instead of running on separate ones. Once I fixed that, it works. Thanks for your help. Rob Richardson Rad-Con, Inc. -Original Message- From: Jay Sprenkle [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 26, 2006 9:49 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Reading the same table from two threads On 7/26/06, Rob Richardson [EMAIL PROTECTED] wrote: Jay, After the first thread, a graph will be displayed and the user can start doing things on it while the next batch of data is being loaded. Ah. Many readers should work fine. An access violation sounds like a bad pointer or a compile problem. Check for the standard stuff: * more than one DLL/loadable lib and you're not loading the one you thought you were * correct compile settings for sqlite * bugs ( it compiles, therefore it's perfect! ) * compiler not service packed
[sqlite] sqlite3_interrupt() and threads
Greetings! My application will occasionally be called on to execute queries that take several minutes. This will be done from a worker thread so that the user interface remains active. As the query is being executed, the user must be able to respond to alarms. When the user hits F11 to go straight to the alarm screen, the window from which the thread that is running the long query disappears. However, the thread continues. I need to stop the query. The thing that takes the time is the first call to sqlite3_step(). That step is likely to take 5 minutes, and I may need to stop it after 1 minute. The only way I can think of to do that is to call sqlite3_interrupt() from a different thread. However, for regular data access operations, we can't use the same database connection in two different threads. Can sqlite3_interrupt() be called from a different thread than the sqlite3_step() that I want to interrupt, or is there some other mechanism I can use, or is there no way to do this? Thanks very much! Rob Richardson Rad-Con, Inc.
RE: [sqlite] sqlite3_interrupt() and threads
Thank you for pointing me to that article. That leads to two more questions: What alternative do I have? If I abort the thread that is running the query, will the query stop? And, if sqlite3_interrupt() has to be issued from the same thread that is using the database, what is it designed to be used for? OK, three questions: Is there a way to run sqlite3 queries asynchronously? Thanks again! Rob Richardson Rad-Con, Inc.
RE: [sqlite] sqlite3_interrupt() and threads
Great! That looks like exactly what I need. Thanks very much! Rob Richardson RAD-CON INC. -Original Message- From: Michael Scharf [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 25, 2006 10:45 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite3_interrupt() and threads Hi Richard, I use a progres_handler: http://www.sqlite.org/capi3ref.html#sqlite3_progress_handler I set it up to be called every 1 ticks (2nd argument) or so and I use and the void* to points to a data structure that contains a boolean isCanceled. If another thread wants to cancel the worker thread it sets the isCanceled to true. The progress callback checks the isCanceled and returns 0 if set. Then the query gets aborted... (I'm using this in java and it works just fine) Michael Thank you for pointing me to that article. That leads to two more questions: What alternative do I have? If I abort the thread that is running the query, will the query stop? And, if sqlite3_interrupt() has to be issued from the same thread that is using the database, what is it designed to be used for? OK, three questions: Is there a way to run sqlite3 queries asynchronously? Thanks again! Rob Richardson Rad-Con, Inc. -- http://MichaelScharf.blogspot.com/
RE: [sqlite] sqlite3_interrupt() and threads
Michael, I notice in the documentation that the sqlite3_progress_handler() method is marked experimental. Is that significant? Rob
