Question about FileMaker Server and ODBC

[William Maslin]

Hi,

I have developed a FileMaker "front end" to an Oracle database via ODBC
that allows me to query for info and also to UPDATE. It works fine.
Everyone else in our workgroup has to use the Oracle interface...which
is far, far worse than anything on the interface hall of shame.

With a little tweaking, everyone in our work group could use my
Filemaker solution. However, there are a few issues. The
administrators are nervous about security issues when doing UDPATEs
since some of the security is built into the Oracle user interface,
itself, and that would have to be duplicated in the FileMaker version.

Secondly, they change the Oracle password every month so I would have to
go into ScriptMaker every month on everyone's computer to change their
password in the stored SEND SQL procedures.

So, I have two questions about FileMaker Server:

First, which product do we need? Do we need FileMaker Server or
FileMaker Server Advanced to query and UPDATE the Oracle DB? The
FileMaker web site is not clear. They state:

"Share data with other applications via server-based ODBC/JDBC support.
ODBC/JDBC clients can submit SQL queries to FileMaker databases."

We don't need to "share" data via ODBC, ie, Oracle doesn't need to
access any FileMaker data. We just need to query, import and UPDATE the
Oracle DB. However, I assume the regular version of Filemaker Advanced
can't do ANY ODBC connections at all and that we probably need the
advanced version.

Secondly, would it be possible to take my present solution and use
FileMaker Server/Advanced to act as a web server so that the end users
would query and UPDATE though a web browser interface?

In other words, the end users wouldn't need to have a copy of FIlemaker
on their computers, plus the monthly password change problem could be
handled on the server. Sounds too good to be true. Is it?

By the way, it seems that there is no "try before you buy" download for
FileMaker Server Advanced. Is that correct?

Thanks,

Bill

[Howard Schlossberg]

William Maslin wrote:
> I have developed a FileMaker "front end" to an Oracle database via ODBC
> that allows me to query for info and also to UPDATE. It works fine.
> Everyone else in our workgroup has to use the Oracle interface...which
> is far, far worse than anything on the interface hall of shame.
>
> With a little tweaking, everyone in our work group could use my
> Filemaker solution. However, there are a few issues. The
> administrators are nervous about security issues when doing UDPATEs
> since some of the security is built into the Oracle user interface,
> itself, and that would have to be duplicated in the FileMaker version.

You don't need FM Server Advanced, just the regular version. Set up
your DSN on the FM server machine and point to that DSN when setting up
the external data sources in FileMaker. You will set the user name and
password in the external data sources (ESS) setup, so that is the only
place you need to change it each month. The DSN does not need to be set
up for each user since it has been set up on the FM server, and the
password therefore does not need to be changed for each user.

If the Oracle DBA is concerned about security, then they need to make
available to you an account that only has the privileges your users
should have.

[105]

William Maslin wrote:
>
>
> By the way, it seems that there is no "try before you buy" download for
> FileMaker Server Advanced. Is that correct?

Technet membership (c.$150 p.a) comes with a free FMSA license for
dev/testing (limited 5 concurrent user)

[William Maslin]

In article <030dd28e$0$1272$c3e...@news.astraweb.com>,
105 <cort...@internode.on.net> wrote:

I spoke too soon. Although no evaluation version of FMP server Advanced
is offered on the "main" products page, there is a 30 day free trial of
Advanced here:

http://www.filemakertrial.com/servertrial/form/entry.aspx

[William Maslin]

In article <hdkg8n$pcj$1...@news.motzarella.org>,
Howard Schlossberg <how...@nospam.fmprosolutions.com> wrote:

Thanks for the info! At the moment, everything in my solution (FMP 9)
works with "stored" procedures, ie, the username and password are stored
in the Import scripts and Send SQL stuff.

I played with ESS briefly and it works great for single tables, but even
trying a FIND using just two find criteria (one each in two related
Oracle tables) was unacceptable slow. In the future I might try the
View route, but not yet.

With respect to speed, my report-building query imports from 53 fields
in about 12 linked tables, and even though it is searching around 2
million records in one of the tables, FileMaker imports and formats the
report in about 1-2 seconds. Amazing!

I wish the user and password dialog would pop up for "stored" SQL
statements just like they do for ESS. It would be great if Filemaker,
Inc., would add that.

In any event, I agree that when going to a web based solution FMP could
easily handle the security instead of Oracle. The web user/password
could be the same as the Oracle user/password. That would be important
because Oracle wants a record of who updated what, but that could be
handled in the update statement since all of the Oracle tables I update
have a field for who updated it and another for DateTime.

Thanks for your help. I look forward to trying out the Server demo.

Bill

[Howard Schlossberg]

William Maslin wrote:
> In article <hdkg8n$pcj$1...@news.motzarella.org>,
> Howard Schlossberg <how...@nospam.fmprosolutions.com> wrote:
>> You don't need FM Server Advanced, just the regular version. Set up
>> your DSN on the FM server machine and point to that DSN when setting up
>> the external data sources in FileMaker. You will set the user name and
>> password in the external data sources (ESS) setup, so that is the only
>> place you need to change it each month. The DSN does not need to be set
>> up for each user since it has been set up on the FM server, and the
>> password therefore does not need to be changed for each user.
>>
>> If the Oracle DBA is concerned about security, then they need to make
>> available to you an account that only has the privileges your users
>> should have.
>
> Thanks for the info! At the moment, everything in my solution (FMP 9)
> works with "stored" procedures, ie, the username and password are stored
> in the Import scripts and Send SQL stuff.
>
> I played with ESS briefly and it works great for single tables, but even
> trying a FIND using just two find criteria (one each in two related
> Oracle tables) was unacceptable slow. In the future I might try the
> View route, but not yet.

Are you sure all those fields you are searching on, as well as all
fields on which you are basing relationships, were indexed in the SQL
db? Remember that FM is not providing the indexes on those since the
data lives in SQL.

Howard

[William Maslin]

In article <hdvmd6$vb4$1...@news.motzarella.org>,
Howard Schlossberg <how...@nospam.fmprosolutions.com> wrote:

I don't know. I'll check,

Thanks!