Android Example [B4XPages] jRDC2 + MySql CRUD + Login

Hi all:


I'm starting (at last) to work with B4XPages, so I've started to adapt the B4XPages + B4XDrawer example.
I think I’ve seen more examples with php + mysql, but no projects with Jrdc2.

Maybe the dummy data and the UI are too basic, but you can get some ideas.

What do we need?
- A mysql database. You can use XAMPP, LARAGON or any other project to set it up. See attached a sql file to import in your database. The database name will be B4X.
- B4J to create the jRDC2 server
- B4A 9.9+ to support B4XPages. As always, you should update B4A to the last versión (10.5 when this post have been write)

Steps:
- Import the sql file to you database. You can use phpMyAdmin, HeidiSQL, etc…
- Configure jRDC2 server. Open the attached B4J file and change your user and password in the config file.

Spoiler: Configuring jRDC2

[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

- Open B4A, and change the rdcLink const in the jRDC2 class to match the IP of the computer where you’re running the jRDC2 server (B4J)

Sub Class_Globals
    'CHANGE THE IP TO MATCH YOUR B4J SERVER
    Private const rdcLink As String = "http://192.168.1.131:8090/rdc"
End Sub

- To add, edit... we will use B4XPreferencesDialog. You should use the Form Builder to build the forms. It's important that the key values match with database's fields names, due to we will construct a map from the rs returned from the db, and it will be easier to work this way, passing this map to the B4XPreferences dialog.

I've commented the code the best I can.
If you think there are some mistakes or you know some way to improve the example, you're welcome to do it.
I've tried to make the B4J project too, but I've never used B4J and I get too many errors. If someone want to do it, we will learn something more. (or B4i)
EDIT: Updated project and now it works with B4J and B4A (maybe in B4i)

The password for all the users is: 1234 (stored as md5)

Spoiler: SQL sentences

sql.Login = SELECT * FROM B4X.users WHERE `username` = ? AND `password` = md5(?)
sql.getEvents = SELECT * FROM B4X.events WHERE `month` = ? AND `id_user` = ?
sql.updateEvents = UPDATE B4X.events SET `month`=?,`event_type`=?,`description`=? WHERE `id` = ?
sql.deleteEvents = DELETE FROM B4X.events WHERE `id` = ?
sql.addEvents = INSERT INTO B4X.events(`id_user`, `month`, `event_type`, `description`, `value`) VALUES (?, ?, ?, ?, ?)

 

[Timm]

I eventually put the localhost ip, but still can not connect /192.168.43.86:8090.
My real question is how do I run the server and the client to communicate together.
Do I need to put the client and server folder in the same wamp/www/ directory. Am using wampserver for mylocalserver. Normally from my php background, I put all this files: server file and client file in ../www/ folder

 

[josejad]

Please, refer to the jRDC2 post.
Usually, during test and debug phase, you run the server in the PC just pressing F5 in B4J, then the server start running on your PC, and you have to configure your client with your PC ip (something like 192.168.1.x or similar).
Then, when everything is working, you get the .jar (java program) generated from B4J and upload it to your server (an internet one?), then run it. (and, of course, you need to change in your client the ip to connect)

Where will you run your server? in a VPS (virtual private server) with linux? In your home PC?
See this related posts

[server] Run a Server on a VPS

The steps required to run a B4J server on a hosted server (such as a VPS) are: 1. Download Java: https://www.b4x.com/android/forum/threads/b4jpackager11-the-simplest-way-to-distribute-ui-apps.99835/#content If you are not familiar with Linux then download it to your development computer, unzip...

www.b4x.com

Running jars under OpenJDK 11

With Java 8 you could run a jar on a Windows PC by double clicking on it. This does not work with OpenJDK so as I want to be able to quickly run some utility B4J programs easily without packaging them this is what I did. 1) I installed OpenJDK in a convenient place, mine is in 'C:\jdk-11', and...

www.b4x.com

 

[aeric]

In B4A,

change the rdcLink const

Private const rdcLink As String = "http://192.168.1.131:17178/rdc"

Provided that you server IP is 192.168.1.131 and you set the serverPort to 17178 in config.properties file

ServerPort=17178

 

[aeric]

Do I need to put the client and server folder in the same wamp/www/ directory

No.
You do not need a webserver for jRDC2. jRDC2 is a web server itself.
You only need a wamp server to host your MySQL database. If you use other databases, you don't even need a wamp server.

 

[Timm]

Thank you for your great work. I want to use your code to start something in b4x. Hence I want to use this jrdc server connection, but the client your code not connecting to the server.
I ran the client ( above client code) code, was running, I input the logins: User/1234 but what are got are these:
problem connecting: Java.net.ConnectExeception: Failed to connect to /192.168.43.86.17178.
Please am sorry for my disturbance. what can I do to make it work?. When I ran the server, there was successful connection but can't get connection between client and server.

 

[OliverA]

Java.net.ConnectExeception: Failed to connect to /192.168.43.86.17178.

Did you accidentally use a period (.) instead of a colon ( : ) between the 86 and the 17178 when you created the rdcLink String?

Private const rdcLink As String = "http://192.168.43.86.17178/rdc" ' Incorrect: the period (.) between 86 and 17178 needs to be a colon (:)
Private const rdcLink As String = "http://192.168.43.86:17178/rdc" ' Corrected rdcLink String

 

[Timm]

Am just afraid to send something because of I may be disturbing you.
The real trouble is not

Private const rdcLink As String = "http://192.168.43.86.17178/rdc" ' Incorrect: the period (.) between 86 and 17178 needs to be a colon )
Private const rdcLink As String = "http://192.168.43.86:17178/rdc" ' Corrected rdcLink String.

Previously, I sent a typo error to you by that period instead of colon. I actually typed it right on the jrdc2 code like
192.168.43.86:17178 but still couldn't connect and I have done this several time, to no avail. Not connecting
Please from your experience what are the factors causing no connection. Don't forget the server connection was successful but just to connect your code(client) with the server is where the trouble is. Thanks eagerly await solution.

 

[aeric]

Do the Android phone and server connected to the same WiFi network?

 

[josejad]

Am just afraid to send something because of I may be disturbing you.

Don't be afraid, as you can see, we are here to help you, but please, make it more easy for us providing the more info, the better.

- Where are you hosting the mysql server? In the same PC where you're running B4J?
- What is the IP of the PC running B4J? (jrdc2 server)
- What is the IP of your client? (B4A? an Android phone?) As Aeric says, they must be on the same network (while testing)
- Can you post your "config.properties" file?

In the next picture, we're running the mysql server and the jRDC2 server on the same computer. Therefore, in the config.properties we use "localhost" (it means... the same computer in which you're running)

JdbcUrl=jdbc:mysql://localhost:3306?characterEncoding=utf8&useSSL=false

Now, the B4A app needs to access your server PC, so you have tom set:

'CHANGE THE IP TO MATCH YOUR B4J SERVER
    Private const rdcLink As String = "http://192.168.0.1:8090/rdc" '8090 it's the port we have set in config.properties, it can be another one

 

[Timm]

Good afternoon. Attached below is my config file and response within wifi connectio

 

[Timm]

Below is the config file

 

[Timm]

#Lines starting with '#' are comments.
#Backslash character at the end of line means that the command continues in the next line.

#DATABASE CONFIGURATION
DriverClass=com.mysql.cj.jdbc.Driver
# JdbcUrl=jdbc:mysql://localhost/test?characterEncoding=utf8
#JdbcUrl=jdbc:mysql://localhost/db_store?characterEncoding=utf8
JdbcUrl=jdbc:mysql://192.168.43.86:3306/b4x?characterEncoding=utf8&useSSL=false&serverTimezone=CST6CDT
#JdbcUrl=jdbc:mysql://localhost:3306/b4x?characterEncoding=utf8&useSSL=false&serverTimezone=CST6CDT
User=root
Password=
#Java server port
ServerPort=17178
#ServerPort=8090

#example of MS SQL Server configuration:
#DriverClass=net.sourceforge.jtds.jdbc.Driver
#JdbcUrl=jdbc:jtds:sqlserver://<server address>/<database>

#example of postegres configuration:
#JdbcUrl=jdbcostgresql://localhost/test
#DriverClass=org.postgresql.Driver

#SQL COMMANDS
#sql.create_table=CREATE TABLE IF NOT EXISTS animals (\
# id INTEGER PRIMARY KEY AUTO_INCREMENT,\
# name CHAR(30) NOT NULL,\
# image BLOB)
#sql.insert_animal=INSERT INTO animals VALUES (null, ?,?)
#sql.select_animal=SELECT name, image, id FROM animals WHERE id = ?;
#sql.create_table=CREATE TABLE article (col1 numeric(10,4) ,col2 text);
# sql.select=select * from article
#sql.select=select * from table_store
#sql.insert=INSERT INTO article VALUES(?, ?)

#SAMPLE
sql.Login = SELECT * FROM B4X.users WHERE `username` = ? AND `password` = md5(?)
sql.getEvents = SELECT * FROM B4X.events WHERE `month` = ? AND `id_user` = ?
sql.updateEvents = UPDATE B4X.events SET `month`=?,`event_type`=?,`description`=?, `value` = ? WHERE `id` = ?
sql.deleteEvents = DELETE FROM B4X.events WHERE `id` = ?
sql.addEvents = INSERT INTO B4X.events(`id_user`, `month`, `event_type`, `description`, `value`) VALUES (?, ?, ?, ?, ?)

 

[Timm]

I just can't reach the server with my b4a or b4j client, whereas server connection is successful

 

[Timm]

Or is there any special permission to be granted before 6y2w

 

[josejad]

Sorry, replying from my phone right now.
Check you don't have a firewall running on your server pc

 

[Timm]

No I have configured my window to get the same pages both on desktop and phone, am not sure it is firewall issue. Please did you check my config file and the error. Thanks

 

[Timm]

Good day. I have been on this jrdc for many days looking for it to work and eventually wow!!!!! I got it working thank you and thank you for your support.

But having little challenge username or password not correct. I used 1234 for the password, what do I use for user. Thanks

 

[Timm]

Thanks once again, I got everything working. I have access to the b4x backend.
One thing I will plead, you help me with is; how do I use b4xtable to access the backend? could you adapt your code to use b4xtable. Thanks

 

[josejad]

Hi @Timm , glad you did it¡¡

I will try to update the example using B4XTable, but I have very few time right now for coding...
In the meantime, you can try this example:

[B4X] B4XTable - Load data from SQL database

B4XTable.SetData expects a List where each entry in the list is an array of objects with the data of a single row. The data can come from any source you like. Example of loading the data from a SQL database: B4XTable1.AddColumn("Customer Id", B4XTable1.COLUMN_TYPE_NUMBERS)...

www.b4x.com

 

[Timm]

Thank you for your response and good great work. I will do some research on my own.