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 wrote: > I don't

[sqlite] Two different Order By in one statement

On Wed, Apr 1, 2015 at 2:43 AM, Igor Tandetnik wrote: > On 3/31/2015 7:50 PM, Bart Smissaert wrote: > >> Say I have a table with 3 fields. Depending on a value in field 1 (this >> value will be either 1 or 2) >> I want to do a different sort order sorting on fields 2 and 3

[sqlite] Two different Order By in one statement

Slavin wrote: > > On 1 Apr 2015, at 9:29am, Bart Smissaert wrote: > > > This needs to be sorted on column A asc > > Then when the value in A is 1 the second sort needs to be asc on column > B, > > but when the value in A is 2 then the second sort needs to be asc on

[sqlite] Two different Order By in one statement

Also, performance is very good with a compound index on emis_number, status, entry_date, significance. RBS On Wed, Apr 1, 2015 at 11:06 AM, Bart Smissaert wrote: > Thanks, that does work indeed. My actual real order is now this: > > ORDER BY emis_number asc, status desc, > (case s

[sqlite] TEXT columns with Excel/VBA

> and the SQLite query planner sometimes notes that length when considering data shape In what situations does that happen? RBS On Thu, Dec 3, 2015 at 12:29 AM, R Smith wrote: > > > On 2015/12/02 6:34 PM, Erwin Kalvelagen wrote: > >> Good morning. >> >> I wrote a little tool to dump certain

[sqlite] TEXT columns with Excel/VBA

So, it will be quite a rare occurrence then that this could be of any benefit. Still nice to know this. RBS On 3 Dec 2015 1:33 am, "Richard Hipp" wrote: > On 12/2/15, Bart Smissaert wrote: > >> and the SQLite query planner sometimes notes that length when > c

[sqlite] How to see SQLite debugging information

Having a problem where 2 UDF's in one SQL cause a crash in my application (Excel) originating from SQLite. This is on Windows 7 with the latest SQLite version. I have a sqlite3.dll compiled with these compiler options: -DSQLITE_DEBUG -DSQLITE_MEMDEBUG -DSQLITE_OMIT_LOOKASIDE I can run PRAGMA

[sqlite] How to see SQLite debugging information

with C added this to a std_call dll and I picked it up from that via a .tlb file and an ActiveX (VB6) dll. All working fine, except in that mentioned situation. RBS On Tue, Dec 8, 2015 at 10:09 PM, Richard Hipp wrote: > On 12/8/15, Bart Smissaert wrote: > > Having a problem where 2 U

[sqlite] How to see SQLite debugging information

> You did you manage to implement new UDFs if you are not a C programmer? I forgot to say that the actual callback functions (the actual code that performs the manipulations) is not in SQLite, but in that ActiveX dll. RBS On Tue, Dec 8, 2015 at 10:09 PM, Richard Hipp wrote: > On 12/8/15

[sqlite] How to see SQLite debugging information

an ActiveX dll that is then called from Excel VBA. RBS On Wed, Dec 9, 2015 at 12:16 AM, Simon Slavin wrote: > > On 8 Dec 2015, at 10:18pm, Bart Smissaert > wrote: > > >> on standard output > > But how this work in concrete steps? Does code have to be added to SQLite > &g

[sqlite] How to see SQLite debugging information

So, do I need to somehow re-direct this output to a text file? RBS On Wed, Dec 9, 2015 at 12:32 AM, Simon Slavin wrote: > > On 9 Dec 2015, at 12:30am, Bart Smissaert > wrote: > > > So, what/where is that standard output channel? > > This is on a Win7 machine. How do

[sqlite] How to see SQLite debugging information

Excel doesn't do much, all the real work is done by that ActiveX dll and I have full access to that as I coded that. Surely there must be simple way to dump debugging data to say a text file. RBS On Wed, Dec 9, 2015 at 12:37 AM, Simon Slavin wrote: > > On 9 Dec 2015, at 12:35am

[sqlite] How to see SQLite debugging information

sure how that works. Will ask somebody who knows these things. I do in fact have MS VS 2013, but no idea how to step through the code in debug mode. RBS On Wed, Dec 9, 2015 at 1:44 AM, Random Coder wrote: > On Tue, Dec 8, 2015 at 4:30 PM, Bart Smissaert > wrote: > > So, what/where is

[sqlite] How to see SQLite debugging information

> The standard output is what displays on your screen when you are in a DOS box. OK, thanks, will look into that. RBS On Wed, Dec 9, 2015 at 2:50 AM, Richard Hipp wrote: > On 12/8/15, Bart Smissaert wrote: > > So, what/where is that standard output channel? > > This is on a

[sqlite] How to see SQLite debugging information

That would be very useful and I take it wouldn't be difficult to add. RBS On Wed, Dec 9, 2015 at 7:53 AM, Dominique Devienne wrote: > On Wed, Dec 9, 2015 at 3:50 AM, Richard Hipp wrote: > > > On 12/8/15, Bart Smissaert wrote: > > > So, what/where is that standard outpu

[sqlite] How to see SQLite debugging information

If all else fails it would be no problem to write a little VB6 exe that calls the ActiveX dll. Not much code should be needed in that to reproduce that crash situation. Would something like the free DOSBox do the job? Will try later. RBS On Wed, Dec 9, 2015 at 12:12 PM, Dominique Devienne

[sqlite] How to see SQLite debugging information

ink you need a simple console app to call your ActiveX DLL, or find > some > other way. Windows GUI app and standard output do not play well together. > > Regards > David M Bennett FACS > > Andl - A New Database Language - andl.org > > > > On 12/8/15, Bart Smissaer

[sqlite] How to see SQLite debugging information

you should attempt to isolate it, for direct testing. Many > hours of many people's lives have been wasted attempting to indirectly > test things that have more than 1 unknown or potential source of error > in a chain. > > regards, > Adam DeVita > > > On Wed, Dec 9, 2015 at 1

[sqlite] Making data unique

This will show in sqlite_master like this: type name tbl_name rootpage sql --- index sqlite_autoindex_dataset_1 dataset 717 So, no SQL. Problem with this is that there is no way to see

[sqlite] Making data unique

Ah, yes they both work and tell me the indexed fields. Saves me some work parsing this out from the table create SQL. Thanks for that. RBS On Thu, Dec 10, 2015 at 10:19 AM, Simon Slavin wrote: > > On 10 Dec 2015, at 10:17am, Bart Smissaert > wrote: > > > This will show in

[sqlite] sqlite3_free needed when calling sqlite3_result_text ?

Not sure if I need to call sqlite3_free after running sqlite3_result_text or if sqlite3_free should be an argument (last one) in sqlite3_result_text. Currently I am using SQLITE_TRANSIENT as the last argument, so that is after the number of bytes, but have feeling I might be doing this wrong.

[sqlite] sqlite3_free needed when calling sqlite3_result_text ?

it? As an argument in sqlite3_result_text or after running sqlite3_result_text? Do I need to run it with a pointer? RBS On Mon, Dec 14, 2015 at 7:27 PM, Igor Tandetnik wrote: > On 12/14/2015 2:21 PM, Bart Smissaert wrote: > >> Not sure if I need to call sqlite3_free after running sqlite3

[sqlite] sqlite3_free needed when calling sqlite3_result_text ?

, SQLITE_TRANSIENT End Sub RBS On Mon, Dec 14, 2015 at 9:00 PM, Igor Tandetnik wrote: > On 12/14/2015 3:09 PM, Bart Smissaert wrote: > >> It could be either a pointer to sqlite3_value_text of sqlite3_value* >> > > No it can't be. sqlite3_result_text takes a ch

[sqlite] sqlite3_free needed when calling sqlite3_result_text ?

eed sqlite3_result_text16 instead. Will do some further testing. RBS On Tue, Dec 15, 2015 at 12:22 AM, Igor Tandetnik wrote: > On 12/14/2015 5:46 PM, Bart Smissaert wrote: > >> OK, thanks, will have to study this carefully. >> So, if I understand you well then the way I do it now I would

[sqlite] sqlite3_free needed when calling sqlite3_result_text ?

Maybe I shouldn't make Unicode strings but keep it all in UTF8. Not sure though how to get the position then of string2 in string1, lPos. RBS On Tue, Dec 15, 2015 at 12:42 AM, Bart Smissaert wrote: > Yes, str and str2 are Unicode string, 2 bytes per character. > lPos counts per cha

[sqlite] sqlite3_free needed when calling sqlite3_result_text ?

Dec 15, 2015 at 1:22 AM, Igor Tandetnik wrote: > On 12/14/2015 7:42 PM, Bart Smissaert wrote: > >> Yes, str and str2 are Unicode string, 2 bytes per character. >> lPos counts per character, not byte, so if the string in the database is >> abcde and I want to find the

[sqlite] sqlite3_free needed when calling sqlite3_result_text ?

: sqlite-users-bounces at mailinglists.sqlite.org [mailto: > sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Bart > Smissaert > Gesendet: Montag, 14. Dezember 2015 20:22 > An: General Discussion of SQLite Database > Betreff: [sqlite] sqlite3_free needed when calling sql

[sqlite] sqlite3_free needed when calling sqlite3_result_text ?

> like sqlite3_malloc() or sqlite3_mprinft() to produce the string. OK, I won't be doing that. > I guess NO So I will need to use SQLITE_TRANSIENT then? > You should know where the memory used to store the string in your own code comes from and how to deal with it. It will nearly always be a

[sqlite] sqlite3_free needed when calling sqlite3_result_text ?

low me to retain only part of the string. Or is there a way to do this with sqlite3_result_value? RBS On Mon, Dec 14, 2015 at 9:00 PM, Igor Tandetnik wrote: > On 12/14/2015 3:09 PM, Bart Smissaert wrote: > >> It could be either a pointer to sqlite3_value_text of sqlite3_value* >

[sqlite] sqlite3_free needed when calling sqlite3_result_text ?

> I have no idea how VB6 implements local variables Pure local variables (declared in the actual procedure) are on the stack as well in VB6. VB6 hides all these kind of details, so I never think about this/deal with this. RBS On Tue, Dec 15, 2015 at 9:33 AM, Hick Gunter wrote: > >Thanks for

[sqlite] sqlite3_free needed when calling sqlite3_result_text ?

Thanks, nice and simple and helpful advice. RBS On 15 Dec 2015 1:45 pm, "Richard Hipp" wrote: > On 12/15/15, Bart Smissaert wrote: > > So I will need to use SQLITE_TRANSIENT then? > > > > Yes. Always use SQLITE_TRANSIENT, at least initially. All the other &

[sqlite] Malloc getting segmentation fault in sqlite

I am interested in this as I have bug that I am sure is to do with some sort of memory problem. It only occurs when I run a procedure defined with sqlite3_create_function. This procedure is not in sqlite3.dll but in a VB6 ActiveX dll. I use the unaltered Windows sqlite3.dll. My question is if

[sqlite] Malloc getting segmentation fault in sqlite

don't need sqlite3_free, sqlite3_malloc or sqlite3_realloc, I take it. Given that I don't use theses it then likely that my problem is to do with a buffer overwrite? RBS On Thu, Dec 24, 2015 at 8:35 PM, Richard Hipp wrote: > On 12/24/15, Bart Smissaert wrote: > > My

[sqlite] Malloc getting segmentation fault in sqlite

, SQLITE_TRANSIENT where arrBytes1 is a local variable, a Byte array. This goes out of scope once the callback procedure finishes. Could that be a problem? RBS On Thu, Dec 24, 2015 at 10:17 PM, Simon Slavin wrote: > > On 24 Dec 2015, at 9:10pm, Bart Smissaert > wrote: > > >

[sqlite] Malloc getting segmentation fault in sqlite

oes sqlite3.dll actually do, directly running after the UDF? It should be the next sqlite3_step, but that doesn't appear in my debug log, so something goes wrong before that. Again, I will drop all this if it is deemed to be inappropriate as it has little to do with SQLite. Happy Xmas,RBS

[sqlite] Malloc getting segmentation fault in sqlite

conventions (cdecl versus stdcall). > > I'll try looking for my old VB6 SQLite integration code when I have some > spare cycles. > > Sent from my iPhone > > > On Dec 24, 2015, at 11:10 AM, Bart Smissaert > wrote: > > > > OK, thanks > > I don't u

[sqlite] Malloc getting segmentation fault in sqlite

:31 PM, Bart Smissaert wrote: > Thanks for clearing that up. > I know all this has very little to do with SQLite, but people using SQLite > with VB6 or VBA might be interested in this. > What puzzles me that all is perfectly fine when the SQL statement has only > one UDF in it, but

[sqlite] Malloc getting segmentation fault in sqlite

5:23pm, Bart Smissaert > wrote: > > >> all is perfectly fine when the SQL statement has only one UDF in it > > > > Just a correction on this. Have now also seen problems when there is only > > one UDF in the SQL statement. > > It is not entirely predictable, so s

[sqlite] Malloc getting segmentation fault in sqlite

QLITE_TRANSIENT > > But as it uses SQLITE_TRANSIENT, I understand SQLite will make local > copy > > and deal with it properly itself. > > > > All the other code is just copying data from SQLite to local VB6 > > variables. > > > > RBS > > > > > &g

[sqlite] Malloc getting segmentation fault in sqlite

; > Then you will know which line could be related to the problem. > > > -Original Message- > > From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users- > > bounces at mailinglists.sqlite.org] On Behalf Of Bart Smissaert > > Sent: Saturday, 26 De

[sqlite] GROUP BY with self join

Having problems with the following SQL: 2 tables, trying to count occurrence of field 2 and field 3 of table 1 in field 1 (only field) of table 2. Table 1 called ITEM with fields: NAME, DEFINITION1, DEFINITION2 all text fields. Values in NAME are all unique. Table 2 called DESCRIPTIONS with

[sqlite] GROUP BY with self join

, Igor Tandetnik wrote: > On 2/14/2015 11:32 AM, Bart Smissaert wrote: > >> SELECT I.ITEM_NAME, COUNT(D.ROWID), COUNT(D2.ROWID) FROM ITEMS I >> INNER JOIN DESCRIPTIONS D ON (INSTR(D.FULL_TEXT, I.DEFINITION1) > 0) >> INNER JOIN DESCRIPTIONS D2 ON (INSTR(D2.FULL_TEXT, I

[sqlite] GROUP BY with self join

ems join Descriptions group by NAME Gives integer values, so no decimals. RBS On Sat, Feb 14, 2015 at 4:49 PM, Igor Tandetnik wrote: > On 2/14/2015 11:32 AM, Bart Smissaert wrote: > >> SELECT I.ITEM_NAME, COUNT(D.ROWID), COUNT(D2.ROWID) FROM ITEMS I >> INNER JOIN DESCRIPTIONS

[sqlite] GROUP BY with self join

Looked in the documentation and the answer seems to use total instead of sum. This is for the ratio field, not the 2 count fields. Looks all sorted now and thanks again. RBS On Sat, Feb 14, 2015 at 5:13 PM, Bart Smissaert wrote: > One more thing. How would I get the ratio of the 2 counts,

[sqlite] Can this be sorted?

Have the following full SQL: SELECT DS.DRUG_NAME AS DRUG_NAME, SUM(INSTR(M.ASSOCIATED_TEXT, DS.SENSITIVE_STRING) > 0) AS SENSITIVE, SUM(INSTR(M.ASSOCIATED_TEXT, DS.RESISTANT_STRING) > 0) AS RESISTANT, ROUND(TOTAL(INSTR(M.ASSOCIATED_TEXT, DS.SENSITIVE_STRING) > 0) / (TOTAL(INSTR(M.ASSOCIATED_TEXT,

[sqlite] Can this be sorted?

: order by field3 desc, field2 asc Trying to get the row with 47 at the bottom. RBS On Mon, Feb 16, 2015 at 2:58 AM, Igor Tandetnik wrote: > On 2/15/2015 6:54 PM, Bart Smissaert wrote: > >> Result is shown below. >> >>

[sqlite] Can this be sorted?

t- > Von: Bart Smissaert [mailto:bart.smissaert at gmail.com] > Gesendet: Montag, 16. Februar 2015 09:49 > An: General Discussion of SQLite Database > Betreff: Re: [sqlite] Can this be sorted? > > Sorry, it looked OK on my side, but I suppose some of the Excel formatting >

[sqlite] Can this be sorted?

Yes, both your suggestions work indeed and thank for that. I prefer the second one as it looks a bit neater and makes it more clear what is going on. RBS On Mon, Feb 16, 2015 at 10:23 AM, John McKown wrote: > On Mon, Feb 16, 2015 at 3:53 AM, Bart Smissaert > wrote: > > >

[sqlite] Can this be sorted?

> Nice to know my brainisnot totally rotted out yet Working well, better than mine. RBS On Mon, Feb 16, 2015 at 10:49 AM, John McKown wrote: > Thanks for testing & reporting back. Nice to know my brainisnot totally > rotted out yet ;-} > On Feb 16, 2015 4:41 AM, "Bar

[sqlite] sqlite3_column_count and sqlite3_data_count

Could somebody tell me what the difference is between these 2 functions? Couldn't work it out from the documentation and not been able to setup an example where they produce different results. RBS

[sqlite] sqlite3_column_count and sqlite3_data_count

OK, thanks for clearing that up. So there seems then little point then in using sqlite3_data_count? RBS On Thu, Feb 26, 2015 at 12:16 AM, Igor Tandetnik wrote: > On 2/25/2015 7:03 PM, Bart Smissaert wrote: > >> Could somebody tell me what the difference is between these

[sqlite] sqlite3_column_count and sqlite3_data_count

Well, sqlite3_column_count seems to have a clear advantage here in that you don't have to worry about being in a row or not. RBS On Thu, Feb 26, 2015 at 12:45 AM, Simon Slavin wrote: > > On 26 Feb 2015, at 12:41am, Bart Smissaert > wrote: > > > OK, thanks for clearing that

[sqlite] problem compiling std_call dll

Trying to compile a std_call dll to be used with VB6 and VBA. Using instructions and files from this website: https://sqliteforexcel.codeplex.com/ All works well and have added a few SQLite functions that weren't in the compiled dll as in the download section from that website. However having

[sqlite] CSV excel import

Moving data from Excel directly to SQLite seems the best option to me as all the values are clearly separated. No need for XML. An Excel sheet range can be directly converted to a variant array and from there it is simple and fast to move the data to SQLite. I have written an Excel add-in that

[sqlite] CSV excel import

Never considered that option but I think it will be a lot slower. Currently I work with these 2: https://sqliteforexcel.codeplex.com/ http://www.vbrichclient.com/#/en/About/ RBS On Fri, Jul 31, 2015 at 1:47 AM, Simon Slavin wrote: > > On 31 Jul 2015, at 1:32am, Bart Smissaert &

[sqlite] problem compiling std_call dll

at underscore) or you have failed to point it at a library. > > I'm afraid I'm not fluent in the windows tools to track this down and > don't have a system I can follow along with. But on Linux one might > use the nm utility to examine a contents of library files for symbols. > > Reg

[sqlite] problem compiling std_call dll

Thanks for clearing this up. I did wonder about this. Came across it when making a .tlb for those calls to the std_call dll. RBS On Mon, Mar 2, 2015 at 6:14 AM, Scott Robison wrote: > On Sun, Mar 1, 2015 at 10:55 PM, J Decker wrote: > > > On Sun, Mar 1, 2015 at 9:22 PM, Keith Medcalf >

[sqlite] How to code this Std_Call?

Been working with this project: https://sqliteforexcel.codeplex.com/ and further enhanced that SQLite3_StdCall.dll with more SQLite functions. As said before I don't know C, but can work things out by looking at other functions. Now stuck though on a complex one, sqlite3_create_function. In the

[sqlite] Messages sent not coming through

Posted a question to sqlite-users at mailinglists.sqlite.org yesterday morning, but can't see anything coming through. Posted this from GMail, all bang standard and not sure if what could be wrong. Any ideas? I will resend it. RBS

[sqlite] How to code this Std_Call?

Been working with this project: https://sqliteforexcel.codeplex.com/ and further enhanced that SQLite3_StdCall.dll with more SQLite functions. As said before I don't know C, but can work things out by looking at other functions. Now stuck though on a complex one, sqlite3_create_function. In the

[sqlite] How to code this Std_Call?

Thanks. I can see now that same message I sent a few minutes ago. Still can't see the first one though. No idea what could be going on here. RBS On Sun, Mar 15, 2015 at 1:26 PM, R.Smith wrote: > I have no answer for you (or rather, others here will have better > answers), but I can tell you

[sqlite] How to code this Std_Call?

I always get to see them, but sometimes a bit late. RBS On Sun, Mar 15, 2015 at 1:30 PM, Sean Dzafovic wrote: > On Sun, Mar 15, 2015 at 10:26 AM, R.Smith wrote: > > > I have no answer for you (or rather, others here will have better > > answers), but I can tell you that this question did in

[sqlite] result of sqlite3_finalize zero, but sqlite3_errcode produces (rightly) SQLITE_CONSTRAINT

Have a simple table with a primary integer key. Doing an insert (with no insert or ignore or insert or replace) with duplicate values for this primary integer key field produces zero on sqlite3_finalize, but 19 from sqlite3_errorcode. I thought that the result value of sqlite3_finalize also should

[sqlite] result of sqlite3_finalize zero, but sqlite3_errcode produces (rightly) SQLITE_CONSTRAINT

time. RBS On Tue, Mar 17, 2015 at 6:33 PM, Richard Hipp wrote: > On 3/17/15, Bart Smissaert wrote: > > Have a simple table with a primary integer key. > > Doing an insert (with no insert or ignore or insert or replace) with > > duplicate values > > for this prim

[sqlite] result of sqlite3_finalize zero, but sqlite3_errcode produces (rightly) SQLITE_CONSTRAINT

OK, but I can find out from sqlite3_errcode after the loop if there was an error, saving all the checks inside the loop. Would there be any harm from that? RBS On Tue, Mar 17, 2015 at 7:52 PM, Simon Slavin wrote: > > On 17 Mar 2015, at 7:39pm, Bart Smissaert > wrote: > > >

[sqlite] result of sqlite3_finalize zero, but sqlite3_errcode produces (rightly) SQLITE_CONSTRAINT

OK, thanks, I see now. Will do some timings, but as you guess the slowdown may be negligible. RBS On Tue, Mar 17, 2015 at 8:08 PM, Simon Slavin wrote: > > On 17 Mar 2015, at 8:00pm, Bart Smissaert > wrote: > > > OK, but I can find out from sqlite3_errc

[sqlite] What is the right order of the main SQLite functions?

I know this is basic and should be in the documentation, but it is not quite clear to me. Basically I have 3 type of procedures: 1. Getting values from a table. For this I have the following steps: (Open) Prepare Then in a loop: Step ColumnInt, ColumnDouble, ColumnText After the loop:

[sqlite] What is the right order of the main SQLite functions?

Thanks, that is very useful. Maybe this should be more clearly (and simple) in the documentation somewhere. RBS On Fri, Mar 20, 2015 at 12:23 AM, Igor Tandetnik wrote: > On 3/19/2015 7:48 PM, Bart Smissaert wrote: > >> I know this is basic and should be in the d

[sqlite] What is the right order of the main SQLite functions?

Thanks, all working beautifully now. RBS On Fri, Mar 20, 2015 at 1:27 AM, Simon Slavin wrote: > > On 19 Mar 2015, at 11:48pm, Bart Smissaert > wrote: > > > Is this all how it should be? > > Apart from the following, nothing I see looks like it will cause > proble

[sqlite] What is wrong with this simple SQL?

This SQL gives me the error ambiguous column name: emis_number: select g.gp_name, d.emis_number from DIABETIC_ISSUES_LAST d inner join patients p on(d.emis_number = p.emis_number) inner join gp_table g on(p.usual_gp_index_number = g.gp_id) where d.emis_number not in(select emis_number from

[sqlite] What is wrong with this simple SQL?

Sorry, that table did indeed not have a column named emis_number, my mistake. Still, the error message ambiguous column name doesn't seem quite right. Should that not also be no such column: emis_number? RBS On Sun, Mar 22, 2015 at 2:06 PM, Igor Tandetnik wrote: > On 3/22/2015 8:50 AM, B

[sqlite] What is wrong with this simple SQL?

the correct error message. > > Regards, Ketil > > On 22 March 2015 at 15:15, Bart Smissaert > wrote: > > Sorry, that table did indeed not have a column named emis_number, my > > mistake. > > Still, the error message ambiguous column name doesn't seem quite righ

[sqlite] What is wrong with this simple SQL?

My misunderstanding and see my reply to Ketil. RBS On Sun, Mar 22, 2015 at 2:47 PM, Igor Tandetnik wrote: > On 3/22/2015 10:15 AM, Bart Smissaert wrote: > >> Sorry, that table did indeed not have a column named emis_number, my >> mistake. >> Still, the error messa

[sqlite] What is wrong with this simple SQL?

OK, will remember that. Still, in this particular case it seems odd as there is only one column and one table in the sub-select. Learned something there. RBS On Sun, Mar 22, 2015 at 3:03 PM, Igor Tandetnik wrote: > On 3/22/2015 10:48 AM, Bart Smissaert wrote: > >> B

[sqlite] What is wrong with this simple SQL?

Well, select column A from table B, kind of implies that that column A can only come from table B. This is what most people would think I would guess. RBS On Sun, Mar 22, 2015 at 10:27 PM, Igor Tandetnik wrote: > On 3/22/2015 11:50 AM, Bart Smissaert wrote: > >> Still, in this par

[sqlite] ODBC

Best is to use the VB (6 and VBA) wrapper from Olaf Schmidt you can download here: http://www.vbrichclient.com/#/en/About/ Examples how to use are there as well. RBS On Wed, Mar 25, 2015 at 12:06 PM, Preston King wrote: > Hello, > > I recently installed the sqliteodbc, sqlite3 ODBC

[sqlite] Two different Order By in one statement

Say I have a table with 3 fields. Depending on a value in field 1 (this value will be either 1 or 2) I want to do a different sort order sorting on fields 2 and 3. This will be either order by field2 desc, field3 desc or field3 desc, field2 desc. I thought of a union, but doesn't allow this. Any

[sqlite] VBA Sqllite blob data

What do you mean with: into Excel? Into a cell in the worksheet, into a VBA variable? What code did you try? I never use blobs, but I don't think it should be a problem. RBS On Fri, May 8, 2015 at 8:15 PM, Preston King wrote: > Does anyone have an example of how to read sqlite blob records,

[sqlite] VBA Sqllite blob data

gt; From: sqlite-users-bounces at mailinglists.sqlite.org [mailto: > sqlite-users-bounces at mailinglists.sqlite.org] On Behalf Of Bart Smissaert > Sent: Friday, May 08, 2015 5:23 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] VBA Sqllite blob data > >

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

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 wrote: > [Not at all expert in sqlite but here's a practical example of speed up > using ":memory:" and

[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

Re: [sqlite] how to do this query?

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 >> t

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 fir

Re: [sqlite] how to do this query?

uld 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? > >

[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

Re: [sqlite] surprising missing query optimization

ll 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 tab

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 y

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: >> Joi

[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

Re: [sqlite] Why does analyze make this go faster?

tia...@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:

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.

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

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

Re: [sqlite] How to use SQLite as a data source in Excel (tables and pivottables)

ividual > 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

Re: [sqlite] How to use SQLite as a data source in Excel (tables and pivottables)

-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)

Re: [sqlite] How to use SQLite as a data source in Excel (tables and pivottables)

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. &

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

Re: [sqlite] How to use SQLite as a data source in Excel (tables and pivottables)

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

Re: [sqlite] How to use SQLite as a data source in Excel (tables and pivottables)

011 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 >

  1   2   3   4   5   6   >