RE: [sqlite] Resetting primary key auto-increment after table re-create
Sorry...That's what I meant - scary you can insert into a table that has this property without first setting The propert off without re-seeding - ie. How Sybase and SqlServer handle this.. It would be good if this was documented a little clearer Thx all for your assistance - onward and upward (so to speak) Cheers... > -Original Message- > From: Kurt Welgehausen [mailto:[EMAIL PROTECTED] > Sent: Tuesday, 9 March 2004 2:34 AM > To: [EMAIL PROTECTED] > Subject: Re: [sqlite] Resetting primary key auto-increment > after table re-create > > > > So what you are saying is that if I recreate the table I can insert > > all the old values back into the newly created table (including the > > primary key values) and the primary key column wont > complain? That's a > > bit scary! > > It's not scary, it's essential. In a real database, an > integer primary key will almost always be referenced by one > or more foreign keys. Changing the primary key would destroy > referential integrity. > > Regards > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] SQLite 2.8.13. Issues found with SQLite.exe
rayB wrote: Seems that dot-commands (.output, .separator, .nullvalue, etc) are now being rejected by SQLite.exe 2.8.13 when contained in files executed via the .read command. Error message text is: ‘unknown command or invalid arguments: "output". Enter ".help" for help’ I tired it and everything appears to be working fine. Please provide more details about the problem and I will look into it further. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] 'show_datatypes' Question
On Mar 8, 2004, at 5:09 PM, Tito Ciuro wrote: You can write your own sqlite_get_table to package up all the types for you. I'd like to do that. Should I create my own TabResult struct, to store the column data or would I be doing too much work? Any pointers? I would see if you can't snarf the sqlite_get_table code and use that. You'll just have to look where it prepends the names and add the type information as well. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Data types in 2.8.13
Tito Ciuro wrote: If I understand correctly, I should write my own sqlite_get_table() function with a custom callback which stores the column data somewhere, right? Would it be better to create my own TabResult struct, to store the column data or would I be doing too much work? The other thing you can do is just process your results in the callback. But if you need to return all the results at once, then you are going to have to gather them up. What I did was snarf the current sqlite_get_table() and modify it so it also returns the type information. I found that easy than trying to right my own from scratch. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] 'show_datatypes' Question
Hello, On 8 mar 2004, at 18:57, Will Leshner wrote: Are you seeing these results with the callback or with sqlite_get_table. I'm betting sqlite_get_table, because the callback doesn't return results in that fashion. Anyway, you can't get the types with sqlite_get_table. Only with the callback. You can write your own sqlite_get_table to package up all the types for you. Or you can use the callback, in which case the types are returned appened to the end of the 4th argument. Yes, you're right, I'm using sqlite_get_table()... you're right on the money ;-) You can write your own sqlite_get_table to package up all the types for you. I'd like to do that. Should I create my own TabResult struct, to store the column data or would I be doing too much work? Any pointers? Thanks, -- Tito - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Data types in 2.8.13
Hello, On 8 mar 2004, at 18:47, Dennis Cote wrote: How are you interfacing to SQLite? Are you using the C API directly? If so, are you using sqlite_exec() with a callback function (the callback API), or are you using the sqlite_compile(), sqlite_step(), and sqlite_finalize() (the newer non-callback API)? I'm calling sqlite_get_table(). These APIs are described here: http://www.hwaci.com/sw/sqlite/c_interface.html Generally speaking, the datatype information follows the column name information in one array, and the column data is returned in a seperate array. For a typical result that returns 3 columns you get something like this: ColumnNames[] [0] -> column 1 name [1] -> column 2 name [2] -> column 3 name [3] -> column 1 type [4] -> column 2 type [5] -> column 3 type Data[] [0] -> column 1 data [2] -> column 2 data [3] -> column 3 data If I understand correctly, I should write my own sqlite_get_table() function with a custom callback which stores the column data somewhere, right? Would it be better to create my own TabResult struct, to store the column data or would I be doing too much work? Thanks a lot, -- Tito
RE: [sqlite] Resetting primary key auto-increment after table re-create
- Forwarded by Ben Carlyle/AU/IRSA/Rail on 09/03/2004 10:56 AM - Ben Carlyle 09/03/2004 10:54 AM To: "Roger Hawkins" <[EMAIL PROTECTED]>@CORP cc: Subject:RE: [sqlite] Resetting primary key auto-increment after table re-create Roger, "Roger Hawkins" <[EMAIL PROTECTED]> 08/03/2004 03:18 PM To: <[EMAIL PROTECTED]> cc: Subject:RE: [sqlite] Resetting primary key auto-increment after table re-create > Thanks for your reply... > I havent found any reference to this in the doco so Im not sure how any > of this might work.. http://sqlite.org/faq.html#q1 Pay particular attention to the "is logically equivalent" part. > So what you are saying is that if I recreate the table I can insert all > the old values back into the newly created table (including the primary > key values) and the primary key column wont complain? That's a bit > scary! So long as you don't insert the same number into the INTEGER PRIMARY KEY twice, sqlite will not complain. It's a primarily a primary key. It only doubles as an auto-increment field when you insert NULLs into it. > Even if this is true what happens when I next insert a value (normally > without specifying the primary key field) - does it just pick up the > latest? > Any one tried this? Yes. You can, too: CREATE TABLE foo(bar INTEGER PRIMARY KEY); INSERT INTO foo VALUES(NULL); INSERT INTO foo VALUES(2); INSERT INTO foo VALUES(NULL); INSERT INTO foo VALUES(3); -- SQL Error: PRIMARY KEY must be unique SELECT * FROM foo: 1 2 3 Benjamin. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] SQLite 2.8.13. Issues found with SQLite.exe
Seems that dot-commands (.output, .separator, .nullvalue, etc) are now being rejected by SQLite.exe 2.8.13 when contained in files executed via the .read command. Error message text is: unknown command or invalid arguments: "output". Enter ".help" for help Was this change intended? Regards. rayB - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] 'show_datatypes' Question
Will Leshner wrote: Anyway, you can't get the types with sqlite_get_table. Only with the callback. Sorry for the misleading information. By "only with the callback" I meant considering those two options, the callback is the only one of them that returns types. You can also create a VM out of your SQL and step through the results with that. I believe that always returns the type. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] 'show_datatypes' Question
Tito Ciuro wrote: Hello, After reading quite a bit about 'show_datatypes' I still don't get its workings. After setting 'PRAGMA show_datatypes = ON;' I go ahead and perform a 'SELECT * FROM address_table LIMIT 1;' The resulting set contains 4 columns, 1 row: [0] --> ROWID [1] --> First [2] --> Last [3] --> Country [4] --> 1 [5] --> Tito [6] --> Ciuro [7] --> Spain Are you seeing these results with the callback or with sqlite_get_table. I'm betting sqlite_get_table, because the callback doesn't return results in that fashion. Anyway, you can't get the types with sqlite_get_table. Only with the callback. You can write your own sqlite_get_table to package up all the types for you. Or you can use the callback, in which case the types are returned appened to the end of the 4th argument. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Data types in 2.8.13
From: "Tito Ciuro" <[EMAIL PROTECTED]> > Now that 2.8.13 is out, is 'PRAGMA show_datatypes = ON' by default? I > wonder because I'm still not getting the data types in the result set. > > I posted a message yesterday and after upgrading to 2.8.13 I'm still > getting the same results. Can someone *please* give me some pointers? How are you interfacing to SQLite? Are you using the C API directly? If so, are you using sqlite_exec() with a callback function (the callback API), or are you using the sqlite_compile(), sqlite_step(), and sqlite_finalize() (the newer non-callback API)? These APIs are described here: http://www.hwaci.com/sw/sqlite/c_interface.html Generally speaking, the datatype information follows the column name information in one array, and the column data is returned in a seperate array. For a typical result that returns 3 columns you get something like this: ColumnNames[] [0] -> column 1 name [1] -> column 2 name [2] -> column 3 name [3] -> column 1 type [4] -> column 2 type [5] -> column 3 type Data[] [0] -> column 1 data [2] -> column 2 data [3] -> column 3 data - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] RMemory database from 2.8.9 to 2.8.13
At 8:16 AM +1100 3/9/04, Greg Obleshchuk wrote: Hi , I have an appllication for MS SQL Server which creates crosstab reports . To generate some extra speed out of the process I use SQLite (funning I think) as an in memory database. Has there been any improvements in speed or stability of the :memory: database since 2.8.9? I just want to know if I should upgrade the program. regards Greg O I would say that if a move is technically easy for you then you should consider always keeping up with the current version. Even if there aren't significant speed changes, there are other benefits such as bug fixes in each new version. The main reason to wait is if you are concerned that new versions may introduce troubling bugs, but then its just a matter of being careful. -- Darren Duncan - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Data types in 2.8.13
Hello guys, Now that 2.8.13 is out, is 'PRAGMA show_datatypes = ON' by default? I wonder because I'm still not getting the data types in the result set. I posted a message yesterday and after upgrading to 2.8.13 I'm still getting the same results. Can someone *please* give me some pointers? Thanks in advance, -- Tito - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] RMemory database from 2.8.9 to 2.8.13
Hi , I have an appllication for MS SQL Server which creates crosstab reports . To generate some extra speed out of the process I use SQLite (funning I think) as an in memory database. Has there been any improvements in speed or stability of the :memory: database since 2.8.9? I just want to know if I should upgrade the program. regards Greg O - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] Resetting primary key auto-increment after table re-create
> So what you are saying is that if I recreate the table I can insert all > the old values back into the newly created table (including the primary > key values) and the primary key column wont complain? That's a bit > scary! It's not scary, it's essential. In a real database, an integer primary key will almost always be referenced by one or more foreign keys. Changing the primary key would destroy referential integrity. Regards - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] rollout of 2.8.13 ???
Ralph Wetzel wrote: Wouldn't it be a nice idea to rollout 2.8.13, so that it reduces the amount of flood (on this list and the wiki) concerning already fixed bugs??? Version 2.8.13 is now on the website. -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]