Nullable for query/procedure parameters

pmmvt

Hi,
I'm developing report modules.
Sometimes, user will not input some criteries, my module will show all record with 'is null' parameter.

DB::Execute('SELECT * FROM table WHERE f_input_date = %T or %T is null', array($date))

I can write other query for null situation and I do more than.

ajb

I don't see a question in your post, but why you won't use RecordBrowser::get_records?

pmmvt

I usually use the procedure for report. Some report, user can dismiss some parameters, report wil show all, therefore the parameter is null in query.
I call
DB::Execute(select * from procedure_name(%d, %T), array($id, $date));

EPESE will throw an exception because null.

ajb

[quote:31qz9bo9]

DB::Execute('SELECT * FROM table WHERE f_input_date = %T or %T is null', array($date))

[/quote:31qz9bo9]
I'm not sure, but you want to get all records if you pass $date = null? Problem is that %T won't return SQL NULL. I suggest to cast to boolean

DB::Execute('SELECT * FROM table WHERE f_input_date = %T or %b is false', array($date, boolval($date)))

Or build your sql like this:

$sql = 'SELECT * FROM table';

$args = array();

if ($date) {

  $sql .= ' WHERE f_input_date = %T';

  $args[] = $date;

}

DB::Execute($sql, $args);

Regards,
Adam

Epesi ENS | Epesi Academy | epesi.cloud PaaS | GitHub

Help Me! | Download from SourceForge