JavaScript中的SQLite访问 [英] SQLite access in Javascript
问题描述
我想使用JavaScript代码访问Sql Lite数据库.该JavaScript代码在html5中使用，必须部署在blackberry 10平台上. 我使用以下代码未成功:
I want to access Sql Lite Database with JavaScript code. The JavaScript code is used in html5 and has to be deployed on blackberry 10 platform. I use the following code without success:
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html; charset=ISO-8859-1" http-equiv="content-type">
<title>Prova</title>
</head>
<body>
<script type="text/javascript">
//Provenia SRL ITC - Paola Savioli
//Questa funzione apre il database SQL Lite
//Il parametro che va cambiato è il nome del database
function ApriDatabase() {
try {
if (window.openDatabase) {
var shortName = 'Ristoranti.sqllite';
var version = '1.0';
var displayName = 'Ristoranti italia';
var maxSize = 65536; // in bytes
db = openDatabase(shortName, version, displayName, maxSize);
}
} catch (e) {
alert('Apri Database' + e);
}
}
//Provenia SRL ITC - Paola Savioli
// Questa funzione eseque una query su un database aperto con la funzione ApriDatabase
function EseguiQuery($query, callback) {
try {
ApriDatabase();
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) {
alert('Esegui Query' + e);
}
}
function VisualizzaComuni() {
try {
var schemanode = document.GetElementById('RCOMUNI');
schemanode.innerHTML = "";
var result = EseguiQuery('SELECT * FROM COMUNE');
for (var i = 0; i < result.rows.lenght; ++i) {
var row = result.row.item(i);
var notediv = document.createElement('div');
notediv.innerHTML = 'Codice Provincia:' + row['PROVINCIA'] + 'Nome:' + row['NAME'];
schemanode.appendchild(notediv);
}
} catch (e) {
alert('Visualizza Comuni' + e);
}
}
</script>
<input type="button" name='select' onClick="VisualizzaComuni()"
value='Visualizza Comuni'>
<div id="RCOMUNI"></div>
</body>
</html>
推荐答案
您发布的代码存在一些问题，包括引用.lenght而不是.length以及在出现以下情况时使用try catch块有内置的成功和错误处理程序.所以我制作了一个演示.
There were a few problems with the code you've posted, including a reference to .lenght instead of .length and use of try catch blocks when there are success and error handlers built-in. So I worked up a demo.
首先，它似乎没有什么不同，但是这是HTML5 对吗?代替HTML 4.01 Transitional文档类型，使用HTML5文档类型:
First, it does not seem to make a difference, but this is HTML5 right? Instead of an HTML 4.01 Transitional doctype, use the HTML5 doctype:
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
<title>demo by userdude</title>
...
接下来，出于演示的目的，我修改了标记.在这种情况下，我们有:
Next, I modified the markup for the purposes of the demonstration. In this case, we have:
<body>
<input type="button" id="run" value='Run Query'>
<div id="query"></div>
<table id="table" border="1" cellspacing="1" cellpadding="5"></table>
</body>
</html>
在head元素中，我使用事件侦听器等待DOM加载.请记住，我没有使用Blackberry进行测试，并且在Blackberry或其他设备上，您可能应该使用deviceready而不是load.我认为.我还附加了button的事件处理程序，该事件处理程序使用.addEventListener运行查询，但是请注意，我是在加载处理程序中完成 的.您必须等待才能尝试访问DOM.
In the head element, I use an event listener to wait for the DOM to load. Keep in mind, I do not have a Blackberry to test this with, and with Blackberry or other devices, you should probably use deviceready instead of load. I think. I also attach the event handler for button that run's the query using .addEventListener, but notice I do that within the load handler. You have to wait before trying to access the DOM.
此外，IE支持attachEvent而不是addEventListener.我可以想象Blackberry支持后者，但是我不确定.
Also, IE supports attachEvent instead of addEventListener. I would imagine Blackberry supports the latter, but I'm not sure.
window.addEventListener('load', function load(){
var run = document.getElementById('run'),
data = document.getElementById('table'),
qtext = document.getElementById('query'),
dropped = false,
created = false,
cities = ['Houston', 'Dallas', 'Paris', 'New York', 'Buenos Aires', 'London'],
shortName = 'Cities',
version = '1.0',
displayName = 'Cities Demo',
maxSize = 5 * 1024 * 1024,
db = false,
queries = [];
run.addEventListener('click', query);
这将建立我的数据库，包括运行对populate()的初始调用，以便我们使用一些数据.
This establishes my database, including running the initial call to populate() so we have some data use.
open();
这是我添加到运行按钮的功能.
This is the function I added to the run button.
function query() {
transact('SELECT * FROM Cities', view);
}
这仅是为了向数据库添加数据.请参见上面的cities变量.
This is just meant to add data to the database. See the cities variable above.
function populate(tx) {
var city,
i = 0;
将cities条目数组清空到INSERT后，我将阻止其运行. dropped和created对于DROP和CREATE事务执行相同的操作.
I block this from running once I've emptied the cities array of entries to INSERT. dropped and created do the same thing for the DROP and CREATE transactions.
请 special 注意我的工作方式；看到transact('...', populate)吗?在这种情况下，我将使用populate循环返回，直到完成所有cities条目的添加.这是异步的，因此您必须设置回调以在必要时等待前面的查询运行.在这种情况下，我可能会在添加行后最终删除表.因此，我必须等待，然后 then 遍历cities列表.
Take special note how I'm doing this; see the transact('...', populate)? I use populate in this situation to loop back until I've finished adding all of the cities entries. This is asynchronous, so you have to setup the callbacks to wait if necessary for the previous queries to run. In this case, I could end up dropping the table after adding my rows. So I have to wait, then loop through the cities list.
if (cities) {
if (!dropped) {
dropped = true;
transact('DROP TABLE IF EXISTS Cities', populate);
return;
}
if (!created) {
created = true;
transact('CREATE TABLE IF NOT EXISTS Cities (id unique, City)', populate);
return;
}
我不需要在这里迭代到populate，因为我只需要INSERT并继续前进即可.
I don't need to iterate back to populate here, since I just need to INSERT and move on.
while (city = cities.pop()) {
transact('INSERT INTO Cities (id, City) VALUES (' + i++ + ', "' + city + '")');
}
cities = false;
}
}
此功能的全部作用是为数据库或return false提供打开的引用或新的引用.这会缩短transact()的执行.
All this function does is give either an opened or new reference to the database, or return false. This short-circuits the execution of transact().
function open() {
if (!db && window.openDatabase) {
db = window.openDatabase(shortName, version, displayName, maxSize);
}
if (cities) {
db.transaction(populate);
}
return db;
}
这是脚本的内容.我从query()调用它，在这种情况下，callback是view，它指向运行结果集并从结果集中创建table的函数.
This is the meat of the script. I call it from query(), and the callback in this case is view, which points to the function which runs through the result set and creates a table from the set.
function transact(query, callback) {
var cb = callback,
qel = document.createElement('p'),
qid = queries.length;
if (!open()) {
console.log('HTML5 Database not supported.');
return false;
}
db.transaction(transact_cb);
qel.innerHTML = query + ' Query Result: <span id="q' + qid + '">Pending...</span>';
qtext.appendChild(qel);
queries[qid] = query;
请注意最后两个参数transact_success, transact_error.这就是您处理这些异步调用的方式.
Note the last two arguments, transact_success, transact_error. This is how you handle these asynchronous calls.
function transact_cb(tx) {
tx.executeSql(query, [], transact_success, transact_error);
}
不太确定为什么里面有eval吗??
Not quite sure why there's an eval in there...?
function transact_success(tx, result) {
var rtext = document.getElementById('q' + qid);
rtext.className = 'success';
rtext.innerHTML = 'Success.';
if (typeof cb == "function") {
cb(result);
} else if (cb != undefined) {
eval(cb + "(result)");
}
}
请注意console.log(error);.
function transact_error(tx, error) {
var rtext = document.getElementById('q' + qid);
rtext.className = 'error';
rtext.innerHTML = 'Error logged to console.';
console.log(error);
}
}
然后此函数创建table结果集视图.您可能会注意到，我遍历每一行，和每一行的列.
And this function creates the table result set view. You'll probably notice I loop through each row, and each row's columns.
function view(result) {
var thead = '<thead><tr>',
tbody = '<tbody>',
row,
col;
for (var i = 0, rows = result.rows.length; i < rows; ++i) {
row = result.rows.item(i);
tbody += '<tr>';
for (col in row) {
if (i === 0) {
thead += "<th>" + col + "</th>";
}
tbody += '<td>' + row[col] + '</td>';
}
tbody += '</tr>';
}
thead += '</tr></thead>';
tbody += '</tbody>';
data.innerHTML = thead + tbody;
}
});
您可以通过在此处下载HTML文件来下载文件并在本地运行它(由于安全错误，它将无法在jsFiddle上运行):
You can download the file and run it locally (due to a security error, it won't run on jsFiddle) by downloading the HTML file here:
所以你去了.希望这将有助于使它更容易理解.如果您有任何问题，请告诉我.
So there you go. Hopefully that will help make this easier to understand. Let me know if you have any questions.
这篇关于JavaScript中的SQLite访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
