Re: [sqlite] Constraint error messages

On Mar 1, 2012, at 12:20 AM, Roger Binns wrote: > There is a reason developers have gone to the trouble of naming their > constraints! Indeed. All these constraint names are meant to convey information. They are not decorative. ___ sqlite-users

Re: [sqlite] Constraint error messages

Me too. Either as a new standard way of working, or as something which can be turned on and off with a PRAGMA. I accept that SQLite is meant to be fast, but having SQLite spit out which check was violated will result in my app running faster and more dependably than when I build the same

Re: [sqlite] Constraint error messages

On 1 Mar 2012, at 12:38am, Mario Becroft wrote: > Just adding my voice to the choir. The constraints are of limited value > if you can't tell which one failed, and the system is not much more > 'lite' if the constraints have to be duplicated using CHECK clauses > anyway. Me

Re: [sqlite] Constraint error messages

Just adding my voice to the choir. The constraints are of limited value if you can't tell which one failed, and the system is not much more 'lite' if the constraints have to be duplicated using CHECK clauses anyway. -- Mario Becroft

Re: [sqlite] Constraint error messages

On 01/03/2012, at 8:28 AM, Richard Hipp wrote: > SQLite implements CHECK constraints by concatenating all expressions from all > CHECK constraints on the table using AND and then evaluating the resulting > boolean to see if it is false. If it is false, an SQLITE_CONSTRAINT error is > raised.

Re: [sqlite] Constraint error messages

I've also tried also using it in an SQL transaction (eg a batch import script), but SQLite doesn't allow it. So, in a transaction, one approach I've used is to create a temp table, a temp trigger and then insert some test data just to be able to use the raise function to abort the transaction

Re: [sqlite] Constraint error messages

On 01/03/2012, at 4:22 AM, Pete wrote: > I would like to include as much error checking as possible in my database > schema. That's an admirable aim. The whole point of constraints is to bring the error checking as close to the data model as possible. > The problem I have is that the error

Re: [sqlite] Set Locale for upper() and lower() using a pragma variable

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 29/02/12 12:23, Carl Desautels wrote: > I would like to be able to run one statement that sets the locale for > upper() and lower() If you register a function with the same name and number of arguments as a builtin one, then yours will take

Re: [sqlite] Constraint error messages

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 29/02/12 13:28, Richard Hipp wrote: >> We *could* keep track of each separate CHECK expression and remember >> the constraint name and evaluate each expression separately and >> output a customized error message for each failure. But that would >>

Re: [sqlite] Constraint error messages

On Wed, Feb 29, 2012 at 2:25 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 29/02/12 09:22, Pete wrote: > > The problem I have is that the error messages that come back from > > constraint violations are extremely generic (e.g. "constraint

[sqlite] Set Locale for upper() and lower() using a pragma variable

From the ICU documentation, ( http://www.sqlite.org/src/artifact?ci=trunk=ext/icu/README.txt) To access ICU "language specific" case mapping, upper() or lower() should be invoked with two arguments.[...] lower('I', 'tr_tr') -> 'ı' (small dotless i) With an ICU enabled build of SQLite, I

Re: [sqlite] Constraint error messages

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 29/02/12 09:22, Pete wrote: > The problem I have is that the error messages that come back from > constraint violations are extremely generic (e.g. "constraint failed") > and would mean nothing to a user. An issue first reported in 2006:

Re: [sqlite] accessing multiple databases

On 29 Feb 2012, at 6:07pm, Rob Richardson wrote: > IIRC, there's a connection string option that will choose between creating an > empty database and throwing an exception if you try opening a database that > doesn't exist. Arguments to sqlite3_open_v2():

Re: [sqlite] accessing multiple databases

IIRC, there's a connection string option that will choose between creating an empty database and throwing an exception if you try opening a database that doesn't exist. Perhaps if that option is set to throw an exception, then the ATTACH command would fail. Or not. RobR

Re: [sqlite] accessing multiple databases

As a follow up... Sqlite finds the first db fine useing a relative path but the ATTACH command needs the FULL PATH. Ugg! Are you sure it's really opening the DB you think it's opening? I think SQLite will create the file if it's not there, and you'll have nothing in it. Perhaps you should

Re: [sqlite] [SOLVED] accessing multiple databases

VERY NICE!!! You were right! I tried ./filename and that did not work, then I tried $_SERVER[DOCUMENT_ROOT]/path/filename BINGO! Thanks for teh help. jan zumwalt Are you sure it's really opening the DB you think it's opening? I think SQLite will create the file if it's not there, and

[sqlite] Constraint error messages

I would like to include as much error checking as possible in my database schema. The problem I have is that the error messages that come back from constraint violations are extremely generic (e.g. "constraint failed") and would mean nothing to a user. I tried including a name for constraints

Re: [sqlite] SELECT average timestamp to get average time of day? (C M)

On Wed, Feb 29, 2012 at 8:41 AM, Mark Belshaw wrote: > This is the first time I've posted a response to any mailing list, so I > hope > I'm doing it right and it appears where it should! > It sure did. Thanks for participating. > Not SQLite, but a technique we

Re: [sqlite] Online backup API and sqlite3_interrupt()

On Wed, Feb 29, 2012 at 11:59 AM, Gregory Johnson wrote: > Hi, > I have an application where one thread (A) is executing various statements > and another thread (B) is performing an online backup. These two threads > share the same source database connection; the SQLite

Re: [sqlite] Sqlite3BTreeMovetoUnpacked SQLite SELECT Statement Workaround is I/O Bound and uses all the Physical Memory

Daniel Kennedy, I tried your SQLite SELECT Statement workaround(the profiler output is attached) but the profiler is full of sqlite functions and the application runs slower because it is I/O bound and uses almost all the physical memory. I was thinking maybe we could write only one SQLITE

[sqlite] Online backup API and sqlite3_interrupt()

Hi, I have an application where one thread (A) is executing various statements and another thread (B) is performing an online backup. These two threads share the same source database connection; the SQLite threading mode is set to serialized. I wish to stop the online backup from another thread

Re: [sqlite] SELECT average timestamp to get average time of day? (C M)

This is the first time I've posted a response to any mailing list, so I hope I'm doing it right and it appears where it should! Not SQLite, but a technique we use in our Time & Attendance system to help with this sort of thing, where employees work nights / days and rotating shift patterns, is to

Re: [sqlite] SET NEW.FieldName in trigger

On 29 Feb 2012, at 12:36pm, hsymington wrote: > Thanks Simon; this does sound like a less headache-driven way of doing it. I > was simplifying things slightly for an example; the actual situation is more > complicated. Okay, that explains it. I'm going to let the

Re: [sqlite] SET NEW.FieldName in trigger

Simon Slavin-3 wrote: > >> Background: I've got a database schema in the form of a text file, which >> some software reads and converts to a SQLite database. I also need php to >> be >> able to read that text file and convert it into a MySQL database. I'm >> trying >> to work out how to define

Re: [sqlite] SET NEW.FieldName in trigger

On 29 Feb 2012, at 11:06am, hsymington wrote: > Simon Slavin-3 wrote: > >>> CREATE TRIGGER UpdateSaleItemDescription BEFORE INSERT ON SaleItem >>> BEGIN >>> SET NEW.SaleItem_Description='Fish'; >>> END; >> >> Yes. You can look at values using 'new.' but you cannot

Re: [sqlite] SET NEW.FieldName in trigger

Simon Slavin-3 wrote: > >> CREATE TRIGGER UpdateSaleItemDescription BEFORE INSERT ON SaleItem >> BEGIN >> SET NEW.SaleItem_Description='Fish'; >> END; > > Yes. You can look at values using 'new.' but you cannot change them. > However, you do not need to. To perform such an operation as you

Re: [sqlite] SET NEW.FieldName in trigger

On 29 Feb 2012, at 10:46am, hsymington wrote: > CREATE TRIGGER UpdateSaleItemDescription BEFORE INSERT ON SaleItem > BEGIN > SET NEW.SaleItem_Description='Fish'; > END; > > [snip] > Is it correct that I can't do the first, or am I misreading the syntax? Yes. You

[sqlite] SET NEW.FieldName in trigger

Hi - In MySQL, I can do CREATE TRIGGER UpdateSaleItemDescription BEFORE INSERT ON SaleItem BEGIN SET NEW.SaleItem_Description='Fish'; END; and I can't do CREATE TRIGGER UpdateSaleItemDescription AFTER INSERT ON SaleItem BEGIN UPDATE SaleItem SET SaleItem_Description='Fish' WHERE

Re: [sqlite] sqlite3.dll wrapper to import .csv file

On 29 Feb 2012, at 4:53am, Rick Guizawa wrote: > Hi All, I am using sqlite3.dll in my c# winform app, I was wondering if > anyone knows how to import .csv file into sqlite db table using c# > sqlite3.dll wrapper function. SQLite has no functions for handling .csv files.