Android Code Snippet [Chargeable] Sync SQLite with MS SQL Server using SyncStudio .JAR

Richard Calienes

Member
Licensed User
SyncStudio is an end-to-end database synchronization platform for B4A. Using SyncStudio frees the developer from having to do any programming related to database sync, replication, REST web service, etc. Below is an example of how to use the SyncStudio B4A compatible .JAR to create off-line data storage with MS SQL replication for your Android applications.

The SyncStudio B4A .JAR works in conjunction with the SyncStudio Sync Management Console. For information and access to a demo download is at www.dbsyncstudio.com. Use coupon code SSDL-8MLY-BA to purchase B4A version of SyncStudio Developer for $125.00 US.

To use SyncStudio with your Basic4Android app you need to do the following:

  1. First, copy the dbSyncStudioB4A.jar and dbSyncStudioB4A.xml files (located in the C:\Program Files (x86)\SyncStudio\Android\Basic4Android\Library\ folder) to your additional libraries folder in B4A.

  2. Add a reference to the SyncStudio Synchronization API Jar. In your project select the “Libs” tab; if you have copied the .jar and .xml files to the additional libraries directory you should see dbSyncStudioB4A as one of the additional libraries (note: you might need to refresh the list of additional libraries by right-clicking the libraries area and selecting “Refresh”, or by exiting the B4A development environment and re-starting it). Once you see the SyncStudio library in the list, check it so that it will be included in your B4A project.

  3. Create a Synchronization Object in the Process Globals section of your Main activity (or any other activity of your choice). The Synchronization Object needs to be a Process Global object. For example:
Sub Process_Globals

… (other statements)

'Declare the SyncStudio Synchronization Object:

DimSyncObjAsdbSyncStudioClient

… (other statements)

End Sub

In the section of your code where you want to call the synchronization add a call like following example to initialize the Synchronization Object. Please note that you cannot use the synchronization object until it has been initialized:


Main.SyncObj.Initialize _

(PROFILE_NAME, _

SERVER_URL, _

USER_ID, _

PASSWORD, _

USER_GROUP, _

USE_SSL, _

DB_FOLDER, _

DB_NAME)



Where:

PROFILE_NAME - String that contains the name of the synchronization profile being used. This field is for your reference only and can be left blank.


SERVER_URL - This is the URL of the Synchronization server where you deployed your project

USER_ID - This is the ID of the user that is being Synchronized. Needs to be one of the User Id’s
that you have configured in your database.

PASSWORD - This is the password of the user that is being synchronized. Needs to be the same as the
password in your database.

USER_GROUP - The group of the user being synchronized. This field is only needed if you are doing
Filtering. Otherwise leave blank.

USE_SSL - Send “Y” to force the use of https or “N” if your server is not configured for SSL.

DB_FOLDER Path (folder only) where the database resides Note: The folder name MUST end with “/”

DB_NAME Name of the Database file (without the folder) including the extension (i.e, testdata.db).

  1. To start the synchronization call:

SyncObj.startSync


Or if your object is declared in the Main Activity and you are calling it from some other activity:


Main.SyncObj.startSync


  1. The SyncStudio Synchronization Object will raise events as it performs the synchronization. All the events have the same format, so you need only one event handler, which must have the signature in the example below:


    Sub SyncStudio(eventName AsString, param1 AsString, _

    param2 AsString, param3 AsString, param4 AsString, _ param5 AsString, param6 AsString, param7 AsString)

Where:

param1 Number of Schema Records

param2 Number of Records Uploaded

param3 Number of Records Downloaded

param4 Downloaded Records Inserted

param5 Downloaded Records Updated

param6 Downloaded Records Deleted

param7 Blank or Error Code


Synchronization Example
The following code snippet shows how to call the synchronization object from a menu handler.

Please note that this code is assuming that somewhere in your app you have form controls for each of the parameters and that they contain data. Please refer to our sample B4A project for a more complete code example.

The first section of the code makes sure that the folder path is formatted correctly:

Sub mnuSync_Click

'Makes Sure that we have the right format for the path (folder) and the db name:

Dim Suffix AsString

Suffix = "/"

Dim TMP_DB_FOLDER AsString

TMP_DB_FOLDER = Main.EditProfile_DB_FOLDER

If TMP_DB_FOLDER.StartsWith (Suffix) = FalseThen

TMP_DB_FOLDER = Suffix & TMP_DB_FOLDER

EndIf

If TMP_DB_FOLDER.SubString2(TMP_DB_FOLDER.Length-1,TMP_DB_FOLDER.Length) <> Suffix Then

TMP_DB_FOLDER = TMP_DB_FOLDER & Suffix

EndIf

Dim TMP_DB_NAME AsString

TMP_DB_NAME = Main.EditProfile_DB_NAME

TMP_DB_NAME = TMP_DB_NAME.Replace(Suffix,"")

Main.SyncObj.Initialize _

(Main.EditProfile_PROFILE_NAME, _

Main.EditProfile_SERVER_URL, _

Main.EditProfile_USER_ID, _

Main.EditProfile_PASSWORD.Trim, _

Main.EditProfile_USER_GROUP, _

Main.EditProfile_USE_SSL, _

"N", _

TMP_DB_FOLDER, _

TMP_DB_NAME)

SyncInProgress = True

Main.SyncObj.startSync

End Sub

Handling Synchronization Events

To handle the events properly you need to look first at param7, which will contain either a blank or an error code. If param7 is blank then we simply have a regular synchronization status update telling you how many records have been synchronized so far. If param7 is not blank then there is a synchronization error.

'Sync Event Handler

Sub dbsyncstudio(eventName AsString, param1 AsString, param2 AsString, param3 AsString, param4 AsString, param5 AsString, param6 AsString, param7 AsString)

If eventName.ToUpperCase = "SYNC FINISHED"Then

SyncInProgress = False

EndIf

Label_SYNC_ACTIVITY.Text = eventName

If param1 <> "*"Then

Label_SYNC_SCHEMA_RECS.Text = "Schema Records: " & param1

EndIf

If param2 <> "*"Then

Label_SYNC_UPLOAD_RECS.Text = "Records Uploaded: " & param2

EndIf If param3 <> "*"Then

Label_SYNC_DOWNLOAD_RECS.Text = "Records Downloaded: " & param3

EndIf

If param4 <> "*"Then

Label_SYNC_DOWNLOAD_RECS_INSERTED.Text = " Inserted: " & param4

EndIf

If param5 <> "*"Then

Label_SYNC_DOWNLOAD_RECS_UPDATED.Text = " Updated: " & param5

EndIf

If param6 <> "*"Then

Label_SYNC_DOWNLOAD_RECS_DELETED.Text = " Deleted: " & param6

EndIf

If param7 <> ""Then

Msgbox(param7, "Synchronization Failed")

Log("Sync Error: " & param7)

SyncInProgress = False

Activity.Finish

EndIf

End Sub


The Profile Database

The SyncStudio Sample Android Client keeps a separate database with the server profiles. A Server Profile includes all the information that is needed to contact a synchronization server, including the profile name, the URL of the server, the User Id and Password for that server, the User Group (for filtering), Y/N flag that indicates the use of SSL, plus the folder where the database resides and the name (including extension but no folder) of the database file. The following code is used to create the Profile Database:


'Create a new SyncStudio Profile Database

Sub CreateDb

Try

SQLobj.Initialize(DbFolder, DbName, True)

Dim fldMap AsMap

fldMap.Initialize

fldMap.Put("PROFILE_ID", DBUtils.DB_INTEGER)

fldMap.Put("PROFILE_NAME", DBUtils.DB_TEXT)

fldMap.Put("SERVER_URL", DBUtils.DB_TEXT)

fldMap.Put("USER_ID", DBUtils.DB_TEXT)

fldMap.Put("PASSWORD", DBUtils.DB_TEXT)

fldMap.Put("USER_GROUP", DBUtils.DB_TEXT)

fldMap.Put("USE_SSL", DBUtils.DB_TEXT)

fldMap.Put("DB_FOLDER", DBUtils.DB_TEXT)

fldMap.Put("DB_NAME", DBUtils.DB_TEXT)


DBUtils.CreateTable(SQLobj, "PROFILES", fldMap, "PROFILE_ID")



ReturnTrue


Catch

ReturnFalse

EndTry

End Sub


Getting at the Database Location
In Android we need to know where the database is located. This function checks the Internal Storage first. If the database is not located there it then checks for a writable SD card and if one is present then searches the SD card. This function returns either the directory where the database is located or a blank string if no database is found.

'Returns the location (directory) of the database

'(SD Card or Internal Storage)

'Returns a blank string if the DB was not found.

Sub GetDBLocation(FileName AsString) AsString

Dim TargetDir AsString

'Check to see if we have the file in the internal storage first:

IfFile.Exists(File.DirInternal , FileName) = TrueThen

'Yes, the DB is located in the internal storage

ReturnFile.DirInternal

Else

'No, see if we have a writable SD Card

IfFile.ExternalWritable = TrueThen

'Yes. See if the DB file is there:

IfFile.Exists(File.DirDefaultExternal , FileName) = _

TrueThen

'Yes, the DB is in the SD Card

'in the default directory

'for the app

' <storage card>/Android/data/<package>/files/

ReturnFile.DirDefaultExternal

Else

'The DB is not there at all (or it has been moved)

Return""

EndIf

Else

'No. This means we either do not

'have a database (1st time app is loaded)

'or that the DB is in the SD Card but

'the SD Card is not accessible.

Return""

EndIf

EndIf

End Sub
 

HotShoe

Well-Known Member
Licensed User
Shouldn't this be in the libraries forum?

--- Jem
 
Top