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

Here is an example

Colin Evans said:

I need to create an API

Click to expand...

jServer.

Colin Evans said:

will return the information in a JSON format

Click to expand...

JSonGenerator and write the json string to the response.

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

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.

Click to expand...

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:


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


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

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

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!

Colin Evans said:

help!

Click to expand...

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

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

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.

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.

Click to expand...

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

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

Colin Evans said:

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

Click to expand...

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

renamed it to have a .txt extension hope that helps

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

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

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

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".

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

Colin Evans said:

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

Click to expand...

Your column names are not matching.
Change to following: