SQLite to online MySQL Example

[Tom Law]

Hi All,

After a few weeks working with B4A I have developed a multi table database application which uses SQLite. The idea was to have the database filled automatically using records downloaded from a MySQL database on my website. Records would be modified by the user and then returned back to the website.

To download files I looked at the online example:

'Connect Android to MySQL Database Tutorial'

The tutorial was very helpful and helped me learn something about the httputils library. This works great and now I can download and modify data quickly and easily.

However I now need to send my modified data back to the online database but am unsure where to start. Does anyone have an example of the code needed to achieve this? Can I use the httputils functions to do this and do I need a different php script?


Thanks

Tom

 

[Erel]

As far as I remember, you can send INSERT commands with this PHP script.

You should build the query and send it to the web service.

 

[Tom Law]

Thank you Erel but I've been struggling to do this for a while now. i haven't been able to find an example on the site that deals with the sending of data from a mobile to an online mysql database. Any chance that you could either provide some code or otherwise point me towards an example.

 

[pluton]

Hi All,
...cut
.... Records would be modified by the user and then returned back to the website.
To download files I looked at the online example:
'Connect Android to MySQL Database Tutorial'
...

However I now need to send my modified data back to the online database but am unsure where to start. Does anyone have an example of the code needed to achieve this? Can I use the httputils functions to do this and do I need a different php script?

Thanks

Tom

Hi Tom
You don't need different php script. If you manage to download data from your online MySql database you can also upload modified again. Just use ExecuteRemoteQuery

Example:
If you manage to download data with this:

ExecuteRemoteQuery("SELECT countries WHERE id='" & tl.First & "'", COUNTRY_POPULATION)

You can also insert your data into your MySql. Just use MySql language:

ExecuteRemoteQuery("INSERT INTO countries WHERE id='" & tl.First & "'", COUNTRY_POPULATION)

You will figure it out

 

[Tom Law]

Hi Pluton,

Yes I had tried something like that however its the underlying code around this step that is giving me the problem. When I call ExecuteRemoteQuery with the insert command

ExecuteRemoteQuery("INSERT INTO NetService WHERE JobComplete = '1'",sendSvc)

it throws up an hc_ResponseError with a status code of 500.

I'm probably missing something really basic but this is all new to me.


Appreciate your input.

Tom

 

[tremara1]

numeric

Not sure on this one but do you need to parenthesise the numeric value? I thought this would turn it into a string? .....just a thought.

 

[Tom Law]

SUCCESS - It works

Thank you gentlemen for putting me on the right track. For the benefit of anyone else struggling to send data from an SQLite database to an online MySQL database you need to:

1) Create a cursor containing the SQLite records that you need to send
2) Cycle through the cursor one record at a time
3) Create a map for each record
4) Call ExecuteRemoteQuery to insert the map values
3) Use either an insert or update query - mine was

ExecuteRemoteQuery("UPDATE NetService SET SiteNameAddress='" & m.GetValueAt(7)& "',SvcPostcode='" & m.GetValueAt(8)& "',SiteTel='" & m.GetValueAt(9)& "',SiteEmail='" & m.GetValueAt(10)& "' WHERE ServiceID = " & m.GetValueAt(1) , SendSvc)

(the m.GetValueAt() was simply a bunch of field values held in a map - the SendSvc variable was an integer to tell the program which table action I was performing)

I would recommend Erels tutorial: 'Connect Android to MySQL Database Tutorial'
:icon_clap: