Re: [sqlite] excel vba use sqlite
Best way to do this is I think with this wrapper: http://www.vbrichclient.com/#/en/About/ It is very fast and has lots of other useful utilities other than SQLite, eg fast collection and dictionary classes. RBS On Thu, Jan 22, 2015 at 1:04 AM, YAN HONG YE yanhong...@mpsa.com wrote: I don't know how to use sqlite in EXCEL vba? Need I install sqlite connect driver? ___ 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] Send Mail from sqlite
Not sure now if SQLite does events that can be picked up by your application, but I use a VB wrapper (vbRichClient, written by Olaf Schmidt) that does do SQLite events. If events can't be used then I presume you need to poll the database for changes. RBS On Fri, Feb 7, 2014 at 10:06 AM, Simon Slavin slav...@bigfraud.org wrote: On 7 Feb 2014, at 9:59am, Vairamuthu muthu.moor...@gmail.com wrote: Thanks for your response, it will be great help if you can get me some sample code or algorithms, on that. That would depend on what programming language you are using and what access it has to any method of sending mail. However you do it, it won't be done inside SQLite so it won't be anything to do with this mailing list. 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] how to use sqlite in excel vba?
The vbRichClient appears to be *very sparsely* documented In practice this is not really a problem. I have been using this library for a few years (in a commercial application) and never found it difficult to get the answers. This is because the SQLite objects and methods closely resemble ADO objects and methods (and most likely VBA people will be familiar with that) and also because answers are easy to get, either from Olaf or via the microsoft.public.vb.general.discussion newsgroup. I am also happy to provide demo code. Very much recommended. RBS On Tue, Aug 6, 2013 at 4:27 PM, Larry Brasfield larry_brasfi...@iinet.comwrote: Wolfgang Enzinger wrote: Assuming you actually do need to compile something, (identity of which you provide few good clues), you might consider a package I had good luck with, a SQLite wrapper called 'Litex', available at https://www.assembla.com/wiki/**show/litexhttps://www.assembla.com/wiki/show/litex The download links on this page don't seem to work properly ...? My recommendation for a SQLite COM wrapper would be this: http://www.vbrichclient.com I'm not getting into a debate about what is best for the OP, having too few clues to do so. The vbRichClient appears to be *very sparsely* documented, with nothing on the SQLite interfaces. And it is closed source. There does not seem to be any demo code for the SQLite functionality. So a user would be left guessing how to use it from what the type library suggests. The Litex wrapper source is available in zipped form via the Subversion repository browser visible at the page I linked. It can be built in Unicode and non-Unicode forms. There is extensive documentation for it. It is partitioned into a useful C++ wrapper and an ActiveX packaging over that wrapper. The Litex source is a little old, and had to be modified slightly to build with more modern tools than VisualC++ 6.0. That would not be an issue for the OP. Cheers, -- Larry Brasfield __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://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] Strange table behaviour after text import with sqlite3.exe
Have table defined like this: CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT) Table is empty, so has no records. Then I import a text file with this data: FIELD1,FIELD2 1,ABC 2,BCD 3,CDE This is via sqlite3.exe with: .mode csv .import textfilename QQQ Table will then be like this: FIELD1 FIELD2 - 0 FIELD2 1 ABC 2 BCD 3 CDE This is all fine and as expected. However I am unable to produce any records when doing a select with a where clause specifying field1 to be zero. Tried all: select * from qqq where field1 = 0 select * from qqq where field1 = '0' select * from qqq where field1 = '' select * from qqq where field1 is null Nil producing a record. Any idea what is going on here or what I might be doing wrong? RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange table behaviour after text import with sqlite3.exe
Whatever it gets turned into, my question is how I can select that record
with a where
clause specifying field1?
Surely, there must be some way.
RBS
On Sat, Jun 8, 2013 at 3:48 PM, Michael Black mdblac...@yahoo.com wrote:
What makes you think field1 gets turned into a zero? Fields are really
typeless in SQLite3
Your .dump should look like this:
SQLite version 3.7.16.2 2013-04-12 11:52:43
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT);
sqlite .mode csv
sqlite .import qqq qqq
Error: cannot open qqq
sqlite .import qqq.txt qqq
sqlite .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT);
INSERT INTO QQQ VALUES('FIELD1','FIELD2');
INSERT INTO QQQ VALUES(1,'ABC');
INSERT INTO QQQ VALUES(2,'BCD');
INSERT INTO QQQ VALUES(3,'CDE');
COMMIT;
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Bart Smissaert
Sent: Saturday, June 08, 2013 9:43 AM
To: General Discussion of SQLite Database
Subject: [sqlite] Strange table behaviour after text import with
sqlite3.exe
Have table defined like this:
CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT)
Table is empty, so has no records.
Then I import a text file with this data:
FIELD1,FIELD2
1,ABC
2,BCD
3,CDE
This is via sqlite3.exe with:
.mode csv
.import textfilename QQQ
Table will then be like this:
FIELD1 FIELD2
-
0 FIELD2
1 ABC
2 BCD
3 CDE
This is all fine and as expected.
However I am unable to produce any records when doing a select
with a where clause specifying field1 to be zero.
Tried all:
select * from qqq where field1 = 0
select * from qqq where field1 = '0'
select * from qqq where field1 = ''
select * from qqq where field1 is null
Nil producing a record.
Any idea what is going on here or what I might be doing wrong?
RBS
___
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] Strange table behaviour after text import with sqlite3.exe
Aaah, OK, that answers my question! This is something I hadn't realised at all and good to know that one. Thanks for clearing this up. RBS On Sat, Jun 8, 2013 at 3:48 PM, RSmith rsm...@rsweb.co.za wrote: Yes, FIELD1 values are formatted int he output to be displayed as 0 since it is a INTEGER field, but the real value of FIELD1 is FIELD1 for the 0th record, since that is what was imported from the CSV. The formatted value is not always the same as the real value. try: select * from qqq where field1 = FIELD1 It will pop out a record I'm sure. On 2013/06/08 16:42, Bart Smissaert wrote: Have table defined like this: CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT) Table is empty, so has no records. Then I import a text file with this data: FIELD1,FIELD2 1,ABC 2,BCD 3,CDE This is via sqlite3.exe with: .mode csv .import textfilename QQQ Table will then be like this: FIELD1 FIELD2 --**--- 0 FIELD2 1 ABC 2 BCD 3 CDE This is all fine and as expected. However I am unable to produce any records when doing a select with a where clause specifying field1 to be zero. Tried all: select * from qqq where field1 = 0 select * from qqq where field1 = '0' select * from qqq where field1 = '' select * from qqq where field1 is null Nil producing a record. Any idea what is going on here or what I might be doing wrong? RBS __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://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-**usershttp://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] Strange table behaviour after text import with sqlite3.exe
Yes, thanks, all clear now. RBS On Sat, Jun 8, 2013 at 4:03 PM, Michael Black mdblac...@yahoo.com wrote: Or to get all the non-integer records. select * from qqq where typeof(field1) 'integer'; Mike -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Bart Smissaert Sent: Saturday, June 08, 2013 9:58 AM To: rsm...@rsweb.co.za; General Discussion of SQLite Database Subject: Re: [sqlite] Strange table behaviour after text import with sqlite3.exe Aaah, OK, that answers my question! This is something I hadn't realised at all and good to know that one. Thanks for clearing this up. RBS On Sat, Jun 8, 2013 at 3:48 PM, RSmith rsm...@rsweb.co.za wrote: Yes, FIELD1 values are formatted int he output to be displayed as 0 since it is a INTEGER field, but the real value of FIELD1 is FIELD1 for the 0th record, since that is what was imported from the CSV. The formatted value is not always the same as the real value. try: select * from qqq where field1 = FIELD1 It will pop out a record I'm sure. On 2013/06/08 16:42, Bart Smissaert wrote: Have table defined like this: CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT) Table is empty, so has no records. Then I import a text file with this data: FIELD1,FIELD2 1,ABC 2,BCD 3,CDE This is via sqlite3.exe with: .mode csv .import textfilename QQQ Table will then be like this: FIELD1 FIELD2 --**--- 0 FIELD2 1 ABC 2 BCD 3 CDE This is all fine and as expected. However I am unable to produce any records when doing a select with a where clause specifying field1 to be zero. Tried all: select * from qqq where field1 = 0 select * from qqq where field1 = '0' select * from qqq where field1 = '' select * from qqq where field1 is null Nil producing a record. Any idea what is going on here or what I might be doing wrong? RBS __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users http://sqli te.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 http://sqli te.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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange table behaviour after text import with sqlite3.exe
Not sure what you are getting at now. Select * from QQQ That is exactly what I did. It is all cleared up now in any case. RBS On Sat, Jun 8, 2013 at 4:56 PM, Keith Medcalf kmedc...@dessus.com wrote: Table will then be like this: FIELD1 FIELD2 - 0 FIELD2 1 ABC 2 BCD 3 CDE How did you arrive at this -- the table looks NOTHING like what you (I can only assume) you ASSUMED it looks like. Instead of assuming what the table looks like, why not actually SEE what it looks like by executing: Select * from QQQ; The error of your untested assumption should then be clear ... --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Bart Smissaert Sent: Saturday, 08 June, 2013 08:43 To: General Discussion of SQLite Database Subject: [sqlite] Strange table behaviour after text import with sqlite3.exe Have table defined like this: CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT) Table is empty, so has no records. Then I import a text file with this data: FIELD1,FIELD2 1,ABC 2,BCD 3,CDE This is via sqlite3.exe with: .mode csv .import textfilename QQQ Table will then be like this: FIELD1 FIELD2 - 0 FIELD2 1 ABC 2 BCD 3 CDE This is all fine and as expected. However I am unable to produce any records when doing a select with a where clause specifying field1 to be zero. Tried all: select * from qqq where field1 = 0 select * from qqq where field1 = '0' select * from qqq where field1 = '' select * from qqq where field1 is null Nil producing a record. Any idea what is going on here or what I might be doing wrong? RBS ___ 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] Strange table behaviour after text import with sqlite3.exe
I did (select * from qqq) but not from the sqlite3 shell. I can see that via the shell you get the output as you mention. My output was via a VB wrapper and I take it that the zero showing instead of FIELD1 has to do with that. Will have a look at that and thanks for alerting me to this discrepancy between the output from sqlite3.exe and from this wrapper. RBS On Sat, Jun 8, 2013 at 6:50 PM, Keith Medcalf kmedc...@dessus.com wrote: I don't think you did because if you had then what you would have seen would be as follows: SQLite version 3.7.17 2013-06-05 16:17:21 Enter .help for instructions Enter SQL statements terminated with a ; sqlite CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT); sqlite .mode csv sqlite .import csv.txt QQQ sqlite select * from QQQ; FIELD1,FIELD2 1,ABC 2,BCD 3,CDE Which clearly indicates that there are 4 rows in table and that the value of FIELD1 where field2='FIELD2' is 'FIELD1'. Of course, if you want column headers, then you see the same result: sqlite .header on sqlite select * from QQQ; FIELD1,FIELD2 FIELD1,FIELD2 1,ABC 2,BCD 3,CDE Or are you saying that you misinterpreted the first row of results from the select as column headers when they were not? I do not see how you could possibly obtain something that looks like: Table will then be like this: FIELD1 FIELD2 - 0 FIELD2 1 ABC 2 BCD 3 CDE --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Bart Smissaert Sent: Saturday, 08 June, 2013 10:52 To: General Discussion of SQLite Database Subject: Re: [sqlite] Strange table behaviour after text import with sqlite3.exe Not sure what you are getting at now. Select * from QQQ That is exactly what I did. It is all cleared up now in any case. RBS On Sat, Jun 8, 2013 at 4:56 PM, Keith Medcalf kmedc...@dessus.com wrote: Table will then be like this: FIELD1 FIELD2 - 0 FIELD2 1 ABC 2 BCD 3 CDE How did you arrive at this -- the table looks NOTHING like what you (I can only assume) you ASSUMED it looks like. Instead of assuming what the table looks like, why not actually SEE what it looks like by executing: Select * from QQQ; The error of your untested assumption should then be clear ... --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Bart Smissaert Sent: Saturday, 08 June, 2013 08:43 To: General Discussion of SQLite Database Subject: [sqlite] Strange table behaviour after text import with sqlite3.exe Have table defined like this: CREATE TABLE QQQ([FIELD1] INTEGER, [FIELD2] TEXT) Table is empty, so has no records. Then I import a text file with this data: FIELD1,FIELD2 1,ABC 2,BCD 3,CDE This is via sqlite3.exe with: .mode csv .import textfilename QQQ Table will then be like this: FIELD1 FIELD2 - 0 FIELD2 1 ABC 2 BCD 3 CDE This is all fine and as expected. However I am unable to produce any records when doing a select with a where clause specifying field1 to be zero. Tried all: select * from qqq where field1 = 0 select * from qqq where field1 = '0' select * from qqq where field1 = '' select * from qqq where field1 is null Nil producing a record. Any idea what is going on here or what I might be doing wrong? RBS ___ 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-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] Import skip first line
Thanks, will have a look at that as well. In my particular situation though I wouldn't want the auto data-type detection as that will slow things down and I will always know what the table fields affinities should be. Is there a way to use the other features, but not this one, say if the table is already there? RBS On Fri, May 31, 2013 at 11:21 PM, Roger Binns rog...@rogerbinns.com wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 31/05/13 05:34, Bart Smissaert wrote: Importing a. csv file via the. import command of sqlite3.exe. As the first line holds the field names I want to skip that. There is no problem achieving this in code, but could I do this purely via sqlite3 commands? You can also use the enhanced shell in APSW (Python wrapper). You don't need to use or write any Python and can just execute it for the import. Of particular use is that it has a .autoimport command which automatically sets up the correct column names. It also deduces the type in each column so for example phone numbers won't be mangled to integers, and dates will be fixed automatically determining if they are US or correct format. It also automatically works out separators (eg csv, tabs, pipes). http://apidoc.apsw.googlecode.com/hg/shell.html sqlite .help autoimport .autoimport FILENAME ?TABLE? Imports filename creating a table and automatically working out separators and data types (alternative to .import command) The import command requires that you precisely pre-setup the table and schema, and set the data separators (eg commas or tabs). In many cases this information can be automatically deduced from the file contents which is what this command does. There must be at least two columns and two rows. If the table is not specified then the basename of the file will be used. Additionally the type of the contents of each column is also deduced - - for example if it is a number or date. Empty values are turned into nulls. Dates are normalized into -MM-DD format and DateTime are normalized into ISO8601 format to allow easy sorting and searching. 4 digit years must be used to detect dates. US (swapped day and month) versus rest of the world is also detected providing there is at least one value that resolves the ambiguity. Care is taken to ensure that columns looking like numbers are only treated as numbers if they do not have unnecessary leading zeroes or plus signs. This is to avoid treating phone numbers and similar number like strings as integers. This command can take quite some time on large files as they are effectively imported twice. The first time is to determine the format and the types for each column while the second pass actually imports the data. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.12 (GNU/Linux) iEYEARECAAYFAlGpIoEACgkQmOOfHg372QRSqwCg3WTRwifRKC+tK55BmTbomVyo PHsAnR8v79nKkpVZ7WYWydeTYxsHbZoE =c95H -END PGP SIGNATURE- ___ 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] Import skip first line
Importing a. csv file via the. import command of sqlite3.exe. As the first line holds the field names I want to skip that. There is no problem achieving this in code, but could I do this purely via sqlite3 commands? RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Import skip first line
That modified version sounds useful. Could you mail it? RBS On May 31, 2013 6:14 PM, Larry Brasfield larry_brasfi...@iinet.com wrote: Bart Smissaert wrote: Importing a. csv file via the. import command of sqlite3.exe. As the first line holds the field names I want to skip that. There is no problem achieving this in code, but could I do this purely via sqlite3 commands? You could do it easily with a temporary table having an and additional autoincrement column, but the verbage to then select that table into the one where you want no header line would be code, IMO. I get this functionality with a modified version of the SQLite shell. When headers are on, it also expects to see them upon .import and does the right thing, which I take to be interpreting them as column names. -- Larry Brasfield __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://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] sequential row numbers from query
Hi Hitesh, Here all the VB6 code to do with this. Ignore all the Debug stuff and also all the RaiseEvent lines. Note that this uses the free VB SQLite wrapper from Olaf Schmidt and if you don't use that then that is very much recommended. Let me know if you want that and I will explain. Also note that my code does something slightly different then what you want to do, but still, it might be useful. Bart Public Function SetSequentialGroups(strDB As String, _ strTable As String, _ strGroupField As String, _ strIDField As String, _ strCompareField1 As String, _ Optional strCompareField2 As String, _ Optional strCompareField3 As String, _ Optional lFirstGroupNumber As Long, _ Optional bLog As Boolean, _ Optional bDebug As Boolean) As Long Dim i As Long Dim c As Long Dim cRs As cRecordset Dim cCmd As cCommand Dim lGroupIdx As Long Dim bDoGroupSwitch As Boolean Dim lCompareFields As Long Dim V1 Dim V2 Dim V3 'compare-values as variant Dim lFieldCount As Long Dim lCompareFieldNumber1 As Long 'all these 4 0-based for convenience Dim lCompareFieldNumber2 As Long Dim lCompareFieldNumber3 As Long Dim lIDFieldNumber As Long 10 On Error GoTo ERROROUT 20 SetSQLiteConn strDB, , , False 30 If SQLiteTableExists(strTable, strDB, False, True) = False Then 40SetSequentialGroups = -1 50Exit Function 60 End If 70 If FieldNumberInTable(strDB, strTable, strGroupField, , False) 1 Then 80SetSequentialGroups = -1 90Exit Function 100 End If 110 If FieldNumberInTable(strDB, strTable, strIDField, , False) 1 Then 120 SetSequentialGroups = -1 130 Exit Function 140 End If 150 If FieldNumberInTable(strDB, strTable, strCompareField1, , False) 1 Then 160 SetSequentialGroups = -1 170 Exit Function 180 End If 190 lGroupIdx = lFirstGroupNumber 'initilize the first lGroupIdx 200 Set cRs = Cnn.OpenRecordset(SELECT * FROM strTable _ ORDER BY strIDField ASC) 210 lFieldCount = cRs.Fields.Count 220 If Len(strCompareField2) = 0 Then 230 lCompareFields = 1 240 Else 250 If Len(strCompareField3) 0 Then 260 lCompareFields = 3 270 Else 280 lCompareFields = 2 290 End If 300 End If 'IndexInFieldList is zero based '-- 310 lIDFieldNumber = cRs.Fields(strIDField).IndexInFieldList 320 lCompareFieldNumber1 = cRs.Fields(strCompareField1).IndexInFieldList 330 If lCompareFields 1 Then 340 lCompareFieldNumber2 = cRs.Fields(strCompareField2).IndexInFieldList 350 End If 360 If lCompareFields 2 Then 370 lCompareFieldNumber3 = cRs.Fields(strCompareField3).IndexInFieldList 380 End If 390 If bDebug Then 400 MsgBoxDLL lFieldCount vbTab lFieldCount vbCrLf _ lIDFieldNumber vbTab lIDFieldNumber vbCrLf _ lCompareFieldNumber1 vbTab lCompareFieldNumber1 vbCrLf _ lCompareFieldNumber2 vbTab lCompareFieldNumber2 vbCrLf _ lCompareFieldNumber3 vbTab lCompareFieldNumber3 vbCrLf _ lCompareFields vbTab lCompareFields, _ Parameters of SetSequentialGroups, _ lFormColour:=lColourForm, bLineUpTabs:=True 410 End If 420 Set cCmd = Cnn.CreateCommand(UPDATE strTable _ SET strGroupField = ? WHERE _ strIDField = ?) 430 If bLog Then 440 ShowStatement Procedure SetSequentialGroups, , , 2, True, , strDB 450 End If 460 BeginTransaction strDB, False 470 Select Case lCompareFields Case 1 'now we work with valuematrix for more speed 480 V1 = cRs.ValueMatrix(0, lCompareFieldNumber1) 490 For i = 0 To cRs.RecordCount - 1 'we split up the comparisons, for a little bit more speed (VB has no early exit in combined If-conditions) 500 If cRs.ValueMatrix(i, lCompareFieldNumber1) V1 Then 510 bDoGroupSwitch = True 520 Else 530 bDoGroupSwitch = False 540 End If 550 If bDoGroupSwitch Then 'set the next set of compare-values 560 V1 = cRs.ValueMatrix(i, lCompareFieldNumber1) 570 lGroupIdx = lGroupIdx + 1 580 End If 590 cCmd.SetInt32 1, lGroupIdx 600 cCmd.SetInt32 2, cRs.ValueMatrix(i, lIDFieldNumber) 'the
Re: [sqlite] sequential row numbers from query
Have a look at this thread in the archive: find sequential groups It can be done with SQL, but it is slow and it can be done enormously faster in code. I did this in VB6 and let me know if you are interested and I mail you the code off-list. RBS On Sat, Apr 27, 2013 at 8:10 AM, hiteshambaliya hitesh.ambal...@gmail.comwrote: You are absolutely right but, I am using VB 6.0 and i have global general function which fill the data in grid so there is if serial number column in query is easy way so.. Any way to do in query??? -- View this message in context: http://sqlite.1065341.n5.nabble.com/sequential-row-numbers-from-query-tp47370p68506.html Sent from the SQLite mailing list archive at Nabble.com. ___ 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] sequential row numbers from query
Hi Hitesh, Attached all the VB6 code to do with this. Ignore all the Debug stuff and also all the RaiseEvent lines. Note that this uses the free VB SQLite wrapper from Olaf Schmidt and if you don't use that then that is very much recommended. Let me know if you want that and I will explain. Also note that my code does something slightly different then what you want to do, but still, it might be useful. Bart On Sat, Apr 27, 2013 at 2:12 PM, hiteshambaliya hitesh.ambal...@gmail.comwrote: Ya I am interested to know more. My mail ID hitesh.ambal...@gmail.com Thank you so much -- View this message in context: http://sqlite.1065341.n5.nabble.com/sequential-row-numbers-from-query-tp47370p68515.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users Public Function SetSequentialGroups(strDB As String, _ strTable As String, _ strGroupField As String, _ strIDField As String, _ strCompareField1 As String, _ Optional strCompareField2 As String, _ Optional strCompareField3 As String, _ Optional lFirstGroupNumber As Long, _ Optional bLog As Boolean, _ Optional bDebug As Boolean) As Long Dim i As Long Dim c As Long Dim cRs As cRecordset Dim cCmd As cCommand Dim lGroupIdx As Long Dim bDoGroupSwitch As Boolean Dim lCompareFields As Long Dim V1 Dim V2 Dim V3 'compare-values as variant Dim lFieldCount As Long Dim lCompareFieldNumber1 As Long 'all these 4 0-based for convenience Dim lCompareFieldNumber2 As Long Dim lCompareFieldNumber3 As Long Dim lIDFieldNumber As Long 10 On Error GoTo ERROROUT 20 SetSQLiteConn strDB, , , False 30 If SQLiteTableExists(strTable, strDB, False, True) = False Then 40SetSequentialGroups = -1 50Exit Function 60 End If 70 If FieldNumberInTable(strDB, strTable, strGroupField, , False) 1 Then 80SetSequentialGroups = -1 90Exit Function 100 End If 110 If FieldNumberInTable(strDB, strTable, strIDField, , False) 1 Then 120 SetSequentialGroups = -1 130 Exit Function 140 End If 150 If FieldNumberInTable(strDB, strTable, strCompareField1, , False) 1 Then 160 SetSequentialGroups = -1 170 Exit Function 180 End If 190 lGroupIdx = lFirstGroupNumber 'initilize the first lGroupIdx 200 Set cRs = Cnn.OpenRecordset(SELECT * FROM strTable _ ORDER BY strIDField ASC) 210 lFieldCount = cRs.Fields.Count 220 If Len(strCompareField2) = 0 Then 230 lCompareFields = 1 240 Else 250 If Len(strCompareField3) 0 Then 260 lCompareFields = 3 270 Else 280 lCompareFields = 2 290 End If 300 End If 'IndexInFieldList is zero based '-- 310 lIDFieldNumber = cRs.Fields(strIDField).IndexInFieldList 320 lCompareFieldNumber1 = cRs.Fields(strCompareField1).IndexInFieldList 330 If lCompareFields 1 Then 340 lCompareFieldNumber2 = cRs.Fields(strCompareField2).IndexInFieldList 350 End If 360 If lCompareFields 2 Then 370 lCompareFieldNumber3 = cRs.Fields(strCompareField3).IndexInFieldList 380 End If 390 If bDebug Then 400 MsgBoxDLL lFieldCount vbTab lFieldCount vbCrLf _ lIDFieldNumber vbTab lIDFieldNumber vbCrLf _ lCompareFieldNumber1 vbTab lCompareFieldNumber1 vbCrLf _ lCompareFieldNumber2 vbTab lCompareFieldNumber2 vbCrLf _ lCompareFieldNumber3 vbTab lCompareFieldNumber3 vbCrLf _ lCompareFields vbTab lCompareFields, _ Parameters of SetSequentialGroups, _ lFormColour:=lColourForm, bLineUpTabs:=True 410 End If 420 Set cCmd = Cnn.CreateCommand(UPDATE strTable _ SET strGroupField = ? WHERE _ strIDField = ?) 430 If bLog Then 440 ShowStatement Procedure SetSequentialGroups, , , 2, True, , strDB 450 End If 460 BeginTransaction strDB, False 470 Select Case lCompareFields Case 1 'now we work with valuematrix for more speed 480 V1 = cRs.ValueMatrix(0, lCompareFieldNumber1) 490 For i = 0 To cRs.RecordCount - 1 'we split up the comparisons, for a little bit more speed (VB has no early
Re: [sqlite] What is wrong with this update SQL
Did a bit of further testing and when the age field is an integer field and the band field is another field with text datatype then the method with a lookup table is slightly faster. Either way there isn't much in it. RBS On 11/22/12, Simon Slavin slav...@bigfraud.org wrote: On 22 Nov 2012, at 5:53pm, Bart Smissaert bart.smissa...@gmail.com wrote: Had a look at this, but it looks the method with select case etc. is faster, maybe some 20%. This is even without setting up the lookup table. Fair enough. Also I am not what the benefit is of the order by in your sql. Doing this: update xxx set band = (select band from convert_age ca where xxx.band = ca.age) seems slightly faster and looks simpler. I didn't know that your ca.age value was always at the bottom of a range, but if it is, then you're right and this will be faster. 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] What is wrong with this update SQL
Have a table with one field holding numbers from 0 to about 110. The field has no data type, so it is not integer or text. Now I run the following SQL: UPDATE table1 SET age_band = (case when age_band = 10 then ' 0 - 10' when age_band BETWEEN 11 AND 20 then ' 11 - 20' when age_band BETWEEN 21 AND 30 then ' 21 - 30' when age_band BETWEEN 31 AND 40 then ' 31 - 40' when age_band BETWEEN 41 AND 50 then ' 41 - 50' when age_band BETWEEN 51 AND 60 then ' 51 - 60' when age_band BETWEEN 61 AND 70 then ' 61 - 70' when age_band BETWEEN 71 AND 80 then ' 71 - 80' when age_band BETWEEN 81 AND 90 then ' 81 - 90' when age_band BETWEEN 91 AND 100 then ' 91 - 100' when age_band 100 then ' over 100' else age_band end) Now there are 2 things wrong with the result. Firstly there are no ' 91 - 100' results and there should be as there are numbers in this range. Secondly, the number 100 remains as it is, so it shows as 100 in the output. I thought that as the field has no date affinity, it should be handled as integer numbers and still should hold the text conversions fine. Thanks for any advice as to what is going on here. RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is wrong with this update SQL
Ignore this e-mail, it was a simple mistake from my side and nil to do with SQLite. Apologies for this. RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is wrong with this update SQL
The simple explanation was that I had make the field text instead of no data affinity. RBS On 11/22/12, Bart Smissaert bart.smissa...@gmail.com wrote: Ignore this e-mail, it was a simple mistake from my side and nil to do with SQLite. Apologies for this. RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] What is wrong with this update SQL
far more quickly using SQL and a second table Not sure this is so in my particular case as the age to ageband conversion is variable, so the lookup table will need to be created and populated every time. I suppose common ones such as the 0 to 10, 11 to 20 etc. could be kept for future use, but that would make it all lot more complex coding-wise. Will have a look though at the speed of creating a temp table in memory and converting that way, but my guess is that it is slower. RBS On Thu, Nov 22, 2012 at 12:58 PM, Simon Slavin slav...@bigfraud.org wrote: On 22 Nov 2012, at 10:47am, Bart Smissaert bart.smissa...@gmail.com wrote: Ignore this e-mail, it was a simple mistake from my side and nil to do with SQLite. It takes a big man to admit a mistake. Thanks for saving us the time of figuring out your problem. By the way ... your original question talks about a problem which can be solved far more quickly using SQL and a second table. Put a table into your database which does the conversion for you: lowerLimit descriptionText 00 - 10 11 11 - 20 ... 91 91 - 100 101 over 100 And create an index on lowerLimit. Then look up the description you want using SELECT descriptionText FROM ageDescriptions WHERE lowerLimit = [myvar] ORDER BY lowerLimit DESC LIMIT 1 You will always get get one row which has the description you want. You can do the lookup either before you do your INSERT (to put the description into the database) or the modern way would be to do the lookup only when you actually need to know the age band, probably just before you show the data on the display. 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] What is wrong with this update SQL
Had a look at this, but it looks the method with select case etc. is faster, maybe some 20%. This is even without setting up the lookup table. Also I am not what the benefit is of the order by in your sql. Doing this: update xxx set band = (select band from convert_age ca where xxx.band = ca.age) seems slightly faster and looks simpler. RBS On 11/22/12, Simon Slavin slav...@bigfraud.org wrote: On 22 Nov 2012, at 10:47am, Bart Smissaert bart.smissa...@gmail.com wrote: Ignore this e-mail, it was a simple mistake from my side and nil to do with SQLite. It takes a big man to admit a mistake. Thanks for saving us the time of figuring out your problem. By the way ... your original question talks about a problem which can be solved far more quickly using SQL and a second table. Put a table into your database which does the conversion for you: lowerLimitdescriptionText 0 0 - 10 1111 - 20 ... 9191 - 100 101 over 100 And create an index on lowerLimit. Then look up the description you want using SELECT descriptionText FROM ageDescriptions WHERE lowerLimit = [myvar] ORDER BY lowerLimit DESC LIMIT 1 You will always get get one row which has the description you want. You can do the lookup either before you do your INSERT (to put the description into the database) or the modern way would be to do the lookup only when you actually need to know the age band, probably just before you show the data on the display. 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] find sequential groups
Thanks. Have a feeling I made this same mistake before and posted to this forum as well ... RBS On Wed, Oct 17, 2012 at 4:13 AM, Igor Tandetnik itandet...@mvps.org wrote: Bart Smissaert bart.smissa...@gmail.com wrote: To do with the same, what is wrong with this update SQL? update final2 set group_count = (select count(*) from final2 group by group_marker) It makes group_count always 1 The subquery does not depend on the values in the row being updated. It always produces the same resultset. Now, the value of the expression of the form (select ...) is the value of the first column of the first row of the resultset. In your case, it just happens to be 1. You are probably looking for something like this: update final2 set group_count = (select count(*) from final2 t2 where t2.group_marker = final2.group_marker); Here, the condition of the subquery mentions a value from the outer table, so it's evaluated anew for every row being updated. See also: http://en.wikipedia.org/wiki/Correlated_subquery -- 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
[sqlite] find sequential groups
Trying to make a query that can mark records, indicating them to belong to a sequential group. Giving the most simple example: IDValue Group_Marker --- 1 D1 2 X 2 3 X 2 4 X 2 5 A 3 6 B 4 Given I have a table with data in the fields ID and Value, but not in Group_Marker, can I make a SQL that will find the values in the field Group_Marker as above and update that field to hold those values. The field Value holds the data indicating a sequential group, so record 2, 3 and 4 are the second group, hence I need the 2 in the field Group_Marker. ID is the field indicating the sequence. This is easy to do in code with a simple loop, but not sure now how to do it in SQL. RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] find sequential groups
Thanks, very nice solution that! Yes, I realise that this is a lot faster in code, but for now that is no option. Actually, it really is slow, made worse by the fact that there is not one grouping field (value in my example), but three. I am running your SQL now, concatenating these 3 fields, but still running and looks will be a long time. Will have to improve it with indexes and maybe avoiding the concatenation. RBS On Tue, Oct 16, 2012 at 10:53 PM, Igor Tandetnik itandet...@mvps.org wrote: On 10/16/2012 4:56 PM, Bart Smissaert wrote: Trying to make a query that can mark records, indicating them to belong to a sequential group. Giving the most simple example: IDValue Group_Marker --- 1 D1 2 X 2 3 X 2 4 X 2 5 A 3 6 B 4 Given I have a table with data in the fields ID and Value, but not in Group_Marker, can I make a SQL that will find the values in the field Group_Marker as above and update that field to hold those values. The field Value holds the data indicating a sequential group, so record 2, 3 and 4 are the second group, hence I need the 2 in the field Group_Marker. ID is the field indicating the sequence. This is easy to do in code with a simple loop, but not sure now how to do it in SQL. Something like this - but note that it's mostly an academic exercise. A simple loop would work orders of magnitude faster than this statement. update MyTable set Group_Marker = ( select count(*) from MyTable t1 where t1.ID = MyTable.ID and t1.Value not in ( select t2.Value from MyTable t2 where t2.ID t1.ID order by t2.ID desc limit 1 ) ); In prose, for each record count the number of records below it (inclusive) that are first-in-group; where first-in-group in turn is defined as a record such that the next record down by ID has a different Value, or there is no smaller ID at all. -- 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] EXT : find sequential groups
Yes, that should be 4 groups, marked with 1, 2, 3 and 4. RBS On Tue, Oct 16, 2012 at 11:15 PM, Igor Tandetnik itandet...@mvps.org wrote: On 10/16/2012 6:08 PM, Black, Michael (IS) wrote: Do this work for you? CREATE TABLE Groups (Value); insert into Groups select distinct(Value) from test; Since the OP mentioned sequential groups, I assumed that a sequence like A,A,B,B,A,A,B,B should count as four groups, not two as your approach would end up with. -- 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] find sequential groups
Thanks, will try that. Yes, the ID field is an integer primary key autoincrement. Still running the old sql with concatenation. Looks I may need to kill that. RBS On Tue, Oct 16, 2012 at 11:38 PM, Igor Tandetnik itandet...@mvps.org wrote: On 10/16/2012 6:29 PM, Bart Smissaert wrote: Actually, it really is slow, made worse by the fact that there is not one grouping field (value in my example), but three. I am running your SQL now, concatenating these 3 fields, but still running and looks will be a long time. Will have to improve it with indexes and maybe avoiding the concatenation. This would avoid concatenation: update MyTable set Group_Marker = ( select count(*) from MyTable t1 where t1.ID = MyTable.ID and not ( select (t2.Value1=t1.Value1 and t2.Value2=t1.Value2 and t2.Value3=t1.Value3) from MyTable t2 where t2.ID t1.ID order by t2.ID desc limit 1 ) ); The only index that would be helful is one on ID, which I suspect you might already have. -- 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] find sequential groups
Thanks, will give that a try as well. RBS On Wed, Oct 17, 2012 at 12:00 AM, Black, Michael (IS) michael.bla...@ngc.com wrote: Ok...how about with triggers then? This will give a unique number to each sequence as you insert them. CREATE TABLE Test(ID,Value,Group_Marker); CREATE TRIGGER insert_trigger1 after insert on Test WHEN new.id=1 BEGIN UPDATE Test set Group_Marker=1; END; CREATE TRIGGER insert_trigger2 after insert on Test WHEN new.id 1 and ((SELECT Group_Marker from Test where id=new.id-1 and Value=new.Value) IS NOT NULL) BEGIN UPDATE Test set Group_Marker=(select Group_Marker from Test where id=new.id-1) where id=new.id; END; CREATE TRIGGER insert_trigger3 after insert on Test WHEN new.id 1 and ((SELECT Group_Marker from Test where id=new.id-1 and Value!=new.Value) IS NOT NULL) BEGIN UPDATE Test set Group_Marker=(select Group_Marker+1 from Test where id=new.id-1) where id=new.id; END; INSERT INTO Test VALUES(1,'D',0); INSERT INTO Test VALUES(2,'X',0); INSERT INTO Test VALUES(3,'X',0); INSERT INTO Test VALUES(4,'X',0); INSERT INTO Test VALUES(5,'A',0); INSERT INTO Test VALUES(6,'B',0); INSERT INTO Test VALUES(7,'X',0); SELECT * FROM Test; You'll see that # 7 gets a new Group_Marker instead of repeating group#2. 1|D|1 2|X|2 3|X|2 4|X|2 5|A|3 6|B|4 7|X|5 Michael D. Black Senior Scientist Advanced Analytics Directorate Advanced GEOINT Solutions Operating Unit Northrop Grumman Information Systems From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on behalf of Bart Smissaert [bart.smissa...@gmail.com] Sent: Tuesday, October 16, 2012 5:45 PM To: General Discussion of SQLite Database Subject: EXT :Re: [sqlite] find sequential groups Thanks, will try that. Yes, the ID field is an integer primary key autoincrement. Still running the old sql with concatenation. Looks I may need to kill that. RBS On Tue, Oct 16, 2012 at 11:38 PM, Igor Tandetnik itandet...@mvps.org wrote: On 10/16/2012 6:29 PM, Bart Smissaert wrote: Actually, it really is slow, made worse by the fact that there is not one grouping field (value in my example), but three. I am running your SQL now, concatenating these 3 fields, but still running and looks will be a long time. Will have to improve it with indexes and maybe avoiding the concatenation. This would avoid concatenation: update MyTable set Group_Marker = ( select count(*) from MyTable t1 where t1.ID = MyTable.ID and not ( select (t2.Value1=t1.Value1 and t2.Value2=t1.Value2 and t2.Value3=t1.Value3) from MyTable t2 where t2.ID t1.ID order by t2.ID desc limit 1 ) ); The only index that would be helful is one on ID, which I suspect you might already have. -- 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 ___ 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] find sequential groups
Without the concatenation it runs fine, enormously faster than with the concatenation. Have checked and the result is fine as well. Thanks again. RBS On Tue, Oct 16, 2012 at 11:38 PM, Igor Tandetnik itandet...@mvps.org wrote: On 10/16/2012 6:29 PM, Bart Smissaert wrote: Actually, it really is slow, made worse by the fact that there is not one grouping field (value in my example), but three. I am running your SQL now, concatenating these 3 fields, but still running and looks will be a long time. Will have to improve it with indexes and maybe avoiding the concatenation. This would avoid concatenation: update MyTable set Group_Marker = ( select count(*) from MyTable t1 where t1.ID = MyTable.ID and not ( select (t2.Value1=t1.Value1 and t2.Value2=t1.Value2 and t2.Value3=t1.Value3) from MyTable t2 where t2.ID t1.ID order by t2.ID desc limit 1 ) ); The only index that would be helful is one on ID, which I suspect you might already have. -- 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] find sequential groups
To do with the same, what is wrong with this update SQL? update final2 set group_count = (select count(*) from final2 group by group_marker) It makes group_count always 1, but should include higher values. The select by itself gives the right result. RBS On Wed, Oct 17, 2012 at 12:31 AM, Bart Smissaert bart.smissa...@gmail.com wrote: Without the concatenation it runs fine, enormously faster than with the concatenation. Have checked and the result is fine as well. Thanks again. RBS On Tue, Oct 16, 2012 at 11:38 PM, Igor Tandetnik itandet...@mvps.org wrote: On 10/16/2012 6:29 PM, Bart Smissaert wrote: Actually, it really is slow, made worse by the fact that there is not one grouping field (value in my example), but three. I am running your SQL now, concatenating these 3 fields, but still running and looks will be a long time. Will have to improve it with indexes and maybe avoiding the concatenation. This would avoid concatenation: update MyTable set Group_Marker = ( select count(*) from MyTable t1 where t1.ID = MyTable.ID and not ( select (t2.Value1=t1.Value1 and t2.Value2=t1.Value2 and t2.Value3=t1.Value3) from MyTable t2 where t2.ID t1.ID order by t2.ID desc limit 1 ) ); The only index that would be helful is one on ID, which I suspect you might already have. -- 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] Subtract times hh:mm:ss
There are no different answers and I think all the information is in the
first post.
RBS
On Oct 7, 2012 1:21 PM, Black, Michael (IS) michael.bla...@ngc.com
wrote:
You haven't provided enough info for anybody to tell what's going on.
What data are you substracting? Can you provide an sql dump of the data
that gets different answers and your code?
You can' even get fractional seconds from those statements as the time
format only supports hr/min/sec
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
on behalf of Bart Smissaert [bart.smissa...@gmail.com]
Sent: Saturday, October 06, 2012 12:38 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Subtract times hh:mm:ss
Times I get (65000 records, subtracting 2 fields defined as text in
the same table)
gives me following times:
method with julianday 0.4 secs
method with unixepoch 0.6 secs
using ctime etc. via VB wrapper 1.2 secs
RBS
On Tue, Oct 2, 2012 at 7:39 PM, Igor Tandetnik itandet...@mvps.org
wrote:
On 10/2/2012 1:00 PM, Bart Smissaert wrote:
Is there a way to subtract times in the text format hh:mm:ss
and return the difference in the same format?
select time(julianday('03:22:11') - julianday('01:22:33') - .5);
select time(strftime('%s', '03:22:11') - strftime('%s', '01:22:33'),
'unixepoch');
Both of these return '01:59:38'.
--
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
___
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] Subtract times hh:mm:ss
The word times in my previous post confused/misled you. These are execution
times, not values produced by SQL.
RBS
On Sunday, October 7, 2012, Black, Michael (IS) wrote:
You expect the readers on this list to go find your old post and then look
at what you're NOT doing now? You asked how to compute time, we showed
you, and now you are apparently doing it incorrectly.
You need to provide enough info in your current post for people to
duplicate your current problem and want to help you.
You showed you are getting 3 different answers...presumably from the same
record...but you don't show us the fields you are computing it from, nor
the code which does it.
Come to think of of itthere was no question in your last post either.
So help us help you.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org javascript:; [
sqlite-users-boun...@sqlite.org javascript:;] on behalf of Bart
Smissaert [bart.smissa...@gmail.com javascript:;]
Sent: Sunday, October 07, 2012 8:18 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Subtract times hh:mm:ss
There are no different answers and I think all the information is in the
first post.
RBS
On Oct 7, 2012 1:21 PM, Black, Michael (IS)
michael.bla...@ngc.comjavascript:;
wrote:
You haven't provided enough info for anybody to tell what's going on.
What data are you substracting? Can you provide an sql dump of the data
that gets different answers and your code?
You can' even get fractional seconds from those statements as the time
format only supports hr/min/sec
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org javascript:; [
sqlite-users-boun...@sqlite.org javascript:;]
on behalf of Bart Smissaert [bart.smissa...@gmail.com javascript:;]
Sent: Saturday, October 06, 2012 12:38 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Subtract times hh:mm:ss
Times I get (65000 records, subtracting 2 fields defined as text in
the same table)
gives me following times:
method with julianday 0.4 secs
method with unixepoch 0.6 secs
using ctime etc. via VB wrapper 1.2 secs
RBS
On Tue, Oct 2, 2012 at 7:39 PM, Igor Tandetnik
itandet...@mvps.orgjavascript:;
wrote:
On 10/2/2012 1:00 PM, Bart Smissaert wrote:
Is there a way to subtract times in the text format hh:mm:ss
and return the difference in the same format?
select time(julianday('03:22:11') - julianday('01:22:33') - .5);
select time(strftime('%s', '03:22:11') - strftime('%s', '01:22:33'),
'unixepoch');
Both of these return '01:59:38'.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
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] Subtract times hh:mm:ss
No trouble, it was somewhat confusing. Yes, all done through the VB wrapper.
RBS
On Oct 7, 2012 7:38 PM, Black, Michael (IS) michael.bla...@ngc.com
wrote:
That makes a LOT more sense...misread times as times :-)
Sorry for any rant on my part.
Are all your times via your VB app?
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
on behalf of Bart Smissaert [bart.smissa...@gmail.com]
Sent: Sunday, October 07, 2012 9:15 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Subtract times hh:mm:ss
The word times in my previous post confused/misled you. These are execution
times, not values produced by SQL.
RBS
On Sunday, October 7, 2012, Black, Michael (IS) wrote:
You expect the readers on this list to go find your old post and then
look
at what you're NOT doing now? You asked how to compute time, we showed
you, and now you are apparently doing it incorrectly.
You need to provide enough info in your current post for people to
duplicate your current problem and want to help you.
You showed you are getting 3 different answers...presumably from the same
record...but you don't show us the fields you are computing it from, nor
the code which does it.
Come to think of of itthere was no question in your last post either.
So help us help you.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org javascript:; [
sqlite-users-boun...@sqlite.org javascript:;] on behalf of Bart
Smissaert [bart.smissa...@gmail.com javascript:;]
Sent: Sunday, October 07, 2012 8:18 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Subtract times hh:mm:ss
There are no different answers and I think all the information is in the
first post.
RBS
On Oct 7, 2012 1:21 PM, Black, Michael (IS) michael.bla...@ngc.com
javascript:;
wrote:
You haven't provided enough info for anybody to tell what's going on.
What data are you substracting? Can you provide an sql dump of the
data
that gets different answers and your code?
You can' even get fractional seconds from those statements as the time
format only supports hr/min/sec
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org javascript:; [
sqlite-users-boun...@sqlite.org javascript:;]
on behalf of Bart Smissaert [bart.smissa...@gmail.com javascript:;]
Sent: Saturday, October 06, 2012 12:38 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Subtract times hh:mm:ss
Times I get (65000 records, subtracting 2 fields defined as text in
the same table)
gives me following times:
method with julianday 0.4 secs
method with unixepoch 0.6 secs
using ctime etc. via VB wrapper 1.2 secs
RBS
On Tue, Oct 2, 2012 at 7:39 PM, Igor Tandetnik itandet...@mvps.org
javascript:;
wrote:
On 10/2/2012 1:00 PM, Bart Smissaert wrote:
Is there a way to subtract times in the text format hh:mm:ss
and return the difference in the same format?
select time(julianday('03:22:11') - julianday('01:22:33') - .5);
select time(strftime('%s', '03:22:11') - strftime('%s', '01:22:33'),
'unixepoch');
Both of these return '01:59:38'.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
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
Re: [sqlite] Subtract times hh:mm:ss
Hi Olaf, Will give that a go. I take it these files are not ready yet to put in a commercial app? RBS On Sun, Oct 7, 2012 at 10:23 PM, Olaf Schmidt s...@online.de wrote: Am 06.10.2012 19:38, schrieb Bart Smissaert: Times I get (65000 records, subtracting 2 fields defined as text in the same table) gives me following times: method with julianday 0.4 secs method with unixepoch 0.6 secs using ctime etc. via VB wrapper 1.2 secs What? A VB-implemented User-Defined-Function slower than a built-in C-function? Now, there's a challenge... ;-) Since I'm in the last stages for a new wrapper- version (RichClient5) - I've tried to speed these two functions up already in the new Binary (FWIW). Wasn't all that difficult, because the former VB.Runtime- function (Format$,... yes, I was lazy), which internally was playing a major role in these conversions, is not the fastest and leaves enough room for improvement. So, after optimization the UDFs CTime/CDbl are now about twice as fast as the time/julianday-functions. If you want to try it out, here's a download of the current snapshot of the new wrapper: www.datenhaus.de/Downloads/vbRC5BaseDlls.zip After registering you should be able to run the following testcode (TestTable contains 86400 increasing seconds). Tried to avoid the selection of too many records in the test-query (only a single one is returned), so that the test can run more or less completely inside SQLites VDBE. The printed results, after scanning over 86400 records are: Count: 1 Timing VB-UDF: 0,049s Count: 1 Timing SQLite: 0,090s --- used table-layout and VB-testcode --- '*Into a Form, then click the Form Option Explicit Private MemDB As New cMemDB, Rs As cRecordset, T! Private Sub Form_Load() With MemDB.NewFieldDefs .Add ID Integer Primary Key .Add T1 Text .Add T2 Text MemDB.CreateTable T End With With MemDB.CreateCommand(Insert Into T Values(@ID,@T1,@T2)) MemDB.BeginTrans Dim i As Long For i = 0 To 86400 - 1 '-one day (increasing seconds in T2) .SetNull !ID .SetText !T1, 00:00:00 .SetText !T2, Format$(i / 86400, hh:mm:ss) .Execute Next i MemDB.CommitTrans End With End Sub Private Sub Form_Click() Const Where1 = CTime(CDbl(T2) - CDbl(T1)) = '23:59:59' Const Where2 = time(julianday(T2) - julianday(T1) - .5) = '23:59:59' T = Timer Set Rs = MemDB.GetTable(T, Where1) T = Timer - T Print Count:; Rs.RecordCount, Timing VB-UDF: ; Format(T, 0.000s) T = Timer Set Rs = MemDB.GetTable(T, Where2) T = Timer - T Print Count:; Rs.RecordCount, Timing SQLite: ; Format(T, 0.000s) Print End Sub Olaf ___ 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] Subtract times hh:mm:ss
Have tested and indeed a lot faster now. Again the same test, so 65000 records subtracting the same 2 fields: method with julianday 0.4 secs method with unixepoch 0.6 secs using ctime etc. via VB wrapper 0.27 secs RBS On Sun, Oct 7, 2012 at 10:23 PM, Olaf Schmidt s...@online.de wrote: Am 06.10.2012 19:38, schrieb Bart Smissaert: Times I get (65000 records, subtracting 2 fields defined as text in the same table) gives me following times: method with julianday 0.4 secs method with unixepoch 0.6 secs using ctime etc. via VB wrapper 1.2 secs What? A VB-implemented User-Defined-Function slower than a built-in C-function? Now, there's a challenge... ;-) Since I'm in the last stages for a new wrapper- version (RichClient5) - I've tried to speed these two functions up already in the new Binary (FWIW). Wasn't all that difficult, because the former VB.Runtime- function (Format$,... yes, I was lazy), which internally was playing a major role in these conversions, is not the fastest and leaves enough room for improvement. So, after optimization the UDFs CTime/CDbl are now about twice as fast as the time/julianday-functions. If you want to try it out, here's a download of the current snapshot of the new wrapper: www.datenhaus.de/Downloads/vbRC5BaseDlls.zip After registering you should be able to run the following testcode (TestTable contains 86400 increasing seconds). Tried to avoid the selection of too many records in the test-query (only a single one is returned), so that the test can run more or less completely inside SQLites VDBE. The printed results, after scanning over 86400 records are: Count: 1 Timing VB-UDF: 0,049s Count: 1 Timing SQLite: 0,090s --- used table-layout and VB-testcode --- '*Into a Form, then click the Form Option Explicit Private MemDB As New cMemDB, Rs As cRecordset, T! Private Sub Form_Load() With MemDB.NewFieldDefs .Add ID Integer Primary Key .Add T1 Text .Add T2 Text MemDB.CreateTable T End With With MemDB.CreateCommand(Insert Into T Values(@ID,@T1,@T2)) MemDB.BeginTrans Dim i As Long For i = 0 To 86400 - 1 '-one day (increasing seconds in T2) .SetNull !ID .SetText !T1, 00:00:00 .SetText !T2, Format$(i / 86400, hh:mm:ss) .Execute Next i MemDB.CommitTrans End With End Sub Private Sub Form_Click() Const Where1 = CTime(CDbl(T2) - CDbl(T1)) = '23:59:59' Const Where2 = time(julianday(T2) - julianday(T1) - .5) = '23:59:59' T = Timer Set Rs = MemDB.GetTable(T, Where1) T = Timer - T Print Count:; Rs.RecordCount, Timing VB-UDF: ; Format(T, 0.000s) T = Timer Set Rs = MemDB.GetTable(T, Where2) T = Timer - T Print Count:; Rs.RecordCount, Timing SQLite: ; Format(T, 0.000s) Print End Sub Olaf ___ 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] Subtract times hh:mm:ss
Times I get (65000 records, subtracting 2 fields defined as text in
the same table)
gives me following times:
method with julianday 0.4 secs
method with unixepoch 0.6 secs
using ctime etc. via VB wrapper 1.2 secs
RBS
On Tue, Oct 2, 2012 at 7:39 PM, Igor Tandetnik itandet...@mvps.org wrote:
On 10/2/2012 1:00 PM, Bart Smissaert wrote:
Is there a way to subtract times in the text format hh:mm:ss
and return the difference in the same format?
select time(julianday('03:22:11') - julianday('01:22:33') - .5);
select time(strftime('%s', '03:22:11') - strftime('%s', '01:22:33'),
'unixepoch');
Both of these return '01:59:38'.
--
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] Sqlite and SQL Server 2005
To connect to SQLite I recommend the VB wrapper written by Olaf Schmidt: http://www.thecommon.net/ There are plenty of examples to connect to SQL Server with VB, ADO and ODBC. A simple Google search will find them. RBS On 10/5/12, Jorge cotrinago...@yahoo.com wrote: Thanks I forgot to include coding language (vb) Do you know where can i find a sample code. Thanks Again Sent from my iPhone On Oct 4, 2012, at 4:47 PM, Bart Smissaert bart.smissa...@gmail.com wrote: What is your coding language? If VB then maybe via ODBC and ADO. RBS On Thu, Oct 4, 2012 at 9:11 PM, Jorge cotrinago...@yahoo.com wrote: Hello, I am new in Sqlite. I would like to know what is the best option to update data in two directions from Sqlite to SQL Server 2005 Thx ___ 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Bug report: null pointer dereference in SQLite 3.7.14 (SEGFAULT)
Now that is a proper bug! Makes Excel crash OK here. RBS On 10/3/12, Klaus Keppler k...@keppler-it.de wrote: Hi, after upgrading from SQLite 3.7.13 to 3.7.14 our application crashed with a SEGFAULT located within SQLite. I boiled down the SQL statement and the tables used, and was able to reproduce this also with the SQLite standalone binary available at http://www.sqlite.org/sqlite-shell-linux-x86-3071400.zip (Debian 6, AMD64). I assume that the new optimizations on the query planner are causing this problem; with 3.7.13 everything works fine. HOW TO REPRODUCE: -- create these tables and fill with data: CREATE TABLE GROUPCUSTOMERS ( GC_GROUPID INTEGER NOT NULL, GC_CUSTOMERID INTEGER NOT NULL, GC_OBJECTTYPE INTEGER ); INSERT INTO GROUPCUSTOMERS VALUES (1, 1, NULL); CREATE TABLE GROUPPERMISSIONS ( GP_GROUPID INTEGER NOT NULL, GP_MODULEID INTEGER NOT NULL, GP_PERMISSIONID INTEGER NOT NULL ); INSERT INTO GROUPPERMISSIONS VALUES (1, 1, 1); CREATE TABLE GROUPUSERS ( GU_GROUPID INTEGER NOT NULL, GU_USERID INTEGER NOT NULL, GU_OBJECTTYPE INTEGER ); -- then run this query: SELECT * FROM ( SELECT GP_MODULEID AS CP_MODULEID, GP_PERMISSIONID AS CP_PERMISSIONID FROM GROUPCUSTOMERS, GROUPPERMISSIONS WHERE (GC_GROUPID = GP_GROUPID) ) AS A1 LEFT JOIN ( SELECT GP_MODULEID AS UP_MODULEID, GP_PERMISSIONID AS UP_PERMISSIONID FROM GROUPUSERS, GROUPPERMISSIONS WHERE (GU_GROUPID = GP_GROUPID) ) AS A2 ON (CP_MODULEID = UP_MODULEID) OR (UP_PERMISSIONID = 0) ; = SEGFAULT -- note that it is important to have these two records inserted, -- without them the SQL will work. -- AND: when running ANALYZE before running the SQL statement, the -- query also succeeds! In our application, the error occured at the sqlite3VdbeExec function at case OP_NullRow: on the line u.bn.pC-nullRow = 1; u.pn.pC is NULL at this point. (tested with GDB and the original (bloated) SQL statement from within our application) We did NOT use the flag SQLITE_ENABLE_STAT3. For any questions don't hezitate to contact me. Best regards -Klaus Keppler -- __ Keppler IT GmbH - Die Hostingexperten. Dipl.-Inf. Klaus KepplerTel. (09131) 691-480 Geschäftsführer Fax: (09131) 691-489 Am Weichselgarten 7 UStID.-Nr. DE259788698 91058 Erlangen Amtsgericht Fürth, HRB 11477 www.keppler-it.de Sitz d. Gesellschaft: Erlangen __ ___ 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 and SQL Server 2005
What is your coding language? If VB then maybe via ODBC and ADO. RBS On Thu, Oct 4, 2012 at 9:11 PM, Jorge cotrinago...@yahoo.com wrote: Hello, I am new in Sqlite. I would like to know what is the best option to update data in two directions from Sqlite to SQL Server 2005 Thx ___ 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] Subtract times hh:mm:ss
Hi Olaf, Thanks, will give that a try today and tell you if it is faster. RBS On Wed, Oct 3, 2012 at 3:55 AM, Olaf Schmidt s...@online.de wrote: Am 02.10.2012 21:23, schrieb Bart Smissaert: Nice one, thanks for that. Just in case you use the COM-Wrapper - and this operation takes place in a plain Recordset-Select (and isn't stored somewhere in the DB itself, e.g. in a Trigger), then you can reduce the amount of function-calls a bit, when you use something like that: Define the Table-Field with the wrapper-supported Time-FieldType, which ends up as 'hh:mm:ss' Text in the SQLite-DB-Field - but is correctly translated back into a Date-Type in the receiving cRecordset. To safe a few CPU-Cycles in the Query, you can directly place the Double-representation of a VB(A)-Date in the Query-String (done in the Example per ? Placeholder in a cSelectCommand). The Diff-expression in the Select then looks this way: CTime(? - CDbl(HMS)) CDbl, to convert the TextContent of the HMS-Field into the Double-representation of a VB-Date - and CTime to convert the difference back into a 'hh:mm:ss' String. Not sure, if that is faster than SQLites built-in Date/Time-Functions, but worth a try... ' Into a Form (clicking the Form gives the Delta to its LoadTime) ' Output then i.e.: HMS 04:21:27 True DTS 00:00:01 True Option Explicit Private Cnn As New cConnection, GetDeltaCmd As cSelectCommand Private Sub Form_Load() Cnn.CreateNewDB '- InMemory 'the wrappers Time-FieldType ensures 'hh:mm:ss' TextFormat in the DB Cnn.Execute Create Table T(HMS Time) With Cnn.CreateCommand(Insert Into T Values(?)) .SetTime 1, Now() .Execute End With Const GetDeltaSQL = Select HMS, CTime(? - CDbl(HMS)) As DTS From T Set GetDeltaCmd = Cnn.CreateSelectCommand(GetDeltaSQL) End Sub Private Sub Form_Click() GetDeltaCmd.SetDouble 1, Now() 'we place the Param directly as Double With GetDeltaCmd.Execute 'returns a cRecordset Debug.Print !HMS.Name, !HMS.Value, VarType(!HMS.Value) = vbDate, Debug.Print !DTS.Name, !DTS.Value, VarType(!DTS.Value) = vbString End With End Sub Olaf ___ 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] Subtract times hh:mm:ss
Is there a way to subtract times in the text format hh:mm:ss and return the difference in the same format? I am sure it could be done with various calculations and casts, but maybe there is a simple, ready-made way to do this. RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subtract times hh:mm:ss
Nice one, thanks for that.
RBS
On Tue, Oct 2, 2012 at 7:39 PM, Igor Tandetnik itandet...@mvps.org wrote:
On 10/2/2012 1:00 PM, Bart Smissaert wrote:
Is there a way to subtract times in the text format hh:mm:ss
and return the difference in the same format?
select time(julianday('03:22:11') - julianday('01:22:33') - .5);
select time(strftime('%s', '03:22:11') - strftime('%s', '01:22:33'),
'unixepoch');
Both of these return '01:59:38'.
--
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] DELETE Query Assistance Please
Why you need to convert?
What about the simple SQL I suggested?
RBS
On 9/24/12, Don Goyette d...@donandcarla.com wrote:
Thank you for your reply and suggestions, Clemens.
With 60*60*24 seconds per day, the number of days since the Unix epoch
is:
sqlite select strftime('%s', '2012-05-22') / (60*60*24);
15482
The timestamp in the tables I'm reading is not in the format of
'2012-05-22'. It's in the Excel format (ie. 41051.395834), which is
why
I need to convert it for use with SQLite. When I read a row of data, I do
not get 2012-05-22, I get a value such as 41051.395834. So, I am
not able to perform the strftime() function you suggest, until I convert
the
Excel timestamp to a Unix epoch timestamp. For now, I'm still hard-coding
the value I need.
... redirect the output to a file, and then execute that file.
Great idea! I had not thought of copying the output from the Tnames table
to a text file and simply adding the DELETE query text around the table
names. Then copying it back into the SQL GUI that I use and execute 10 or
so DELETE queries at a time.
So, I still need to know how to convert the Excel format timestamp (Days
since 1900-01-01) into a Unix Epoch format timestamp (Seconds since
1970-01-01).
Thank you all,
-Don
-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Clemens Ladisch
Sent: Sunday, September 23, 2012 6:09 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] DELETE Query Assistance Please
Don Goyette wrote:
The first problem I'm running into is that the timestamp in these tables
is
NOT a standard Unix timestamp. Rather, it's an Excel timestamp, which is
the number of Days since Jan 1, 1900. An example is '41051.395834'
(May
22, 2012), but the DELETE query will only use the integer portion.
QUESTION #1: How do I convert this Excel timestamp value into a Unix
timestamp value that SQLite understands and can work with?
With 60*60*24 seconds per day, the number of days since the Unix epoch is:
sqlite select strftime('%s', '2012-05-22') / (60*60*24);
15482
So with 41051 - 15482 = 25569, the conversion would be:
(excel_timestamp - 25569) * (60*60*24)
And indeed:
select datetime((41051.395834 - 25569) * (60*60*24), 'unixepoch');
2012-05-22 09:30:00
Next, I've managed to create a table of table names to be worked on
(Tnames)
and have gotten a DELETE query running for a single table, with the table
name hard-coded into the query (ie. 'EMC_intraday'). But I've not been
able
to figure out how to accomplish the DELETE query for ALL of the history
table names in Tnames.
You cannot modify the table name of a DELETE statment from inside SQLite
itself.
What you can do is to generate all the DELETE statements from a query:
select 'DELETE FROM ' || Tname || ' WHERE ...' from Tnames;
... redirect the output to a file, and then execute that file.
Regards,
Clemens
___
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] DELETE Query Assistance Please
Problem 1 seems simple: delete from TableX where timestamp 41115 I think the table name may need to be produced by code. RBS On Sun, Sep 23, 2012 at 12:06 PM, Don Goyette d...@donandcarla.com wrote: Hello Everyone, I'm using what has become a huge (3.5 GB) SQLite3 database that was created by an investment (stocks) tracking program I use. The program does not have a database purge function to delete old data, and it's no longer supported. So I'm trying to do this manually with SQL, via a Windows program called RazorSQL. The database contains 1,034 tables, one for each stock ticker being tracked. Each table contains up to 60,000 rows. These tables store historical intraday stock prices at five minute intervals, so there are about 150 rows for each day that is stored. Some of the tables contain up to 388 days of data, but I only need 60 days. Thus, a LOT of data needs to be deleted from these tables and then the database needs to be compacted. The database file name is 'Don.db' The history tables are named 'ticker_intraday' where ticker is a stock ticker, such as 'EMC' or 'ORCL' The history table columns are... timestamp REAL NOT NULL (Primary Key) open REAL high REAL low REAL close REAL volumeREAL The table of table names is 'Tnames' (created during run) The only column in Tnames is 'Tname' (names of all the intraday tables) The first problem I'm running into is that the timestamp in these tables is NOT a standard Unix timestamp. Rather, it's an Excel timestamp, which is the number of Days since Jan 1, 1900. An example is '41051.395834' (May 22, 2012), but the DELETE query will only use the integer portion. QUESTION #1: How do I convert this Excel timestamp value into a Unix timestamp value that SQLite understands and can work with? Next, I've managed to create a table of table names to be worked on (Tnames) and have gotten a DELETE query running for a single table, with the table name hard-coded into the query (ie. 'EMC_intraday'). But I've not been able to figure out how to accomplish the DELETE query for ALL of the history table names in Tnames. And I've Googled until my hands and eyes are just plain sore. Here's what I'm trying to do... -- Create a table of table names to work on (this works)... CREATE TABLE Tnames ( Tname nvarchar(50) ); INSERT INTO Tnames --(this works) SELECT name FROM sqlite_master WHERE type='table' and name LIKE '%intraday'; -- BEGIN processing all results -- Delete rows 60 days old (this does not work)... -- Convert the Excel timestamp to a Unix timestamp... -- Not sure how to do this -- DELETE FROM 'Don.db'||Tnames does not work -- DELETE FROM Don.db.Tnames.Tname does not work -- DELETE FROM Tnames.Tname does not work -- What I want to do is... DELETE FROM tablename entry in Tnames table WHERE timestamp 41109; --(need to convert this) -- END processing all results -- Compact the database... VACUUM QUESTION #2: How do I code the DELETE query so it will use each of the Tname values in the Tnames table? Thank you all, in advance, for your assistance. I appreciate your help very much. -Don ___ 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] Count(*) help
Hi John, If you zip the file (it sounds it is only small) and mail it to me or to this list and tell what answer you want I (or somebody else) will post the SQL. RBS On Mon, Sep 17, 2012 at 7:59 AM, John Clegg john.cl...@nailsea.net wrote: OK thanks folks. Here is the full query (which is why I can't use WHERE clauses! It has always worked before.. I have removed the double-quoted but it makes no difference. SELECT COUNT( Year2007 ), COUNT( Year2008 ), COUNT( Year2009 ), COUNT( Year2010 ), COUNT( Year2011 ), COUNT( Year2012 ), COUNT( Year2013 ) FROM Members On 16 September 2012 17:48, Bart Smissaert bart.smissa...@gmail.com wrote: Hi John, Funny seeing you here on the SQLite forum. Are these by any chance the ISUG members? Doing a count without a WHERE clause is always likely to give different results with the various SQL implications as far as I know. Why not add a WHERE? RBS On Sun, Sep 16, 2012 at 5:17 PM, John Clegg john.cl...@nailsea.net wrote: I have a table Members with 896 rows and a text field Year2012. It contains Paid 156 times, Comp 13 times and the rest are null (confirmed in sqlitebrowser as empty) Back in the olden days when this table was in Access, select count(Year2013) from Members used to return 169. In LibreOfiice with the data stored in embedded HSQL it returns 169. In LibreOffice connecting to sqlite3 it returns 896. Any ideas please? ___ 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Count(*) help
Hi John, Funny seeing you here on the SQLite forum. Are these by any chance the ISUG members? Doing a count without a WHERE clause is always likely to give different results with the various SQL implications as far as I know. Why not add a WHERE? RBS On Sun, Sep 16, 2012 at 5:17 PM, John Clegg john.cl...@nailsea.net wrote: I have a table Members with 896 rows and a text field Year2012. It contains Paid 156 times, Comp 13 times and the rest are null (confirmed in sqlitebrowser as empty) Back in the olden days when this table was in Access, select count(Year2013) from Members used to return 169. In LibreOfiice with the data stored in embedded HSQL it returns 169. In LibreOffice connecting to sqlite3 it returns 896. Any ideas please? ___ 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] select max(field1), field2 from table1
Had a look at the new option as in the SQL above. Noticed it will only return one record, even if there are more records where field1 equals max(field1). I suppose it returns the first record it finds where field1 = max(field1). Is this indeed how it works? RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] select max(field1), field2 from table1
OK, thanks for confirming that. RBS On 9/12/12, Richard Hipp d...@sqlite.org wrote: On Wed, Sep 12, 2012 at 6:15 AM, Bart Smissaert bart.smissa...@gmail.comwrote: Had a look at the new option as in the SQL above. Noticed it will only return one record, even if there are more records where field1 equals max(field1). I suppose it returns the first record it finds where field1 = max(field1). Is this indeed how it works? Yes. RBS ___ 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
Re: [sqlite] instr function or equivalent
Hi Olaf, Yes, I am aware of those functions in your COM wrapper and I am using them. Was just thinking in general terms as not many users on this forum use VB. Great news about the new version and will download and test that today. Thanks for that. Bart On 9/11/12, Olaf Schmidt s...@online.de wrote: Am 10.09.2012 17:17, schrieb Bart Smissaert: Ah, OK. I have a feeling that needs to be done either in your application code or with a user defined SQLite function. Somebody may prove me wrong. Hi Bart, since I know you're using my COM-Wrapper, a larger set of Functions (in VBA-Style, similar to the JET-Engine) are already built-in there (including Instr, Left$, Right$, Mid$, DateDiff, DatePart, ... etc.). For example Sébastiens requirement could be handled this way: Select Mid$(TheField, Instr(TheField,'[') From Tbl Olaf BTW, a new version including newest SQLite 3.7.14 is out now since yesterday: www.datenhaus.de/Downloads/vbRC4BaseDlls.zip ___ 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] instr function or equivalent
Hi Olaf, I think it should be: Select Left$(TheField, Instr(TheField,']')) From Tbl Have tested you new dll's and all working fine as usual. Bart On 9/11/12, Olaf Schmidt s...@online.de wrote: Am 10.09.2012 17:17, schrieb Bart Smissaert: Ah, OK. I have a feeling that needs to be done either in your application code or with a user defined SQLite function. Somebody may prove me wrong. Hi Bart, since I know you're using my COM-Wrapper, a larger set of Functions (in VBA-Style, similar to the JET-Engine) are already built-in there (including Instr, Left$, Right$, Mid$, DateDiff, DatePart, ... etc.). For example Sébastiens requirement could be handled this way: Select Mid$(TheField, Instr(TheField,'[') From Tbl Olaf BTW, a new version including newest SQLite 3.7.14 is out now since yesterday: www.datenhaus.de/Downloads/vbRC4BaseDlls.zip ___ 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] instr function or equivalent
http://www.sqlite.org/lang_corefunc.html Look at substr RBS On 9/10/12, Sébastien Roux roux.sebast...@gmail.com wrote: Hi, I'm looking for the[in]famous sqlite *instr* function which doesn't exist (searched the web so far without success). Also searched for a *position*function, without success too! Any idea or help? I found some threads about custom functions but no tutorial nor deeper explanations! Many thanks. Sébastien Roux ___ 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] instr function or equivalent
Could your application supply the values in code? What exactly are you trying to do? RBS On 9/10/12, Sébastien Roux roux.sebast...@gmail.com wrote: Thanks Bart but substr require hard coded positions I guess, I need to get this position dynamically! Sébastien Roux 2012/9/10 Bart Smissaert bart.smissa...@gmail.com http://www.sqlite.org/lang_corefunc.html Look at substr RBS On 9/10/12, Sébastien Roux roux.sebast...@gmail.com wrote: Hi, I'm looking for the[in]famous sqlite *instr* function which doesn't exist (searched the web so far without success). Also searched for a *position*function, without success too! Any idea or help? I found some threads about custom functions but no tutorial nor deeper explanations! Many thanks. Sébastien Roux ___ 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] instr function or equivalent
Ah, OK. I have a feeling that needs to be done either in your application code or with a user defined SQLite function. Somebody may prove me wrong. RBS On 9/10/12, Sébastien Roux roux.sebast...@gmail.com wrote: I want to remove/trim characters strating from from til ] Logging in user [aa] from [10.165.69.247] 194|2012-09-07|Logging in user [a] from [10.296.44.163] 160|2012-09-04|Logging in user [aaa] from [10.164.69.248] 136|2012-09-07|Logging in user [aaa] from [10.168.59.169] 132|2012-09-07|Logging in user [aaa] from [10.169.22.58] Sébastien Roux 2012/9/10 Bart Smissaert bart.smissa...@gmail.com Could your application supply the values in code? What exactly are you trying to do? RBS On 9/10/12, Sébastien Roux roux.sebast...@gmail.com wrote: Thanks Bart but substr require hard coded positions I guess, I need to get this position dynamically! Sébastien Roux 2012/9/10 Bart Smissaert bart.smissa...@gmail.com http://www.sqlite.org/lang_corefunc.html Look at substr RBS On 9/10/12, Sébastien Roux roux.sebast...@gmail.com wrote: Hi, I'm looking for the[in]famous sqlite *instr* function which doesn't exist (searched the web so far without success). Also searched for a *position*function, without success too! Any idea or help? I found some threads about custom functions but no tutorial nor deeper explanations! Many thanks. Sébastien Roux ___ 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-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] instr function or equivalent
Nice one! Works here.
RBS
On 9/10/12, Black, Michael (IS) michael.bla...@ngc.com wrote:
Does this work for you?
SQLite version 3.7.13 2012-06-11 02:05:22
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite create table t(s);
sqlite insert into t values('Logging in user [aa] from
[10.165.69.247]');
sqlite insert into t values('194|2012-09-07|Logging in user [a] from
[10.296.44.163]');
sqlite insert into t values('160|2012-09-04|Logging in user [aaa] from
[10.164.69.248]');
sqlite insert into t values('136|2012-09-07|Logging in user [aaa] from
[10.168.59.169]');
sqlite insert into t values('132|2012-09-07|Logging in user [aaa]
from [10.169.22.58]');
sqlite select rtrim(rtrim(s,']'),'.1234567890[ from') from t;
Logging in user [aa]
194|2012-09-07|Logging in user [a]
160|2012-09-04|Logging in user [aaa]
136|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [aaa]
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Sébastien Roux [roux.sebast...@gmail.com]
Sent: Monday, September 10, 2012 10:22 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] instr function or equivalent
Sad! Would you have any link toward SQLite's user defined SQLite function?
Many thanks.
Sébastien Roux
___
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] instr function or equivalent
This is slightly faster:
select rtrim(s,' from [.0123456789]') || ']' from t
RBS
On 9/10/12, Bart Smissaert bart.smissa...@gmail.com wrote:
Nice one! Works here.
RBS
On 9/10/12, Black, Michael (IS) michael.bla...@ngc.com wrote:
Does this work for you?
SQLite version 3.7.13 2012-06-11 02:05:22
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite create table t(s);
sqlite insert into t values('Logging in user [aa] from
[10.165.69.247]');
sqlite insert into t values('194|2012-09-07|Logging in user [a] from
[10.296.44.163]');
sqlite insert into t values('160|2012-09-04|Logging in user [aaa]
from
[10.164.69.248]');
sqlite insert into t values('136|2012-09-07|Logging in user [aaa] from
[10.168.59.169]');
sqlite insert into t values('132|2012-09-07|Logging in user
[aaa]
from [10.169.22.58]');
sqlite select rtrim(rtrim(s,']'),'.1234567890[ from') from t;
Logging in user [aa]
194|2012-09-07|Logging in user [a]
160|2012-09-04|Logging in user [aaa]
136|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [aaa]
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
on
behalf of Sébastien Roux [roux.sebast...@gmail.com]
Sent: Monday, September 10, 2012 10:22 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] instr function or equivalent
Sad! Would you have any link toward SQLite's user defined SQLite
function?
Many thanks.
Sébastien Roux
___
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] instr function or equivalent
Yes, you are right there.
As rtrim incorporates an instr type of function I am not sure why there
is no plain instr function in SQLite. It would make things a bit simpler.
RBS
On 9/10/12, Black, Michael (IS) michael.bla...@ngc.com wrote:
It might be faster but it doesn't work for anybody who has any letters in
from in their name.
sqlite insert into t values('132|2012-09-07|Logging in user [tom] from
[10.169.22.59]');
sqlite select rtrim(s,' from [.0123456789]') || ']' from t;
Logging in user [aa]
194|2012-09-07|Logging in user [a]
160|2012-09-04|Logging in user [aaa]
136|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [t]
The original way still works just fine.
sqlite select rtrim(rtrim(s,']'),'.1234567890[ from') from t;
Logging in user [aa]
194|2012-09-07|Logging in user [a]
160|2012-09-04|Logging in user [aaa]
136|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [tom]
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
You have to be very careful when parsing char sets like this to ensure your
barriers are valid.
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Bart Smissaert [bart.smissa...@gmail.com]
Sent: Monday, September 10, 2012 11:19 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] instr function or equivalent
This is slightly faster:
select rtrim(s,' from [.0123456789]') || ']' from t
RBS
On 9/10/12, Bart Smissaert bart.smissa...@gmail.com wrote:
Nice one! Works here.
RBS
On 9/10/12, Black, Michael (IS) michael.bla...@ngc.com wrote:
Does this work for you?
SQLite version 3.7.13 2012-06-11 02:05:22
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite create table t(s);
sqlite insert into t values('Logging in user [aa] from
[10.165.69.247]');
sqlite insert into t values('194|2012-09-07|Logging in user [a]
from
[10.296.44.163]');
sqlite insert into t values('160|2012-09-04|Logging in user [aaa]
from
[10.164.69.248]');
sqlite insert into t values('136|2012-09-07|Logging in user [aaa] from
[10.168.59.169]');
sqlite insert into t values('132|2012-09-07|Logging in user
[aaa]
from [10.169.22.58]');
sqlite select rtrim(rtrim(s,']'),'.1234567890[ from') from t;
Logging in user [aa]
194|2012-09-07|Logging in user [a]
160|2012-09-04|Logging in user [aaa]
136|2012-09-07|Logging in user [aaa]
132|2012-09-07|Logging in user [aaa]
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
on
behalf of Sébastien Roux [roux.sebast...@gmail.com]
Sent: Monday, September 10, 2012 10:22 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] instr function or equivalent
Sad! Would you have any link toward SQLite's user defined SQLite
function?
Many thanks.
Sébastien Roux
___
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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] instr function or equivalent
Yes, but doesn't rtrim do an instr function with the same problems as you mention? RBS On 9/10/12, Simon Slavin slav...@bigfraud.org wrote: On 10 Sep 2012, at 5:36pm, Bart Smissaert bart.smissa...@gmail.com wrote: Yes, you are right there. As rtrim incorporates an instr type of function I am not sure why there is no plain instr function in SQLite. It would make things a bit simpler. Two ways to add appropriate functions to SQLite to do it. Either supply a 'find substring' function which returns the character number of where the substring is found, or supply a GLOB-type or regexp-type 'replace' function. Neither of them are trivial given that strings /may/ be 16-bit. 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] Can I rely on this being ordered?
Have the following table: CREATE TABLE READCODE( [SUBJECT_TYPE] TEXT, [READ_CODE] TEXT, [TERM30] TEXT, [TERM60] TEXT, [ENTRY_COUNT] INTEGER) Records are ordered ascending on READ_CODE as the records are obtained from an ordered array and inserted sequentially. There is a non-unique index on Read code. Now I need to run queries like this: SELECT DISTINCT READ_CODE, TERM30, TERM60, ENTRY_COUNT FROM READCODE WHERE TERM30 LIKE '%ANGINA%' OR TERM60 LIKE '%ANGINA%' Now if I do this then the ascending order on READ_CODE is lost, so I need to add an ORDER BY However if I do this: SELECT READ_CODE, TERM30, TERM60, ENTRY_COUNT FROM READCODE WHERE TERM30 LIKE '%ANGINA%' UNION SELECT READ_CODE, TERM30, TERM60, ENTRY_COUNT FROM READCODE WHERE TERM60 LIKE '%ANGINA%' Then I get the required ascending order on READ_CODE. This looks good as this query is some 50% faster. However, I am not sure if I can rely on this order to always happen. I take it that this an unintended outcome that might change in future versions? Is this indeed the case? RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can I rely on this being ordered?
OK, thanks, that confirms my suspicion then. RBS On Tue, Jul 3, 2012 at 10:00 PM, Igor Tandetnik itandet...@mvps.org wrote: On 7/3/2012 4:53 PM, Bart Smissaert wrote: However if I do this: SELECT READ_CODE, TERM30, TERM60, ENTRY_COUNT FROM READCODE WHERE TERM30 LIKE '%ANGINA%' UNION SELECT READ_CODE, TERM30, TERM60, ENTRY_COUNT FROM READCODE WHERE TERM60 LIKE '%ANGINA%' Then I get the required ascending order on READ_CODE. This looks good as this query is some 50% faster. However, I am not sure if I can rely on this order to always happen. I'd rather not if I were you. It's likely just an accident of implementation. Generally, SQLite doesn't guarantee any particular order of the records unless there's an explicit ORDER BY clause. -- 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] Can I rely on this being ordered?
Thanks for that tip, useful to know that one. RBS On Tue, Jul 3, 2012 at 11:37 PM, Roger Binns rog...@rogerbinns.com wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/07/12 14:03, Bart Smissaert wrote: OK, thanks, that confirms my suspicion then. SQLite can also help you. Run your test suite normally, and then run again with this pragma which gives a different order to unordered selects. Your test suite should help pick up places where you assumed an ordering: http://www.sqlite.org/pragma.html#pragma_reverse_unordered_selects Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAk/zdDAACgkQmOOfHg372QQNDACgtxix1/92FxlMKP2ZfYIxFDLz qf8AoIityNNnSao0Jh75Vs67swDqvQkw =QF+6 -END PGP SIGNATURE- ___ 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] substr bug in 3.7.13?
Should that zero not be a 1?
From the documentation:
The left-most character of X is number 1
RBS
On 6/19/12, Yongil Jang yongilj...@gmail.com wrote:
Dear all,
I've found following result when I try to use 'substr' function.
sqlite create table test (data text);
sqlite insert into test values ('010101');
sqlite select substr(data, 0, 2) from test;
0
sqlite select substr(data, 0, 3) from test;
01
As you can see, string length should be one plus value to get correct
length of string.
I'm using sqlite 3.7.13 legacy source code and compiled on my Ubuntu server
10.04 64bit.
Thank you for read this message.
___
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?
On 3/5/12, Fabio Spadaro fabiolinos...@gmail.com wrote: Hi. Il giorno 05 marzo 2012 14:50, Rob Richardson rdrichard...@rad-con.com ha scritto: ... I keep hoping to find something better, because SQLite Spy does not offer the ability to edit a table inside a grid ... RobR With sqlite root is possbile to edit the table inside a grid and foreign keys. -- Fabio Spadaro Try Sqlite Root a GUI Admin Tools for manage Sqlite Database: www.sqliteroot.com ___ 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] problem with case when
Have a table with an integer age field and a text age_group field. Need to update the age_group field according to the age. Tried with several case when constructions, but sofar nil working, eg: update pats set age_group = (case when age between(0 and 9) then '0 to 9' when age between(10 and 19) then '10 to 19' when age between(20 and 29) then '20 to 29' when age between(30 and 39) then '30 to 39' when age between(40 and 49) then '40 to 49' when age between(50 and 59) then '50 to 59' when age between(60 and 69) then '60 to 69' when age between(70 and 79) then '70 to 79' when age between(80 and 89) then '80 to 89' when age between(90 and 99) then '90 to 99' when age 99 then '100 and above' end) How can this be done? RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] problem with case when
Yes, thanks had just figured that out the same. Working fine now. RBS On Tue, Feb 7, 2012 at 11:17 PM, Richard Hipp d...@sqlite.org wrote: On Tue, Feb 7, 2012 at 6:14 PM, Bart Smissaert bart.smissa...@gmail.comwrote: Have a table with an integer age field and a text age_group field. Need to update the age_group field according to the age. Tried with several case when constructions, but sofar nil working, eg: update pats set age_group = (case when age between(0 and 9) then '0 to 9' when age between(10 and 19) then '10 to 19' when age between(20 and 29) then '20 to 29' when age between(30 and 39) then '30 to 39' when age between(40 and 49) then '40 to 49' when age between(50 and 59) then '50 to 59' when age between(60 and 69) then '60 to 69' when age between(70 and 79) then '70 to 79' when age between(80 and 89) then '80 to 89' when age between(90 and 99) then '90 to 99' when age 99 then '100 and above' end) How can this be done? Omit the parentheses RBS ___ 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
Re: [sqlite] Is there any API for counting number of rows in a particular table.
SQLiteRoot is a free SQLite manager managed in this thread. I downloaded and tried it, but couldn't figure out how to open databases with an extension other than the default for that app, which I think was .slt Nil to do with SQLite really. RBS On 1/31/12, Tim Streater t...@clothears.org.uk wrote: On 30 Jan 2012 at 21:58, Bart Smissaert bart.smissa...@gmail.com wrote: OK, so how you open those then with SQLiteRoot? I use PHP, thus: $dbh = new PDO (sqlite: . $db); where $db is a string like /path/to/database. I don't know what SQLiteRoot is although possibly I should do. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to find number of columns and types in a table.
http://www.sqlite.org/faq.html#q7 RBS On 1/31/12, bhaskarReddy uni...@gmail.com wrote: Hi Friends, Is there any way to find the number of columns and column types in a table. I tried with select count(*) from tablename. But it is showing number of records. Regards, Bhaskar. -- View this message in context: http://old.nabble.com/How-to-find-number-of-columns-and-types-in-a-table.-tp33235184p33235184.html Sent from the SQLite mailing list archive at Nabble.com. ___ 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] Is there any API for counting number of rows in a particular table.
How do you make it open database files that have an extension other than .slt? RBS On 1/30/12, Fabio Spadaro fabiolinos...@gmail.com wrote: Hi 2012/1/30 bhaskarReddy uni...@gmail.com Its working with only table name. Regards, Bhaskar. You could use a GUI to facilitate these requests, and your this regard we propose to test my application it is freeware: www.sqliteroot.com http://www.sqliteroot.com -- Fabio Spadaro Try Sqlite Root a GUI Admin Tools for manage Sqlite Database: www.sqliteroot.com ___ 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] Is there any API for counting number of rows in a particular table.
OK, so how you open those then with SQLiteRoot? RBS On Mon, Jan 30, 2012 at 9:46 PM, Tim Streater t...@clothears.org.uk wrote: On 30 Jan 2012 at 16:37, Bart Smissaert bart.smissa...@gmail.com wrote: How do you make it open database files that have an extension other than .slt? Eh? None of my SQLite databases has any extension at all. -- Cheers -- Tim ___ 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] Bug
And FWIW, this query works as expected on MS SQL Works on Firebird and produces one record with value 1. RBS On Wed, Nov 23, 2011 at 4:35 PM, Pavel Ivanov paiva...@gmail.com wrote: On Wed, Nov 23, 2011 at 11:28 AM, Simon Slavin slav...@bigfraud.org wrote: On 23 Nov 2011, at 4:17pm, Wiktor Adamski wrote: sqlite select 1 from t order by avg(a); -- should be possible Why should this be possible ? For an 'ORDER BY' you need a value for each row. But aggregate functions produce only one value for the whole SELECT command. My first reaction was the same. But although it's completely senseless just syntactically it looks correct - should produce just one row and thus ORDER BY will be a no-op. And FWIW, this query works as expected on MS SQL (query with GROUP BY 1 doesn't work though). Pavel ___ 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] can you select series with SQL?
Thanks; that to me looks a truly amazing SQL! As you say doing this in code might be more efficient and definitely less confusing. In fact when doing something as you suggest: select * from MyTable where ID = 14 order by ID desc I can make it a lot more efficient by adding a limit as not many records will be needed. RBS On Sun, Nov 20, 2011 at 12:15 AM, Igor Tandetnik itandet...@mvps.org wrote: Bart Smissaert bart.smissa...@gmail.com wrote: If we have the 14 (we know to start at 14) can we select the records 14, 13, 12 and 11, so the consecutive numbers, going down from 14? select * from MyTable t1 where (select count(*) from MyTable t2 where t2.ID between t1.ID and 14) == 14 - t1.ID + 1; Personally, I'd just run a query like select * from MyTable where ID = 14 order by ID desc; and step through it until the next ID is non-consecutive. -- 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] can you select series with SQL?
I am not not working that close to the SQLite source to talk about sqlite3_step etc. as I am using a VB wrapper. Still, I suppose what you say still applies. As it turns out and can now beforehand (without checking for non-consecutive id numbers) how many records should be fetched, so with that things are in fact simple. RBS On Sun, Nov 20, 2011 at 2:12 PM, Igor Tandetnik itandet...@mvps.org wrote: Bart Smissaert bart.smissa...@gmail.com wrote: In fact when doing something as you suggest: select * from MyTable where ID = 14 order by ID desc I can make it a lot more efficient by adding a limit as not many records will be needed. There's no difference between adding a LIMIT N clause to the query, and simply calling sqlite3_step N times and then resetting or finalizing. -- 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
[sqlite] can you select series with SQL?
Say we have a table table1 with unique integer field ID. Now we have the following data: ID 1 2 3 4 11 12 13 14 If we have the 14 (we know to start at 14) can we select the records 14, 13, 12 and 11, so the consecutive numbers, going down from 14? RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Limit COUNT
That seems to be the answer and after some quick testing it looks it makes it more efficient as well! RBS On Tue, Oct 18, 2011 at 1:36 PM, Kit kit.sa...@gmail.com wrote: 2011/10/16 Fabian fabianpi...@gmail.com: How can you limit a count-query? I tried: SELECT COUNT(*) FROM table LIMIT 5000 SELECT min(COUNT(*),5000) FROM table; -- Kit ___ 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] Limit COUNT
He is trying to make it more efficient, so stop counting if count X. So setting the count after having counted the whole lot won't help. RBS On Sun, Oct 16, 2011 at 2:46 PM, Simon Slavin slav...@bigfraud.org wrote: On 16 Oct 2011, at 1:21pm, Fabian wrote: 2011/10/16 Frank Missel i...@missel.sg What do you want to attain with the count? I want to allow users to paginate through a result set. The pages are retreived through LIMIT/OFFSET, but to calculate the total number of pages, I have execute a separate COUNT() query (without LIMIT) once. You're using a programming language. So do this: numberOfRows = SELECT count(*) FROM myTable if (numberOfRows 100) then numberOfRows = 100 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] How to use SQLite as a data source in Excel (tables and pivottables)
Hi Frank, But I guess the best for now will be to just accept creating a DSN for each individual database and use the ODBC driver. Looks that is your best option then, yes. You could create DSN's in code via the Windows API. Can't see any great problem with that. RBS On Fri, Oct 14, 2011 at 2:33 AM, Frank Missel i...@missel.sg wrote: boun...@sqlite.org] On Behalf Of Bart Smissaert Sent: 14 October 2011 04:05 To: General Discussion of SQLite Database Subject: Re: [sqlite] How to use SQLite as a data source in Excel (tables and pivottables) It looks you can't make a pivot table directly from an array. What you could do though is write the array to a text file and base the array on that file as an external data source via a text driver. Another option is build your pivot table in code, not using the Excel pivot table object. RBS I do sometimes use the option of building the Pivottable through usage of the SELECT and GROUP BY. However, it is then frozen in that form. The nice thing about Pivottables is that the row and column fields can be changed on the spur to get a new view of the data. As for text files I find that they introduce yet another layer. The data is already coming from somewhere else then stored in SQLite, and now they then have to go to a text file to then be imported to Excel. Also, this may create new challenges with the data types being recognized correctly, and more importantly: I could not find a way to programmatically get Excel to take a text file as basis of a Pivottable. Sure, doing it manually is no problem at all but back with Excel 2003, I and some others tried to get it done through Automation (Excels COM object model) -- it simple could not be done (I almost suspect this was so by design from MS). Perhaps it is different in Excel 2007 / 2010, but I could imagine not. All in all it would be nice to just use the SQLite database as a proper data source like you can with Oracle, SQL server and a number of other databases / data sources. But I guess the best for now will be to just accept creating a DSN for each individual database and use the ODBC driver. Its a bit messy programmatically as you have to access the Registry but it can be done. /Frank ___ 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 use SQLite as a data source in Excel (tables and pivottables)
I use SQLite extensively as a data source in Excel and have never come across this problem. Is use Olaf Schmidt's VB wrapper vbRichClient4 and vb_cairo_sqlite. If you can send me a workbook that clearly demonstrates the problem then I can see if I can deal with it with the above wrapper. I am sure there will be no problem at all. RBS On Thu, Oct 13, 2011 at 5:48 PM, Frank Missel i...@missel.sg wrote: I am trying to use data from an SQLite database as a data source for a Pivottable in an Excel sheet. By use of Micrsoft Query and the SQLite ODBC driver I can reference a table from an SQLite database either as a list in a worksheet or as basis for a Pivottable. This apparently works fine. However, there is an issue with the decimal data type which is not recognized, i.e. the cells are of the type General whereas the integer and date fields are represented with the correct cell format and function. Even if one does change the format of the cells containing data from a decimal field to Number, they still do not work properly as numbers, i.e. the sum function does not work correctly. I have emailed with the author of the SQLite ODBC driver, Christian Werner, about the problem. He writes: The problem is the typelessness of SQLite. In order to obtain column information early, a SELECT is prepared twice. The first gives the column names and potential type information. In the second phase the second select retrieves data. For computed columns, SQLite usually reports SQLITE_TEXT or even SQLITE_NULL in sqlite3_column_type. As long as an ODBC application retrieves in advance the correct typed values of a rowset, everything is fine. But that seems not to be the case for Excel/Query. I have also installed the System.SQlite.Data ADO.NET driver in the hope that perhaps it could be used as an OLE DB data source or other type of data source that could be chosen from Excel. However, it does not show in the various list of data sources so perhaps that is not possible. Does anyone have experience and/or ideas about how to use SQLite as a proper data source that can be accessed from Excel besides the ODBC driver which has the mentioned problems with decimal fields? /Frank Missel ___ 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 use SQLite as a data source in Excel (tables and pivottables)
Hi Frank, This VB wrapper is not an ODBC driver, so there is no DSN. The database file is set in the connection string: Function OpenDB([FileName As String], [EncrKey As String], [EnableVBFunctions As Boolean = True]) As Boolean Member of vbRichClient4.cConnection RBS On Thu, Oct 13, 2011 at 7:11 PM, Frank Missel i...@missel.sg wrote: Bart, thanks for the offer, but we found the cause of the problems in the data type naming of the columns. Have you found a way to avoid having to define DSN's for each individual database? /Frank -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Bart Smissaert Sent: 14 October 2011 01:35 To: General Discussion of SQLite Database Subject: Re: [sqlite] How to use SQLite as a data source in Excel (tables and pivottables) I use SQLite extensively as a data source in Excel and have never come across this problem. Is use Olaf Schmidt's VB wrapper vbRichClient4 and vb_cairo_sqlite. If you can send me a workbook that clearly demonstrates the problem then I can see if I can deal with it with the above wrapper. I am sure there will be no problem at all. RBS ___ 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 use SQLite as a data source in Excel (tables and pivottables)
Hi Frank, But are you saying that you are use the wrapper itself as a data source in Excel? No, the wrapper is not used that way and I don't think it can be used that way. The SQLite database is dealt with in VBA or VB6 code via this wrapper. I suppose you could compare it to using ADO with a DSN-less connection. RBS On Thu, Oct 13, 2011 at 7:41 PM, Frank Missel i...@missel.sg wrote: Hi Bart, Okay, but I thought that the wrapper was just for working with the SQLite database and then later when you wanted to use the database as a data source that you would then still use the ODBC driver. But are you saying that you are use the wrapper itself as a data source in Excel? And if so, how do you specify the wrapper as a data source? /Frank -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Bart Smissaert Sent: 14 October 2011 02:21 To: General Discussion of SQLite Database Subject: Re: [sqlite] How to use SQLite as a data source in Excel (tables and pivottables) Hi Frank, This VB wrapper is not an ODBC driver, so there is no DSN. The database file is set in the connection string: Function OpenDB([FileName As String], [EncrKey As String], [EnableVBFunctions As Boolean = True]) As Boolean Member of vbRichClient4.cConnection RBS On Thu, Oct 13, 2011 at 7:11 PM, Frank Missel i...@missel.sg wrote: Bart, thanks for the offer, but we found the cause of the problems in the data type naming of the columns. Have you found a way to avoid having to define DSN's for each individual database? /Frank ___ 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 use SQLite as a data source in Excel (tables and pivottables)
Hi Frank, So when referencing an SQLite database from Excel you also use the ODBC driver I guess. I don't access SQLite this way. Only access through this VB wrapper. I now use the C API directly Interesting. Why, if you had a well performing VB wrapper, did you go this route? If anyone have any solution for this or any other, easier alternative way of accessing an SQLite database as a data source programmatically This is exactly what I do and no problem at all for example to produce a pivot table based on data from SQLite. RBS On Thu, Oct 13, 2011 at 8:10 PM, Frank Missel i...@missel.sg wrote: Hi Bart, boun...@sqlite.org] On Behalf Of Bart Smissaert No, the wrapper is not used that way and I don't think it can be used that way. The SQLite database is dealt with in VBA or VB6 code via this wrapper. I suppose you could compare it to using ADO with a DSN-less connection. RBS Okay, that's what I thought. I did try the wrapper a couple of years ago and found it very well designed and performing; I can also recommend it for VB 6, VBA or VBScript. I now use the C API directly -- sort of my own wrapper for some special purposes. So when referencing an SQLite database from Excel you also use the ODBC driver I guess. This brings me to my main remaining issue which is to avoid having to create an individual data source for each SQLite database. If anyone have any solution for this or any other, easier alternative way of accessing an SQLite database as a data source programmatically through the Excel COM object model (in order to e.g. create a Pivottable), I would be very eager to hear about it :-). /Frank ___ 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 use SQLite as a data source in Excel (tables and pivottables)
My problem is that the data basis of the Pivottable will sometimes be millions of rows OK, I haven't got that problem and my pivots are based on a sheet range. Sheet range is based on a variant array obtained from SQLite. I will need to check, but I think you can use an array for the basis of a pivot. RBS On Thu, Oct 13, 2011 at 8:36 PM, Frank Missel i...@missel.sg wrote: Hi Bart, Interesting. Why, if you had a well performing VB wrapper, did you go this route? 1. Implementing the wrapper in the project code would also take some coding, and I found that using the C API would not be that much extra work. Thus I could save a layer, which was good as the project had several other layers already. 2. The project required heavy data loads. I thought that I could get better performance and control of data validation, i.e. I could decide exactly how much and what to have. 3. It was a bit fascinating to get to work close to engine -- minimalistic and effective is always fascinating :-). If anyone have any solution for this or any other, easier alternative way of accessing an SQLite database as a data source programmatically This is exactly what I do and no problem at all for example to produce a pivot table based on data from SQLite. Interesting, how do you get the data from the table or view into Excel to be the basis of the Pivottable? Do you paste it to a worksheet (perhaps as arrays) that then becomes the basis of the Pivottable? My problem is that the data basis of the Pivottable will sometimes be millions of rows, i.e. many more than can be contained in a worksheet. But when referencing the data source directly as a proper data source the number of rows are not limited to the maximum number of allowed rows in a worksheet. /Frank ___ 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 use SQLite as a data source in Excel (tables and pivottables)
It looks you can't make a pivot table directly from an array. What you could do though is write the array to a text file and base the array on that file as an external data source via a text driver. Another option is build your pivot table in code, not using the Excel pivot table object. RBS On Thu, Oct 13, 2011 at 8:44 PM, Bart Smissaert bart.smissa...@gmail.com wrote: My problem is that the data basis of the Pivottable will sometimes be millions of rows OK, I haven't got that problem and my pivots are based on a sheet range. Sheet range is based on a variant array obtained from SQLite. I will need to check, but I think you can use an array for the basis of a pivot. RBS On Thu, Oct 13, 2011 at 8:36 PM, Frank Missel i...@missel.sg wrote: Hi Bart, Interesting. Why, if you had a well performing VB wrapper, did you go this route? 1. Implementing the wrapper in the project code would also take some coding, and I found that using the C API would not be that much extra work. Thus I could save a layer, which was good as the project had several other layers already. 2. The project required heavy data loads. I thought that I could get better performance and control of data validation, i.e. I could decide exactly how much and what to have. 3. It was a bit fascinating to get to work close to engine -- minimalistic and effective is always fascinating :-). If anyone have any solution for this or any other, easier alternative way of accessing an SQLite database as a data source programmatically This is exactly what I do and no problem at all for example to produce a pivot table based on data from SQLite. Interesting, how do you get the data from the table or view into Excel to be the basis of the Pivottable? Do you paste it to a worksheet (perhaps as arrays) that then becomes the basis of the Pivottable? My problem is that the data basis of the Pivottable will sometimes be millions of rows, i.e. many more than can be contained in a worksheet. But when referencing the data source directly as a proper data source the number of rows are not limited to the maximum number of allowed rows in a worksheet. /Frank ___ 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] I havn't a clue
Best way to do this is probably with a VB SQLite wrapper and I would recommend this one: http://www.thecommon.net/3.html Download from this link. The Toolset-Binaries: (ca. 1.8MB) There is example code showing you how to use it. If you get stuck I can mail you a workbook that demonstrates it all. RBS On Sat, Jul 16, 2011 at 2:28 AM, Dave Klein pepsiki...@yahoo.com wrote: Can anyone out there tell me how I can take a SQLite database and open it in Microsoft Excell? I'm not stupid but I don't know servers, I don't know ODBC's all I know is according to a the software I use that my data is stored in a SQLite database with the name SlotCar30.db and their website says it can be viewed and modified in excell, but the steps are missing a few details like Server names, OBDC,s and a whole lot more I don't understand. Even SQLite website say something about SQLite.exe but the Zip file has no .exe file in it. Please help me. ___ 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] I havn't a clue
Yes, if the OP is not familiar with VBA then that is the best option. If he is familiar with VBA then with the mentioned wrapper you could write a simple Excel add-in (.xla) that will allow you to dump data from SQLite to Excel. RBS On Sun, Jul 17, 2011 at 12:56 PM, Baruch Burstein bmburst...@gmail.com wrote: Best way is to use a tool like SQLite Database Browser (or a similar tool). You can get it from http://portableapps.com/apps/development/sqlite_database_browser_portable or from http://sourceforge.net/projects/sqlitebrowser/files/sqlitebrowser/2.0%20beta1/sqlitebrowser_200_b1_win.zip/download. Open the database, select the table you need and select Export as CSV on the File menu. Excel can open csv files. On Sat, Jul 16, 2011 at 4:28 AM, Dave Klein pepsiki...@yahoo.com wrote: Can anyone out there tell me how I can take a SQLite database and open it in Microsoft Excell? I'm not stupid but I don't know servers, I don't know ODBC's all I know is according to a the software I use that my data is stored in a SQLite database with the name SlotCar30.db and their website says it can be viewed and modified in excell, but the steps are missing a few details like Server names, OBDC,s and a whole lot more I don't understand. Even SQLite website say something about SQLite.exe but the Zip file has no .exe file in it. Please help me. ___ 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] Why does analyze make this go faster?
Have the following query: INSERT INTO A3Test7D4_J (PATIENT_ID, ENTRY_ID, READ_CODE, TERM_TEXT, ADDED_DATE, START_DATE, NUMERIC_VALUE, TYPE_SPECIFIC_INFO) SELECT E.PATIENT_ID, E.ENTRY_ID, E.READ_CODE, E.TERM_TEXT, E.ADDED_DATE, E.START_DATE, EA.NUMERIC_VALUE, EA.TYPE_SPECIFIC_INFO FROM A3Test7D4_E E INNER JOIN ENTRY_ATTRIBUTES EA ON (EA.ENTRY_ID = +E.ENTRY_ID) WHERE EA.ATTRIBUTE_TYPE = 101 ORDER BY E.PATIENT_ID ASC, E.ENTRY_ID ASC, EA.NUMERIC_VALUE ASC And as it was quite slow (50 secs) I did an analyze on the table ENTRY_ATTRIBUTES and after that the query was much faster, less than one second. Now, what I don't understand about this is that the query plan is still the same: 0--0--0--SCAN TABLE A3Test7D4_E AS E (~18491 rows) -- 0--1--1--SEARCH TABLE ENTRY_ATTRIBUTES AS EA USING COVERING INDEX IDX_ENTRY_ATTRIBUTES_ATTRIBUTE_TYPE_ENTRY_ID_TYPE_SPECIFIC_INFO_NUMERIC_VALUE (ATTRIBUTE_TYPE=? AND ENTRY_ID=?) (~2 rows) -- 0--0--0--USE TEMP B-TREE FOR ORDER BY And also the data in sqlite_stat1 is still the same: IDX_ENTRY_ATTRIBUTES_NUMERIC_VALUE 1389416 176 IDX_ENTRY_ATTRIBUTES_TYPE_SPECIFIC_INFO 1389416 9 IDX_ENTRY_ATTRIBUTES_ENTRY_ID 1389416 2 IDX_ENTRY_ATTRIBUTES_ATTRIBUTE_TYPE 1389416 198488 IDX_ENTRY_ATTRIBUTES_ATTRIBUTE_TYPE_ENTRY_ID_TYPE_SPECIFIC_INFO_NUMERIC_VALUE 1389416 198488 2 2 1 The data in the table is the same as well. The other table involved A3Test7D4_E is exactly the same as well. So why runs this query so much faster after running the analyze? RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why does analyze make this go faster?
The table A3Test7D4_E is deleted and re-written every time. The table ENTRY_ATTRIBUTES is large table (millions of records) and it will need an analyze when made first time. Doesn't look cache is the reason for this behaviour here. RBS On Wed, Jul 13, 2011 at 1:44 PM, Stephen C pontia...@gmail.com wrote: What happens when you let the query run the first time, delete the records, then re-run the query? If the same length of time is spent (50 seconds) then I'm at a loss. If the shorter length of time happens, I'd say blame caching. On 07/13/2011 06:00 AM, Bart Smissaert wrote: Have the following query: INSERT INTO A3Test7D4_J (PATIENT_ID, ENTRY_ID, READ_CODE, TERM_TEXT, ADDED_DATE, START_DATE, NUMERIC_VALUE, TYPE_SPECIFIC_INFO) SELECT E.PATIENT_ID, E.ENTRY_ID, E.READ_CODE, E.TERM_TEXT, E.ADDED_DATE, E.START_DATE, EA.NUMERIC_VALUE, EA.TYPE_SPECIFIC_INFO FROM A3Test7D4_E E INNER JOIN ENTRY_ATTRIBUTES EA ON (EA.ENTRY_ID = +E.ENTRY_ID) WHERE EA.ATTRIBUTE_TYPE = 101 ORDER BY E.PATIENT_ID ASC, E.ENTRY_ID ASC, EA.NUMERIC_VALUE ASC And as it was quite slow (50 secs) I did an analyze on the table ENTRY_ATTRIBUTES and after that the query was much faster, less than one second. Now, what I don't understand about this is that the query plan is still the same: 0--0--0--SCAN TABLE A3Test7D4_E AS E (~18491 rows)-- 0--1--1--SEARCH TABLE ENTRY_ATTRIBUTES AS EA USING COVERING INDEX IDX_ENTRY_ATTRIBUTES_ATTRIBUTE_TYPE_ENTRY_ID_TYPE_SPECIFIC_INFO_NUMERIC_VALUE (ATTRIBUTE_TYPE=? AND ENTRY_ID=?) (~2 rows)-- 0--0--0--USE TEMP B-TREE FOR ORDER BY And also the data in sqlite_stat1 is still the same: IDX_ENTRY_ATTRIBUTES_NUMERIC_VALUE 1389416 176 IDX_ENTRY_ATTRIBUTES_TYPE_SPECIFIC_INFO 1389416 9 IDX_ENTRY_ATTRIBUTES_ENTRY_ID 1389416 2 IDX_ENTRY_ATTRIBUTES_ATTRIBUTE_TYPE 1389416 198488 IDX_ENTRY_ATTRIBUTES_ATTRIBUTE_TYPE_ENTRY_ID_TYPE_SPECIFIC_INFO_NUMERIC_VALUE 1389416 198488 2 2 1 The data in the table is the same as well. The other table involved A3Test7D4_E is exactly the same as well. So why runs this query so much faster after running the analyze? RBS ___ 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] surprising missing query optimization
Joining a large table (ENTRY_ATTRIBUTES) and a small table (BPNewENTRY) and putting the resulting records in a third table, BP3. Large table may have a few million records and small table a few hundred records. The join field is called ENTRY_ID in both tables and this has a non-unique index in the large table and is the integer primary key in the small table. Data type is integer in both these fields. This is the SQL: INSERT OR REPLACE INTO BP3 (ENTRY_ID, NUMERIC_VALUE, UNIT) SELECT EA.ENTRY_ID, EA.NUMERIC_VALUE, EA.TYPE_SPECIFIC_INFO FROM ENTRY_ATTRIBUTES EA INNER JOIN BPNewENTRY E ON (EA.ENTRY_ID = E.ENTRY_ID) and this is the generated query plan for that: 0--0--0--SCAN TABLE ENTRY_ATTRIBUTES AS EA (~54855 rows) -- 0--1--1--SEARCH TABLE BPNewENTRY AS E USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) Now I can speed up the query a lot by putting a where clause in with the min(ENTRY_ID) of the small table: INSERT OR REPLACE INTO BP3 (ENTRY_ID, NUMERIC_VALUE, UNIT) SELECT EA.ENTRY_ID, EA.NUMERIC_VALUE, EA.TYPE_SPECIFIC_INFO FROM ENTRY_ATTRIBUTES EA INNER JOIN BPNewENTRY E ON (EA.ENTRY_ID = E.ENTRY_ID) WHERE EA.ENTRY_ID = 4262936 and that will have this query plan: 0--0--0--SEARCH TABLE ENTRY_ATTRIBUTES AS EA USING INDEX IDX_ENTRY_ATTRIBUTES_ENTRY_ID (ENTRY_ID?) (~18102 rows) -- 0--1--1--SEARCH TABLE BPNewENTRY AS E USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) Getting this min(ENTRY_ID) is done in a separate query and because it is done on the small table it is very fast. Now what surprises me is that this optimization is not done automatically by SQLite. I suppose I just over estimate the capabilities of the SQLite plan generator. Or, would this be something that could be improved? I am using SQLite version 3.7.5. RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] surprising missing query optimization
it thinks that scanning the whole ENTRY_ATTRIBUTES table will read about 54855 rows. And you say that it has much more rows. This particular database has less rows, the millions I mentioned are in a different database. I think the figures are right, but will check. I think running ANALYZE on your database Analyze was done, but will double-check. RBS On Tue, Jul 12, 2011 at 8:59 PM, Pavel Ivanov paiva...@gmail.com wrote: Now what surprises me is that this optimization is not done automatically by SQLite. I suppose I just over estimate the capabilities of the SQLite plan generator. Or, would this be something that could be improved? It's very non-obvious optimization and I think other type of optimization will give much better result in your case. To understand the optimizer's behavior look at numbers it shows you: it thinks that scanning the whole ENTRY_ATTRIBUTES table will read about 54855 rows. And you say that it has much more rows. That's why optimizer selects sub-optimal plan. I think running ANALYZE on your database should fix selected plans and even first query will run much faster. Pavel On Tue, Jul 12, 2011 at 3:39 PM, Bart Smissaert bart.smissa...@gmail.com wrote: Joining a large table (ENTRY_ATTRIBUTES) and a small table (BPNewENTRY) and putting the resulting records in a third table, BP3. Large table may have a few million records and small table a few hundred records. The join field is called ENTRY_ID in both tables and this has a non-unique index in the large table and is the integer primary key in the small table. Data type is integer in both these fields. This is the SQL: INSERT OR REPLACE INTO BP3 (ENTRY_ID, NUMERIC_VALUE, UNIT) SELECT EA.ENTRY_ID, EA.NUMERIC_VALUE, EA.TYPE_SPECIFIC_INFO FROM ENTRY_ATTRIBUTES EA INNER JOIN BPNewENTRY E ON (EA.ENTRY_ID = E.ENTRY_ID) and this is the generated query plan for that: 0--0--0--SCAN TABLE ENTRY_ATTRIBUTES AS EA (~54855 rows) -- 0--1--1--SEARCH TABLE BPNewENTRY AS E USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) Now I can speed up the query a lot by putting a where clause in with the min(ENTRY_ID) of the small table: INSERT OR REPLACE INTO BP3 (ENTRY_ID, NUMERIC_VALUE, UNIT) SELECT EA.ENTRY_ID, EA.NUMERIC_VALUE, EA.TYPE_SPECIFIC_INFO FROM ENTRY_ATTRIBUTES EA INNER JOIN BPNewENTRY E ON (EA.ENTRY_ID = E.ENTRY_ID) WHERE EA.ENTRY_ID = 4262936 and that will have this query plan: 0--0--0--SEARCH TABLE ENTRY_ATTRIBUTES AS EA USING INDEX IDX_ENTRY_ATTRIBUTES_ENTRY_ID (ENTRY_ID?) (~18102 rows) -- 0--1--1--SEARCH TABLE BPNewENTRY AS E USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) Getting this min(ENTRY_ID) is done in a separate query and because it is done on the small table it is very fast. Now what surprises me is that this optimization is not done automatically by SQLite. I suppose I just over estimate the capabilities of the SQLite plan generator. Or, would this be something that could be improved? I am using SQLite version 3.7.5. RBS ___ 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] surprising missing query optimization
Have checked and missing analyze is indeed not the cause of this difference in query speed. RBS On Tue, Jul 12, 2011 at 9:06 PM, Bart Smissaert bart.smissa...@gmail.com wrote: it thinks that scanning the whole ENTRY_ATTRIBUTES table will read about 54855 rows. And you say that it has much more rows. This particular database has less rows, the millions I mentioned are in a different database. I think the figures are right, but will check. I think running ANALYZE on your database Analyze was done, but will double-check. RBS On Tue, Jul 12, 2011 at 8:59 PM, Pavel Ivanov paiva...@gmail.com wrote: Now what surprises me is that this optimization is not done automatically by SQLite. I suppose I just over estimate the capabilities of the SQLite plan generator. Or, would this be something that could be improved? It's very non-obvious optimization and I think other type of optimization will give much better result in your case. To understand the optimizer's behavior look at numbers it shows you: it thinks that scanning the whole ENTRY_ATTRIBUTES table will read about 54855 rows. And you say that it has much more rows. That's why optimizer selects sub-optimal plan. I think running ANALYZE on your database should fix selected plans and even first query will run much faster. Pavel On Tue, Jul 12, 2011 at 3:39 PM, Bart Smissaert bart.smissa...@gmail.com wrote: Joining a large table (ENTRY_ATTRIBUTES) and a small table (BPNewENTRY) and putting the resulting records in a third table, BP3. Large table may have a few million records and small table a few hundred records. The join field is called ENTRY_ID in both tables and this has a non-unique index in the large table and is the integer primary key in the small table. Data type is integer in both these fields. This is the SQL: INSERT OR REPLACE INTO BP3 (ENTRY_ID, NUMERIC_VALUE, UNIT) SELECT EA.ENTRY_ID, EA.NUMERIC_VALUE, EA.TYPE_SPECIFIC_INFO FROM ENTRY_ATTRIBUTES EA INNER JOIN BPNewENTRY E ON (EA.ENTRY_ID = E.ENTRY_ID) and this is the generated query plan for that: 0--0--0--SCAN TABLE ENTRY_ATTRIBUTES AS EA (~54855 rows) -- 0--1--1--SEARCH TABLE BPNewENTRY AS E USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) Now I can speed up the query a lot by putting a where clause in with the min(ENTRY_ID) of the small table: INSERT OR REPLACE INTO BP3 (ENTRY_ID, NUMERIC_VALUE, UNIT) SELECT EA.ENTRY_ID, EA.NUMERIC_VALUE, EA.TYPE_SPECIFIC_INFO FROM ENTRY_ATTRIBUTES EA INNER JOIN BPNewENTRY E ON (EA.ENTRY_ID = E.ENTRY_ID) WHERE EA.ENTRY_ID = 4262936 and that will have this query plan: 0--0--0--SEARCH TABLE ENTRY_ATTRIBUTES AS EA USING INDEX IDX_ENTRY_ATTRIBUTES_ENTRY_ID (ENTRY_ID?) (~18102 rows) -- 0--1--1--SEARCH TABLE BPNewENTRY AS E USING INTEGER PRIMARY KEY (rowid=?) (~1 rows) Getting this min(ENTRY_ID) is done in a separate query and because it is done on the small table it is very fast. Now what surprises me is that this optimization is not done automatically by SQLite. I suppose I just over estimate the capabilities of the SQLite plan generator. Or, would this be something that could be improved? I am using SQLite version 3.7.5. RBS ___ 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] surprising missing query optimization
Try ON (EA.ENTRY_ID = +E.ENTRY_ID) Yes, that works indeed nicely. Thanks for that. I keep forgetting these non-standard SQL tricks. RBS On Tue, Jul 12, 2011 at 9:28 PM, Igor Tandetnik itandet...@mvps.org wrote: On 7/12/2011 3:39 PM, Bart Smissaert wrote: Joining a large table (ENTRY_ATTRIBUTES) and a small table (BPNewENTRY) and putting the resulting records in a third table, BP3. Large table may have a few million records and small table a few hundred records. The join field is called ENTRY_ID in both tables and this has a non-unique index in the large table and is the integer primary key in the small table. Data type is integer in both these fields. This is the SQL: INSERT OR REPLACE INTO BP3 (ENTRY_ID, NUMERIC_VALUE, UNIT) SELECT EA.ENTRY_ID, EA.NUMERIC_VALUE, EA.TYPE_SPECIFIC_INFO FROM ENTRY_ATTRIBUTES EA INNER JOIN BPNewENTRY E ON (EA.ENTRY_ID = E.ENTRY_ID) Try ON (EA.ENTRY_ID = +E.ENTRY_ID) . The unary plus should suppress the index on BPNewENTRY, and force SQLite to turn the execution around: scan BPNewENTRY and look up in ENTRY_ATTRIBUTES using index. -- 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
[sqlite] how to do this query?
SQLite objects against this SQL, particularly the first t1 after xxx delete from xxx t1 where not t1.entry_id in(select t2.entry_id from xxx t2 where t1.patient_id = t2.patient_id order by t2.start_date desc limit 1) How could I achieve this with a different syntax? RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to do this query?
Thanks, that works indeed nicely. RBS On Thu, Mar 24, 2011 at 7:12 PM, Jim Morris jmor...@bearriver.com wrote: A simple restatement should work: delete from xxx where entry_id in (select t1.entry_id from xxx t1 where not t1.entry_id in(select t2.entry_id from xxx t2 where t1.patient_id = t2.patient_id order by t2.start_date desc limit 1)) On 3/24/2011 12:00 PM, Bart Smissaert wrote: delete from xxx t1 where not t1.entry_id in(select t2.entry_id from xxx t2 where t1.patient_id = t2.patient_id order by t2.start_date desc limit 1) ___ 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 do this query?
Couldn't get this to work yet. What would be the full SQL, including the order by clause? RBS On Thu, Mar 24, 2011 at 7:13 PM, Igor Tandetnik itandet...@mvps.org wrote: On 3/24/2011 3:00 PM, Bart Smissaert wrote: SQLite objects against this SQL, particularly the first t1 after xxx delete from xxx t1 where not t1.entry_id in(select t2.entry_id from xxx t2 where t1.patient_id = t2.patient_id order by t2.start_date desc limit 1) delete from xxx where entry_id not in ( select entry_id from xxx where xxx.patient_id = patient_id); xxx. prefix resolves to the first mention of xxx in the query, which happens to be DELETE FROM clause. It's a bit scary, but it works. Unfortunately, SQL syntax as implemented by SQLite doesn't allow an alias in DELETE FROM. -- 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] how to do this query?
Deletes all but the most recent entry for each patient. Is this what you are after? Yes, that is exactly it. delete from xxx where entry_id not in ( select entry_id from xxx where xxx.patient_id = patient_id order by start_date desc limit 1); This deletes all but one record. It should be a few hundred out of a few thousand. RBS On Thu, Mar 24, 2011 at 7:37 PM, Igor Tandetnik itandet...@mvps.org wrote: On 3/24/2011 3:32 PM, Bart Smissaert wrote: Couldn't get this to work yet. What would be the full SQL, including the order by clause? delete from xxx where entry_id not in ( select entry_id from xxx where xxx.patient_id = patient_id order by start_date desc limit 1); Deletes all but the most recent entry for each patient. Is this what you are after? -- 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] In-memory database with persistent storage
through a version of dijkstra's routing algorithm Just out of interest, what data is this working on? RBS On Tue, Mar 22, 2011 at 7:25 AM, Amit Chaudhuri amit.k.chaudh...@gmail.com wrote: [Not at all expert in sqlite but here's a practical example of speed up using :memory: and perhaps a slightly different strategy for getting at the persistent data.] I use sqlite3 with Qt4 / C++ for an application which reads in an undirected graph and then chunks through a version of dijkstra's routing algorithm. A colleague runs this on his machine and it takes all night on a large network running on a database on disk. On my own machine which is more powerful it probably runs a lot faster but still takes a couple of hours plus. Changing to an in memory database, reading data in and processing in memory brings the run time down to a couple of minutes. So yes - running in memory can be much quicker. At the end of the run I attach an on disk database and copy out the tables I need to save using create table select . A On Mon, Mar 21, 2011 at 1:13 PM, Simon Friis simo...@gmail.com wrote: I know how to create a database that exists only in memory by using the :memory: filename. This however, creates a new database every time and it can not be saved. Is is possible to make SQLite load a database file into memory and then save it back to the file again when the connection to the database is closed? Would it improve speed? - paldepind ___ 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] calculate difference of two times and show the result as double value
As you are using Acccess maybe you are using Olaf Schmidt's VB wrapper
dhRichclient3 and in that case you do things like this:
select
(cast(left$('16:30:00', 2) as integer) +
cast(mid$('16:30:00', 4, 2) as real) / 60) -
(cast(left$('08:00:00', 2) as integer) +
cast(mid$('08:00:00', 4, 2) as real) / 60)
Native SQLite with Julianday etc. will probably be quicker.
RBS
On Mon, Jan 10, 2011 at 7:42 AM, Sven tabaluga...@yahoo.de wrote:
Dear SQLite users,
I've created a Microsoft Access 2003 database with three complex queries
written in pure SQL language. The function of the database is to
document and calculate the daily work-time.
Today I want to convert this database to SQLite but I have problems with
my written queries: How can I calculate the difference of two times and
show the result as double value?
For more information I'll include one example: One table hold the two
times (start_time, end_time) and the calculated result (shown in the
table as 'difference'):
[code]
start_time | end_time | difference
08:00:00 | 16:30:00 | 8.5
[/code]
The result of the difference between the two times (start_time,
end_time) shown in the table above as 'difference' will be calculated
with the following SQL-query code within Microsoft Access 2003:
[code]
( [end_time] - [start_time] ) * 24
[/code]
and returns the following data '8.5'. While using the following query
code within SQLite:
[code]
select time(end_time) - time(start_time)
[/code]
I only get the following data returned: '8' (but not '8.5' as calculated
with SQL).
So currently I don't know what to do next to solve my problem and
perhaps all the other users of this SQLite-users mailing list could help
me out.
best regards
___
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] union all with limit
Trying to run this SQL: select patient_id from table1 where age = 50 limit 6 union all select patient_id from table1 where age = 60 limit 4 But it fails due to the limit clause before the union. Would there be a way round this? RBS ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] union all with limit
Thanks, I tried that and it gives no error, but only gives the first lot, not the bit after the union all. RBS On Wed, Nov 24, 2010 at 10:27 AM, Swithun Crowe swit...@swithun.servebeer.com wrote: Hello BS select BS patient_id BS from BS table1 BS where BS age = 50 BS limit 6 BS union all BS select BS patient_id BS from BS table1 BS where BS age = 60 BS limit 4 You might want to wrap the two selects with limits inside subqueries: select patientID from ( select patientID from table1 where age = 50 limit 6 ) union all select patientID from ( select patientID from table1 where age = 60 limit 4 ); Swithun. ___ 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] union all with limit
Thanks, that one works indeed and will use that. RBS On Wed, Nov 24, 2010 at 11:08 AM, Swithun Crowe swit...@swithun.servebeer.com wrote: Hello BS Thanks, I tried that and it gives no error, but only gives the first BS lot, not the bit after the union all. Ah. I hadn't tried with data. I don't know why the LIMIT affects the UNION ALL, when it is buried in a subquery. It doesn't affect a UNION (no ALL). In your real data and query, can a patient have more than one age? If not, then UNION and UNION ALL would produce the same results. sqlite create table table1 (patientID int, age int); sqlite insert into table1 (patientID, age) values (1, 50); sqlite insert into table1 (patientID, age) values (2, 50); sqlite insert into table1 (patientID, age) values (3, 50); sqlite insert into table1 (patientID, age) values (4, 50); sqlite insert into table1 (patientID, age) values (5, 50); sqlite insert into table1 (patientID, age) values (6, 50); sqlite insert into table1 (patientID, age) values (7, 50); sqlite insert into table1 (patientID, age) values (8, 60); sqlite insert into table1 (patientID, age) values (9, 60); sqlite insert into table1 (patientID, age) values (10, 60); sqlite insert into table1 (patientID, age) values (11, 60); sqlite insert into table1 (patientID, age) values (12, 60); This seems to work: select patientID from table1 where patientID in (select patientID from table1 where age=50 limit 6) union all select patientID from table1 where patientID in(select patientID from table1 where age=60 limit 4); 1 2 3 4 5 6 8 9 10 11 Swithun. ___ 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] Simple SQL question?
That is a strange construction and for now I haven't got it to work yet in my VB application. It does run though in Firefox SQLite manager. Maybe after all the SQL I came up with in the end wasn't that bad. RBS On Wed, Nov 17, 2010 at 12:09 AM, Igor Tandetnik itandet...@mvps.org wrote: Bart Smissaert bart.smissa...@gmail.com wrote: Have (simplified) a table like this: CREATE TABLE TABLE1( [PATIENT_ID] INTEGER PRIMARY KEY, [ADDRESS] TEXT, [DATE_OF_BIRTH] TEXT) DATE_OF_BIRTH is in the ISO8601 format -mm-dd Now I need a SQL to find the oldest patients living at all the different (unique) addresses, so this will be the patient with the lowest DATE_OF_BIRTH. I will need the PATIENT_ID of that patient and nil else. select (select PATIENT_ID from TABLE1 t1 where t1.ADDRESS = t2.ADDRESS order by DATE_OF_BIRTH limit 1) from (select distinct ADDRESS from TABLE1) t2; 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] Simple SQL question?
Tried your SQL, but it doesn't look right and didn't run. Will see if I can alter it. RBS On Tue, Nov 16, 2010 at 11:17 PM, Petite Abeille petite.abei...@gmail.com wrote: On Nov 16, 2010, at 11:55 PM, Bart Smissaert wrote: This seems to work fine, Then you are golden :) but I am not sure if this SQL is correct and if the results will always be correct and have a feeling that there must be a better construction. Any suggestions? Nothing very meaningful, but you could rewrite the 'in' clause as a 'join' to avoid all these concatenations, e.g.: select t1.patient_id from table1 t1 join ( select table1.address, min( table1.date_of_birth ) as date_of_birth from table1 group by table1.address ) as t2 join t2.address = t1.address and t2.date_of_birth = t1.date_of_birth ___ 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] Simple SQL question?
What do you suggest should be the full SQL then? RBS On Wed, Nov 17, 2010 at 8:16 AM, luuk34 luu...@gmail.com wrote: On 17-11-10 00:17, Petite Abeille wrote: select t1.patient_id from table1 t1 join ( select table1.address, min( table1.date_of_birth ) as date_of_birth from table1 group by table1.address ) as t2 join t2.address = t1.address 'join' should be: ON t2.address = t1.address and t2.date_of_birth = t1.date_of_birth ___ 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
