SQL::Abstract with non SQL source data

[Anonymous Monk]

I have a table in an oracle database the columns contain search details for other tables. These details are used by a third party product over which I have no control, it searches the same oracle database. I want to be able to generate a SQL query based on these search details from the table. These search details are not SQL :(

I've had some success using SQL::Abstract. I am stuck with some of the criteria. For example a record type may be as follows:

FOO
FOO|BAR|BAZ
FO%|B%|CAT

Here % is a wildcard, similar to oracle and | is logical OR. The first two aren't a problem, I don't understand how to process the third. In addition to that one of the columns contains a key value pair of a column name and it's value:
SHIFT=FOO
SHIFT=FO%|BAR

Using SQL::Abstract I've written a script, for each filed checking to see if each value contains things like |,%,:,< and > characters. So far in testing this works ok up to a point. I can't working out how to deal with examples containing multiple values which have wild cards (FO%|B%|CAT). With a great number of columns (and a column which contains key value pairs) I'm wondering if there's a more genereric way to do this, without having a stack of if,else conditions for each possible combination. If anyone can advise on a more sensible approach I'd be glad to know it.

[roboticus]

First, you need to figure out what you would convert each case of your criteria to, and then figure out what the common elements are. I'm going to assume a simple text field. So the first one is pretty simple, you want to convert FOO to column_name = 'FOO'. The second one is also pretty simple, you can convert it to:

column_name in ('FOO', 'BAR', 'BAZ')

or you could use:

(column_name = 'FOO' or column_name = 'BAR' or column_name = 'BAZ')

For the third case, you'd want to convert it to something like:

(column_name like 'FO%' or column_name like 'B%' or column_name = 'CAT
+')

To make things simpler, we should wrap all of our clauses in parenthesis, so we don't have to add code to figure out when we need them or not. You need them in some cases to allow different clauses to interact properly, as we wouldn't want an adjacent search clause to interact with part of this one.

So to code it up, I'd suggest something like:

sub generate_criteria {
   my ($ColName, $Criteria) = @_;
   my @fields = split /\|/, $Criteria;

   return " $ColName = '$field[0]' " if @fields==1;

   my @ret;
   my @wildfields = grep { $_ =~ /[%_]/ } @fields;
   if (@wildfields) {
      push @ret, join(" or ", map { "$ColName like '$_'" } @wildfields
+;
   }
   my @constfields = grep { $_ !~ /[%_]/ } @fields;
   if (@constfields) {
      push @ret, " $ColName in (" . join(", ", map { "'$_'" } @constfi
+elds) . ")";
   }
   return join(" ", "(", @ret, ")");
}

Now there are a few details you'll want to take care of, like ensuring the fields are properly quoted, etc., but this is how I'd approach the problem. Let me know if you need any further details or explanations.

...roboticus

When your only tool is a hammer, all problems look like your thumb.

Update:

1. I didn't test any of the code, so there may be some errors here and there. (I code & type fast, and let perl tell me when I bobbled the syntax.)
2. I didn't think of it at the time, but we can take care of the quotes with another map statement just after the initial split, like so:
   

   my @fields = map { s/'/''/g; $_ } split /\|/, $Criteria;
   

3. Finally, I know I could've gotten away with a simpler routine by treating all cases the same:
   

   sub generate_criteria {
      my ($ColName, $Criteria) = @_;
      my @fields = map { s/'/''/g; $_ } split /\|/, $Criteria;
   
      return "( " . join(" or ", map { "$ColName LIKE '$_'" } @fields) . 
   +" )";
   }
   

   It works because LIKE used with a constant string is converted to the same code as '=' (at least it appears so in Oracle, Sybase and MS SQL). But I like making the SQL look like it would if I generated it by hand.

[moritz]

Your question is very vague. You show no code, and yet hope that we can propose how to change your code to make it work.

Also you don't tell us what SQL you want to generate from FO%|B%|CAT, and why you are having problems with doing that when more than one placeholder is present.

At the current state of knowledge I'd just say "use a loop around the code that handles placeholders".