Read/Write from/to a database on your harddrive

IamBot · Jul 23, 2012

Hi,

First of all, I'm very new to B4A but I like it a lot so far.

I have managed to create a database file using SQlite database browser, added the database to files in the B4A program and successfully read necessary data from it.

In my program, I want to be able to manually modify the database (on my computers harddrive) while the program is running and it should be able to input and receive new data without uploading a new file to Dir.Assets. Later on, I want to be able to read/input from/to a database on an online server, but I want to take one step at the time.

I want this to be able to work while I'm running the program on the emulator (I guess its not possible if I do it on the device when the datebase is on the computer harddrive).

I've read most SQL/database threads on the forum but can't figure out what to focus on.

Thank you guys in advance.

Erel · Jul 23, 2012

There is no difference between a device and an emulator in this case.
The emulator uses its own file system and cannot access directly Windows file system.

You will need to use a solution based on some network implementation.

IamBot · Jul 23, 2012

Thank you for your reply.

Can you refer to any relevant thread? Also, should I use SQlite, MySQL, MS SQL or any other program?

warwound · Jul 23, 2012

Hi there.

You will have a device SQLiteDatabase and and a remote (your computer) SQLiteDatabase.

It will be up to you to synchronise them and ensure that they remain synchronised.
How will you do that?

Has your computer got a web server running?*
If so then you can use various PHP (or other server side) functions to maintain and synchronise both databases.

BUT the process will never (or maybe!) be perfect...

If you modify the database on your computer or device then how will the other database know that a change has been made and therefore you need to synchronise both databases?

You'll need a 'last_updated' or similar column in BOTH database tables.
Now your device or computer knows when exactly the database was last updated.

Your device can send a request to your computer with a parameter 'last_updated'.
Your computer can check to see if it's data is newer than that 'last_updated' timestamp and if so return new data to your application.

You need to think about all the possible possibilities...

Has the computer version of the database been modified but the Android device database has NOT?

Or has the device database been modified and the computer version NOT?

*Your computer will need to be accessible from your device and for that you'll need to run a web server on your computer.
I'd recommend XAMPP

Things get technical - have you any skills in PHP or other server side programmings languages?

Post again with more detail and i'll try and help.

Martin.

IamBot · Jul 24, 2012

Hi,

Sorry if my initial post was unclear, but basically what I want is:

- Create an online database which can be modified by any user using my application on their phone.

I started with B4A couple of days before and before that I had no experience in the program nor any SQL etc, unfortunately.

I have installed MySQL and managed to create a database with necessary tables for my program, but now I'm stuck.

I would appreciate if you could suggest necessary steps to take, like a program I should install, thread I can read or any other helpful info.

Thank you.

warwound · Jul 24, 2012

Here's some ideas...

Your need a method to get values from the remote database into your B4A project and a script to save values from your B4A project to the online database.

For this i'd use PHP as i am familiar with it and just about every webhost supports PHP.
Have you got any server-side scripting skills?

Here's an example script you might be able to adapt:

PHP:

<?php

//   edit these strings so the script can connect
$database_name='';
$host='';
$password='';
$tablename='';
$username='';

//   the array $response will eventually be converted to a JSON object and output as text by the script
$response=array();

$database=mysql_connect($host, $username, $password);
if($database==false){
   $response['debug']='Failed to connect to the database: ' . mysql_error();
} else {
   if(mysql_select_db($database_name, $database)==false){
      $response['debug']='Failed to select the database: ' . mysql_error();
   } else {
      mysql_query('set names utf8');
      $query="SELECT * FROM $tablename";
      $result=mysql_query($query);
      
      if($result==false){
         $response['debug']='A database error has occurred'.mysql_error();
      } else {
         while ($row = mysql_fetch_assoc($result)) {
            $response[]=$row;
         }
      }
   }
}

ob_start('ob_gzhandler'); //   comment/uncomment to enable gzip compression on the JSON output by this script
header('Cache-Control: no-cache, must-revalidate');
header('Content-type: application/json; charset=UTF-8');

echo utf8_encode(json_encode($response));
?>

It'll return a String which can be processed using the JSON library, so have a read on the JSON tutorial.
The object created by the String will either be a JSONObject that has a single property named 'debug' whose value will be an error message.
Or if the script runs successfully it will create a JSONArray of JSONObjects.
Each JSONObject in the JSONArray will represent a row of data from the query 'SELECT * FROM $tablename'.

So edit the String variables at the start of the script and upload it to your webserver.
Now try to open it in a browser - what result does the browser display?

Once the script is working you need to read up on how to call the script from your B4A project - look at the HttpUtils example.

Your B4A project can now connect to your remote database and get the current database values.
You'll need to create an Activity to enable the user now to edit those values.
And then you'll need to use HttpUtils again to send the modified values back to your web server - where a different PHP script can insert the modified values into the database.

Get as far as retrieving the JSON String and converting it to values you can use in your B4A code first though and think about the rest as and when you get that far.

Martin.

cirollo · Oct 9, 2012

followed your example....

but I always get this message if I try to access my php script from browser:

"debug":"A database error has occurredQuery was empty"}

obviously the table exists and is not empty (I access It from php myadmin)

warwound · Oct 10, 2012

cirollo said:
but I always get this message if I try to access my php script from browser:

"debug":"A database error has occurredQuery was empty"}

obviously the table exists and is not empty (I access It from php myadmin)

Try downloading the PHP script again, i'd made a mistake: :signOops:

PHP:

$result=mysql_query($result);

Should be:

PHP:

$result=mysql_query($query);

Martin.

cirollo · Oct 10, 2012

Thank You!!!

Now it works!!!!!!

thanksssss

peacemaker · Oct 10, 2012

And what about any PHP-template of receiving\updating MySQL from B4A ?
Maybe getting a file from B4A (uploading) and updating the MySQL table ?

warwound · Oct 10, 2012

peacemaker said:
And what about any PHP-template of receiving\updating MySQL from B4A ?
Maybe getting a file from B4A (uploading) and updating the MySQL table ?

Not as straightforward as a generic script to get values from MySQL to the B4A app.

First thing to consider is how will you ensure that the requests to your MySQL server to insert and/or update data are valid - that the requests are from your app and not a hacker...?

Security aside i'd suggest sending data to MySQL in JSON format.
PHP has functions that make light work of decoding the string you send your script back into an object that you can iterate over.
You can get column names, row indexes and values that you want to update if they exist or insert if they don't exist.

I don't have time to create such an example today - in fact i'm pretty busy all of this week.

Martin.

cirollo · Oct 15, 2012

examples of insert/updating mysql from b4a app

I'm requesting these examples too

infact your php code is the only one that works for me and I've tried all the insert/update examples with no success

:BangHead:

warwound · Oct 16, 2012

OK here's an untested script that will receive POST data with a name of 'json'.
The POST data is converted from a string into an associative array.
This associative array can have properties 'insert' and/or 'update'.
Both insert and update are indexed arrays of associative arrays.

Here's some B4A code that shows the data structure to create in B4A:

B4X:

Sub CreateJson
   
   Dim PostData As Map
   PostData.Initialize
   
   Dim InsertData As List
   InsertData.Initialize
   
   Dim NewRow As Map
   NewRow.Initialize
   
   NewRow.Put("name", "Martin")
   NewRow.Put("postcode", "PE30 2DU")
   NewRow.Put("age", 44)
   
   InsertData.Add(NewRow)
   
   Dim NewRow As Map
   NewRow.Initialize
   
   NewRow.Put("name", "Zedolf")
   NewRow.Put("postcode", "Unknown")
   NewRow.Put("age", 12)
   
   InsertData.Add(NewRow)
   
   PostData.Put("insert", InsertData)
   
   Dim Generator As JSONGenerator
   Generator.Initialize(PostData)
   
   Log("The data to be POST'd to the PHP script is:")
   Log(Generator.ToString)
   
End Sub

The log shows:

{"insert":[{"postcode":"PE30 2DU","age":44,"name":"Martin"},{"postcode":"Unknown","age":12,"name":"Zedolf"}]}

'name', 'postcode' and 'age' are column names in your database table.

And the PHP script:

PHP:

<?php
//   edit these strings so the script can connect to your database
$database_name = '';
$host = '';
$password = '';
$tablename = '';
$username = '';

$response = array('status'=>'SUCCESS');

if (isset($_POST['json'])) {

   $database = mysql_connect($host, $username, $password);
   if ($database == false) {
      $response['debug'] = 'Failed to connect to the database: ' . mysql_error();
      $response['status']='FAIL';
   } else {
      if (mysql_select_db($database_name, $database) == false) {
         $response['debug'] = 'Failed to select the database: ' . mysql_error();
         $response['status']='FAIL';
      } else {
         mysql_query('set names utf8');
         //   first get the POST'd json string, escaping it to prevent injection attacks
         $json=mysql_real_escape_string($_POST['json']);
         //   convert the string into an object
         $json=json_decode($json, true);
         
         //   the json object can have 'insert' and/or 'update' properties
         //   each property will be an indexed array of associative arrays
         
         if(isset($json['insert'])){
            $insert=$json['insert'];
            foreach($insert as $row){
               $column_names=array();
               $column_values=array();
               foreach($row as $key=>$value){
                  $column_names[]=$key;
                  $column_values="'$value'";
               }
               $column_names=implode(',', $column_names);
               $column_values=implode(',', $column_values);
               $query="INSERT INTO $tablename ($column_names) VALUES ($column_values)";
               $result=mysql_query($query);
               if($result==false){
                  if(!isset($response['failed_inserts'])){
                     $response['failed_inserts']=array();
                     $response['status']='ERRORS';
                  }
                  $response['failed_inserts'][]=$query.'|'.mysql_error();
               }
            }
         }
         
         //   to do: process the json 'update' array
         
      }
   }
} else {
   $response['debug']='no json data found in request';
   $response['status']='FAIL';
}

ob_start('ob_gzhandler'); //   comment/uncomment to enable gzip compression on the JSON output by this script
header('Cache-Control: no-cache, must-revalidate');
header('Content-type: application/json; charset=UTF-8');

echo utf8_encode(json_encode($response));
?>

You can see i've only had time to implement the 'insert' code.

The script returns a string to your B4A code which you'd parse into a Map object.
The Map will have a key named 'status' and that key's value will be 'SUCCESS', 'FAIL' or 'ERRORS'.
If the value is FAIL then the Map will also have a key named 'debug', it's value telling why the script failed.
If the value is ERRORS then the Map will have a key named 'failed_inserts', it's value will be a List of Strings, each List item will show the query that was attempted and the reason why it failed.

It's all untested, i don't have time to setup a test database and created a B4A project etc.
So if any of you want to give it a test and post your results.

And as time allows i'll update the script to implement the insert code.

Martin.

Read/Write from/to a database on your harddrive

IamBot

Member

Erel

B4X founder

IamBot

Member

warwound

Expert

IamBot

Member

warwound

Expert

Attachments

cirollo

Active Member

warwound

Expert

cirollo

Active Member

peacemaker

Expert

warwound

Expert

cirollo

Active Member

warwound

Expert

Attachments

Read/Write from/to a database on your harddrive

Member

B4X founder

Member

Expert

Member

Expert

Attachments

Active Member

Expert

Active Member

Expert

Expert

Active Member

Expert

Attachments

Privacy & Transparency

Privacy & Transparency