SQLite syntax using the Mono.Data.SqliteClient library

Question

I'm writing an inventory system in Unity 5.4 using SQLite and I can't figure out the syntax or find a good example anywhere. Ideas?

public void GetInventory(string _user, string _container) {
    ExecuteSQL("SELECT User (Name), Modifier (ModName), Property (PropName) " +
        "FROM User " +
            "[INNER] JOIN Ownership " +
            "[INNER] JOIN Container " +
                "[INNER] JOIN Inventory " +
                    "[INNER] JOIN Item " +
                        "[INNER] JOIN Property " +
                            "[INNER] JOIN Modifier " +
                                "ON User (UserID) = Ownership (UserID) " +
                                "AND Ownership (ContainerID) = Container (ContainerID) " +
                                "AND Container (ContainerID) = Inventory (ContainerID) " +
                                "AND Inventory (ItemID) = Item (ItemID) " +
                                "AND Item (PropertyID) = Property (PropertyID) " +
                                "AND Item (ModifierID) = Modifier (ModifierID) " +
                            "WHERE User (Name) = '" + _user + "' AND Container (ContainerName) = '" + _container + "'", false);
}

I've tested the function with simpler commands and it works fine. And I've tested the function in a DB manager. I'm not familiar with this flavor of the SQLite syntax and I can't find a good example anywhere. Can anyone point out where this is going wrong?

The error I'm getting is "No such function: User"

No such function: User ... likely caused by things such as WHERE User (Name) ... I have never seen a query written like this. It doesn't look like SQLite or ANSI compliant. — Tim Biegeleisen
– Tim Biegeleisen, Commented Dec 8, 2016 at 1:32
I'm writing in C#. This is where it's getting sent: filesbit.ch/u/i/8f87af392b51b2cfaa64b104a8d9a2f5.png It doesn't do anything yet. I'm just trying to get it to compile. — Josh Lichty
– Josh Lichty, Commented Dec 8, 2016 at 1:39

Tim Biegeleisen · Accepted Answer · 2016-12-08 01:52:43Z

Your query syntax is off. This is how you should write this query:

SELECT t1.name,
       t7.ModName,
       t6.PropName
FROM User t1
INNER JOIN Ownership t2
    ON t1.UserID = t2.UserID
INNER JOIN Container t3
    t2.ContainerID = t3.ContainerID
INNER JOIN Inventory t4
    ON t3.ContainerID = t4.ContainerID
INNER JOIN Item t5
    ON t4.ItemID = t5.ItemID
INNER JOIN Property t6
    ON t5.PropertyID = t6.PropertyID
INNER JOIN Modifier t7
    ON t5.ModifierID = t7.ModifierID
WHERE t1.Name = '" + _user + "' AND
      t3.ContainerName = '" + _container + "'"

I've never worked with SQLite in .NET, but that doesn't matter because your syntax doesn't follow anything I know. By the way, your immediate error was probably caused by this:

SELECT User (Name)

SQLite thinks you are trying to call a function named User. Other major problems included putting all the ON clauses together after the joins. The ON clause needs to appear after each join.

Collectives™ on Stack Overflow

SQLite syntax using the Mono.Data.SqliteClient library

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related