[sqlite] Unable to create table, default value of column [ID] is not constant
Alright, trying to set the scenario: * I open/create a database * I register a custom function called HASH (set to be deterministic), tested and working * I create a bunch of tables (if not exists) So far, everything works fine. Then, the troublesome create: CREATE TABLE IF NOT EXISTS Programmes ( ID NVARCHAR(64) UNIQUE NOT NULL PRIMARY KEY DEFAULT (HASH(Title, Year, EpNo2)), Title NVARCHAR(100) NOT NULL, Description NVARCHAR(1000), Year NVARCHAR(10), EpNo1 NVARCHAR(50), EpNo2 NVARCHAR(100)) This query raises the following exception: Unable to create table default value of column [ID] is not constant Is it not possible to use custom functions this way? I tried with random() on another table and that works. Any ideas and explanations would be appreciated. /Jörgen ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Virtual tables are driving me insane!
Thanks OBones! Your link gave me the solution to why my code didn't work! It was (of course) I who made an error in translating function parameters from C to Pascal! Best regards! /Jörgen sqlite-users-requ...@sqlite.org skrev 2012-09-24 18:00: Re: [sqlite] Virtual tables are driving me insane! ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Virtual tables are driving me insane!
Hi all! First off, my configuration: Windows 7 Ultimate x64 SQLite3 v3.7.14 Amalgamation, compiled to DLL using VS2010 Used directives: SQLITE_4_BYTE_ALIGNED_MALLOC (*) SQLITE_THREADSAFE=2 SQLITE_OMIT_DEPRECATED SQLITE_DEBUG (*) SQLITE_MEMDEBUG (*) (*) Tried both with and without these Now, my problem... I am trying to create a virtual table module in my Delphi application using my own interface to SQLite3.dll. All tests so far of my interface are working correctly, i.e. queries, preparations, functions etc. Now, I'm trying to implement virtual tables, but I seem to be in over my head with this. I tried creating a super simple test which just return "default" values on all calls. This is the order my functions are being called: xCreate (enter function) xCreate (exit function) xConnect (enter function) xConnect (exit function) At this point I get an access violation in SQLite3.dll (reading of address 0008) I have tried everything I can think of, but this is how far I get. I then tried a simple test in VS2010 where I include sqlite3.h (that is, no DLL). Using the same calls/queries as in Delphi, I get this order of calls: xConnect (enter function) xConnect (exit function) xDisconnect (enter function) xDisconnect (exit function) xConnect (enter function) xConnect (exit function) xCommit (enter function) xCommit (exit function) xBestIndex (enter function) xBestIndex (exit function) And here the application crashes sending Windows to search for a solution. The SQL statement used to create my virtual table, in both cases, is: CREATE TABLE x (Severity TEXT, Source TEXT, IP TEXT, Message TEXT) And, finally, both cases are run against a newly created database. Does anyone have any ideas to what I'm doing wrong? Anyone having some source of how to implement a really simple virtual table (in any language)? I am aware of that I am uncertain of how to implement xBestIndex, but that is my next headache, I guess... :) Best regards all! /Jörgen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] (no subject)
Hi! Thank You for replying Stefanos! Unfortunately, the errors were typos on my part. Sorry about that. As I wrote, everything works fine the first time around. What I am trying to accomplish is an update from the source data. If the entry is already there, just ignore it. Otherwise add the new data. I'm using version 3.7.12. Theories anyone? Regards, /Jörgen skrev 1970-01-01 01:00: You are missing parentheses here: create table if not exists a (id integer primary key autoincrement, a text unique, b default current_timestamp); create table if not exists b (id integer primary key autoincrement, a integer references a(id) on update cascade on delete cascade, b text, c text, d text, e text, f real, g text, h text, unique (a, b) on conflict ignore); create table if not exists c (id integer primary key autoincrement, a integer, b integer references b(id) on update cascade on delete cascade, c integer, d text, e integer, f text, g blob, unique (a, b, c) on conflict ignore); Also here you miss 'a' insert into c (a, b, c, d, e, f, g) values (:a, :b, :c, :d, :e, :f, :g); Also what sqlite version are you using? I have run this with the latest available version (3.7.12.1) under SQLite Manager (Firefox / Iceweasel extension). If you need any further help, please let me know. Cheers. Stefanos ____ From: Jörgen Hägglund <jor...@sirlucifer.mine.nu> To: sqlite-users@sqlite.org Sent: Sunday, June 3, 2012 11:10 PM Subject: [sqlite] Foreign key constraint failed Hi all! I have just recently discovered the strengths of foreign keys but now, I've been banging my head against the wall for a couple of days. I just can't figure out what is causing the error or how to get around it. Here's the specs (simplified) of my DB; create table if not exists a (id integer primary key autoincrement, a text unique, b default current_timestamp); create table if not exists b (id integer primary key autoincrement, a integer references a(id) on update cascade on delete cascade, b text, c text, d text, e text, f real, g text, h text, unique (a, b) on conflict ignore; create table if not exists c (id integer primary key autoincrement, a integer, b integer references b(id) on update cascade on delete cascade, c integer, d text, e integer, f text, g blob, unique (a, b, c) on conflict ignore; And the insert query; insert into c (b, c, d, e, f, g) values (:a, :b, :c, :d, :e, :f, :g); The problem is that I get a "Foreign key constraint failed" error when I try to insert the same data twice. The first time it works as it should and the data is inserted correctly. But, the second time, I get the exception. Is there anyone out there who's willing (and able) to help me? And hopefully explain what I'm doing wrong. Regards, /Jörgen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Foreign key constraint failed
Hi all! I have just recently discovered the strengths of foreign keys but now, I've been banging my head against the wall for a couple of days. I just can't figure out what is causing the error or how to get around it. Here's the specs (simplified) of my DB; create table if not exists a (id integer primary key autoincrement, a text unique, b default current_timestamp); create table if not exists b (id integer primary key autoincrement, a integer references a(id) on update cascade on delete cascade, b text, c text, d text, e text, f real, g text, h text, unique (a, b) on conflict ignore; create table if not exists c (id integer primary key autoincrement, a integer, b integer references b(id) on update cascade on delete cascade, c integer, d text, e integer, f text, g blob, unique (a, b, c) on conflict ignore; And the insert query; insert into c (b, c, d, e, f, g) values (:a, :b, :c, :d, :e, :f, :g); The problem is that I get a "Foreign key constraint failed" error when I try to insert the same data twice. The first time it works as it should and the data is inserted correctly. But, the second time, I get the exception. Is there anyone out there who's willing (and able) to help me? And hopefully explain what I'm doing wrong. Regards, /Jörgen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Help request for a query...
Hi all!
I'm not sure if it's my brain melting or what it might be... :-)
I have a table defined as:
CREATE TABLE IF NOT EXISTS History (Path TEXT UNIQUE NOT NULL, Hits
INTEGER DEFAULT 0)
Is there any way of making inserting data as follows;
- If Path does not exist in the table;
INSERT INTO History VALUES ('c:\', 1)
Should yield:
c:\,1
- But, if Path already exists do this:
UPDATE History SET Hits = Hits + 1 WHERE Path = 'c:\'
Should yield:
c:\,2
Of course, the 'c:\' is entered programmatically (using params).
Anyone up for modifying, explaining and solving this? :-)
Best regards,
/Jörgen
*
* Engelska
* Svenska
* Franska
* Tyska
* Engelska
* Svenska
* Franska
* Tyska
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Need help constructing a query
Hi all! I have three tables, one containing IP addresses as integers, lets call it 'base'. A second table containing IP ranges and the country code the range belongs to. Lets call this 'IPtoCC' with the fields IPFrom and IPTo as integers and CC as text. Then a third, 'Exclude', containing country codes i want to be excluded with a single field CC as text. What I need is to delete all records in 'base' where base.IP falls into a range of IPtoCC.IPFrom to IPtoCC.IPTo and that IPtoCC.CC is in Exclude.CC. Is it possible to do this in a single DELETE? Regards, /Jörgen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Compiling without dependencies on Windows?
Hi all! Anyone being able to give me some pointers on how to compile SQLite3 into dependency-free .obj's on Windows? The only reason for this is to import it into Delphi, and dependencies are driving me crazy... It doesn't matter if You give me an example (or maybe project source(s)) in Borland C++ or Microsoft C++, I have both at my disposal. And please note, that I am not a C++-developer so nothing too tricky. ;) Thanks all in advance /Jörgen ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
