Re: [sqlite] Attach to file in same directory

>> On 03-02-11 16:18, BareFeetWare wrote: >> >> What SQLite or C library call could I put before that to set the current >> directory, that the sqlite3_prepare_v2 function would observe when >> processing the attach statement? > On 04/02/2011, at 2:25

Re: [sqlite] Attach to file in same directory

>> On 3 Feb 2011, at 2:59pm, BareFeetWare wrote: >> >> But if a I have an arbitrary SQL script/procedure to perform, that starts >> with an attach statement, I don't have creation control over the path >> specified in the script. > On 04/02/2011, at 2:18 AM

Re: [sqlite] Attach to file in same directory

C function: rc = sqlite3_prepare_v2(_db, cSQL, -1, , ); where cSQL is the C string containing the SQL attach statement. What SQLite or C library call could I put before that to set the current directory, that the sqlite3_prepare_v2 function would observe when processing the attach statem

Re: [sqlite] Attach to file in same directory

>> On 3 Feb 2011, at 1:03am, BareFeetWare wrote: >> >> How can I attach to a local file in the same directory, without specifying >> the full absolute path? > On 03/02/2011, at 12:17 PM, Simon Slavin wrote: > > No easy way. Argh. That's kind of mental, t

Re: [sqlite] Attach to file in same directory

>> On 2/2/2011 8:03 PM, BareFeetWare wrote: >> I use the attach command to attach another SQLite database file that resides >> in the same directory as my main file. I tried: >> >> attach 'Import.sqlitedb'; >> >> But it fails to find the file. If I sp

[sqlite] Attach to file in same directory

and the procedure containing the attach to iPhones/iPads where the absolute path changes between devices. How can I attach to a local file in the same directory, without specifying the full absolute path? Thanks, Tom BareFeetWare Sent from my iPhone

Re: [sqlite] sqlite3 command line utility

TO "Objectives"(ObjectiveID, MicroscopeID, TurretPos, Name, Description, Magnification, NA, WD) VALUES( 2, (select MicroscopeID from Store), 1, '10X' , 'CFI LU Plan Fluor BD 10X' , 10.0, 0.30, 15.0); etc Tom BareFeetWare -- Comparison

Re: [sqlite] Getting a table's field labels with Perl, DBI

INFORMATION_SCHEMA or similar is not provided by SQLite, a topic that keeps recurring here in one form or another. Yu currently have to do your own parsing of the schema to get much of the info about columns, triggers, foreign keys etc. Tom BareFeetWare -- Comparison of SQL

Re: [sqlite] Drop Foreign Key

"; drop table "My Table Backup"; > create trigger ; > create index ; > commit or rollback; Thanks, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Drop Foreign Key

mp table "My Table Backup" as select * from "My Table"; drop table "My Table"; create table "My Table"( ); create trigger ; create index ; commit or rollback; Thanks, Tom BareFeetWare -- Comparison of SQLite GUI t

Re: [sqlite] Select statement not returning any result

expected result: select * from symbols where pattern like 'n'; So there's something funky with the data you're inserting. Ah, just read your new post. Seems you've found the error in code. Good :-) Tom BareFeetWare -- Comparison of SQLite GUI tools: h

Re: [sqlite] Select statement not returning any result

o read it >> won't read most of the messages. >> > > I was not aware of this. Thanks for pointing it out. Id the above dump doesn't help, email me the file directly and I'll have a look. Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compar

Re: [sqlite] Select statement not returning any result

e other whitespace variation. Or it could be a problem with your C interface. Using the sqlite3 command line tool, try: .mode insert select * from symbols; Reply here with one of the lines showing an 'n'. The insert syntax should make it clear what's going on. Tom BareFeetWare -- Compari

Re: [sqlite] More descriptive error

On 9 Jan 2011, at 2:48pm, BareFeetWare wrote: >> When I run this via C, using sqlite3_errmsg(), I get the error message: >> >> constraint failed >> >> but when I run it via the sqlite3 command line I get a more descriptive: >> >> SQL error: columns F

[sqlite] More descriptive error

Mouse'); rollback; When I run this via C, using sqlite3_errmsg(), I get the error message: > constraint failed but when I run it via the sqlite3 command line I get a more descriptive: > SQL error: columns First, Last are not unique How can I get the more descriptive error via sqlite3_*()

Re: [sqlite] Disabling foreign key cascading during re-insertion of existing data

On 01/07/2011 09:19 PM, BareFeetWare wrote: >> pragma foreign_keys = no; On 08/01/2011, at 1:26 AM, Dan Kennedy wrote: > The command above should have disabled all foreign-key constraint checks > (including cascades etc.). Check it for typos perhaps. I thought it would too,

Re: [sqlite] Rollback transaction if error

On 23/12/2010, at 5:38 PM, Drake Wilson wrote: > Quoth BareFeetWare <list@barefeetware.com>, on 2010-12-23 15:10:30 +1100: >> Is there a way to do this in pure SQL, without my application code >> having to check for errors along the way and then interrogate the >>

[sqlite] Rollback transaction if error

thout my application code having to check for errors along the way and then interrogate the SQL to look for a "commit" type line and replace it? This seems pretty error prone and convoluted. Thanks, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/c

Re: [sqlite] pragma vs select for introspection

On 12/12/2010, at 1:48 AM, Simon Slavin wrote: > On 11 Dec 2010, at 2:28pm, BareFeetWare wrote: > >> I think there's an SQL standard for introspective queries, isn't there? Is >> it something like MySQL's "INFORMATION_SCHEMA Tables", as per?: >> http

[sqlite] pragma vs select for introspection

.com/doc/refman/5.0/en/information-schema.html Thanks, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] pragma vs select for introspection

, but I feel like I'm reinventing the wheel, since SQLite obviously already has this information internally, but won't share. And I'm concerned that if SQLite's internals change, my external mimicking will fail. I hope this clarifies what I'm talking about. Thanks, Tom BareFeetWare -- Co

Re: [sqlite] pragma foreign_key_list

ult of a pragma. Pragmas seem unnecessarily separated from the SQLite engine, returning a table of info that can't be manipulated by the SQL engine in which it resides. Thanks for your consideration, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/

Re: [sqlite] pragma foreign_key_list deprecated in 3.7.4?

w SQLite is dealing with the schema). Thanks for your consideration and a great product, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite Introspection

Master where Type = 'trigger' and Tbl_Name = 'My Table'; to get the Name and SQL of all triggers for a particular table (or view). But there's no provided way to parse the trigger definition into its parameters (eg instead of|before|after, update of|update|delete|insert, steps). Tom BareFe

Re: [sqlite] Human readable SQL from prepared statement

esn't substitute the parameter values in the result string. Thanks, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Human readable SQL from prepared statement

bind parameters: (int) 5 (char *) Mickey How can I get out the human readable version?: select * from MyTable where IntColumn = 5 and TextColumn = 'Mickey' Thanks, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml

[sqlite] SQL code vs application code (was: Stored procedures, was: Question about SQLite features.)

On 13/11/2010, at 11:14 AM, Scott Hess wrote: > On Fri, Nov 12, 2010 at 5:50 AM, BareFeetWare <list@barefeetware.com> > wrote: >> IMO, if you're implementing database logic (ie constraints and triggers) in >> application code, then you're reinventing the wh

Re: [sqlite] Stored procedures

On 13/11/2010, at 10:33 AM, Olaf Schmidt wrote: >>>> From: "Olaf Schmidt" >>>> Wednesday, November 10, 2010 9:07:19 AM >>>> >>> There was a somewhat similar sounding post (from BareFeetWare, >>> sent on 20.Oct to this list) who al

Re: [sqlite] INSERT OR UPDATE

ctually delete the old row and insert a new one, something that can make a mess if, for instance, you wanted to keep the value of other columns or you have internal database logic that is initiated by deletes and inserts, so I tend to avoid it. HTH, Tom BareFeetWare -- Comparison of SQLite GUI tools:

[sqlite] Stored procedures (was: Question about SQLite features.)

ry for enforcing the business >> rules of the data to be stored in the database itself. This is just a good >> basic design principal. > > There was a somewhat similar sounding post (from BareFeetWare, sent on 20.Oct > to this list) who also encouraged, to include "mor

Re: [sqlite] Formatting (left aligned) Integers

|| '.' || Column3 || '.' || Column4 from MyTable order by Column1, Column2, Column3, Column4 ; Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://s

Re: [sqlite] Changing Date Format

tionality built into SQLite, but alas there is not. Thanks, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQL code vs application code (was: Insert or Update, was: ON DELETE CASCADE along with ON CONFLICT REPLACE)

is a striking feature of poorly >> written SQL programs to see the high number of lines of code outside of SQL >> queries that are simply devoted to summing up, multiplying, dividing, and >> subtracting inside loops what is painfully returned from the database. This >> is a totally useless and utterly inefficient job: we have SQL aggregate >> functions for that sort of work. Thanks, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Insert or Update (was: ON DELETE CASCADE along with ON CONFLICT REPLACE)

't: update users set "name" = 'Joe C', "type" = 4, where "id" = 1; insert or ignore into users ("id", "type", "name") values (1, 4, 'Joe C'); Furthermore, I suspect that you want the userId to be dynamically looked up, rather than specifica

Re: [sqlite] GUI for data entry

e and I will add it to the web page. Or let me know of any changes needed to existing data, or extra features you've compared. Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] GUI for data entry

consistent within the schema itself. I'll see if I can put together some SQL with a few examples of how a view updates several related tables. Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sq

Re: [sqlite] Importing csv to SQLite

s a new column. Please make it so. Thanks, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] delete constraint

te may optimize complex statements involving > IN better than those involving EXISTS, but I don't have formal evidence. That's good to know. Thanks Igor. I wasn't aware that "in" prevents a full table scan in the same way (or better) than EXISTS does. Thanks, Tom BareFeetWare

Re: [sqlite] delete constraint

= album_fk) If the columns are indexed, the advantage is negligible, but it's good SQL practice to avoid "count" and "in" when you only care if there is any existence of a match. As far as I'm aware, "count" and "in" do a full table scan, wh

Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

ready mentioned, though, this won't help if the view currently has no rows, or if the value of MyColumn for the present rows is null, and is of limited value of there are just a few existing rows with data. Tom BareFeetWare -- Comparison of SQLite GUI tools: http://

Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

On 06/05/2010, at 2:51 PM, Dan Bishop wrote: > BareFeetWare wrote: >> >> I've had the same issue. In the end I had to parse my view functions in my >> own code and look for functions that give a particular type of result. So, >> for instance, round() gives an i

Re: [sqlite] Computed columns in VIEWs return NULL but should be able to be typed! Any ideas?

have to search through the results of a query to find the first non-null result value and examine it's type and hope that it's the same as the column's (un)declared type. That's inefficient. Obviously, when I am displaying individual cells of data I can look at the value type, but the declared

<    1   2