how to change dtabase in sql

[farzad321]

Hi to every one . In local database when i need to change database juse with this command do that.( set path to 2022 or set path to 2023 ) and then use mydbf. but now how to do that in sql. because i have a database name mydata. in new year how can make a new database 2024 that user can change to 2023 or 2024 and see his data ?
i make a connection in odbc to connect to my sql server . did i change that or that is enough ?
in my local database i made a some remote view to retrieve data from mydbf.dbf in farzad database. this is a 2023 data. how can i do that for 2024. did i need to make a new database or just new mydbf2024.dbf. so i need to make a new remote view for any years separate ?
thank you to all
farzad

 

[Steve Meyerson]

...odbc to connect to my sql server . did i change that or that is enough ?

I use a single odbc connection to my commercial MySQL server which contains several databases. The odbc connection does not need to be changed to access these databases. I suppose that would be true of your server, whether commercial or not.

Regarding the transition from year to year, if you want a separate database for each year, it should be simple to do that. Is that your question?

Steve

 

[Mike Lewis]

You would normally specify the name of the remote database as part of the connection rather than within the remote view. To point to a different database, you can simply edit the connection. The remote views will then automatically point to the new database without any changes to the view or the application.

Alternatively, if you want to keep a connection to both the old and new databases, you can create a new connection pointing to the new database, and change the remote view to point to that connection.

I would also question your need to use a different database for each year (if that is what you are doing). In most cases, it would be preferable to have a single database, and to filter the remote views to only show the data for the year you are interested in.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[farzad321]

thank you steve and mike
yes i have to has seperate equal database for any year. and i search for the best and easy way for changing database without editing application.
if we can just edit the connection to change a database and do not need to change remote view Then this is my point and every things okay.

 

[Chris Miller]

I think you can answer that on your own.

How is your connection made now? Is it configured in the ODBC manager? Then look into it what properties are set in it.
Do you have a connection string? Then please look into the single parts of it.
Both a connection configured in the ODBC manager or a connection string can be used as a part of a connection object in a DBC which is usually the basis for your remote views.

I think you already did all this and just have forgotten.

If you don't find the database specified in either of these things, what's your code to connect to the database you use now?

Chriss

 

[GriffMG]

Also it sounds like you need to be thinking about database version control, making sure they all match the
applications accessing them.

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0
​

 

[Steve Meyerson]

I use a single odbc connection to my commercial MySQL server which contains several databases

Sorry, Farzad, I misspoke. The "single" odbc connection was for multiple TABLES, not multiple DATABASES.

I assumed you could use a different table for each year, all residing in a single database, as Mike has suggested.

Steve

 

[Chris Miller]

Steve,

while you're only using multiple tables from the database you connect to it's still true that in general the connection is made to the database server and it can not only have multiple tables, but also multiple databases and depending on which database system, the switch to another database can be made with an SQL command of the database server, MySQL and MSSQL have the USE command for that. It may not be a coincidence that the VFP USE command uses a DBF since DBF actually is short for DataBase File. But that's leading off-topic.

A connection can also be made only to a server and then it will depend on the server configuration and permissions of the connecting user or role to which database you are connected or whether you only connect to the server itself. In short, what you told us - Steve - is still true.

Anyway - Farzad321 - if you wonder why you don't get a direct answer on how to do this: So far we only know you make use of remote views. That's still not telling us how you connect to the database, as there are - as so often - many ways to do things in VFP.

Anyhow, I can give you an ensuring answer: It's possible to not need to change an EXE to change the database it connects to.

Even if the database choice is made in your code, currently, you can always change your code to use data - configuration data - to not have a hardcoded connection that only is to one database. In that case, you do need a code change, but one, that allows you to never need to change code again. This, by the way, is true for anything you want to make independent from the code of an EXE to not need to rebuild an EXE more often than necessary to roll out new versions.

It's not even what I was thinking about at first, but it's another way of seeing it's always possible to not need the change of an EXE. Have you never thought about configuration?

We still need to know more details to give a recommendation that fits your exact situation. I already indicated a DBC storing your remote views can also have a connection object. That's the normal case, but not the only possible way. And even if there is one, this connection object can be specified in two major ways: You can specify to make the connection via a data source name aka DSN (that's the name of a connection made in the ODBC Data Source Administrator of Windows). The other way is to specify a connection string that is specifying how the connection object actually connects to the remote database. And besides thee two ways you can also have created the views to use a DSN of the ODBC Data Source Administrator directly, without a connection object, so there could even be no connection object within your DBC. Finally, as a fourth option, you could use a remote view using the CONNSTRING option to specify the connection string.

Since we don't know that, it's impossible to give you a recommendation.

If I were you, I'd first look into the DBC that has the remote views in it and look for a connection object. You MODIFY that dbc and then look into this:

If you find a connection listed when you click that, it's likely how the remote views connect and this means it's outside of your EXE, you can change that without changing your EXE.

But even when you find such a connection, you need to make sure it is what the views use. Pick a view of your choice and inspect it, by executing:

Open Database your.dbc && if it's not already opened
? DBGetProp("viewname","VIEW","ConnectName")

In fact, if you don't know what you did yourself or inherited this application project/code, then strictly speaking you would need to check all views what their ConectName is.

If that ConnectName matches with the name of the connection object in the DBC, then the view uses that. If there is no connection object in the DBC, then open up the ODBC Data Source Administrator tool (the 32bit version of it) of Windows and see if you find that name in there, because that's where VFP looks when it doesn't find a connection object in the DBC of the remote view with that name.

Well, I would really like to have spared you all that details, and I could have, if you'd just told more from the outset. In essence, you see in any of the endpoints of all of this, you either have a connection object in the DBC, so a change of that is changing the database, or you change the connection properties within the Windows ODBC Data Source Administrator. The only case not covered yet is also the least probable case that you USE a remote view with the CONNSTRING command option. Well, in that case, it's again a code change you need to make, so the CONNSTRING itself is turned into configuration data.


In very short, I could have skipped all these details and just ask you to realize that whenever you want something to be done without recompiling an EXE, your first thought must be configuration data.


That's what you want to have to read into an application object property, a variable or a cursor, typically at the start of your application. And what you will use wherever in your code you need it. That way you avoid what's called "hardcoding" or "hardwiring" something, which has the technical debt and cost of finding all these places, changing them, and building a new EXE.

If you didn't think of configuration data in the first place, and already have 1000 places in your 1 million code lines large EXE that are that database name, you have gotten yourself into a bad situation indeed, but at least you can use a search and replace tool like VFPs own Code References to find all these places, change them once and be done with it for all future versions.

And now comes the heretical question: "Does that mean we could get down to a simple universal EXE which never needs to change itself and changes behavior just by changing configuration data?"
Of course not, even though code also can be data, configuration data has its major use for short data, like the name of a database surely is. Even the term "database driven application" is not pointing out such a construct. But you can do a lot with configuration data to have the flexibility you need to use another database, another server, another printer, anything that you can anticipate to change and won't want to address with a rebuild of your EXE to make it work again.

So, think of configuration data more often.

Chriss

 

[farzad321]

Hi Chris, I enjoyed your thorough explanation and others. Maybe I should have provided more detailed explanations from the beginning. Of course, I myself am a software engineer and this program belongs to me

It's actually been a short time since I migrated the database to sql and it's still in its first year. Therefore, it is necessary to consider the parameters of use in the following years.

First, I have created a connection in the odbc control panel for permanent communication with the server

Then in dbc I have created a connection using the same name in odbc.

As you can see, farzad01 database keeps the data of the current year, which includes several dbf files.
Now it is necessary to create Farzad02 database with the same files for the new year.

Now we need a way to change the year with minimal changes.
Will using this code work for me?

salmal is the active fiscal year specified by the user and specifies the path to our database.

I am also very grateful for the help and guidance of other friends.
The reason for the delay in answering was the holiday two days ago, and I apologize for that.
Actually, I got a little help from the translator.
farzad

 

[Chris Miller]

Well,

SALMAL seems to be the year you want and is a configuration item, is it?

The connection object in your DBC specifies the ODBC DSN name vasl and usually you'd change which database to use there, but your VFP connection object also specifies user, password, and database. So in your case that's what to change, if you want to switch.

You create three connections by code. Is that done with every start of your application? It's no good idea to alter a DBC in its usage, as that needs exclusive access, usually. No idea how that is with CREATE CONECTION. But that wold only be needed once anyway.

What you can't do, though, is to USE a view and specify which connection object to use at that time. It's part of the view definition to be a VASL, a MVASL, or a ETESAL view and that can't be changed on the fly.

What I did once was having a source DBC of which I copy all objects into a new DBC at start, starting with the creation of the currently needed connection object, that way creating the DBC was no problem, each user also has their own DBC file, the central DBC just was the source of this generation process.

Chriss

 

[farzad321]

Hi again . Do you suggest I create a separate dbc for each year and each point to a specific database? In this case, I have to recreate the remote views. What do you think about the least change in the program?

That is, for example, for 5 fiscal years, we should have 5 similar DBCs, and only their connections should be changed, and other settings should be kept exactly the same, and DBCs should be opened based on the need.

In this case, we will not need any changes during the execution of the program and everything will be set at the beginning of the work.
Do you like and recommend this method?
I am very grateful for your ideas

thank you


farzad

 

[Chris Miller]

Let's think from scratch

For maintenance reasons, you should only have 1 DBC to maintain, so a view change is done once and not in 3 DBCs. That would be my first concern over all others.

It does not help to have three connections in this one DBC. So I would try what happens if I delete the connection and create the currently needed connection for the year you want to connect to. I guess deleting the connection object just means the views can't be used until the connection is recreated with the same name. I also don't know from the top of my hat if you can ALTER a connection or change its properties with code other than doing so by deleting and creating it.

And you should have a DBC local per user anyway, as remote views shared in a central DBC for all users have the small problem of getting an error when two users concurrently open a view. Just at the opening stage, but that still happens from time to time. And to avoid that you have a view DBC as part of the installation directory of all clients. And then changes on this local views DBC are less of a problem.

Chriss

 

[Chris Miller]

As you're using sa user, there is a simpler scheme in T-SQL to add users by their windows account profile and the use a connection without user/password but with Trusted_Connection=Yes. This means SQL Server checks with the connecting user. This way you don't have such a sensitive information as the sa password in the open.

Chriss

 

[Chris Miller]

I think I can answer my own question about modifying a connection.

There is DBSetProp("vasl","CONNECTION","Database","dbname") to change the database property of an existing connection. So you can do that at the start:

cDatabaseName = config.database && however you determine which db is necessary

copy file \\server\yourappdata\remoteviews.* to (Getenv('TEMP')+'\yourapp\remoteviews.*')
open database (Getenv('TEMP')+'\yourapp\remoteviews.*') exclusive
DBSetProp("vasl","CONNECTION","Database",cDatabasename)

And that fixes which database the connection connects to just in time.

Chriss