Forum Stats

  • 3,876,197 Users
  • 2,267,081 Discussions
  • 7,912,467 Comments

Discussions

Escaping of special signs, once it works, once not

PS_orclNerd
PS_orclNerd Member Posts: 384 Bronze Badge
edited Apr 1, 2015 6:37PM in PHP

Hi all,

I would like to ask a simple question, I believe some of you know this issue already and know how to solve it, I googled an hour, changed the code 100 times, but with the first $query I get always "Execute not successful."

Does anyone know how to solve this?

       $query = "

            select

                t.snap_time,

                n.value-lag(n.value,1,n.value) over (order by n.name,n.snap_id) lag

            from stats\$sysstat n,stats\$snapshot t 

            where

            n.snap_id in (select snap_id from stats\$snapshot where snap_time between :low and :high )

            and n.name = :statname

            and t.snap_id=n.snap_id

            order by 1,2 " ;

      

        //$query = "select * from stats\$snapshot where rownum<=:bind" ;

function doQuery($conn,$query) {

            try {

            $stmt = oci_parse($conn, $query) ;


            $low = '25-03-2015 10:00:00';

            $high = '25-03-2015 21:00:00';

            $statname = 'parse count (total)';


            oci_bind_by_name($stmt, ":low", $low ) ;

            oci_bind_by_name($stmt, ":high", $high ) ;

            oci_bind_by_name($stmt, ":statname", $statname ) ;

          

            //$val = 1;

          

            //oci_bind_by_name($stmt, ":bind", $val) ;

          

            // Perform the logic of the query

            $status = oci_execute($stmt);

            if ($status){

            while (($row = oci_fetch_assoc($stmt)) != false){

                // Do something

                var_dump($row) ;

            }

            } else {

                echo 'Execute not successful.<br>';

            }

          

            oci_free_statement($query) ;

            } catch (Exception $e){

                echo $e->getMessage() ;

            }

        }

Answers

  • Antonio Navarro
    Antonio Navarro Member Posts: 787
    edited Apr 1, 2015 6:15AM

    I remember the dollar symbol get problems. Probe $$ or \$$.

    Other easy way is create a synonym for the table stats$sysstat.

    Example

    create synonym tab1 for stats$sysstat

    HTH - Antonio NAVARRO

  • PS_orclNerd
    PS_orclNerd Member Posts: 384 Bronze Badge
    edited Apr 1, 2015 7:02AM

    Hi, I tried it now, but it does not work, too. The purpose is to generate graphs later, get the data would be nice, creating synonyms on 500 databases without a change request is not really a good solution.

    If I echo the query I get:

    select t.snap_time, n.value-lag(n.value,1,n.value) over (order by n.name,n.snap_id) lag from stats$sysstat n,stats$snapshot t where n.snap_id in (select snap_id from stats$snapshot where snap_time between :low and :high ) and n.name = :statname and t.snap_id=n.snap_id order by 1,2

    Execute not successful.

    0.06801 ms


    Edit: after adding some debug info:

                $status = oci_execute($stmt) ;

               

                if (!$status) {

                    $e = oci_error($stmt) ;

                    var_dump($e);

                }

    I get:

    array (size=4)

      'code' => int 1830

      'message' => string 'ORA-01830: zadan� form�t d�tumu neposta?uje pre �pln� konvertovanie vstupn�ho re?azca' (length=85)

      'offset' => int 279

      'sqltext' => string 'select
      t.snap_time,
      n.value-lag(n.value,1,n.value) over (order by n.name,n.snap_id) lag
      from stats$sysstat n,stats$snapshot t 
      where
      n.snap_id in (select snap_id from stats$snapshot where snap_time between :low and :high )
      and n.name = :statname
      and t.snap_id=n.snap_id
      order by 1,2 ' (length=393)


    So it means my date format is not good.

  • PS_orclNerd
    PS_orclNerd Member Posts: 384 Bronze Badge
    edited Apr 1, 2015 7:21AM

    After finding out the problem is not the escaping, but the date format I have changed it little bit and now it works

    $query = "select

                    t.snap_time,

                    n.value-lag(n.value,1,n.value) over (order by n.name,n.snap_id) lag

                from stats\$sysstat n,stats\$snapshot t

                where

                n.snap_id in (select snap_id from stats\$snapshot where snap_time between to_date(:low,'dd.mm.yyyy hh24:mi:ss') and to_date(:high,'dd.mm.yyyy hh24:mi:ss') )

                and n.name = :statname

                and t.snap_id=n.snap_id

                order by 1,2 " ;

  • Christopher Jones-Oracle
    Christopher Jones-Oracle Member Posts: 1,656 Employee
    edited Apr 1, 2015 6:37PM

    Glad you resolved it.

    To avoid quoting issues, I like using NOWDOCs: https://blogs.oracle.com/opal/entry/php_53_nowdocs_make_sql_escapi

This discussion has been closed.