Filemaker › FMPExperts

beginners odbc question

Classic

List

Threaded

14 messages Options

Colm Osiris-2

beginners odbc question

Hi Experts

I am running OS 10.4.10 and FMPA 8.0 on my iBook G4, which is
networked to an Ubuntu Linux server box.

I have a potential client who are currently using FMP 6 and FMP
Server 6, but wish to upgrade to 9. They have an SQL database (don't
yet know more details of flavour or OS) which they wish to query from
FMP 9. (As far as I am so far aware, they only want to import data
into the FMP database, and not export.)

I am somewhat out of my depth here!

Is it worth my setting up a trial run of this using FMP 8 and Ubuntu
at home, or do 8 and 9 act so differently that it wouldn't be of any
use? And would it very different again if, say, they were using
Windows instead of Linux?

I've read what I could find on FMP 7 ODBC access in the two books I
have (FMPro 7 Bible and Special Edition Using FileMaker 7), but this
has only given me a very basic overview. Are there any sources of
information on the web for this, or will I need to buy a book?

If so, could I get away with buying a book for FMP 8, or would I
really need the 9 version (which wouldn't be so much use to me
personally until I buy FMPA 9)? I suppose, if it clearly marks what
is new in 9, then a 9 book would be just as useful to me even before
I buy a copy of FMPA 9.

TIA

Colm

--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>

Robertson, Forbes-2

RE: beginners odbc question

The way 8 and 9 can work with SQL sources is pretty different.

With 7 + 8 you have to import the data from the SQL source, using an
ODBC connection. This can be slow for large data sets. You then have a
copy of the data in a FM table.

With 9 you have the option to use ESS which is a nearly live connection
to the SQL source. Your ODBC setup can be done on the server, which
simplifies things. But the new ESS only works with a few data sources.

Whereas the ODBC import can work with far more data sources -- basically
on source you have an ODBC driver for.

One of the tricker parts of the whole process is getting the ODBC
connection to work -- so you can practice this with 8 and presumably
mySQL on Linux -- but the details may well be different on your clients
setup.

In 9 once you have the ODBC connection, it is easy, but slightly counter
intuitive to setup an ESS table. You first make an external data
connection, then add the table to the relationship chart, then a FM
table is automatically created for you. Then it is normal FM.

Hope this helps somewhat
Forbes

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of Colm
Sent: Tuesday, July 31, 2007 1:39 PM
To: Filemaker Experts
Subject: beginners odbc question

Hi Experts

I am running OS 10.4.10 and FMPA 8.0 on my iBook G4, which is
networked to an Ubuntu Linux server box.

I have a potential client who are currently using FMP 6 and FMP
Server 6, but wish to upgrade to 9. They have an SQL database (don't
yet know more details of flavour or OS) which they wish to query from
FMP 9. (As far as I am so far aware, they only want to import data
into the FMP database, and not export.)

I am somewhat out of my depth here!

Is it worth my setting up a trial run of this using FMP 8 and Ubuntu
at home, or do 8 and 9 act so differently that it wouldn't be of any
use? And would it very different again if, say, they were using
Windows instead of Linux?

I've read what I could find on FMP 7 ODBC access in the two books I
have (FMPro 7 Bible and Special Edition Using FileMaker 7), but this
has only given me a very basic overview. Are there any sources of
information on the web for this, or will I need to buy a book?

If so, could I get away with buying a book for FMP 8, or would I
really need the 9 version (which wouldn't be so much use to me
personally until I buy FMPA 9)? I suppose, if it clearly marks what
is new in 9, then a 9 book would be just as useful to me even before
I buy a copy of FMPA 9.

TIA

Colm

--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>

--
********************************************************************************

The information in this email and any files transmitted with it is confidential
and may be legally privileged. It is intended solely for the addressee and
others authorised to receive it.

If you are not the intended recipient,, any disclosure, copying, distribution or
action taken in reliance on its contents is prohibited and may be unlawful.

Prospects Services Limited is a limited company registered in England and Wales.

Reg No: 3042176
Registered Office: Prospects House, 19 Elmfield Road, Bromley, BR1 1LT.

If you have received this email in error please notify:

[hidden email]

http://www.prospects.co.uk

This footnote also confirms that this email message has been swept for the
presence of computer viruses

********************************************************************************

--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>

Steven H. Blackwell

Re: beginners odbc question

I'd recommend everyone's reading of the ESS Tech Brief on the FMI Web
Site. See Tech Info 6566.

Steven
--
Steven H. Blackwell
Platinum Member, FileMaker Business Alliance
Partner Member, FileMaker Solutions Alliance (1997-2007)
FileMaker 8 Certified Developer
FileMaker 7 Certified Developer
_________________________________________
"The commitment of our Fathers is now the calling of our time."

--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>

Colm Osiris-2

Re: beginners odbc question

In reply to this post by Robertson, Forbes-2

Thanks very much to Forbes and Steven.

Before I go any further, am I right in thinking that the 'Execute
SQL' script step will not actually execute a 'live' SQL query, and
that I need ESS to do that?

I'm sure I'll have more questions as I get started with the project,
which won't be for a few weeks yet.

Colm

--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>

Robertson, Forbes-2

RE: beginners odbc question

You can use execute SQL to grab a live value from SQL server.

I have a script that performs a query against SQL server to get the
total value of invoices, this is then placed in a global field.

But to update this value you would have to run the script again -- which
is pretty quick.

If you are looking at lots of records and fields, then you are back to
importing data, which can be slow. I find that the delete of records,
and the cleaning of data often takes longer than the actual SQL import.

Forbes

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of Colm
Sent: Friday, August 03, 2007 5:44 PM
To: Filemaker Experts
Subject: Re: beginners odbc question

Thanks very much to Forbes and Steven.

Before I go any further, am I right in thinking that the 'Execute
SQL' script step will not actually execute a 'live' SQL query, and
that I need ESS to do that?

I'm sure I'll have more questions as I get started with the project,
which won't be for a few weeks yet.

Colm

--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>

--
********************************************************************************

The information in this email and any files transmitted with it is confidential
and may be legally privileged. It is intended solely for the addressee and
others authorised to receive it.

If you are not the intended recipient,, any disclosure, copying, distribution or
action taken in reliance on its contents is prohibited and may be unlawful.

Prospects Services Limited is a limited company registered in England and Wales.

Reg No: 3042176
Registered Office: Prospects House, 19 Elmfield Road, Bromley, BR1 1LT.

If you have received this email in error please notify:

[hidden email]

http://www.prospects.co.uk

This footnote also confirms that this email message has been swept for the
presence of computer viruses

********************************************************************************

--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>

Colm Osiris-2

Re: beginners odbc question

Thanks very much Forbes.

> You can use execute SQL to grab a live value from SQL server.
>
> I have a script that performs a query against SQL server to get the
> total value of invoices, this is then placed in a global field.
>
> But to update this value you would have to run the script again --
> which
> is pretty quick.
>
> If you are looking at lots of records and fields, then you are back to
> importing data, which can be slow. I find that the delete of records,
> and the cleaning of data often takes longer than the actual SQL
> import.

Robertson, Forbes-2

RE: beginners odbc question

One way of reducing the amount of data that needs to be transferred, is
to get the SQL Server admin to setup a view for you, which shows the
data you require. You may need more than one view depending on the
relationships / data you need.

The Admin may prefer this from a security POV as he can set permissions
on the view.

The biggest downside with having multiple servers, is that you will need
to setup multiple ODBC connections on the client PCs. It may be possible
to have a view setup on 1 SQL server to pull data from the others.

You have two options with retrieving the data.

Get everything -- can be pretty slow (10s of minutes to hours), so
probably only done once a day. But easier if there is a complex set of
tables and data can be shown in lots of ways. Also if most data changes
slowly then yesterday's data isn't a big problem.

Get the record(s) you need at the time -- pretty quick, instant to a few
seconds. But the SQL queries can be complex if you need data from lots
of linked tables -- but this can be simplified by having a SQL view
setup. Needs more forward planning. Would be good if your users tend to
just look at individual records, rather than groups of records.

HTH

Forbes

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of Colm
Sent: Wednesday, August 08, 2007 10:26 AM
To: Filemaker Experts
Subject: Re: beginners odbc question

Thanks very much Forbes.

I don't know yet how many records are in the SQL database to be
queried. We are talking about a hospital, so I imagine there'll be
quite a lot, 250,000, maybe more. I will find out. AFAIK, data are
only being retrieved from the SQL database, not written to it.

I have yet to get more details, but I understand that patients'
details will be retrieved from the SQL database. This could amount to
quite a lot of data per query.

This may turn out to be more complicated than I thought, because when
I asked what flavour of SQL was being used, and what OS it was under,
I was told "We use MSSQL Server versions 7, 2000 and 2005 all running
on windows server 2000 / 2003 servers". I thought it was only going
to be the one database!

I will ask more detailed questions about what will be required.

Colm

--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>

--
********************************************************************************

The information in this email and any files transmitted with it is confidential
and may be legally privileged. It is intended solely for the addressee and
others authorised to receive it.

If you are not the intended recipient,, any disclosure, copying, distribution or
action taken in reliance on its contents is prohibited and may be unlawful.

Prospects Services Limited is a limited company registered in England and Wales.

Reg No: 3042176
Registered Office: Prospects House, 19 Elmfield Road, Bromley, BR1 1LT.

If you have received this email in error please notify:

[hidden email]

http://www.prospects.co.uk

This footnote also confirms that this email message has been swept for the
presence of computer viruses

********************************************************************************

--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>

kazar

Re: beginners odbc question

This is one situation where I would suggest looking at Servoy instead of
FMP as the application authoring and deployment tool, not only for the
increased stability and performance of Servoy, not only to avoid having
to set up an FMSA server (with the associated high costs and nowadays
expert-only husbandry requirements) ...

... but also because, particularly with multiple SQL back end servers,
you may end up cursing yourself every time you need to set up, update,
troubleshoot multiple ODBC connections on every client machine. (With
Servoy, the server application is free, it can run on any server box
with some spare space & RAM to provision and does not require its own
server, and one NEVER has to install any thing on a client computer,
NEVER.)

And, before the flames hit my in-box: No. I do not consider this
off-topic. It answers the unasked question in Colm's messages on this
thread: "Is FMP the right tool for this job?" That is the question we do
best to start with, rather than "how can I beat FMP over the head with
my sledge hammer to make it __________?"

Nothing does the reputation of FMI's or FMP devs more harm than
obstinately recommending it be used in situations where it will fall far
short of the performance, features and manageability compared to "real"
IT tools, and where performance, features and manageability will really
count. Why shoe-horn lots of SQL data into an FMP database, when you
could use a RAD development platform that is *made* to be a front end to
SQL data rather than made primarily to access its own db engine?

kazar

--
Datatude, Ltd.
Tech strategies & solutions
<http://www.datatude.net>
Visit our "RAD Tools of Choice" forums at <http://network.datatude.net>

--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>

Jonathan Fletcher

Re: beginners odbc question

On Aug 8, 2007, at 12:08 PM, Datatude wrote:

> This is one situation where I would suggest looking at Servoy
> instead of FMP as the application authoring and deployment tool,
> not only for the increased stability and performance of Servoy, not
> only to avoid having to set up an FMSA server (with the associated
> high costs and nowadays expert-only husbandry requirements) ...
>
> ... but also because, particularly with multiple SQL back end
> servers, you may end up cursing yourself every time you need to set
> up, update, troubleshoot multiple ODBC connections on every client
> machine. (With Servoy, the server application is free, it can run
> on any server box with some spare space & RAM to provision and does
> not require its own server, and one NEVER has to install any thing
> on a client computer, NEVER.)
>
> And, before the flames hit my in-box: No. I do not consider this
> off-topic. It answers the unasked question in Colm's messages on
> this thread: "Is FMP the right tool for this job?" That is the
> question we do best to start with, rather than "how can I beat FMP
> over the head with my sledge hammer to make it __________?"
>
> Nothing does the reputation of FMI's or FMP devs more harm than
> obstinately recommending it be used in situations where it will
> fall far short of the performance, features and manageability
> compared to "real" IT tools, and where performance, features and
> manageability will really count. Why shoe-horn lots of SQL data
> into an FMP database, when you could use a RAD development platform
> that is *made* to be a front end to SQL data rather than made
> primarily to access its own db engine?
>
> kazar

Kazar,

Hasn't a lot of this changed with the easier to setup and management
of FMS 9 and the ability to have just the server connect to the ODBC
data sources and not every individual desktop?

j.

--
Jonathan Fletcher
[hidden email]
Project Foreman
NewMedia Construction Co.

--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>

jim_bob_w

Re: beginners odbc question

In reply to this post by kazar

Not to quibble, but you set up ODBC on FMS, not on the client machines.

> ... but also because, particularly with multiple SQL back end
> servers, you may end up cursing yourself every time you need to set
> up, update, troubleshoot multiple ODBC connections on every client
> machine. (With Servoy, the server application is free, it can run on
> any server box with some spare space & RAM to provision and does not
> require its own server, and one NEVER has to install any thing on a
> client computer, NEVER.)

--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>

kazar

Re: beginners odbc question

In reply to this post by kazar

Jonathan Fletcher wrote, On 8/8/07 1:35 PM:

> Hasn't a lot of this changed with the easier to setup and management of
> FMS 9 and the ability to have just the server connect to the ODBC data
> sources and not every individual desktop?

and

Jim McNeely wrote, On 8/8/07 10:33 PM:

> Not to quibble, but you set up ODBC on FMS, not on the client machines.

No, not "quibbling at all" -- you guys are right, and thanks for the
connection. I went back to re-read Colm's original message and realized
I missed the reference to his client thinking about upgrading their
server to 9. The new ability in 9 to define ESS tables in the
relationship graph certainly does solve the thorny "get drivers working
on all clients" issues ...

... But for Colm's situation, where several big back end servers will be
queried, I just do not see FMP as being the best tool for the job.

From FileMaker's own FileMaker 9 ESS tech brief, page 4:

--------------------------------------------------------------
ESS Design Goals: What It Isn’t

The emphasis with ESS should be on integration. The ESS feature set is
not intended to allow FileMaker Pro to act as a “front end” to SQL data
sources. In particular:

• ESS does not allow a FileMaker Pro developer to compose their own SQL
queries and pass them to the server: ESS creates all SQL queries behind
the scenes. (Some such functionality is possible with ESS, though, by
defining views within the SQL data source. FileMaker Pro’s Execute SQL
script step, by contrast, does allow a developer to create and send
customized SQL queries.).
• ESS does not allow a FileMaker Pro developer to alter or extend the
schema of an SQL data source on the server side (though local extensions
are possible with the ability to add supplemental fields to an ESS table
within FileMaker Pro).
• ESS is not designed as a means to allow a FileMaker Pro solution to
scale beyond the limits of a purely FileMaker Pro based solution. The
ESS feature set is designed to emphasize the seamless integration of
SQL-based tables into a FileMaker Pro solution, rather than to take
specific advantage of the high-scalability features of SQL back ends.
--------------------------------------------------------------

(FWIW, Servoy also lets you compose your own SQL queries, no need to put
demands on IT resources to create a view for you ... and no need to
learn how to do that yourself.)

So, considering the cost of this client's possible upgrade from FMP 6 to
9 (no upgrade path), and considering that from Colm's description it
appears that much of the data the application needs will be pulled from
SQL dbms's (and admittedly with knowing only the barest requirements as
described thus far) -- it does not appear that FMP is an appropriate
choice. Predominantly connecting to multiple SQL servers with lots of
data? That is not the description of FMP's purpose given by its own
manufacturer.

kazar
--
Datatude, Ltd.
tech strategies & database solutions
<http://www.datatude.net>
Visit our "RAD Tools of Choice" forums at <http://network.datatude.net>

--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>

kazar

Re: beginners odbc question

In reply to this post by Colm Osiris-2

Colm wrote, On 7/31/07 8:39 AM:
> I have a potential client who are currently using FMP 6 and FMP Server
> 6, but wish to upgrade to 9. They have an SQL database (don't yet know
> more details of flavour or OS) which they wish to query from FMP 9.
> (As far as I am so far aware, they only want to import data into the
> FMP database, and not export.)

<snip>

> Is it worth my setting up a trial run of this using FMP 8 and Ubuntu
> at home, or do 8 and 9 act so differently that it wouldn't be of any
> use? And would it very different again if, say, they were using
> Windows instead of Linux?

Colm! I think we all missed that line. If you mean that this hospital
uses Linux servers: FileMaker Server has not been available for Linux
ever since FMS 7.

kazar

--
Datatude, Ltd.
tech strategies & database solutions
<http://www.datatude.net>
Visit our "RAD Tools of Choice" forums at <http://network.datatude.net>

--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>

kazar

Re: beginners odbc question

I wrote:
>
> Colm! I think we all missed that line. If you mean that this hospital
> uses Linux servers: FileMaker Server has not been available for Linux
> ever since FMS 7.

... and, come to think of it, you later posted that they are using MS
SQL Server on various flavors of Windows servers ... how does Linux fit
into the picture?

kazar

--
Datatude, Ltd.
tech strategies & database solutions
<http://www.datatude.net>
Visit our "RAD Tools of Choice" forums at <http://network.datatude.net>

--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>

Jason DeLooze

Re: beginners odbc question

Hi Ilyse (Kazar),

There was 8 days between Colm's original "SQL database possibly running on Windows or Linux Server" and his "three or more databases running on MSSQL version 7, 2000, and 2005 under Windows Server 2000 and 2003". He is learning more about his client's SQL setup. Undoubtedly, when he learns the client's vision of the future and reads the ESS capabilities of FM 9, as outlined in the "Introduction to External SQL Sources.pdf paper mentioned by Steven Blackwell, he will be able to make an informed decision as to which is the better approach: FM 9's ESS or SQL programming (via Servoy or other development tools).

Regards,
Jason L. DeLooze
Annapolis, MD USA

On 8/9/07 at 6:50 AM -0400, Datatude wrote:
> I wrote:
>>
>> Colm! I think we all missed that line. If you mean that this hospital uses Linux servers: FileMaker Server has not been available for Linux ever since FMS 7.
>
> ... and, come to think of it, you later posted that they are using MS SQL Server on various flavors of Windows servers ... how does Linux fit into the picture?
>
> kazar

--
To unsubscribe: send an email to <[hidden email]>
Archives: http://www.nabble.com/FMPExperts-f735.html
FMPexperts is hosted by Ironclad Networks <http://www.ironclad.net.au/>