在Javascript中使用Sqllite访问 [英] Sqllite access in Javascript
问题描述
我想用JavaScript代码访问Sql Lite数据库。 JavaScript代码在html5中使用，必须在黑莓10平台上部署。
我没有成功地使用下面的代码：
<！DOCTYPE html PUBLIC - // W3C // DTD HTML 4.01 Transitional // EN>
< html>
< head>
< meta content =text / html; charset = ISO-8859-1http-equiv =content-type>
< title> Prova< / title>
< / head>
< body>
< script type =text / javascript>
// Provenia SRL ITC - Paola Savioli
// Questa funzione apre il数据库SQL Lite
// Il parametro che va cambiatoèil nome del database
function ApriDatabase（）{
尝试{
if（window.openDatabase）{
var shortName ='Ristoranti.sqllite';
var version ='1.0';
var displayName ='Ristoranti italia';
var maxSize = 65536; //以字节为单位
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 ，回调）{
try {
ApriDatabase（）;
if（window.openDatabase）{
db.transaction（
function（tx）{
tx.executeSql（$ query，[]，function（tx，结果）{
if（typeof（callback）==function）{
callback（result）;
} else {
if（callback！= undefined）{
eval（callback +（result））;
}
}
}，function（tx，error）{}）;
}）;
return rslt;
}
} catch（e）{
alert（'Esegui Query'+ e）;
函数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'）;
markediv.innerHTML ='Codice Provincia：'+ row ['PROVINCIA'] +'Nome：'+ row ['NAME'];
schemanode.appendchild（notediv）;
}
} catch（e）{
alert（'Visualizza Comuni'+ e）;
}
}
< / script>
< input type =buttonname ='select'onClick =VisualizzaComuni（）
value ='Visualizza Comuni'>
< div id =RCOMUNI>< / div>
< / body>
< / html>
能否帮助我？
Simone
您发布的代码存在一些问题，包括对 .lenght 而不是 .length 并使用 try catch 块内置成功和错误处理程序。所以我做了一个演示。
首先，它似乎没有什么区别，但是这是HTML5 正确吗？ $ b
<！DOCTYPE html>
< html>
< head>
< meta http-equiv =content-typecontent =text / html; charset = UTF-8>
< title> demo by userdude< / title>
...
接下来，我为演示目的修改了标记。在这种情况下，我们有：
< body>
< input type =buttonid =runvalue ='运行查询'>
< div id =query>< / div>
< table id =tableborder =1cellspacing =1cellpadding =5>< / table>
< / body>
< / html>
在头部元素中，我使用事件监听器等待DOM加载。请记住，我没有黑莓手机来测试它，并且使用Blackberry或其他设备，您应该使用 deviceready 而不是 load 。我认为。我还附加了使用 .addEventListener 运行查询的按钮的事件处理程序，但请注意我这样做在负载处理程序中。您必须等待才能访问DOM。还有，IE支持 attachEvent 而不是 addEventListener 。我会想象黑莓支持后者，但我不确定。
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（）的初始调用，以便我们有一些数据使用。
open（）;
这是函数I a （'SELECT * FROM Cities'，查看）
pre $ ;
}
这只是为了向数据库添加数据。请参阅上面的 cities 变量。
函数populate（tx）{
var city，
i = 0;
我在清空城市到 INSERT 的条目数组。 下降和创建为 DROP 做同样的事情， CREATE 交易。
请注意 。请参阅 transact（'...'，填充）？在这种情况下，我使用填充来循环，直到完成添加所有城市条目。这是异步的，因此必须设置回调才能在必要时等待以前的查询运行。在这种情况下，我最终可能会在添加我的行后删除表。因此，我必须等然后循环城市列表。
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 并继续。
<$ （城市= cities.pop（））{
transact（'INSERT INTO Cities（id，City）VALUES（'+ i ++ +'，''+ city +'） ）'）;
}
cities = false;
$ b $ p
$ b
所有这些函数都会给出一个打开的或新的引用数据库，或者返回false 。这会缩短 transact（）的执行时间。
function open （）{
if（！db&& window.openDatabase）{
db = window.openDatabase（shortName，version，displayName，maxSize）;
}
if（cities）{
db.transaction（populate）;
}
return db;
}
这是脚本的内容。我从 query（）调用它，在这种情况下回调是 view ，它指向贯穿结果集并在该集合中创建表的函数。
函数transact（query，callback）{
var cb = callback，
qel = document.createElement（'p'），
qid = queries.length;
if（！open（））{
console.log（'HTML5 Database not supported。'）;
返回false;
}
db.transaction（transact_cb）;
qel.innerHTML = query +'查询结果：< span id =q'+ qid +'>待定...< / span>';
qtext.appendChild（qel）;
查询[qid] =查询;
请注意最后两个参数 transact_success，transact_error 。这是你如何处理这些异步调用。
pre $函数transact_cb ]，transact_success，transact_error）;
不太清楚为什么有 eval 在那里......？
$ p $ 函数transact_success（tx，result）{
var rtext = document .getElementById（'q'+ qid）;
rtext.className ='success';
rtext.innerHTML ='成功'。
if（typeof cb ==function）{
cb（result）;
} else if（cb！= undefined）{
eval（cb +（result））;
请注意 console.log （误差）; 。
函数transact_error（tx，error）{
var rtext = document.getElementById（'q'+ qid）;
rtext.className ='error';
rtext.innerHTML ='记录到控制台的错误';
console.log（error）;
code $
这个函数创建表结果集视图。您可能会注意到我遍历每行，和每行的列。
功能视图（结果）{
var thead ='< thead> tr>'，
tbody ='< tbody>'，
行，
col; （i = 0，rows = result.rows.length; i
。
tbody + ='< tr>';
（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;
}
}）;
您可以下载并在本地运行它（由于安全错误，它不会运行在jsFiddle上）通过下载HTML文件在这里：
所以你去了。希望这将有助于更容易理解。如果您有任何问题，请告诉我。
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>
Can anyoune help me?
Simone
解决方案 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.
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>
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.
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);
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);
}
This is just meant to add data to the database. See the cities variable above.
function populate(tx) {
var city,
i = 0;
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.
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;
}
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;
}
}
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;
}
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;
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);
}
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)");
}
}
Note the 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);
}
}
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;
}
});
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中使用Sqllite访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
