SQLite 3 - Foreign Key Problem

C

CryoGenID

Active Member
Licensed User
Longtime User
Hello everybody!

I am currently working on a desktop program, which should read and write things to a SQLite 3 - DB.
I have bought and used "SQLite Maestro" to create a (somewhat complex) DB, with foreign keys used.

But if I try to open that DB inside B4PPC, I get an error about a malformed DB:
"Error Description:
The database disk image is malformed
malformed database schema (Q1) - near "NO":syntax error"

I have then created a new DB, with just two tables, those opened perfectly fine. But as soon as I add a foreign key, I get the error from above.

Now I am shocked as I have spent a lot of time to create the (real complex) DB (not this demo db ;-) ) and hope that anybody from you knows what I could do here to get this working :-?
I need this DB with the foreign keys :-/

I have attached a ZIP file, consisting of a small demo-program, which simply opens the DB and tries to read s.th. from the (empty) DB and the test-DB-File with the two tables and the one foreign key.

Thanks a lot for any help on this! :)

Best regards,

Christian
 

Attachments

  • SQL-Test.zip
    5.8 KB · Views: 271
C

CryoGenID

Active Member
Licensed User
Longtime User
Erel,

thanks for your reply.
I just tried it myself:
- Downloaded my ZIP-File from the Post above
- Downloaded and installed the current SQlite Expert Professional Trial from SQLite Expert - SQLite Database Administration Tool

"SQlite Expert Professional" opened my test-db absolute fine :confused:

Would you please be so kind as to re-download the ZIP-File and try to open it again?

Thanks a lot and best regards,

Christian
 
C

CryoGenID

Active Member
Licensed User
Longtime User
Erel,

thanks for your updates.
So I have seen that the current SQLite is 3.6.23 and the newest DLL from the System.Data.SQLite - Website ist 3.6.16, so there the foreign keys should be included already...
Can I update the DLL used by Basic4PPC myself or can you provide me/the community with an updated version?

Thanks a lot in advance for your help and support!

Best regards,

Christian
 
C

CryoGenID

Active Member
Licensed User
Longtime User
Erel,

thanks for your reply!
If you could update the DLL soon, that would be perfect, as my work is currently on hold as I cannot access the database ;-)

Thanks again for your help and support, buying your software was definitely a good idea :)

Best regards,

Chris
 
C

CryoGenID

Active Member
Licensed User
Longtime User
Erel,

have you already found time to look into the DLL?

I currently cannot work on my PhD-Thesis, as I need the DB-Access... ;-)

It would be perfect if you would find time soon to re-compile the DLL ;-)

Thanks a lot anyway in advance!

Best regards,

Christian
 
C

CryoGenID

Active Member
Licensed User
Longtime User
Erel,

thanks a lot for your answer :)

And thanks for your trying to integrate the new version even before the next release :)

Best regards,

Christian
 
C

CryoGenID

Active Member
Licensed User
Longtime User
Erel,

thanks for the release of the new version! :)

I am now nearly ripping my hair out, it still does not work :-(

I am still getting the exact same error...

This is a part of the SQLite-DB-Source:
B4X: 
CREATE TABLE Q1 (
  Q1ID  integer PRIMARY KEY AUTOINCREMENT NOT NULL,
  Name  integer,
  /* Foreign keys */
  FOREIGN KEY (Name)
    REFERENCES UserData(UDID)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
);

As I am getting the exact same error message as in my first post in this thread ("... near 'NO' ..."), I think the problem is at the "NO ACTION"-part... Could anybody please help me?

I am really stuck with my PhD-Thesis and have no idea why the system doesn't seem to accept the "NO ACTION"-Part... Inside the SQL-Creation-Program (SQLite Maestro) I have no chance to leave the "ON DELETE" and "ON UPDATE"-Parts out...

I would be really thankful if anybody could help me here...

Thanks a lot in advance!

Best regards,

Chris
 
C

CryoGenID

Active Member
Licensed User
Longtime User
Update:
It really seems to be the "NO ACTION"-Statement.
If I use your Software (SQLite Expert Professional), I can delete the "NO ACTION"-Statement and then the DB can be opened.
I haven't tried any transactions etc. yet, but it can be opened without an error at least.

The problem is, that normally a statement has to be written there, so the "NO ACTION"-Statement would be the correct one as I don't want to do anything on "delete" and on "update". So the "trick" with deleting the "NO ACTION" and replacing it with "" is not the preferable way and my software (SQLite Maestro) doesn't even offer it, so that seems to be a "trick" only the software you use (SQlite Expert Professional) knows about ;-)

Could you simply add the "NO ACTION" as a statement which is ignored in the DLL? So at the point where the DLL checks for the Statements for "on update" and "on delete" simply add an "OR" and check for the string "NO ACTION"?

Thanks a LOT in advance for your help and support regarding this problem!

Best regards,

Chris
 
C

CryoGenID

Active Member
Licensed User
Longtime User
Thanks Erel!

Hm well then I have to think about what to do now... :-/
Hopefully the SQLMaestro-Guys will add the ""-Statement to their software... ;-)

Best regards,

Chris
 
You must log in or register to reply here.
Top