I was stuck on this back way back when and Aeric's sample helped me more than I can ever express.
I use it now as my preferred method of data access even though I have a full AWS web server and database.
My web code (initially PHP, but now ASPX) has SELECT, UPDATE, DELETE, INSERT, and run stored proc.
my url looks something like this
//https:/mysite.co.nz/dblayer?ACTION=SELECT&fields=field1, field2, field3& table=customers&where=field1 > 200&order=customer_name&deviceid=78623467523
for stored procedures
//https:/mysite.co.nz/dblayer?ACTION=EXEC_SP&sp_name=increase&deviceid=78623467523
sending even quite raw sql statements split up into separate web field query paramater thingies was me hoping this would negate sql injection
Also any passwords needing to be sent or received are always encrypted
It would also be possible to build the url query as a single encrypted item and split it up on the web server
so encrypt ?ACTION=SELECT&fields=field1, field2, field3& table=customers&where=field1 > 200&order=customer_name with B4X into a single variable
and so that as a single query parameter
//https:/mysite.co.nz/dblayer?dothis=B4XencryptedString&deviceid=78623467523
That would hopefully slow a hacker up for a while
The deviceid is one of a number of registered devices in one of my database tables
In my case I use this as preferred method simply because it means deny access to the database to all IP addresses other than my web server IP address
This at least gives me an approach for security that I can if needed explain to any concerned party
Mostly for me its a simple understandable approach and I'm a big fan of simple
Being APSX I could code a web service to handle my call, but for me I cant see the need, other to put it on my resume
I have a module with a bunch of the routines I need and use them all my projects now. Although not perfect, it works really well for me
public Sub InsertData(Jobname As String, fields As String, tablename As String, values As String, ProgressMsg As String)
values = values.Replace(Chr(13), "")
values = values.Replace(Chr(10), "\r\n")
ToastMessageShow(ProgressMsg, True)
UpdateComplete = False
Dim Job As HttpJob
Job.Initialize(Jobname, Me)
Job.Download2(Main.gblURL, Array As String("Action", "INSERT", "fields", fields, "table", tablename, "values", values,"deviceid",Main.company.DeviceId))
End Sub
