Android Question Best Practice Question - SQL server

[sulsys]

First, want to say the support in this forum is awesome and the software Anywhere is putting out is really good and Erel seems to be the rockstar, and one willing to help also!

Ok, here goes the background: I am developing an application to be used in the health care field. Each tablet user will need to receive daily updates of data that is currently in MS SQL server. Each user will only receive and update of "their" information. That data will be stored and referenced on the tablet. The users will add data in the form of transactions complete with signatures and potentially lengthy text data. This data they enter needs to be sent back to the ms sql server daily.

Now the questions:

1) What would be the best way to gather the MS Sql data? I have complete control over the server so can pre-filter the data or can do it while connected.
2) What is the best storage method on the tablet? SQLITE?
3) How would I best return the data to the server? I will want to flag transactions as sent so they remain on the tablet but are not sent each time. (That should be easy).
4) Should the data transfer functions be in their own app separate from the data entry app?

Thanks for any suggestions!

Tim

 

[qsrtech]

HI Tim

1) What would be the best way to gather the MS Sql data? I have complete control over the server so can pre-filter the data or can do it while connected.

I use http and asp page to run stored procedures and return data. Here is sample:

qry=request.form'("qry")
sqlConnectStr=session("ConnectionString")
Set SqlConn = Server.CreateObject("ADODB.Connection")
sqlConn.open  session("ConnectionString")
set oRs=server.CreateObject("ADODB.RecordSet")
ors.Open qry,sqlConn,3,1
  
on error resume next
if oRs.BOF and oRs.EOF then
    Response.Write("0")
else
    Response.Write(oRs.GetString(2)) 'this returns chr(13) separated records and a TAB separated record
end if

This is a sub I use to get a "List" of records

Sub GetRecords(result As String) As List
    Dim aList As List
    aList.Initialize
    'Dim line As String
    If EndRecord="" Then
        EndRecord=Chr(13)'Chr(0)
    End If
    If result="0" Then 'nothing
    Else
        Dim lines() As String
        lines=Regex.Split(EndRecord,result)
        Dim A As Int
        For A=1 To lines.Length
            aList.Add(lines(A-1))
        Next
    End If
    Return aList
End Sub

Used like this in a httpjob "done":

    If job.Success Then
        Dim Records As List
        Records=GetRecords(job.GetString)

and I loop through the "records" like this:

    For Each ALine As String In Records
        Dim line() As String
        line=Regex.Split(TAB,ALine)
        Edit1.text=line(0)
                Edit2.text=line(1)
...etc

2) What is the best storage method on the tablet? SQLITE?

I Use SQL Lite to log everything. My transactions are done in real-time unless there is a network glitch. I have a "posted" flag such that I can return any "SQL Script" that hasn't been posted yet.
Here's a "log" script I use:

Sub LogSQL(SQL As String) As Int
        Dim sSql As String
        sSql="INSERT INTO SQLLog(Date,SQL,Posted) VALUES(" & Delphi.Date & ",'" & SQL.Replace("'","""") & "',0)" 'the date is only for the "log" to know when it was logged
        Log(sSql)
        ASQL.ExecNonQuery(sSql)
        sSql="Select last_insert_rowid()"
        Return ASQL.ExecQuerySingleResult(sSql)

Here's an update script I use:

            Dim sSQL As String
            sSQL="UPDATE SQLLog SET POSTED=1 WHERE ID=" & ID
            ASQL.ExecNonQuery(sSQL)

3) How would I best return the data to the server? I will want to flag transactions as sent so they remain on the tablet but are not sent each time. (That should be easy).

As I said earlier, I use http and Stored Procedures to post data to my server. I do thousands of transactions per day in real-time. You could store your "SQL INSERT Script" in a simple SQLLITE table with the "posted" flag=0. When ready to "post" your transactions you loop through your table that has posted=0 and post to http. I successful you flag it posted=1

4) Should the data transfer functions be in their own app separate from the data entry app?

I would just do it within the app. Maybe run it as a service.

I do hundreds of thousands of transactions this way. Hope this helps you move forward.

 

[sulsys]

qsrtech,

Thank you! May take me a while to comprehend but I sincerely appreciate the reply, and the detail.

This app has tremendous excitement in our user community and your help may make it happen faster.

Tim

 

[Roberto P.]

Hi Qsrtech,
I also have the same requirement described. But I can not understand how should I implement the connection to the database and how to read and write data to the sql database.
if you can, would you do me the courtesy to send me a small example of how you did.

thank you very much in advance.
roberto

 

[sulsys]

Qsrtechs reply was very helpful and very complete. However, after reading some threads I found this one about remote database connector.

http://www.b4x.com/android/forum/threads/remote-database-connector-connect-to-any-remote-db.31540/

I was able to configure the server in no time and with a few tweaks I was accessing SQL data directly in the application. I am very pleased with the performance and believe this is going to be the direction I go. Check out the tutorial and be sure to check out the link to the other required files.

 

[qsrtech]

Qsrtechs reply was very helpful and very complete. However, after reading some threads I found this one about remote database connector.

http://www.b4x.com/android/forum/threads/remote-database-connector-connect-to-any-remote-db.31540/

I was able to configure the server in no time and with a few tweaks I was accessing SQL data directly in the application. I am very pleased with the performance and believe this is going to be the direction I go. Check out the tutorial and be sure to check out the link to the other required files.

IDK sulsys, it might be a good approach for simple stuff but to maintain a list of sql commands in the server could be very daunting. And it's pretty much using the same http framework so not sure how it could be any faster/different than asp. I would prefer to use ASP and have a list of "sql scripts" in a table which could be run based on the "command_name".

Also asp allows for html formatting and other stuff for reports, etc.

Lastly, I use sql mgmt studio and if I want to add a new "command" to the rdc I'd have to log on to my server all the time and update it. Using asp I simply build the stored procedure and I can run/test it immediately, no maintenance. And what if your sever doesn't allow running RDC? You can get a simple server for like $6/7 to host the whole thing with ASP/SQL Server.

 

[qsrtech]

Hi Qsrtech,
I also have the same requirement described. But I can not understand how should I implement the connection to the database and how to read and write data to the sql database.
if you can, would you do me the courtesy to send me a small example of how you did.

thank you very much in advance.
roberto

Are you using IIS? ASP? ASP.NET? SQL SERVER?

 

[Roberto P.]

I'm need use SqlServer

 

[sulsys]

qsrtech,

All good points. Like most solutions there are many. I to use SQL mgmt studio and for this project being able to manage the queries in only one place is ideal. I'll have to look at the asp again. I think I got a little hung up and then the RDC presented itself and it worked first time.

Thanks again

 

[sulsys]

qsrtech,

What are you using to create the asp page? I have created many aspx pages in visual studio but never an asp page standing alone.

 

[Roberto P.]

I'd use B4J to make this little "server"

 

[qsrtech]

Do you have ASP setup on your "sql server" server? Do you have your sql connection string? I'll work on packing my custom httputils into a library in the mean time

qsrtech,

What are you using to create the asp page? I have created many aspx pages in visual studio but never an asp page standing alone.

Notepad lol. Is your server set up to run asp? IIS isn't running asp by default anymore. Anyways you can use asp.net, i just haven't bothered trying to switch over. For what I'm doing asp is good enough.

 

[sulsys]

qsrtech

Yes ASP is enabled and I have the connection string.

 

[qsrtech]

k, I'll post a couple files shortly

 

[qsrtech]

here's a global ASA file where you can put your connection string in and access it from any asp script. Also Included is a very simple, basic asp script to execute a SQL statement and either return data or just insert, basically anything. You can add an "Authorization" header to your post request to authenticate the user.

Here's some basic b4a code:

    Dim http As HttpJob
    http.Initialize("SQL",Me)
    Dim SQL as String
    SQL="EXECUTE YOUR STORED PROCEDURE OR SQL SCRIPT"
    SQL=SQL.Replace(" ","%20")'I like to remove spaces just in case
    http.PostString(SQLServer,SQL) 'SQLServer is a variable to the webserver, i.e. http://www.yourserver.com/getsql.asp
    http.GetRequest.SetHeader("Authorization",SOMECODE) 'you can use a header like this but you have to update the asp script to grab the header and do what you want with it

here's some code to handle a "SELECT".

Sub JobDone(job As HttpJob)
    If job.success Then
        Dim result As String=job.getstring
        If result<>"0" then
        Dim rows() As String=Regex.Split(Chr(13),result)
        Select Case job.jobname
            Case "AJOBNAME"
                For Each record As String In rows
                    Dim cols() As String=Regex.Split(TAB,record)
                    x=cols(0)
                    y=cols(1)
                    z=cols(2)
                    'etc....
                Next
        End Select
        Else
            'nothing returned, do something?
        End If
    End If
End Sub

Note: I use a custom httputils implementation so I could be off slightly on the httpjob code. Hope this gets you going...

 

[sulsys]

qsrtech

Got on W3Schools and reviewed some basic ASP. Setup an asa file and an ASP page to execute and write out the results of a simple query. Very fast and was pretty easy! Thanks for the direction. Now on to the tablet to consume the results!

thanks again for all your help

 

[qsrtech]

Results, ASP or RDC?

 

[sulsys]

Good question, depends on the situation. In this one I believe ASP will win as there are 100's of stored procedures I have already written that I can use for much of what I need for this.

Now what I have to figure out is how to make the response HIPPA compliant as the information is medical related..... I assume this will work on a HTTPS connection?

 

[qsrtech]

I'm not sure about https yet. I know I'll have to switch soon though...

 

[sulsys]

qsrtech,

I hate being the rookie...!

I have an asp page on my server that is working just fine when I type in the url from a browser on a client. I have started simple just to prove the connection and get a feel for the process. If I type into the client browser the address http://myserveraddress.com/TestASP/providers.asp?Company=40 I get the results I expect. (TestASP is the virtual directory).

In B4A I want to post the same thing and I am using the http.poststring(url, querystring) format to do so where url is "http://myserveraddress.com/TestASP/providers.asp" and querystring is "?Company=40".

I get an "Internal Server Error" in the jobdone routine. I have tried the querystring with and without the "?".

Apologize for my ignorance.... I know what I am doing wrong is likely super simple.