IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this forum will no longer be available. More details available on our FAQ.
Topic
  • 3 replies
  • Latest Post - ‏2019-08-08T06:01:01Z by B.Hauser
VicWilson
VicWilson
17 Posts

Pinned topic SQL Global variables from PHP

‏2019-04-05T22:50:32Z |

We are using SQL Global variables regularly in views from RPG.  We need to add them to some PHP scripts.  However we are experiencing problems.

It looks like the statement to set the global variable executes correctly.  However when the select against the view happens we get an SQL error that the global variable was not found.

I'm at a loss as to what needs to happen differently.

Vic

  • robberendt
    robberendt
    84 Posts

    Re: SQL Global variables from PHP

    ‏2019-04-08T13:31:41Z  

    I think we would need more samples.  

    I can get this to work:

    create variable Delete_this_variable for system name dtvar integer;
    set delete_this_variable = 2;
    values delete_this_variable;
    select iclas, count(*) as itemclasscount
    from erplxf.iim
    group by iclas
    having count(*) > delete_this_variable;

    So I am wondering if you might possibly running into some situation where you are hitting an "overloading" issue.  Basically your variable type and the type you are selecting from differ.

     

     

  • VicWilson
    VicWilson
    17 Posts

    Re: SQL Global variables from PHP

    ‏2019-08-07T19:12:20Z  

    I think we would need more samples.  

    I can get this to work:

    create variable Delete_this_variable for system name dtvar integer;
    set delete_this_variable = 2;
    values delete_this_variable;
    select iclas, count(*) as itemclasscount
    from erplxf.iim
    group by iclas
    having count(*) > delete_this_variable;

    So I am wondering if you might possibly running into some situation where you are hitting an "overloading" issue.  Basically your variable type and the type you are selecting from differ.

     

     

    Rob,

    I'm just now getting back to this - as we had a work around before but really need to solve this problem.

    Was the sample code you show executed in PHP?

    We generally create the global variable before creating the view.  The global variable is created in the same library as the underlying tables.

    Then we set the global variable when the process runs - which has mostly been RPG.  It's my understanding that when the global variable is 'SET' - a copy is created in QTEMP.

    This all works fine in RPG.  However it does not work in PHP.  In your example I would create the global variable every time.  But a global variable cannot be created in QTEMP?  And I'm not thinking it would work well for multiple users to be creating the global variable every time someone runs the process.

     

    Have you done this in PHP?  Or some other non RPG scenario?

     

    Thanks.

    Vic

  • B.Hauser
    B.Hauser
    320 Posts

    Re: SQL Global variables from PHP

    ‏2019-08-08T06:01:01Z  
    • VicWilson
    • ‏2019-08-07T19:12:20Z

    Rob,

    I'm just now getting back to this - as we had a work around before but really need to solve this problem.

    Was the sample code you show executed in PHP?

    We generally create the global variable before creating the view.  The global variable is created in the same library as the underlying tables.

    Then we set the global variable when the process runs - which has mostly been RPG.  It's my understanding that when the global variable is 'SET' - a copy is created in QTEMP.

    This all works fine in RPG.  However it does not work in PHP.  In your example I would create the global variable every time.  But a global variable cannot be created in QTEMP?  And I'm not thinking it would work well for multiple users to be creating the global variable every time someone runs the process.

     

    Have you done this in PHP?  Or some other non RPG scenario?

     

    Thanks.

    Vic

    Are the SET and the File/View Access performed on the same connection and within the same job?

    Birgitta