更好的SQLITE 3：我得到的是undefined而不是表的默认值

oknrviil 于 2022-12-13 发布在 SQLite

关注(0)|答案(2)|浏览(176)

我希望看到为表设置的默认值，但得到的却是undefined
这是代码：

import Database from 'better-sqlite3-multiple-ciphers'

// https://gist.github.com/anhdiepmmk/b8dcd1c7be8c09580f607ef186529192
// https://www.sqlite.org/quirks.html#no_separate_boolean_datatype
// https://www.sqlite.org/stricttables.html#strict_tables

const commandToCreateJitsiSettingsTable = `
  CREATE TABLE IF NOT EXISTS jitsiTable (
    id INTEGER PRIMARY KEY AUTOINCREMENT,

    alwaysOnTopWindowEnable INTEGER NOT NULL DEFAULT 1,
    disableAGC INTEGER NOT NULL DEFAULT 0,
    serverURL TEXT,
    serverTimeout REAL DEFAULT undefined,

    created_at timestamp NOT NULL DEFAULT current_timestamp,
    updated_at timestamp NOT NULL DEFAULT current_timestamp
  )
`

if (!fs.existsSync(path.join(settingsUserDataFolder, "Settings.db")) {
  const SettingsDB = new Database(path.join(settingsUserDataFolder,"Settings.db"), {})
  SettingsDB.pragma('journal_mode = WAL')
  SettingsDB.pragma("rekey='secret-key'");

  SettingsDB.exec(commandToCreateJitsiSettingsTable)

  SettingsDB.close();
} else {

  console.log("Settings.db already exists")

  const SettingsDB = require('better-sqlite3-multiple-ciphers')(path.join(settingsUserDataFolder,"Settings.db"), {})
  SettingsDB.pragma("key='secret-key'");

  const row = SettingsDB.prepare("SELECT * FROM jitsiTable");
  console.log(row.alwaysOnTopWindowEnable, row.disableAGC, row.serverURL, row.serverTimeout, row.created_at, row.updated_at)

  SettingsDB.close();

}

输出量：

Settings.db already exists
undefined undefined undefined undefined undefined undefined

如何正确地设置然后获取表中的默认值？

sqlite

来源：https://stackoverflow.com/questions/74747014/better-sqlite3-i-get-undefined-instead-of-the-default-values-of-the-table

2条答案

按热度按时间

mrfwxfqh1#

您已准备好陈述式，但尚未撷取任何数据。您将row变数设定为准备好的陈述式，而非数据。您需要使用这些方法之一，例如.get（）或.all（），视您对准备好的陈述式撷取数据的需求而定。在您的情况下，可能是.get（）。如需详细信息，请参阅API文件。
例如，您可以将这些行

const row = SettingsDB.prepare("SELECT * FROM jitsiTable");
  console.log(row.alwaysOnTopWindowEnable, row.disableAGC, row.serverURL, row.serverTimeout, row.created_at, row.updated_at)

与

const stmt = SettingsDB.prepare("SELECT * FROM jitsiTable");

for (const row of stmt.iterate()) {
  console.log(row.alwaysOnTopWindowEnable, row.disableAGC, row.serverURL, row.serverTimeout, row.created_at, row.updated_at)
}

赞(0）回复(0）举报 2022-12-13

evrscar22#

多亏了@ PCemGuy和一个善良的better-sqlite3 repo的家伙，我意识到插入操作丢失了：

const SettingsDB = require('better-sqlite3-multiple-ciphers')
(path.join(settingsUserDataFolder,"Settings.db"), {})
  SettingsDB.pragma("key='secret-key'");

const insert = SettingsDB.prepare('INSERT INTO jitsiTable  
(alwaysOnTopWindowEnable, disableAGC, serverTimeout) VALUES (?, ?, ?)'); 
  const row = SettingsDB.prepare("SELECT * FROM jitsiTable").get();

  console.log(row.alwaysOnTopWindowEnable, row.disableAGC, row.serverURL, 
  row.serverTimeout, row.created_at, row.updated_at)

输出量：

0 1 null 30 2022-12-10 11:00:22 2022-12-10 11:00:22

iterate()用于获取all到目前为止的插入：

const SettingsDB = require('better-sqlite3-multiple-ciphers')
(path.join(settingsUserDataFolder,"Settings.db"), {})
SettingsDB.pragma("key='secret-key'");

const insert = SettingsDB.prepare('INSERT INTO jitsiTable  
(alwaysOnTopWindowEnable, disableAGC, serverTimeout) VALUES (?, ?, 
?)');

  insert.run(0, 1, 30)

  const stmt = SettingsDB.prepare("SELECT * FROM jitsiTable");
  for (const row of stmt.iterate()) {
    console.log(row.alwaysOnTopWindowEnable, row.disableAGC,  
  row.serverURL, row.serverTimeout, row.created_at, row.updated_at)
  }

SettingsDB.close();

Output：

0 1 null 30 2022-12-10 11:00:22 2022-12-10 11:00:22
0 1 null 30 2022-12-10 11:05:46 2022-12-10 11:05:46
0 1 null 30 2022-12-10 11:18:42 2022-12-10 11:18:42
0 1 null 30 2022-12-10 11:22:10 2022-12-10 11:22:10
0 1 null 30 2022-12-10 11:24:18 2022-12-10 11:24:18
0 1 null 30 2022-12-10 11:27:17 2022-12-10 11:27:17
0 1 null 30 2022-12-10 11:35:28 2022-12-10 11:35:28

赞(0）回复(0）举报 2022-12-13

我来回答

更好的SQLITE 3：我得到的是undefined而不是表的默认值

2条答案

相关问题

热门标签

最新问答