Change DBC connection to MySQL

[Gerrit Broekhuis]

Hi,

I'm working on an older application from someone that uses a DBC (for storing remote views) with a MySQL database.

In the DBC the connection is hard coded (servername(or IP), username, databasename, password.

I want to change as little as possible in this application, as it's working fine. But I need to be able to use variables instead of the fixed connection parameters, as the servername, username, databasename and password could be different.

I can read most connection parameters with DBGETPROP (for example: DBGETPROP("module1", "CONNECTION", "database").
When trying to change anything with DBSETPROP I only get errors, like: ERROR 111 "Cannot update the cursor MODULE1, since it is read-only." Module 1 is the name of the DBC.

How should I make the DBC connection using my variables? Right now the original fixed parameters are still stored in the DBC connection. Can I simply overwrite these? How?

Regards, Gerrit

 

[Mike Lewis]

Hello Gerrit,

So, the first question is: Is it possible the DBC (or the accompanying DCT or DCX) is in fact read-only? That could happen if any of those files has their read-only attribute set (which you can check by looking at their file properties), or - more likely - that the DBC is bound into the executable?

Either of those cases would generate the error that you are seeing.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Gerrit Broekhuis]

Hi Mike,

The DBC is definitely not read-only.

Regards, Gerrit

P.S. I already changed one application from using RV's to SPT. If I cannot tweak the DBC's I still have to more applications to go...

 

[Steve Meyerson]

This is a wild, out-of-the-box idea which just occurred to me:

If you know what these hard-wired parameters are, treat them as memory variables instead. Intercept the current call to the MySQL database and use those "memvars" to somehow create an in-between call somehow converting those "memvars" to the parameters you need.

As I implied, it's a total long shot. I just know I'll be thanked or invited to a loony bin.

Steve

 

[Gerrit Broekhuis]

Hi Steve,

I already tried to use the connectionstring as used elsewhere in this application. I changed the connection parameters to use the connection string and I tried with the variable "lcConnectionString" and "&lcConnectionstring" in the connectionstring field, but still got the same error.

Regards, Gerrit

 

[Steve Meyerson]

Gerrit,

Thanks for the feedback.

As I said, it was a long shot.

Steve

 

[Chris Miller]

Connections are objects within a DBC and there can be more than one.

My guess when the DBC isn't embedded in the EXE, the connection may be in use. You would need to change it before use, even better in an administrative one time change, why do it in code?


Chriss

 

[Gerrit Broekhuis]

I’ve almost completed a complete rewrite for 2 applications. The DBC is no longer used, all remote views have gone. I’m now using cursors only, with rapid access to MySQL for read/write. Cursors are opened and immediately closed when lo longer used. I have no permanent connection with the MySQL database. All form sizes and positions are saved in a MySQL table.

This is how I build all other modules. Very fast and very reliable and stable.

Regards, Gerrit

 

[Chris Miller]

Gerrit, that is a solution,

but weren't you asking how to use the connection in the DBC of this project?
Well, you can, but a connection is based on either a DSN name or a connection string stored in the connection item of the DBC, there is no use of variables or parameters of the effective connection string.

So what you can do is as you plan to set the connection of the DBC. That can be done just in time, but it has to be done before it's used. Any views in the DBC are based on the connection name, so you can change the connection, but that has to be done before the first view is used.

You also can add another connection object, but for the views to use it this new connection has to have the name of the old one which means that has to be renamed.

Chriss

PS: Maybe to put all this into a better perspective.

You initially said:

I'm working on an older application from someone that uses a DBC (for storing remote views) with a MySQL database.

You now said:

I’ve almost completed a complete rewrite for 2 applications. The DBC is no longer used, all remote views have gone.

Do these 2 application include this one? Has the question become obsolete now? Or are you just saying now that you tried some recommendations you'll go about it as usual? Nothing wrong to base all your maintenance on your ways, but even though you already solved other applications your other way it seems to me you just want to get this connection to work in your environment to not need this complete rewrite. And while it's not possible to use variables in the connection objects within DBCs, you can use them for building the connection strings dynamically and set an existing DBC connection object to the result of your built string or create another connection object etc.

Why abondon the idea to get the connection under your control as necessary already so fast? Remote views are a good thing to have, redoing that in SPT is possible but also an effort not necessary in every case.

 

[Steve Meyerson]

Hi Gerrit,

I agree 100 percent with your (rewritten) methods as I use them all. Because it is simple, reliable, stable, etc. The stability of quick connects & disconnects happens to be especially important for me because of my primary user's unreliable wifi causing unwanted disconnects.

Steve

 

[Chris Miller]

One more thing to look at: If you already can't change the connection once you open the DBC, look for DBC events and what's done in the OpenData event or anything related to the connection in any dbc code, if there's nothing also in a file DBGETPROP("Thedatabse","Database","DBCEventFileName") might point to.



Chriss

 

[Gerrit Broekhuis]

Hi Chris,

I know how to use a DBC in general and mine has a fixed connection. I haven’t found a way to change this in code (leaving the DBC as is) and us e variables for the connection. I couldn’t get DBSETPROP to work.

I’ve sort of given up on this and will continue changing RV’s to cursors with SPT. I know very well how to do that and can get rid of the DBC’s this way. Speed and reliability will improve too.

Regards, Gerrit

 

[Chris Miller]

SPT and RVs via connection use the same thing: ODBC. I don't see a benefit.

There's no such thing as a fixed connection, I don't know what other advice to give, if there is no feedback to recommendations. But then it is what it is. Good luck with your rewrite.

Chriss

 

[Gerrit Broekhuis]

Hi Chris,

With “fixed connection” I mean that after each interaction with a MySQL database (select/update/insert/delete) the connection is closed. If something has to be done with the MySQL table, a new connection is created, only fot that “something”.

Perhaps I should have said “permanent” instead of “fixed”, but English is not my native language.

All my table operations are done with cursors, in other words: in memory. When handling small portions of data memory is all that’s needed, without disk interactions. Only with big (really big) cursors data will be buffered to disk.

Using a DBC for RV’s is definitely slower, even though this uses the same ODBC driver. I was very happy switching from DBC/DBF to RV’s with MySQL almost 20 years ago. I’m much happier now (the last 8 years or so with cursors and MySQL).

Regards, Gerrit

 

[Chris Miller]

Using a DBC for RV’s is definitely slower

I still doubt that, the queries of RVS are passed over and executed just like the SQL you send over SQLEXEC. A closing connection is adding overhead for reconnecting, if you don't see that and don't use connection pooling actively from the VFP side by shared connections, then you're maybe just lucky the MySQL Server is configured to do that, i.e. not really release a closed connection right away but allow the next connecting user to reuse it. It's a myth connections are to be used as short living as possible because that makes them more stable.

But aside of all of that, it's still unclear whether you're now investing much time in rewriting the data access vs solving the concrete problem of setting the connection object in a DBC to be able to use connections for production, test and development, local, central, off the customers site, whatever.

Chriss

 

[Gerrit Broekhuis]

Hi Chris,

Rewriting these applications was not very complicated, as I have all procedures required already available. It’s mainly a cut&paste operation + testing. I’ve done this already for numerous other applications.

As no one could show how to change the connection stored in the DBC I did the rewrite. I did about 100 forms in less than a day, including testing.

I’ve made lots of measurements a few years ago, when I had the idea to stop using DBC’s/RV’s/ODBC and use SPT with ODBC instead, using cursors. In all queries I could notice at remarkable speed improvement. In the end my code and forms were much easier to read and less complicated, and maintenance was easier (and faster as well).

The best thing would probably to use MEMSQL instead of MYSQL, but MEMSQL is way too expensive for my type of customers. Running MySQL from a fast SSD instead of a harddisk is the best I can achieve.

Regards, Gerrit

 

[Chris Miller]

Okay, understood,

And I also can take in that you see remarkable speed improvements, but that doesn't have technical reason and must be due to your conversion.

I did about 100 forms in less than a day, including testing.

That's a good conversion rate, I assume that's using some automatisms.

All reasons speaking for your conversion.

But let me ask one more question, because you only posted your dbgetprop, not your dbsetprop. What are you doing at all?
And what's the definition of the connection? Is it based on a connection string or a DSN name? Because you won't be able to change the connection parameters the DSN uses by setting connection properties, when they are governed by the DSN. That includes the Database property to switch the active database. And by the way you could also SQLExec(h,"USE databasename") to switch the active database, when it's on the same MySQL Server and access to the other database is granted to the connected MySQL user. And when you use fully qualified names, like ´database´.´table´ in the query instead of just the table name, you could even do cross database queries and not require a second connection. an ODBC connection connects your client to the server, mainly. That your application process is connected to a specific database is secondary.

Chriss

 

[Gerrit Broekhuis]

Hi Chris,

The DBC used a DSN connection. I tried DBSETPROP, but couldn’t get this working properly. Therefore my initial question, for which I couldn’t find a proper solution.

Regards, Gerrit

 

[Chris Miller]

Okay, well, that explains it.

I may be too obvious to me, that I'd even think if that possibility. The DSN then is what you need to change, and that's not doable with DBSEtProp. The DSN completely determines the connection in that case and you can't switch only one aspect, say the database, or change the connetion string.

So say you'd like to connect with a DSN but to another database, then DBSetProp won't work. So all you had was a wrong concept of what DBSetProp can do.

Chriss

 

[Gerrit Broekhuis]

Hi Chris,

In other words: it’s impossible to overwrite the connection properties in the DBC by code.
So I took the right decision to do the rewrite.

Regards, Gerrit