Skip to Main Content
Forums

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Strange problem with PHP and ODBC

StarocotesMar 15 2017 — edited Mar 29 2017

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

Comments

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).

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

1 - 2
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 26 2017
Added on Mar 15 2017
#odbc, #php
2 comments
338 views