INSERT OR REPLACE INTO

[dmvianna]

I can't find a good way to do an INSERT OR REPLACE INTO statement in alasql. As far as I can tell, from an array of objects (JSON), I can only do db.exec("INSERT INTO " + table + " VALUES ?", [data[row]]); in a loop, row by row. And it seems to be doing it asynchronously, regardless of my supplying a callback. The promises in alasql 0.2.0 do not seem to be present.

Using this method, if the table already has the PRIMARY KEY supplied in the row, I get an exception. Is there a workaround?

Also, because everything seems to be async here, I'm not successful when trying to DELETE FROM TABLE (all rows) before looping through my data to make my insertions. By the time the insert statements start, the deletion hasn't finished. I haven't been able to solve this by wrapping alasql functions into other promises module (i.e., $q from Angular.js).

[gnachao]

// copy from objB to objA
alasql('SELECT * INTO ? FROM ?', [objA, objB]);

// export objB to excel file name data.xlsx
alasql('SELECT * INTO XLSX(?,{headers:true}) FROM ?',['data.xlsx', objB]);

// export objB to excel file name data.cvs
alasql('SELECT * INTO CVS(?,{headers:true}) FROM ?',['data.xlsx', objB]);

Info Link: https://github.com/agershun/alasql/wiki/Into

I hope it helps.

[dmvianna]

I don't think you understood my question. I have an array such as [{a:1, b:2},... {a: 10, b:2}] and I need to insert its objects ("rows") into an alasql table. This table might already have, say, {a:1, b: 3} and "a" is the primary key column. I want to loop the objects in the array and insert them in a fail-safe manner.

I do not have two arrays as your comment assumes.

Daniel Vianna

On 21 Aug 2015, at 5:22 pm, gnachao notifications@github.com wrote:

// copy from objB to objA alasql('SELECT * INTO ? FROM ?', [objA, objB]);

// export objB to excel file name data.xlsx alasql('SELECT * INTO XLSX(?,{headers:true}) FROM ?',['data.xlsx', objB]);

// export objB to excel file name data.cvs alasql('SELECT * INTO CVS(?,{headers:true}) FROM ?',['data.xlsx', objB]); Info Link: https://github.com/agershun/alasql/wiki/Into

I hope it helps.

— Reply to this email directly or view it on GitHub.

[agershun]

Sorry for delay with this issue, I will do it tonight.

Отправлено с iPhone

21 авг. 2015 г., в 10:53, Daniel Vianna notifications@github.com написал(а):

I don't think you understood my question. I have an array such as [{a:1, b:2},... {a: 10, b:2}] and I need to insert its objects ("rows") into an alasql table. This table might already have, say, {a:1, b: 3} and "a" is the primary key column. I want to loop the objects in the array and insert them in a fail-safe manner.

I do not have two arrays as your comment assumes.

Daniel Vianna

On 21 Aug 2015, at 5:22 pm, gnachao notifications@github.com wrote:

// copy from objB to objA alasql('SELECT * INTO ? FROM ?', [objA, objB]);

// export objB to excel file name data.xlsx alasql('SELECT * INTO XLSX(?,{headers:true}) FROM ?',['data.xlsx', objB]);

// export objB to excel file name data.cvs alasql('SELECT * INTO CVS(?,{headers:true}) FROM ?',['data.xlsx', objB]); Info Link: https://github.com/agershun/alasql/wiki/Into

I hope it helps.

— Reply to this email directly or view it on GitHub.

— Reply to this email directly or view it on GitHub.

[gnachao]

@dmvianna : Sorry. When I first saw your question, it was one line. It's like "I can't find a good way to do an INSERT" only.

[agershun]

Just added INSERT OR REPLACE statement into AlaSQL. May I ask you test it?

    INSERT OR REPLACE INTO one VALUE (10,"Ten"), (20,"Twenty")
    INSERT OR REPLACE INTO one SELECT * FROM two

[dmvianna]

I cloned the github repository and used dist/alasql.js after running gulp (in the master branch). It doesn't work for me. I'm not being able to run gulp on the develop branch, I get an error

Error: Cannot find module './quick-sort'
at Function.Module._resolveFilename (module.js:336:15)

this even after I installed quick-sort running npm install quick-sort in the alasql directory (the root) of my clone.

[mathiasrw]

Have you npm install after cloning?

[dmvianna]

Yes I did. I tried to install it explicitly after it because running npm install wasn't enough. The weird thing is, node_modules is there (you didn't include it in your .gitignore file), so there is no reason for it to fail.

[mathiasrw]

I got confused now...

Are you sure its the right repo you are installing?

I am sorry for this silly question, but `node_modules/' is located in both https://github.com/agershun/alasql/blob/master/.gitignore and https://github.com/agershun/alasql/blob/develop/.gitignore so cant make it fit with your last comment

node_modules is there (you didn't include it in your .gitignore file),

[dmvianna]

So could you please provide instructions as to how can I test this? Is this supposed to work on the browser? I don't use npm, I use bower.

[mathiasrw]

Yes - should work for the browser.

Ill make an example tonight

[mathiasrw]

@dmvianna I read the text again and got an idea: are you trying to clone this github repo to compile a bower component?

I am actually not sure about how to compile the bower package, but have a feeling that what you need to do is to isntall the library with bower by bower install alasql

After this you include the library in your html like in this example:

<script src="bower_components/alasql/dist/alasql.js"></script>
<script>
  var r = alasql('SELECT 123 AS abc');
  console.log(JSON.stringify(r));
  // outputs: [{"abc":123}]
</script>

It could also be that you are running the alasql('SELECT * INTO XLSX(?,{headers:true}) FROM ?',['data.xlsx', objB]); part in a local .html without serving from a server. getting access to a file from a local .html file is not allowed in most browseres.

Please let me know how you are using the library

[dmvianna]

@mathiasrw I am using alasql successfully in production. The problem is not including alasql.js in a <script> tag, but making any recent feature work. It feels like only basic functionality is available in the browser.

Is INSERT OR REPLACE INTO available in the 0.2.0 version you offer through bower in the browser? I can't seem to make it work there. But I can definitely use select, insert into, count, etc.

[mathiasrw]

insert or replaceIt is not supported. Sorry for late answer.

Closed per request from #628