Skip to main content

SQL bind peeking

  • December 8, 2013
  • 2 replies
  • 10 views

eli_revach
Forum|alt.badge.img+2

Hi ,

Any method to peek bind variables values from a given query ?   

For example :

I see in the query_requets table a query like -> select * from mytable where id=? 

I like to know what is the value of the id . 

Oracle provide some API to show this value , any method in vertica ?

Thanks

2 replies

Daniel_Leybovic
Forum|alt.badge.img+1
  • Participating Frequently
  • December 8, 2013
Hi!
Use in regexp (for described task regexp are good enough)
SELECT *
FROM (
SELECT regexp_substr(request, 'from\s+[^\s]+', 1, 1, 'in') AS "table",
regexp_substr(request, 'where\s+id\s+=\s+[^\s]+', 1, 1, 'in') AS "filter"
FROM query_requests) AS queries
WHERE queries.filter IS NOT NULL;

daniel=> \g
table | filter
----------+-----------------------
from foo | where id = 683679;
from egg | where id = 683679;
from bar | where id = 9268854;
from bar | where id = hash('a');
from foo | where id = hash('a');
from foo | where id = 000;
from foo | where id = 666;
from foo | where id = 109;
from foo | where id = 16;
(9 rows)

SELECT *
FROM (
SELECT regexp_substr(request, 'from\s+([^\s]+)', 1, 1, 'in', 1) AS "table",
regexp_substr(request, 'where\s+id\s+=\s+([^\s]+)', 1, 1, 'in', 1) AS "filter"
FROM query_requests) AS queries
WHERE queries.filter IS NOT NULL;

daniel=> \g
table | filter
-------+------------
foo | 683679;
egg | 683679;
bar | 9268854;
bar | hash('a');
foo | hash('a');
foo | 000;
foo | 666;
foo | 109;
foo | 16;
(9 rows)

eli_revach
Forum|alt.badge.img+2
  • Author
  • Participating Frequently
  • December 9, 2013
Binds var will be presnt on query request as set of ? ? Chars , no regexp will help her , we need some dictinary table that hold the mapiing berween the ? Chars To the real values