多个SQLite CREATE TABLE语句:只有第一个执行。

huangapple go评论76阅读模式
英文:

Multiple SQLite CREATE TABLE statements: only first one executes

问题

我在iOS应用程序启动时运行一些SQLite命令来准备数据库表。整个语句在不返回错误的情况下运行。
当我尝试将数据插入第二个或第三个表时,我收到了这个SQLite错误:“没有这个表:Images”。
在检查本地数据库后,只有第一个表被创建。我的SQL语句如下。整个语句在sqliteonline.com上完美运行,并且所有表都被创建。在iOS上运行时可能出现什么问题?

static func CreateDatabase() -> Bool {
    let cmd = """
    CREATE TABLE IF NOT EXISTS Conversations(Id TEXT PRIMARY KEY UNIQUE NOT NULL, InsertDate REAL NOT NULL, LastMsgDate REAL NOT NULL, Name TEXT NULL, AvatarId INTEGER NOT NULL, Unread INTEGER NOT NULL DEFAULT '0', LastMsg TEXT NULL, Flags INTEGER NULL, ReservedText TEXT NULL, ReservedInt INTEGER NULL);
    CREATE TABLE IF NOT EXISTS Communities(Id INTEGER NOT NULL, UbietyId INTEGER NOT NULL, Name TEXT NOT NULL, Distance REAL NOT NULL, InsertDate REAL NOT NULL, Photo BLOB NULL, ReservedText TEXT NULL, ReservedInt INTEGER NULL, PRIMARY KEY (Id, UbietyId) ON CONFLICT REPLACE);
    CREATE TABLE IF NOT EXISTS Images(Id TEXT PRIMARY KEY UNIQUE NOT NULL, InsertDate REAL NOT NULL, ExpiryDate REAL NULL, Data BLOB NOT NULL, ReservedText TEXT NULL, ReservedInt INTEGER NULL);
    """
    var pointer: OpaquePointer?
    guard sqlite3_prepare_v2(db, cmd, -1, &pointer, nil) == SQLITE_OK else {
        print(String(cString: sqlite3_errmsg(db)))
        return false
    }
    
    guard sqlite3_step(pointer) == SQLITE_DONE else {
        print(String(cString: sqlite3_errmsg(db)))
        return false
    }
    sqlite3_finalize(pointer)
    return true
}

请检查您的iOS应用程序是否连接到正确的数据库文件。如果只有第一个表被创建,可能是因为数据库连接或文件路径不正确。确保您的SQLite数据库文件位于正确的位置并且与应用程序的连接是正确的。如果问题仍然存在,请检查数据库文件的权限以确保应用程序可以写入它。

英文:

I'm running some SQLite commands at the launch of an iOS app to prepare database tables. The whole statement runs without returning an error.
When I try to insert data into second or third tables, I get this SQLite error: "No such table: Images".
After inspecting the local database, only the first table was created. My SQL statement is as below. Entire statement runs perfectly in sqliteonline.com and all tables are created. What could be the problem running on iOS?

static func CreateDatabase() -> Bool {
    let cmd = """
CREATE TABLE IF NOT EXISTS Conversations(Id TEXT PRIMARY KEY UNIQUE NOT NULL, InsertDate REAL NOT NULL, LastMsgDate REAL NOT NULL, Name TEXT NULL, AvatarId INTEGER NOT NULL, Unread INTEGER NOT NULL DEFAULT '0', LastMsg TEXT NULL, Flags INTEGER NULL, ReservedText TEXT NULL, ReservedInt INTEGER NULL);
CREATE TABLE IF NOT EXISTS Communities(Id INTEGER NOT NULL, UbietyId INTEGER NOT NULL, Name TEXT NOT NULL, Distance REAL NOT NULL, InsertDate REAL NOT NULL, Photo BLOB NULL, ReservedText TEXT NULL, ReservedInt INTEGER NULL, PRIMARY KEY (Id, UbietyId) ON CONFLICT REPLACE);
CREATE TABLE IF NOT EXISTS Images(Id TEXT PRIMARY KEY UNIQUE NOT NULL, InsertDate REAL NOT NULL, ExpiryDate REAL NULL, Data BLOB NOT NULL, ReservedText TEXT NULL, ReservedInt INTEGER NULL);
"""
    var pointer: OpaquePointer?
    guard sqlite3_prepare_v2(db, cmd, -1, &pointer, nil) == SQLITE_OK else {
        print(String(cString: sqlite3_errmsg(db)))
        return false }
    
    guard sqlite3_step(pointer) == SQLITE_DONE else {
        print(String(cString: sqlite3_errmsg(db)))
        return false }
    sqlite3_finalize(pointer)
    return true
}

答案1

得分: 1

你只需使用 sqlite3_prepare_v2sqlite3_step 来执行单个 SELECTINSERTUPDATEDELETE 语句。

对于像你的 CREATE 语句这样的操作,你需要使用 sqlite3_exec

static func CreateDatabase() -> Bool {
    let cmd = """
CREATE TABLE IF NOT EXISTS Conversations(Id TEXT PRIMARY KEY UNIQUE NOT NULL, InsertDate REAL NOT NULL, LastMsgDate REAL NOT NULL, Name TEXT NULL, AvatarId INTEGER NOT NULL, Unread INTEGER NOT NULL DEFAULT '0', LastMsg TEXT NULL, Flags INTEGER NULL, ReservedText TEXT NULL, ReservedInt INTEGER NULL);
CREATE TABLE IF NOT EXISTS Communities(Id INTEGER NOT NULL, UbietyId INTEGER NOT NULL, Name TEXT NOT NULL, Distance REAL NOT NULL, InsertDate REAL NOT NULL, Photo BLOB NULL, ReservedText TEXT NULL, ReservedInt INTEGER NULL, PRIMARY KEY (Id, UbietyId) ON CONFLICT REPLACE);
CREATE TABLE IF NOT EXISTS Images(Id TEXT PRIMARY KEY UNIQUE NOT NULL, InsertDate REAL NOT NULL, ExpiryDate REAL NULL, Data BLOB NOT NULL, ReservedText TEXT NULL, ReservedInt INTEGER NULL);
"""

    let result = sqlite3_exec(db, cmd, nil, nil, nil)
    if result == SQLITE_OK {
        return true
    } else {
        print(String(cString: sqlite3_errmsg(db)))
        return false
    }
}

请注意,我已经将 HTML 实体编码(如 "')还原为原始字符。

英文:

You only use sqlite3_prepare_v2 and sqlite3_step with single SELECT, INSERT, UPDATE, and DELETE statements.

For something like your CREATE statements, you need to use sqlite3_exec.

static func CreateDatabase() -> Bool {
    let cmd = """
CREATE TABLE IF NOT EXISTS Conversations(Id TEXT PRIMARY KEY UNIQUE NOT NULL, InsertDate REAL NOT NULL, LastMsgDate REAL NOT NULL, Name TEXT NULL, AvatarId INTEGER NOT NULL, Unread INTEGER NOT NULL DEFAULT '0', LastMsg TEXT NULL, Flags INTEGER NULL, ReservedText TEXT NULL, ReservedInt INTEGER NULL);
CREATE TABLE IF NOT EXISTS Communities(Id INTEGER NOT NULL, UbietyId INTEGER NOT NULL, Name TEXT NOT NULL, Distance REAL NOT NULL, InsertDate REAL NOT NULL, Photo BLOB NULL, ReservedText TEXT NULL, ReservedInt INTEGER NULL, PRIMARY KEY (Id, UbietyId) ON CONFLICT REPLACE);
CREATE TABLE IF NOT EXISTS Images(Id TEXT PRIMARY KEY UNIQUE NOT NULL, InsertDate REAL NOT NULL, ExpiryDate REAL NULL, Data BLOB NOT NULL, ReservedText TEXT NULL, ReservedInt INTEGER NULL);
"""

let result = sqlite3_exec(db, cmd, nil, nil, nil)
if result == SQLITE3_OK {
    return true
} else {
    print(String(cString: sqlite3_errmsg(db)))
    return false
}

答案2

得分: 0

大概率情况下,您的特定iOS SQLite 驱动程序 不允许在单个准备的语句API调用中执行多个语句。解决方法是为每个CREATE TABLE 调用使用单独的语句。

实际上,您的驱动程序正在执行正确的操作,因为允许在单个调用中包含多个语句可能会打开安全问题的大门,比如SQL注入。

英文:

Most likely, your particular iOS SQLite driver does not permit multiple statements executing in a single prepared statement API call. The fix here would be to just use separate statements for each CREATE TABLE call.

Actually, your driver is doing the right thing, because allowing multiple statements in a single call possibly opens the door to security problems such as SQL injection.

huangapple
  • 本文由 发表于 2023年7月20日 15:56:29
  • 转载请务必保留本文链接:https://go.coder-hub.com/76727781.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定