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 incon
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 budget
> Did something in the documentation make
> you think SQLite wouldn't use a
> multi-column index unless you forced it ?
No, but I just noticed it didn't use the index I thought would be best. As
it turned out it looks I was wrong in that that index didn't give the
quickest result.
RBS
>
> On 18
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
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 cas
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';
>
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?
>
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
>
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,
>
> --
> Ch
> "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: sqlite-us
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 a
How about this:
select
*
from
table1
where
rowid >=
(select
rowid
from
table1
where
time_stamp = xxx
limit 1)
and
rowid <
(select
rowid
from
table1
where
time_stamp > xxx
limit 1)
RBS
> Donald,
>
>> To test this, I think you'd want to create a select for some of the most
>> recent data (i.e. da
I am not actually dealing with images, but normal
text and integer data.
Got one reply now from RAC, so will see what comes from it.
RBS
> The easiest way to store an image would be to first convert the image to
> base64 and store the base64 text to the database. Converting data to
> base64
> t
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 do
same
> familiar 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?
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 databa
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 applicat
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 you
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
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
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
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 PROTE
You may have a look at the dhRCPServer at:
http://www.thecommon.net/2.html
I am not using it, but it sounds it may do the job.
RBS
> Hi All,
>
> I currently have a single-user SQLite-based application that, due to
> customer need, is being pushed toward multi-user access. I've done some
> resea
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 w
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
>> model.
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
>
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
>
>
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:
>> >> > W
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 e.P
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 crit
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:-
>
>
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 e
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 b
> 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
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
> 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 delete
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 Cot
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
> 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
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 1
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 l
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.
At
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 work
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 ou
Thanks, I will take out the UTF and trace pragma's.
synchronous=off is fine as this is only a temp database and not
for any permanent storage.
RBS
> RB Smissaert wrote:
>> 'these speed up inserts enormously, particulary the first one
>> '---
> 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,
> 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 d
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 cor
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 lo
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
>
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 = "UP
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
> lightwei
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 PROTECTE
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 -m
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 3
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.
>
> RB
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 should
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
> and
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 fas
> 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 nee
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
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 worki
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 i
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,
>
> excel_dat
> 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,
>
> excel_date
> 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
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
>
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. Wi
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
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
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?
>
> http://www.sqlite.org/cvstrac/wiki?p=SqliteOdb
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?
>
72 matches
Mail list logo
