Program which builds SQL-queries

[JackTheRussel]

Hi all.

I have program where I have GetOption structure:

GetOption('t|time=s' => %time,
          'n|name=s' => $name,
          'p|prodID=i' => $productID);

So user can give example time, name and product information in command line like this:
./script -t begin=19.9.2006 end=20.9.2006 -n=john -p 3

And I can get values like this:
print "Start time: ", $time{begin};
print "End time: ", $time{end};
print "name: ", $name;
print "prodID", $productID;

But how I can build "generic" SQL-queries in these values?
What I mean is that user dont always give all values.

example user can give ONLY name or just productID and name etc...

so please give me some advice. I have no clue.

 

[stevexff]

You have a fixed set of options allowed in your getopts. Check each variable and build the query up in stages. It can get a bit messy, because of the syntax of the WHERE clause.

#pseudocode (sorry, don't have perl on this pc)

my @parms;

$sql = "SELECT * FROM mytable WHERE ";
$and = "";

if (begin and end specified) {
   $sql = $sql . $and . "time BETWEEN ? AND ? ";
   push @parms, $start, $end;
   $and = "AND ";
}
if (start but no end) {
   $sql = $sql . $and . "time > ? ";
   push @parms, $start;
   $and = "AND ";
}
if (name specified) {
   $sql = $sql . $and . "name = ? ";
   push @parms, $name;
   $and = "AND ";
}

prepare the statement
execute it using the @parms array as parameters

Well, you get the idea. The @parms array still protects you from SQL injection attacks.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[JackTheRussel]

Thanks steve.

Only thing what I dont understand is: How I know which is the last statement.

If I do like that the last statement word is AND

Example

"SELECT * FROM mytable WHERE time BETWEEN ? AND ? and name = ? AND

 

[stevexff]

$sql = $sql . [red]$and[/red] . "time BETWEEN ? AND ? ";

The first time $and is used, it is set to "". Thereafter it is "AND ". Why would it appear at the end, when it is in the middle?

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]