Use Database SQLite in service

[Stefano Tagnochetti]

It's possibile use db SQLite into the service.js?

Thanks

[Chris]

Easy to find out, try it :-)

But if I renember, yes.

[Stefano Tagnochetti]

I did a test, the database file is created, but the openSuccess function returns me undefined.

In my file Service.js
db = app.OpenDatabase( appPath + "/db/DBTest" ); // file created
app.ShowPopup( db.openSuccess() ); // undefined

if necessary, I can post the code.

I need help
Thanks

[Steve Garman]

That's nothing to do with being in a service.

Your cide would not work in a foreground app either.

If you want to check whether CreateDatabase() was a success, try
app.ShowPopup( db !== null )

If you have not used sqlite in DroidScript before, I recommend testing your code in a foreground app first, as any problems are likely to be confusing to you in a service.

I also recommend looking at the "Database" sample that comes with DroidScript to be sure you are familiar with the asynchronous code DS uses from
db.ExecuteSql()

[Stefano Tagnochetti]

Thanks for the advice and support.

I did some testing, the sample code of DS adapted in my test app works well for me (create the db, create the table, write the data in the table)

The same code shown in the Service.js part of the same App does not work for me, (create the db, it does not create the table).

db.ExecuteSql ("CREATE TABLE IF NOT EXISTS tblTest ('Id' Number, 'textSample' String)"); it is as if it were not executed, without detecting errors, even run in debugging, while in the App part it does not give me problems.

I noticed that subsequent instructions that do not have to do with the db are done correctly. (Eg Deletefile ())

What can I do?
Where am I wrong?

I report the function

//import data into db
function addDataDB( jsonData, appPath)
{
//Parse JSON
var obj =JSON.parse(jsonData);

//app.MakeFolder ( appPath + "/db");
db = app.OpenDatabase( appPath + "/db/DBTest" ); //ok create db file

app.ShowPopup( "db is " + (db !== null) ); // db is true
if (db !== null )
{
//Create a table (if it does not exist already).
db.ExecuteSql( "CREATE TABLE IF NOT EXISTS tblTest ('Id' Number, 'textSample' String)" ); // it does not create the table

// Code to write data in the table
....

// Delete the file
app.DeleteFile( appPath + "/dwn/import.json" ); ok
}

}

Thanks

[Steve Garman]

What is appOath set to.and re you testing this in an apk?

If appPath is app.GetAppPath() and you are running an apk

this line will not work

db = app.OpenDatabase( appPath + "/db/DBTest" );

because you do not have write-access to
appPath

[Stefano Tagnochetti]

Yes appPath is app.GetAppPath ().

Which path should I use to open the database so I can write?

[Steve Garman]

If you don't need access to the database outside your app, I suggest using
appPath=app.GetPrivateFolder("myData")

If you do need it to be public, using something below
"/sdcard"

[Stefano Tagnochetti]

Thanks for your suggestions and help.

I replaced AppPath with app.GetPrivateFolder ("myData") but the problem remains the same, the db file is created but then the table is not created inside.

I can not understand why?!?

I am attaching you spk.
Everything works for me except db.ExecuteSql () in the Service.js file

Thank you

[Steve Garman]

That is not an spk

The Service.js is 1 level too deep in the archive and there is no foreground app at all.

If you are not using DroidScript tools (Device IDE or WiFi IDE) to build and maintain your app, please explain what you are using.

If you want to post an spk, please use DroidScript to create it.

[Steve Garman]

I appear to be wrong about the format of the spk.

It does not have a foreground app, which gives it no chance of working

Also because it has no foreground app, it does not appear in the Device IDE.

For that reason I had to examine the spk in a zip utility, that caused sufficient confusion for me to misunderstand the format.

This thread seems to be full of red herrings.

If your service is running at all, there must be a servieDbTest.js or serviceDbTest.html that is starting it.

I find it hard to reconcile anything you have posted with what is happening.

If you want to post an spk that is actually working and creating a db file as you described, I'll take a look at it but I've made enough guesses already and my time is not inexhaustible.

[Stefano Tagnochetti]

It is not my intention to make you waste time making you angry.

To solve this problem, you place the code of the two .js files.

ServiceDbTest.js
function OnStart()
{
//Create a layout.
lay = app.CreateLayout( "linear", "VCenter,FillXY" );

//Create a 'Manual import data into dbApp' button.
btn = app.CreateButton( "Import data", 0.6, 0.1 );
lay.AddChild( btn );
btn.SetOnTouch( function(){manualImport()} );

//Create text control to display data from the service.
txt = app.CreateText( "", 0.4 );
txt.SetMargins( 0, 0.05, 0, 0 );
txt.SetTextSize( 22 );
lay.AddChild( txt );

//Create an 'Send Message' button.
btn = app.CreateButton( "Send Message to Service", 0.6, 0.1 );
lay.AddChild( btn );
btn.SetOnTouch( function(){svc.SendMessage("change")} );

//Create a 'Stop Service' button.
btn = app.CreateButton( "Stop Service", 0.6, 0.1 );
lay.AddChild( btn );
btn.SetOnTouch( function(){svc.Stop()} );

//Add layout to app.
app.AddLayout( lay );

//Start/connect to our service.
svc = app.CreateService( "this","this", OnServiceReady );
svc.SetOnMessage( OnServiceMessage );
}

//Called after our service has started.
function OnServiceReady()
{
console.log( "Service Ready" );

}

//Called when messages comes from our service.
function OnServiceMessage( msg )
{
var msgArr = msg.split("," );
if (msgArr[0]=="count")
txt.SetText( "Count: " + msgArr[1] );
else if (msgArr[0]=="log")
console.log(msgArr[1]);
else if (msgArr[0]=="appPath")
{
// request Path from service
var appPath = app.GetAppPath();
msgSend = ["appPath" , appPath ];
// return Path app to service
svc.SendMessage( msgSend );
}
else
app.Alert( "error message from service - " + msgArr[0] );
}

function manualImport()
{
//if exist file json - import data into db
var existsApp = app.FileExists( app.GetAppPath() + "/dwn/manual.json" );
if (existsApp)
{
var dataRFApp = app.ReadFile( app.GetAppPath() + "/dwn/manual.json" );
addDataDBApp(dataRFApp);
}
}

//import data into db
function addDataDBApp( jsonDataApp )
{
//Parse JSON
var objApp =JSON.parse(jsonDataApp);

//app.MakeFolder ( appPath + "/db");

dbApp = app.OpenDatabase( app.GetAppPath() + "/db/DBTestApp" );

app.ShowPopup( "dbApp is " + (dbApp !== null) );
if (dbApp !== null )

{
//Create a table (if it does not exist already).

dbApp.ExecuteSql( "CREATE TABLE IF NOT EXISTS tblTest ('Id' Number, 'textSample' String)" );

for(var i =0; i<objApp.tsList.length; i++)
{
const colsApp = "'"+Object.keys(objApp.tsList[i] ).join("', '")+"'";
const placeholdersApp = Object.keys(objApp.tsList[i]).fill('?').join(", ");

//app.ShowPopup( Object.values(objApp.tsList[i]) );

//Add data (with error handler).
dbApp.ExecuteSql('INSERT INTO tblTest (' + colsApp + ') VALUES (' + placeholdersApp + ')', Object.values(objApp.tsList[i]), null, OnErrorApp);

}

app.DeleteFile( app.GetAppPath() + "/dwn/manual.json" );
}

}

//Callback to show errors.
function OnErrorApp( msg )
{
app.WriteFile( app.GetAppPath() + "/log.txt", "App Error: " + msg + "\n", "Append" );
app.ShowPopup( "App Error: " + msg );
}

Service.js
//Init variables.
var count = 0;
var diff = 1;

var msgSend = "";
var lastDvId = "";

var interval = 10000; //millisec

var appPath = "";

//Called when service is started.
function OnStart()
{
//Send request Path app to the app
msgSend = ["appPath", "" ];
app.SendMessage( msgSend );

//Start a timer to do some regular work.
setInterval( DoWork, interval );
}

//Called when we get a message from main app.
function OnMessage( msg )
{
//Handle commands from main App.
if( msg=="change" ) diff = (diff > 0 ? -1 : 1);

var msgArr = msg.split("," );
if (msgArr[0]=="appPath")
{
appPath = msgArr[1] ;
}
}

//Do some work.
function DoWork()
{
//This is where we do some regular background task
//(here we just modify a counter).
count += diff;

//Send data to the App (if it is running).
msgSend = ["count" , count ];
app.SendMessage( msgSend );

// Send last data value id to the app
msgSend = ["log", "lastDvId: " + lastDvId + "\n"];
app.SendMessage( msgSend );

// Send last data value id to the app
msgSend = ["log", "PrivateFolder: " + app.GetPrivateFolder( "DBTest" ) + "\n"];
app.SendMessage( msgSend );

//Send request Path app to the app
msgSend = ["appPath", "" ];
app.SendMessage( msgSend );

//if exist file json - import data into db
var exists = app.FileExists( appPath + "/dwn/import.json" );
if (exists)
{
var dataRF = app.ReadFile( appPath + "/dwn/import.json" );
addDataDB(dataRF);
}
}

//import data into db
function addDataDB( jsonData )

{
//Parse JSON
var obj =JSON.parse(jsonData);

//app.MakeFolder ( appPath + "/db");

//db = app.OpenDatabase( appPath + "/db/DBTest" );
db = app.OpenDatabase( app.GetPrivateFolder( "DBTest" ) + "/DBTest" );

app.ShowPopup( "db is " + (db !== null) );

if (db !== null )
{
//Create a table (if it does not exist already).
db.ExecuteSql( "CREATE TABLE IF NOT EXISTS tblTest ('Id' Number, 'textSample' String)" );

var lastDvIdLoc="";
for(var i =0; i<obj.tsList.length; i++)
{
const cols = "'"+Object.keys(obj.tsList[i] ).join("', '")+"'";
const placeholders = Object.keys(obj.tsList[i]).fill('?').join(", ");

//app.ShowPopup( Object.values(obj.tsList[i]) );

//Add data (with error handler).
db.ExecuteSql('INSERT INTO tblTest (' + cols + ') VALUES (' + placeholders + ')', Object.values(obj.tsList[i]), null, OnError);

( i == obj.tsList.length-1 ? lastDvIdLoc += obj.tsList[i].Id : lastDvIdLoc += obj.tsList[i].Id + ",")
}
lastDvId = lastDvIdLoc;
//app.ShowPopup( lastDvId );

app.DeleteFile( appPath + "/dwn/import.json" );

app.CopyFile( app.GetPrivateFolder( "DBTest" ) + "/DBTest", appPath + "/db/DBTest" );
app.DeleteFile( app.GetPrivateFolder( "DBTest" ) + "/DBTest" );
}

}

//Callback to show errors.
function OnError( msg )
{
app.WriteFile( appPath + "/log.txt", "Error: " + msg + "\n", "Append" );
app.ShowPopup( "Error: " + msg );
}

I use DroidScript v.1.68 on Motorola phone and I used DroidScript to create spk I have attached. I do not know what else to say! I'm sorry.

[Steve Garman]

Looks like you are still using the wrlng file.

This appears to still be the code you are using to try to create the table

[Stefano Tagnochetti]

This code part is in the serviceDbTest.js file and it works fine.

The part that does not work is found in the Service.js file that I wrote to you in the previous post.