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

[Pro_Certs]

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);

Then:

var myData=""
Function success()
 var len = results.rows.length;
 JavaScript 
 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

[lotsofcows]

https://wiki.appstudio.dev/SqlExport

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.

[ghenne]

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:

https://bobby-tables.com/

[Pro_Certs]

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.

[Pro_Certs]

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

[lotsofcows]

DataToSend = JSON.stringify(transactionsresults)

Your second device will have this.

transactionsresults = JSON.parse(DataToSend)

[Pro_Certs]

@lotsofcows

transactionsresults

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)
 JavaScript
  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.

[lotsofcows]

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.

[ghenne]

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.

[Pro_Certs]

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
console.log(sysData)
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)
console.log(sysData2)
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.

[lotsofcows]

Sorry. Was working from memory. Use this…

sysData2 = JSON.stringify(sysData.rows)

[Pro_Certs]

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

importMe2 = JSON.parse(SysData);

Then

 for (let i = 0, len = Object.keys(importMe2).length; i < len; i++) {
    db.transaction(function(tx){
    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.

[lotsofcows]

@Pro_Certs

s+'", "0", "'+im

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 & "')"

[Pro_Certs]

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

[lotsofcows]

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”""

[Pro_Certs]

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

I was using

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

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,'""') 

then:

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

and now all is working,

Thank you.