CODEWITHSUNDEEP
phpsqloracle-databaseplsql
McBurgerKong
McBurgerKong

Reputation: 151

No results from PL/SQL query, but there should be.

I know there are several question about this error, also for php.
I've tried most of the answers and nothing seems to work.

This is my query :

"select
    id,
    employeenr,
    name,
    section,
    description,
    duration,
    to_date(substr(startdate,0,12)||' '||starttime, 'DD-MM-YYYY HH24:MI:SS') starttime,
    to_date(substr(enddate,0,12)||' '||endtime, 'DD-MM-YYYY HH24:MI:SS') endtime,
    worktime,
    statement,
    remark
    from
    data
    where   1=1
    ".$vw."
    AND startdate between to_date(':df 00:00:00', 'DD-MM-YYYY HH24:MI:SS') and to_date(':dt 23:59:59', 'DD-MM-YYYY HH24:MI:SS')
    ".$vw1."
    order by nvl(employeenr,0), startdate";
$parameters = array(':df' => $date_from, ':dt' => $date_to);

Echo var vw:

AND ( employeenr between '100000' and '199999'OR employeenr between '400000' and '499999'OR employeenr between '700000' and '799999'OR (employeenr is not null AND employeenr between '100000' and '199999'OR employeenr between '400000' and '499999'OR employeenr between '700000' and '799999'))

$date_from and $date_to:

$date_from = $_POST["date_from"]; // jQuery datepicker value
$date_to = $_POST["date_to"]; // jQuery datepicker value

What am I doing wrong?

Upvotes: 2

Views: 145

Answers (3)

McBurgerKong
McBurgerKong

Reputation: 151

SOLUTION

I have been able to fix my problem by stripping out the substr in the SELECT area of the query.

$sql_report = "
        select
        id,
        employeenr,
        name,
        section,
        description,
        duration,
        startdate,
        starttime,
        enddate,
        endtime,
        worktime,
        statement,
        remark
        from
        data
        where   1=1
        ".$vw."
        AND startdate BETWEEN to_date('$date_from', 'DD-MM-YYYY') AND to_date('$date_to', 'DD-MM-YYYY')
            ".$vw1."
        order by nvl(employeenr,0), startdate";
$parameters = array();

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95052

New question, new answer.

Still you don't use your bind variable for a variable (date, number or string), but try to replace part of a string with it. This doesn't work.

':df 00:00:00' is a string containing a colon followed by a 'd', an 'f', etc.

Use a bind variable for a complete string, i.e.:

AND startdate >= to_date(:date_from, 'DD-MM-YYYY')
AND startdate < to_date(:date_to, 'DD-MM-YYYY') + 1

or

AND TRUNC(startdate) BETWEEN to_date(:date_from, 'DD-MM-YYYY') 
                         AND to_date(:date_to, 'DD-MM-YYYY')

or

AND startdate between to_date(:date_from || ' 00:00:00', 'DD-MM-YYYY HH24:MI:SS') 
                  and to_date(:date_to || ' 23:59:59', 'DD-MM-YYYY HH24:MI:SS')

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 95052

How is $Parameters used later? It looks as if you want to use it as a bind varibale array to pass later to Oracle. If so, then you use this incorrectly, because rather than holding a variable's value the string contains SQL. This is not how bind variables work in Oracle.

You must give Oracle a complete query to parse, only that values in the query can be unknown (a date, a number or a string). These you can pass later. You cannot parse half a query and add more SQL in a variable later.

So AND ? cannot be parsed, as no value makes sense here. Something like AND X = ? could be parsed on the other hand, as Oracle would expect to get the value to compare column X with later when the query gets executed.

Upvotes: 1

Related Questions