本地 Javascript 中的 SQLite 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5244471/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLite database in Javascript locally
提问by Tjekkles
I'm using a PhoneGap project on XCode. I am trying to connect to a SQLite databse by using Javascript.
我在 XCode 上使用 PhoneGap 项目。我正在尝试使用 Javascript 连接到 SQLite 数据库。
I have made a file "myDatabase.sqlite" in an SQLite tool. Now my question is how do I open that database in my code? Right now I'm using the following code:
我在 SQLite 工具中创建了一个文件“myDatabase.sqlite”。现在我的问题是如何在我的代码中打开该数据库？现在我正在使用以下代码：
var db;
var shortName = 'myDatabase';
var version = '1.0';
var displayName = 'myDatabase';
var maxSize = 65535;
db = openDatabase(shortName, version, displayName,maxSize);
db.transaction(function(transaction) {
transaction.executeSql('SELECT * FROM User;', [],
function(transaction, result) {
if (result != null && result.rows != null) {
for (var i = 0; i < result.rows.length; i++) {
var row = result.rows.item(i);
alert(row.ID);
}
}
}, errorHandler);
}, errorHandler, nullHandler);
The problem is that the database is empty because when i run it it gives the error 'No such table'. I think it created a new database named "myDatabase" and thats why it has no tables.
问题是数据库是空的，因为当我运行它时，它给出了错误“没有这样的表”。我认为它创建了一个名为“myDatabase”的新数据库，这就是它没有表的原因。
Does anyone know how I can open my file with all the tables in it?
有谁知道如何打开包含所有表格的文件？
Thanks!
谢谢！
采纳答案by Sujeet
This script will help you:
该脚本将帮助您：
<script type="text/javascript">
function createDatabase(){
try{
if(window.openDatabase){
var shortName = 'db_xyz';
var version = '1.0';
var displayName = 'Display Information';
var maxSize = 65536; // in bytes
db = openDatabase(shortName, version, displayName, maxSize);
}
}catch(e){
alert(e);
}
}
function executeQuery($query,callback){
try{
if(window.openDatabase){
db.transaction(
function(tx){
tx.executeSql($query,[],function(tx,result){
if(typeof(callback) == "function"){
callback(result);
}else{
if(callback != undefined){
eval(callback+"(result)");
}
}
},function(tx,error){});
});
return rslt;
}
}catch(e){}
}
function createTable(){
var sql = 'drop table image';
executeQuery(sql);
var sqlC = 'CREATE TABLE image (id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, image BLOB )';
executeQuery(sqlC);
}
function insertValue(){
var img = document.getElementById('image');
var sql = 'insert into image (name,image) VALUES ("sujeet","'+img+'")';
executeQuery(sql,function(results){alert(results)});
}
<input type="button" name='create' onClick="createDatabase()" value='Create Database'>
<input type="button" name='create' onClick="createTable()" value='create table'>
<input type="button" name='insert' onClick="insertValue()" value='Insert value'>
<input type="button" name='select' onClick="showTable()" value='show table'>
<input type="file" id="image" >
<div result></div>
</script>
To download the code go visit url:
要下载代码，请访问网址：
http://blog.developeronhire.com/create-sqlite-table-insert-into-sqlite-table/
http://blog.developeronhire.com/create-sqlite-table-insert-into-sqlite-table/
回答by Merbin Joe
In my sqlite code I am using three js file for controlling sqlite one for debugging purpose, one for executing querys and another one for initilize the database and create basic tables.
在我的 sqlite 代码中，我使用三个 js 文件来控制 sqlite，一个用于调试目的，一个用于执行查询，另一个用于初始化数据库和创建基本表。
debug.js
startup.js
query.js
debug.js
startup.js
query.js
Reference URL ishttp://allinworld99.blogspot.in/2016/04/sqlite-first-setup.html
startup.js
参考网址为http://allinworld99.blogspot.in/2016/04/sqlite-first-setup.html
startup.js
var CreateTb1 = "CREATE TABLE IF NOT EXISTS tbl1(ID INTEGER PRIMARY KEY AUTOINCREMENT, CreatedDate TEXT,LastModifiedDate TEXT, Name TEXT)";
var CreateTb2 = "CREATE TABLE IF NOT EXISTS tbl2(ID INTEGER PRIMARY KEY AUTOINCREMENT, CreatedDate TEXT,LastModifiedDate TEXT,Mark INTEGER)";
var DefaultInsert = "INSERT INTO tbl1(CreatedDate,Name) select '" + new Date() + "','Merbin Joe' WHERE NOT EXISTS(select * from tbl1)";
var db = openDatabase("TestDB", "1.0", "Testing Purpose", 200000); // Open SQLite Database
$(window).load(function()
{
initDatabase();
});
function createTable() // Function for Create Table in SQLite.
{
db.transaction(function(tx)
{
tx.executeSql(CreateTb1, [], tblonsucc, tblonError);
tx.executeSql(CreateTb2, [], tblonsucc, tblonError);
insertquery(DefaultSettingInsert, defaultsuccess);
}, tranonError, tranonSucc);
}
function initDatabase() // Function Call When Page is ready.
{
try
{
if (!window.openDatabase) // Check browser is supported SQLite or not.
{
alert('Databases are not supported in your device');
}
else
{
createTable(); // If supported then call Function for create table in SQLite
}
}
catch (e)
{
if (e == 2)
{
// Version number mismatch.
console.log("Invalid database version.");
}
else
{
console.log("Unknown error " + e + ".");
}
return;
}
}
debug.js
调试.js
function tblonsucc()
{
console.info("Your table created successfully");
}
function tblonError()
{
console.error("Error while creating the tables");
}
function tranonError(err)
{
console.error("Error processing SQL: " + err.code);
}
function tranonSucc()
{
console.info("Transaction Success");
}
query.js
查询.js
function insertquery(query, succ_fun)
{
db.transaction(function(tx)
{
tx.executeSql(query, [], eval(succ_fun), insertonError);
});
}
function deletedata(query, succ_fun)
{
db.transaction(function(tx)
{
tx.executeSql(query, [], eval(succ_fun), deleteonError);
});
}
function updatedata(query, succ_fun)
{
db.transaction(function(tx)
{
tx.executeSql(query, [], eval(succ_fun), updateonError);
});
}
function selectitems(query, succ_fun) // Function For Retrive data from Database Display records as list
{
db.transaction(function(tx)
{
tx.executeSql(query, [], function(tx, result)
{
eval(succ_fun)(result.rows);
});
});
}
回答by essi
I had same problem and I found out that you cannot use your sqlite db like this.
我有同样的问题，我发现你不能像这样使用你的 sqlite 数据库。
I used chrome and I found out that Chrome stores DBs in "C:\Users\%USERNAME%\AppData\Local\Google\Chrome\User Data\Default\databases".
我使用了 chrome，我发现 Chrome 将数据库存储在"C:\Users\%USERNAME%\AppData\Local\Google\Chrome\User Data\Default\databases".
There is a Databases.dbthat Chrome uses for managing DBs.
还有一个Databases.db是Chrome浏览器使用，用于管理数据块。
If you want to use your db you should add a record into Databases.dband put your file in "file__0"directory and rename it (your db file) to the id that is assigned to that record.
如果你想使用你的数据库，你应该添加一条记录Databases.db并将你的文件放在"file__0"目录中，然后将它（你的数据库文件）重命名为分配给该记录的 ID。
回答by MattP
myDatabase and myDatabase.sqlite are 2 different filenames, update your code to reference the correct filename with extension.
myDatabase 和 myDatabase.sqlite 是 2 个不同的文件名，更新您的代码以引用带有扩展名的正确文件名。
SQLite does automatically create a new empty database if you try to open a database that doesn't exist.
如果您尝试打开一个不存在的数据库，SQLite 会自动创建一个新的空数据库。
