Android Question How do we create Database thru SQLite?

[Juzer Hussain]

Hi Erel,

I am downloading datatable from my online ms sql databse thru jason. It is taking around 1 min 25secs everytime.
I want to download this datatable in SQLite table once and use it from there so that it is faster from second time.Is there any example of this functionality.

Thanks in advance.

 

[imbault]

Hi,
You want to download your data from MS SQL, via a Web Service?
What do you mean by downloading once ? Is that online or you just want export your MS SQL data ?

 

[Juzer Hussain]

Hi ,
Yes via web service.
There is some data in MS SQL which will not change for some time.This data i want to download once in SQLite and keep using it.
Here requirement is simple.

Thanks

 

[imbault]

Ok, so this is quite easy:
You have to call your web service method from your app, then you receive a Json string with all your data, so first create the SQLite Database that matches your database structure.
Then in your JobDone sub , if successful, you do something like this code:

Sub JobDone (job1 As HttpJob)
Dim sRet As String

If job1.JobName = "checkjobs" Then
sRet = tools.GetWsresult(job1,Main.WS_get_pending_jobs,"checkjobs")
If sRet.Length>0 And sRet <> "-1" And sRet <> "-2" Then
affichejobs(sRet)
end if
end if
End Sub

Sub affichejobs(sJobs As String)
Dim parser As JSONParser
Dim rows As List
Dim query As String
parser.Initialize(sJobs)
rows = parser.NextArray

ProgressDialogShow("Data Import..") ' just a indicator
query ="DELETE FROM temp_job " ' Deleting all data in temp_job
Main.SQLpi.ExecNonQuery(query)

dbutils.InsertMaps(Main.SQLpi, "temp_job", rows) ' Importing all new data parsed
ProgressDialogHide ' hide the indicator


End Sub

 

[Juzer Hussain]

Many Thanks Imbault,
The code its showing errors for DBUtils( guess its a lib searching to download)
Pls inform Sqlpi is what type of variable in main.

Juzer

 

[Juzer Hussain]

Hi Imbault,
OK Sqlpi is from DBUtils module. Pls help me with tools object where to find this.
Thanks
Juzer

 

[imbault]

Many Thanks Imbault,
The code its showing errors for DBUtils( guess its a lib searching to download)
Pls inform Sqlpi is what type of variable in main.

Juzer

Ok SQLpi is a SQL object declared in Main:

Sub Process_Globals
    Dim ProgDB  = "sms.db" As String
    Dim sqlpi As SQL
End Sub

Sub Activity_Create(FirstTime As Boolean)
    If File.Exists(File.DirDefaultExternal, ProgDB)=False Then
        CreateDB
    End If
   
    sqlpi.Initialize(File.DirDefaultExternal, ProgDB, False)
....

Concerning DbUtils, you can find it on this forum https://www.b4x.com/android/forum/threads/dbutils-android-databases-are-now-simple.8475/

 

[DonManfred]

OK Sqlpi is from DBUtils module. Pls help me with tools object where to find this.

Have you tried to use the forumsearch? No. Try it.

 

[Juzer Hussain]

Hi Imbault,

Pls tell me which library to add to get tools class.it gives error there.

Thanks
Juzer

 

[imbault]

What error do you have?
you just need SQL Library and DbUtils Code Module...