Re: [sqlite] Re: Retrieving column data by providing column names
I am contemplating something on similar lines. Just wanted to make sure that I am not trying to reinvent the wheel. Thanks! On 10/26/06, Igor Tandetnik <[EMAIL PROTECTED]> wrote: Ever Green <[EMAIL PROTECTED]> wrote: > I am using sqlite3_column_text16 to retrieve data. > > The problem is that sqlite3_column_text16 takes the column number as > argument. This breaks my existing code if I insert new columns in the > beginning of the column list that was specified in the SELECT > statement. Unfortunately, there is no lookup from column name to index, but there is one from index to name: see sqlite3_column_count, sqlite3_column_name[16], sqlite3_column_origin_name[16] . Not as convenient, but you can enumerate all column names and determine how those you want map to indexes. Igor Tandetnik - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Error in SQLite's CSV output
On 10/26/06, T <[EMAIL PROTECTED]> wrote: On 17/10/2006, at 9:31 PM, Martin Jenkins wrote: > Does the SQL output work for your application? Might be just as > easily parseable as yet-another-version-of-CSV... I originally dismissed the sql "insert" output mode as most likely having the same bug as csv. Upon checking, however, the insert mode seems to work perfectly, handling commas and returns/linefeeds within quotes, as well as substituting a quote within a quote with a double quote. .. For those who asked, I'm building an application using "AppleScript Studio", which is basically Apple's Xcode development environment, using AppleScript as the programming language. SQLite is built into Mac OS X, and can be called via shell scripts. Within AppleScript, this looks something like: do shell script "sqlite3 Test.sqlite 'CREATE TABLE Test( field1 );'" If anyone knows how to instead directly (ie not via a shell script) call the SQLite routines in Mac OS X, I'm all ears :-) what do you mean by "call the SQLite routines"? You can use any number of programming languages to do so just as you are doing with Apple Script. I use Perl, but you could use Perl, Python, PHP, Tcl, C, and even Objective C. Did you explore Quicklite? I believe I had sent that link a few days ago... it is a Cocoa wrapper around SQLite create by Tito Ciuro. Looks quite good. -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation https://edu.osgeo.org/ --- collaborate, communicate, compete - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Error in SQLite's CSV output
Hi All, On 18/10/2006, at 3:08 AM, Dennis Cote wrote: You could try using the line mode output. It puts each field on a line of its own, and separates each record with a completely blank line. A sample is shown below. sqlite> select * from t2; a = 1 b = 2 a = 3 b = 4 I don't think that will work, since b could conceivably contain something confusing, such as: "The solution to the simultaneous equations is: a = 77 b = 5 " Also , there's a lot more overhead to parse the output, since the separators are different before each value, than a consistently delimited output. Thanks, Tom - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Error in SQLite's CSV output
On 17/10/2006, at 9:31 PM, Martin Jenkins wrote:
Does the SQL output work for your application? Might be just as
easily parseable as yet-another-version-of-CSV...
I originally dismissed the sql "insert" output mode as most likely
having the same bug as csv. Upon checking, however, the insert mode
seems to work perfectly, handling commas and returns/linefeeds within
quotes, as well as substituting a quote within a quote with a double
quote.
It works the way that CSV should (which needs fixing). I can use a
slightly modified CSV parsing handler, by just using this as the row
separator (instead of just plain linefeed):
");INSERT INTO VALUES("
For example:
CREATE TABLE Test( field1 );
INSERT INTO "Test" VALUES('I said "Hi there"');
INSERT INTO "Test" VALUES("I said 'all you'");
INSERT INTO "Test" VALUES('I said ''SQLite
people''');
.mode insert
SELECT * FROM Test;
which gives:
INSERT INTO table VALUES('I said "Hi there"');
INSERT INTO table VALUES('I said ''all you''');
INSERT INTO table VALUES('I said ''SQLite
people''');
which parses to give 3 records:
I said "Hi there"
I said 'all you'
I said 'SQLite
people'
Great :-)
For those who asked, I'm building an application using "AppleScript
Studio", which is basically Apple's Xcode development environment,
using AppleScript as the programming language. SQLite is built into
Mac OS X, and can be called via shell scripts. Within AppleScript,
this looks something like:
do shell script "sqlite3 Test.sqlite 'CREATE TABLE Test( field1 );'"
If anyone knows how to instead directly (ie not via a shell script)
call the SQLite routines in Mac OS X, I'm all ears :-)
Thanks,
Tom
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Trouble with ALTER TABLE/ADD
On 10/25/06, Christian Smith <[EMAIL PROTECTED]> wrote: A better solution would be to transfer the contents of the table being updated to a temporary table, then recreate the original tables sans the surplus columnn: It may not be quick for large tables, but how often are you going to be updating the table definition? If often, then you probably have a more fundamental problem on your hands. I considered this kind of solution briefly, but I'm afraid that users might have too much data for this to be effecient at all. It wouldn't happen a lot, but that rebuilding will have to happen between user interactions. The user is basically allowed to create a form template attached to an object. This template has an ID and a table devoted to that type (user_data_nn). A row in a user_tables: ID name fields 01 "Person" "name,email,site" Then user_data_01 is created with 3 generic columns. If the total field count ever drops below 3, it's simply ignored. So what's really
[sqlite] test message ...
Please ignore this test -- sorry. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] command-line shell handling of errors.
"Yes and yes." :) Alejo On 10/26/06, Kees Nuyt <[EMAIL PROTECTED]> wrote: Hi Richard, On Thu, 26 Oct 2006 17:23:17 +, you wrote: > So the question: > Who will be adversely effected by the new error behavior > in the sqlite command-line shell? Not really. I prefer the new behaviour. At the moment I have to jump through hoops and scan my make logs to detect errors and signal them. > Who is using the sqlite command-line shell in scripts in > such a way that the script will no longer work with the > new behaviors? It might break some of my scripts, but rightly so. I'll be glad to repair them. > Do I need to change the behavior back to the way it was > and provide a command-line option to provoke the new > (more rational) behavior? Not really needed, but it would be nice to have a choice to explicitly suppress errors or explicitly provoke the new behaviour, either way. As my makefiles use a macro $(SQLITE) instead of the program name itself it is easy to add any switch anyway. Your approach in the remarks of said ticket is right, in my view. Thank you for any abort-on-error solution and the beautiful, consistent product sqlite is! - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] command-line shell handling of errors.
Hi Richard, On Thu, 26 Oct 2006 17:23:17 +, you wrote: > So the question: > Who will be adversely effected by the new error behavior > in the sqlite command-line shell? Not really. I prefer the new behaviour. At the moment I have to jump through hoops and scan my make logs to detect errors and signal them. > Who is using the sqlite command-line shell in scripts in > such a way that the script will no longer work with the > new behaviors? It might break some of my scripts, but rightly so. I'll be glad to repair them. > Do I need to change the behavior back to the way it was > and provide a command-line option to provoke the new > (more rational) behavior? Not really needed, but it would be nice to have a choice to explicitly suppress errors or explicitly provoke the new behaviour, either way. As my makefiles use a macro $(SQLITE) instead of the program name itself it is easy to add any switch anyway. Your approach in the remarks of said ticket is right, in my view. Thank you for any abort-on-error solution and the beautiful, consistent product sqlite is! -- ( Kees Nuyt ) c[_] - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] command-line shell handling of errors.
[EMAIL PROTECTED] writes: > So the question: Who will be adversely effected by the new > error behavior in the sqlite command-line shell? Who is > using the sqlite command-line shell in scripts in such a > way that the script will no longer work with the new > behaviors? Do I need to change the behavior back to the > way it was and provide a command-line option to provoke the > new (more rational) behavior? My use of the command line shell in scripts expects, in some cases, errors to be ignored and for the remainder of the queries to be processed regardless of the earlier errors. I agree that your proposed change would have been preferred as the original implementation. I wouldn't mind if the new behavior became the default as long as there was a new command-line option to enable backward-compatibility mode of ignoring errors and continuing to process. Cheers, Derrell - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Regarding sqlite3_exec
No there isnt, but RDBM systems are a generalised data retrieval mechanism. As such they suffer from that generality. Dont get me wrong, RDBM systems are appropriate for 95% of all data requirements I have had to deal with and I would never dream of trying to write one from scratch, nor can I imagine a world without them. However certain applications (Weather data, Gnome data, Large indices (like google)) require using somethng designed specifically for that purpose. If you customise data retrieval (and particluar your sorting/indcies/access path) you can leave rdbms in the dust in terms of performance. All I have read about google, suggests they do exactly this. Although I must point out, I dont actually know anything about google with any certainty. Just what has "leaked" out over the years on the rumour mill. But designiing my own "google" like indices (on a smaller scale of coure) and some specialisted weather stuff, it neccessary to throw away the rdbms and do it yourself. For a goole query for instance, they know they will get a list of 1 or more words. They also know they will only ever search through the index of words. They dont have other data types, records or tables. Why go through all the hassles of compiling SQLs, and that generic overhead when your application will only ever do one thing? You can just make an API like this "search(wordlist): Resultset. " You immediatly save yourself complexity and processing time. Then for large indices you will know your data set, so instead of using a std BTree you would use a more appropraite DS possible with skip lists etc.. . As for performing a database search twice, this whole thread has shown, that sometimes the you have to :-) S On 10/25/06, John Stanton <[EMAIL PROTECTED]> wrote: There is no magic in data retrieval. Google use the same physical laws as us ordinary mortals. I see no reason to ever perform a dataabase search twice.
Re: [sqlite] command-line shell handling of errors.
I don't use the command-line shell, but I'd definitely prefer not to see a fundamental change in the behavior of any tool I use. The debugging could get nasty and it's possible that someone using the tool in an unsupervised script might not notice the problem until after it had done some damage, or until the new, undesirable behavior had been taking place for days/weeks. If you make the more rational behavior a command-line option, I suspect everyone will be happier. Thanks, Alex [EMAIL PROTECTED] wrote: In previous versions of SQLite, when the command-line shell encountered an error, it would print an error message but continue processing its input. This seems wrong. In response to ticket #2045, I changed the command-line shell so that when it is reading from a file, it stops reading whenever it encounters an error. The new behavior seems more rational. But it is not backwards compatible. So the question: Who will be adversely effected by the new error behavior in the sqlite command-line shell? Who is using the sqlite command-line shell in scripts in such a way that the script will no longer work with the new behaviors? Do I need to change the behavior back to the way it was and provide a command-line option to provoke the new (more rational) behavior? For additional information: http://www.sqlite.org/cvstrac/tktview?tn=2045 -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] command-line shell handling of errors.
In previous versions of SQLite, when the command-line shell encountered an error, it would print an error message but continue processing its input. This seems wrong. In response to ticket #2045, I changed the command-line shell so that when it is reading from a file, it stops reading whenever it encounters an error. The new behavior seems more rational. But it is not backwards compatible. So the question: Who will be adversely effected by the new error behavior in the sqlite command-line shell? Who is using the sqlite command-line shell in scripts in such a way that the script will no longer work with the new behaviors? Do I need to change the behavior back to the way it was and provide a command-line option to provoke the new (more rational) behavior? For additional information: http://www.sqlite.org/cvstrac/tktview?tn=2045 -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Retrieving column data by providing column names
"Ever Green" <[EMAIL PROTECTED]> writes: > I dont think so! > > These functions would help to get the parameter names from a prepared > statement. I want to retrieve data using column headings after a > sqlite3_step. The function I pointed you at takes the parameter name and returns its index. I believe that index is what you'll want to pass to sqlite3_column_text16(). Derrell > On 10/26/06, [EMAIL PROTECTED] < > [EMAIL PROTECTED]> wrote: > >> "Ever Green" <[EMAIL PROTECTED]> writes: >> >> > I am using sqlite3_column_text16 to retrieve data. >> > >> > The problem is that sqlite3_column_text16 takes the column number as >> > argument. This breaks my existing code if I insert new columns in the >> > beginning of the column list that was specified in the SELECT statement. >> >> Does this solve your problem? >> >> http://sqlite.org/capi3ref.html#sqlite3_bind_parameter_index >> >> Derrell >> >> >> >> - >> To unsubscribe, send email to [EMAIL PROTECTED] >> >> - >> >> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Retrieving column data by providing column names
I dont think so! These functions would help to get the parameter names from a prepared statement. I want to retrieve data using column headings after a sqlite3_step. On 10/26/06, [EMAIL PROTECTED] < [EMAIL PROTECTED]> wrote: "Ever Green" <[EMAIL PROTECTED]> writes: > I am using sqlite3_column_text16 to retrieve data. > > The problem is that sqlite3_column_text16 takes the column number as > argument. This breaks my existing code if I insert new columns in the > beginning of the column list that was specified in the SELECT statement. Does this solve your problem? http://sqlite.org/capi3ref.html#sqlite3_bind_parameter_index Derrell - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Retrieving column data by providing column names
"Ever Green" <[EMAIL PROTECTED]> writes: > I am using sqlite3_column_text16 to retrieve data. > > The problem is that sqlite3_column_text16 takes the column number as > argument. This breaks my existing code if I insert new columns in the > beginning of the column list that was specified in the SELECT statement. Does this solve your problem? http://sqlite.org/capi3ref.html#sqlite3_bind_parameter_index Derrell - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Retrieving column data by providing column names
I am using sqlite3_column_text16 to retrieve data. The problem is that sqlite3_column_text16 takes the column number as argument. This breaks my existing code if I insert new columns in the beginning of the column list that was specified in the SELECT statement. I was not able to figure out the right API to get this done. Thanks in advance.
