CODEWITHSUNDEEP
sqlsqlitesyntax-errorunity-game-engine
Josh Lichty
Josh Lichty

Reputation: 3

SQLite syntax using the Mono.Data.SqliteClient library

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"

Upvotes: 0

Views: 56

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

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.

Upvotes: 1

Related Questions