Re: [sqlite] About Alter table add column
Hi, If you are specifing fieldnames in your statements then there won't be any problem. Means if a statement like INSERT INTO Person (ID, name , addr ) VALUES ( 10,'Yoky','India'); will not be distrubed. But if your other code contains INSERT INTO Person VALUES ( 10,'Yoky','India'); then you will have to insert the newly added column to those statements. ALWAYS USE PARAMETRISED SQL STATEMENTS to avoid these type of conditions. Rajesh Nair. On 2/5/09, yoky wrote: > > Hi all, >I have a question about alter table. for example, I create a table > person(ID integer primary key, name text, addr text), > Then I want to upgrade the table and add some columns, "alter table person > add age integer", I want to know the changed > table structure will have a big effect on the after DB operation (like > Insert, Select) or not? Suppose the table already > has many many rows. > Thanks! > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Regards Rajesh Nair ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this expected behavoir, or should I open a ticket?
Never mind, brain glitch. Sorry for the waste of bandwidth. Just too late on a Friday afternoon. Noah -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik Sent: Friday, February 06, 2009 4:14 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Is this expected behavoir, or should I open a ticket? Noah Hart wrote: > SQL Version: 3.6.10 > > CREATE TABLE T1(a, b); > INSERT INTO T1 VALUES(C1_A, randomblob(1)); > > SQL error:no such column: C1_A > > I know that I can quote the value C1_A, but why is that necessary? Why do you believe it shouldn't be necessary? C1_A is an identifier, not a string literal. What is this identifier supposed to name? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Is this expected behavoir, or should I open a ticket?
Noah Hart wrote: > SQL Version: 3.6.10 > > CREATE TABLE T1(a, b); > INSERT INTO T1 VALUES(C1_A, randomblob(1)); > > SQL error:no such column: C1_A > > I know that I can quote the value C1_A, but why is that necessary? Why do you believe it shouldn't be necessary? C1_A is an identifier, not a string literal. What is this identifier supposed to name? Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is this expected behavoir, or should I open a ticket?
SQL Version: 3.6.10 CREATE TABLE T1(a, b); INSERT INTO T1 VALUES(C1_A, randomblob(1)); SQL error:no such column: C1_A I know that I can quote the value C1_A, but why is that necessary? Thanks, Noah CONFIDENTIALITY NOTICE: This message may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose, or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trying to INSERT rows into a table
Good day, I'm new to SQL lite too, but I don't see the point of reset for a one-off insert query or one that gets finalized right away. Insert has no row set, so you aren't about to reset and step through the rowset again. As always, I'm willing to be enlightened My code runs successive inserts without an apparent problem so far. Adam On Fri, Feb 6, 2009 at 3:57 PM, Stephen Abbamonte wrote: > Because the SDK docs tell me to. > http://www.sqlite.org/c3ref/step.html > > > " SQLITE_DONE means that the statement has finished executing successfully. > sqlite3_step() should not be called again on this virtual machine without > first calling sqlite3_reset() to reset the virtual machine back to its > initial state." > > > > -Original Message- > From: sqlite-users-boun...@sqlite.org > [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Adam DeVita > Sent: Friday, February 06, 2009 2:57 PM > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Trying to INSERT rows into a table > > Why are you calling reset after step? > > regards, > Adam > > On Fri, Feb 6, 2009 at 3:51 PM, Stephen Abbamonte > wrote: >> Hi all I am using SQLite3 and running into a bit of a snag. I have a >> Database with an empty table and I am calling a series of INSERT commands >> during the execution of my program but it seems that each call is >> overwriting the first row in the table. I have tried the table with and >> without a PRIMARY KEY and I get the same result. The call to sqlite3_step >> with my INSERT command returns SQLITE_DONE so I call sqlite3_reset first >> then I call sqlite3_finalize. Then I prepare a new statement > and >> do these steps again. If anyone can help I would greatly appreciate it. >> Thanks. >> >> >> >> Stephen Abbamonte >> >> Software Engineer >> >> Tornado Studios >> >> >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > > > > -- > VerifEye Technologies Inc. > 905-948-0015x245 > 7100 Warden Ave, Unit 3 > Markham ON, L3R 8B5 > Canada > ___ > 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 > -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trying to INSERT rows into a table
Because the SDK docs tell me to. http://www.sqlite.org/c3ref/step.html " SQLITE_DONE means that the statement has finished executing successfully. sqlite3_step() should not be called again on this virtual machine without first calling sqlite3_reset() to reset the virtual machine back to its initial state." -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Adam DeVita Sent: Friday, February 06, 2009 2:57 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] Trying to INSERT rows into a table Why are you calling reset after step? regards, Adam On Fri, Feb 6, 2009 at 3:51 PM, Stephen Abbamonte wrote: > Hi all I am using SQLite3 and running into a bit of a snag. I have a > Database with an empty table and I am calling a series of INSERT commands > during the execution of my program but it seems that each call is > overwriting the first row in the table. I have tried the table with and > without a PRIMARY KEY and I get the same result. The call to sqlite3_step > with my INSERT command returns SQLITE_DONE so I call sqlite3_reset first > then I call sqlite3_finalize. Then I prepare a new statement and > do these steps again. If anyone can help I would greatly appreciate it. > Thanks. > > > > Stephen Abbamonte > > Software Engineer > > Tornado Studios > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ 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] Trying to INSERT rows into a table
Why are you calling reset after step? regards, Adam On Fri, Feb 6, 2009 at 3:51 PM, Stephen Abbamonte wrote: > Hi all I am using SQLite3 and running into a bit of a snag. I have a > Database with an empty table and I am calling a series of INSERT commands > during the execution of my program but it seems that each call is > overwriting the first row in the table. I have tried the table with and > without a PRIMARY KEY and I get the same result. The call to sqlite3_step > with my INSERT command returns SQLITE_DONE so I call sqlite3_reset first > then I call sqlite3_finalize. Then I prepare a new statement and > do these steps again. If anyone can help I would greatly appreciate it. > Thanks. > > > > Stephen Abbamonte > > Software Engineer > > Tornado Studios > > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- VerifEye Technologies Inc. 905-948-0015x245 7100 Warden Ave, Unit 3 Markham ON, L3R 8B5 Canada ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Trying to INSERT rows into a table
Hi all I am using SQLite3 and running into a bit of a snag. I have a Database with an empty table and I am calling a series of INSERT commands during the execution of my program but it seems that each call is overwriting the first row in the table. I have tried the table with and without a PRIMARY KEY and I get the same result. The call to sqlite3_step with my INSERT command returns SQLITE_DONE so I call sqlite3_reset first then I call sqlite3_finalize. Then I prepare a new statement and do these steps again. If anyone can help I would greatly appreciate it. Thanks. Stephen Abbamonte Software Engineer Tornado Studios ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] TRIGGER to catch UPDATES without a WHERE clause
Hi Puneet, I don't know if you can intercept a query with a trigger the way you're intending (maybe this is possible in sqlite?), but if I may make a suggestion, get in the habit of starting your every session on a crucial database with a transaction. The moment you login: sqlite> BEGIN; Take a good hard look at the number of rows affected after every operation, check all the data before you end your session, and then either commit it, or rollback the changes: sqlite> COMMIT; sqlite> ROLLBACK; Hope that helps (Time Machine certainly rocks, yeah?) Cheers, Billy On Fri, Feb 6, 2009 at 8:06 AM, P Kishor wrote: > I had asked this a while back but I think my query got Warnocked. > > I would like to create a TRIGGER (or a CONSTRAINT) that throws an > error if an UPDATE query is run *without* a WHERE clause. This would > prevent accidentally mucking up the entire database, which, believe > you me, I have done more than once, and have been saved only by the > magic of Time Machine. > > The TRIGGER would allow a full UPDATE of the table only by using some > special syntax in the query... like say, by adding something like > WHERE 0=0; > > > > -- > Puneet Kishor > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Billy Gray wg...@zetetic.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Insertion and Updation using sqlite3_prepare()
I second that motion, there's a possibility here that you might be mixing up integers and strings either when you bind for the insert or when you bind for the select later to check if the insert worked! On Fri, Feb 6, 2009 at 12:22 AM, Igor Tandetnik wrote: > "hussainfarzana" > wrote in > message > news:21866676.p...@talk.nabble.com > > Below is the query we used where all the fields are numeric and it is > > prepared once: > > > > INSERT INTO ShopDataNum values(?,?,?,?,?,?,?,?,?,?,?) > > > > When we bind the values the steps were returning the correct value > > but the record is not found in the DB. > > > > Then we tried by passing the static value for the first field in > > sqlite3_prepare and we bind the values. > > INSERT INTO ShopDataNum values(960002,?,?,?,?,?,?,?,?,?,?) > > The steps were executed correctly and the record is inserted > > correctly in the DB. > > > > Again we tried the first query and assigned the static value for the > > first field while binding the values, the steps were executed > > correctly but the record is not found in the DB. > > Can you show a small complete code sample that reproduces the problem? > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Billy Gray wg...@zetetic.net ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] TRIGGER to catch UPDATES without a WHERE clause
I had asked this a while back but I think my query got Warnocked. I would like to create a TRIGGER (or a CONSTRAINT) that throws an error if an UPDATE query is run *without* a WHERE clause. This would prevent accidentally mucking up the entire database, which, believe you me, I have done more than once, and have been saved only by the magic of Time Machine. The TRIGGER would allow a full UPDATE of the table only by using some special syntax in the query... like say, by adding something like WHERE 0=0; -- Puneet Kishor ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to drop table in trigger
On Feb 6, 2009, at 3:23 AM, Wenton Thomas wrote: > could anyone help me ? > The sqlite version i usedis 3.5.0. You cannot make schema changes while an SQL statement is running. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to drop table in trigger
could anyone help me ? The sqlite version i usedis 3.5.0. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Error message This program cannot be run in DOS
Hi, The file seems to be perfect. I am sorry, I omitted some thing. Right click the ZIP file and select the "Properties" menu item and then examine the window displayed. You can see the "UNBLOCK" button on that window. Generally XP blocks the downloaded ZIP,EXE,DLL,... files and some email-attachments from executing. You can download but can't execute it. So before unzipping unblock it. Rajesh Nair. On 2/4/09, Richard Hardwick wrote: >> selecting the UNBLOCK button from the window displayed, and unzip and > Well I dont seem to have an unblock button > Here is what I did > > I downloaded > sqlite-3_6_10.zip 245,575 bytes > which unzipped to > sqlite3.exe501,456 > > The start of file sqlite3.exe looks like this > 4D 5A 90 00 03 00 00 00 04 00 00 00 FF FF 00 00 > B8 00 00 00 00 00 00 00 40 00 00 00 00 00 00 00 > 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 > 00 00 00 00 00 00 00 00 00 00 00 00 80 00 00 00 > 0E 1F BA 0E 00 B4 09 CD 21 B8 01 4C CD 21 54 68 > 69 73 20 70 72 6F 67 72 61 6D 20 63 61 6E 6E 6F > 74 20 62 65 20 72 75 6E 20 69 6E 20 44 4F 53 20 > 6D 6F 64 65 2E 0D 0D 0A 24 00 00 00 00 00 00 00 > > That is > > MZ.. > . > . > . > ...Th > is program canno > t be run in DOS > mode > > And indeed "sqlite3.exeEX1.DB" at the dos prompt returns > "This program cannot be run in DOS mode". > I'm still stuck > > Richard H > >> Message: 4 >> Date: Wed, 4 Feb 2009 17:28:35 +0530 >> From: Rajesh Nair >> Subject: Re: [sqlite] Error message This program cannot be run in DOS >> mode >> To: General Discussion of SQLite Database >> Message-ID: >> >> Content-Type: text/plain; charset=ISO-8859-1 >> >> Is it sqlite3.ex1 or sqlite3.exe ? >> >> Before unziping, Unlock the downloaded file by righ-clicking it an >> selecting the UNBLOCK button from the window displayed, and unzip and >> then try. Unblock the EXE and the DLL file also, if needed. >> >> Rajesh Nair. >> >> On 2/4/09, Richard Hardwick wrote: >> > Stupid Newbie problem. I'm running WinXP. >> > I want to make a little database. >> > So at http://www.sqlite.org/download.html >> > (Precompiled Binaries For Windows) >> > I downloaded sqlite-3_6_10.zip >> > >> > Unzipped it to sqlite3.exe. >> > At DOS prompt I did >> > sqlite3 ex1 >> > And I got >> > "This program cannot be run in DOS mode". >> > >> > Googled for help and found >> > http://jroller.com/obie/entry/installing_sqlite_3_on_windows >> > Followed their suggestion. >> > Downloaded sqlitedll-3_6_10.zip and unzipped to >> > sqlite3.def and sqlite3.dll >> > >> > But still 'sqlite3 ex1' gives me "This program cannot be run in DOS >> > mode". >> > I'm stuck. >> > Your help much appreciated >> > rch >> > >> > >> > >> > ___ >> > sqlite-users mailing list >> > sqlite-users@sqlite.org >> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > >> >> >> -- >> Regards >> Rajesh Nair >> >> >> >> > > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Regards Rajesh Nair ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
