Cordova-sqlite-storage: Pragma foreign_keys ON doesn't work on IOS

[gogiel]

Problem:
I am unable to set PRAGMA foreign_keys to ON on iOS 7 (Iphone Simulator).
Code example:

db.transaction(function(tx){
    tx.executeSql("PRAGMA foreign_keys=ON;", [], function(tx, res){
        tx.executeSql("PRAGMA foreign_keys;", [], function(tx, res){
            console.log('foreign_keys:', res.rows.item(0).foreign_keys); // foreign_key: 0
        })
    })
}, function(e){
    console.log(e)
});

[mirko77]

I have the same problem, on the device as well (iPhone 5S) Did you solve this?

[brodybits]

PRAGMA statements must be executed in executeSql() on the db object and NOT in a transaction object. This is already documented in the readme. Documentation needs improvement.

[mirko77]

Actually I tried in any possible way without success, I ended up using triggers on iOS and PRAGMA on Android. All good that way

[brodybits]

Actually I tried in any possible way without success, I ended up using
triggers in iOS and PRAGMA on Android. All good that way

Good _workaround_, though it does sounds a bit clumsy in general practice.
I will treat this as a bug, for further investigation (someday when I get a
chance).

[canac]

It has been a few months now, any updates on the status of a fix for this bug?

[ronaldaraujo]

Any solution? I'm having the same problem!

[KPaul31]

Sorry for repeating but has anyone found a solution for this "bug".
However i can seem to get the pragma to work on android to put foreign keys on using the following code.
db.executeSql("PRAGMA foreign_keys=ON;", [], function(res) {
db.executeSql("PRAGMA foreign_keys;", [], function(res){
console.log("PRAGMA res: " + JSON.stringify(res));
})
})

[ronaldaraujo]

KPaul31 tried running the code that you mentioned, but it did not work...

[mirko77]

As I posted above, one solution is to use triggers and forget about PRAGMA on iOS

[ronaldaraujo]

Hi mirko77. I am new to developing for android. Could you show an example. Thanks.

[mirko77]

The problem with PRAGMA is on iOS only, on Android it works ace.

This is how you activate it , as it is off by default

EC.db = window.sqlitePlugin.openDatabase({
            name : "database"
        });

//enable PRAGMA to use foreign keys constraint: it is OFF by default
EC.db.executePragmaStatement("PRAGMA foreign_keys=ON;", function(res) {
     console.log("PRAGMA res: " + JSON.stringify(res));
});


//your transaction here
EC.db.transaction(...);

EC.db is your database object

[ronaldaraujo]

Taking advantage of the space, someone could tell me where the .db file generated by the plugin? I am planning to run some queries via shell coommand using sqlit3 ...

[mirko77]

db file on Android is on /data/data/<package_name>/databases/

you need root access on the device to access that folder

[mirko77]

On Android, executePragmaStatement does not work anymore with the latest release?

It used to, but now I get a "Object [object Object] has no method 'executePragmaStatement'" on the code I posted above

If I try to use PRAGMA with the latest version of the plugin on executeSql, it does not work anymore, kinda like the database enters a sort of infinite loop, at least going through breakpoints on Chrome Dev Tools I had that impression...

Edit: using @KPaul31 code to enable PRAGMA on Android seems to work!

[brodybits]

To execute a PRAGMA statement, please use db.executeSql() _as documented in README.md_.

I will add a note to README.md to make this more clear.

[brodybits]

Note added to README.md to make this clear.