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

 

[Chris Miller]

No, just when the connection is based on a DSN, and you still have all options to create other connection objects, a DBC isn't limited to one.

If you even just describe that you want to change a connection object anybody will assume you have a connection object based on a connection string and not a DSN.

You can also change anything of a DSN, but in the odbc data manager, not with Dbsetprop. The DBC object merely is a pointer to the DSN in that case.

You can also combine all the things, create a second DSN and another DBC connection object based on that second DSN. And I'm sure there are ways to influence or create a DSN programmatiacaly. You ust picked the one thing that has no effect o the connection, as the database is part of the DSN configuration.

Sorry, that I didn't asked earlier, but some things are too basic to think of them as unknonws.

Chriss

 

[Gerrit Broekhuis]

Hi Chris,

I’m sure you’re right about this, but I still have no idea whatsoever how to get to use a DBC with a single “preset” connection. It may be a basic question, but I’ve never used a DBC with “other” connection parameters.

Regards, Gerrit

 

[Chris Miller]

I’ve never used a DBC with “other” connection parameters.

The DBC doesn't have connection parameters, the DBC stores connections just like it stores tables or views, as information about them. And in case of a connection of which the DBC mainly knows a DSNName, any details of which driver to use, which remote (MySQL) database to connect to etc. are not at all stored in the DBC connection object but in the DSN which is maintained and configurable in the ODBC manager of Windows. That's not even a VFP feature anymore.

I'm not even sure what you're still confused about.

If you have a DSN that works all views defined with that connection automatically establish it, there's no SQLCONNECT or SQLSTRINGCONNECT you need to do yourself. If the DSN isn't connecting to the MySQL database you want to use, you can change that in the DSN configuration and the DBC doesn't need to change at all. Not even the views need to change., not even the connection the views use need to change.

That's the very reason for using a DSN, an admin can configure the backend datbase in Windows ODBC manager without any VFP knowledge.

Chriss

 

[Chris Miller]

Maybe this detail helps you understand how connection in a DBC work:

Whenever you define a remote view in a DBC, you have to specify the DBC connection object to use. In case of prgrammatically creating a view with CREATE VIEW viewname AS query there is a mandatory CONNECTION caluse (unlike local views), because of course a remote view doesn't query DBF data but data from a remote bckend like MSSQL or MySQL, in the DBC you mainly only store the query to execute via the connection.

The connection object in a DBC can be one of two major type, one that actually only refers to a DNS mainteined by Windows ODBC manager or a connection object that cotains several deatils of which one canbe the connection string to use. If you ony ever worked with DSN base connections in DBCs, then you should knwo that the details about the connection are mainteind by Windows ODBC manager, don't you? I wonder how you then even get the idea you could use DBSETPROP to change property that's a detail of the DSN in Windows ODBC manager.

And to make one more thing clear: The connection object in a DBC isn't the connection itself, it just is information about the connection. Once you used a view of the DBC that establishes a connection you have exactly the same type of connectiion handle as you get from SQLCONNECT or SSQLSTRINGCONNECT, you'll find it in the array of handles you can get from ASQLHANDLES. And with that you can even combine using RVs and any SQLEX§EC you like going thrrough the same already established connection.

Chriss

 

[Gerrit Broekhuis]

Hi Chris,

Yes, I understand the concept, with ODBC, etc..

The only thing I cannot find out is how to create a new connection for an existing DBC by code. The DBC has a default connection, but with the wrong parameters. I cannot edit the DBC, because for every installation the parameters may be different. So I need to overrule this by code.

Regards, Gerrit

 

[Chris Miller]

What you could do in the DBC is use CREATE CONNECTION and create it the way the current user needs it, not using a DSN, but a connection string, for example. That would give you the maximum freedom to define the connection as you like. But as we now know the DBC merely points to a DSN, you can also make all necessary changes there. i.e. use User DSNs or file DSNs and so the same DSN mrely by it's name can mean something specific for each user, client, customer, company, whatever, because each user, even each session/account can see a different configuration in a user DSN, system DSNs as usually used are only one type of DSNs.

If you go for creating a new connection object in the DBC, what would speak for it is not going through the ODBC manager at all, have no DSN and nothing a user can change on the Windows level, though usually such manipulations are restricted by group policies and a DBC could even be easier to hack than the ODBC manager DSNs. So any problem you might have is already thought of with all the ODBC manager and Windows group policies offer.

What I also did already is have a base DBC that I copy at start and extend it per user, per session, per day even, if that would be necessary. A view DBC is recommended to be local to the user anyway, so it can also have user specific things in it, including the connection object. And that even gives you exclusive access, would that be necessary.

In the most extreme solution you could actually just use your own DBC. It will obviously be useful to be able to copy over views, and that is possible using DBGETPROP() and then CREATE VIEW and DBGETPROP on your own local per-user DBC.

Chriss

 

[tomk3]

Hello,

we use RVs like this :

On startup we create a connection string based on location and store it in a public variable or property of app object.
Then instead of "use rv_name" we do "use rv_name connstring(gcConnstring)

Maybe with gofish this can be done without too much loss of hair

Regards
tom

In reality we open all rvs on startup with NODATA, build indices and do requery() later when filters are set by user/program

 

[Chris Miller]

And for the case you'd use the possibilities DSNs offer, here's a way to programmatically create a DSN:

https://fox.wikis.com/wc.dll?Wiki~ProgrammaticallyCreatingDSNs

At first glimpse it lacks the creation of a User DSN, but looking into

https://docs.microsoft.com/en-US/sq...edatasource-function?view=sql-server-ver15you

see in the case a hwnd is passed in instead of NULL the dialog triggered will be the one asking what type of DSN to create.

How to provide that in parameters surely is somewhere in there.

Chriss

 

[tomk3]

... jsut to add : connection string can be DSN=ohmydsn, so you have both.

Regards
tom

 

[Chris Miller]

Hi Tom,

isn't it setting a oonnectionstring to "DBQ=theDSN"? And as far as I know it allows adding settings. But I'm not sure what you can add or even override from the original DSN.



Chriss