[sqlite] Suppressing column (field) headers?
New to SQL, SQLite, and the Delphi wrapper I'm using to talk to sqlite.dll. A couple of questions: 1. How can I get the dll to return JUST the desired data? When I send the following SQL, for example, select colEntry from tblEntry where colNum = 3 the textbox shows colEntry "The text in the column." What I really want is just: The text in the column. No header, no quotes, and no newline. Just data. I get the impression the dll is sending all this back, not that the Delphi code is adding it. If so, any way to supress, or must I strip the unneeded stuff off? 2. The Delphi setup is using the 2.something dll. Can I drop in the 3.something dll and change the 'sqlite.dll' line to 'sqlite3.dll' and expect it to work the same? If so, would there be any advantages? Thanks
[sqlite] Is it possible to BIND in a CREATE TABLE
I have a CREATE TABLE sql command with DEFAULT values. I would like to BIND the default values, but it seems it is not supported. Is this so? I am using SQLITE3.. Any help appreciated. Randall Fox
Re: [sqlite] DATABASE SCHEMA HAS CHANGED
On Sat, 20 Nov 2004 08:08:48 -0500, you wrote: >So to answer your questions: > > No, this is not a serious bug. You just need to be prepared to > reissue any SQL statement that returns SQLITE_SCHEMA. > > Yes, this issue is fixed in version 3.0. Thank you.. It seems the original poster didn't say what version, and a follow up mentioned the sqlite3 structure, so I assumed it was v3. Good to know it is not a problem. Randall Fox
RE: [sqlite] sqlite project--working with table structure
-Original Message- From: Darren Duncan [mailto:[EMAIL PROTECTED] Sent: Saturday, November 20, 2004 3:14 PM To: [EMAIL PROTECTED] Subject: RE: [sqlite] sqlite project--working with table structure -- Darren Duncan ok Darren. Thank you and the other posters for helping. I will think about everything I read today before trying to proceed any further with this very small project. I didn't write a line of code yet so hopefully I will get the design right before I start doing that. good luck with your ongoing SQL::Routine project. thanks again, jim
Re: [sqlite] upgrade?
Hi Michael, Michael Hunley wrote: Hi, I am currently using SQLite v 2.8.13 for a commercial product for Palm Handhelds. I am noticing some performance issues; most notably when I do a database validate() to verify my integrity at app open and close. I see that version 3.0.8 has some code optimizations, but am unsure if upgrading from the 2.8 series to the 3.0 series is just a drop in or if my code will need to change. Can anyone tell me if the API has changed in a notable way? The API has changed slightly. Mostly it's a matter of changing the sqlite_ prefix to sqlite3_, but you also need to change the name of the structure which you pass to sqlite3_step, and a few other changes. I can send you two pieces of code that show "before" and "after" use if you contact me off-list. Also, is my performance going to improve with the 3.0.8 over 2.8.13? If not, what about 2.8.15? I experienced a performance increase when I moved from 2.8.13 to 2.8.15, but a performance decrease when moving from 2.8.13 to 3.0.7. A recent thread on this list deals with this perceived decrease in performance (the thread is from around 5 October 2004, entitled "Degradation of performance in SQLite3?" -- you can find it in the archives, which are linked to from www.sqlite.org). PLEASE note that some people seem to experience performance *increases* when going from SQLite 2.8 to 3.0, so Your Mileage May Vary. Basically, Dr. Hipp said back then that SQLite 3 uses less disk space (and so fewer disk reads) at the expense of using more CPU cycles. This may or may not give you a performance increase, and in my case, it gave me a performance decrease, probably because the databases I have are so small that the operating system (Linux in my case) can map most of the file into virtual memory both for 2.8 and 3.0, and so the usage of more CPU cycles in 3.0 gives an overall performance decrease. However, there are several other good reasons to move to SQLite 3 than performance issues, and I'm sure that others can fill in the details. HTH. Cheers, Ulrik
[sqlite] upgrade?
Hi, I am currently using SQLite v 2.8.13 for a commercial product for Palm Handhelds. I am noticing some performance issues; most notably when I do a database validate() to verify my integrity at app open and close. I see that version 3.0.8 has some code optimizations, but am unsure if upgrading from the 2.8 series to the 3.0 series is just a drop in or if my code will need to change. Can anyone tell me if the API has changed in a notable way? Also, is my performance going to improve with the 3.0.8 over 2.8.13? If not, what about 2.8.15? thanks! Michael Hunley Managing Partner/Senior Engineer PocketPurchase Consultants
RE: [sqlite] sqlite project--working with table structure
At 5:40 AM -0500 11/20/04, j-marvin wrote: Darren, this is cool. I can't wait to pass this along to a person at my job who works with DB's at his full-time job. Thanks, I appreciate it. Note also that another release of SQL::Routine should be posted on CPAN either today or tomorrow. I had thought wouldn't it be nice if people would agree on the table structue of the create table statement within the sqlite community for all these sqlite db tools popping up. In my own self serving way in part at least because I am not smart thought of the standardization because you just know I will mess up the table relations design. And like most projects who the hell wants the headache of maintaining all the changes. Why cant I borrow someone elses who did it before me who is smarter ;-) I know it sounds incredibly lazy but because of my brain it takes me longer to do things so I often think of things to cut corners to help save time. SQL is already an international ISO/ANSI standard and should be conformed to as closely as possible. If you mean that you want the community to agree on a parsed representation, then I'm not sure what to say. SQL::Routine implements one, which I hope will gain defacto standard usage. I never dreamed a whole language spanning multiple db systems would end up being developed. And if you look at some tools out there I believe the fancy structure change was omitted because of the headache to support my theory. Probably a feature creep decision. SQL::Routine is not tied to the database; it is soley a database description model that is strongly influenced by SQL but is not string-SQL. It should work with any vendor of database on equal terms. SQLite 3 is the first one to be tested, though, any day now. this looks like its required install OS is linux though??? thanks, jim No. SQLite runs on any operating system. The SQL::Routine library also runs on any operating system, including the many flavors of Unix or Linux, and Mac OS, and Windows. -- Darren Duncan
Re: [sqlite] sqlite project--working with table structure
Ulrik Petersen wrote:
> [T]he way I understand SQLite, if you delete a row with the DELETE
> statement, the row will not actually be deleted, only marked as
> deleted. Then the next time you insert a row, the row may be reused.
> At least that's how I understand it... could somebody please correct
> me if I'm wrong?
>
Not correct. SQLite really does delete rows when you do a DELETE.
Under normal circumstances, a ROWID might be reused. The algorithm
for chosing a ROWID (assuming the user doesn't specify it) is to
first pick a integer that is one more than the current largest
ROWID. If the current largest ROWID is the maximum integer
(9223372036854775807) then an unused ROWID is picked at random.
So if you do this:
CREATE TABLE test1(x);
INSERT INTO test1 VALUES('one');
INSERT INTO test1 VALUES('two');
INSERT INTO test1 VALUES('three');
The rowids chosen will be 1, 2, and 3, respectively. If you
then do:
DELETE FROM test1 WHERE x='three';
INSERT INTO test1 VALUES('four');
When the insert of 'four' occurs, the largest existing
ROWID is 2, so 3 is chosen as the rowid of 'four'. The
ROWID of 3 gets reused. If you later do this:
DELETE FROM test1 WHERE x='two';
INSERT INTO test1 VALUES('five');
The ROWID chosen for 'five' is 4 because 4 is one more
than the current larest ROWID of 3. The ROWID of 2 is
not chosen even though it is available because that is not
how the rowid chooser algorithm described above works.
There is a new feature in CVS HEAD (not yet released) that
will always cause SQLite to generate a unique ROWID - one that
has never been used before and which is greater than every prior
ROWID. You can now use the AUTOINCREMENT keyword on an integer
primary key, like this:
CREATE TABLE test2(id INTEGER PRIMARY KEY AUTOINCREMENT, x);
An integer primary key is just an alias for the ROWID. If you
insert a NULL into the integer primary key, SQLite selects the
ROWID using the algorithm described above. But if the AUTOINCREMENT
keyword is specified, a different algorithm is used. The new
ROWID chosen is always one greater than the largest ROWID that
has ever existed in the table before. This guararantees that
ROWIDs chosen by SQLite will always be unique and will always
be increasing, even if you delete rows.
AUTOINCREMENT is a little slower than the normal way of
chosing ROWIDs so you should not use it unless you really do
need ROWIDs that are unique over the lifetime of the table.
But the speed penalty is not that great, so if unique and
monotonic ROWIDs are important to your application,
AUTOINCREMENT can be a handy feature.
Scheduled release date is Dec 14.
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] DATABASE SCHEMA HAS CHANGED
Randall Fox wrote: I believe VACUUM changes the schema version numbering. After you VACUUM, your sqlite3 struct holds information about your previous database version. Solution: reopen the database and SQLite will pick up the new changes. After that, INSERT will not report an error (until you VACUUM again, that is.) This sounds like a major bug. I use Sqlite embedded in my app and an error like that could be catastrophic. Should I close and reopen the DB every time after a vacuum? Is there a fix? I was not able to recreate the error but I just upgraded to v3 today so I am still testing.. The steps SQLite uses to process a single statement of SQL are roughly as follows: 1) Parse the SQL statement 2) Generte virtual machine code to execute the statement 3) Open the database file 4) Execute the virtual machine code 5) Close the database files Step (2) is based on the last known schema for the database. When step (4) begins, the first thing it does is make sure that the schema used in step (2) is the same as the current schema. If the schema has changed, then the generate virtual machine code might be incorrect so execution aborts with an SQLITE_SCHEMA error. It also sets a flag so that the schema will be automatically reread from the database file prior to doing another parse. Note that the parser cannot check to see if it has the current schema because at the time the parser and code generator are running, the database file is not yet open. In SQLite version 3.0, when a schema change occurs, SQLite automatically goes back to step 1, rereads the schema, and tries again. So you should never get an SQLITE_SCHEMA error in version 3.0. Back in version 2.8, you could get an SQLITE_SCHEMA error in some circumstances. When you do, all you have to do is retry the command and it should work. So to answer your questions: No, this is not a serious bug. You just need to be prepared to reissue any SQL statement that returns SQLITE_SCHEMA. Yes, this issue is fixed in version 3.0. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
Re: [sqlite] sqlite project--working with table structure
Hi Jim, > hi- > > i was hoping to get a little feedback on an idea i had. > > the create table statement can get complex with its variable number > field constraints and table constraints etc. etc. > when i first tackled the problem i tried to parse it. now i have a > different idea. what if i viewed the create table statement > as a group of tables with records . then when i am finished adding > records to the structure db tables i could call > a routine to write the create table statement based on the structure db > database for that particular table. > > a problem i see with this design is someone with a table already > designed would not want to use a program like this > because they have all ready generated the table structure in > sqlite_master.sql. > > i'll probably be the only user anyways. > > i thought i could use delphi personal edition and libsql to create an > interface in this manner to insert,update,delete,select > table structure data. > > is this a really bad idea? i have to try something. changing the > structure is the worst part of my program :-( > > thanks, > jim > If you go with this design, please make sure you include a unique autoincrementing column which always increases as you insert rows. To see how, go to http://www.sqlite.org/faq.html#q1 Then when you retrieve the rows to be created as columns, be sure to ORDER BY this autoincrement field. The reason is, the way I understand SQLite, if you delete a row with the DELETE statement, the row will not actually be deleted, only marked as deleted. Then the next time you insert a row, the row may be reused. At least that's how I understand it... could somebody please correct me if I'm wrong? But, you see, if I am right, and you don't have an autoincrement field to ORDER BY, then your columns may be inserted out of order, and thus retrieved out of order, and so your table will have a different column order when you inserted the rows representing the columns. HTH Cheers, Ulrik -- Ulrik Petersen, Denmark
RE: [sqlite] sqlite project--working with table structure
Have a look here: http://search.cpan.org/dist/SQL-Routine/ That's part of a project I'm working on. With it, each database table, as well as every other kind of thing you could possibly put in a schema or use with a database, is represented as a cross-referenced hierarchy of atomic-value nodes. You can create and manipulate your "create table statement" by adding, removing, and altering the nodes. Then the create can be generated from them when done. -- Darren Duncan jim wrote: Darren, this is cool. I can't wait to pass this along to a person at my job who works with DB's at his full-time job. I had thought wouldn't it be nice if people would agree on the table structue of the create table statement within the sqlite community for all these sqlite db tools popping up. In my own self serving way in part at least because I am not smart thought of the standardization because you just know I will mess up the table relations design. And like most projects who the hell wants the headache of maintaining all the changes. Why cant I borrow someone elses who did it before me who is smarter ;-) I know it sounds incredibly lazy but because of my brain it takes me longer to do things so I often think of things to cut corners to help save time. I never dreamed a whole language spanning multiple db systems would end up being developed. And if you look at some tools out there I believe the fancy structure change was omitted because of the headache to support my theory. Probably a feature creep decision. this looks like its required install OS is linux though??? thanks, jim
Re: [sqlite] sqlite project--working with table structure
At 3:37 AM -0500 11/20/04, j-marvin wrote: hi- i was hoping to get a little feedback on an idea i had. the create table statement can get complex with its variable number field constraints and table constraints etc. etc. when i first tackled the problem i tried to parse it. now i have a different idea. what if i viewed the create table statement as a group of tables with records . then when i am finished adding records to the structure db tables i could call a routine to write the create table statement based on the structure db database for that particular table. a problem i see with this design is someone with a table already designed would not want to use a program like this because they have all ready generated the table structure in sqlite_master.sql. i'll probably be the only user anyways. i thought i could use delphi personal edition and libsql to create an interface in this manner to insert,update,delete,select table structure data. is this a really bad idea? i have to try something. changing the structure is the worst part of my program :-( thanks, jim Have a look here: http://search.cpan.org/dist/SQL-Routine/ That's part of a project I'm working on. With it, each database table, as well as every other kind of thing you could possibly put in a schema or use with a database, is represented as a cross-referenced hierarchy of atomic-value nodes. You can create and manipulate your "create table statement" by adding, removing, and altering the nodes. Then the create can be generated from them when done. -- Darren Duncan
Re: [sqlite] DATABASE SCHEMA HAS CHANGED
>I believe VACUUM changes the schema version numbering. After you >VACUUM, your sqlite3 struct holds information about your previous >database version. Solution: reopen the database and SQLite will pick up >the new changes. After that, INSERT will not report an error (until you >VACUUM again, that is.) This sounds like a major bug. I use Sqlite embedded in my app and an error like that could be catastrophic. Should I close and reopen the DB every time after a vacuum? Is there a fix? I was not able to recreate the error but I just upgraded to v3 today so I am still testing.. Randall Fox
