Forum Stats

  • 3,826,042 Users
  • 2,260,590 Discussions
  • 7,896,773 Comments

Discussions

Strange problem with PHP and ODBC

Starocotes
Starocotes Member Posts: 8
edited Mar 29, 2017 7:10PM in PHP

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

Tagged:

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Mar 15, 2017 12:02PM

    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
    Christopher Jones-Oracle Member Posts: 1,648 Employee
    edited Mar 29, 2017 7:10PM

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

This discussion has been closed.