RE: [sqlite] Problem creating extension for use with load_extension
Nuno Lucas wrote > Seems like you didn't enable the extension loading mechanism. It > defaults to disabled for security reasons. > > Check the wiki page about the SQLITE_OMIT_LOAD_EXTENSION define: > * http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions I have been using the precompiled sqlite3.exe for Windows which I believe includes extension loading (.help shows the .load function, and the source code seems to indicate that this is only visible if extension loading is turned on). Please correct me if I am wrong. Still haven't been able to get this to work - does anyone have a working extension in Windows form that I could try just to make sure I am using this properly? Thanks, Bob Dankert - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Problem creating extension for use with load_extension
First off, I would like to say that although I have a lot of experience
with programming, most of it is in C#/Java and I do not have a lot of
experience with C++, although I have been working with SQLite for years.
I am attempting to create my own extension to use with SQLite but am
having problems. Using the command line interface, when I load the
extension I get the following:
SQLite version 3.5.2
Enter ".help" for instructions
sqlite> select load_extension('mydblib.dll');
SQL error: The specified procedure could not be found.
I have tried numerous things to get this to work but all have yielded
the same result. I tried modifying the SQLite source to manually add
the function I created, modifying func.c, and was successfully able to
use it in the CLI. However, I would much prefer to use an extension for
this purpose to avoid changing the source at each new release. I am
implementing an aggregate function for concatenating results in a group
by field (similar to group_concat in MySQL) and have implemented the
necessary step and finalize methods for this and passing to
sqlite3_create_function.
I am using Visual Studio 2005 to compile the project but also tried
using MinGW and had similar issues. It could very well be that I am
simply compiling the library incorrectly as I do not have a lot of
experience with this in C++, but I am unsure at this point where to
point the finger (code issue/compiling issue/etcetera). I searched the
SQLite source but cannot find anything on this error. I am running this
with Windows Vista 32-bit in a command prompt run as administrator.
Thanks for any help, let me know if I need to post a link to the
source/my project for this.
Bob Dankert
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] Question on SQL query optimization with joins
I have been pondering an issue for a while regarding the separation of query conditions from the join condition and the where condition of the query. All I have been able to find on this matter is general text "use the ON clause for conditions that specify how to join tables, and the WHERE clause to restrict which rows you want in the result set", but I have not been able to get any reason why this is? Take the following table: CREATE TABLE table1 (id INTEGER PRIMARY KEY, name TEXT); CREATE TABLE table2 (id INTEGER PRIMARY KEY, table1id INTEGER, name TEXT); CREATE INDEX table2index ON table2(table1id); And I do the following query: SELECT table2.name FROM table1 JOIN table2 ON table2.table1id = table1.id WHERE table1.name like 'bob%'; Wouldn't it be better to put the filter on table1.name in the on condition as well so it does not have to join as many rows? If this is the case, it seems it would make sense to put almost all filtering conditions in the join condition rather than the where condition. I know this is not correct and I suspect it is related to the use of indexes while joining the tables. If this is the case, would it be quicker if I had an index on both table1id and the name columns from table2? I am more or less looking at the theory behind these optimizations in SQL and not a specific case for SQLite - just trying to understand how all this stuff works so I can write better queries. Thanks! Bob Dankert
RE: [sqlite] unsuscribe
Especially considering they are all spelling the word wrong with the same mis-spelling. -Original Message- From: John Newby [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 31, 2006 1:25 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] unsuscribe whats with all these unsubscribe messages, they're beginning to do my head in now!!! On 31/05/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > > unsuscribe > > > > > >
RE: [sqlite] Purging the mailing list roles. Was: Please Restore Your Account Access
I have to agree as well - Quite frequently I am just too busy to read the list, other times I get a wealth of information from the list. I would hate to get unsubscribed because I was too busy to reply to a message. Plus, I archive all of my messages in the list so I can search for previous information. I may not read some posts right away, but they do serve a useful purpose for me later down the road. Being placed on a moderated status would be fine in my opinion, as described by Eric, for new or infrequent posters. Bob -Original Message- From: Eric Scouten [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 30, 2006 10:26 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Purging the mailing list roles. Was: Please Restore Your Account Access I agree with Jay. I'd hate to have to respond to an e-mail ping every so often just to continue reading the list. On the other hand, I wouldn't mind terribly if I got placed on "moderated" status (i.e. had to go through a verification step in order to *post* to the list) if I were either (a) new to the list, or (b) hadn't posted in a while. -Eric On 30 May 2006, at 07:57, Jay Sprenkle wrote: > On 5/30/06, Eugene Wee <[EMAIL PROTECTED]> wrote: >> Hi, >> >> Basically, what I imagined from DRH's original proposal was that >> accounts that >> have not sent out mails after some period of time would receive an >> email >> informing them that they will be unsubscribed unless they send a >> mail to the >> mailing list, or they reply to this notification email, within >> some (short) >> period of time. >> >> I have qualms about asking people to click on a link, since it may >> look like a >> bogus email in which the link will take them elsewhere. You know, >> like one of >> those "your account has expired, click on this link to renew" spam >> emails. > > I wouldn't want a lot of "I want to stay on the list" mail spamming > the list. > > You could sign up an autoresponder email account (like paypal) and > it would stay > signed up forever. It would always respond to the query email with a > reply including > the original text of the message. You'd need to set it up so they had > to reply to a > different email account than the one to send the query mail.
RE: [sqlite] Cannot load sqlite3.dll
You can not use it directly in .Net as it is not a .Net module - you need to reference it's API using PInvoke (platform invoke). You can look at some of the .Net wrappers in the wiki at http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers Bob Bob -Original Message- From: Paul Hunnisett [mailto:[EMAIL PROTECTED] Sent: Thursday, May 04, 2006 3:57 PM To: sqlite-users@sqlite.org Subject: [sqlite] Cannot load sqlite3.dll I'm trying to add sqlite3.dll ot my application in VS 2005. I downloaded the dll from the sqlite home page. VS simply says that it is not a valid assembly and can't be loaded. I have no idea what to do next... Can anyone point me in the right direction? Cheers Paul Hunnisett
RE: [sqlite] Windows SQLITE3 SUPER SLOW
Hi Sly, Can't really help without the query that you are using and more information about the database. Are you using indexes? How many records are in the tables? Bob Envision Information Technologies Associate [EMAIL PROTECTED] v. 608.256.5680 f. 608.256.3780 -Original Message- From: Sylvain Lafleur [mailto:[EMAIL PROTECTED] Sent: Friday, September 09, 2005 1:05 PM To: sqlite-users@sqlite.org Subject: [sqlite] Windows SQLITE3 SUPER SLOW Hello, Sorry if this isn't correct, it's my first post in a mailing list. My problem: I created a Ruby on Rails application with sqlite as the database. Everything worked for about a day. Now, any query with a inner join takes forever (i have yet to actually have results returned). When i pop the query in the sqlite2.exe, same problem, it's like there is an infinite loop. However, when i use the program "SQLITE Browser 1,2.1.exe", the same query returns results instantly. Does anyone have any clue what is going on here. I hope i can fix it and still use sqlite. Thanks Sly
RE: [sqlite] SUM and NULL values
I would think that if you are looking to know the total of something (which is what SUM provides), and there is nothing to total, the total should be 0. If you want to know if any sales were made, it seems you should be using COUNT and not SUM. Just my opinion, of course. Bob Envision Information Technologies Associate [EMAIL PROTECTED] v. 608.256.5680 f. 608.256.3780 -Original Message- From: Thomas Briggs [mailto:[EMAIL PROTECTED] Sent: Thursday, September 08, 2005 3:43 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] SUM and NULL values > Can somebody come up with a realistic scenario where they would > actually want SUM() to return NULL instead of 0? NULL is such I think your example of totaling sales in October makes the argument itself - you didn't make sales totaling $0 in October, you just didn't make any sales. A subtle but irritatingly big difference. :) -Tom
RE: [sqlite] Survey: NULLs and GROUP BY
MySQL 4.1.1-NT +--+--++ | a| b| sum(c) | +--+--++ |1 |2 | 2 | | NULL |2 | 4 | |1 | NULL | 8 | | NULL | NULL | 16 | +--+--++ Bob Envision Information Technologies Associate [EMAIL PROTECTED] v. 608.256.5680 f. 608.256.3780 -Original Message- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Thursday, September 01, 2005 1:51 PM To: sqlite-users@sqlite.org Subject: [sqlite] Survey: NULLs and GROUP BY I'm rewriting the aggregate function processing in SQLite (so that it runs faster and uses less memory) and I want to make sure I get it right. In particular, I want to make sure that SQLite handles NULLs in GROUP BY values the same as other database engines. Can I get some volunteers to run the SQL shown below on various other SQL database engines and tell me what the output is? CREATE TABLE t1(a INTEGER, b INTEGER, c INTEGER); INSERT INTO t1 VALUES(1,2,1); INSERT INTO t1 VALUES(NULL,2,2); INSERT INTO t1 VALUES(1,NULL,4); INSERT INTO t1 VALUES(NULL,NULL,8); INSERT INTO t1 SELECT * FROM t1; SELECT a, b, sum(c) FROM t1 GROUP BY a, b ORDER BY 3; Thanks. -- D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] Unlucky number for the ROUND function
Using the sqlite.exe 3.2.5 binary off the website with WinXP SP2 here, I get the ":.0" result (less the quotes). I have tried this on a couple machines in the office here running similar environments. Bob Envision Information Technologies Associate [EMAIL PROTECTED] v. 608.256.5680 f. 608.256.3780 -Original Message- From: D. Richard Hipp [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 30, 2005 1:08 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Unlucky number for the ROUND function On Tue, 2005-08-30 at 12:46 -0500, Bob Dankert wrote: > According to that, it rounds to the nearest even number. Shouldn't 9.95 > go to 10 then, and 9.85 go to 9.8? > > After additional testing with SQLite 3.2.2, I have the following > results: > > Round(9.95,1) -> 9.9*Rounded Down* > Round(9.85,1) -> 9.8*Rounded Down* > Round(9.5,0) -> 10 *Rounded Up* > Round(9.995,2) -> 9.99 *Rounded Down* > > I really see no pattern or sense to the results. > The reason you see no pattern is because you are thinking in decimal whereas SQLite thinks in binary. The number 9.95 does not a have finite representation in binary. The closest you can get with a 64-bit IEEE float is: 9.949289457264239899814128875732421875 So when you type "9.95" into an SQL statement, SQLite really inserts the number shown above, not 9.95. And the number shown above rounds down. 9.5 does have an exact representation in binary so it rounds as you would expect. But neither 9.85 nor 9.995 do - the binary values chosen to represent them are both just a little less than their decimal values. Hence they both round down. So I'm not overly worried when I see round(9.95,1) come out with 9.9. But I am concerned about the people who are seeing results like ":.0". I wish I could reproduce that problem. -- D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] Unlucky number for the ROUND function
According to that, it rounds to the nearest even number. Shouldn't 9.95 go to 10 then, and 9.85 go to 9.8? After additional testing with SQLite 3.2.2, I have the following results: Round(9.95,1) -> 9.9*Rounded Down* Round(9.85,1) -> 9.8*Rounded Down* Round(9.5,0) -> 10 *Rounded Up* Round(9.995,2) -> 9.99 *Rounded Down* I really see no pattern or sense to the results. Bob Envision Information Technologies Associate [EMAIL PROTECTED] v. 608.256.5680 f. 608.256.3780 -Original Message- From: Kervin L. Pierre [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 30, 2005 12:18 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Unlucky number for the ROUND function Interesting... Bankers' Rounding http://blogs.msdn.com/ericlippert/archive/2003/09/26/53107.aspx Brass Tilde wrote: >>From: "Bob Dankert" > > >>Using an older version which I compiled, I get 9.9, though it >>seems it should round up to 10.0? > > > This may be dependent upon the math library linked into the application > by the compiler used to build SQLite. Some libraries appear to now be > using so-called "banking rounding" (though there are other names), where > a "5" digit is rounded up or down depending upon the digit immediately > to its left. If that digit is odd, it rounds one way, if even, the > other. It looks like in this case, 9.95 would round to 9.9, while 9.85 > would likely round to 9.9 as well. Try rounding 9.85 and see what you > get. > > Brad > > > > > > > . >
RE: [sqlite] Unlucky number for the ROUND function
Using the downloaded command-line tool for 3.2.5, I get the same :.0 result. Using an older version which I compiled, I get 9.9, though it seems it should round up to 10.0? Bob Envision Information Technologies Associate [EMAIL PROTECTED] v. 608.256.5680 f. 608.256.3780 -Original Message- From: Eric Bohlman [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 30, 2005 11:05 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Unlucky number for the ROUND function Edzard Pasma wrote: > I found a number where the ROUND () function goes wrong: > > SQLite version 3.2.5 > Enter ".help" for instructions > sqlite> select round (9.95, 1); > :.0 I get 9.9 (running on Win98, compiled with MingW).
[sqlite] How to update wiki? date/time page misprint
I'm not sure if it is possible for myself to update the SQLite wiki, but on the Date and Time Functions page, http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions, the text for the format of strftime is incorrect for the %j format. I believe this should specify 001-366 instead of 000-366. A minor change, but may cause confusion for some. Thanks, Bob Envision Information Technologies Associate [EMAIL PROTECTED] v. 608.256.5680 f. 608.256.3780
RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working
I am using a fully patched/updated VS 2003 and have never had an issue with sqlite on any OS, including Win 2003 (SP1 and pre-SP1). I am still using a slightly older 3.x build, however, and have not tested the newest build. Also, I have some code modifications in my sqlite dll but I doubt it would affect the issue you are seeing. Bob Envision Information Technologies Associate [EMAIL PROTECTED] v. 608.256.5680 f. 608.256.3780 -Original Message- From: Derek Shaw [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 12, 2005 6:10 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working Yeah, :) I just found those. Don't quite have it hooked in yet. But we shall see. I spoke with MS, they are saying that what is happening with the gcc gened dll is that when the dll loads it has to get relocated, when the dll tables get fixed up there are a couple of entries that point outside itself. This seems to point to a gcc bug to me, but who knows. If the vc build still has the same problem they are going to take a look at it for me :) I assume you are running under SP1? Everything works fine? ~derek -Original Message- From: Bob Dankert [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 12, 2005 4:01 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working I followed these instructions for VS 2003 http://www.sqlite.org/cvstrac/wiki?p=HowToCompileWithVsNet Bob Envision Information Technologies Associate [EMAIL PROTECTED] v. 608.256.5680 f. 608.256.3780 -Original Message- From: Derek Shaw [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 12, 2005 5:22 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working Peerrrfect. I will give that a shot, I was trying to figure out if the preprocessed files might get in the way of the dll config. But perhaps not. I am using VS.NET 2003. I'll let you know what happens, thanks. ~derek. -Original Message- From: Tim McDaniel [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 12, 2005 3:16 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working Which version of VC++ do you have? I'm using VS.NET 2003. In any case, it isn't diffcult. You can download the "pre-processed" source for Windows from sqlite.org. Create a dll project, and add the source files, including sqlite3.def, and excluding shell.c. I specify the NO_TCL macro to omit any TCL related stuff, but I don't remember if that is essential. > -Original Message- > From: Derek Shaw [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 12, 2005 5:01 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working > > That would be great for me... How are you building it? Just > down load the source and point a project at it? I can imagine > its going to be that easy? I could not find any documentation > on building with VC++. > > I am working on upgrading to 3.2.2, I doubt its going to make > a difference, but you never know. I just download the dll and > run lib against it to get the lib and such. Then soft link to > it that way. > > I would not be surprised to find out that it has something to > do with the gcc build... > > ~derek. > > -Original Message- > From: Tim McDaniel [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 12, 2005 2:54 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working > > > > -Original Message- > > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, July 12, 2005 1:20 PM > > To: sqlite-users@sqlite.org > > Subject: Re: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working > > > > On Tue, 2005-07-12 at 11:01 -0700, Derek Shaw wrote: > > > SQLite 3 relocates 2 addresses out of its memory bounds > > when it loads. > > > > Can you explan in more detail what this means? > > -- > > D. Richard Hipp <[EMAIL PROTECTED]> > > > > If this is really the problem, then it's probably some "problem" with > GCC compiling for Win32, or maybe some compile switch needs to be > adjusted. I don't have any details, I've only compiled sqlite with > Visual C++. >
RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working
I followed these instructions for VS 2003 http://www.sqlite.org/cvstrac/wiki?p=HowToCompileWithVsNet Bob Envision Information Technologies Associate [EMAIL PROTECTED] v. 608.256.5680 f. 608.256.3780 -Original Message- From: Derek Shaw [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 12, 2005 5:22 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working Peerrrfect. I will give that a shot, I was trying to figure out if the preprocessed files might get in the way of the dll config. But perhaps not. I am using VS.NET 2003. I'll let you know what happens, thanks. ~derek. -Original Message- From: Tim McDaniel [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 12, 2005 3:16 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working Which version of VC++ do you have? I'm using VS.NET 2003. In any case, it isn't diffcult. You can download the "pre-processed" source for Windows from sqlite.org. Create a dll project, and add the source files, including sqlite3.def, and excluding shell.c. I specify the NO_TCL macro to omit any TCL related stuff, but I don't remember if that is essential. > -Original Message- > From: Derek Shaw [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 12, 2005 5:01 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working > > That would be great for me... How are you building it? Just > down load the source and point a project at it? I can imagine > its going to be that easy? I could not find any documentation > on building with VC++. > > I am working on upgrading to 3.2.2, I doubt its going to make > a difference, but you never know. I just download the dll and > run lib against it to get the lib and such. Then soft link to > it that way. > > I would not be surprised to find out that it has something to > do with the gcc build... > > ~derek. > > -Original Message- > From: Tim McDaniel [mailto:[EMAIL PROTECTED] > Sent: Tuesday, July 12, 2005 2:54 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working > > > > -Original Message- > > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, July 12, 2005 1:20 PM > > To: sqlite-users@sqlite.org > > Subject: Re: [sqlite] sqlite 3.x and Win Server 2003 SP1 not working > > > > On Tue, 2005-07-12 at 11:01 -0700, Derek Shaw wrote: > > > SQLite 3 relocates 2 addresses out of its memory bounds > > when it loads. > > > > Can you explan in more detail what this means? > > -- > > D. Richard Hipp <[EMAIL PROTECTED]> > > > > If this is really the problem, then it's probably some "problem" with > GCC compiling for Win32, or maybe some compile switch needs to be > adjusted. I don't have any details, I've only compiled sqlite with > Visual C++. >
RE: [sqlite] SQL functions - documentation?
Adam, Most of the functions (except date/time functions) are covered on this page: http://www.sqlite.org/lang_expr.html Many functions are intentionally left out of SQLite but can be easily added. Bob Envision Information Technologies Associate [EMAIL PROTECTED] v. 608.256.5680 f. 608.256.3780 -Original Message- From: hilaner [mailto:[EMAIL PROTECTED] Sent: Monday, March 28, 2005 12:08 PM To: sqlite-users@sqlite.org Subject: [sqlite] SQL functions - documentation? Hi All, Is there any description of SQL functions which are implemented in SQLite? I mean functions like substr, mean, etc. (date and time functions have their documentation in wiki) Only in some source files of the SQLite? Regards, Adam
RE: [sqlite] Is this possible in SQLite?
Unfortunately, as the syntax on the website points out, this is not supported. As you pointed out previously, it is doable with a few extra commands. Bob Envision Information Technologies Associate [EMAIL PROTECTED] v. 608.256.5680 f. 608.256.3780 -Original Message- From: John O'Neill [mailto:[EMAIL PROTECTED] Sent: Thursday, March 17, 2005 3:48 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Is this possible in SQLite? Hi Dennis, Thanks for the reply. In the original "INSERT" commands, my intention was to update a field in the columns as they were being copied to the new table. Sorry, I didn't mean just "SELECT ... WHERE id=1" as the only condition...I'd like to select those items and update their primary keys to a new value (hence the "SET" command) as they are being inserted into the new table. So for example, in table 'a' there might be a column that has primary key = 1, and in the copied version, I want to set that primary key = 2 or some other unique value. I guess this question is can I combine an UPDATE...SET with an INSERT...SELECT command? Thanks, John -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Thursday, March 17, 2005 4:32 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Is this possible in SQLite? John O'Neill wrote: >Hello all, > >I have a fairly simple DB with two tables. I'm trying to combine a >SELECT and UPDATE command, if it is possible: > >CREATE TABLE a (id PRIMARY KEY, data INT); >CREATE TABLE b (id INT, data INT); > >INSERT INTO a VALUES( 1, 100 ); >INSERT INTO b VALUES( 1, 101 ); >INSERT INTO b VALUES( 1, 102 ); >INSERT INTO b VALUES( 1, 103 ); >... > >And at some point in the future, two new tables (possibly in a >different database) are created: > >CREATE TABLE acopy (id PRIMARY KEY, data INT); >CREATE TABLE bcopy (id INT, data INT ); > >Is there a way to do the following: > >INSERT INTO acopy SELECT * FROM a WHERE id = 1 ( SET id = some value X >); INSERT INTO bcopy SELECT * FROM b WHERE id = 1 ( SET id = X ); > >Instead of doing the following 4 commands or SELECTing a and b into >TEMP tables: > >UPDATE a SET id = X WHERE id = 1; >UPDATE b SET id = X WHERE id = 1; >INSERT INTO acopy SELECT * FROM a; >INSERT INTO bcopy SELECT * FROM b; > >Thanks, >John > > > > > John, The following will do what you have asked (I substituted the letter 'X' for your new value X for clarity), but I'm not sure if this is general enough for your real needs. CREATE TABLE a (id PRIMARY KEY, data INT); CREATE TABLE b (id INT, data INT); INSERT INTO a VALUES( 1, 100 ); INSERT INTO b VALUES( 1, 101 ); INSERT INTO b VALUES( 1, 102 ); INSERT INTO b VALUES( 1, 103 ); CREATE TABLE acopy (id PRIMARY KEY, data INT); CREATE TABLE bcopy (id INT, data INT ); INSERT INTO acopy SELECT 'X', data FROM a WHERE id = 1; INSERT INTO bcopy SELECT 'X', data FROM b WHERE id = 1; SELECT * FROM acopy; SELECT * from bcopy; HTH Dennis Cote
[sqlite] User functions
Is there a good example somewhere of how to implement user-defined functions? Thanks Bob Envision Information Technologies Associate [EMAIL PROTECTED] v. 608.256.5680 f. 608.256.3780
RE: [sqlite] basic sql question: Is there a way of entering multiple rows in a table?
I would use attach database, and then use an insert select.. Ie. Attach database db2; Insert into table1 select * from db2.table2; Hope this helps... Bob Envision Information Technologies Associate [EMAIL PROTECTED] v. 608.256.5680 f. 608.256.3780 From: Richard Boyd [mailto:[EMAIL PROTECTED] Sent: Friday, February 11, 2005 1:06 PM To: sqlite-users@sqlite.org Subject: [sqlite] basic sql question: Is there a way of entering multiple rows in a table? Hi, I know I can create a temporary table and insert them from that or use a for loop in C and get it to update the table one row at a time, but is there a 'nicer' way?? I basically have a string of text which I've obtained from another (remote) Sqlite database which I want to append to the end of an existing table in a separate database. Is there a better way than doing it one row at a time? TIA, Richard.
[sqlite] how to use multiple tables in an update or delete?
Is there any way to use multiple tables in an update or a delete? Most databases support doing something like: Update table1 join table2 on table1.index = table2.index set table1.column = value The only way I know around it is to use a subselect in the filter for the update/delete, but this can take some time with complicated queries. Is there any plans to add this functionality at anytime?? Thanks, Bob Envision Information Technologies Associate [EMAIL PROTECTED] v. 608.256.5680 f. 608.256.3780
RE: [sqlite] How to unite query results from two databases
I havent worked with multiple databases before, but I would think you could just union two queries together if nothing else. Eg: select ... union select ... order by col Bob Envision Information Technologies Associate [EMAIL PROTECTED] v. 608.256.5680 f. 608.279.3780 -Original Message- From: Michael Ruck [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 09, 2005 12:49 PM To: sqlite-users@sqlite.org Subject: [sqlite] How to unite query results from two databases I need to perform a select, which queries two databases (same tables and columns in both databases; both open at the same time in the same SQLite session via ATTACH DATABASE.) I need the query result to appear as a single result with sorting etc. performed on the entire result from both databases. Does someone have an idea of how to perform this in SQLite? Thanks, Michael
RE: [sqlite] download db - security question
My tip and advice would be to stop sending the same message over and over. Bob -Original Message- From: Ramon [mailto:[EMAIL PROTECTED] Sent: Monday, January 24, 2005 12:55 AM To: sqlite-users@sqlite.org Subject: [sqlite] download db - security question I was wondering if someone can just download off my webpage the sqlite database. And if they can is there a way to block this type of download throw apache? Any tips and advices are welcome. Thanks, Ramon
RE: [sqlite] Union queries with sub-select tables with limits returns no results
Thanks for the help, Dennis. I created a bug with ticket number 1035 for anyone interested in following up on this. Thanks! Bob -Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Thursday, December 16, 2004 2:01 PM To: [EMAIL PROTECTED] Subject: Re: [sqlite] Union queries with sub-select tables with limits returns no results Bob Dankert wrote: > I am trying to union two queries with each query having a sub-select > defining the table, but I consistenly get no results if I try to limit > the sub-query. Here is my query, assuming the table a and table > contain one column filled with integers. > > Select * from (select * from a limit 3) as a; <-- this returns 3 > results > Select * from (select * from b limit 3) as b; <-- this returns 3 > results > > Select * from (select * from a limit 3) as a union select * from > (select * from b limit 3) as b; <-- this returns 0 results > > If I get rid of the limits, everything is returned from both tables as > it should be: > Select * from (select * from a) as a union select * from (select * > from b) as b; > > Unfortunately, I need to limit the results in individual queries which > are being unioned together. Since SQLite does not support limiting > individual queries in a union, I was hoping to use sub-selects for the > tables and limit these. Does anyone have any suggestions as to what I > can do for this? > > Thanks, > > Bob Dankert Bob, As a work around you can create temp tables from the two sub-selects and use a union select to combine them, or create a temp table with the output of the first sub-select and then insert the result of the second sub-select into the temp table and then dump that table. create temp table t1 as select * from a limit 3; create temp table t2 as select * from b limit 3; select * from t1 union select * from t2; or create temp table u as select * from a limit 3; insert into u select * from b limit 3; select * from u; I hope this helps.
[sqlite] Union queries with sub-select tables with limits returns no results
I am trying to union two queries with each query having a sub-select defining the table, but I consistenly get no results if I try to limit the sub-query. Here is my query, assuming the table a and table contain one column filled with integers. Select * from (select * from a limit 3) as a; <-- this returns 3 results Select * from (select * from b limit 3) as b; <-- this returns 3 results Select * from (select * from a limit 3) as a union select * from (select * from b limit 3) as b; <-- this returns 0 results If I get rid of the limits, everything is returned from both tables as it should be: Select * from (select * from a) as a union select * from (select * from b) as b; Unfortunately, I need to limit the results in individual queries which are being unioned together. Since SQLite does not support limiting individual queries in a union, I was hoping to use sub-selects for the tables and limit these. Does anyone have any suggestions as to what I can do for this? Thanks, Bob Dankert -Original Message- From: George Ionescu [mailto:[EMAIL PROTECTED] Sent: Thursday, December 16, 2004 1:00 AM To: SQLite Forum Subject: Re: [sqlite] db admin tool Hello sten, you could try SQLiteDb Query Analyzer from http://www.terrainformatica.com/sqlitedb (it's included in the SQLiteDb install package). It's not much right now but it's going to be improved alot in the next weeks. Best regards, George Ionescu
RE: [sqlite] Documentation help
I believe if you take a look in the wiki, you will find the date and time functions. Otherwise, look through the syntax section and the wiki section of the web site. Bob Dankert -Original Message- From: John Mistler [mailto:[EMAIL PROTECTED] Sent: Thursday, October 14, 2004 4:47 PM To: [EMAIL PROTECTED] Subject: [sqlite] Documentation help I am new to SQLite and am trying to gather more detailed information about the available functions and syntax than what it set out in the "Syntax" page. One example would be: Where can I find out how to return a date from a datetime column in the format "12/24/2004 08:45:23 PM" rather than "2004-12-24 20:45:23"? Can someone lead me to this TYPE of documentation? Is there a searchable SQLite documentation database? Thanks, John
[sqlite] Get a listing of tables
Is there any way through a sql call to get a listing of tables in sqlite? I tried '.tables' as would be used at the console application, but this is not supported. Thanks! Bob Dankert
[sqlite] string concatenation
I noticed that sqlite uses || as a string concatenation operator, but I am curious if they also support the concat(arg1,arg2...argn) function as well, and if so, since what version? I couldn't find anything related to this documented, but after doing some tests it seems as though the concat function is supported. Bob Dankert
RE: [sqlite] Encryption?
http://www.hwaci.com/sw/sqlite/prosupport.html#crypto I would also recommend going through the mailing list archives as I know there have been a couple conversations per this topic in the last few weeks. Bob -Original Message- From: Bryan Ashby [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 22, 2004 11:22 AM To: [EMAIL PROTECTED] Subject: [sqlite] Encryption? I've searched and can't find a definite answer - does SQLite 3 support encryption? If so, how? I knoticed a sqlite3_key() and sqlite3_rekey() that say something about encryption but am unsure of how to use them. Thanks, Bryan
[sqlite] Default column value as current date
Is there any way to make the default column value as the current datetime? Thanks! Bob
RE: [sqlite] Encrypting data stored in database
Joey,
You are going to have to excuse my ignorance of c++, but I am a bit
confused as far as the ppdbuffersize and ppdbuffer. Is there additional
code I would need to add elsewhere for these variables, and if so, would
you mind including those as well??
I'm very sorry if this is really stupid, but I'm stuck in a C#.Net world
and don't have to deal with this stuff.
Thanks!
Bob
-Original Message-
From: Joey Blankenship [mailto:[EMAIL PROTECTED]
Sent: Friday, September 10, 2004 2:48 PM
To: [EMAIL PROTECTED]
Subject: RE: [sqlite] Encrypting data stored in database
I'm including the routines that we modified. I hate to send the whole
file
around the entire list. The ppdbuffer and ppdbuffersize are set
initially
when the database is opened and closed. The current implementation may
not
be threadsafe, but we are single threaded.
extern int ppdbuffersize;
extern void *ppdbuffer;
int sqlite3OsRead(OsFile *id, void *pBuf, int amt){
DWORD got;
int i;
assert( id->isOpen );
SimulateIOError(SQLITE_IOERR);
TRACE3("READ %d lock=%d\n", id->h, id->locktype);
if( !ReadFile(id->h, pBuf, amt, , 0) ){
got = 0;
}
// PPD - XOR the buffer with a pattern so the disk file contents are
not
in plain text
for (i = 0; i < got/4; i++)
{
*((DWORD *)((DWORD *)pBuf + i)) = (*((DWORD *)((DWORD *)pBuf +
i)))^0xA5A5A5A5;
}
// XOR the buffer with a pattern - any leftover bytes
for (i = 0; i < got%4; i++)
{
*((BYTE *)((BYTE *)pBuf + i)) = (*((BYTE *)((BYTE *)pBuf +
i)))^0xA5;
}
if( got==(DWORD)amt ){
return SQLITE_OK;
}else{
return SQLITE_IOERR;
}
}
int sqlite3OsWrite(OsFile *id, const void *pBuf, int amt){
int rc;
DWORD wrote;
int i;
if (ppdbuffersize < amt)
{
ppdbuffersize = amt + 1024;
ppdbuffer = realloc(ppdbuffer, ppdbuffersize);
}
// PPD - XOR the buffer with a pattern so the disk file contents are
not
in plain text
for (i = 0; i < amt/4; i++)
{
*((DWORD *)((DWORD *)ppdbuffer + i)) = (*((DWORD *)((DWORD *)pBuf +
i)))^0xA5A5A5A5;
}
// XOR the buffer with a pattern - any leftover bytes
for (i = 0; i < amt%4; i++)
{
*((BYTE *)((BYTE *)ppdbuffer + i)) = (*((BYTE *)((BYTE *)pBuf +
i)))^0xA5;
}
pBuf = ppdbuffer;
assert( id->isOpen );
SimulateIOError(SQLITE_IOERR);
TRACE3("WRITE %d lock=%d\n", id->h, id->locktype);
while( amt>0 && (rc = WriteFile(id->h, pBuf, amt, , 0))!=0 &&
wrote>0 ){
amt -= wrote;
pBuf = &((char*)pBuf)[wrote];
}
if( !rc || amt>(int)wrote ){
return SQLITE_FULL;
}
return SQLITE_OK;
}
At 03:03 PM 9/10/2004, you wrote:
>Joey,
>
>Would you mind sharing the modifications you made to the os_win.c file?
>I would be interested in using such a modification, unfortunately, c++
>isn't my strongest language.
>
>Thanks!
>
>Bob
RE: [sqlite] Encrypting data stored in database
Joey,
Would you mind sharing the modifications you made to the os_win.c file?
I would be interested in using such a modification, unfortunately, c++
isn't my strongest language.
Thanks!
Bob
-Original Message-
From: Joey Blankenship [mailto:[EMAIL PROTECTED]
Sent: Friday, September 10, 2004 8:00 AM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] Encrypting data stored in database
We made the mods in os_win.c, in the read and write routines, after the
read and before the write. A more robust solution would have been to
modify the code in os_mac.c and os_unix.c as well, but we didn't need
that. It was very easy and the only caveat is that once you modify it,
it
will no longer read existing databases. That was not a problem for us,
but
it might be if you want to convert a database.
Joey.
At 06:11 PM 9/9/2004, you wrote:
>Joey,
>
>Where in the code did you do that? Was it easy to put in one or two
places?
>
>-brett
>
>try IeToolbox Passwords & Notes Keeper, Form Filler and much more
>www.ietoolbox.com
>
>
>
>Joey Blankenship wrote:
>
>>Not to respond out of turn here, but we had a need to obfuscate the
plain
>>text (due to some users that are a little too curious for their own
good)
>>that was stored but did not want the performance burden that
>>encryption/decryption would place on the PocketPC. Prior to writing
and
>>subsequent to reading, we just perform a simple mangle of the
>>read/written data. A byte NOT or XOR works pretty well and does not
>>cause an extreme performance hit. Again, no real encryption, but it
>>hides the data from casual viewing.
>>
>>Joey.
>>
>>At 05:34 AM 9/9/2004, you wrote:
>>
>>>Hello sqlite users,
>>>Hello Dr. Hipp,
>>>
>>>I would like to know if there is a way to encrpyt the data stored in
the
>>>database but to still be able to use SQL queries with plain text,
>>>something like
>>>
>>>CREATE TABLE TEST (fld)
>>>INSERT INTO Test VALUES ('some string')
>>>SELECT * FROM Test WHERE fld = 'some string'
>>>
>>>but, when the sqlite database is opened in a file viewer, 'some
string'
>>>would not be visible but only it's encrypted form.
>>>
>>>Regards,
>>>George Ionescu
>>
>>
[sqlite] Encrypt or somehow protect database?
I am wondering if there is some way that a SQLite database file can be encrypted or password protected, or something similar to this? I would like to prevent any mischievous users from digging through the database if they figure it out to be a sqlite file. Thanks! Bob
