Android Question Database design, options and utilities advice?

[RandomCoder]

Dear Forum experts, I require a little advice to hopefully get me started in the right direction.

A little background first...
In a previous employment I administrated a large database created using MS Access and a huge amount of VBA. The database was split in two, a front-end stored locally on each of the Windows PC's (multiple users) for entering and viewing data using Forms, and a back-end which was stored on the company network that held all of the data in Tables. The database was used to store machine information, maintenance schedules and inspection reports along with all manner of other data and productivity information. Reports would be automatically issued detailing what maintenance was required on which machines and graphs to show how productive the machines were and thus how effective the maintenance was. I even did some work with pivot tables/charts so that the data could be drilled into enabling the various production managers to better understand where resources were required.

My new requirement...
Having shown some of the reports produced from the old system to my current employer, they are very interested in implementing something similar. I would like to modernise it and use B4A so that all information can be entered and made available using handheld tablets. I realise that pivot tables/charts might be out of the question at the moment but so long as the data is available in a networked database then there is no reason why I couldn't implement something else for this. The data will still be stored on a Windows PC which I suspect is running Windows Server Edition. I could also continue to use MS Access on some of the shop-floor PC's although this might complicate things? Maybe implementing a B4J equivalent of the tablet B4A code would be better?

My questions...
1). What would be the most suitable way to store the back-end data?
2). Is RDC still the best method of connecting to the back-end data?
3). How do I handle multiple users and concurrent data (MS Access did this automatically for me)?
4). What is the best way to handle the front-end accessing and updating data?
5). What tools and utilities are there to make life easier? I've done a lot of SQL but would still consider myself as somewhat of a beginner. MS Access has a utility that allows me to create queries etc and I then copy and manipulate the SQL statements to my exact needs.
6). Security considerations? It would be nice that at a later stage I could allow remote access from outside of the LAN, allowing production data to be viewed from anywhere.

How much of this is possible? I suspect that it all can be done although I might not be the right man for the job, which I've already pointed out to them? I've no PHP Scripting experience and JSON is also a new term that I am not familiar with. Where to start? Advice please?

Spoiler: I've read these posts...

RDC - Simple way to create your own back-end database (Dated August 2013)
Access any database or system with B4AServer (Dated June 2011) I don't think this is applicable for my requirements.
Read/Write from/to a database on your harddrive (Dated July 2012) Is this still the best way?
RDC (Remote Database Connector) and Access mdb database (Dated September 2013)
Best approach for database in App (Dated January 2014) Is this still current and upto date?

Thanks,
RandomCoder

 

[edgar_ortiz]

RandomCoder,

In my experience:
1) My-Sql
2) In a private LAN: Remote ODBC Conecction, in a WAN use Web-Services and access the data via Store Procedures
3) My-Sql do that for you
4) B4A
5) My-Sql Workbench and Toad For My-Sql (from Quest Software)
6) The easy: Use a Web Hosting

Regards,

Edgar

 

[DonManfred]

1). What would be the most suitable way to store the back-end data?

MySQL

2). Is RDC still the best method of connecting to the back-end data?

Normally i would say yes. But it may depend on your needs.

3). How do I handle multiple users and concurrent data (MS Access did this automatically for me)?

Use RDC or a self-made-bridge between b4a and your database. Written in PHP for example.

4). What is the best way to handle the front-end accessing and updating data?

B4A, Httputils2 and on DB-side a PHP-bridge for ex. Works a treat for me. Fou can make the "bridge" with vb too if you feel comfortable with it. Just use the language you prefer..... Please note that you can build the bridge with B4J too. B4A and B4J are a good team!

5). What tools and utilities are there to make life easier? I've done a lot of SQL but would still consider myself as somewhat of a beginner. MS Access has a utility that allows me to create queries etc and I then copy and manipulate the SQL statements to my exact needs.

MySQL-Workbench, phpMyAdmin

6). Security considerations? It would be nice that at a later stage I could allow remote access from outside of the LAN, allowing production data to be viewed from anywhere.

I've no PHP Scripting experience and JSON is also a new term that I am not familiar with.

talking about json you should have THIS on your "most wanted urls" about json-parsing (generating source)!!!
For example:
Copy the following json-code into the top left box

{
"glossary": {
"title": "example glossary",
"GlossDiv": {
"title": "S",
"GlossList": {
"GlossEntry": {
"ID": "SGML",
"SortAs": "SGML",
"GlossTerm": "Standard Generalized Markup Language",
"Acronym": "SGML",
"Abbrev": "ISO 8879:1986",
"GlossDef": {
"para": "A meta-markup language, used to create markup languages such as DocBook.",
"GlossSeeAlso": ["GML", "XML"]
},
"GlossSee": "markup"
}
}
}
}
}

and press PARSE. As result you will get a working B4A-code to read that json. It´s one of @Erel genius-tools
I like it a lot

 

[KMatle]

Yep

 

[RandomCoder]

@edgar_ortiz and @DonManfred thanks for the pointers guys. Now I need to read up more on these things, knowing at least that I am on the right track. The JSON converter is indeed a useful tool. Does @Erel have anymore utilities like this stashed away that I'm unaware of?

@KMatle I've just noticed the links in your sig and I'll also read through those later.

Once again, thanks guys!

 

[RandomCoder]

May I just check that I'm still on track with my understanding of what I've read so far....
Unlike my original MS Access database where the front-end contained all the Forms AND Queries and the back-end contained only Tables.
The new approach with b4a is that the front-end on the phone/tablet will contain only Forms and http requests to a new "middle-man" which is where the PHP scripts are stored. The "middle-man" then handles all of the Queries both getting and setting data. The back-end is still where all the tables are stored.
And in reality the "middle-man" and the back-end are both stored on the server.

In my simplistic approach does this sounds about right?
Also, am I OK to ask php related questions here, tagged as "other". Or is there a different forum specialising in php that you can recommend?

 

[KMatle]

Yes. It is "normal" to separate the surface (User Interface) from the Databases. Next thought: Is it a local app (on one device only)? I yes, use MySql Lite on your phone. If you want to develop an app which several users use, you need a central server to handle the data.

As an inhouse solution you could use Xampp (see my tutorial here: https://www.b4x.com/android/forum/t...hp-mysql-server-xampp-with-b4a.48635/#content) or another similar installation (see DonManfreds post). It is simple to set up and to use.

PHP is used because any server uses it.

The workflow looks like this:

B4x App -> httputils -> Server -> php -> Sql query -> MySql -> Database -> Table -> Content -> php -> httputils -> App

Example (Gets all rows from a table)

B4A:

Dim GetA As HttpJob
    GetA.Initialize("GetA", Me)
    GetA.Download2("http://192.168.178.21/order/order.php", _
              Array As String("Action", "GetA"))

PHP:

case "GetA":
        $q = mysql_query("SELECT aid, agruppe, aname, apreis FROM artikel order by aname");
        $rows = array();
        while($r = mysql_fetch_assoc($q))
        {
            $rows[] = $r;
        }
        print json_encode($rows);
        break;

Back in B4A:

 ListofArtikel = parser.NextArray
                       If ListofArtikel.Size > 0 Then
                          artikelLV.Clear
                           For i = 0 To ListofArtikel.Size - 1
                            Dim SingleArtikel As Map
                            SingleArtikel = ListofArtikel.Get(i)
                            artikelLV.AddSingleLine(SingleArtikel.Get("aid")&":"& SingleArtikel.Get("aname") & ":" & SingleArtikel.Get("agruppe") & ":" & SingleArtikel.Get("apreis"))
                           Next
                       End If