Strange problem with PHP and ODBC

Starocotes

Im axecuting a query against an database and I do have a different behaviour between executing via isql from the command line and via PHP.

The query:

	SELECT
	A.LFDNR,
	A.ARTNR1,
	A.ABEZ1,
	A.ABEZ2,
	A.ABEZ3,
	G.AGRUPPE,
	O.LBEZ1,
	L.PHYBEST,
	L.LFDARTNR,
	M.MINWERT,
	M.BESTWERT,
	L.PHYBEST*A.EKPR/A.PREISKENNZ as WERT,
	B.SBMG, B.SBMG*A.EKPR/A.PREISKENNZ as BWERT

    FROM

	KW.LAGER L,
	KW.ARTIKEL A,
	KW.LAGERORT O,
	SYSADM.AGRUPPE G,
	SYSADM.LGMINBEST M,
	(
		SELECT
			MANDANT,
			ARTNR,
			SUM(BESTMENGE) as SBMG
		FROM
			KW.BESTELLUNG
		WHERE
			BESTSTATUS = '3'
		GROUP BY
			MANDANT,
			ARTNR
	) B

    WHERE

	A.LFDNR = L.LFDARTNR AND
	A.MANDANT = L.MANDANT AND
	A.AGRUPNR = G.LFDNR AND
	L.LFDARTNR = M.LFDARTNR(+) AND
	L.MANDANT = M.MANDANT(+) AND
	A.LFDNR = B.ARTNR(+) AND
	A.MANDANT = B.MANDANT(+) AND
	L.LFDLAGNR = O.LFDNR(+) AND
	G.AGRUPPE = 'GLASU' AND
	(
		B.SBMG <> 0 or
		L.PHYBEST <> 0 or
		M.MINWERT <> 0 or
		M.BESTWERT <> 0
	)
	AND
	A.LFDNR = L.LFDARTNR

    ORDER BY

A.ABEZ1;

Works fine either way. As soon as I enter the field A.MANDANT in the select statement:

	SELECT
	A.MANDANT,
	A.LFDNR,
	A.ARTNR1,
	A.ABEZ1,
	A.ABEZ2,
	A.ABEZ3,
	G.AGRUPPE,
	O.LBEZ1,
	L.PHYBEST,
	L.LFDARTNR,
	M.MINWERT,
	M.BESTWERT,
	L.PHYBEST*A.EKPR/A.PREISKENNZ as WERT,
	B.SBMG, B.SBMG*A.EKPR/A.PREISKENNZ as BWERT

    FROM

	KW.LAGER L,
	KW.ARTIKEL A,
	KW.LAGERORT O,
	SYSADM.AGRUPPE G,
	SYSADM.LGMINBEST M,
	(
		SELECT
			MANDANT,
			ARTNR,
			SUM(BESTMENGE) as SBMG
		FROM
			KW.BESTELLUNG
		WHERE
			BESTSTATUS = '3'
		GROUP BY
			MANDANT,
			ARTNR
	) B

    WHERE

	A.LFDNR = L.LFDARTNR AND
	A.MANDANT = L.MANDANT AND
	A.AGRUPNR = G.LFDNR AND
	L.LFDARTNR = M.LFDARTNR(+) AND
	L.MANDANT = M.MANDANT(+) AND
	A.LFDNR = B.ARTNR(+) AND
	A.MANDANT = B.MANDANT(+) AND
	L.LFDLAGNR = O.LFDNR(+) AND
	G.AGRUPPE = 'GLASU' AND
	(
		B.SBMG <> 0 or
		L.PHYBEST <> 0 or
		M.MINWERT <> 0 or
		M.BESTWERT <> 0
	)
	AND
	A.LFDNR = L.LFDARTNR

    ORDER BY

A.ABEZ1;

The query works fine if I execute it via isql in the command line but fails in PHP without an error. It just returns an empty result set.

I'm using the 12.1 Linux client

Server Version is 10.2.0

Gaz in Oz

so what does your php code look like?...

and isql? unixODBC iSQL?!!

Run the sql you posted in sqlplus and show us the result.

It would also help immensly if you simplified your above example, showing the issue, and formatted it too so it is easier to read.

(To format with fixed font, syntax highlighting etc, click on the "Advanced formatting link, top right of post).

Christopher Jones-Oracle

Other than the suggestions from @Gaz in Oz, I would recommend ditching the ODBC driver and using PHP OCI8.