Using sql.js to display a SQLite .db in HTML page

Question

I am trying to take a sqlite database (ex. data.db) and display in a HTML page. If I can display in table that would be great. But right now, I just want to see the data. I have tried everything I could find thus far.

I am using sql.js as the framework and below is my code.

<html>
<head>
<title>Title</title>

<script type="text/javascript" src="https://raw.githubusercontent.com/kripken/sql.js/master/js/sql.js"></script>
<script type="text/javascript" src="https://raw.githubusercontent.com/kripken/sql.js/master/js/worker.sql.js"></script>
<script src='sql.js'></script>

<script type="text/javascript">
var xhr = new XMLHttpRequest();
xhr.open('GET', 'data.db', true);
xhr.responseType = 'arraybuffer';

xhr.onload = function(e) {
  var uInt8Array = new Uint8Array(this.response);
  var db = new SQL.Database(uInt8Array);
  var contents = db.exec("SELECT * FROM my_table");
};
xhr.send();
</script>
</head>
<body>

<h1>Database Data</h1>

...data to display here...

Sample_d · Accepted Answer · 2017-08-20 11:04:55Z

I hope you already found the answer, so my answer is just for posterity. It took me also awhile to find a solution, but the code should direct you to a solution.

<html>
<head>
<title>Title</title>
<script type="text/javascript" src="https://raw.githubusercontent.com/kripken/sql.js/master/js/sql.js"></script>
<script type="text/javascript" src="https://raw.githubusercontent.com/kripken/sql.js/master/js/worker.sql.js"></script>
<!--
<script type="text/javascript" src="sql.js"></script>
<script type="text/javascript" src="worker.sql.js"></script>
-->

<script type="text/javascript">
var path = "http://alasql.org/demo/016sqlite/Chinook_Sqlite.sqlite";
//var path = "016sqlite_files/Chinook_Sqlite.sqlite"
var xhr = new XMLHttpRequest();
xhr.open('GET', path, true);
xhr.responseType = 'arraybuffer';

xhr.onload = function(e) {
  var uInt8Array = new Uint8Array(this.response);
  var db = new SQL.Database(uInt8Array);
  //var contents = db.exec("SELECT name FROM sqlite_master where type='table'");
  var contents = db.exec("SELECT * FROM Playlist");
  //alert(JSON.stringify(contents));

  var table_string = '<table>';
  if (contents) {
    table_string += '<tr>';
    for (var index in contents[0].columns) {
      table_string += '<th>' + contents[0].columns[index] + '</th>';
    } 
    table_string += '</tr>';
    for (var row_index in contents[0].values) {
      table_string += '<tr>';
      for (var col_index in contents[0].values[row_index]) {
        table_string += '<td>' + contents[0].values[row_index][col_index] + '</td>';
      }
      table_string += '</tr>';
    }

    }

table_string += '</table>';
    //alert(table_string);
    document.getElementById("res").innerHTML = table_string;    
}
xhr.send();

</script>
</head>
<body>

<h1>Database Data</h1>
<p id="res"></p>
</body>
</html>

For some fast errorchecking i used local files, but the code should work as it is. These links may help you further:

https://github.com/kripken/sql.js/issues/207 https://github.com/kripken/sql.js/blob/master/README.md

Collectives™ on Stack Overflow

Using sql.js to display a SQLite .db in HTML page

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related