B4J Question connect to remote database using an API (SOLVED)

[Colin Evans]

Hi I haven't used B4J yet but I need to create a program that will connect to a database held on my web server, I have been in contact with the tech support and they state I must have an API to use the data held in the database.

The data is updated via a third party app , I know my server name being my web address, and I know the database name used by the app and user name and password

database server name: localhostL3306 or my web address
Database name: rmosfdov_
User name: colinsql
Password: ********

I've tried to find examples in the forum but I'm not having much look,

I assume I need to create an API which in turn will return the information in a JSON format

The table contains five fields

Username
telephone
date and time
email address
identifier

Any advice on where to start would be greatly appreciated

 

[aeric]

Here is an example

User Login + MySQL PHP API

Web API Updates: For B4J API Server and cross platform client apps, please check [B4J] MySQL API Server (Key + Token) and [B4X] User Login Apps https://www.b4x.com/android/forum/threads/b4j-mysql-api-server-key-token-and-b4x-user-login-apps.126081/ Android App This is the 3rd...

www.b4x.com

 

[Alexander Stolte]

I need to create an API

jServer.

will return the information in a JSON format

JSonGenerator and write the json string to the response.

it is easier than you think to write an API with B4J.

Private JSONGenerator As JSONGenerator
JSONGenerator.Initialize(CreateMap("id":1))
resp.Write(JSONGenerator.ToString)

 

[TILogistic]

I have been in contact with the tech support and they state I must have an API to use the data held in the database.

You should ask support personnel what language is available on the server.

With this you decide.

sample tutorials:

1.- If you want your APP to connect directly to your BD:

SQL Tutorial

B4J jSQL library is similar to Basic4android SQL library. This tutorial will cover the differences between the two libraries. You can read more about the library methods here: http://www.b4x.com/android/forum/threads/sql-tutorial.6736/#content jSQL library is not tied to any specific SQL...

www.b4x.com

2.- If you want your APP to request the data through web services or API

Connect Android to MySQL Database Tutorial

A new more powerful framework is now available: jRDC2. This tutorial explains the basic concepts required for creating a connection between Android device and a remote server. In this case it is a MySQL database. A tutorial for connecting to SQL Server is available here. Android cannot connect...

www.b4x.com

3.- If you want your APP to request data from a JRDC (Remote Database Connector) server

[B4X] jRDC2 - B4J implementation of RDC (Remote Database Connector)

RDC is a middleware server that makes it simple to safely connect clients and remote SQL database servers. jRDC2 is the latest version. All new projects should use this version. jRDC2 is made of two components: - B4J server. The server receives the requests from the clients, issues the SQL...

www.b4x.com

 

[Colin Evans]

Wow, thanks for all the advice, really appreciated, I'll have a look and see if I can work it out, thanks again

 

[Colin Evans]

Just goes to show what a novice I am, I've tried and played with loads of examples but I don't think I'm getting how it all works.

I've tried the jRDC example and edited the config file to;
#DATABASE CONFIGURATION
DriverClass=com.mysql.jdbc.Driver
JdbcUrl=jdbc:mysql://doginthelane.co.uk/DITLlogin?characterEncoding=utf8
User=colin
Password=*********
#Java server port
ServerPort=17178

And when it's run I get
<code>
Waiting for debugger to connect...
Program started.
2020-07-19 13:00:33.473:INFO::main: Logging initialized @546ms to org.eclipse.jetty.util.log.StdErrLog
Jul 19, 2020 1:00:33 PM com.mchange.v2.log.MLog
INFO: MLog clients using java 1.4+ standard logging.
Jul 19, 2020 1:00:33 PM com.mchange.v2.c3p0.C3P0Registry
INFO: Initializing c3p0-0.9.5.2 [built 08-December-2015 22:06:04 -0800; debug? true; trace: 10]
2020-07-19 13:00:34.057:INFOejs.Server:main: jetty-9.4.z-SNAPSHOT; built: 2018-05-03T15:56:21.710Z; git: daa59876e6f384329b122929e70a80934569428c; jvm 1.8.0_191-b12
2020-07-19 13:00:34.086:INFOejs.session:main: DefaultSessionIdManager workerName=node0
2020-07-19 13:00:34.086:INFOejs.session:main: No SessionScavenger set, using defaults
2020-07-19 13:00:34.088:INFOejs.session:main: node0 Scavenging every 600000ms
2020-07-19 13:00:34.092:INFOejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@26a7b76d{/,file:///D:/Downloads/jRDC2/jRDC/Objects/www,AVAILABLE}
2020-07-19 13:00:34.095:INFOejs.AbstractNCSARequestLog:main: Opened D:\Downloads\jRDC2\jRDC\Objects\logs\b4j-2020_07_19.request.log
2020-07-19 13:00:34.107:INFOejs.AbstractConnector:main: Started ServerConnector@eafc191{HTTP/1.1,[http/1.1]}{0.0.0.0:17178}
2020-07-19 13:00:34.108:INFOejs.Server:main: Started @1183ms
Emulated network latency: 100ms
jRDC is running (version = 2.22)
</code>

The table I wish to extract the data from is called 'login'
If the server / api is working how do I extract the information from the table 'login' that contains five fields

Username
telephone
date and time
email address
identifier

And display the information in a table, I'm sure it's possible but I haven't got a clue how to, help!

 

[DonManfred]

help!

Help for what? Everything is explained in the jRDC2 Tutorial incl. Server and Clientsetup.

 

[aeric]

sql.select_login=SELECT Username, telephone, date_and_time, email_address, identifier FROM login WHERE id = ?

 

[Colin Evans]

Thanks aeric, and donmanfred whilst everything may be explained in the tutorial, as I said I am a novice but I do like to keep my brain active programming with B4 but unfortunately some of the tutorials are way over my head, maybe there should be a complete novice forum so it doesn't frustrate experienced coders

 

[aeric]

Maybe you can start with PHP then move to jRDC when you are more comfortable. There are a lot of PHP tutorials in this forum.

 

[TILogistic]

If you have php installed on your server

and you want to do something similar to JRDC with PHP.

I recommend using an PHP API Frameworks for REST APIs.

It will be easier for you to learn.

search google you will find many frameworks and tutorials

Regards,

do not invent the wheel, if it is already created, improve it, adapt it and use it.

 

[Colin Evans]

Thank you, I'll have a look at PHP API's, thanks again for everyone's input

 

[Colin Evans]

Hi, I'm back again

Having had a look at the PHP examples, I thought I had sorted it using one of the examples posted.

I'm confident I am connecting but I'm not getting anything back, nor have the ability to add to the database

I attach a zip file of the amended program, could anyone spot where I'm going wrong, really appreciate any help offered

 

[aeric]

Hi, I'm back again

Having had a look at the PHP examples, I thought I had sorted it using one of the examples posted.

I'm confident I am connecting but I'm not getting anything back, nor have the ability to add to the database

I attach a zip file of the amended program, could anyone spot where I'm going wrong, really appreciate any help offered

Your PHP returns empty result on insert new data. Can you post your PHP files?

 

[Colin Evans]

renamed it to have a .txt extension hope that helps

 

[aeric]

PHP variable is case-sensitive. Change the parameter "name" to "Name" and so on.

    Case "InsertPerson":
        $pname=stripslashes(mysqli_real_escape_string($con,$jsone["Name"]));
        $pphone=stripslashes(mysqli_real_escape_string($con,$jsone["Phone"]));
        $pparty=stripslashes(mysqli_real_escape_string($con,$jsone["Party"]));

In B4J, also change the key names from "pName" to "pname" and so on (if columns in MySQL are all lowercase).

Sub ShowPersons
    Dim PersonsList As List
    PersonsList.Initialize
    Wait For(DBService.GetPersons) Complete (Result As List)
    PersonsList=Result
    
    PersonsTV.SetColumns(Array As String("PID", "NAME", "PHONE", "PARTY", "EMAIL"))
    PersonsTV.SetColumnWidth(0,100)
    PersonsTV.SetColumnWidth(1,200)
    PersonsTV.SetColumnWidth(1,300)
    PersonsTV.SetColumnWidth(1,300)
    PersonsTV.SetColumnwidth(1,300)
    
    PersonsTV.Items.Clear
    
    For i=0 To PersonsList.Size-1
        Dim Row(5) As Object
        Dim m As Map
        m=PersonsList.Get(i)
        Row(0)=m.Get("pid")
        Row(1)=m.Get("pname")
        Row(2)=m.Get("pphone")
        Row(3)=m.Get("pparty")
        Row(4)=m.Get("pemail")
        PersonsTV.Items.Add(Row)
    Next
End Sub

 

[Colin Evans]

HI Aeric, thanks again for your help, I've made the changes you said but I'm still not getting anything returned from the connection in the 'GetAllPersons' nor can I add a record, I've added images of the database structure don't know if that will help, I got told by the administrator's of the site to create a subdomain and put the PHP file in there and then use localhost as the host, I don't know if I'm connecting properly or not

 

[aeric]

In your PHP, you are using table name "persons". You can either change the PHP script from "persons" to "login" or rename the database table from "login" to "persons".

    Case "InsertPerson":
        $pname=stripslashes(mysqli_real_escape_string($con,$jsone["Name"]));
        $pphone=stripslashes(mysqli_real_escape_string($con,$jsone["Phone"]));
        $pparty=stripslashes(mysqli_real_escape_string($con,$jsone["Party"]));
        
        $stmt = $con->prepare("INSERT INTO persons (pname, pphone, pparty) VALUES (?, ?, ?)");
        $rc=$stmt->bind_param("sss", $pname, $pphone, $pparty);
        $rc=$stmt->execute();
        $iid=$stmt->insert_id;
        if ($iid == 0)
        {
            exit (json_encode(array(array('InsertPerson' => 'failed'))));
        }
        else
        {
            exit (json_encode(array(array('InsertPerson' => 'ok', 'pid' =>  $iid))));
        }
        break;

 

[Colin Evans]

Hi, sorry I did notice that before and amended it after I'd sent you the php file, I changed all the persons to login but whilst it appears to run okay I don't get anything populated in the table and I know there are 18 records in the table
<code>
<?php

$host = "localhost:3306";
$user = "colin";
$pw = "CmA112209";
$db = "DITLLogin";


$con = mysqli_connect($host,$user,$pw) or die(mysqli_error());
mysqli_select_db($con,$db) or die(mysqli_error());
mysqli_query($con,"SET CHARACTER SET utf8");
mysqli_query($con,"SET NAMES 'utf8'");


$json = file_get_contents("php://input");
$jsall = array();
$jsone = array();
$jsall=json_decode($json, true);
$jsone=$jsall[0];

$action = $jsone["Action"];

switch ($action)
{

Case "InsertPerson":
$pname=stripslashes(mysqli_real_escape_string($con,$jsone["Name"]));
$pphone=stripslashes(mysqli_real_escape_string($con,$jsone["Phone"]));
$pparty=stripslashes(mysqli_real_escape_string($con,$jsone["Party"]));

$stmt = $con->prepare("INSERT INTO login (pname, pphone, pparty) VALUES (?, ?, ?)");
$rc=$stmt->bind_param("sss", $pname, $pphone, $pparty);
$rc=$stmt->execute();
$iid=$stmt->insert_id;
if ($iid == 0)
{
exit (json_encode(array(array('InsertPerson' => 'failed'))));
}
else
{
exit (json_encode(array(array('InsertPerson' => 'ok', 'pid' => $iid))));
}

break;

Case "DeletePerson":
$pid=stripslashes(mysqli_real_escape_string($con,$jsone["pid"]));

$stmt = $con->prepare("DELETE FROM login WHERE pid = ?");
$rc=$stmt->bind_param("i", $pid);
$rc=$stmt->execute();
$ar=$stmt->affected_rows;
if ($ar < 1)
{
exit (json_encode(array(array('DeletePerson' => 'deletenorow'))));
}
else
{
exit (json_encode(array(array('DeletePerson' => 'deleteok'))));
}

break;

Case "GetAllPersons":
$stmt = $con->prepare("SELECT ID, Name, Party, Phone, Email, _sfm_form_submission_time_ from login");
//$rc=$stmt->bind_param("s", $uname);
$rc=$stmt->execute();
$q = $stmt->get_result();
$count=mysqli_num_rows($q);
$stmt->close();

$rows = array();
while($r = mysqli_fetch_assoc($q))
{
$rows[] = $r;
}
//exit (json_encode(array(array('login' => 'ok', 'loginlist' => json_encode($rows)))));
exit (json_encode($rows));

break;

default:
print json_encode ("Error: Function not defined (" . $action . ")");
}
?>
</code>

I also changed the GetAllPersons to select specific fields but still no luck, presumably I need to change something in the J code, or do I, really appreciate you taking the time to help,thanks

I would be happy just to populate the table as the external program populates the database so all I need to do is show the records in a table, after that I'll work on anything else I need to do with the data

Cheers, Colin

 

[aeric]

$stmt = $con->prepare("INSERT INTO login (pname, pphone, pparty) VALUES (?, ?, ?)");

Your column names are not matching.
Change to following:

$stmt = $con->prepare("INSERT INTO login (Name, Phone, Party) VALUES (?, ?, ?)");