Connecting to Filemaker Server

[jeffe]

Hi All,

I have been using Filemaker for about 10 years, and recently have considered using "Livecode" as I find that filemaker is very slow and limited once you relize your potential. I have been considering "Livecode" and more than happy to purchase a copy, but to my surprize I have NOT been able to find one example or instructions on how to connect "Livecode" to a filemaker Server. Once again, I really thought that the devoloper's of livecode have the potential to switch many filemaker user's - once they trial the power of "Livecode" its really hard to go back !!! But to my surprize NOT a single demo or instructions on how this is done - poor marketing on "Livecode" - Missing an opportunity there. As I am new I would like to only change the front end and continue to use Filemaker as the server. In time this will change.

I have been reading & searching through the forums, I understand that I can purchase a filemaker to livecode convertor. I don't want to do this I want to learn how to connect directly to a filemaker server using Livecode. Obviously, I also need to know how to add a new entry, update an existing entry and even navigate from one record to another. Thank you in advance for all for your help.


jeffe

[sturgis]

If filemaker supports odbc you can ue that to connect. There is no "native" filemaker support (yet) but another option might be to use a web based intermediary if odbc is not an option. IE use a php gateway to do all the transfers back and forth.

Another thing to mention is that livecode is attempting to go open source as we speak ( http://www.kickstarter.com/projects/175 ... f-livecode ) at which point you or someone else could create a filemaker connection module.

[bangkok]

But to my surprize NOT a single demo or instructions on how this is done - poor marketing on "Livecode" - Missing an opportunity there. As I am new I would like to only change the front end and continue to use Filemaker as the server. In time this will change

Don't worry.

I use on a daily basis FileMaker Pro Server (version 9 !) and LiveCode, since a long time. And it's working perfectly well.

There is no specific demo on how to connect to FM... because FM is just another database.

You understood that LiveCode has a few "specific" connectors : MySQL, SQLlite, Postgresql etc.

So in order to access to FM, we have to use the "generic" ODBC (you will use ODBC as well to connect to a MS SQL server for instance).

-create an ODBC data source on the machine that will be the client (that will have the LiveCode application installed on it)
-that's the only drawback : you have to set up an ODBC data source on each client machine. Can be boring if you have a large network.

-on the FM machine (with a regular FM or a FM Server), open your DB and then menu File / Sharing, choose "ODBC/JDBC..."
-turn it on, and be sure to specify "all users"

And in LiveCode :

Code: Select all

put revOpenDatabase("ODBC","FMDB",,"admin","admin75") into dbID

[it opens the connexion, with login and password]
[here "FMDB" is the name of the ODBC datasource, not the name of FM database itself].

After it's regular SQL (that obviously you will have to learn).

Code: Select all

put "SELECT *  FROM myfmdb" into dbSQL
put revDataFromQuery(,,dbID,dbSQL) into myData

Through this method you can :
-read data
-update and insert new data

Watchout for UPDATE and INSERT, LiveCode code will be :

Code: Select all

 put "insert into myfmtable (mycolumn1,mycolum2) values ('hello','okay')" into dbSQL
revExecuteSQL dbID, dbSQL

[jeffe]

Hi Bangkok,

Wow, thanks for the fast response. Great forum...

I am really exicited to see another filemaker user. How is the speed & reliability using "Livecode" as a frontend to a Filemaker Server. Are you happy with the performance? Funny enough, I feel like a Newbie when it comes to "Livecode" well I have alot of learning to do. I'm hoping that by the time I'm finished with this thread, I can release a simple code so that other filemaker user's are able to learn as I am doing now. I don't have an issue sharing code as I believe the more we all learn the more we can all achieve.

I am now trying to follow your steps - please guide me and sorry for what you might think are lame questions.
I have Filemaker Server 12.0.3 setup on one of my computers (Master Computer)
I opened Filemaker Advanced 12 and created a new file.

File Name: testconnection
User Name: Admin
Password: password
Sharing: ODBC/JDBC - All users on

Table Name: CompanyNames
- Field 1: MasterID
- Field 2: CompanyName

I added some data only 2 items
- ID 1 = Apple Inc
- ID 2 = American Express

I have now placed this file into the Master computer to be hosted by the filemaker server.
Once again, on the server i have made sure ODBC/JDBC is on.

I opened filemaker Advanced on my client machine to check to see if the file was viewable.
No problems, everything working well so far.

This is where I have no clue,
I opened Livecode, create a new stack and for now have added one button named "Connect"
following your instructions, I placed the following into the button.

on mouseUp

put revOpenDatabase("ODBC","FMDB","testconnection","Admin","password") into dbID -- Connect to Filemaker DB

if dbID is null then
answer "Not connected"
else
answer information "Connected - Your connection ID is: " & dbID
end if

end mouseUp

All I get is [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
I have been trying now for 3 hrs to without posting and unfortunately, I need more advise.
Can you please direct me from here.


Thanks jeffe

[bangkok]

You forgot one important step : to create the ODBC data source !

Take your FileMaker CD-Rom... inside you should have an ODBC driver : "Datadirect Sequelink client for ODBC 5.5"
Install it on your PC.

Doc :
http://www.filemaker.com/support/produc ... ide_en.pdf

(disclaimer : you have a very new FM version... I have an old one)

One advice : first, do everything on your PC (Filemaker Advanced + ODBC + Livecode client). It will allow you to test the whole solution quickly.

Once the solution is fine, then you can put everything on the machine with FM Server.

As for "reliability and speed". It's okay for me (but i use only SELECT, not update or insert new data).

One problem though : from time to time, the connexion to FM would be "lost". The client has to renew the connexion.

Another problem : if in your FM tables you use field names with spaces (or table names). That will force you to use the keyword quote within your SQL queries. Boring.
Like :

Code: Select all

put "SELECT "&quote&"my nice field"&quote&" from mytable" into dbSQL

[jeffe]

Hi Bangkok,

Sorry, worked out that the firewall on the server machine was blocking the connection.

I have now managed to connect. GREAT !!!
now my next problem.

I'm now using another button with this command
on mouseUp

--This query will select all the data from the table Test
put "SELECT * FROM CompanyNames" into tQuery

--This will query the Database Table
put revDataFromQuery(,,dbID, tQuery) into tData

--This will display all the data from the Database
put tData into field "CompanyNames"


end mouseUp


my error is "Execution error at line n/a (External Handler: exception) near revdberr, invalid connection id


Does this mean I am still not connected?


jeffe

[sturgis]

Put this before your revdatafromquery

answer information "The open databases: " & the revopendatabases & cr & "The database id i'm using: " & dbID

This way you can compare the actual open database ids and compare to the value of dbID

The most likely answer is that dbID doesn't contain your database id due to scope. You have 1 button that connects and it sets a database id. Then you have another button that tries to use dbid. If dbID is a global variable you still have to declare it in any scripts that will use it. If it is a local script to a different button then you can't see it from a different object. You could use a property, declare a global, or it might be easier to consolidate everything into the stack script and then just call the handlers from each button. This way they can all share the same local variables.

So, if you put your handlers into the stack script like so (including the variable declaration)

Code: Select all

local dbID

command myConnectHandler
--stuff here
end myConnectHandler

command myQueryHandler
--grab the info here
end myQueryHandler

command whateverOtherHandler
-- whatever
end whateverOtherHandler

Then every handler in that script can use the value of the local dbID variable.

[jeffe]

Hi bangkok,

I managed to connect and retrieve the data.
Thank you so much,
I changed the button to...

local dbID

on mouseUp

put revOpenDatabase("ODBC","fmODBC","testconnection","Admin","password") into dbID -- Connect to Filemaker DB

if dbID is null then
answer "Not connected"
else
answer information "Connected - Your connection ID is: " & dbID

--This query will select all the data from the table Test
put "SELECT * FROM CompanyNames" into tQuery

--This will query the Database Table
put revDataFromQuery(,,dbID, tQuery) into tData

--This will display all the data from the Database
put tData into field "CompanyNames"
end if

end mouseUp


I will try add a couple of features and then post some more.
I will also add the files with exact instructions for other filemaker users.


Thanks again

[sturgis]

Yep that works. As long as the database id is still available when its needed. (in this case, since its ALL the same button its not an issue. The only thing I might mention is that if you click the button several times there will be an open database connection for each click, so you should check to see if the database is already open, if not don't open it again.

If you only want to connect, do your stuff, disconnect, then you'll want to add revclosedatabase at the end. You can see if you have opened the database multiple times by using the message box and execute

put revopendatabases()

If its a list of numbers, each number is an open connection.

[jeffe]

Hi Bangkok,

Yes, I will include the Revclosedatabase...
I have another question, hopefully my last.

Is it possible to connect remotely to the Filemaker database (Not on local network) via ip address?
would this work?

put revOpenDatabase ("ODBC","xxx.xxx.xxx.xxx","testconnection","Admin","password") into dbID


Thanks jeffe

[sturgis]

i'm not bangkok, but yes it should be possible.. depending.

If its an internal IP, rather than your server having a "real" ip number, you have to forward the port from the router to the correct machine.

If you have a real ip on the filemaker server, you can probably point directly to that machine. The first option is most likely.

I don't know what ports an ODBC connection requires, hopefully bangkok or a google search will help with that part.

[jeffe]

Hi Sturgis,

Thanks for sharing,
I have now tried to connect many different ways remotely with NO SUCCESS.

Please anyone, little help here would go a long way.


Thanks again


jeffe

[bangkok]

Is it possible to connect remotely to the Filemaker database (Not on local network) via ip address?
would this work?

put revOpenDatabase ("ODBC","xxx.xxx.xxx.xxx","testconnection","Admin","password") into dbID

No. You can not point directly to an IP address with ODBC protocol, in LiveCode.

First, as I said before, you have to create an ODBC DSN (datasource name) on the PC that will connect to the FileMaker database.

-1-have you installed the ODBC driver, like presented in the documentation, on your PC ?
http://www.filemaker.com/support/produc ... ide_en.pdf

-2-then have you created the ODBC DSN on the PC that will host your LiveCode client ?

I can't help you if you don't do those 2 steps.

[jeffe]

Hi Bangkok,

I worked it out, I can now connect remotely.
I do appreciate your help.

Now I will try and put a little open source file together for other filemaker users.



jeffe

[sturgis]

I don't know enough about odbc unfortunately, but I thought you could use a dns file pointing to IP address, port etc and if your port forwarding was set up correctly then you could connect from the outside. The other option would be to vpn into the network and have dsn set up for that config.
Is it just filemaker that can't do it, or on the other side of the coin, is the example I found an exception to the rule?

Either way, vpn should solve the issue. Grats on figuring out a method jeffe, mind sharing what you did to solve the issue?

Is it possible to connect remotely to the Filemaker database (Not on local network) via ip address?
would this work?

put revOpenDatabase ("ODBC","xxx.xxx.xxx.xxx","testconnection","Admin","password") into dbID

No. You can not point directly to an IP address with ODBC protocol, in LiveCode.

First, as I said before, you have to create an ODBC DSN (datasource name) on the PC that will connect to the FileMaker database.

-1-have you installed the ODBC driver, like presented in the documentation, on your PC ?
http://www.filemaker.com/support/produc ... ide_en.pdf

-2-then have you created the ODBC DSN on the PC that will host your LiveCode client ?

I can't help you if you don't do those 2 steps.