Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

Generate SQL Query, SQL::Abstract?

by Anonymous Monk
on Mar 16, 2020 at 10:04 UTC ( [id://11114334]=perlquestion: print w/replies, xml ) Need Help??

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

First poted on reddit, can't link here.

In a database I have, various field. The fields contain search criteria for that respective field e.g.
FOO FOO|BAR|BAZ *TEST*|*BLUE*
The | character here means 'or' in SQL. So if the column here was called PROD_TITLE, the SQL where clause for above would be:
where PROD_TITLE = 'FOO' where PROD_TITLE in ('FOO','BAR','BAZ') where PROD_TITLE like '*TEST* or PROD_TITLE like '*BLUE'
To complicate things further I have a specific column for free search data, containing key/value pairs (like URL params):
&BAZ=TEST &BAZ=*TEST* &ORDERDATE=20200101:2020130 &BAZ=TEST*&ORDERDATE=20200101:2020130
I looked at SQL::Abstract::FromQuery but I don't have this data as a URL param. Is there a clever perl way I can use either SQL::Abstract or SQL::Abstract form to generate a SQL query catering for all where clauses?

Replies are listed 'Best First'.
Re: Generate SQL Query, SQL::Abstract?
by haukex (Archbishop) on Mar 16, 2020 at 12:12 UTC

    It seems like you've got a custom format that you won't be able to use directly with SQL::Abstract, or SQL::Abstract::FromQuery and so on. That means you'll have to transform your input data anyway, whether you transform it to Perl data structures for use in those modules, or you transform it directly to SQL strings. If you do happen to choose the latter, then remember to always use DBI's quote, quote_identifier, and placeholders!

Re: Generate SQL Query, SQL::Abstract?
by choroba (Cardinal) on Mar 16, 2020 at 17:17 UTC
    The link to Reddit.

    map{substr$_->[0],$_->[1]||0,1}[\*||{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^*ARGV,3]

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://11114334]
Approved by haukex
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others wandering the Monastery: (4)
As of 2024-03-29 08:55 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found