Problems Sharing Query Builder .TSM Files Between Users

Toad Data Point
1

We’ve been having difficulties sharing .tsm files between different users. The problems are:

  1. The .tsm file appears to save the username and possibly the password of the user who created the .tsm file.

  2. When a user other than the original creator of the .tsm file opens it, sometimes they can overwrite the username and password in the “Need More Information to Connect” dialog box, but after that it ends up creating a new connection, even though they may already have a connection defined to the same database using their username/password combination.

  3. Every time they open a new .tsm file, the user has to re-authenticate to the database, even though it may be to a database they are already connected to.

  4. There doesn’t appear to be a way to re-save the .tsm file with the new connection information for future use. The .tsm file gets saved with the original username stored in it.

  5. I don’t see an easy way to choose which existing connection to use for the .tsm file, if I already have a connection defined, nor do I see any way to switch the query to use a different database if I want to run the same query against a different data set (production compared to test/dev database, for example).

When I last contacted support about this issue the reponse was that this would be fixed in this summer’s release. I’ve been testing with the beta versions as they come out (I just tested it with 2.5.0.418) and the behavior appears to be the same.

Is this still in the works?

Sincerely,
Alan Fasick

2

Yes, a resolution for this is on our list. We have CR 59,771 for this issue. However, it is a bit harder to resolve than it may appear to the user. Our components that draw the tables include the database info in them and are not designed to be independent of the connection.

I can offer to pieces of info that might make the use of sharing files easier or more understandable.

  1. Connection aliases are case sensitive. The string that you see in the connection window that contains the server and schema has to be exactly the same to resolve. I have found that just an extra space or lower case letter will fail to resolve to what I think is an existing connection. So it might be best to start with sharing your connections. The connection manager toolbar has an import and export connection action.

  2. Use reverse engineering to save local copies. If a saved connection in a tsm file does not resolve to what you want, go to the query tab, copy the SQL and place into a new Query Builder form. Click the “Visualize the Query Statement” action to reverse engineer the query. This method can also be used for creating QB files from SQL.

I appreciate your detailed list of issues. I have created a new Cr 61,399 to include your details. But I am not confident a flexible change will be made for the next release. (Or maybe I am wrong…) When it comes to collaborative SQL work I think that a broader think should be done. It should include connections, tsm files, project manager files, annotation cache, report templates, and job automation files. I also need some input from several shops to gather the data for a comprehensive solution. If this is a big issue for you and you have more input for a broader solution I would be happy to interview you.

Debbie

3

FYI. We entered CR 61,399 for short term solution to be included in this next release and Cr 61,424 for enhancements to collaborative resources.

Please continue to input on this area. Thanks

Debbie

4

Thank you for your response. I’ve been experimenting with both of the suggestions you made. I wasn’t aware of the export/import features in the connection manager – that will make it easier for us to setup connections consistently for multiple users. However, in my experience even if the connection alias is exactly the same on two computers, it will not consider it a match if the usernames are different – I will retest to confirm.

The “Visualize the Query Statement” will also come in very handy, however, the way it puts all of the WHERE clauses under one global WHERE clause will not be very helpful for our users. They have very limited SQL experience and rely heavily on being able to put in WHERE conditions using the GUI tool.

Our need is fairly simple. We have a group of users who have access to a couple of different databases they query against. Each user has their own username and password to the databases (sharing user accounts is against our organization’s policy). When they write a new query, they save the .tsm file to a shared location on the server so other users can run the same query, or base new queries off of their queries. Maintaining separate queries for each user is not really practical for us.

This issue is currently the top issue preventing us from being able to fully commit to using TDA as a replacement for our current query tool which we are trying to phase out. We have work-arounds in place for our other issues, but I haven’t found a work-around for this issue.

Thanks,
Alan

5

I did some experiementing with this issue. Your are correct, it is really aggrevated by the use of different logids. I have esculated the CR and I believe we can have a fix for the connect issuse by the next Beta build. Unfortunately, I do not see any work around in the released version.

The reverse engineering global where clause is not something that can change in this release. However, if the above gets resolved you will not need it.

So hold tight, I am rounding up the troops…

6

Thank you for looking into it – I’ll retest in the next beta release.

-Alan

7

I’ve been doing some testing in Beta 2.5.0.460. I’m primarily working with Oracle connections, using the “Oracle Client” connection method. I have been using the SID of the database as the name of the connection, regardless of the server or username the connection is setup to use.

Here are my observations:

As long as I don’t use the “Save Password” option in the “Connection Properties”, I can create a query, save the .tsm file, open it as a different user and over-ride the connection information. I also can re-save the file and it saves the new connection information with the .tsm file. This works great, we are looking forward to having this fix in the next release.

If the user does use the “Save Password” option when defining the connection, it not only saves their password in their connection definition, but it also saves their password in any .tsm files the save. If another user opens their .tsm file, TDA creates a new connection, even if one already exists by the same name, and it fills in the first user’s username and password and logs them in. This is definately not a desireable behavior for us as it is common for our users to share .tsm files with each other, but against policy for them to share logins. For us, the desired behavior would be for it to save their password under the connection definition, but not in the .tsm files. We can work around this issue by not using the “Save Password” at all if necessary.

As a side-note, I noticed that .tsm files do not show up in the open dialog box with Files of type: “Supported Files (.)” is selected – I have to switch to “All Files (.)” or “Query Builder File (*.tsm)” to have them show up.

Thank you very much for your assistance.

-Alan