Cannot change database name

[LaRetta]

Hi everyone,

We connect to several SQL tables using SQL server (and they reside in our graph). I have noticed that, if the table is moved to another server, FM has no problems with it; I can just change the DSN to point to the new server. But if the external source changes the default DATABASE name, FileMaker cannot maintain the same table occurrence. In other words, there is no way to re-point a table occurrence to a different DSN if the database name changes. Even if I rename it within the DSN, it breaks. FM will create a different source in the tables tab. No matter how I attempt to rename and repoint the existing table occurrence, FM refuses.

It seems that FM somehow encodes the database name internally and will NOT allow a change. What this means is that, if we attempt to point to a different database or if the external source renames their database and asks us to repoint to it, we can't. Our calculations, scripts, imports ... everything breaks. This rigidity is causing no end to our grief.

Has anyone else experience this? Are there ways around this issue? We cannot demand that our external source always keep the same database name!! Ideas greatly appreciated. :qwery:

LaRetta

[Baloo]

In 8 years of MySQL development I don't think I've ever changed a database's name once it went into production.

I got the following to work for me using MySQL ESSs so it's a little Apples to Oranges but in theory it should work with SQL server.

I created a database called test with myTable in it. I built a System DSN then created a table reference in FMP.

I then changed test to thedbfkatest this broke everything in Filemaker. I pointed the DSN to the "new" database and saw the same behavior you described.

I created a new database called test with a view called myTable

create view myTable as select * from thedbfkatest.myTable

I went back into filemaker hit sync for the old broken myTable shadow table. I was prompted for the primary key and everything was good from then on.

I'm not sure how your SQL server DBAs are going to feel about keeping legacy databases filled with views around, but they really shouldn't be changing database names in the first place.

Also if you're going to use views from an ESS don't forget to check the Views box in the lower right hand corner of the external data source dialog

[LaRetta]

Hi Baloo!

Thank you for the idea and, although we don't have access to create a view on that server, their IT is setting it up for us.

In 8 years of MySQL development I don't think I've ever changed a database's name once it went into production.

I'm afraid we do not have control over it. This large company has split who has rights to the three different tables so the database names must change. It has happened before and I won't be surprised that it will happen again. I'll let you know if these tests work for us. I sure hope they do and thank you for the idea!

LaRetta

[Teutonic_Night]

I've run into a similar problem. We're looking at developing a FM solution to keep track of specific data that is related to data in a MySQL backend. The problem is that this is a fairly high-profile project, and we are being required to make use of different environments for the different stages of development. There's going to a be a dev, a QC, and finally a production environment.

The problem I've encountered from some quick testing is that I can't seem to easily move which MySQL environment the FileMaker portion points to.

I thought that all I'd have to do is change which system DSN the solution is using when setting up the different environments, but that seems to break everything. I'm not really looking forward to having to update every layout and script that has any ESS references included, so I'd love to know of a solution to this as well.

[Baloo]

Set up dev, qc and production FM servers, point FM.dev to MySQL.dev and so on. Keep the DSNs the same on all three boxes, and you shouldn't have a problem. You will of course also need to keep your table and database nomenclature consistent across all servers.

If you don't want to manage 3 FM servers, you will be ok if you re-point the system DSN to the 'new' server as you move the project along. However (and this is experience talking) this is a recipe for getting bit in the arse.

[Teutonic_Night]

I also discovered that the tables in the referenced external database have to have their names remain consistent, not just the database name.

The only thing that comes to mind as a work around is to insert some sort of go-between table, and have your solution's scripts and what-not reference that table instead of the ESS. I haven't fully thought this through, though, and I'm guessing that it could cause all kinds of latency or synchronization issues.

And you'd still have to redo all the references to your ESS, but it *might* allow you to minimize how much scripting is impacted.

Just an idea.

[Baloo]

I also discovered that the tables in the referenced external database have to have their names remain consistent, not just the database name.

Well yes. If I change a table name in one of my databases, all of the queries in the PHP part of my solution will break as well. Why do you expect something different just because you're using the FM Client for a front end? If you're going to use MySQL you going to have to play by its rules. It's unfortunate that Filemaker won't give us direct access to the underlying code to do a global find and replace (or a tool to accomplish the same task), but the willy nilly renaming of databases and tables is really something to be avoided. Still if dynamic database and table names is a requirement of the system, you can get around it by using MySQL views. Keep the names of the views static and alter their SQL to point to the renamed tables.

[Teutonic_Night]

Being new to using ESS sources, I didn't realize that FileMaker grabs the database and table name from the DSN and stores it locally without granting access to it to a developer. I was hoping that if the DSN was simply pointed to a new database, that FileMaker would just get the database and table names from the DSN as necessary, not just the first time the ESS is accessed.

I don't think there was anything "willy nilly" about the database names- one instance was for development, and the second was for QA. I don't know why the MySQL DBA named them the way he did, but the tables and fields were all named the same, simply the database name was different between the two databases. Maybe it was because they were both hosted on the same server and couldn't have the same name.

And I agree, some sort of search and replace capability would be a huge benefit.

[Baloo]

Yes MySQL does require database names to be distinct across a server. Generally speaking though, each stage of software development has its own server with consistent nomenclature across the lot to prevent exactly this kind of thing from happening.

MySQL is free and will run on just about anything. You could build a reasonable test server with a box that's been hanging around in an attic for the last 5 years.

[FM Maniac]

I know this is an old topic, but it seems the most visible in Google searches of "filemaker external sql source database rename". Anyway, FileMaker 12 supports ESS relinking for tables as described here: http://www.filemaker.com/12help/html/odbc_ess.20.9.html.