PHP/mySQL query on Filemaker Pro (non-server), with ODBC

[skiss]

Hello, I would like to display on a web page, using PHP and mySql queries, data from a FileMaker Pro 9 database. It's the opposite of what most people want to do.

Is it possible without the server version ? I don't plan to access the database directly with PHP functions, I would like to pull data from Filemaker with mySql queries.

How to do that ? Do I need the Filemaker ODBC client driver, the mySQL client driver, or both ?

How should I set them up ? I have downloaded and installed the mySql odbc connector, and set it up in ODBC administrator in OS X, with these values

Host : localhost

Port : 2399

ServerDataSource : fmp_db

(fmp_db is without the .fp7 extension)

But I'm not sure it was the right thing to do.

And what PHP/MySql commands should I write in my web page to get the data ?

I have this at the moment :

$db = mysql_connect('localhost', 'admin', '');

mysql_select_db('fmp_db', $db);

$sql = 'SELECT * FROM table';

$req = mysql_query($sql) or die('

Erreur SQL !

'.$sql.'

'.mysql_error());

I get an SQL error with these lines.

Thanks in advance for your help / hints.

Stephane

[Genx]

Ahhhhhh... You can't use the mySQL connection to interact with something that isn't mySQL. mySQL is a peice of software, and the php class you're using is created to interact specifically with mySQL not any SQL based application.

..It's like expecting Microsoft Word to be able to read the text in a FileMaker file by right clicking the .fp7 file and clicking open with Microsoft Word.

You have to use the PHP ODBC connector - go to php.net and search odbc_connect as a function, should lead you to further resources... why you would want to do this however I'm not sure.

ODBC is fairly innefficient and limited when compared to using the FileMaker PHP API. Further, to run with ODBC you will need a copy of FileMaker Server Advanced 9 (costs 2 to 3 times more) where as to run with the API you will only need FileMaker Server 9. Should you still want to use ODBC, you will find that you need to install the FM ODBC connector which should be somewhere on your FM Disk.

I understand your desire to want to use something you are familiar with, but the FM PHP API is easy to get the hang of. You may find some useful resources at: http://www.fmwebschool.com.

Edited July 29, 2007 by Guest

[Genx]

To replicate your MySQL query with the PHP API:

$fm = new FileMaker( 'fmp_db', 'localhost', 'admin', '' );

$find_query = $fm -> newFindAllCommand('layout');

$find_result = $find_query->execute();



if( FileMaker::isError($find_result) ) {

die('FileMaker Error: '.$find_result->getCode().' '.$find_result->getMessage());

} else {

/*Process Your Result here*/

}

[Genx]

Oh non server - well okay. In any case, you will need to use the odbc class not the mySQL class.

[skiss]

Thanks for your help. I don't have filemaker pro server, that's why I couldn't use the PHP API from filemaker. But I'll think about it.

For now, I'm trying to get the PHP odbc to work. Should work fine with filemaker pro non-server, right ?

I use PHP 5, but the problem I have is I don't seem to have the ODBC functions. When I test wether they are installed with function_exists(), PHP tells me they are not there. Is there a way to install them ?

Thanks,

Stephane

[Genx]

Did you use the PHP installer to install PHP or did you do so manually?

[skiss]

I'm using MAMP. Maybe that's the problem.

[Genx]

Well I always avoided MAMP and WAMP all those other combined installers - to be honest only because apache conflicted with my IIS install.

Anywho, when installing PHP with the php installer, you are given the option to install various components - the odbc component is one of these. You can also install it manually but its more effort. If you want, go to the php site and search something like php setup or manual php install (or google search those).

[skiss]

I'm getting closer, but it still doesn't work. I have installed PHP 5.2.2 on OS X, which includes the ODBC functions.

I have setup ODBC administrator (the OS X app) this way

DSN : dsn_name

Host : localhost

Port : 2399

ServerDataSource : fmp_db_name

I have set up fmp so that my database authorizes ODBC accesses, and I have set up a user with full access rights.

Here is my PHP code :

<?php

$db = odbc_connect("dsn_name", "user", "password", "") or die('

Could Not Connect to ODBC Database!

');

$query = 'SELECT * FROM table';

$result = odbc_exec($db, $query) or die('

Query error !

');

odbc_fetch_row($result);

$res1 = odbc_result($result, 1);

$res2 = odbc_result($result, 2);

print("$res1 $res2n");

odbc_close($db);

?>

I'm only trying to display the first result for now. I get the "Could not connect to ODBC Database" message, which tells me my odbc_connect has failed.

I really need to get that to work. Any advice ?

The problem I have is I don't have a clue what is working, what is not, and where to look.

Thanks in advance for the help,

Stephane

[Genx]

Okay, I'm going to assume you added a user in FileMaker called "user" with the password "password".

Go to the define accounts box in FileMaker.

Go to the extended privileges tab, double click fmxdbc. Next, make sure you turn this on for the privilege that your "user" account is using.

Next, Click Edit->Sharing->ODBC/JDBC. Turn it on and Click Okay.

Also, make sure the FileMaker file remains open while you are trying to execute your query.

Let me know how you go.

[skiss]

I had done all that already. It still doesn't work.

What can it be ?

I have no idea what's wrong. Is there a way to check if the ODBC drivers are running properly ?

[Genx]

$db = odbc_connect("dsn_name", "user", "password");

if( odbc_error($db) ){

die(odbc_error($db).': '.odbc_errormsg($db));

}

You really shouldn't use generic error's - doesn't help you when you're trying to debug your code...

Try the above and see what your error is.

Edited July 31, 2007 by Guest

[skiss]

Here's what I get. Any ideas ? I think the drivers are not working but I don't have a clue why. I followed exactly what's in the FMP manual.

Warning: odbc_connect() [function.odbc-connect] SQL error: [iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded, SQL state IM002 in SQLConnect in /Users/steph/Sites/index2.php on line 16

Warning: odbc_error(): supplied argument is not a valid ODBC-Link resource in /Users/steph/Sites/index2.php on line 18

Warning: odbc_close(): supplied argument is not a valid ODBC-Link resource in /Users/steph/Sites/index2.php on line 38

[Genx]

okay - you know how you keep using "dsn_name", "user" and "password" - what are the real values you are using here?

Where did you get the driver for FM?

[skiss]

I thought it would be more clear, here are the real values

dsn_name = MBPLevel2Sys

user = WebUser

password = level2

I have changed the ODBC drivers location, from being installed just from one user to beeing installed for the whole system.

It's getting a bit better, now it seems to find the drivers, but it still doesn't work :

Warning: odbc_connect() [function.odbc-connect]: SQL error: [iODBC][Driver Manager]Specified driver could not be loaded, SQL state IM003 in SQLConnect in /Users/steph/Sites/index2.php on line 16

Warning: odbc_error(): supplied argument is not a valid ODBC-Link resource in /Users/steph/Sites/index2.php on line 18

Warning: odbc_close(): supplied argument is not a valid ODBC-Link resource in /Users/steph/Sites/index2.php on line 38

******* ! Setting up ODBC is the most complicated thing I have ever tried to do on my computer...

I guess if it had worked right away I would have found it easy.

[Genx]

Okay -- apparently IM003 refers to the fact that the SQL driver couldn't be loaded.

I suggest you just runt hrough the setup again...

I'd go into more detail but i've got to run, will try help you out later in the day.

[skiss]

I'll try to continue that tomorrow. For now I'm trying to start the work I need to do in File maker.

I'm trying to do the equivalent of

SELECT error_type, COUNT(error_type) AS error_type_count FROM table GROUP BY error_type ORDER BY error_type_count DESC

If you could have a look at the topic I created in the "Calculation Engine" section of the forum, it would be really nice.

Thanks for all your help, I appreciate it.

[Dusolais]

re: [iODBC][Driver Manager]Specified driver could not be loaded, SQL state IM003 in SQLConnect

i too have had the dreaded "IM003" blues, however nothing to do with filemaker, but with other iODBC drivers.

I did finally find the solution, and it is because Leopard ships with a 64bit executable of apache, and that is incompatible with some odbc drivers.

simple solution is here, worked for me

http://www.actualtechnologies.com/phpsetup.php

Alex.

Edited June 23, 2008 by Guest