Database connection lost?

benoestrada · Post by **benoestrada** » Wed Apr 02, 2008 2:46 am

Hi,

I did a MySQL database front end with RevStudio 2.8.1. It is a simplistic project to retrieve/edit/save/delete database records.

I am connecting to the database manually using revOpenDataBase() and all seems to work nice.

I can navigate in my database records, but if I try to save a edited record "revdberr, invalid, cursor id" appears in my front end fields.

It seems I'm losing the database connection.

Why do I think this?

Because if I connect to a copy of the same database in my LAN (I have a computer running MySQL) my program runs perfect, the problems pops when I connect to the database via the Internet.

I have a:

Code: Select all

put "SET wait_timeout=600" into VarSQL
revExecuteSQL MyDB_ID, VarSQL

I would like to hear recommendations from the RR experts and gurus.

Any idea will be welcome.

Thanks for your time.

Benito

Janschenkel · Post by **Janschenkel** » Wed Apr 02, 2008 6:02 am

Apsuuming the two versions are the same, does the SET command also work locally?
Is the SET command the only one that fails, or do INSERT and UPDATE render the same error?

Personally, I'd never connect directly to a database over the internet, but that's a topic for another discussion...

Jan Schenkel.

benoestrada · Post by **benoestrada** » Wed Apr 02, 2008 7:39 am

Hi Jan,

Both versions are the same; one stored in a webserver and one the other my LAN.

The SET command:

- In my LAN works Ok, and the program works Ok if I do NOT use it too

- In the webserver; I have to use it if I want to call any database command. If I do not send it to the webserver then all database commands fails.

About INSERT and UPDATE:

- In my LAN both works nice and at all moments. No matter my program is running 10 or 20 minutes.

- Using the webserver database works nice only for the first minutes. If the program has more than 5 minutes working then these does not works.

--------------------

Personally, I'd never connect directly to a database over the internet, but that's a topic for another discussion...

Good point. I would like to hear more about this. Maybe it is because I'm new to RunRev that I have ideas like this. Mmmhh, but I have not found any text/documentation that does not recommend connecting to remote databases.

My project is supposed to be used by 2 to 10 people, they will be adding and updating records to the database. This is why I thought it would be a good idea to put the database in the webserver.

Thanks for your time,

Benito

Mark · Post by **Mark** » Wed Apr 02, 2008 8:50 am

Hi Benito,

It should be possible to set the wait_timeout of the webserver. I did this myself and had no problems.

For a start, try setting the timeout to 1200 instead of 600.

After setting the timeout, get it back from the server again. What do you get?

Have you set the database permissions to allow users to change system variables? You need to do this with your on-line database administration tool, you can't do this with MySQL commands if you have no permissions.

Best,

Mark

Janschenkel · Post by **Janschenkel** » Wed Apr 02, 2008 11:57 am

Mark has a good point: what is the difference in configuration and user permissions between the database on the webserver and your local server?

As for directly connecting apps to databases over the internet: that is indeed documented neither as a "here's how to do it" or "please don't ever do that".
There are multiple reasons:
- security issues - less open ports mean less ways for hackers to exploit known or unknown problems in the database engine.
- software updates - if you change something in your app, you need to update all the clients at the same time.
- application scalibility - every open connection eats resources on the server, so pooling on the server will definitely help

You may want to look into using Revolution as a cgi engine, and use the HTTP protocol to issue POST commands to scripts residing on the webserver. While this has its own set of gotchas, it should be fine for anything up to a few dozen users.

Jan Schenkel.

benoestrada · Post by **benoestrada** » Wed Apr 02, 2008 2:57 pm

Hi,

Mark:

For a start, try setting the timeout to 1200 instead of 600.

After setting the timeout, get it back from the server again. What do you get?

Get it back?, good point, until now I just found how to get this variable value is using the shell (from MySQL docs). I have no shell access in my webserver.

Do you know another approach?

Have you set the database permissions to allow users to change system variables? You need to do this with your on-line database administration tool, you can't do this with MySQL commands if you have no permissions.

In both cases the database user has all privileges (there is a checkbox for this).

Jan,

As for directly connecting apps to databases over the internet: that is indeed documented neither as a "here's how to do it" or "please don't ever do that".

Mmmhh, RR website and docs states that this is a perfect tool to create databases front ends. As said, this project goal is quite simplistic: add and update database records.

I thought that using RunRevs elements: input fields, comboboxes, etc I would be able to create a nice looking and powerful database front end for my customer.

Thanks for your time,

Benito

Janschenkel · Post by **Janschenkel** » Wed Apr 02, 2008 6:14 pm

Mmmhh, RR website and docs states that this is a perfect tool to create databases front ends. As said, this project goal is quite simplistic: add and update database records.

I thought that using RunRevs elements: input fields, comboboxes, etc I would be able to create a nice looking and powerful database front end for my customer.

Oh, don't get me wrong - Revolution is remarkably easy as a database front-end tool. The automated queries are a perfect example of how you can quickly put something together without having to learn Java or .NET and arcane binding frameworks.

But where latency and security are less of an issue in a local network, other items come into play when you try to extend that to the worldwide open internet. That's the point I was trying to make: it's not because something works and is relatively easy to do, that it's automatically a best practice

I've solved this sort of problem in multiple ways: Revolution cgi-engine, a Revolution standalone as a custom HTTP/SOAP-based application server - and in all cases was able to setup powerful solutions in no time when compared to the 'big' tools and technologies.

But then again, I just happen to be a firm believer in SOA and ESB.

Jan Schenkel.

benoestrada · Post by **benoestrada** » Sat Apr 05, 2008 3:51 am

Hi Jan,

Thanks a lot for your ideas on security.

My problem at this stage is not security, is as mentioned that the connection gets lost, the program response is real clumsy. In other words; it is useless.

Now I do not see what else to do, I'm using the database tools provided in Runtime Revolution. And as stated before, this application is real simplistic.

Ok I know, support will have to say something... I'm writting a message for them in this moment...

At this moment I'm real doubtful about RR potential as a database front end. Worst of all; my project is stuck and my customer is blaming on me.

If anyone here would like to test my application and see why I am complaining here send me a private message.

Regards,

Benito

Janschenkel · Post by **Janschenkel** » Sat Apr 05, 2008 6:51 am

Hi Benito,

I'm sorry to hear that you have doubts about Revolution as a database front-end builder. As you've explained, the problem you're facing is in connection timeouts.

The only thing we can try and give you at that point is a workaround, and in my case, also an explanation of why I wouldn't conect to the database directly in the first place.

If you can factor out the database calls into a separate library stack, you can reuse these from cgi-scripts. Then it's a matter of rewriting the calls to this library on the client side, into a HTTP get/post driven approach.

Yes, that is a bit of work, but it will help separate data acces from business logic from presentation. Which is a good practice. Again, it is not because something looks easy enough and seems to work locally, that it's going to work under all circumstances.

Architecting the solution to be as resilient as possible, is a delicate task.

Jan Schenkel.

benoestrada · Post by **benoestrada** » Sat Apr 05, 2008 7:28 am

Hi Jan,

The only thing we can try and give you at that point is a workaround, and in my case, also an explanation of why I wouldn't conect to the database directly in the first place.

I think these thoughts and advice should be posted as a warning to other users like me, in a visible place. I feel fooled if I got caught from advertising.

If you can factor out the database calls into a separate library stack, you can reuse these from cgi-scripts. Then it's a matter of rewriting the calls to this library on the client side, into a HTTP get/post driven approach.

I agree, now I see that interacting with remote databases deserves a different approach.

But if dealing with remote databases requires writing the cgi part first (server side), then this tool does not seem to be a good front end creator. You have to work twice; first the server side, next the client side. Phew!

Personally I do not have time to re-invent the wheel at this moment. Most webservers (especially *nix flavour) offers cgi tools.

I really appreciate your time and thoughts Jan. Do not get me wrong.

I do think that RR advertising database capabilities creates a false expectation. I got involved with RR for this database project. In order to be accurate it should state something like: "superb cgi development tool" or "if your project uses databases then think in the server side first".

And yes, a project like the one I'm facing requires a server side solution. It's time to move on... after wasting lots of hours learning/wrestling with RR.

If frustrating experiences are not allowed in the forum delete the whole thread.

Regards,

Benito

Janschenkel · Post by **Janschenkel** » Sat Apr 05, 2008 5:35 pm

Whether a tool is the right tool depends on what you are trying to accomplish. Revolution is a great tool to build database front-ends for both local single-user databases (Valentina, SQLite) and network multi-user databases (MySQL, PostgreSQL, Valentina, Oracle).

That does not automatically make it the best tool to build a front-end application that connects directly to a database hosted on a server that isn't even in the same LAN, but out there on the internet, somewhere on a server in your provider's data center.

Always research, always ask - don't assume all the advertised features work equally under all circumstances. The jump from local computing to networked computing already adds a host of possible interferences - and once you mix in the Internet, it can only get more complicated and less reliable as a result.

This is not just a Revolution limitation - I'm sure you would encounter similar problems if you built a similar client using Visual Basic.NET or Java (and I've used both in the past for local and network database front-ends) Don't blame the tool when the architecture fails.

Jan Schenkel.

benoestrada · Post by **benoestrada** » Sat Apr 05, 2008 6:08 pm

Don't blame the tool when the architecture fails.

Blame?

I'm aware there is nothing more to say about this.

I really appreciate your time and knowledge. But let's keep our minds open, speak frankly and do not confuse things.

Regards,

Benito