sql-js/sql.js

A javascript library to run SQLite on the web.

sql.js's Issues

Persistent database and max DB size

Hi,
I'm adding this DB on my Windows Phone 8.1 app.
May I ask you if DB will be persistent and the max DB size?
Many Thanks.

Riccardo.

support for HTML5 storage

Support Indexed db/ localstorage for data storage.

SQL.js won't run on an iPad or Android tablet

When I try to run the demo of SQL.js at http://syntensity.com/static/sql.html on the latest version of Safari on an iPad it does not work. It throws and error of

JavaScript Error on Line 1
http://syntensity.com/static/sql.js
ReferenceError: Can't find variable: Float64Array

JavaScript Error on Line 15
http://syntensity.com/static/sql.html?
ReferenceError: Can't find variable: SQL

Also, the demo does not run on a Android tablet running Ice Cream Sandwich with Webkit or Firefox browser. If needed, I can send the exact error message after figuring out how to do it.

Thanks.
Me

INSERT OR REPLACE

I found this to be really cool replacing WebSQL for Windows 8 development but I'm not sure on all its limitations and such so I was wondering if this library supports INSERT OR REPLACE into tables.

I want to use :

INSERT OR REPLACE INTO TABLE_NAME VALUES("1", "TEST");

But its getting thrown exception 1 - no such table exists.

I created the table using the following statement:

CREATE TABLE IF NOT EXISTS TABLE_NAME (id VARCHAR(255) PRIMARY KEY, data VARCHAR(255))

Am I using something that isn't supported?

problem with chinese

my html and js files are encoded by utf-8, and my test code is just like this:
var db = new SQL.Database();
db.run("create table test(a Integer, b varchar2);insert into test values (1, '语文');");

var stmt1 = db.prepare("select * from test;");
var stmt2 = db.prepare("select * from test where b = $keyword");
stmt.bind({$keyword: '语文'});

stmt1 can search it, but stmt2 can not, how it could be?

Without Typed Arrays

Hello,
Can you please provide a complied version that does not use typed arrays?

Dennis

Encapsulate js

Encapsulate library (like jquery minified do) to avoid variable clash names with other libraries

generate db file

[question]
can this be used to generate a db file that can be opened by sqlite(c/c++, php, etc.)?

Bug with datetime function

Try running this statement in sql.js:

SELECT datetime('2012-04-16 12:35:57', '+1 days');

You'll see that it returns 2012-04-15 12:35:57 instead of 2012-04-17 12:35:57. I have verified that vanilla SQLite does return 2012-04-17 12:35:57.

Emscripten problem? "make" never works

Hello,

I try to recompile sql.js by make, compiled sql.js throw error when it is executed.
Like,

$ node ../js/sql.js 
Assertion failed: undefined:
Error
    at abort (/dev/sql.js/js/sql.js:399:32)
    at assert (/dev/sql.js/js/sql.js:405:5)
    at Object.FS.init (/dev/sql.js/js/sql.js:2768:9)
    at __ATINIT__.unshift.func (/dev/sql.js/js/sql.js:4108:93)
    at callRuntimeCallbacks (/dev/sql.js/js/sql.js:694:7)
    at initRuntime (/dev/sql.js/js/sql.js:702:3)
    at Object.<anonymous> (/dev/sql.js/js/sql.js:5732:1)
    at Module._compile (module.js:449:26)
    at Object.Module._extensions..js (module.js:467:10)
    at Module.load (module.js:356:32)

/dev/sql.js/js/sql.js:401
  throw "Assertion: " + text;
                      ^
Assertion: Assertion failed: undefined

Condition is written in below:

• OS, Mac OS X 10.7.5

• clang and llvm are 3.2 from macports

• emscripten is latest master, pull from github at 2013-03-19 21:00:00 at UTC

• compile option is same with written in Makefile,

  ~/Dev/emscripten/emcc -O2 -DSQLITE_DISABLE_LFS -DLONGDOUBLE_TYPE=double -DSQLITE_INT64_TYPE="long long int" -DSQLITE_THREADSAFE=0 sqlite3.c main.c --pre-js ../js/pre.js --post-js ../js/post.js -o ../js/sql.js -s EXPORTED_FUNCTIONS="['_sqlite3_open', '_sqlite3_close', '_sqlite3_exec']"

I also encountered with same error, at
https://github.com/gurjeet/sql.js
which is forked long time before, so there are capability of emscripten's issue...

I like both sql.js and emscripten's potential, It is glad to if any helps are given.

Regards,

Chineese character

Since W3C has deprecated WebSQL databases in HTML 5, the only solution to use SQL to manipulate client-side data is this project ! Hate NOSQL movement ... sql.js is a GREAT idea !

But I found an issue while filling some chineese text into a table :

create table MY_TABLE (MY_ID integer, MY_TEXT text)

insert into MY_TABLE (MY_ID, MY_TEXT) values (3300, '郑安琳')
-> Worked like a charm

insert into MY_TABLE (MY_ID, MY_TEXT) values (23200, '包言明')
-> Got a failure (SQLite exception: 1, unrecognized token: "'�")

Tested on Chrome 24 with :

• Default params
• Use of pragma encoding = "UTF-16" just after database open (same issue)

BUG - typecasting

In the demo (http://kripken.github.io/sql.js/test/demo.html), the values from the two "INTEGER" columns are being returned as strings when they are looked up.

The expected behavior would be for INTEGER and other numeric columns to return values that are of the javascript "number" type.

[QUESTION] - storage limits?

Hi there.

Sorry if it's not the place for a question, i just didn't found any other place for it.

I'm working in a web app that manages lots of documents. Those documents can be text files, pdf files, LibreOffice texts, mp3 files, images, and so on. I do stuff to the documents, and then store the results. And i want to do all that client-side, using only javascript.

JS seems to be mature enough for an app like that, as lots of libraries already let me do every job and everything works pretty good actually. However, there's a critical point on the current state of JS development regarding storage. My app will most likely use a lot of storage: lots in size, and lots in time. And after testing the current APIs for local storage, none of them was ok for my project.

So... i found sql.js, and it seems great. I'll just open and save sqlite dbs using file and blob urls, and that would give me any storage i could need. However, i wonder how does this sqlite port works with the js db. Is it an in-memory blob? It's some file object on the local storage? Something else?
I ask this because i'm concerned about the possible limits for a DB using sql.js. Did anyone tested it with some >500MB database?

Thanks.
And thanks for this great project too, btw :)

Broken SQL.open( data ) / SQL.exportData()

The mechanism through which it is possible to save and load a SQL instance seems to be broken.

The problem happens when opening a SQL instance passing the data returned by exportData() to the open(data) method.
To me it seems that although when created empty the database makes his buffer grow without problem, when the database is opened with a given uint8array, as the database grows the buffer is not reallocated and an overflow is generated, leading then to a corruption in the database disk image (SQLite exception: 11, database disk image is malformed)

Here is the test case proving the bug. The principle is simple, i first create an empty database, fill it with some records, then export its data, close it, reopen it with the exported data, and then going again with insertions.

Here is the Javascript code for this bug test-case, it is inpired by your demo page :

<script type="text/javascript" language="javascript">

// This variable will receive instance of SQLite
var db = null;

// method taken from your demo
function print(text) {
  var element = document.getElementById('output');
  element.innerHTML += "<br/>" + text;//.replace(/\n/g, '<br>');
}

// method taken from your demo
// only one modification : returns true when everything ok, and false when an exception has been triggered
function execute(commands) {
  try {
    print( "<b>" + commands + "</b>" );
    var data = db.exec(commands.replace(/\n/g, '; '));
    print(JSON.stringify(data, null, '  '));
    return true;
  } catch(e) {
    print(e);
    return false;
  }
}

var res = true;
for( i=0; i<10; i++ )
{
    print( "Pass " + i );

    var data = null;

    // if a SQLite instance has already been opened, we just export its data, 
    // close it and reopen a new instance with the exported data
    if( db != null )
    {
        data = db.exportData();
        db.close();
        db = null;

        print( "Exporting data and closing sqlite, bytes=" + data.length );
    }

    if( data != null )
    {
        db = SQL.open( data );
    }
    else
    {
        db = SQL.open();
        execute( "CREATE TABLE Persons( id INTEGER PRIMARY KEY AUTOINCREMENT, LastName varchar(255) NOT NULL );" );
    }

    execute( "SELECT count(*) as 'CountOfPersons' from Persons;" );

    for( j=0; j<100; j++ )
    {
        res = execute( "insert into Persons (LastName) values ('Toto-"+i+"-"+j+"');" );
        if( ! res )
            break;
    }

    if( ! res )
        break;
}

print( "Finished" );

</script>

benchmark.js file missing

seems this file is missing in the test directory

error in javascript using current master of emscripten

Using the current master, the compile will finish but generate error when loading in browser:

Uncaught TypeError: Cannot call method 'set' of undefined

How to open a sqlite db file

In javascript, may I use SQL.open("test.db");?

It use SQL.open() in demo now. Thanks

unknown vector type <4 x i8>

I am trying to compile the latest version of SQLite. It works with -O1, but with -O2, it fails with:

    aborting from js compiler due to exception: unknown vector type <4 x i8> | undefined
    aborting from js compiler due to exception: unknown vector type <4 x i8> | undefined

I use emscripten 1.10.0 (the on in Ubuntu 14.04's repos).

Could not insert cyrillic words in demo table

Hello,
I tried using demo of sql.js, when inserting Russian words, sql.js returns an error. Like below

create table s1 (word text);
insert into s1 values("привет");

Returns
SQLite exception: 1, unrecognized token: ""привеѼ

Wiki

Please make the wiki available so that we can contribute documentation.

Compiled version of sql.js throws error

sql.js supplied in the repo works fine. But when compiled from the master branch, sql.js causes SQLite exception on INSERT statement in the demo (CREATE TABLE works fine).

"SQLite exception: 10, disk I/O error"

I have tried with and without emcc options -O0 --closure 0, and I get the same result.

sql.js branch: master (at commit dff88cd)

OS: Ubuntu 12.04 64 bit
Emscripten: master 3f855a246e873ca2c862e3747f530cd0544f2f25

Makefile diff:
-EMSCRIPTEN=/Dev/emscripten
-EMCC=$(EMSCRIPTEN)/emcc -O2
+EMSCRIPTEN=/dev/EMSCRIPTEN
+EMCC=$(EMSCRIPTEN)/emcc -O0 --closure 0

LLVM downloaded from:
http://llvm.org/releases/download.html#3.1
"Clang Binaries for Ubuntu-12.04/x86_64"
http://llvm.org/releases/3.1/clang+llvm-3.1-x86_64-linux-ubuntu_12.04.tar.gz

LLVM binaries extracted to:
/home/gurjeet/Downloads/clang+llvm-3.1/

~/.emscripten
LLVM_ROOT = os.path.expanduser(os.getenv('LLVM') or '/home/gurjeet/Downloads/clang+llvm-3.1/bin')

NodeJS version: 0.8.11

The only visible sin of some trouble is that I get a warning on 'make'

Warning: Casting a function pointer type to another with a different number of arguments. See more info in the compiler source

When I added '-s VERBOSE=1' option to the sql.js build rule, I get this additional detail:

Warning: Casting a function pointer type to another with a different number of arguments: void (%struct.sqlite3_, i8_)* vs. void (i8_)_, on _sqlite3DbFree

I don't think this function has anything to do with INSERT statement failing, but I don't know anything about EMCC/SQLite internals so I can be completely wrong.

I am trying to learn how to compile C programs using Emscripten, hoping to put Postgres in JS some day.

requireJS support

so I have this requirejs bootstrapper:

function init() {
    requirejs.config({
        baseUrl: "scripts",
        paths: {
            "jquery": "jquery-2.1.1.min",
            "sql": "sql.min"
        }
    });

    require(["main"], function (main) {
        main.OnInit();
    });
}

init();

And this is the main code

define(["require", "exports", "sql"], function(require, exports, sql) {
    function OnInit() {
        console.log(sql);
    }
    exports.OnInit = OnInit;
});

But it prints undefined, what's wrong with my config?

Have a button or panel to see the database schema in a "user friendly" way

for example something like a dropdown menu when you click on "schema" it expands and display each tables and their structure (maybe in a first time no need to make fancy stuff, one <table> by table with one column with its name should be already quite useful)

Missing binding to get the last inserted record id

There is apparently no way to get the id of the last inserted item, maybe just an emscripten binding is missing ?
Thanks

SQLite exception: file is encrypted or is not a database

i can use both sqlite manager and php to access the sqlite database, cannot with sql.js

it shows:
SQLite exception: file is encrypted or is not a database

my code is:

try {
var db = SQL.open(dbUri);
var data = db.exec('SELECT * FROM CONTACT');
} catch(e) {
$('#div_err').html(e);
}

Request for spatial support

it would be great and very usefull the porting of sqllite spatial into sql.js
Do You think is it possible?

sqlite command not working

it seems it's not possible to type .schema for example to see which tables are available

Port mongo db to JavaScript?

Do you think you can the same with mongo db?

Readable version code of SQL.js (question)

Is there a version sql.js with more readable code
Without obfuscation and optimization?

Or Emstsripten makes the code directly in this form?

please insert support for prepared statements

this would be helpful to speed up the querries and prevent the need to escape the database inputs

Trouble reading blob type fields from the database

The issue is that i have a database that has fields of type blob, and when i read them the column has no content in it i.e. the value field is empty.

I read the Sqlite database using the FileReader and do the following
reader.readAsBinaryString(file);

Then i pass reader.result to this function, before opening the database.

function bin2Array(bin) {
'use strict';
var i, size = bin.length, ary = [];
for (i = 0; i < size; i++) {
ary.push(bin.charCodeAt(i) & 0xFF);
}
return ary;
}

I have posted my question on Stackoverflow as well.

http://stackoverflow.com/questions/23754590/trouble-reading-sqlite3-database-columns-of-type-blob-with-sql-js

Broken in IE9

When I visit your demo page in IE9 (9.0.8112.16421) on Win7: http://syntensity.com/static/sql.html

I get this javascript runtime error:

SCRIPT5009: 'Int32Array' is undefined
sql.js, line 1 character 5008

there is no close method

dlmalloc linking issue

Not sure if it is a config lack on my side, an emscripten issue, or a sqlite issue.
Tried to compile sqlite with default makefile and got this :

Intrinsic has incorrect return type!
i32 (i32)* @llvm.exp2.i32
Intrinsic has incorrect return type!
i32 (i32)* @llvm.exp2.i32
Intrinsic has incorrect return type!
i32 (i32)* @llvm.exp2.i32
Broken module found, compilation aborted!
0 libLLVM-3.2.so.1 0x40db3e28
1 libLLVM-3.2.so.1 0x40db4364
2 0x40022400 __kernel_sigreturn + 0
3 0x40022424 _kernel_vsyscall + 16
4 libc.so.6 0x416d41df gsignal + 79
5 libc.so.6 0x416d7825 abort + 373
6 libLLVM-3.2.so.1 0x407aebfa
7 libLLVM-3.2.so.1 0x407b5738
8 libLLVM-3.2.so.1 0x407926cb llvm::FPPassManager::runOnFunction(llvm::Function&) + 619
9 libLLVM-3.2.so.1 0x4079272c llvm::FPPassManager::runOnModule(llvm::Module&) + 76
10 libLLVM-3.2.so.1 0x407923f4 llvm::MPPassManager::runOnModule(llvm::Module&) + 500
11 libLLVM-3.2.so.1 0x40794c49 llvm::PassManagerImpl::run(llvm::Module&) + 121
12 libLLVM-3.2.so.1 0x40794c96 llvm::PassManager::run(llvm::Module&) + 38
13 libLLVM-3.2.so.1 0x407b1918 llvm::verifyModule(llvm::Module const&, llvm::VerifierFailureAction, std::string) + 488
14 llvm-link 0x08049a5e
15 libc.so.6 0x416bf4d3 __libc_start_main + 243
16 llvm-link 0x0804ad69
Stack dump:
0. Program arguments: /usr/bin/llvm-link /tmp/tmpc7t9KA/dlmalloc.c.o /tmp/tmpc7t9KA/new.cpp.o -o /tmp/tmpc7t9KA/libc.bc

1. Running pass 'Function Pass Manager' on module '/tmp/tmpc7t9KA/dlmalloc.c.o'.
2. Running pass 'Module Verifier' on function '@malloc'
   Traceback (most recent call last):
   File "/home/me/Desktop/emscripten/emcc", line 1153, in
   libfile = shared.Cache.get(name, create)
   File "/home/me/Desktop/emscripten/tools/cache.py", line 37, in get
   shutil.copyfile(creator(), cachename)
   File "/home/me/Desktop/emscripten/emcc", line 1075, in create_libc
   shared.Building.link(o_s, in_temp('libc.bc'))
   File "/home/me/Desktop/emscripten/tools/shared.py", line 830, in link
   assert os.path.exists(target) and (output is None or 'Could not open input file' not in output), 'Linking error: ' + output
   AssertionError: Linking error:
   make: *_* [sqlite.js] Error 1

SEVERE issue in SQLite versions < 3.7.17 affecting a great percentage of users

There's a SEVERE error in SQLite. It's actually returning incorrect queries. It happens any time an OR is used in a LEFT JOIN statement. This is a very serious and critical error. If you like you can see my fiddle which demonstrates this issue.
Here's my original issue from the SQLite mailing list:
I'm getting unexpected and differing results between WebSQL(SQLite) and SQLite. The query produces the correct results in WebSQL.

This is the script that I'm running. It's designed to allow us to filter out, over several tables, data that we decide is not interesting and therefore whitelisting it.

First here's the link to the fiddles I've created demonstrating this issue. The correct result is one row; SQLite returns all rows.
http://sqlfiddle.com/#!7/74e01/1 WebSQL(SQLite)
http://sqlfiddle.com/#!5/74e01/1 SQLite(SQL.js) (Click "Cancel" if prompted.)

CREATE TABLE scans (
scan_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL);

CREATE TABLE hosts(
host_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
scan_id INTEGER NULL);

CREATE TABLE programs(
program_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
host_id INTEGER NULL,
name varchar NULL,
publisher varchar NULL);

CREATE TABLE whitelist(
whitelist_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
scan_id INTEGER NULL,
value1 varchar NULL,
value2 varchar NULL
);

insert into hosts(host_id) VALUES (1);

insert into programs(program_id,host_id,name,publisher) VALUES (1,1,"foo", "foo");
insert into programs(program_id,host_id,name,publisher) VALUES (2,1,"bar", "bar");
insert into programs(program_id,host_id,name,publisher) VALUES (3,1,"abc", "def");
insert into programs(program_id,host_id,name,publisher) VALUES (4,1,"ghi", "jkl");
insert into programs(program_id,host_id,name,publisher) VALUES (5,1,"mno", "jkl");
insert into programs(program_id,host_id,name,publisher) VALUES (6,1,"pqr", "stu");

insert into whitelist(whitelist_id,value1,value2) VALUES (1,"foo","foo");
insert into whitelist(whitelist_id,value1,value2) VALUES (2,"bar","bar");
insert into whitelist(whitelist_id,value1,value2) VALUES (3,"abc","");

---The Query---

SELECT *
FROM programs p
LEFT JOIN hosts h
ON p.host_id = h.host_id
LEFT JOIN whitelist w
ON (w.value1 = p.name
AND p.publisher = w.value2)
OR ( w.value1 = ''
AND p.publisher = w.value2 )
OR ( w.value2 = ''
AND p.name = w.value1 )
WHERE w.whitelist_id IS NULL

And here's the correct answer:

The bug was fixed for 3.7.17. It's this one:

http://www.sqlite.org/src/info/f2369304e47167e3e

Please fix!!!

Cannot load database from in memory array

Hi,

As outlined here https://github.com/kripken/sql.js/wiki/Database-Format-for-the-open()-Method I am trying to load data from a simpleStorage.js KVP and I am getting the following error: Error: no such table: test

It appears that the data I am providing is valid, however the contents of the db is not being persisted

I have the same result with and without using bin2Array

        function bin2Array(bin) {
            'use strict';
            var i, size = bin.length, ary = [];
            for (i = 0; i < size; i++) {
                ary.push(bin.charCodeAt(i) & 0xFF);
            }
            return ary;
        }

        $(document).ready(function(){

            console.log(simpleStorage.canUse());

            var list = simpleStorage.index();

            console.log(list);

            var db = null;

            if($.inArray('mydb', list) > -1){
                console.log('loading db');

                var data = simpleStorage.get('mydb');

                console.log(data);

                db = new SQL.Database(bin2Array(data));                 
            }
            else{
                console.log('creating db');

                //Create the database
                db = new SQL.Database();

                // Run a query without reading the results
                db.run("CREATE TABLE test (col1, col2);");
                // Insert two rows: (1,111) and (2,222)
                db.run("INSERT INTO test VALUES (?,?), (?,?)", [1,111,2,222]);
            }       

            // Prepare a statement
            var stmt = db.prepare("SELECT * FROM test WHERE col1 BETWEEN $start AND $end");
            stmt.getAsObject({$start:1, $end:1}); // {col1:1, col2:111}

            // Bind new values
            stmt.bind({$start:1, $end:2});
            while(stmt.step()) { //
                var row = stmt.getAsObject();
                // [...] do something with the row of result
                console.log(row.col1);
            }

            var data = db.export();

            simpleStorage.set('mydb', data)
        });

Queries for blob fields return truncated results

When executing queries on tables with blob fields, the result set only displays the first 4 characters of the blob data, instead of the entire blob.

For instance, when saving JPG's to a blob field, I will only get back the following result for each and every JPG stored in the DB: "ÿØÿà"

Add Travis CI hook

Hey @kripken,
Could you please add a travis hook to this project? I can't do that myself, because I'm not the owner of the repo.

Thanks,
Ophir

MakeFile build a new sql.js fail work

I used makefile build a new sql.js ,but the sql.js is different from "https://github.com/kripken/sql.js/blob/master/js/sql.js" ,and it can't work .

run test page get errors: Uncaught ReferenceError:FUNCTION_TABLE is not defined

my os is Mac OS , Emscripten version 3.2 ,python 2.7 .

Can't SELECT on more than ~50k rows

When running db.exec(SELECT * FROM tbl_data), where tbl_data contains around 200k~ rows it passes an error to the callback, looking like this:

TypeError: Property '0' of object 0,0,function () {},0,function vf(b,c){var d;
..............
value:pd(yd(d+f*wc,"i32"))})}h2.push(b)},0,0 is not a function
    at Lp (/Users/karlpetersson/voxLogConnect/node_modules/node-sqlite-purejs/js/sql.js:3117:75)
    at kj (/Users/karlpetersson/voxLogConnect/node_modules/node-sqlite-purejs/js/sql.js:4123:80)
    at vi (/Users/karlpetersson/voxLogConnect/node_modules/node-sqlite-purejs/js/sql.js:1224:1)
    at Yj (/Users/karlpetersson/voxLogConnect/node_modules/node-sqlite-purejs/js/sql.js:5458:38)
    at OF (/Users/karlpetersson/voxLogConnect/node_modules/node-sqlite-purejs/js/sql.js:14972:1)
    at Array.LF (/Users/karlpetersson/voxLogConnect/node_modules/node-sqlite-purejs/js/sql.js:14908:9)
    at Al (/Users/karlpetersson/voxLogConnect/node_modules/node-sqlite-purejs/js/sql.js:19495:26)
    at vl (/Users/karlpetersson/voxLogConnect/node_modules/node-sqlite-purejs/js/sql.js:1593:64)
    at tl (/Users/karlpetersson/voxLogConnect/node_modules/node-sqlite-purejs/js/sql.js:1530:9)
    at Ym (/Users/karlpetersson/voxLogConnect/node_modules/node-sqlite-purejs/js/sql.js:2055:5)
    at Object.gd [as ccall] (/Users/karlpetersson/voxLogConnect/node_modules/node-sqlite-purejs/js/sql.js:125:14)
    at Object.Module.open.exec (/Users/karlpetersson/voxLogConnect/node_modules/node-sqlite-purejs/js/sql.js:27306:28)
    at Sql.module.exports.Sql.exec (/Users/karlpetersson/voxLogConnect/node_modules/node-sqlite-purejs/js/node-sqlite-purejs.js:91:33)

if i put in LIMIT 50000 into the query, it works. Anyone have any clue on what's going on here?

Add support for opening a database from a data uri or base64 blob

I want to embed a small SQLite database as a base64 encoded data uri in a web page. I'd like to be able to open the database via passing in the data uri, or the actual decoded blob.

See also: dataURItoBlob

Encoding Problem Needs UTF-8

exec function parameter needs encode UTF-8

output column value also needs decode UTF-8

export problem

I appear to have the same problem as the one posted a couple of years ago
https://github.com/kripken/sql.js/issues/9

My code goes as follows.

// db is initialized using pre-existing .db file, which had 3 rows in 'users' table

// insert a new row in 'users' table

var stmt = db.prepare("SELECT * FROM users;");
while (stmt.step()) { // prints out 4 users in console
    var row = stmt.getAsObject();
    console.log(row);
}

var data = db.export();
db = new SQL.Database(data);

stmt = db.prepare("SELECT * FROM users;");
while (stmt.step()) { // prints out 3 users in console
    var row = stmt.getAsObject();
    console.log(row);
}

So, the problem is:

• I export the database
• I create a new database using the data that I got by exporting the original db
• the new database does not have all the data inside that the original database had.

Why does this happen?
I would like to have an 'export()' method that behaves like:
db <is_equal_to> new SQL.Database(db.export())

The problem is most certainly in db.export()... The modifications I made on the original db object are not taken into consideration when calling db.export()

how to change output format?

Hi, this is not necessarily an issue, but I didn't know where else to post this. I was wondering how to change the output format from...

[
{
column: 'columnName1',
value: 'value1'
},
{
column: 'columnName2',
value: 'value2'
},
...
]

to the more idiomatic:

{
columnName1: 'value1',
columnName2: 'value2'
}

I tried editing the format in post.js, in the method "callbackTemp," but it broke other parts of the code.

REFRENCEERROR: SQL IS NOT DEFINED

Hi, I am using sql.js on my firefox browser and its working perfectly. But when the same code i am using on my android app then it showing this error "REFRENCEERROR: SQL IS NOT DEFINED". I have also included sql.js .

Nice! [NOT AN ISSUE]

Sorry, I don't see another place to post.

Thanks! This really takes the work out constructing SQL injection attacks! Oh wait... I just rooted myself.... ;)

supporting some kind of encryption ? (perhaps sqlcipher?)

hi,
i would like to be able to open encrypted sqlite db's with sql.js , how should i do it? do i have to decode integer array before passing it to sql.js or can you implant some kind of decryption methods internally ?

How to supply SQL.open method with an array of integers?

Sorry for being such a noob. I am a student who is making use of SQL. is for my school project.

If I not wrong, I am able to solve the error below by supplying SQL.open method with an array of integers.
"SQLite exception: file is encrypted or is not a database"

But can anyone explain how can i supply the sql.open method?

Thanks!

BLOB fields are returned as strings, not as raw data

Steps to reproduce:

1. Convert an image to a data URI
2. Strip the encoding information from the data URI until only the binary string is left
3. Convert the URI string to raw binary using atob()
4. Insert the binary data into a database field
5. Close the database
6. Reopen the database and query the field you saved the binary data to
7. The binary data is truncated to 4 characters of text

Link to demo not working anymore

http://syntensity.com/static/sql.html
propably some server issues?