How to Save sql SELECT * result so I can email it to another user so they can Import the data?

I need to save the result of an SQLITE SELECT * results so I can email it to another app user so they can import the data to their copy of the app.

I dont know the format I need to save the data as so the other user can import it.

So Far:- Step 1:

db.transacion(function(tx) {
 tx.executeSql('SELECT column FROM myTable WHERE my_id=?, [myid], success, error);


var myData=""
Function success()
 var len = results.rows.length;
 for (var i=0; i<len; i++) {
 myData += '' + results.rows.item(i).mycolum + ', '; 
 End JavaScript
End Function

I then save myData to the device which I then email using a cordova plugin.
Then i need to add the data using sql INSERT

Then with pwa I save that to the server and the other user can download and sqlImport.

Not sure on server access with a voltbuilder app sorry.

If someone could point me in the right direction I would like to look into a standalone app accessing server files.

Download and importing SQL into your database is a bad idea - it leaves you wide open hacking. You’ll want to sanitize the data fully.

From XKCD, this classic:

Screen Shot 2021-06-09 at 9.04.13 AM

I think the SQLExport / Import is only to export or import the whole database?

I just need to query the db table and send the results to some else using my app so they can import it.

Are there any examples on how to sanitize?

I wont be importing any actual SQL statements just the result of a SELECT statement. I then need to use the result string as variable such as INSERT myVARString into

DataToSend = JSON.stringify(transactionsresults)

Your second device will have this.

transactionsresults = JSON.parse(DataToSend)

Thank you for the info, I am already using JSON.stringify() and JSON.parse() but I cant get it to work.

How should I format the SQL results for (transactionresults) ?
I am currently doing this:

//Get the Data
var cerid = "1"
  tx.executeSql('SELECT * FROM my_values WHERE my_id=?', [cerid], SysResult, SysError);

Function SysResult(tx, results)
  var Sysjson=""
  var len = results.rows.length;
  for (var i=0; i<len; i++){
  Sysjson += '('+results.rows.item(i).cert_qu+',\''+results.rows.item(i).cert_ans+ '\',0)';

 exportMe = (JSON.stringify(Sysjson))
  End JavaScript
End function

I then save expotMe to the device so I can email it.

I’d just do…

Function SysResult(tx, results)
  DataToSend = JSON.stringify(results)
End Function

…then save DataToSend somewhere and send it to other device.
Then the other device would…

Function ReadInDB(emaildata)
  DataFromOtherDevice = JSON.parse(emaildata)
End function

I use JSON.stringify(results,null,vbtab) now to make the data in a text file look structured and easy on the eye.

Tip : If you’re pasting code, html or config files, surround the code with triple back ticks (```), before the first line and after the last one. It will be formatted properly.

Thank you for your help. I’m getting some strange results.
The code below works and the console shows the data as expected.

Dim sysData
Function SysResult(tx, results)
sysData = results
End function 

The above console.log = [object SQLResultSet], however if I use below:

Dim sysData
Function SysResult(tx, results)
sysData = results
sysData2 = JSON.stringify(sysData)
End function 

The above console.log = {}, the data has gone?
I need to use the JSON.stringify otherwise the sjcl.encrypt throws a slice error.

Sorry. Was working from memory. Use this…

sysData2 = JSON.stringify(sysData.rows)

Thank you. I got it working by: (however get a new error, see further down below.)

importMe2 = JSON.parse(SysData);


 for (let i = 0, len = Object.keys(importMe2).length; i < len; i++) {
    tx.executeSql('INSERT INTO my_values (my_id, my_type, my_value_qu, my_value_ans, my_value_deleted, my_value_created) VALUES ("'+newID+'", "'+importMe2[i].my_type+'", "'+importMe2[i].my_value_qu+'", "'+importMe2[i].my_value_ans+'", "0", "'+importMe2[i].my_value_created+'" ) ');

Some of the data in “my_value_ans” contains a JSON string like:

{"refid":"1", "desc":"VH", "isfinal":"", "pd_type":""}

And the SQL.transcation fails at this point with error:

could not prepare statement (1 near "refID": syntax error)

All the other data INSERTS ok, it just fails at the JSON string which is inside a single column.

not sure on the data but you code above should have hyphens around the 0 eg
...s+"', '0', '"+im...
…and all the other other hyphens need to be on the inside of the speech marks

var1 & "','" & var2 & "','" & var3 & "','" & var4 & "')"

Hi, the current INSERT method is working, but if my_cert_ans contains a JSON string rather than a single value then it fails.
example if my_cert_ans = “Test” ← works
but if my_cert_ans ={“refid”:“1”, “desc”:“VH”, “isfinal”:"", “pd_type”:""} then it fails with format error

Is the data put inside speech marks? Assuming it’s a string and not an object you may have success with triple speech marks. Eg “”“jsonstring”""

Hi, thank you so much for your help, I have been struggling with this for days.

I was using


Inside the SQL function:

tx.executeSql('INSERT INTO my_table (col1, col2) VALUES (" '+newID+' ", " '+importMe2[i].my_val.replace(/\"/g,'""')+' ") ');

But as the sql transaction statement was inside quotes ’ the .replace() would’t work properly. So instead I did:

var myVal = importMe2[i].my_val.replace(/\"/g,'""') 


tx.executeSql('INSERT INTO my_table (col1, col2) VALUES (" '+newID+' ", "'+myVal+' ")

and now all is working,

Thank you.