Android Question CLOSED / SOLVED JdbcSQL - A little help please

[Robert Valentino]

My first time at trying to connect to a MySQL database and I am trying this example: https://www.b4x.com/android/forum/threads/jdbcsql-directly-connect-to-remote-databases.84016/

And I keep getting No suitable driver

I've tried using settings
Private driver As String = "net.sourceforge.jtds.jdbc.Driver" '"com.mysql.jdbc.Driver"

and still same thing. Boo hoo, why is this so hard.

 

[udg]

An EU-based provider for a cheap VPS could be:

Aruba Cloud VPS | ArubaCloud.com

www.arubacloud.com

I signed for the small config when it cost just 1€/month.. btw, the one showed are the worst fees you may incur in if you let the machine active 24h/day all the month long. Turning it off when not in use will reduce the monthly fee accordingly.

Anyway, try to search for "cheap VPS" and you should find good alternatives in the U.S. too.

 

[Robert Valentino]

Bladimir Silva Toro

I tried the example you posted. Thought it was the same as what I was using (but it's not. just same name) It works fine.
Thanks I can work with this.

 

[Robert Valentino]

Well going to close this out.

Have it working just fine. On my crappie satellite internet takes almost 8 seconds for a complex search (just testing) that returns 3 records.

FindUserRequest = "SELECT Users.*, GPA_Payload.* FROM Users INNER JOIN GPA_Payload ON (Users.RecordID = GPA_Payload.UserID) WHERE (Users.Name = 'Robert Valentino')"

On my Phone (with internet turned off so using cell tower) is pretty much just happens.

 

[Robert Valentino]

One final note.

I did just realize that there is no way to automatically backing up and restoring database (I could manually export and import - but what happens if I am not around)
So this (RemoteMySQL) may be a good temporary fix I don't see it working for the long run.

I've started looking at other hosting sites. Some of them offer Remote MySQL in there cheaper packages (no daily backups most of the time) - probably time for me to move from FatCow.

AGAIN - Thanks for all the help

 

[OliverA]

1) You could try AWS services free for one year
2) A quick VPS type machine may be AWS Lightsail. It starts off at $3.50 per month for the smallest instance.

 

[Shelby]

Thanks, all of you, mostly Robert and Bladimir, for the discussion. I've been preparing for my next app using a database. I've been studying MySQL with the MySQL Workbench. Do you guys have any experience with it? I've primarily learned to manipulate the database in various ways. I have little or no ability to set up the connection apparatus and I'm happy to see your talk as I'm trying to glean some understanding of how to get started with my app. Next, I'll check the code project by Bladimir above.
Shelby
swayzeconstruction.com

 

[Albert Kallal]

I just going to add a few things.
First, why so few hosting allow external direct connections to a database? Well, for one, opening up a database server to DIRECT connections means that you are by-passing ALL of the web secuirty systems. Years ago. (in fact 2004), I had just installed SQL express (in fact it was the free version before that called MSDE if I recall). Got it all working. Then I though, hey, would be not be cool if my desktop software could work any place and anytime.

So, I opened up the ports on my router. (1433 for SQL server). And now I could use my laptop and desktop software to connect to sql server any place.
However, I also took a look at the logs. and in less then 30 minutes? There were bots and people ALREADY trying to connect and logon to the SQL server!

The standard logon for SQL server is "sa" (short for system admin).
So, the logs looked like this:
User:sa, Pass: password
User:sa Pass:123456789
user:System: Pass:system

And so on!
I mean, you can connect to say amazom.com to buy a book or some such. And you can even subscript to their "web services" to get/grab/track say sales of your products. Now their hosted web site? Sure, it has 100% direct use of the database. However, outside of the web server? Well, there is absolute no way they would simply open up the ports on the SQL server. It is a very high risk idea.
For one, none of the built in and VERY battle hardened web server and services are being used. You in effect would by-pass all those web layers and security.

So a simple direct connect and logon to the database server tends to no be allowed. And like the old movie "war games"? Well, in the old days, people would enter a long list of phone numbers into a text file, and then have the modem dial out. If no answer (IE: a modem on the other end), then it would move on and try the next number. So, after dialing 1000's of phone numbers, a new text file list of "connects" would be built up.

Then you pass that text file onto a program that dials up and tries to connect.

and the same now on a MUCH more massive scale exists for the internet. But you don't dial up phone numbers, but scan for open database server ports. These scanners then log the ping back from open database ports (be it mysql, SQL server, or Oracle). And once they find a valid port? Well now robotized password crackers and attempts will now spring into action.

So, the real question:
Do you really want to find a database that has open ports to the wild west internet?
Answer: no, you really don't want to risk this idea.

so, what does everyone do?

Well, first, a solid 100% ODBC type connection OVER the internet is not really reliable anyway. In other words, the concept of say desktop software + a LAN and some sql system? Well, you on a local network, and it MUCH assumed from day one in terms of the software stack that the connection is solid, direct, and does not FREQUENT dis-connect. In other words, such direct connections to databases are pre-internet age in terms of design. It also means they often will blow up and fail if the connection is lost. ODBC drivers don't have auto-reconnect for example. To be fair, the newer SQL server drivers ARE after almost 20 years seeing a lot of new investment and things like re-connect are now being added (because Microsoft's ODBC standards are now being asked to connect OVER the internet to services like SQL running on Azure. So, ODBC technology is moving towards "web like" software in which a live 100% solid and good connection is NOT part of the design.

However, web services are kind of like say emails. They are designed from day 1 and the ground up to work in a "disconnected" world. So, bad wi-fi, and minor dis-connects and re-connects are part of the design, and the deck of cards being dealt to you. As a result, most Android systems to pull data and interact with a database in fact expect/assume/are designed around the web services model, and not a direct ODBC like connection.

So, what does this mean? Well, the whole idea behind the jdbc drivers? Well, what some enterprising people did here was build a software layer that works VERY similar to ODBC, but was web service based.

this explains the so called type 1, 2, 3 and 4 drivers for jdbc.
To make a long story short? Because near no one will direct open the ports on sql systems?
Well, then you can still use jdbc drivers, but in place of a direct connection (type 4 I believe), then what some people did was create a web service

So, what you do is install some code on the web server and have THAT software talk directly to the database (which of course the web site software can do - it has a direct connection to the database - just not the outside world).

So, if you want to say do a query? Well, you send that sql command to the web service. it then hands the sql off to the sql server, gets the data and THEN sends the data back as a web service call. Thus the data format is often json, or perhaps even xml.

And because most phone software OFTEN interacts with a web site? So, when you say run the amazon andriod app (as opposed to web browser), in fact that local application simply interacts with the web services that the amazon site has.

So, some enterprising people simply wrote a bunch of web services that quite much allow you to use jdbc as a "sort of" a driver, but it now talking to the web site in place of a direct connection to the database.

The down side of a web services approach?
Well, you need some additional software installed on the web server. (so if it is asp.net hosting, then what you install (or write) will have to be windows compatible software), or if Linux based, then you install software based on Linux. Now often both asp.net and Linux sites do support say the PHP programming language, and thus one of several such jdbc systems is written in PHP.

So the up-side? No opening of sql server is required.

You using web service calls, and thus the "web land" designs such as dis-connects and re-connects and flaky connections being par for the course allows the software to STILL work just fine. And not only say do you have a regular logon to the web site? Well often the developers will also ask/ensure/have some kind of key or other pin or other long GUID value that ALSO must be passed along.

If you don't pass that additional security information, then the web service will reject the web service call and not dish out data.

Compare that to a simple user + logon that most database servers require? Can you say now lets have 2 passwords? No, you can't change the design of the database server. You ONLY need user + password to connect.

Thus, I can say add web service, but also have the client side software REQUIRE a few extra data fields that are for security. I can do this in 2 minutes of code. I am about to do some web service stuff. I have standard logons for users to the web site, but for the web services and Android software? I will have one extra "token" key that the android phone will also have to supply in addition to a legal web logon.

So a direct connection to a database server is NOT battle hardened, does not have extra security abilities , and ALL it needs is a user + password ONCE you open up the ports on that server to the crazy and wild internet.

I recommend you do NOT do this. The exceptions are say customers working at home, and they VPN into work. Once they do this, and ok - a direct connection to the database server is permitted, but in ZERO cases are outside wide open port connections to the database allowed (a VPN gets you on that company network, so any printer or sql server for that matter is now usable).

.So if the web hosting does allow and have provisions for a VPN connection? Ok, then you can run some VPN software on the Android and a direct connect to the database will then work. (I do this right now). (running FortiClient on Android side).

So, you can either:
if you have web skills and can modify/write/maintain and add software or code to the web site? Then you can then add a few web service calls and methods to the site, and use those from Android side. This nicely gets around the security and we never have to open up the database server to outside connections.

Or you can install one of the web bridge packages or other approaches that is a standard set of web calls made for you. That web service will then take your requests and return the data.

So, I just wanted to outline why it seems "rather" difficult to find and simply connect to some database. It not easy, because in most cases we can't direct connect. And since we can't hardly ever direct connect to a database?
Then little software and examples exist of how to do this, since no one has much luck with this idea, and it too high risk.

Regards,
Albert D. Kallal
Edmonton, Alberta Canada

 

[Robert Valentino]

Thanks for the feedback.

But I've already moved on. I realize after my last post that If I could get Cloud Firestore working it is my best bet. I save very little data per user and this just seems to be a good way to go. I've spent the last two days trying to get DonManfred Cloud FirestoreEx example to work, not easy stuff but I feel it will be worth it in the end.

 

[Albert Kallal]

Thanks for the feedback.

But I've already moved on. I realize after my last post that If I could get Cloud Firestore working it is my best bet. I save very little data per user and this just seems to be a good way to go. I've spent the last two days trying to get DonManfred Cloud FirestoreEx example to work, not easy stuff but I feel it will be worth it in the end.

No problem . As noted, most have trouble because the jdbc drivers are the ONE exception in which you don't (can't) include the sql instance in the server name. yet every other system (.net) etc., we are used to going SERVERNAME\SQLINSTANCE name. But with jdbc, you ONLY are to enter the server name without the SQL instance, and the instance is set elsewhere in the connection string. And as noted, when connecting to a windows network, you can't use the server name, but use the IP address. This of course is not the case with a http conneciton, but a direct network connection, then its IP address(s) all the way.

Anyway, this post hopefully will still help others. And my post seems a "wee bit" harsh, and that was not my intention - just limited for time these days!
Best of luck.
Regards,
Albert D. Kallal
Edmonton, Alberta Canada

 

[OliverA]

But with jdbc, you ONLY are to enter the server name without the SQL instance, and the instance is set elsewhere in the connection string.

Huh? With what driver? JTDS? Ok. But with the driver supplied by Microsoft it works as expected.
Edit: ugh. I’m in the Android forum. Microsoft driver won’t work. But then, you are trying to use direct SQL from Android. Android<> desktop environment and you should not judge JDBC by the lack of capabilities it has running on Android.

 

[Albert Kallal]

No, no intention to judge or say it bad.
We have a thread of 30 posts here now. I am trying to save world poverty and suffering. It is VERY sad that we have all these posts here, and yet the WHOLE point I was making?

In near 100% of the cases, you have SERVERNAME\SQLINSTANCE.

And even with the driver from Microsoft, you use above. (of course we replace server name with IP address).
And more so the Microsoft driver does not work.

However, in 99% of cases the #1 face plant problem is everyone types in the server instance for the connection as part of the server name. And I 100% understand why!

this is due to every other case when you connect to SQL server I can think of - from the Microsoft jtds driver, to odbc, to desktop .net, to asp.net and the list goes on an on?

You type in servername\SQLINSTANCE name.

However, as noted the Microsoft driver don't work, so everyone still tends to use the source forge one.

So, like always?
We don't' need a book 100+ pages long here. We don't need long drawn out articles either. The KEY concept is that this one huge large exception simply needs to be packed away in one's memory bank.
that ONE simple issue?
You don't include the sql instance name as part of the server name. the sql instance is specified separate from the server name in the connection string.
This is key knowledge the reading public needs here. So, not being critical at all - just doing my best to note this elephant in the room.

Take a look at this recent post:
Android Question - Please help with example code... | B4X Rapid Application Development & Programming Tools

And in that post, I shared a "test" connection utility I wrote!.
Note what the poster did:
They shared a screen shot (the utility I wrote).
And guess what? Well, look at this:

See the problem? The users sql instance name is typed in with the server name!!! Again, what a sad waste of time and poor user experience this user encountered (that is sad to me!!!). In the above screen shot you can INSTANT see the users mistake! (and they guessed and typed in MSSQL for the server instance name - (wrong!!!). It is clearly COURTSENDB

Not being critical. Just another user (like in this thread) was asking why their connection to SQL server did not work.

So I am in no way dumping cold water on the jdbc driver. In fact I use this all the time with B4A, and it is a great choice.

Provided we don't have every 2nd or 3rd person trying to learn B4A, and walking away with a horrible and bad experience?

There is simply NO REASON for this endless parade of people having such a poor experiences in what should be simple and easy.

And one big reason is this SIMPLE advice I am giving here.

That advice is don't include the SQL instance as part of the server name. Once done, then you have multiple orders of a better chance that the connection will work.

you should not judge JDBC by the lack of capabilities it has running on Android.

If there is any statement, claim, hint, or any text I have here that suggests, hints, implies, advocates that I am somehow being judgmental or suggesting that this technology or the jdbc concepts are bad, or even lacking on Android? That certainly not my intention.

However, I will happy edit or change such text I have here or anywhere you can find if I have done so - not my intention.

I am in zero ways throwing cold water here or making some kind of judgment in the negative. Only try to reduce the suffering here!

In fact, I think the whole technology stack is rather amazing, and it quite easy and works rather well on Android + B4A.

I think we all suggest the web service based approach is better. However a good number of VERY handy scenarios in which a direct connect is not only easy, less work, but does not require any interaction with a web server, or to even have one, or to set one up!.

And in the last 1/2 dozen posts on connecting to sql server on B4A forms? They all been developers from VB6, .net etc., and simply expected the same concepts to work on Android. And in fact with direct connect, this does work. And in fact that's why i shared that simple little bit of code and that above form. The user can enter in the settings - touch connect. If it don't work, then they don't have to bother writing code or checking THEIR code!

Anyway, if there is any text here that seems to be critical of the jdbc drivers on Android? Please point it out - I will edit and change that text.

Regards,
Albert D. Kallal
Edmonton, Alberta Canada

 

[Shelby]

Thank you,
In my mind, you have come a long way toward saving world poverty and maybe suffering.
S