Problems with ODBC, PHP and text

[jonas234]

Ok, I've a quite big problem for which I couldn't find a solution yet.

I've a FM8-DB with ODBC-connection on and with the drivers from http://www.filemaker.com/odbc

The connection is working, tested with iODBC and also with FmPro Worksheet 1.04 from .com Solutions.

There I can also do queries which are working correctly.

Now to the problem. In my DB I have two fields, a number-field and a text-field. I have 42 records in it.

When I do the following query with odbc_exec:

"SELECT ID FROM dns"

it works perfectly and I get all the numbers.

When I do:

"SELECT text FROM dns"

I don't get the first 9 datasets. When I add another dataset I just don't get the first 10 datasets.

When I do

"SELECT * FROM dns"

it returns me for the ID for the first 9 sets just 0.

PHP version is 5.1.2 running on MacOSX 10.4.5.

Has anyone any idea what my mistake could be?

Thanks,

jonas

[Chris Irvine]

Interesting Jonas,

I'm seeing similar problems. I'm curious, are you using the bundled iODBC support from entropy.ch PHP package? I'm starting to think there might be something wrong with the ODBC client in this package.

Performing a "SELECT state FROM zips" on my table of 42k records took less than 1 minute of CPU time from FileMaker but over 20 minutes of CPU time from php. Like you, I'm seeing many blank results for the first records in my result sets. But, I think the problem isn't on the ODBC side because queries using the iODBC `odbctest` program seem to return valid results in a fraction of the time.

PHP 5.0.2, OSX 10.4.4, FMPA 8.0v2

Looking for any other suggestions of things to try.

-Chris

[benh57]

I am using the raw php interface w/ ODBC+ FM8. I had serious problems / extreme slowness / bad data from FM8 ODBC until i connected to the ODBC driver using SQL_CUR_USE_ODBC as the 4th argument to odbc_connect.

http://us2.php.net/odbc_connect

(Similarly, my tests were fast using ODBC test apps, but slow from php.)

Edited March 29, 2006 by Guest

[Chris Irvine]

Thanks, but making that change to the odbc_connect call didn't seem to help with my bad data problem.

Here is the strange part, it seems like I only receive data for the last 33 fields for any given SELECT. If I select 1 fields, all records are blank accept for the last 33. If I select 2 fields, all records are blank accept for record last-17 has 1 valid field, and records last-16 to the end are valid. If I select 3 fields, all records are blank accept for the last 11.

I've tested this code under 3 different entropy.ch packages, php versions: 4.3.11, 5.0.4, 5.1.2

I'm starting to wonder if the problem should be reclassified as PHP, but most of the users there seem to be using XML based connections.

Here is my failing code and a snip of the results:

#!/usr/local/php/bin/php

<?php



$dsn="zips"; 

$user="johnd";

$password="foo";





$sql="SELECT city, county, zip FROM zips WHERE state = 'OR'";



// directly execute mode

if ($conn_id=odbc_connect("$dsn","$user","$password", SQL_CUR_USE_ODBC)) {

	echo "connected to DSN: $dsn";

	if($result=odbc_do($conn_id, $sql)) {

		echo "executing '$sql'";

		$result_fields=odbc_num_fields($result);

		echo "fields in result:" . $result_fields . "n";

		echo "Results: ";

		while (odbc_fetch_row($result)) {

			for($i = 1; $i <= $result_fields; $i++)

				echo odbc_result($result, $i) . ", ";

			echo "n";

		}

		echo "freeing result";

		odbc_free_result($result);

	} else {

		echo "can not execute '$sql' ";

	}

	echo "closing connection $conn_id";

	odbc_close($conn_id);

} else {

	echo "can not connect to DSN: $dsn ";

}



?>

...

...

, , , 

, , , 

, , , 

, , , 

, , , 

Harper, Malheur, 97906, 

Huntington, Baker, 97907, 

Ironside, Malheur, 97908, 

Jamieson, Malheur, 97909, 

Jordan Valley, Malheur, 97910, 

Juntura, Malheur, 97911, 

Nyssa, Malheur, 97913, 

Ontario, Malheur, 97914, 

Riverside, Malheur, 97917, 

Vale, Malheur, 97918, 

Westfall, Malheur, 97920, 

freeing resultclosing connection Resource id #4

Edited March 29, 2006 by Guest

[jonas234]

Hah, I almost forgot this topic.

I still have this problem and did a lot of research now.

I have 3 test-enviroments:

1 with MacOS 10.4 and PHP 5 (entropy.ch)

1 with MacOS 10.3.9 and PHP 5 (entropy.ch)

1 with Windows 2000 and XAMP.

With Windows is works perfectly. It's still slow, but thats not the problem. It returns all the data.

With MacOS there are only problems. It not only returns not all data in a simple SELECT-statement, it also sometime just stops the script anywhere in between.

I also don't think that it has anything to do with the FM-ODBC driver. FMPro Worksheet returns me all the data correctly.

I thought something about a limit of the size of the cache or something similar.

[jonas234]

After a search at the entropy-forums I found the following post:

http://www.entropy.ch/phpbb2/viewtopic.php?p=8096&highlight=odbc#8096

I could imagine that this is the problem. PHP uses an old ODBC-library which doesn't work right with FileMaker while all the other applications (iodbctest, FmProWorksheet) are using the newest iODBC-library.