SQLite export to CSV and send by FTP

I need a way to export the data stored locally on the device to a csv file and then send that file through FTP. Any ideas on how to accomplish this using Rho? Thanks

Robert Galvin
Tim, You are better off

Tim,

You are better off sending the database file via FTP and then have some backend process for reading and converting to CSV. There is no built in way for RhoElements to do this conversion of the database into a flat file format. It would be less of a load on the device to have that process run from the backend. This way on the device you can just checl to make sure the file make it up to the server. On the server you can use whatever language you want to read the SQLite db file and convert to CSV, etc.

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Tim Leland
Lets say the user wants to

Lets say the user wants to send just the grocery order. Right now I have one database(WLFOrders) file with multiple(Grocery1, Grocery2, Ref1, Ref2, Produce) tables inside of it. Can they send just one table or do I need separate databases for each order type?

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Robert Galvin
What you could do is create a

What you could do is create a table called 2nd database called “Sync”. In it you would copy the records that you want to send (the same ones that you would have written to the CSV file). Then do the FTP and then on success, clear the table. This way the size of the Sync database is minimized to just the records you want to FTP.

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Tim Leland
Rob Galvin wrote:What you

Rob Galvin wrote:

What you could do is create a table called 2nd database called “Sync”. In it you would copy the records that you want to send (the same ones that you would have written to the CSV file). Then do the FTP and then on success, clear the table. This way the size of the Sync database is minimized to just the records you want to FTP.

This will work but converting it on the device would be better for us. Converting sqlite to a .csv file is a simple process. Theres is a command line tool called sqlite3.exe that does the conversions. http://www.sqlite.org/download.html  We wouldnt want the user to exit rho to do the conversion. Is it possible to run an .exe from inside rho? Is javascript not an option? Any other ideas? Thanks

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Robert Galvin
Good idea with the sqllite3

Good idea with the sqllite3.exe. I have not tried that before.

Check the Generic section of the Help file for Launchprocess:

Ex:

// Launch CtlPanel (blocking)

var exitCode1 = generic.LaunchProcess('
application
ctlpanel.exe', '');

// Launch CtlPanel and see if the user closes it before 5 seconds

var hProcess = generic.LaunchProcessNonBlocking('
application
ctlpanel.exe', '');

var bRetVal = generic.WaitProcess(hProcess, 5);

if (bRetVal)

{

var exitCode2 = generic.GetProcessExitCode(hProcess);

alert('Process Ended by User: ' + exitCode2);

}

else

alert('Process Still Running');

generic.CloseProcess(hProcess);

</script>

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Tim Leland
What exactly is that doing?

What exactly is that doing?

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Tim Leland
Can windows mobile 6.5 run a

Can windows mobile 6.5 run a command prompt?

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Robert Galvin
You can't really launch a

You can't really launch a command prompt on the device. I think you really don't want to do that anyway and have more control of the process programatically.

It looks like my last post did not come over correctly, so I will try and explain again. In RhoElements you can use the generic.LaunchProcess() method to launch an external application (Windows Mobile/CE only). This will launch the application and when the application is exited will return back to the RHoElements application. Execution in RHoE will pause until the application is finished. You can also do this in a non Blocking way with generic.LaunchProcessNonBlocking. But in your case you may want to wait until the convert to csv is complete.

Example:

function launchExProc()
     {
      //  Launch CtlPanel (blocking)
      var exitCode1 = generic.LaunchProcess('\\application\\ctlpanel.exe', '');
      alert(exitCode1);

     }

In your case you would want to change the path and exe name to the application that is doing the converting. I did not find the exe you mentioned and am not sure what options it has for return values, command line options etc. But ideally you would be able to pass in some variable for how may records, what tables, etc and it would return a success/failure. If you have something that you have found to work on WinMo, please reply with the attachment and I will take a look at it.

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Tim Leland
Attached is the file Im

Attached is the file Im talking about. I tried to get it to work on the gun but it gave an error because it is not a valid pocket.exe. It works fine on windows 7 but not on the gun. Here is a link to the site where I got it. http://www.sqlite.org/download.html

The best option would be to convert it with using javascript but I cant figure out a way to do it. Here is a link to a demo app where it appears to take the file stored locally in browser (Local storage not databases but Id assume it works the same way) and converts it to a csv file for you to download. http://joshualay.net/examples/StamPad/StamPad.html

We would like to be able to do something similar and then ftp the file to our server.

Any ideas? Thanks

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Tim Leland
http://www.smorgasbordet.com

http://www.smorgasbordet.com/pellesc/ Did some googling and found this. What do you think?

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Robert Galvin
Try looking at: http:/
Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Tim Leland
I beleive that sqlite3.exe

I beleive that sqlite3.exe that we were running on windows 7 is just a shell of sqlite. The source code has to be combined with other windows mobile projects to work correctly. The sqlite3.exe shell only works on windows 7

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Robert Galvin
TimThat is a Win32 EXE. It

Tim

That is a Win32 EXE. It would need to be recompiled for WinMo using the C file and associated libraries. I was able to also get this to work on Win7. Once the exe is rebuilt for Winmo, the same process could work:

1) Create a text file with a list of the Sqlite3.exe commands (ex: export.sql)

.mode list

.separator ,

.output export.csv

select * from myTable;

.exit

2) The command to execute these commands would be something like this (if database, sqlite3.exe and export.sql are all in the same folder)

sqlite3.exe 1.db < export.sql

Once the exe is recompiled for WinMo, you could effectively change the launchprocess call above to:

generic.LaunchProcess('\\sqlite3.exe 1.db < export.sql', '');

of course with all of the paths set properly.

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Tim Leland
Ok I didnt think about

Ok I didnt think about compiling it for winmo. What would be the process for that?

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Tim Leland
I found this which is a port

I found this which is a port to C# which is suppose to run on windows mobile.

http://code.google.com/p/csharp-sqlite/

and this for CE

http://sqlite-wince.sourceforge.net/

I just am not familiar with compiling. Any experience with that?

Vote: 
Vote up!
Vote down!

Points: 0

You voted ‘up’


Log in to post comments