Re: [sqlite] NOT LIKE
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 inconsistent data. > > Thanks > ___ > 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] Comparing two tables?
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 I was given for this > project, zero... Any words of wisdom from the group at large on where > to find how to do what I'm looking for or any examples? > > > > -Joe > ___ > 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] Force the use of a specified index?
> 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 Sep 2009, at 1:51pm, bartsmissa...@blueyonder.co.uk wrote: > >> I was trying to force the use of a multi-column index. > > Did something in the documentation make you think SQLite wouldn't use > a multi-column index unless you forced it ? There is rarely any point > in forcing any particular index. The part of SQLite which determines > which index to use is very good at working out which index is best for > the operation it's doing. > > 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] Force the use of a specified index?
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 performance boost, it's possible a > single multi-column index might be better. > Sam > > > On Thu, Sep 17, 2009 at 3:43 PM, Bart Smissaert > wrote: > >> Have tried INDEXED BY and it does indeed work and force the use of the >> specified index. >> It didn't however make the query faster, so maybe the SQLite plan >> generator is better than I thought! >> >> 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] Force the use of a specified index?
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 case. I remember a >> discussion about this and that this option might be added to SQLite, >> but couldn't find it anywhere. > > There is the "INDEXED BY" clause. But many are of the opinion that > this feature is prone to misuse. > >http://www.sqlite.org/lang_indexedby.html > > Dan. > >> >> 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] Why row is not found?
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'; > but I really don't have an explanation... > > -- > Marco Bambini > http://www.sqlabs.com > http://www.creolabs.com/payshield/ > > > > > > > On Jun 5, 2009, at 11:10 AM, Martin Engelschalk wrote: > >> Hi, >> >> attachments do not make it through the list. >> There is no row with the value 'admin' in the field 'lo_name' in your >> table. Did you check that there are no blank spaces or other invisible >> characters? >> >> Martin >> >> Marco Bambini wrote: >>> Anyone can please explain me why this query: >>> SELECT * FROM lo_user WHERE lo_name='admin'; >>> returns 0 rows in this db? >>> >>> >>> >>> Thanks. >>> -- >>> Marco Bambini >>> http://www.sqlabs.com >>> http://www.creolabs.com/payshield/ >>> >>> >>> >>> >>> >>> >>> >>> >>> ___ >>> 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] Export the results from a select to excel
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? > > tx, > > Danny > Belgium > > ___ > 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] drop table question ?
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 > -- > View this message in context: > http://www.nabble.com/drop-table-question---tp21806118p21806118.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] What is a Group By, having clause?
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, > > -- > Christophe Leske > > www.multimedial.de - [EMAIL PROTECTED] > http://www.linkedin.com/in/multimedial > Lessingstr. 5 - 40227 Duesseldorf - Germany > 0211 261 32 12 - 0177 249 70 31 > > > ___ > 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] best language match for SQLite?
> "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-users@sqlite.org >> Subject: Re: [sqlite] best language match for SQLite? >> >> SQLite is written in C, and its creators seem to be fond of TCL, so >> those are 2 good choices, but I don't think there is a most ideal >> language for anything - it all depends on balancing what you need to do >> and what you want to learn. >> >> And of course, Perl is always best. :) >> >> g >> >> -Original Message- >> From: Patrick [mailto:[EMAIL PROTECTED] >> Sent: Tuesday, September 16, 2008 10:51 AM >> To: General Discussion of SQLite Database >> Subject: [sqlite] best language match for SQLite? >> >> I am a beginner to intermediate Python Programmer. I can use SQLite with >> >> it just fine but it is my understanding that relational database and >> object oriented programming our not the perfect marriage. >> >> I was just wondering if anyone had an opinion on the most ideal language >> >> to use with SQLite? >> >> I love Python but I LOVE SQLite, I would learn another language just to >> use it better-Patrick >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > _ > See how Windows connects the people, information, and fun that are part of > your life. > http://clk.atdmt.com/MRT/go/msnnkwxp1020093175mrt/direct/01/ > ___ > 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] SQL question
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 an id, all entries with > id's >= the id need to be incremented to keep the id's unique > > ie > > 1 bob > 2 joe > 3 irene > > someone wants to insert 2 sarah, I need the table to look like this > > 1 bob > 2 sarah > 3 joe > 4 irene > > > Any help would be great. > thanks > > - > Gregor > > > ___ > 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] Implicit INDEX?
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. data at the tail of the database), perhaps after >> clearing cache. I suspect this will take the full table scan time to >> return any values. > > I'd actually just thought of that. All my test SELECTs happened > to be using data close to the "front" of the file. As suspected, if I try > it on "later" data, I have to wait for the table scan, then I get the > data. I'd be erroneously assuming that SQLite was somehow searching for > the first value quickly, then scanning the table. > >> Two thoughts: >> >> 1). Easy. >> How bad is the extra 0.8 GByte cost of the index? At today's prices, >> it's only about 20 cents on ordinary 5" drives. (but maybe you're >> programming a cellphone) > > No, it's nothing terribly constrained. I'm just trying to > understand the mechanisms and do what I can to keep the size down where > possible. I was somewhat surprised to find that adding an index on a > single INTEGER column nearly doubled the size of the database and wanted > to figure out if there was a way around it, given that the column will > always be sorted. (And given my, perhaps erroneous understanding that > creating an INDEX just makes sure that the column stays sorted so SQLite > can search through it more intelligently) > >> 2). Fancy. >> You could create your own sparse index table mapping a ROWID to say, >> every thousandth timestamp. Then you could create upper and lower ROWID >> bounds on any query based on timestamps. Maybe you import the records >> in batches already and can create the new table at the same time. > > This is closer to what I'm probably going to do. The data gets > pulled in every 5 minutes, but between runs, very little actually changes. > So the idea is to store only the changes along with a full dump say once > or twice every day. Then I can just query the values from time> to and compute the state of everything from > that data. > > Thanks, > > Chris > ___ > 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] PHP Code That Can Store and Retrieve Images
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 > takes up about 33% more space than the original data. > > On Tue, Mar 11, 2008 at 6:22 PM, RB Smissaert < > [EMAIL PROTECTED]> wrote: > >> Funny you ask that as just 2 days ago I posted a little project >> on RAC to do exactly this. In my case it has to be called from >> VBA or VB. Unfortunately and surprisingly no takers yet. >> >> RBS >> >> >> -Original Message- >> From: [EMAIL PROTECTED] >> [mailto:[EMAIL PROTECTED] On Behalf Of Robert L Cochran >> Sent: 11 March 2008 21:15 >> To: General Discussion of SQLite Database >> Subject: [sqlite] PHP Code That Can Store and Retrieve Images >> >> Is there open source PHP code (PHP 5.x compatible) that can store and >> retrieve images from an SQLite 3.5.6 database? >> >> For SQLite version 3.5.x, I need to use the PHP PDO functions if I am >> using PHP 5.2.5, right? >> >> I want to show a group of people about 45 photos which I would like to >> store on an SQLite database and then retrieve. >> >> Thanks >> >> Bob Cochran >> ___ >> 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] Join trouble
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 don't know but am really struggling. > > > > $query = "SELECT name, username, currency, company, salescontact, > orders2006, orders2007, sales2006, sales2007, company FROM tblusers, > tblcompany WHERE tblusers.company = tblcompany.company"; > > > > The page loads up but there is just no data from the database, if I use > any of the databases on their own they work fine its only when I join > them > > > > Cheers for any help > > > > Simon Johnstone > > Customer Service Data Officer > > > > Tel + 44 (0) 1484 465 500 Ext 5154 > > Fax: +44 (0) 1484 465 586 > > E-mail [EMAIL PROTECTED] > mailto:[EMAIL PROTECTED]> > > Web: www.davidbrown.com http://www.davidbrown.com/> > > > > David Brown Engineering Limited > > Company No 331925 England > > Park Works, Park Road, Huddersfield HD4 5DD England > > > > ___ > 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] free excel-like COLORFUL gui for sqlite
Still not sure why you want to use SQLite here, but I think to get what you want you will have to code it yourself. RBS > > Let me be more clear. There's very little data, and I want to help my > client > be able to expand his business, so the first step is automating what he's > been doing by hand. I can get the excel data into sqlite no problem -- > and > wish to do so in order to START doing automated stuff with the data -- but > would like to PRESENT (and only present) the data in the familiar > spreadsheet manner. > > This is a general problem: many people use excel as a database only > because > of how nice it looks once they add coloring, and because it's easier to, > say, have the three address fields as three columns, even though logically > it should be a separate table Addresses. So, I'd like to have the 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? > > Thanks! > > > > bartsmissaert wrote: >> >> 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 database. It's >>> colorful, well-structured, and a joy to use. He has no code working on >>> the >>> data though -- it's just used for holding data, like a ledger book! >>> >>> My question is how I can put his information into a sqlite database but >>> give >>> him a very similar interface -- the same, well-structured, colorful, >>> spreadsheet view? I don't want him to even have to worry about which >>> column >>> is actually in which table-- just have it look like an excel sheet. >>> >>> This is very basic and easy, and I'd hate to have to reinvent the wheel >>> coding it -- is there a free sqlite gui that can present such a >>> colorful >>> spreadsheet view? >>> >>> Thank you! >>> -- >>> View this message in context: >>> http://www.nabble.com/free-excel-like-COLORFUL-gui-for-sqlite-tp14686423p14686423.html >>> Sent from the SQLite mailing list archive at Nabble.com. >>> >>> >>> - >>> To unsubscribe, send email to [EMAIL PROTECTED] >>> - >>> >>> >>> >> >> >> >> >> - >> To unsubscribe, send email to [EMAIL PROTECTED] >> - >> >> >> > > -- > View this message in context: > http://www.nabble.com/free-excel-like-COLORFUL-gui-for-sqlite-tp14686423p14686909.html > Sent from the SQLite mailing list archive at Nabble.com. > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] free excel-like COLORFUL gui for sqlite
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 database. It's > colorful, well-structured, and a joy to use. He has no code working on > the > data though -- it's just used for holding data, like a ledger book! > > My question is how I can put his information into a sqlite database but > give > him a very similar interface -- the same, well-structured, colorful, > spreadsheet view? I don't want him to even have to worry about which > column > is actually in which table-- just have it look like an excel sheet. > > This is very basic and easy, and I'd hate to have to reinvent the wheel > coding it -- is there a free sqlite gui that can present such a colorful > spreadsheet view? > > Thank you! > -- > View this message in context: > http://www.nabble.com/free-excel-like-COLORFUL-gui-for-sqlite-tp14686423p14686423.html > Sent from the SQLite mailing list archive at Nabble.com. > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Suggests for improving the SQLite website
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 application, plans for future. Not sure if this is feasible, but it would be interesting to read + to see the stats of it. Obviously it could become a very big list and I am not sure web-design-wise how that should be handled. RBS > We are looking at renovating the design of the SQLite > website and would love to have suggestions from the > community. If you have any ideas on how to improve > the SQLite website, please constribute either to the > mailing list or directly to me. > > Here are some links to competing database products > that might inspire comments: > >http://www.postgresql.org/ >http://www.firebirdsql.org/ >http://www.hsqldb.org/ >http://opensource.ingres.com/ >http://db.apache.org/derby/ >http://exist.sourceforge.net/ > > Among the comments received already are these: > > (1) It is not clear from the homepage that the software > is free. > (2) Half the page is devoted to talking about bugs in > the software. This suggests low quality. > (3) The "News" contains scary words: "radical changes". > (4) Three releases in as many months suggests the > code is not stable. > (5) Move the BigNameUsers to the front page > (see http://www.sqlite.org/wiki?p=BigNameUsers) > (6) Need more eye-candy. > > I do not necessary agree with the above comments, but I > am open to any and all ideas. You will not hurt my feels, > so speak freely. > > Thanks in advance for your input. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] How use 'uSQLiteServer' wrapper with Visual Basic .NET ???
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 know other server application ?? Please help me !
>
>
> Regards,
> Paulito
>
> 2007/11/6, [EMAIL PROTECTED]
> <[EMAIL PROTECTED]>:
>>
>> 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 SQLiteCommand()
>> > cmd.CommandText = "Select * from CFG_USER"
>> > cmd.Connection = cn
>> > cn.Open()
>> > dr = cmd.ExecuteReader()
>> >
>> > While (dr.Read())
>> >MsgBox(dr.GetValue(0))
>> > End While
>> >
>> > dr.Close()
>> > cn.Close(
>> >
>> > What changes i must to do, for use the "uSQLiteServer" wrapper ???
>> Please
>> > help me !!
>> >
>> > Thanks,
>> > Paulito
>> >
>> >
>> > **
>> >
>>
>>
>>
>>
>>
>> -
>> To unsubscribe, send email to [EMAIL PROTECTED]
>>
>> -
>>
>>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] How use 'uSQLiteServer' wrapper with Visual Basic .NET ???
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 SQLiteCommand()
> cmd.CommandText = "Select * from CFG_USER"
> cmd.Connection = cn
> cn.Open()
> dr = cmd.ExecuteReader()
>
> While (dr.Read())
>MsgBox(dr.GetValue(0))
> End While
>
> dr.Close()
> cn.Close(
>
> What changes i must to do, for use the "uSQLiteServer" wrapper ??? Please
> help me !!
>
> Thanks,
> Paulito
>
>
> **
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] New Operator Support
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 and its attachments contain confidential information from > HUAWEI, which is intended only for the person or entity whose address is > listed above. Any use of the information contained herein in any way > (including, but not limited to, total or partial disclosure, > reproduction, or dissemination) by persons other than the intended > recipient(s) is prohibited. If you receive this e-mail in error, please > notify the sender by phone or email immediately and delete it! > > * > > - Original Message - > From: [EMAIL PROTECTED] > Date: Friday, September 7, 2007 5:26 pm > Subject: Re: [sqlite] New Operator Support > >> 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 want implement it similar to '='. Can anyone help me >> > what all steps,files i need to change? >> > >> > regards >> > ragha >> > >> > >> > >> **> >> This email and its attachments contain confidential information from >> > HUAWEI, which is intended only for the person or entity whose >> address is >> > listed above. Any use of the information contained herein in any way >> > (including, but not limited to, total or partial disclosure, >> > reproduction, or dissemination) by persons other than the intended >> > recipient(s) is prohibited. If you receive this e-mail in error, >> please> notify the sender by phone or email immediately and delete it! >> > >> *> >> > - >> >> > To unsubscribe, send email to [EMAIL PROTECTED] >> > - >> >> > >> > >> > >> >> >> >> >> --- >> -- >> To unsubscribe, send email to [EMAIL PROTECTED] >> --- >> -- >> >> > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] New Operator Support
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 want implement it similar to '='. Can anyone help me > what all steps,files i need to change? > > regards > ragha > > > ** > This email and its attachments contain confidential information from > HUAWEI, which is intended only for the person or entity whose address is > listed above. Any use of the information contained herein in any way > (including, but not limited to, total or partial disclosure, > reproduction, or dissemination) by persons other than the intended > recipient(s) is prohibited. If you receive this e-mail in error, please > notify the sender by phone or email immediately and delete it! > > * > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLite or MS Access
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 PROTECTED] > Envoyé : vendredi 7 septembre 2007 10:25 > À : sqlite-users@sqlite.org > Objet : RE: [sqlite] SQLite or MS Access > > Well here are my test results (im using Delphi for this one) > > This is my insert statement: > > INSERT INTO TEST (TEST_ID_NO_IDX, NO_INDEX, TEST_DATA, TEST_ID) values > (%d, %s, %s, %d) > This table deliberately has NO index. > > > 1000 inserts took: > Inserting MS Access - 4,043.273 ms > Inserting SQLite - 249.329 ms > > In my sample the key is doing the following > > BEGIN TRANSACTION > Loop inside transaction: > Do inserts > > COMMIT TRANSACTION > > I'm suspecting that you are falling into the trap of not doing most of > your work in a transaction, in SQLite that is BAD, sqlite tries to be as > durable as possible, so it writes to the journal and flushes file > buffers each time a transaction commits, if you don't specify one, then > this will automatically happen on each insert and will kill your speed. > > > What I don't understand is how Access, and other DB's are able to still > operate much faster (maybe not as fast), and still flush file buffers to > disk, is beyond me. Maybe it really still caches it, I would not be > surprised if you pull the plug from an MS access db you may end up with > missing records even if you committed, I could be wrong... > > Used correctly SQlite should be the fastest there is, obviously for more > single user (desktop db) style operations not multiuser. > > Ive done Inserts up to the 40-50K/ sec in sqlite with the right kind of > tweaks. > > > > -Original Message- > From: Michael Martin [mailto:[EMAIL PROTECTED] > Sent: 07 September 2007 10:06 AM > To: sqlite-users@sqlite.org > Subject: [sqlite] SQLite or MS Access > > Hi All, > > > > I've done some benchmarks tests and I wonder where I've made a mistake. > > > > In C# code with SQLite.NET.2.0.1 wrapper with sqlite 3.0: 1000 inserts > in a table of two columns -> 168 seconds > > > > In C# code with Jet.Oledb.4.0 with MS Access: 1000 inserts in a table > of two columns -> 1.14 seconds > > > > Could someone help me please > > > > Thanks in advance > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Multi-User confusion
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 > research on the multi-user capabilities of SQLite. It seems the general > consensus is that when the database file is stored on a network drive > (as is my case), the integrity of the stored data becomes questionable > (apparently due to bugs in the various NFS file locking protocols). > > Fortunately, my application is designed such that (generally speaking) > each User of the system will be working within their own SQLite > database. However, there are a few select places in the code where a > User could trigger an action that would cause the storage of data to a > common, upper-level SQLite database. > > I think I can change portions of the application to ensure that these > common writes never happen concurrently, but I'd like to understand the > underlying situations and dangers that can occur in this environment. > So, what are the cases that could cause database corruption? > > 1. Multiple Users writing to the same table of the same open database at > the same time? > 2. Multiple Users writing to two different tables of the same open > database at the same time? > 3. Multiple Users writing to the same table of the same open database at > different times? > 4. Other cases I haven't thought about? > > I realize there also some dangers with regard to potentially writing > "stale" data to the database thus losing someone else's updates. I > still have some thinking to do in that regard, but for now I'd like to > understand the situations that could compromise the integrity of the > underlying database file itself. > > Thanks for any details you can provide. > > Jeff > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index?
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 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. > > Even in the simplest situation it pays to have a minimum of context > analysis before starting the construct. > > The obvious solution is probably to just extend planks across the > river, but if you stop to think a little you may remember the water > level on Winter is much higher than the current levels, meaning a much > higher pressure of the water on the bridge (and helps to know if it > increases in a logarithmic or linear way). > > By applying the right engineering techniques you at least know the > limits of the wood construction you just built, and maybe manage to > solve the problem with less resources by having the bridge built in a > special way, like in an U shape to increase strength against the > current. > > > Regards, > ~Nuno Lucas > > >> >> RBS >> >> >> > Absolutely. Big bridge or small bridge, if it fails you fall in the >> > water. >> > >> > It looks as if the bridge in Minneapolis failed because construction >> > workers moved tons of repaving material onto part of it and >> overstressed >> > that section. A few calculations could have saved the catastrophe. >> > >> > I saw an estimate that software errors cost just the US more than $100 >> > billion per year. That is equivalent to more than 5% of the entire UK >> > GDP. Doesn't it make sense to try to build software which works to >> > design rather than trying alternatives until one which does not fail >> > eventuates? >> > >> > Also note what early researchers in proof of software accuracy pointed >> > out. Testing only finds bugs, it does not establish the correctness >> of >> > a program. Only an appropriate design methodology can hope to >> establish >> > correct behaviour of the program. >> > >> > RB Smissaert wrote: >> >> Poor comparison in this case. >> >> Are you going to make a mathematical model when you got a little >> stream >> >> to >> >> cross and you have a few available planks to do it? >> >> >> >> RBS >> >> >> >> >> >> -Original Message- >> >> From: John Stanton [mailto:[EMAIL PROTECTED] >> >> Sent: 05 August 2007 16:43 >> >> To: sqlite-users@sqlite.org >> >> Subject: Re: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite >> choose >> >> the >> >> index? >> >> >> >> We learn mathematics etc so that we can make numerical models which >> give >> >> us design information. Imagine trying to build every combination of >> a >> >> bridge to settle on a design! >> >> >> >> Make a mathematical model and get it close to optimal at the first >> >> attempt. >> >> >> >> RB Smissaert wrote: >> >> >> >>>Yes, I suppose you are right there. >> >>>I will see if I can put together a report that runs all possible >> types >> >>> of >> >>>queries (sequentially) and then see if I have left anything out that >> >>> would >> >>>cause problems. >> >>> >> >>>RBS >> >>> >> >>> >> >>>-Original Message- >> >>>From: Gerry Snyder [mailto:[EMAIL PROTECTED] >> >>>Sent: 05 August 2007 03:35 >> >>>To: sqlite-users@sqlite.org >> >>>Subject: Re: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite >> choose >> >> >> >> the >> >> >> >>>index? >> >>> >> >>>RB Smissaert wrote: >> >>> >> >>> >> I think an application that >> would produce all the needed indexes based on the table and all the >> >>> >> >>>possible >> >>> >> >>> >> queries would be helpful. Anybody done such an app? >> >>> >> >>>_All_ possible queries? Not practical for any significant number of >> >>>columns. N factorial gets big fast. >> >>> >> >>>The indexes would be much larger than the data base itself. >> >>> >> >>>I'm afraid you are going to have to settle for doing an intelligent >> >>>design of the data base. >> >>> >> >>> >> >>>Gerry >> >>> >> >>> >> >>> >> >> >> >> >> >> >> >>>- >> >>>To unsubscribe, send email to [EMAIL PROTECTED] >> >>> >> >> >> >> >> >> >> >>>- >> >>> >> >>> >> >>> >> >>> >> >>> >> >> >> >> >> >> - >> >> >> >>>To unsubscribe, send email to [EMAIL PROTECTED] >> >>> >> >> >> >> >> >> - >> >> >> >> >> >> >> >> >> >> - >> >> To unsubscribe, send email to [EMAIL PROTECTED] >> >> >> >> - >> >> >> >> >> >> >> >> >> >> --
Re: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index?
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. >> 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 >>>water. >>> >>>It looks as if the bridge in Minneapolis failed because construction >>>workers moved tons of repaving material onto part of it and overstressed >>>that section. A few calculations could have saved the catastrophe. >>> >>>I saw an estimate that software errors cost just the US more than $100 >>>billion per year. That is equivalent to more than 5% of the entire UK >>>GDP. Doesn't it make sense to try to build software which works to >>>design rather than trying alternatives until one which does not fail >>>eventuates? >>> >>>Also note what early researchers in proof of software accuracy pointed >>>out. Testing only finds bugs, it does not establish the correctness of >>>a program. Only an appropriate design methodology can hope to establish >>>correct behaviour of the program. >>> >>>RB Smissaert wrote: >>> Poor comparison in this case. Are you going to make a mathematical model when you got a little stream to cross and you have a few available planks to do it? RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 05 August 2007 16:43 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index? We learn mathematics etc so that we can make numerical models which give us design information. Imagine trying to build every combination of a bridge to settle on a design! Make a mathematical model and get it close to optimal at the first attempt. RB Smissaert wrote: >Yes, I suppose you are right there. >I will see if I can put together a report that runs all possible types >of >queries (sequentially) and then see if I have left anything out that >would >cause problems. > >RBS > > >-Original Message- >From: Gerry Snyder [mailto:[EMAIL PROTECTED] >Sent: 05 August 2007 03:35 >To: sqlite-users@sqlite.org >Subject: Re: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite > choose the >index? > >RB Smissaert wrote: > > > >> I think an application that >>would produce all the needed indexes based on the table and all the > >possible > > > >>queries would be helpful. Anybody done such an app? > >_All_ possible queries? Not practical for any significant number of >columns. N factorial gets big fast. > >The indexes would be much larger than the data base itself. > >I'm afraid you are going to have to settle for doing an intelligent >design of the data base. > > >Gerry > > > >- >To unsubscribe, send email to [EMAIL PROTECTED] > >- > > > > > - >To unsubscribe, send email to [EMAIL PROTECTED] > - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - >>> >>> >>>- >>>To unsubscribe, send email to [EMAIL PROTECTED] >>>- >>> >>> >>> >> >> >> >> >> >> - >> To unsubscribe, send email to [EMAIL PROTECTED] >> - >> > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsub
Re: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose the index?
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 > water. > > It looks as if the bridge in Minneapolis failed because construction > workers moved tons of repaving material onto part of it and overstressed > that section. A few calculations could have saved the catastrophe. > > I saw an estimate that software errors cost just the US more than $100 > billion per year. That is equivalent to more than 5% of the entire UK > GDP. Doesn't it make sense to try to build software which works to > design rather than trying alternatives until one which does not fail > eventuates? > > Also note what early researchers in proof of software accuracy pointed > out. Testing only finds bugs, it does not establish the correctness of > a program. Only an appropriate design methodology can hope to establish > correct behaviour of the program. > > RB Smissaert wrote: >> Poor comparison in this case. >> Are you going to make a mathematical model when you got a little stream >> to >> cross and you have a few available planks to do it? >> >> RBS >> >> >> -Original Message- >> From: John Stanton [mailto:[EMAIL PROTECTED] >> Sent: 05 August 2007 16:43 >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose >> the >> index? >> >> We learn mathematics etc so that we can make numerical models which give >> us design information. Imagine trying to build every combination of a >> bridge to settle on a design! >> >> Make a mathematical model and get it close to optimal at the first >> attempt. >> >> RB Smissaert wrote: >> >>>Yes, I suppose you are right there. >>>I will see if I can put together a report that runs all possible types >>> of >>>queries (sequentially) and then see if I have left anything out that >>> would >>>cause problems. >>> >>>RBS >>> >>> >>>-Original Message- >>>From: Gerry Snyder [mailto:[EMAIL PROTECTED] >>>Sent: 05 August 2007 03:35 >>>To: sqlite-users@sqlite.org >>>Subject: Re: [sqlite] Re: Re: Re: Re: Re: Re: Re: How does SQLite choose >> >> the >> >>>index? >>> >>>RB Smissaert wrote: >>> >>> I think an application that would produce all the needed indexes based on the table and all the >>> >>>possible >>> >>> queries would be helpful. Anybody done such an app? >>> >>>_All_ possible queries? Not practical for any significant number of >>>columns. N factorial gets big fast. >>> >>>The indexes would be much larger than the data base itself. >>> >>>I'm afraid you are going to have to settle for doing an intelligent >>>design of the data base. >>> >>> >>>Gerry >>> >>> >>> >> >> >> >>>- >>>To unsubscribe, send email to [EMAIL PROTECTED] >>> >> >> >> >>>- >>> >>> >>> >>> >>> >> >> >> - >> >>>To unsubscribe, send email to [EMAIL PROTECTED] >>> >> >> >> - >> >> >> >> >> - >> To unsubscribe, send email to [EMAIL PROTECTED] >> >> - >> >> >> >> >> - >> To unsubscribe, send email to [EMAIL PROTECTED] >> - >> > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Re: inner join
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 > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQL query help (mutiple joins)
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: >> >> > 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.PatientID=p.ID >> >> join TestTable as t on t.ExamID=e.ID >> >> join ForcedSpiroTable as f on f.TestID=t.ID >> >> join RelaxedSpiroTable as r on r.TestID=t.ID >> >> where f.EVC > 2.0 and r.FVC > 2.0; >> > >> > Ok, so here's another question, how would I get the count of patients >> > where the EVC and FVC > 2.0? >> > >> > > > > -- > Puneet Kishor http://punkish.eidesis.org/ > Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ > Open Source Geospatial Foundation http://www.osgeo.org/education/ > - > collaborate, communicate, compete > = > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQL query help (mutiple joins)
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.PatientID=p.ID >> join TestTable as t on t.ExamID=e.ID >> join ForcedSpiroTable as f on f.TestID=t.ID >> join RelaxedSpiroTable as r on r.TestID=t.ID >> where f.EVC > 2.0 and r.FVC > 2.0; > > Ok, so here's another question, how would I get the count of patients > where the EVC and FVC > 2.0? > > > > DISCLAIMER: > This information and any attachments contained in this email message is > intended only for the use of the individual or entity to which it is > addressed and may contain information that is privileged, confidential, > and exempt from disclosure under applicable law. If the reader of this > message is not the intended recipient, or the employee or agent > responsible for delivering the message to the intended recipient, you are > hereby notified that any dissemination, distribution, forwarding, or > copying of this communication is strictly prohibited. If you have > received this communication in error, please notify the sender immediately > by return email, and delete the original message immediately. > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQL query help (mutiple joins)
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 criteria.
>
> i.e. If a single patient say "Joe Bloggs" has 5 tests, all with EVC and
> FVC greater than 2.0 then I get
>
> Joe Bloggs
> Joe Bloggs
> Joe Bloggs
> Joe Bloggs
> Joe Bloggs
>
> What I want is Joe Blogs just the once.
>
> Does this make sense? What I need to do is find all patients that have an
> EVC and FVC greater than 2.0.
>
> Is there a way to do this? Am I missing something?
>
> Thanks again
>
> Mark
>
>
>> -Original Message-
>> From: Dennis Cote [mailto:[EMAIL PROTECTED]
>> Sent: 01 May 2007 15:31
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] SQL query help (mutiple joins)
>>
>>
>> Allan, Mark wrote:
>> > I have a database that looks something like the following:-
>> >
>> > PatientsTable { ID, Name, Sex, }
>> > ExaminationsTable { ID, PatientID, }
>> > TestTable { ID, ExamID, .}
>> > ForcedSpiroTable { ID, TestID, EVC, IVC, IC ... }
>> > RelaxedSpiroTable { ID, TestID, FVC, FEV1, PEF, ...}
>> >
>> > Can someone help me out with the syntax for applying a
>> search for all rows in the PatientsTable that have a
>> ForcedSpiroTable.EVC > 2.0 and a RelaxedSpiroTable.FVC > 2.0?
>> >
>> >
>> > Basically ExaminationsTable has a foreign key to
>> PatientsTable, TestsTable has a foreign key to
>> ExaminationsTable and both ForcedSpiroTable and
>> RelaxedSpiroTable have a foreign key to the TestTable.
>> >
>> >
>> >
>> Mark,
>>
>> This should do the trick:
>>
>> select p.*
>> from PatientsTable as p
>> join ExaminationsTable as e on e.PatientID=p.ID
>> join TestTable as t on t.ExamID=e.ID
>> join ForcedSpiroTable as f on f.TestID=t.ID
>> join RelaxedSpiroTable as r on r.TestID=t.ID
>> where f.EVC > 2.0 and r.FVC > 2.0;
>>
>> HTH
>> Dennis Cote
>>
>> --
>> ---
>> To unsubscribe, send email to [EMAIL PROTECTED]
>> --
>> ---
>>
>>
>>
>>
>>
>>
>
>
> DISCLAIMER:
> This information and any attachments contained in this email message is
> intended only for the use of the individual or entity to which it is
> addressed and may contain information that is privileged, confidential,
> and exempt from disclosure under applicable law. If the reader of this
> message is not the intended recipient, or the employee or agent
> responsible for delivering the message to the intended recipient, you are
> hereby notified that any dissemination, distribution, forwarding, or
> copying of this communication is strictly prohibited. If you have
> received this communication in error, please notify the sender immediately
> by return email, and delete the original message immediately.
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] SQL query help (mutiple joins)
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:-
>
> PatientsTable { ID, Name, Sex, }
> ExaminationsTable { ID, PatientID, }
> TestTable { ID, ExamID, .}
> ForcedSpiroTable { ID, TestID, EVC, IVC, IC ... }
> RelaxedSpiroTable { ID, TestID, FVC, FEV1, PEF, ...}
>
> Can someone help me out with the syntax for applying a search for all rows
> in the PatientsTable that have a ForcedSpiroTable.EVC > 2.0 and a
> RelaxedSpiroTable.FVC > 2.0?
>
>
> Basically ExaminationsTable has a foreign key to PatientsTable, TestsTable
> has a foreign key to ExaminationsTable and both ForcedSpiroTable and
> RelaxedSpiroTable have a foreign key to the TestTable.
>
> Your help would be gratefully received
>
> Thanks in advance
>
> Mark
>
>
> DISCLAIMER:
> This information and any attachments contained in this email message is
> intended only for the use of the individual or entity to which it is
> addressed and may contain information that is privileged, confidential,
> and exempt from disclosure under applicable law. If the reader of this
> message is not the intended recipient, or the employee or agent
> responsible for delivering the message to the intended recipient, you are
> hereby notified that any dissemination, distribution, forwarding, or
> copying of this communication is strictly prohibited. If you have
> received this communication in error, please notify the sender immediately
> by return email, and delete the original message immediately.
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Optimize a query
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 explicitly by restructuring > your SQL. > > As an example, consider this query: > > > SELECT * FROM table1 WHERE a=5 AND b=11; > > Suppose there are two indices: > > CREATE INDEX index1 ON table1(a); > CREATE INDEX index2 ON table1(b); > > As written, SQLite will only use one of these two indices > to perform the query. The choice is arbitrary (unless you > have run ANALYZE and SQLite has some information to help it > pick the "best" index.) > > If you want to use both indices, rewrite the query this > way: > > SELECT * FROM table1 WHERE rowid IN > (SELECT rowid FROM table1 WHERE a=5 >INTERSECT SELECT rowid FROM table1 WHERE b=11); > > The optimizer in PostgreSQL will make this change for you > automatically and will use a bitmap to implement the IN > operator and the INTERSECT. With SQLite, though, you have to > type in the expanded version yourself. And because rowids > in SQLite are user visible and changeable and can thus be > diffuse, SQLite is unable to use bitmaps to optimize the > computation. But modulo the bitmap optimization, SQLite gives > you all the capabilities of PostgreSQL, you just have to type > it in yourself rather than letting the optimizer do it for > you. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Version 3.3.14
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 been several changes to the back-end layers (the pager > and the b-tree subsystems) that reduce the amount of disk > I/O. A new optimization has been added to the INSERT command > which, as a side effect, makes VACUUM work significantly faster > for large databases and helps VACUUM to greatly reduce the > amount of fragmentation in the database. We have also added > the concept of "exclusive access mode". In exclusive access > mode, SQLite holds onto locks until you close the connection. > This allows for additional I/O reductions and corresponding > performance improvements, at the expense of concurrency. The > core SQLite sources are now also available as a single huge > file of C code (which we call "the amalgamation") rather than > as a collection of smaller files. > > When all the latest changes are used and the code is > recompiled using the amalgamation with -O3 under gcc 4.1.0, > we are seeing performance improvements on Linux of about 35% > over version 3.3.13. We are very interesting in hearing > about performance changes on other compilers and with other > operating systems. > > Version 3.3.14 incorporates many changes over version 3.3.13. > These changes have been well tested on Linux, but as the > no so much on windows and other systems. If you find problems, > please report them either on this list or at > > http://www.sqlite.org/cvstrac/tktnew > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Questions on views
> 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 a utility that could analyze (at least to some >> extent) the results for you? >> >> > The documentation you need is in the VDBE tutorial at > http://www.sqlite.org/vdbe.html and the opcode reference at > http://www.sqlite.org/opcode.html > > The only utility is the explain command in the sqlite shell. Use > .explain on to get a nicely formatted display and then execute > > explain ; > > Its really fairly simple (at least for anyone who has worked with > assembly level programming) to follow the execution of an SQL statement > by the virtual machine (at least for simple statements). Following the > logic of a complex statements can be challenging because there are no > human friendly text labels for branches, table and index names, or > runtime variables. > > HTH > Dennis Cote > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Questions on views
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 prepared to decipher the opcodes. >> >> >> > Joe, > > I had looked at the explain output and it wasn't clear which would be > faster. The count method produces less VDBE code but involves calls to > the count functions (step and final). The exists tests are done by > setting and testing integer variables in the VDBE code even though the > code is somewhat longer. Hence my recommendation to test both. > > Still, examining the explain output is generally good advice whenever > you are optimizing your SQL. > > Dennis Cote > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Any way to do this faster?
> 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 deletes on a table. Is it usually better to re-index after deletes? RBS > Another way of saying what Dennis said (I had to read his reply twice > before I > understood it): > > your query: for every record in TableB it returns ALL the records in > tableA > and then looks through them > > Dennis's query: for every record in TableB it checks tableA directly for > the > existence of that ID > > > the speed increase should be dramatic > > also, as Dennis said .. unless you have some other reason for that index > on > Table B, get rid of it. Indexes slow down everything but database reads > and > you're not using it in this example. > > > [EMAIL PROTECTED] wrote: >> >> 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 tableA and non-unique integer numbers in >> >> tableB. >> >> Both tables have an index on this field and for tableA this is an >> >> INTEGER >> >> PRIMARY KEY. >> >> Now I need to delete the rows in tableB where this number doesn't >> appear >> >> in >> >> the corresponding field in tableA. >> >> >> >> Currently I do this with this SQL: >> >> >> >> Delete from tableB where ID not in (select tableA.ID from tableA) >> >> >> >> When table tableB gets big (say some 10 rows) this will get a bit >> >> slow >> >> and I wonder if there is a better way to do this. >> >> >> >> RBS >> >> >> >> >> >> >> >> >> >> >> >> >> >> - >> >> To unsubscribe, send email to [EMAIL PROTECTED] >> >> - >> >> >> >> >> >> >> > Your query is doing a complete table scan of tableA for each record in >> a >> > table scan of tableB. >> > >> > SQLite version 3.3.13 >> > Enter ".help" for instructions >> > sqlite> create table tableA(id integer primary key, b); >> > sqlite> create table tableB(id, c); >> > sqlite> create index b_id on tableB(id); >> > sqlite> explain query plan delete from tableB where id not in (select >> > tableA.id >> > from tableA); >> > 0|0|TABLE tableB >> > 0|0|TABLE tableA >> > >> > You can improve this greatly using correlated subquery that will use >> the >> > primary key index on tableA to find any matching records. >> > >> > sqlite> explain query plan delete from tableB where not exists (select >> > id from t >> > ableA where tableA.id = tableB.id); >> > 0|0|TABLE tableB >> > 0|0|TABLE tableA USING PRIMARY KEY >> > >> > Note that your index on tableB.id is not used and could be eliminated >> > unless it serves another purpose. >> > >> > HTH >> > Dennis Cote >> > >> > - >> > To unsubscribe, send email to [EMAIL PROTECTED] >> > - >> > >> > >> > >> >> >> >> >> - >> To unsubscribe, send email to [EMAIL PROTECTED] >> - >> >> > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Any way to do this faster?
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 Cote [mailto:[EMAIL PROTECTED] > Sent: 26 March 2007 16:12 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Any way to do this faster? > > > 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 tableA and non-unique integer numbers in > tableB. >> Both tables have an index on this field and for tableA this is an > INTEGER >> PRIMARY KEY. >> Now I need to delete the rows in tableB where this number doesn't > appear in >> the corresponding field in tableA. >> >> Currently I do this with this SQL: >> >> Delete from tableB where ID not in (select tableA.ID from tableA) >> >> When table tableB gets big (say some 10 rows) this will get a bit > slow >> and I wonder if there is a better way to do this. >> >> RBS >> >> >> >> >> >> >> > > - >> To unsubscribe, send email to [EMAIL PROTECTED] >> > > - >> >> >> > Your query is doing a complete table scan of tableA for each record in a > > table scan of tableB. > > SQLite version 3.3.13 > Enter ".help" for instructions > sqlite> create table tableA(id integer primary key, b); > sqlite> create table tableB(id, c); > sqlite> create index b_id on tableB(id); > sqlite> explain query plan delete from tableB where id not in (select > tableA.id > from tableA); > 0|0|TABLE tableB > 0|0|TABLE tableA > > You can improve this greatly using correlated subquery that will use the > > primary key index on tableA to find any matching records. > > sqlite> explain query plan delete from tableB where not exists (select > id from t > ableA where tableA.id = tableB.id); > 0|0|TABLE tableB > 0|0|TABLE tableA USING PRIMARY KEY > > Note that your index on tableB.id is not used and could be eliminated > unless it serves another purpose. > > HTH > Dennis Cote > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > THE INFORMATION CONTAINED IN THIS MESSAGE AND ANY ATTACHMENT MAY BE > PRIVILEGED, CONFIDENTIAL, PROPRIETARY OR OTHERWISE PROTECTED FROM > DISCLOSURE. If the reader of this message is not the intended recipient, > you are hereby notified that any dissemination, distribution, copying or > use of this message and any attachment is strictly prohibited. If you have > received this message in error, please notify us immediately by replying > to the message and permanently delete it from your computer and destroy > any printout thereof. > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Any way to do this faster?
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 tableA and non-unique integer numbers in >> tableB. >> Both tables have an index on this field and for tableA this is an >> INTEGER >> PRIMARY KEY. >> Now I need to delete the rows in tableB where this number doesn't appear >> in >> the corresponding field in tableA. >> >> Currently I do this with this SQL: >> >> Delete from tableB where ID not in (select tableA.ID from tableA) >> >> When table tableB gets big (say some 10 rows) this will get a bit >> slow >> and I wonder if there is a better way to do this. >> >> RBS >> >> >> >> >> >> >> - >> To unsubscribe, send email to [EMAIL PROTECTED] >> - >> >> >> > Your query is doing a complete table scan of tableA for each record in a > table scan of tableB. > > SQLite version 3.3.13 > Enter ".help" for instructions > sqlite> create table tableA(id integer primary key, b); > sqlite> create table tableB(id, c); > sqlite> create index b_id on tableB(id); > sqlite> explain query plan delete from tableB where id not in (select > tableA.id > from tableA); > 0|0|TABLE tableB > 0|0|TABLE tableA > > You can improve this greatly using correlated subquery that will use the > primary key index on tableA to find any matching records. > > sqlite> explain query plan delete from tableB where not exists (select > id from t > ableA where tableA.id = tableB.id); > 0|0|TABLE tableB > 0|0|TABLE tableA USING PRIMARY KEY > > Note that your index on tableB.id is not used and could be eliminated > unless it serves another purpose. > > HTH > Dennis Cote > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Question about speed of CASE WHEN
> 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 SQLite as a data manipulator, not as a database. I get data >> from a >> server database, dump to SQLite, manipulate the data and finally >> dump to >> Excel. As this is reporting software speed is important, so I will >> go with >> the fastest method. > > OK, I have to ask. What do you then do with the data in Excel? I > spend so much of my time with clients converting them from using > spreadsheets (80% of the time when it's more appropriate) to using a > database, that my ears prick up whenever I hear someone doing the > reverse. > > Can you create whatever facilities you're using in Excel, directly in > the SQLite database (eg via CREATE VIEW)? Or is it just a case of > needing to view the final data in a user friendly environment? > > Tom > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] x000 line queries - only for benchmarks?
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 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. >> > Under 2k? That seems a bit restrictive. No benchmarking for you then, > because the 25k line queries in the SQLite benchmark are nearly 2 meg. I > guess they're unusual but even the (more typical?) 100 liners can get up > to about 6-8k. > > Martin > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] x000 line queries - only for benchmarks?
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 list but I was > wondering, how often do 1000+ line queries (as in the SQLite benchmark) > occur in the real world? Do queries of this sort of size exist outside > of initialising tables with a long lists of inserts? > > Martin > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How do I know sqlite_get_table is finished
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 work now, but will see if I can test in a bit, but I think that is it.
> 1. Use like you have used sqlite_get_table, but for situations when you
> don't need a result set (like Update queries or Pragma commands).
I think this was already in Todd's dll.
RBS
> Hello.
>
> At 14:32 2/2/2007 +, you wrote:
>>lReturnedRows is one of the function arguments, so that is fine.
>>
>>I have made some progress though and that is that this problem only
>> occurs
>>with this particular table, called SQL. It is a table that logs all the
>> SQL
>>statements that run in my app. When I instead make this for example
>>sqlite_master there is no problem ever.
>>No idea though why this table would cause a problem. Could it be that
>>one of the items in that table is a reserved word?
>>
>>CREATE TABLE 'SQL'
>>([STATEMENT_COUNT] INTEGER,
>>[DB] TEXT,
>>[QUERY_TIME] TEXT,
>>[QUERY_LENGTH] REAL,
>>[QUERY] TEXT)
>>
>>Any other ideas what could be wrong with this table?
>
> It is possible that the result set is too large to fit in the memory
> constraints you have, so when one of the memory allocations in the
> sqlite_get_table fails, the application crashes.
> Can you run the same statement from the sqlite3 command line application
> and see if it returns the expected results? and if it's very long?
>
> In any case, I've worked a bit on the function, and here's the result:
>
> VBSQL.h -
>
> #include
> #include
> #include
> #include
> #include
> #include "sqlite3.h"
>
> SAFEARRAY * __stdcall sqlite_get_table(sqlite3 * , const char *, BSTR * ,
> long*, VARIANT);
> BSTR __stdcall sqlite_libversion(void);
> int __stdcall sqlite_exec(sqlite3*, const char*, BSTR*);
>
> VBSQL.c -
> #include "vbsqlite.h"
>
> #define MEMORY_ERROR"Memory allocation error"
>
> BOOL FillVariantFromString(VARIANT* pVariant, const char* pString)
> {
> int nLen;
> LPOLESTR pOLEString;
>
> VariantClear(pVariant);
>
> nLen = MultiByteToWideChar( CP_ACP, 0, pString, -1, NULL, 0);
> if (nLen == 0)
> return FALSE;
> nLen++;
> pOLEString = CoTaskMemAlloc(nLen * sizeof(WCHAR));
> if (pOLEString == NULL)
> return FALSE;
> if (MultiByteToWideChar( CP_ACP, 0, pString, -1, pOLEString,
> nLen)
> == 0)
> {
> CoTaskMemFree(pOLEString);
> return FALSE;
> }
> pVariant->bstrVal = SysAllocString(pOLEString);
> V_VT(pVariant) = VT_BSTR;
> CoTaskMemFree(pOLEString);
> return TRUE;
> }
>
> SAFEARRAY * __stdcall sqlite_get_table(
>sqlite3 *db, /* The database on which the SQL executes */
>const char *zSql, /* The SQL to be executed */
>BSTR *ErrMsg, /* Write error messages here */
>long* pNumberOfRows, /* Number of rows in the returned array (ALWAYS
> without the headers!) */
>VARIANT varIncludeHeaders /* TRUE to include headers, FALSE not to */
> ) /* Return the SAFEARRAY */
> {
> // Temp result fields
> char **pSQL_Results;
> char *pErrMessage= 0;
> int nNumberOfColumns;
> int nResult;
> SAFEARRAY* pResult = NULL;
>
> nResult = sqlite3_get_table(db, zSql, &pSQL_Results,
> pNumberOfRows, &nNumberOfColumns, &pErrMessage);
>
> if (nResult == SQLITE_OK)
> {
> SAFEARRAYBOUND SA_Bounds[2];
>
> sqlite3_free(pErrMessage);
> if (nNumberOfColumns == 0)
> {
> sqlite3_free_table(pSQL_Results);
> // Return an empty array - to make sure nothing
> happens:
> SA_Bounds[0].cElements = 0;
> SA_Bounds[0].lLbound = 0;
> pResult = SafeArrayCreate(VT_VARIANT, 1,
> SA_Bounds);
> if (pResult == NULL)
> {
> *ErrMsg =
> SysAllocStringByteLen(MEMORY_ERROR,strlen(MEMORY_ERROR) );
> return NULL; // Don't know what this will
> do. NEVER USE AN ARRAY when an error was returned!
> }
> }
> else
> {
> //We have a resultset so transform this into a
> SAFEARRAY
> // Create SAFEARRAY
> //SAFEARRAY FAR* resultp = NULL;
> BSTR bstrTemporyStringHolder = NULL;
> VARIANT tmpVariant;
> BSTR bstr1 = NULL;
>
RE: [sqlite] How do I know sqlite_get_table is finished
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, but I think this is what the trouble was, so indeed a simple VBA bug and little to do with SQLite. Will see if I can test it in a bit and confirm that this was the trouble. RBS > If you want to email me a copy of the table I'd be more than happy to try > running it here and see what happens. You can send it to epankoke @ gmail > dot com. > > -- > Eric Pankoke > Founder / Lead Developer > Point Of Light Software > http://www.polsoftware.com/ > > -- Original message -- > From: "RB Smissaert" <[EMAIL PROTECTED]> >> lReturnedRows is one of the function arguments, so that is fine. >> >> I have made some progress though and that is that this problem only >> occurs >> with this particular table, called SQL. It is a table that logs all the >> SQL >> statements that run in my app. When I instead make this for example >> sqlite_master there is no problem ever. >> No idea though why this table would cause a problem. Could it be that >> one of the items in that table is a reserved word? >> >> CREATE TABLE 'SQL' >> ([STATEMENT_COUNT] INTEGER, >> [DB] TEXT, >> [QUERY_TIME] TEXT, >> [QUERY_LENGTH] REAL, >> [QUERY] TEXT) >> >> Any other ideas what could be wrong with this table? >> >> I could mail a db with that table if that would be helpful. >> >> RBS >> >> >> -Original Message- >> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] >> Sent: 02 February 2007 13:51 >> To: sqlite-users@sqlite.org >> Subject: RE: [sqlite] How do I know sqlite_get_table is finished >> >> I'll admit I'm not much of a C expert, so I'd say Guy has a much better >> handle on that part than I do. As far as your code goes I only had one >> thought, and forgive me if this is just my ignorance of VBA. I didn't >> see a >> declaration for lReturnedRows anywhere. If you don't declare a >> variable, >> what is the value of that variable if it's used before you explicitly >> assign >> a value? The reason I ask is because you only assign a value to >> lReturnedRows if the length of the error message is 0, and you only set >> the >> error flag if lReturnedRows = 0. Do you need to do: >> >> If Len(strError) = 0 then >> Else >> lReturnedRows = 0 >> End If >> >> Don't know if this would really make a difference, and sorry if it's >> just a >> waste of time, but I thought I'd suggest it. >> >> -- >> Eric Pankoke >> Founder / Lead Developer >> Point Of Light Software >> http://www.polsoftware.com/ >> >> -- Original message -- >> From: [EMAIL PROTECTED] >> > 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 out the field headers. I haven't tried >> > to compile myself yet, but I do have VC6, so it should be OK. >> > >> > Still, I don't understand why this crashes. >> > >> > RBS >> > >> > > At 07:44 2/2/2007 +, you wrote: >> > >>Sure, here it is: >> > > >> > > >> > > >> > > Looking at the C code published at >> > > http://www.tannertech.net/sqlite3vb/index.htm , it seems very risky >> - >> > > there >> > > are a lot of places it can fail to allocate memory (for example), >> and >> the >> > > use of some variables looks like it could randomly crash at any >> time... >> > > but >> > > I didn't test it, just looked at it. >> > > >> > > As far as I can understand the problem, it happens around this line: >> > > arr = GetFromDB(strSQL, lRows, strError, lDBHandle, strDB) >> > > >> > > Are you seeing a crash on the actual call to sqlite_get_table or >> only >> > > after >> > > it (when you try to use the results)? >> > > >> > > If you compiled the SQLite3VB.dll on your own, I can probably make >> some >> > > suggestions about fixing the C code of sqlite_get_table >> implementation, >> > > and >> > > maybe even some improvments (for example, a boolean flag to allow >> you to >> > > request the results array without the column headers, which I >> remember >> you >> > > asking about), and returning the number of rows immediately instead >> of >> in >> > > another function (which is somewhat dangerous), etc.). >> > > You should also probably consider using some kind of wrapper around >> > > sqlite3_exec in addition to sqlite3_get_table so you will be able to >> run >> > > commands (like pragma) without going through sqlite_get_table. >> > > >> > > >> > > Guy >> > > >> > > >> > > >> > > >> >> - >> > > To unsubscribe, send email to [EMAIL PROT
RE: [sqlite] How do I know sqlite_get_table is finished
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 out the field headers. I haven't tried to compile myself yet, but I do have VC6, so it should be OK. Still, I don't understand why this crashes. RBS > At 07:44 2/2/2007 +, you wrote: >>Sure, here it is: > > > > Looking at the C code published at > http://www.tannertech.net/sqlite3vb/index.htm , it seems very risky - > there > are a lot of places it can fail to allocate memory (for example), and the > use of some variables looks like it could randomly crash at any time... > but > I didn't test it, just looked at it. > > As far as I can understand the problem, it happens around this line: > arr = GetFromDB(strSQL, lRows, strError, lDBHandle, strDB) > > Are you seeing a crash on the actual call to sqlite_get_table or only > after > it (when you try to use the results)? > > If you compiled the SQLite3VB.dll on your own, I can probably make some > suggestions about fixing the C code of sqlite_get_table implementation, > and > maybe even some improvments (for example, a boolean flag to allow you to > request the results array without the column headers, which I remember you > asking about), and returning the number of rows immediately instead of in > another function (which is somewhat dangerous), etc.). > You should also probably consider using some kind of wrapper around > sqlite3_exec in addition to sqlite3_get_table so you will be able to run > commands (like pragma) without going through sqlite_get_table. > > > Guy > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite3VB.dll
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 >> ' >> sqlite_get_table lDBHandle, "PRAGMA synchronous=off;", strErrors >> sqlite_get_table lDBHandle, "PRAGMA encoding='UTF-8';", strErrors >> 'not sure default_cache_size applies to a memory database, probably >> not >> If bFile Then >> sqlite_get_table lDBHandle, _ >> "PRAGMA default_cache_size = 32768;", >> strErrors >> End If >> sqlite_get_table lDBHandle, "PRAGMA vdbe_trace = OFF;", strErrors >> sqlite_get_table lDBHandle, "PRAGMA page_size=4096;", strErrors >> > You might want to be careful with this code. The first pragma above > turns off synchronous disk I/O so your database is subject to corruption > by a power failure. The second sets the encoding to its default value, > UTF-8, and shouldn't be needed. The second last (vdbe_trace) is also > setting the value to its default. Furthermore this pragma can only be > turned on in a special debug build of the sqlite dll. Increasing the > cache and page size will speed up your code safely if you have the > memory to spare. > > HTH > Dennis Cote > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: AW: [sqlite] sqlite performance, locking & threading
> 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, not > to spark a debate. Many talented people gave you some of their time to > help you solve your problem and one in particular gave you a well > conceived and executed piece of software free of charge. Appreciate > their charity. > > If you have some insights which have escaped the rest of us, implement > them and present the results to the world and dazzle us. > > BTW, a major advantage of Open Source software is that you do not need > to have people explain it to you. You have the source and that explains > everything. That is particularly so with Sqlite, which is clearly > written and consequently the source reads like a book. A few minutes > with the source and grep and you have your answers. > > Emerson Clarke wrote: >> John, >> >> Um, alright then... >> >> But i think your preaching to the converted, simplifying things is >> what i always try to do. And not just when theres a problem >> >> If you followed the thread fully you would realise that there was >> never a problem with my design, though that didnt stop many people >> from chirping in and offering suggestions. >> >> The problem i had was with sqlite not being compatible with the simple >> design that i wanted. I did try several alternate designs, but only >> as a way of working around the problem i had with sqlite. It took a >> long time but eventually i managed to get someone to explain why >> sqlite had that particular problem, and i was able to modify the >> sqlite source to resolve the issue. >> >> Unfortunately no one has yet commented on my solution, or the problem >> which it addresses. Basically sqlite has thread safety checking >> routines which work a little like mutexe's. Every time you enter a >> bit of code which is potentially thread unsafe it sets a magic number, >> then resets it when it comes out. This is an attempt to detect when >> two threads are accessing the same bit of code at the same time. >> >> Clearly its not 100% reliable, and is subject to all kinds of thread >> races, but it does provide some measure of protection. Unfortunately >> though, the way it has been coded, an unbalanced safety check is >> performed in the sqlite3_step() function. >> >> This is equivalent to entering a mutex but never leaving, which causes >> deadlock in a multithreaded program. Only in this situation sqlite >> throws a misuse error any time two or more threads use sqlite3_step() >> at the same time, even if those threads are synchronised and perfectly >> safe. >> >> The easy solution is to disable the safety checks, the propper >> solution is to balance out the checks in sqlite3_step() so that users >> who actually wish to use sqlite in a multithreaded program are free to >> synchronise access to the api without error and there is still a >> reasonable level of safety checking for users who do not synchronise >> properly. >> >> >> Emerson >> >> On 1/5/07, John Stanton <[EMAIL PROTECTED]> wrote: >> >>> Work on turning "reasonable" into "adequate" or "good" and it will help >>> you get an intuitive feel for the design of programs such as yours. >>> Then your programs will be simple, fast and robust, as Einstein >>> counselled - "Make it as simple a possible, but no simpler". >>> >>> I also suggest that you take Niklaus Wirth's advice and when you run >>> into a difficulty backtrack your work and scrap everything until you >>> reach a point where there are no problems and start again from that >>> point taking a different approach having learned a lesson from your >>> previous attempt. >>> >>> By the way, I doubt whether you are using a modern operating system, it >>> is most likely to be old technology like Windows or Linux. Neither >>> supports much in the way of parallelism. >>> >> >> - >> >> To unsubscribe, send email to [EMAIL PROTECTED] >> - >> >> > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Version 3.3.9
> 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 data manipulation in SQLite. On the whole this re-write will probably make my software slightly slower (difficult to beat the array work), but the benefit is in simplicity and therefor increased maintainability. The other great benefit is that it is much easier to see what is going on as all can be seen in the SQL, which is easy to log. So, I am doing some large datadumps from Interbase to SQLite and then lots of manipulations in SQLite to get the output in the right shape to finally dump to Excel. If you are interested then I can send you the SQL output of a typical report/search. RBS > [EMAIL PROTECTED] wrote: >> Looks it has got faster in my particular situation. > > There were a few minor enhancements to the optimizer, > but nothing major. Perhaps one of those enhancements > fell right where you needed it. > > I'm curious to know what your situation is. > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Version 3.3.9
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 corruption on multi-processor systems. Other > important changes include the addition of the sqlite3_prepare_v2() > API which new users should always use in place of the legacy > sqlite3_prepare() API. A summary of the changes can be found at > >http://www.sqlite.org/changes.html > > A detailed listing of all changes can be found by consulting the > CVSTrac timeline at > >http://www.sqlite.org/cvstrac/timeline > > There are a number of important changes to winCE and win95 support. > I have no ability to test these changes. I think they work. If > not, I suppose I will be hearing about it very soon now. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: UPDATE multiple fields
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 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 supports a syntax like this: >> >> UPDATE newTable SET >> field1 = (SELECT field1 FROM oldTable T WHERE PATIENT_ID = T.PID), >> field2 = (SELECT field2 FROM oldTable T WHERE PATIENT_ID = T.PID), >> field3 = (SELECT field3 FROM oldTable T WHERE PATIENT_ID = T.PID); >> >> I'm not sure this is going to be significantly faster than the loop you >> have now. Unfortunately, SQLite doesn't support UPDATE...FROM syntax >> some other engines use, as in >> >> -- doesn't work with SQLite >> UPDATE newTable SET >> field1=T.field1, field2=T.field2, field3=T.field3 >> FROM oldTable T WHERE PATIENT_ID = T.PID; >> >> Igor Tandetnik >> >> >> - >> To unsubscribe, send email to [EMAIL PROTECTED] >> - >> >> >> > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: UPDATE multiple fields
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 supports a syntax like this: > > UPDATE newTable SET > field1 = (SELECT field1 FROM oldTable T WHERE PATIENT_ID = T.PID), > field2 = (SELECT field2 FROM oldTable T WHERE PATIENT_ID = T.PID), > field3 = (SELECT field3 FROM oldTable T WHERE PATIENT_ID = T.PID); > > I'm not sure this is going to be significantly faster than the loop you > have now. Unfortunately, SQLite doesn't support UPDATE...FROM syntax > some other engines use, as in > > -- doesn't work with SQLite > UPDATE newTable SET > field1=T.field1, field2=T.field2, field3=T.field3 > FROM oldTable T WHERE PATIENT_ID = T.PID; > > Igor Tandetnik > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] UPDATE multiple fields
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 = "UPDATE " & strNewTable & " SET " & _ strUPDATE & " = (SELECT " & arrFields(c) & _ " FROM GROUP_" & i & " T WHERE PATIENT_ID = T.PID)" RunSQLiteActionQuery2 strDB, strSQL, False, True, False, True, , True Next Next Thanks for any advice. RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Mathematical 'power' operator?
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 > lightweight embedded RDBMS role! > > It is very simple to add functions to Sqlite, and since it is a library > you link into your application there is no reason not to have your own > Sqlite-local library which adds all the functions needed by your > application. Many of the features people want to add to Sqlite are > better added by the addition of a specifically targetted application > layer. > > Those persons wanting the simplicity of Sqlite and all the functionality > of PostgrSQL might do better to re-assess their goals and save time by > using PostgreSQL and coming to terms the fact that the extra complexity > is the price to pay for the added functionality. > > In our applications we have done just that and have the advantage of > simple SQL, excellent performance and small footprint in our distributed > applications. We use PostgreSQL where its enterprise features are > necessary to handle large numbers of concurrent users. We thereby avoid > underkill and overkill. > > The add-on functions, and application interfaces are better being > contributed software than to bloat Sqlite distributions and be a boat > anchor on its continued development. > > Tom Briggs wrote: >> >> >> >>>In the case of SQLite, I (arguably) have to use a 3rd party >>>management >>>tool, for which my custom functions are no longer available. I'm >>>curious how others handle this. >>> >>>A. You don't need or use any custom SQL functionality >>>B. You don't use a 3rd party SQLite management tool >>>C. Something else I haven't thought of? >> >> >>I think that the key point you're missing here is that SQLite is not >> intended to be standalone database system like the other products you >> mentioned (Access, Oracle, etc.) - it is an embeddable database library. >> It happens to have a convenient command line interface that allows it to >> be used as a standalone database, but that's just a shell (pun intended) >> that allows you to get to the library itself. The 3rd party "front >> ends" to which you refer are really application consumers of SQLite >> itself - not add-ons to or features of SQLite. In other words: it's a >> development tool, not a database. >> >>Now, as for a "power" function: we had exactly the same need when we >> first started using SQLite. Our solution: we added it. The source code >> is freely available, after all. Adding a new function to the code is >> shockingly straightforward; from there you simply compile your version >> of the library and use that in your application(s). Quick, simple and >> portable, both across platforms and applications using your version of >> the library. >> >>-Tom >> >> >> >> >> - >> To unsubscribe, send email to [EMAIL PROTECTED] >> - >> > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Calculate years and months difference with Julian Date?
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 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 -mm-dd ?
>>
>
> Yes. That format is called ISO-8601. Everybody in the world
> is moving to it (some faster than others). You would do well
> to get on board.
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Calculate years and months difference with Julian Date?
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 -mm-dd ?
>>
> Only if you want it to work... ;)
>
> Martin
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Calculate years and months difference with Julian Date?
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 30 days, so 16 Feb 06 will give
>> 18
>> March 06 etc.
>>
>
> SELECT date('2006-01-16','+1 month');
>
> yields 2006-02-16.
>
> You must have typed something wrong.
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Calculate years and months difference with Julian Date?
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.
>
> RBS
>
>
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: 18 December 2006 01:52
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Calculate years and months difference with Julian
> Date?
>
> "RB Smissaert" <[EMAIL PROTECTED]> wrote:
>> Is it possible with Julian dates in 2 fields to calculate the months
> and/or
>> years difference between those 2 dates?
>>
>> I know I can subtract the 2 dates and divide by 30 (or 30.42) or 365 and
>> take it from there, but I would like it a bit more precise, so for
> example:
>> Is 2 March 2006 more than one month after 1 February 2006?
>> If calculated with a simple division the answer would be no, but I would
>> like it to be yes.
>>
>> Had a look at the date/time functions on the WIKI site, but couldn't see
> it.
>>
>
> 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 ...
>
> If you really need to know the number of months difference between
> A and B, then perhaps something like this:
>
> SELECT (strftime('%m',A)+12*strftime('%Y',A)) -
>(strftime('%m',B)+12*strftime('%Y',B)) ...
>
> Caution: Both of the above are off the top of my head and are
> untested. But perhaps they will give you some ideas.
> --
> D. Richard Hipp <[EMAIL PROTECTED]>
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
>
> -
>
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] GROUP BY in SQLite
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 be in the GROUP BY clause. I just tried it in Interbase and the above construction indeed doesn't work with the error: SQL error code = -104, invalid column reference Runs fine though in SQLite. Is this a known feature? RBS - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Transpose table
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 wondered if there was a better way. > > I have just found a possible way to do this and maybe it is faster. > Say I have a table with an ID column and 3 other columns. > The data in these other columns need to be grouped by ID number, so > > ID col1 col2 col3 > > would become: > > ID col1_1 col2_1 col_1 col1_2 col2_2 col3_2 col1_3 col2_3 col3_3 > > etc. where the maximum number of fields will be determined by the > maximum number of records for one ID number > > Now I found that if I do: > select > ID, > col1, > col2, > col3 > from > table > group by > ID > > Then it will always pick up the row that comes last in the group of > ID numbers. This might actually be faster than doing a subquery with MAX. > > Now if I run the above and move the data to a new table, say table2 and > then run a query like this: > > select > t1.ID, > t1.col1, > t1.col2, > t1.col3 > from > table1 t1 inner join table2 t2 on > (t1.ID = t2.ID) > where > t1.col1 < t2.col1 > group by > t1.ID > > Then I will get the rows (if there was a row left)in the ID group > that comes second from last, so > > ID > 1 > 1 > 1 > 1 < will get this one > 1 > > If I keep repeating this in a VBA loop and then join the tables I would > get my output. Not sure it is faster, but I think it might. > Will see. > > > RBS > > > > > > >> Hi RBS! >> >> If I understood you correctly you need a tool to transform these data >> just once? >> So there is a pseudocode describing one of possible approaches. To >> convenient transformation SQLite is not enough for me, I suggest to use >> any script language like Lua, Ptython, etc. >> >> 1) With a statement >> SELECT COUNT(ID) AS counter FROM old GROUP BY ID ORDER BY counter DESC >> LIMIT 1 >> Determine max number of a values >> >> 2) construct create table statement >> CREATE TABLE new( >> ID INTEGER NOT NULL UNIQUE >> for n=1, maxVal >> ", value TEXT" >> end >> ); >> and execute it >> >> 3) then navigate through 'old' table, create statements for insert data >> to 'new' >> >> >> >> But please be sure that you need exactly such transformation. It is a >> _denormalization_, almost anytime people try to perform conversion >> exactly as you describe but in reverse direction :) >> >> With a 'new' table many operation, such as adding another one value for >> ID = 1, will lead to ALTER TABLE ADD COLUMN, etc. You will come away >> from SQL logic. >> >> Regards, Denis >> >> -Original Message- >> From: RB Smissaert [mailto:[EMAIL PROTECTED] >> Sent: Thursday, December 14, 2006 10:39 AM >> To: sqlite-users@sqlite.org >> Subject: RE: [sqlite] Transpose table >> >> >> The example I gave shows exactly what I need to do. >> I have a column of ID numbers with duplicates. I have to make this >> column hold only unique ID numbers by moving the values to the first row >> where that ID number appears, with that increasing the number of >> columns. Hope this makes it a clearer. >> >> RBS >> >> -Original Message- >> From: Darren Duncan [mailto:[EMAIL PROTECTED] >> Sent: 14 December 2006 06:59 >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] Transpose table >> >> Can you please provide a use case for your example, so we know what >> you're trying to accomplish? That should help us to help you better. >> -- Darren Duncan >> >> At 12:08 AM + 12/14/06, RB Smissaert wrote: >>>I am moving my code away from VBA and transferring it to SQL. There is >>>one particular routine where I haven't found a good replacement >> for >>>and that is to transpose a table from a vertical layout to a horizontal >> one, >> >> >> >> >> - >> To unsubscribe, send email to [EMAIL PROTECTED] >> >> >> - >> >> >> >> >> >> - >> To unsubscribe, send email to [EMAIL PROTECTED] >> >> - >> >> >> - >> To unsubscribe, send email to [EMAIL PROTECTED] >> - >> >> >> > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Transpose table
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 faster. Say I have a table with an ID column and 3 other columns. The data in these other columns need to be grouped by ID number, so ID col1 col2 col3 would become: ID col1_1 col2_1 col_1 col1_2 col2_2 col3_2 col1_3 col2_3 col3_3 etc. where the maximum number of fields will be determined by the maximum number of records for one ID number Now I found that if I do: select ID, col1, col2, col3 from table group by ID Then it will always pick up the row that comes last in the group of ID numbers. This might actually be faster than doing a subquery with MAX. Now if I run the above and move the data to a new table, say table2 and then run a query like this: select t1.ID, t1.col1, t1.col2, t1.col3 from table1 t1 inner join table2 t2 on (t1.ID = t2.ID) where t1.col1 < t2.col1 group by t1.ID Then I will get the rows (if there was a row left)in the ID group that comes second from last, so ID 1 1 1 1 < will get this one 1 If I keep repeating this in a VBA loop and then join the tables I would get my output. Not sure it is faster, but I think it might. Will see. RBS > Hi RBS! > > If I understood you correctly you need a tool to transform these data > just once? > So there is a pseudocode describing one of possible approaches. To > convenient transformation SQLite is not enough for me, I suggest to use > any script language like Lua, Ptython, etc. > > 1) With a statement > SELECT COUNT(ID) AS counter FROM old GROUP BY ID ORDER BY counter DESC > LIMIT 1 > Determine max number of a values > > 2) construct create table statement > CREATE TABLE new( > ID INTEGER NOT NULL UNIQUE > for n=1, maxVal > ", value TEXT" > end > ); > and execute it > > 3) then navigate through 'old' table, create statements for insert data > to 'new' > > > > But please be sure that you need exactly such transformation. It is a > _denormalization_, almost anytime people try to perform conversion > exactly as you describe but in reverse direction :) > > With a 'new' table many operation, such as adding another one value for > ID = 1, will lead to ALTER TABLE ADD COLUMN, etc. You will come away > from SQL logic. > > Regards, Denis > > -Original Message- > From: RB Smissaert [mailto:[EMAIL PROTECTED] > Sent: Thursday, December 14, 2006 10:39 AM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Transpose table > > > The example I gave shows exactly what I need to do. > I have a column of ID numbers with duplicates. I have to make this > column hold only unique ID numbers by moving the values to the first row > where that ID number appears, with that increasing the number of > columns. Hope this makes it a clearer. > > RBS > > -Original Message- > From: Darren Duncan [mailto:[EMAIL PROTECTED] > Sent: 14 December 2006 06:59 > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Transpose table > > Can you please provide a use case for your example, so we know what > you're trying to accomplish? That should help us to help you better. > -- Darren Duncan > > At 12:08 AM + 12/14/06, RB Smissaert wrote: >>I am moving my code away from VBA and transferring it to SQL. There is >>one particular routine where I haven't found a good replacement > for >>and that is to transpose a table from a vertical layout to a horizontal > one, > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > > - > > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] faster SELECT time on second run
> 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 need to predict the users actions if your database > isn't too large. > > Simply read the entire file once using fread when you open the database. > That will load the entire file in to the OS cache so that when SQLite > starts reading the file, the required disk blocks are already in memory. > The result is the same fast lookups that you see in SQLite the second > time you run a query. The additional read is also quite fast since it > reads the file sequentially from start to finish with no seeking, > whereas SQLite will seek back and forth as it reads database pages into > memory. > > HTH > Dennis Cote > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Interbase to SQLite
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 it looks it needs installing the .NET framework and J#. >> I will keep it in mind, but for now that has put me off this route. >> >> > Yes, since Turbo C++ is based on BDS 2006 which supports C# and > Delphi.Net development. As a result it uses .Net to support the .Net SDK > in the help system used for .Net development. So the .Net framework and > SDK are prerequisites. I am fairly certain the IDE itself (i.e. editor, > project manager, compiler, etc.) does not use .Net, they were just too > lazy to remove the requirements for the new single language Turbo > versions. In fact, I think I saw some instructions for installing BDS > without the .Net framework somewhere on the web, but I don't know where. > > If you are doing straight Win32 development, and Turbo C++ only does > Win32, your application will not need .Net. > > I'm not sure why you think you needed J#, but that is not required to > the best of my knowledge. > > Dennis Cote > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Dealing with dates in the format yyyymmdd
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 working though. Still the invalid use of null error.
>>
>>
> Bart,
>
> Here is what I get:
>
> SQLite version 3.3.5
> Enter ".help" for instructions
> sqlite> select
>...> cast (
>...> julianday(
>...> substr(20061204, 1, 4) || '-' ||
>...> substr(20061204, 5, 2) || '-' ||
>...> substr(20061204, 7, 2)
>...> ) - julianday('1900-01-01')
>...> as integer
>...> ) as excel_date
>...> ;
> 39053
> sqlite>
>
> When I display the value of a cell with the formula =today() as an
> integer it shows 39055. So there seems to be an off by 2 error (or, I
> suspect, two off by one errors). One comes from the fact that excel
> displays a value of zero as the invalid date 1900-01-00. So the minimum
> legal value is 1, and therefore we need to add one to the difference
> between the julianday numbers. This gives the following:
>
> sqlite> select
>...> cast (
>...> julianday(
>...> substr(20061204, 1, 4) || '-' ||
>...> substr(20061204, 5, 2) || '-' ||
>...> substr(20061204, 7, 2)
>...> ) - julianday('1900-01-01') + 1
>...> as integer
>...> ) as excel_date
>...> ;
> 39054
> sqlite>
>
> I can' t account for the other off by one error though. You could, of
> course, just add 2 instead of 1 to get the right date from excel.
>
> HTH
> Dennis Cote
>
>
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Dealing with dates in the format yyyymmdd
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 is a small problem, the
> julianday function needs a date string in -MM-DD format, but the
> numeric calculations don't insert leading zeros for months and days less
> than 10. Here i sa modified version that does leading zero insertion.
> Even more reason to use the original substring based version.
>
> select
> cast (
> julianday(
> cast(20061204 / 1 as integer) || '-' ||
> substr('00' || (cast(20061204 / 100 as integer) % 100), -2,
> 2) || '-' ||
> substr('00' || (20061204 % 100), -2, 2)
> ) - julianday('1900-01-01')
> as integer
> ) as excel_date
> ;
>
> HTH
> Dennis Cote
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Dealing with dates in the format yyyymmdd
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_date is just a normal SQL alias name for the complicated > expression. The string 'execl_date' will be returned as the name of this > result column. This name is only used if you look at or display the > column name, like you would if you use the sqlite shell to execute the > command. > > P.S. see my subsequent post after I noticed that your datefield was an > integer raher than a string. Note that this string based version will > also work because sqlite automatically converts the integer datefield > value into a string to pass it to the substr function. > > HTH > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Dealing with dates in the format yyyymmdd
> 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 is just a normal SQL alias name for the complicated > expression. The string 'execl_date' will be returned as the name of this > result column. This name is only used if you look at or display the > column name, like you would if you use the sqlite shell to execute the > command. > > P.S. see my subsequent post after I noticed that your datefield was an > integer raher than a string. Note that this string based version will > also work because sqlite automatically converts the integer datefield > value into a string to pass it to the substr function. > > HTH > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Dealing with dates in the format yyyymmdd
> 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 >> format >> is the integer mmdd. Maybe I will need some custom SQLite function. > > What's difficult about comparing integers of the form mmdd? > > Comparing them is easy: normal integer comparison operators work just > fine. > > Date arithmetic with mmdd integers, OTOH, is more difficult; but you > can convert to seconds since start of epoch, do the arithmetic, then > convert back. > > Nico > -- > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Dealing with dates in the format yyyymmdd
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
>> the
>> days past 31 December 1899. With substr I can make it dd/mm/ (I am
>> in
>> the UK and that is the normal way to format dates) but the problem is it
>> will be displayed in Excel like mm/dd/ if that would be a possible
>> date.
>> This is due to the US date format of Excel.
>> So, would it be possible in SQLite to make a date format like this:
>> dd/mmm/ so that would be 03/dec/2006
>> This would prevent Excel from putting the month first.
>> or alternatively make it the Excel integer date format so the above date
>> would be: 39054
>>
>> I could handle the date formatting in VBA, but I would like to do as
>> much as
>> possible in SQLite as it will be faster and it would keep the code
>> neater.
>> Thanks for any advice.
>>
>>
> I saw later that your datefield was actually stored as an integer value
> (rather than a string as I had assumed) so this should work instead.
>
> select
> cast (
> julianday(
> cast(datefield / 1 as integer) || '-' ||
> (cast(datefield / 100 as integer) % 100) || '-' ||
> (datefield % 100)
> ) - julianday('1900-01-01')
> as integer
> ) as excel_date
> from mytable;
>
> HTH
> Dennis Cote
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Dealing with dates in the format yyyymmdd
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. With substr I can make it dd/mm/ (I am
>> in
>> the UK and that is the normal way to format dates) but the problem is it
>> will be displayed in Excel like mm/dd/ if that would be a possible
>> date.
>> This is due to the US date format of Excel.
>> So, would it be possible in SQLite to make a date format like this:
>> dd/mmm/ so that would be 03/dec/2006
>> This would prevent Excel from putting the month first.
>> or alternatively make it the Excel integer date format so the above date
>> would be: 39054
>>
>> I could handle the date formatting in VBA, but I would like to do as
>> much as
>> possible in SQLite as it will be faster and it would keep the code
>> neater.
>> Thanks for any advice.
>>
>>
> I saw later that your datefield was actually stored as an integer value
> (rather than a string as I had assumed) so this should work instead.
>
> select
> cast (
> julianday(
> cast(datefield / 1 as integer) || '-' ||
> (cast(datefield / 100 as integer) % 100) || '-' ||
> (datefield % 100)
> ) - julianday('1900-01-01')
> as integer
> ) as excel_date
> from mytable;
>
> HTH
> Dennis Cote
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Dealing with dates in the format yyyymmdd
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
>> days past 31 December 1899. With substr I can make it dd/mm/ (I am
>> in
>> the UK and that is the normal way to format dates) but the problem is it
>> will be displayed in Excel like mm/dd/ if that would be a possible
>> date.
>> This is due to the US date format of Excel.
>> So, would it be possible in SQLite to make a date format like this:
>> dd/mmm/ so that would be 03/dec/2006
>> This would prevent Excel from putting the month first.
>> or alternatively make it the Excel integer date format so the above date
>> would be: 39054
>>
>> I could handle the date formatting in VBA, but I would like to do as
>> much as
>> possible in SQLite as it will be faster and it would keep the code
>> neater.
>> Thanks for any advice.
>>
>> RBS
>>
>>
> This should give you the excel integer date format directll.
>
> select
> cast (
> julianday(
> substr(datefield, 1, 4) || '-' ||
> substr(datefield, 5, 2) || '-' ||
> substr(datefield, 7,2)
> ) - julianday('1900-01-01')
> as integer
> ) as excel_date
> from mytable;
>
> HTH
> Dennis Cote
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
>
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Dealing with dates in the format yyyymmdd
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 type and the second one holds a date data type. RBS > The Interbase date type is a simple integer! > > RB Smissaert wrote: >> No, these particular fields don't use the IB date type, but a simple >> integer. I have managed to get the Interbase dll ib_udf.dll working now >> and >> that has a substr function that seems to do the job. So now I can make >> 03-12-2006 type of dates and hopefully SQLite will accept that as a >> date. >> In a way it would still be better though to make Excel integer dates as >> that >> will save some trouble when dumping data in the sheets. Maybe I could >> make >> an Interbase UDF for that. Or maybe the SQLite dates are fine as well >> when >> dumped in an Excel sheet. >> >> RBS >> >> >> -Original Message- >> From: John Stanton [mailto:[EMAIL PROTECTED] >> Sent: 04 December 2006 00:41 >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] Dealing with dates in the format mmdd >> >> I suspect that you are not using the Interbase date type, which is a 64 >> bit object encapsulating date and time and having an epoch November 17, >> 1898. You seem to have some private packed format. >> >> Interbase has a rich set of date and time handling functions built in, >> provided you use the date type. >> >> You have two options in your application. The first is to just do with >> Sqlite what you did with interbase and have your own private date >> format. The second is to transform your dates into the Sqlite date >> format. It depends upon your application and reuse of legacy code. >> >> RB Smissaert wrote: >> >>>Don't know that much about it myself, but there are no functions for >>> this >>>that I know of. There are third party UDF's though and that is probably >> >> the >> >>>best way forward. We are still on Interbase 5.6 and I think the latest >>> is >>>7.5, so that might explain something. >>> >>>RBS >>> >>>-Original Message- >>>From: John Stanton [mailto:[EMAIL PROTECTED] >>>Sent: 03 December 2006 23:30 >>>To: sqlite-users@sqlite.org >>>Subject: Re: [sqlite] Dealing with dates in the format mmdd >>> >>>I know nothing of Interbase, but cannot imagine that it does not have a >>>set of date manipulation functions. >>> >>>RB Smissaert wrote: >>> >>> If I try to convert the integer Interbase date with standard SQL I get something like this: CAST(CAST((E.ADDED_DATE - (CAST(E.ADDED_DATE / 1 AS INT) * 1)) - " >>> >>>& >>> _ "CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS INT) * 1) AS INT) AS INT) || '/' || " & "CAST((E.ADDED_DATE - CAST(E.ADDED_DATE / 1 AS INT) * 1) / 100 AS INT) || '/' || " & "CAST(E.ADDED_DATE / 1 AS INT) AS VARCHAR(10)) But I haven't got it working yet. RBS -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 21:05 To: sqlite-users@sqlite.org Subject: RE: [sqlite] Dealing with dates in the format mmdd Is the date function as in: Function Overview Five date and time functions are available, as follows: 1. date( timestring, modifier, modifier, ...) ? Will give that a go. RBS -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: 03 December 2006 20:46 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Dealing with dates in the format mmdd Why not use Interbase to transform the date into a character string, like an ISI8601 date or yymmdd, dd-mm- etc and then use the Sqlite date function to insert it into Sqlite? RB Smissaert wrote: >>It looks like Interbase uses a traditional date format based on an >> >> epoch. >> > >It just does year * 1 + month * 100 + day > > >I suppose I could something like this in SQL: > >((lIBDate - ((lIBDate \ 1) * 1 + ((lIBDate - (lIBDate \ 1) > * >1) \ 100) * 100)) + _ >(153 * (((lIBDate - (lIBDate \ 1) * 1) > \ >100) + _ >12 * ((14 - ((lIBDate - (lIBDate \ 1) >* 1) \ 100)) / 12) - 3) + 2) / 5 + _ >((lIBDate \ 1) + 4800 - ((14 - >((lIBDate - (lIBDate \ 1) * 1) \ 100)) / 12)) * _ >365 + ((lIBDate \ 1) + 4800 - _ > ((14 - ((lIBDate - (lIBDate \ >1) * 1) \ 100)) / 12)) / 4 - 32083) - _ > 2415033 > >Except it looks a mess and it is one day o
RE: [sqlite] C++ SQLite
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=SqliteOdbc > http://www.ch-werner.de/sqliteodbc/ > > That way, you're not even tied to SQLite. > > >> >> RBS >> >> -Original Message- >> From: John Stanton [mailto:[EMAIL PROTECTED] >> Sent: 28 November 2006 21:58 >> To: sqlite-users@sqlite.org >> Subject: Re: [sqlite] C++ SQLite >> >> Maybe all you have to do is to make yourself a list og VB types cross >> referenced to the fundamental type used in the Win32 and Sqlite APIs. >> Then you could link any library into your VB programs. >> >> RB Smissaert wrote: >>> You might be right, but with the Win32 API you have loads of nice >>> documents/programs (I like the API guide from KPD) that help you out. >>> All I have to do is copy their declares straight to VB and look at the >> data >>> types I have to provide. Is the same available for the SQLite API? >>> >>> RBS >>> >>> -Original Message- >>> From: John Stanton [mailto:[EMAIL PROTECTED] >>> Sent: 28 November 2006 18:43 >>> To: sqlite-users@sqlite.org >>> Subject: Re: [sqlite] C++ SQLite >>> >>> If you can use the Win32 API you can use the Sqlite API. Just because >>> they can be called from C programs does not make them "all to do with >>> C". >>> >>> RB Smissaert wrote: >>> Will have a look, but I was looking for a text geared towards VB. I take >>> >>> it >>> the documentation that comes with SQLite is all to do with C. In fact I already have a wrapper that seems to work well, the one from TerraInformatica, but maybe there was more control if I could write my >>> >>> own. >>> RBS -Original Message- From: Clay Dowling [mailto:[EMAIL PROTECTED] Sent: 28 November 2006 18:19 To: sqlite-users@sqlite.org Subject: Re: [sqlite] C++ SQLite [EMAIL PROTECTED] said: > Thanks. Would you know any VB source code that wraps all the possible > declares in a class? Or do you know where to find the documentation > to make all the possible declares in VB/VBA? > > RBS The SQLite documentation will give you everything you need to write the wrapper. The sqlite.h file in the source bundle would also be a great help. You may also be able to find a wrapper already written linked on the SQLite web site. Clay Dowling >> Yes. It's a regular windows DLL, so it will behave like all other >> Windows >> DLLs. >> >> Clay Dowling >> >> [EMAIL PROTECTED] said: >> >> >>> 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? You should be able to call the Sqlite3 API directly. >>> >> >>> - To unsubscribe, send email to [EMAIL PROTECTED] >>> >> >>> - >>> >>> >>> >>> >> >>> - >>> To unsubscribe, send email to [EMAIL PROTECTED] >>> >>> >> >>> - >> -- >> Simple Content Management >> http://www.ceamus.com >> >> >> >>> >> >>> - >> To unsubscribe, send email to [EMAIL PROTECTED] >> >>> >> >>> - >> > > > >>> >> >>> - > To unsubscribe, send email to [EMAIL PROTECTED] > >>> >> >>> - >>> >>> >>> >> >>> - >>> To unsubscribe, send email to [EMAIL PROTECTED] >>> >> >>> - >>> >>> >>> >>> >>> >> >> - >>> To unsubscribe, send email to [EMAIL PRO
Re: [sqlite] C++ SQLite
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? > You should be able to call the Sqlite3 API directly. > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
