Re: [sqlite] Resources for newbies.
On Fri, Aug 1, 2008 at 4:03 PM, Rich Shepard <[EMAIL PROTECTED]>wrote: > On Fri, 1 Aug 2008, Dwight Ingersoll wrote: > > > I have a slew of newbie type questions, ... > > Er, what sort of questions? SQL, specific to SQLite, or something else? > > Rich > SQLite specific. For example, when/why would one choose to attach database files over having the scheme reside in one database file, performance considerations when databases are attached vs. single file databases. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Resources for newbies.
I have a slew of newbie type questions, and was wondering if there were other resources (preferably searchable) I could go look at before I start asking here. I'm pretty sure my questions are of the sort that makes people roll their eyes thinking "if I see that question one more time" I'd also like to learn more about FTS, but not sure where to start with that either. Thanks in advance. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to bind data to Data Grid VB6
On 10/1/07, Olaf Schmidt <[EMAIL PROTECTED]> wrote: <> > > @ Dwight: > The wrapper at phxsoftware is not ADO-compatible AFAIK. > (ADO and ADO.NET are two different animals, since > ADO is part of the COM-world as is VB6) My bad. I've been using C# lately and didn't realize there was a differentiation. The little bit i've done with the wrapper seemed similar. Thx for the heads up.
Re: [sqlite] SQL command support question
> > I was wondering if SQLite supports the command > SELECT ... INTO OUTFILE ... Can't you accomplish this in the programming language you're using to access the SQLite database? The SQLite command line utility ( http://www.sqlite.org/sqlite.html) has an option for specifying an output file.
Re: [sqlite] How to bind data to Data Grid VB6
> > I would lige to give a datagrid.datasource a recordset resulting > from a query of SQLite, but I do not know how to do, since SQLite > cannot give me a RecordSet as result (I guess...) The SQLite wrapper at http://sqlite.phxsoftware.com/ provides ADO access ( i.e. records sets) to SQLite databases.
Re: [sqlite] Can I simulate a COMMIT?
On 9/7/07, Yves Goergen <[EMAIL PROTECTED]> wrote: > > Hi, > > in a scenario when multiple operations need to be transactionally > synchronised, I have a file that must be deleted when the database > records are added successfully, but the database operations must be > rolled back, if the file cannot be deleted. Assuming the data files you are working with are of a manageable size, either read the file into a variable in your code and then process it if the file delete succeeds, or import the data file into a work table in SQLite, and then attempt the file delete.
Re: [sqlite] SQL-92 Syntax Question
On 9/7/07, Brad Stiles <[EMAIL PROTECTED]> wrote:
>
> Anyway, if I understand what you're saying, I believe what you want is an
> OUTER JOIN.
>
> SELECTT1.COLUMN, T2.COLUMN
> FROM TABLE2 T2
> outer join TABLE1 T1 on T2.COLUMN = T1.COLUMN
>
> That will get all rows from T2, and matching rows from T1. If no row in
> T1 exists, then T1.COLUMN will be null.
Thanks for pointing me in the right direction. I created a test database
and tables, and was able to figure it out. Turns out LEFT JOIN does exactly
what I was looking for. I'm pasting in the text below just in case anyone
else finds it useful (the concept more than the SQL syntax).
DROP TABLE ACCOUNT;
DROP TABLE IMPORT;
CREATE TABLE ACCOUNT(PKEY VARCHAR(10));
CREATE TABLE IMPORT(PKEY VARCHAR(10));
INSERT INTO ACCOUNT VALUES('E1');
INSERT INTO ACCOUNT VALUES('E2');
INSERT INTO ACCOUNT VALUES('E3');
INSERT INTO ACCOUNT VALUES('E4');
INSERT INTO ACCOUNT VALUES('E5');
INSERT INTO IMPORT VALUES('E1');
INSERT INTO IMPORT VALUES('E6');
INSERT INTO IMPORT VALUES('E3');
INSERT INTO IMPORT VALUES('E7');
INSERT INTO IMPORT VALUES('E5');
SELECT T1.PKEY, T2.PKEY FROM IMPORT T2 LEFT JOIN ACCOUNT T1 ON T2.PKEY =
T1.PKEY;
Thanks again for the tip.
Re: [sqlite] New Operator Support
On 9/7/07, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote: > > Hi, > > Its to get involved in the development of sqlite.If possible > move to contrib section.a dream. If that's the case, I think the first step is to post your proposal, and get input from the SQLite community as to whether the modification would be useful enough to be included in the base distribution.
[sqlite] SQL-92 Syntax Question
I learned SQL before the SQL-92 standard was in place. The database engines I used (SQL Server and Oracle) have their own proprietary methods for indicating forced inner outer joins. I haven't looked at this code in over 10 years, so I'm a little unclear about exactly what the result set returned was, and I don't have a database in place to play with these as yet. The SELECT I have is: SELECTT1.COLUMN, T2.COLUMN FROMTABLE1T1, TABLE2T2 WHERE T1.COLUMN *= T2.COLUMN In SQL Server, the *= indicates a forced inner join which would cause a record to be generated regardless if it existed in the T1 table or not. The result set generated (if I remember correctly) would set the T1.COLUMN to null if it did not appear in the T1 table and both columns would be populated if the record existed in both tables. Changing the *= to =* caused the reverse of this. So my questions are how would this be expressed in SQL-92 (or more to the point SQLite) syntax, and am I mistating the result set that would be returned by this (there was another post in the mailing list on how to determine whether a record existed in a primary table or not, and I didn't want to spread bad advice). Thanks in advance. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQL approach to Import only new items, delete other items
On 9/6/07, Andre du Plessis <[EMAIL PROTECTED]> wrote: > Im importing data > > The data has a unique value, call it MD5 for now that could be a unique > value for the data. > > > > Each record that gets imported is converted to MD5, a lookup is done on > the table for that MD5, > > if found it must leave it alone, if not found it must insert a new > record... <> A SQL approach to this would be to force a left inner join creating a result set which would indicate which records currently exist/does not exist in the primary table. So in the result set, if both columns have values, then the record exists in the primary table and needs to be updated, if the column from the primary table is null, then it doesn't exist and must be inserted. To determine which records need to be deleted, force a right outer join, and use similar logic to determine which records need to be deleted. Unfortunately the SQL dialects I learned were prior to SQL-92, and my examples use the database engine proprietary syntax to indicate forced inner/outer joins, so I can't give you an example. I'm going to post another message asking what the correct syntax would be in SQL-92 terms.
Re: [sqlite] Problem opening a new SQLite3 database file
On 8/23/07, Dennis Achá <[EMAIL PROTECTED]> wrote: > I cannot open a new SQLite3 database file through the command prompt. In > the > windows "run" window, I type "SQLite3 mydatabase.db3" and I get the > following error > message: <> Using Start Menu/Run is not the most flexible way of running the SQLite command line utility. The error message you are getting is being generated because the directory where you placed the SQLite files is not defined in your PATH variable so they cannot be found from the Run window. To modify the PATH variable in XP: 1. Right click on My Computer. 2. Select 'Properties' 3. Click the 'Advanced' tab. 4. Locate the 'Environment Variables' button and click it. 5. On the bottom half of the screen under 'System Variables' select 'Path' (you may have to scroll down to find the the Path variable). 6. Click the 'Edit' button. 7. Add the directory where you placed the SQLite files (make sure you include a semicolon (;) before the new entry). While not advisable, you can also copy/move the SQLite files to a directory already defined in the PATH variable and running the SQLite3.exe should work. I haven't worked with Win98 in a long time so I'm not sure where the Environment Variables settings are located for that OS.
Re: [sqlite] example of import/copy for Windows?
On 8/21/07, Preston, Brian <[EMAIL PROTECTED]> wrote: <> > Does anyone have an example of doing an import from a .sql file into an > existing sqlite3 db? I've tried: > > sqlite3 .import > > sqlite3 .import | > > sqlite3 .import > > sqlite3 | .import > > and various other permutations. It usually give me an error saying > 'syntax error at' the start of my <> I'm assuming you're including the greater/less than signs in the above commands. These have special significance to the command shell and should be omitted. Also, you've reversed the parameters, so assuming that your using your C: drive, your database is named TEST.DB in a directory called DATABASE, and that your datafile is called INPUTDATA.SQL in a directory called DATA, the command should look like: sqlite3 C:\DATABASE\TEST.DB < C:\DATA\INPUTDATA.SQL According to what I read, the .dump command includes both the DDL to create your database schema as well as the data within the tables. I don't have my database far enough along to confirm this. >From reading your message, it appears that you're not familiar with working at the command line (in your example, it seems your confusing what commands are available at the command line, and what commands are available once you've invoked the sqlite3 utility). I think it would be beneficial if you read up on the subject on Google, or located a book on the subject. O'Reilly books has an excellent book on the NT command shell, which hasn't changed since that release, so it will still apply no matter what Windows OS you're using.
Re: [sqlite] Unique ids for each record
On 8/20/07, Sreedhar.a <[EMAIL PROTECTED]> wrote: <> > > I want to restrict the Artist range to 100 to 199 so that with the id > I can make my search fast and also I knew with unique id that I need to > search for the Artist alone. <> Considering the small range of data it appears you want to deal with, SQLite should be more than capable of quickly returning the results you desire, especially since it appears that you're using a numeric field as primary keys. It would be helpful to understand why you chose this approach, and the source of your data. The reason I'm asking is because the project I'm currently working on is an offline viewer which utilizes the data available from the FreeDB Organization.
Re: [sqlite] [Visual Basic] How do you work with SQLite?
On 8/9/07, Gilles Ganault <[EMAIL PROTECTED]> wrote: > > Hello > > I was wondering: how do you VB developpers work with SQLite? > > Currently, I use a variant array that I fill with data from SQLite, and > use > ComponentOne's grid object to display the data through its LoadArray() > method; If/once the data is updated by the user, I write the array back to > SQLite. > > Do you use ADO et al? Other ways to connect VB and SQLite? I've been using the System.Data.SQLite ADO.NET provider from http://sqlite.phxsoftware.com/ It seems to be actively developed, and the author seems to be fairly responsive on his forums.
RE: [sqlite] File Permission and Busy Handler Issue in SQLite
--- Kiran <[EMAIL PROTECTED]> wrote: > This make me suspect that there is some point which > I am missing or may be it is a defect in SQLite. I wouldn't necessarily call a product defective simply because it didn't behave in the manner you expected or wished it to. Considering what I've seen when other applications have encountered similar issues, I find it encouraging that the engine simply went to sleep leaving the database intact. > Now suppose my Linux system already has a DB > with "444" permission (say I copied from another > Linux machine) and tried running my application, > then I want to trap the read-only (unable to write) > scenario. This doesn't explain why you expect that the file permissions may change unexpectedly. While modifying the source code to handle the situation is a solution, the *Nix environment already provides these tools for you. Start your application in a shell script that checks the file permissions before launching your application. If you're trying to prevent others from changing the permissions on your database, then create a user strictly for the process accessing the SQLite database, and ensure that the permissions are set prohibiting other users from making modifications to the DB file. Yahoo! oneSearch: Finally, mobile search that gives answers, not web links. http://mobile.yahoo.com/mobileweb/onesearch?refer=1ONXIC - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Creating index takes over an hour
> Anyone know a simple Windows command line equivalent > of the cat to dev null command above to put a file > into OS cache? The command would be: type filename.db > nul but I'm pretty sure that this does not work the same way under Windows as it does in *Nix. Shape Yahoo! in your own image. Join our Network Research Panel today! http://surveylink.yahoo.com/gmrs/yahoo_panel_invite.asp?a=7 - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Is SQLite Case Sensitive?
--- Lee Crain <[EMAIL PROTECTED]> wrote: > I am working on an application where I am importing > data for which great care has NOT been taken to > ensure uppercase and lowercase letters have been > entered appropriately. Just a suggestion: This sounds like it's a candidate for some data scrubbing and cleanup rather than trying to code for a lot of 'what if' scenarios, especially since you indicate that the data is pretty freeform. It will probably save a lot of development time and make future debugging easier if your data is in a known consistent state rather than the currently somewhat random state you implied. Sick sense of humor? Visit Yahoo! TV's Comedy with an Edge to see what's on, when. http://tv.yahoo.com/collections/222 - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Security Problem C/C++
> Is there any way to request the digest form of the > mailing list? Subscribe to the digest using: [EMAIL PROTECTED] Luggage? GPS? Comic books? Check out fitting gifts for grads at Yahoo! Search http://search.yahoo.com/search?fr=oni_on_mail&p=graduation+gifts&cs=bz - To unsubscribe, send email to [EMAIL PROTECTED] -
