B4J Question Connecting to database server

[JackieBlue]

Good evening. I'm very new to B4x and just doing a little bit of exploring and testing for now. One of the most common things we do in programming is connecting to a database and inserting/updating/selecting data. I did some searching here and briefly reviewed. This is what I see is recommended for using databases. Please let me know if I am incorrect.

1. Setup a B4J server on your database server.
2. Create your SQL in the config.properties of the B4J server you created
3. Add globals to Main module, add CreateRequest, CreateCommand subs.
4. Use these to create functions that execute sql

Questions:
1. So, it looks as if any SQL you want to execute must essentially be in the config.properties of the B4J server, correct?
2. Same for stored procedures, you just define in the confic.properties of the B4J server?
3. Why is all this extra work needed? Most programming languages I've used allowed me to connect to the database and call directly. Not go through the server and and configure additional commands.

I can see where this could be more secure (I'm not so sure about that honestly) but it seems like this is just a lot of extra work for minimal benefit. I hope I am missing something here.

Thanks In Advance

 

[Erel]

This is what I see is recommended for using databases

Not exactly true.

You are talking about jRDC2 which is a middleware server between the clients and the database. jRDC2 is the recommended way with mobile apps. With a desktop app both options are good: connect directly to the database (jSQL) or use jRDC2 as a middleware.

If you choose to use jSQL then make sure to use the (very simple) async methods: [B4X] SQL with Wait For

 

[BillMeyer]

Good Day and Welcome

You have 3x options
1. You can connect via a php script somewhere (done at your own risk - not secure)
2. You can connect direct to the SQL Server (again - risky)
3. You can use jRDC2 as you describe above (secure - worth the extra bit of work - it is also much quicker than points 1 and 2 above)

My scenario
One of my apps is medically inclined and I need to read and write confidential information to the database. At the risk of an MITM or SQL Injection attack, I could loose my clients and business if I am not careful and can prove that I have tried every method there is to keep the data safe (local SA law).

If you use the php method or the direct method, you actually send your SQL query where it can be intercepted, modified and who knows what. If you use jRDC2 then you do not send the query as it is resident in the config.properties file on the server and you essentially only send a "code" to the server that looks up the query in the file and then executes it and return the data to you, and believe me, it might sound like a rigmarole but it's safe and very fast.

The second tip. On my SQL server, I use procedures that I call from my app. In this way, should there be any changes to your query, you do it on the SQL server and leave your config.properties and jRDC2 server intact.

Now your questions:

1. So, it looks as if any SQL you want to execute must essentially be in the config.properties of the B4J server, correct?

Correct

2. Same for stored procedures, you just define in the confic.properties of the B4J server?

No Sir, you define them on your SQL server and you just call them from your config.properties and the jRDC2 server

Like this, for example

sql.nearest_marker=Call GetMarkerBoard(?,?,?,?)

3. Why is all this extra work needed? Most programming languages I've used allowed me to connect to the database and call directly. Not go through the server and and configure additional commands.

As explained above about speed and security

And as @Erel is quicker than me on the keyboard - his answer too is spot on !!

I trust this will help

 

[aeric]

If you choose to use jSQL

Check this tutorial

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

 

[JackieBlue]

Thanks everyone . Interesting. I've never used an extra layer in any application I've developed but I've only done web (mostly classic asp and php) and old school client/server desktop apps.

@BillMeyer - regarding stored procedures, yes, they are created on the server but you have to put the execute code in the config.properties which was my real question.

The extra work definitely provides security. I honestly do not see how creating a jRDC2 would quicker.