Query texts stored in database or a WEB Service for DB hits?

vfafou · May 20, 2020

Hello!

I'm starting a fresh application from scratch and I would like to ask you for your opinions.
The application will hit a MariaDB database.
I will write a WebSocket server for mobile devices connections and also for workstations via a desktop app.
The mobile devices will hit the web server via events and they will get back JSON or Maps.
I'm in a dilemma about the way to connect the desktop app to the database.
I could use the web server in the same way as mobile but I'm thinking if the direct connection to the database would be better for desktop, because the workstations are in the same building as the DB server.
If I connect the desktop directly to the DB, how to implement the queries? Write them within the app or store them in a DB table as texts so I could change them without changing source code? This same question about queries implementation would be made also for the web server queries!

What do you think?

Thank you in advance!

aeric · May 21, 2020

Maybe you can try my sample:

(PHP/MySQL/API) User Login App

Web API Updates: Latest B4XPages user login client apps (using B4J server): https://www.b4x.com/android/forum/threads/project-template-user-login-client-b4x.161914/ Older project: https://www.b4x.com/android/forum/threads/b4j-mysql-api-server-key-token-and-b4x-user-login-apps.126081/...

www.b4x.com

It would work with MariaDB.

vfafou · May 21, 2020

aeric said:
Maybe you can try my sample:

(PHP/MySQL/API) User Login App

Web API Updates: Latest B4XPages user login client apps (using B4J server): https://www.b4x.com/android/forum/threads/project-template-user-login-client-b4x.161914/ Older project: https://www.b4x.com/android/forum/threads/b4j-mysql-api-server-key-token-and-b4x-user-login-apps.126081/...

www.b4x.com

It would work with MariaDB.

Hello @aeric!
Thank you for your response!
It's a very nice example of web service!
The purpose of my question is mainly to see how others implement the queries of an application. According to all examples, I/we write the queries into the apps and give their texts as values to variables. My thought is the following: if I store the query texts into a database table and fetch them as needed, I will avoid the source code changes if something in a query being changed and I will achieve any change without restarting the application!

aeric · May 21, 2020

You can fix the commands in client app. If you are connecting to server using okhttputils then you can call the url path. You do not submit the query between the client and server. If you store the queries in remote database, you have to query for the right query and then execute the result SQL. I am not sure this has any impact of performance and I think we seldom modify the SQL queries once it is in production.

TILogistic · May 21, 2020

You can do it in JRDC2.

Copy the "SELECT" queries from the config.properties file to the database.

Because every time JRDC2 has a request, it looks for the query ID "SELECT" in the Map (config.properties).

Now you need to modify where JRDC2 will search and execute the queries

Example:

config.properties to data base

ID =sql.insert_animal
TEXT= INSERT INTO animals VALUES (null, ?,?)

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.insert=INSERT INTO article VALUES(?, ?)

Modify GetCommand (Read Data base)

B4X:

Public Sub GetCommand(Key As String) As String
    If commands.ContainsKey("sql." & Key) = False Then
        Log("*** Command not found: " & Key)
    End If
    Return commands.Get("sql." & Key)
End Sub

See Execute query

B4X:

Private Sub ExecuteQuery2 (con As SQL, in As InputStream,  resp As ServletResponse) As String
    Dim ser As B4XSerializator
    Dim m As Map = ser.ConvertBytesToObject(Bit.InputStreamToBytes(in))
    Dim cmd As DBCommand = m.Get("command")
    Dim limit As Int = m.Get("limit")
    Dim rs As ResultSet = con.ExecQuery2(Main.rdcConnector1.GetCommand(cmd.Name), cmd.Parameters)

Note: Analyze these:

RDCConector and RDCHandler

If it's not what you're looking for, it will at least give you an idea

TILogistic · May 21, 2020

Note:

We modified the JRDC2, because it was our need, not to restart the JRDC2 server or edit the config.properties file, every time we edit, delete or add SQL.

separate database connections

separate query list (SQL)

Also add a user interface to make it more dynamic

vfafou · May 21, 2020

Thank you very much @oparra!
That is similar to what I was thinking to do!
I haven't used the jRDC2 before, so I have to see how it's working!

vfafou · May 22, 2020

Do you think it's a good idea to integrate jRDC2 within my web server, so I will have one jar for my server?

KMatle · May 22, 2020

RDC offers just db commands. In almost all the cases this is not enough like security, access protecting or calling other services (encryption, email, fcm, etc.). So I prefer php as it is available on any server.

inakigarm · May 22, 2020

vfafou said:
Do you think it's a good idea to integrate jRDC2 within my web server, so I will have one jar for my server?

I will prefer having it separately (I had two projects in these two layers and it worked fine); this way, the jRDC server modifications and Web modifications won't affect each other (think about adding new SQL commands and having to restart the whole webservice even if you're not modify the web server part, or configuring filters in web service, changing html code served by the server handlers, etc.)
If you think in security terms, the first layer access (webservice) if compromised, don't have access to the DB layer

vfafou · May 22, 2020

inakigarm said:
I will prefer having it separately (I had two projects in these two layers and it worked fine); this way, the jRDC server modifications and Web modifications won't affect each other (think about adding new SQL commands and having to restart the whole webservice even if you're not modify the web server part, or configuring filters in web service, changing html code served by the server handlers, etc.)
If you think in security terms, the first layer access (webservice) if compromised, don't have access to the DB layer

So you mean that it is more secure and flexible to have a layer that connects to database and does all SQL instead of the application and also instead of the WEB server and extending that, it could be used for sending queries from a mobile app?

TILogistic · May 22, 2020

My humble comment, all solutions or combinations are possible and it will depend on your project which one to implement, in addition to the resources you have (VPS, Windows Server, Hosting, VPN, etc.).

Note:
Take a look at this work which I find B4XSerializator interesting in PHP.

B4X with PHP Server B4XSerializator.

[B4X] PHP implementation of B4XSerializator and PhpCloudKVS Server example

Hello, First of all, sorry for my bad english... I'll try to do my best... For my project, I ported the source of B4XSerializator of @Erel from here to php. Even if php is considered as a weakly type language (before php 7), we can manage to work fully as expected with B4J and normally B4X (not...

www.b4x.com

inakigarm · May 22, 2020

vfafou said:
So you mean that it is more secure and flexible to have a layer that connects to database and does all SQL instead of the application and also instead of the WEB server and extending that, it could be used for sending queries from a mobile app?

You can google for Web application Architectures or similar:
https://www.synopsys.com/blogs/software-security/attributes-of-secure-web-application-architecture/
https://docs.microsoft.com/en-us/do...ps-azure/common-web-application-architectures

Query texts stored in database or a WEB Service for DB hits?

vfafou

Well-Known Member

aeric

Expert

(PHP/MySQL/API) User Login App

vfafou

Well-Known Member

(PHP/MySQL/API) User Login App

aeric

Expert

TILogistic

Expert

TILogistic

Expert

vfafou

Well-Known Member

vfafou

Well-Known Member

KMatle

Expert

inakigarm

Well-Known Member

vfafou

Well-Known Member

TILogistic

Expert

[B4X] PHP implementation of B4XSerializator and PhpCloudKVS Server example

inakigarm

Well-Known Member

Query texts stored in database or a WEB Service for DB hits?

Well-Known Member

Expert

Well-Known Member

Expert

Expert

Expert

Well-Known Member

Well-Known Member

Expert

Well-Known Member

Well-Known Member

Expert

Well-Known Member

Privacy & Transparency

Privacy & Transparency