Re: [sqlite] NOT LIKE

2009-10-12 Thread bartsmissaert
Try this instead: SELECT * FROM suppliers WHERE (NOT supplier_name LIKE '%blabla%'); RBS > Does anybody know wether the "NOT LIKE" condition such as > > SELECT * FROM suppliers > WHERE supplier_name NOT LIKE '%blabla%'; > > works in sqlite? > I used it in a query but it resulted in

Re: [sqlite] Comparing two tables?

2009-09-29 Thread bartsmissaert
What is the delta? RBS > Hi, > > > Have two tables structured exactly the same. Want to compare both of > them and get the delta. Been Googling for about an hour now and I see > tools that do this (maybe a freeware one I haven't found?) and was > looking for a solution that more meets the

Re: [sqlite] Force the use of a specified index?

2009-09-18 Thread bartsmissaert
I was trying to force the use of a multi-column index. Will have a better look and see what is going on here. For now I get best performance with a 2-stage approach with the use of a intermediate temp table. Will post the exact details of this later. RBS > If neither index individually offers a

Re: [sqlite] Force the use of a specified index?

2009-09-17 Thread bartsmissaert
Ah, thanks, that was the one and will give that a try. RBS > > On Sep 17, 2009, at 5:02 AM, Bart Smissaert wrote: > >> Is it possible to tell SQLite to use a specified index? >> I know you can use the + to excludes fields being used in an index, >> but this doesn't help me in this particular

Re: [sqlite] Why row is not found?

2009-06-05 Thread bartsmissaert
Maybe don't use varchar, but text instead when creating the table. RBS > I just posted the db on my website... there is one row and there > aren't invisible characters. > > Please note that the following query returns the exact row: > SELECT * FROM lo_user WHERE CAST(lo_name AS TEXT)='admin';

Re: [sqlite] Export the results from a select to excel

2009-03-23 Thread bartsmissaert
Simplest and maybe also quickest way is to do this with a VB(A) wrapper. I recommend Olaf Schmidt's free dhRichClient3.dll wrapper: www.datenhaus.de/Downloads/dhRichClient3.zip www.datenhaus.de/Downloads/dhRichClient3-Demo.zip RBS > Hello, > > what is the fastest way to do this with the C-api?

Re: [sqlite] drop table question ?

2009-02-03 Thread bartsmissaert
Run a loop in the code of your application and drop the tables. I don't think SQLite can do something like this. RBS > > hi > > simple question : How to drop all tables in my database that start , for > example, with X? > > table 1 is X1998 > table 2 is X8676 > table 3 is X2912 > ... > > thanx >

Re: [sqlite] What is a Group By, having clause?

2008-12-09 Thread bartsmissaert
http://www.w3schools.com/sql/default.asp RBS > Hi, > > can someone point me to some docs where the difference between a normal > "where"-clause and the "Group by", and "having" statements are being > explained? > > I donĀ“t quite understand what these are actually good for. > > Thanks, > > -- >

Re: [sqlite] best language match for SQLite?

2008-09-16 Thread bartsmissaert
> "And of course, Perl is always best. :)" Of course; but VB (VB6) is just a bit better. RBS > > "And of course, Perl is always best. :)" > > That's right brother! > Educate them , Educate them i say! > > P. > >> Date: Tue, 16 Sep 2008 11:13:27 -0500 >> From: [EMAIL PROTECTED] >> To:

Re: [sqlite] SQL question

2008-06-16 Thread bartsmissaert
update table set id = id + 1 WHERE id > 1 RBS > Sorry about this, but this is a SQL question and not a SQLite specific > question. > > Is there a way to increment a value in a table in-situ. Without > reading it, incrementing it, writing it? > > I need to insert an entry into a table, it has

Re: [sqlite] Join trouble

2008-02-11 Thread bartsmissaert
Did you try with an inner join with table aliases? so: from tblusers a inner join tblcompany b on (a.company = b.company) RBS > I am having serious trouble creating a join on my database. I have > tested it in other databases and it works so it must be a problem with > sqlite and the join??? I

Re: [sqlite] free excel-like COLORFUL gui for sqlite

2008-01-08 Thread bartsmissaert
input view without having the client worry about the details > (which > column is really what table, etc) and also to be able to color it as he > has > done to date. So, is there is a free gui frontend to sqlite that will do > it, or do I have to code one myself? > > Thanks! &

Re: [sqlite] free excel-like COLORFUL gui for sqlite

2008-01-08 Thread bartsmissaert
If it is so good then why would you want to use SQLite? Holiday data can't be that much, so I would think Excel can cope with that fine. If you really want to move the data from Excel to SQLite then you will need a VB wrapper. RBS > > I have a client who's using a colorful excel sheet as a

Re: [sqlite] Suggests for improving the SQLite website

2007-11-08 Thread bartsmissaert
What could be interesting is a listing of all the ways SQLite is used and for users to upload their details with a number of fixed fields like: short description, size of database, number of users, commercial/non-commercial, platform/OS, programming language, wrapper, satisfaction with the

Re: [sqlite] How use 'uSQLiteServer' wrapper with Visual Basic .NET ???

2007-11-06 Thread bartsmissaert
Sorry, I don't use .NET and couldn't tell you. I am sure somebody will. RBS > Hello RBS, > thanks for your answer. I'am a newbie in "SQLite". > I need a "SQlite" server and i read that the "uSQLite" can do this, and > i'am > try to work with this. But i need something more in VB .NET ? > Or

Re: [sqlite] How use 'uSQLiteServer' wrapper with Visual Basic .NET ???

2007-11-06 Thread bartsmissaert
What wrapper are you using now that works good and what is the "uSQLiteServer" wrapper? RBS > Hello friends, > i use this code to work with a "SQLite" database and works good : > > Dim cn As New SQLiteConnection("Data Source=C:\sqlite-3_5_1\tst.db") > Dim dr As SQLiteDataReader > Dim cmd As New

Re: [sqlite] New Operator Support

2007-09-07 Thread bartsmissaert
It still might be useful to explain what the ~ should do. RBS > Hi, > > Its to get involved in the development of sqlite.If possible > move to contrib section.a dream. > > regards > ragha > > ** > This email

Re: [sqlite] New Operator Support

2007-09-07 Thread bartsmissaert
Couldn't tell you as I don't know C, but what will the ~ do? Can't the same be done with the available operators? RBS > > Hi, > > Just to get more hands on Sqlite i want to > write a custom operator. Pls suggest how i can do it. > > For example > select * from tablex where column1 ~ '123'; > > I

RE: [sqlite] SQLite or MS Access

2007-09-07 Thread bartsmissaert
Transactions are the main thing to speed this up but there are others such as the various Pragma settings. If you search in this group for slow insert you will find them. RBS > The problem was transactions > > Thanks all > > -Message d'origine- > De : Andre du Plessis [mailto:[EMAIL

Re: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index?

2007-08-06 Thread bartsmissaert
Now you can take comparisons too far and then they won't be useful anymore. If you want I will explain the setup here and you will see. RBS > On 8/6/07, [EMAIL PROTECTED] > <[EMAIL PROTECTED]> wrote: >> Yes, sure if it fails you might get wet, but I would try the few planks >> I >> got and see

Re: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index?

2007-08-06 Thread bartsmissaert
Very true, so I will give up on you then. RBS > You can lead a horse to water but you cannot make it drink. > > [EMAIL PROTECTED] wrote: >> Yes, sure if it fails you might get wet, but I would try the few planks >> I >> got and see which one fits best and not bother with a mathematical >>

Re: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index?

2007-08-06 Thread bartsmissaert
Yes, sure if it fails you might get wet, but I would try the few planks I got and see which one fits best and not bother with a mathematical model. All a bit academic as you don't know the situation I am dealing with. RBS > Absolutely. Big bridge or small bridge, if it fails you fall in the >

Re: [sqlite] Re: Re: inner join

2007-07-16 Thread bartsmissaert
Thanks for that. I looked there, but overlooked it. RBS > RB Smissaert <[EMAIL PROTECTED]> > wrote: >> Where could I find documentation about coalesce? > > http://sqlite.org/lang_expr.html > > Near the bottom where all the built-in functions are described. > > Igor Tandetnik > >

Re: [sqlite] SQL query help (mutiple joins)

2007-05-01 Thread bartsmissaert
Yes, you are right. Good thing the OP found it himself. RBS > actually > > SELECT COUNT(DISTINCT ... > > On 5/1/07, [EMAIL PROTECTED] > <[EMAIL PROTECTED]> wrote: >> It will be as the below query, but replace: >> distinct p.* >> with: >> count(p.ID) >> >> RBS >> >> >> Allan, Mark wrote: >> >> >

RE: [sqlite] SQL query help (mutiple joins)

2007-05-01 Thread bartsmissaert
It will be as the below query, but replace: distinct p.* with: count(p.ID) RBS >> Allan, Mark wrote: >> > What I want is Joe Blogs just the once. >> > >> > >> Mark, >> >> Then try adding distinct like this: >> >> select distinct p.* >> from PatientsTable as p >> join ExaminationsTable as e on

RE: [sqlite] SQL query help (mutiple joins)

2007-05-01 Thread bartsmissaert
Try instead: select distinct etc. will only work if your select only involves the patient table. RBS > Hi, > > Thanks for your quick replies. I have tried this method but however I am > getting a row returned for each entry in ForcedSpiroTable or > RelaxedSpiroTable that matches the search

Re: [sqlite] SQL query help (mutiple joins)

2007-05-01 Thread bartsmissaert
select * from PatientsTable P inner join ForcedSpiroTable F on (P.ID = F.ID) inner join RelaxedSpiroTable R on (P.ID = R.ID) where F.EVC > 2.0 and R.FVC > 2.0 RBS > Hi, > > Can anyone offer any help with the following SQL query? > > I have a database that looks something like the following:- >

Re: [sqlite] Optimize a query

2007-04-17 Thread bartsmissaert
Interesting and thanks for that tip. Is there a performance penalty from structuring the query like that? I take it that there will be. RBS > Martin Pelletier <[EMAIL PROTECTED]> wrote: >> This is news to me. Why can't SQlite use more than one index? >> > > It can. You just have to tell it to

Re: [sqlite] Version 3.3.14

2007-04-02 Thread bartsmissaert
Thanks; that sounds like a very worthwile update. Will let you know what the performance gain is once I have my wrappers sorted. RBS > SQLite version 3.3.14 is now available on the SQLite website > >http://www.sqlite.org/ > > Version 3.3.14 focuses on performance improvements. There > have

Re: [sqlite] Questions on views

2007-03-27 Thread bartsmissaert
> at least for anyone who has worked with > assembly level programming That are not many then, but thanks and will have a look at the documentation you mentioned. RBS > [EMAIL PROTECTED] wrote: >> Is there any documentation about how to analyze the results of EXPLAIN? >> Or even better is there

Re: [sqlite] Questions on views

2007-03-27 Thread bartsmissaert
Is there any documentation about how to analyze the results of EXPLAIN? Or even better is there a utility that could analyze (at least to some extent) the results for you? RBS > Joe Wilson wrote: >> >> "EXPLAIN SELECT ..." is also a good way to find out what the queries are >> doing >> if you're

Re: [sqlite] Any way to do this faster?

2007-03-26 Thread bartsmissaert
> also, as Dennis said .. unless you have > some other reason for that index on > Table B, get rid of it. I will need an index on that field later, but I could drop it prior to the delete and create a new one after. Thinking about it I am not sure in any case of the value of an index after

RE: [sqlite] Any way to do this faster?

2007-03-26 Thread bartsmissaert
Thanks, will try that and report back. RBS > I'm not sure if SQLite support this syntax, but try following statement, > > Delete from tableB b > Where not exist ( select 'x' > from tableA a > where a.id = b.id ) > > -Original Message- > From: Dennis

Re: [sqlite] Any way to do this faster?

2007-03-26 Thread bartsmissaert
Thanks Dennis, will try that when I get chance (at work now) and will report back about the difference it made. RBS > RB Smissaert wrote: >> Simplified I have the following situation: >> >> 2 tables, tableA and tableB both with an integer field, called ID, >> holding >> unique integer numbers in

Re: [sqlite] Question about speed of CASE WHEN

2007-03-20 Thread bartsmissaert
> Or is it just a case of needing to view > the final data in a user friendly environment? That is it. They can format, sort, print etc. in Excel. They won't even have to know about SQLite. The SQLite database file can safely be deleted and it will still work the same. RBS > Hi RBS, > >> I use

Re: [sqlite] x000 line queries - only for benchmarks?

2007-02-08 Thread bartsmissaert
There is no problem running queries of whatever length, so I could do benchmarks fine, it is just that VBA has a problem with array elements holding over 1823 characters. RBS > [EMAIL PROTECTED] wrote: >> In my (commercial) app I regularly have queries with over 1000 >> characters. >> Not over

Re: [sqlite] x000 line queries - only for benchmarks?

2007-02-08 Thread bartsmissaert
In my (commercial) app I regularly have queries with over 1000 characters. Not over 1000 lines though. As VBA (not sure now about VB6) has a problem with array elements having over 1823 characters I had to truncate my SQL logging routine. RBS > I've seen some longish SQL queries posted to this

RE: [sqlite] How do I know sqlite_get_table is finished

2007-02-02 Thread bartsmissaert
Thanks for that, will have a look at the altered C code, but not sure yet if I can compile. Just have never done it yet. As I posted earlier I think I have found the trouble. Due to a simple typo I enclosed the queries in that table SQL in double quotes, where it should have been single quotes.

RE: [sqlite] How do I know sqlite_get_table is finished

2007-02-02 Thread bartsmissaert
Thanks for the offer. I think I just figured what the trouble was. The query in that table should be enclosed in single quotes as it is a TEXT field. Instead though I had enclosed it in double quotes, so I did chr(34) & strQuery & chr(34) instead of: chr(39) & strQuery & chr(39) Had to go to

RE: [sqlite] How do I know sqlite_get_table is finished

2007-02-02 Thread bartsmissaert
Thanks for looking at that. The crash can happen at the actual call to sqlite_get_table or it can happen when I assign the resulting array to the wrapper function, so when I do: GetFromDB = arr Yes, I would be interested in making a boolean (optional) argument in sqlite_get_table that can leave

Re: AW: [sqlite] sqlite performance, locking & threading

2007-01-05 Thread bartsmissaert
> not to spark a debate Although the majority of this thread is as clear as mud, it is still interesting, even for simple VBA programmers like me that have no chance (maybe via a VB6 ActiveX exe) to use multi-threading. RBS > Emerson, one posts to a forum like this to get help and other ideas,

Re: [sqlite] Version 3.3.9

2007-01-04 Thread bartsmissaert
> I'm curious to know what your situation is. I am doing commercial software that provides an Excel frontend (.xla with some VB6 ActiveX dll's) for a medical Interbase database. I am in the process of doing a major re-write, moving away from array manipulations and text file SQL to doing all the

Re: [sqlite] Version 3.3.9

2007-01-04 Thread bartsmissaert
Looks it has got faster in my particular situation. Thanks for that. RBS > SQLite version 3.3.9 is now available from the SQLite website > >http://www.sqlite.org/ >http://www.sqlite.org/download.html > > The more important change is a fix for an obscure bug that can > lead to database

Re: [sqlite] Re: UPDATE multiple fields

2006-12-27 Thread bartsmissaert
Actually it is even better as I can combine all the UPDATE statements both from the inner and the outer loop and run only one UPDATE, so it is more than the number of fields times as fast, although not quite i times c times as fast. Thanks again! RBS > Thanks, that was very helpful. In fact it

Re: [sqlite] Re: UPDATE multiple fields

2006-12-27 Thread bartsmissaert
Thanks, that was very helpful. In fact it looks it as many times faster as the number of fields to be done, so in my particular case 5 times faster! Maybe somebody who knows the inner workings of SQLite could explain why this is. Will see if I can apply this to some other places in my app. RBS

[sqlite] UPDATE multiple fields

2006-12-27 Thread bartsmissaert
What would the query construction be in SQLite to update multiple fields? I have tried all sort of syntaxes, but sofar no success yet. I now have to do it in a loop, but that is a bit slow: For i = 2 To lMaxEntryCount For c = 1 To UBound(arrFields) strUPDATE = arrFields2(c) & "_E" & i strSQL =

Re: [sqlite] Mathematical 'power' operator?

2006-12-21 Thread bartsmissaert
Maybe a dumb queston, but: As it looks it is easiest to work with SQLite from Tcl, is it possible to code in Tcl and call that from VB/VBA? RBS > Well put. If Sqlite were turned into a junior Oracle, DB2 or PostgreSQL > then someone else would have to create a new Sqlite to handle the >

Re: [sqlite] Calculate years and months difference with Julian Date?

2006-12-18 Thread bartsmissaert
Thanks for clarifying that. I think this should settle it now. In fact when that format is used Excel will pick it up correctly as well, so that avoids the trouble of my previous method to convert my Interbase mmdd dates to Excel integer dates via the Julianday function. RBS > [EMAIL

Re: [sqlite] Calculate years and months difference with Julian Date?

2006-12-18 Thread bartsmissaert
Yes, so that may settle it then. RBS > [EMAIL PROTECTED] wrote: >> I think it was the date format in the SQLite table that wasn't right. >> I had this as dd-mm- >> >> Doing SELECT date('2006-02-16','+1 month') >> gives me indeed correctly 16 Feb 2006 >> >> So does the format have to be

Re: [sqlite] Calculate years and months difference with Julian Date?

2006-12-18 Thread bartsmissaert
I think it was the date format in the SQLite table that wasn't right. I had this as dd-mm- Doing SELECT date('2006-02-16','+1 month') gives me indeed correctly 16 Feb 2006 So does the format have to be -mm-dd ? RBS > [EMAIL PROTECTED] wrote: >> Unfortunately, it looks that just adds

RE: [sqlite] Calculate years and months difference with Julian Date?

2006-12-18 Thread bartsmissaert
Unfortunately, it looks that just adds 30 days, so 16 Feb 06 will give 18 March 06 etc. RBS >> If you have two dates A and B, you can ask the question: > >> Is A more than one month after B like this: > >> SELECT julianday(A,'+1 month')>B ... > > > Thanks; that looks exactly what I need. > >

[sqlite] GROUP BY in SQLite

2006-12-14 Thread bartsmissaert
Just wondering about the implementation of GROUP BY in SQLite. It looks I can do: select field1, field2, field3 from table group by field1 and I will get the last row in the field1 group. This is fine and I can use that, but I thought that the SQL standard was that all non-aggregate fields

RE: [sqlite] Transpose table

2006-12-14 Thread bartsmissaert
Have tested this now and it seems to work fine. Faster as well than my old method. RBS > Hi Denis, > > Yes, it is a one-off action and the only purpose is to present the data > into and Excel sheet in a more readable way. > I had done your suggestion in VBA, but I thought it was a bit slow >

RE: [sqlite] Transpose table

2006-12-14 Thread bartsmissaert
Hi Denis, Yes, it is a one-off action and the only purpose is to present the data into and Excel sheet in a more readable way. I had done your suggestion in VBA, but I thought it was a bit slow and wondered if there was a better way. I have just found a possible way to do this and maybe it is

Re: [sqlite] faster SELECT time on second run

2006-12-11 Thread bartsmissaert
> using fread Not sure how to do that with my VB wrapper. At work now, so can't see, but will give it a try. RBS > RB Smissaert wrote: >> Can't think of any code for that :) >> >> >> >> >> you could prefetch data if you can predict what users will search for >> ;) >> >> > Actually, you don't

Re: [sqlite] Interbase to SQLite

2006-12-07 Thread bartsmissaert
Hi Dennis, I have installed the whole lot and it went very smoothly. Now though I am not sure what the next step is. Were you saying there was a driver to connect to both Interbase and SQLite like an ODBC driver? Or did you have something else in mind? RBS > RB Smissaert wrote: >> Unfortunately

Re: [sqlite] Dealing with dates in the format yyyymmdd

2006-12-04 Thread bartsmissaert
Dennis, Excel dates start from 30 December 1899. Then there is of course the famous Excel date bug. Just type in Google: Excel date bug and you will see what I am talking about. RBS > [EMAIL PROTECTED] wrote: >>> just a normal SQL alias name >>> >> >> Of course, I get it. >> Haven't got it

Re: [sqlite] Dealing with dates in the format yyyymmdd

2006-12-04 Thread bartsmissaert
Great stuff. For your information it needs to be: julianday('1899-12-30') RBS > [EMAIL PROTECTED] wrote: >> Tried it, but get the error invalid use of null. >> The field I tried it on had no NULL values. >> Will figure it out. >> >> > Bart, > > I should have tested what I posted first. There

Re: [sqlite] Dealing with dates in the format yyyymmdd

2006-12-04 Thread bartsmissaert
Hi Dennis, Got this working now, great stuff and thanks! Will do some testing to see what is faster and report back here. RBS > [EMAIL PROTECTED] wrote: >> Thanks , will try that. >> What is as excel_date? >> Is this a variable or is this jus plain SQL against SQLite? >> >> > Bart, > >

Re: [sqlite] Dealing with dates in the format yyyymmdd

2006-12-04 Thread bartsmissaert
> just a normal SQL alias name Of course, I get it. Haven't got it working though. Still the invalid use of null error. RBS > [EMAIL PROTECTED] wrote: >> Thanks , will try that. >> What is as excel_date? >> Is this a variable or is this jus plain SQL against SQLite? >> >> > Bart, > >

Re: [sqlite] Dealing with dates in the format yyyymmdd

2006-12-04 Thread bartsmissaert
> can convert to seconds since start of epoch, do the arithmetic, > then convert back Don't get you. It would be quite a calculation, with leap years etc. RBS > On Sun, Dec 03, 2006 at 05:31:55PM +, RB Smissaert wrote: >> Still have the problem though how to compare dates in SQLite when the

Re: [sqlite] Dealing with dates in the format yyyymmdd

2006-12-04 Thread bartsmissaert
Tried it, but get the error invalid use of null. The field I tried it on had no NULL values. Will figure it out. RBS > RB Smissaert wrote: >> When moving data from Interbase to SQLite I have to convert integer >> dates in >> the format mmdd to Excel dates. These are integer numbers counting

Re: [sqlite] Dealing with dates in the format yyyymmdd

2006-12-04 Thread bartsmissaert
Thanks, will try that. What does the: % 100 do and what the Excel_date ? RBS > RB Smissaert wrote: >> When moving data from Interbase to SQLite I have to convert integer >> dates in >> the format mmdd to Excel dates. These are integer numbers counting >> the >> days past 31 December 1899.

Re: [sqlite] Dealing with dates in the format yyyymmdd

2006-12-04 Thread bartsmissaert
Thanks , will try that. What is as excel_date? Is this a variable or is this jus plain SQL against SQLite? RBS > RB Smissaert wrote: >> When moving data from Interbase to SQLite I have to convert integer >> dates in >> the format mmdd to Excel dates. These are integer numbers counting >> the

Re: [sqlite] Dealing with dates in the format yyyymmdd

2006-12-04 Thread bartsmissaert
Well, these fields I am talking about hold date information but they have nil to do with dates in that you can't do any date manipulation on the data. There also are proper date-time fields in the database and they hold something entirely different. The first field (with mmdd) hold a long data

RE: [sqlite] C++ SQLite

2006-11-29 Thread bartsmissaert
I did try the ODBC driver, but I found it was quite a bit slower. RBS > RB Smissaert uttered: > >> Yes, that looks to be the way to do it. Basically will have to learn how >> to >> translate C defines to VB declares. > > > Why not just use ODBC? > >

Re: [sqlite] C++ SQLite

2006-11-28 Thread bartsmissaert
Can I call the SQLite API (as in the dll SQLite.dll) directly from VB or do I need the wrapper? So, could it work from VB with declares as I use for the Windows API? RBS > sebcity wrote: >> How would one go about using c++ (Visual Studio.NET) to call and display >> SQLite tables. C++ wrappers? >