When Connecting read-only to a SQL database, will records get locked up, or modified in anyway?

[ejpvi]

Greetings,

I have used Filemaker to connect to a SQL server before, so I know it works.

Our company uses a program by Dydacomp called: Mail Order Manager (MOM).

This System runs on a SQL server. I have several of their table structures that were in their help file. We already use Crystal Reports to pull some information out of it.

So I want to create a read-only link to this SQL table with Filemaker.

Does anyone know, will Filemaker attempt to modify this data in anyway? Or lock the records? I want the read to be as transparent as possible. I don't want the Mail Order System to be affected. I assume if Crystal Reports works in this fashion, that Filemaker will be similiar. Because both use SQL Queries to pull their information.

Does anyone have any insight into this? I am basically trying to make sure that the data doesn't get changed, or locked when I make a Filemaker Connection to it. I simply need to read information out of it.

[ejpvi]

Nobody Knows?

[mr_vodka]

No it should not lock the record as when viewing the records, you will just be using a SELECT.

[ejpvi]

Thanks for your insight.

I have been nervous about connecting to our huge Mail Order System, because I don't want to cause any corruption or "record Locking." That might cause that system to crash.

Do I need to ensure my ODBC is configured a certain way? Or just make sure when I create the layouts, I don't give people write access to those fields?

Do you know the trick to keep it a "Read Only" Connection when connecting to a SQL Table?

[mr_vodka]

Well the more secure way would to setup your SQL server account to have only SELECT capability for the one you will use to connect to FileMaker.

[ejpvi]

Good Idea... thanks for the advice!

[ejpvi]

I really appreciate your advice on this, I am getting closer to getting the nerve up to make this connection.

I think I have a pretty firm idea of how to get Filemaker talking to this thing, once it is connected, by adding the SQL table Occurrences to the Relationship tab of my database.

My concern is more on the SQL side....

I understand the basics of reading data out of SQL... as tables, data, queries... but the backbone, user accounts, creating the connections I am a bit un-experienced on. I have only ever made a few ODBC connections in my time.

One person I recently talked to, suggested that I create a VIEW table inside the SQL database that would have all of the variables I need, he said that would ensure I wouldn't lock any records up, and not hinder the performance of the database. Not sure if I need this or not. I just heard the ESS is slow in Filemaker, I don't want it to slow down performance on the SQL.

I was wondering if you knew of any good websites that provide a pretty nice demonstration of setting up a Filemaker pro 10 Advanced Server connection to a SQL 2005 Via ODBC. I'd rather not just start tweaking the SQL tables permissions without some understanding of what I need.

[mr_vodka]

You wouldnt be tweaking any permissions from the main table. You would only be tweaking any account settings that you will use to connect to the SQL source. Your DBA can create a whole new account if they are more comfortable with it.

Furthermore, as for the views, I would actually recommend using them. The views can be very handy when dealing with sorting sets, outer joins, etc. Keep in mind that you can also use a view to only show the columns that you will need to display in FileMaker. There is no reason to show the rest of the columns in the table. Use a view to restrict them as this is just extra data that you will have to load.

BTW just because you have a view doesnt mean that one can not edit records. It still a permissions thing.

[ejpvi]

Our SQL DBA recently left the company. We just do basic stuff in SQL, but that is where some of our big Databases are stored.

The guy that is currently keeping an eye on it, created me an account in active Directory.

From there he went into the SQL Admin, and we changed the permission to "Data Reader" for that account. Not sure what "Data Reader" does... it was already a privilege set in SQL.

Would you happen to know how to limit the access to only read-only? That way, when I connect Filemaker to SQl, there is no chance it can write back?

Are there any steps you would take, that I may have missed?