Re: [sqlite] Changing Table Contents
On Wed, Jul 1, 2009 at 9:50 PM, Rick Ratchford wrote: > From what I understand, ANYTIME you do a SQL statement, such as "SELECT...", > you are doing this to a TABLE and returning the result in a sort of > 'recordset'. > > So then, the table is this sqlite_master, the field is tbl_name, and the > result of the SQL query can be had from the recordset it returns. Right? That is correct. I think that, to be very pedantic, "column" might be a better term than "field" for tbl_name. > So using VB and my wrapper, with SQLite, it is... > > Set Rs = Cnn.OpenRecordset(SQLString) > > I did this and it worked! The returned recordset has a field named after the > function, called count(*). Here's another trick you can do too if you want to give specific names to the resulting columns: SELECT count(*) AS my_count FROM sqlite_master WHERE tbl_name = 'Foo'; > So I guess the answer I was looking for as to where to find the returned > result is "RECORDSET". It wasn't that obvious to me until a few minutes ago. Glad you found it! :-) If you're still new to SQL and want to delve more into theory, you could search Google for "normal forms" (e.g. database normalization)... but that may not be too important until you start working with larger or complex data. -David ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Changing Table Contents
Although I'm going to use the easier DROP TABLE IF EXISTS... line as this works wonderfully. The only reason I asked about the other method you mentioned is that I wanted to at least understand how it worked. The more one knows, the easier it gets later on. Anyway, I think I may know how the value is returned. I have to assume that you thought I was looking for a VB solution. Actually, I'm still brand new at this so sometimes the answer is something obvious to others. >From what I understand, ANYTIME you do a SQL statement, such as "SELECT...", you are doing this to a TABLE and returning the result in a sort of 'recordset'. So then, the table is this sqlite_master, the field is tbl_name, and the result of the SQL query can be had from the recordset it returns. Right? So using VB and my wrapper, with SQLite, it is... Set Rs = Cnn.OpenRecordset(SQLString) I did this and it worked! The returned recordset has a field named after the function, called count(*). So I guess the answer I was looking for as to where to find the returned result is "RECORDSET". It wasn't that obvious to me until a few minutes ago. Thanks David. (and Simon for your comments as well). Thank you again. Rick -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of David Baird Sent: Wednesday, July 01, 2009 9:05 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Changing Table Contents On Wed, Jul 1, 2009 at 6:05 PM, Rick Ratchford wrote: > I'm using a VB wrapper, and so I run this by... > > Cnn.Execute "Select count(*) FROM sqlite_master WHERE tbl_name = > 'DeltaGrid'" > > Thing is, I don't know where to check for the return value. I'm afraid I can't help with this - I don't have Visual Basic experience :-( ___ 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] Changing Table Contents
On Wed, Jul 1, 2009 at 6:05 PM, Rick Ratchford wrote: > I'm using a VB wrapper, and so I run this by... > > Cnn.Execute "Select count(*) FROM sqlite_master WHERE tbl_name = > 'DeltaGrid'" > > Thing is, I don't know where to check for the return value. I'm afraid I can't help with this - I don't have Visual Basic experience :-( ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Changing Table Contents
On 2 Jul 2009, at 1:22am, Rick Ratchford wrote: > Okay, I found what needed to be added to DROP TABLE to make it not > produce > the error. > > DROP TABLE IF EXISTS Foo > > That did the trick. > > Still haven't figured out how to get the test result from... > > SELECT count(*) FROM sqlite_master WHERE tbl_name = 'Foo'; > > If this returns 1 or 0 based on whether there is a table called > 'Foo' in the > sqlite_master, where does one check for that 1 or 0? You no longer care. Each time you want to start again do these two commands: DROP TABLE IF EXISTS foo; CREATE TABLE foo ... (whatever your requirements are) Neither will give an error. The second command will always result in an empty table. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Changing Table Contents
Okay, I found what needed to be added to DROP TABLE to make it not produce the error. DROP TABLE IF EXISTS Foo That did the trick. Still haven't figured out how to get the test result from... SELECT count(*) FROM sqlite_master WHERE tbl_name = 'Foo'; If this returns 1 or 0 based on whether there is a table called 'Foo' in the sqlite_master, where does one check for that 1 or 0? Thanks! Rick -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Rick Ratchford Sent: Wednesday, July 01, 2009 7:06 PM To: 'General Discussion of SQLite Database' Subject: Re: [sqlite] Changing Table Contents DROP TABLE Foo; -- It's okay to execute this command, even if Foo does not exist already. Hello David. Thanks for your reply. The above "Drop Table" created an error when I tried to run it when no table existed. CREATE TABLE Foo ( ... ); Forgive my novice ignorance. Although I have no trouble executing this SQL without an error, where do I look for the return value? I'm using a VB wrapper, and so I run this by... Cnn.Execute "Select count(*) FROM sqlite_master WHERE tbl_name = 'DeltaGrid'" Thing is, I don't know where to check for the return value. Thank you. Rick ___ 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] Changing Table Contents
DROP TABLE Foo; -- It's okay to execute this command, even if Foo does not exist already. Hello David. Thanks for your reply. The above "Drop Table" created an error when I tried to run it when no table existed. CREATE TABLE Foo ( ... ); Forgive my novice ignorance. Although I have no trouble executing this SQL without an error, where do I look for the return value? I'm using a VB wrapper, and so I run this by... Cnn.Execute "Select count(*) FROM sqlite_master WHERE tbl_name = 'DeltaGrid'" Thing is, I don't know where to check for the return value. Thank you. Rick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Changing Table Contents
On Wed, Jul 1, 2009 at 5:29 PM, Rick Ratchford wrote: > 1. Determine if the table has already been created due to a prior run. > > 2. If so, to remove the information currently in that table and replace it > with new information. > > I'm not sure how to determine whether the table already exists. > > If it does exist, I suppose I can then use the SQL Delete to delete all the > records and then write the new stuff. If this is not the way to do it, maybe > someone can suggest the proper way. Well, here are a few commands that may give you some ideas, and you can see what works best for you Here is how you can use sqlite_master to determine if a table exists: SELECT count(*) FROM sqlite_master WHERE tbl_name = 'Foo'; -- returns 1 if table exists, 0 otherwise If you want to drop your table and then create it, you could do this: DROP TABLE Foo; -- It's okay to execute this command, even if Foo does not exist already. CREATE TABLE Foo ( ... ); Here you can see whether or not a table contains any rows at all: SELECT count(*) FROM Foo; -- returns 0 if table is empty If you want to clean out your table: DELETE FROM Foo; -- This will delete all rows from Foo If you just want to update some already existing data: UPDATE Foo SET column2 = 33 WHERE column1 = 1; -- This lets you update (modify) information that already exists in the table -David ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Changing Table Contents
Language: VB6 In my project, I create a Table that holds specific information based on a User's selection. When the user runs a new selection, my procedure that creates this table is run again to recreate the table but with new information. However, the problem I have is that since the table name is constant (DataTable), running the procedure again will simply cause a "Table already exists" error. What I need to do is to: 1. Determine if the table has already been created due to a prior run. 2. If so, to remove the information currently in that table and replace it with new information. I'm not sure how to determine whether the table already exists. If it does exist, I suppose I can then use the SQL Delete to delete all the records and then write the new stuff. If this is not the way to do it, maybe someone can suggest the proper way. I've seen some routines where you try to create a RS from the Table in order to test if it exists. Is this how it is done for SQLite? Thanks. Rick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
