Passing a parameter to a bookmark

If we look again at the first bookmark we created (finding all books for author 1), we realize that although it's useful, it's limited to finding just one author—always the same one.

Special query syntax enables the passing of parameters to bookmarks. This syntax uses the fact that SQL comments enclosed within /* and */ are ignored by MySQL. If the /*[VARIABLE]*/ construct exists somewhere in the query, it will be expanded at execution time with the value provided when recalling the bookmark.

Creating a parameterized bookmark

Let us say we want to find all the books for a given author when we don't know the author's id. We first enter the following query: 

SELECT author.name, author.id, book.title
FROM book, author
WHERE book.author_id = author.id
/* AND author.name LIKE '%[VARIABLE]%' */

The part between the comment characters (/* */) will be expanded later, and the tags will be removed. We label this query as a bookmark named find author by name (before executing it) and then click on Go. The first execution of the query just stores the bookmark while retrieving all books by all the authors, as this time we haven't passed a parameter to the query.

In this example, we have two conditions in the WHERE clause, of which one contains the special syntax. If our only criterion in the WHERE clause needs a parameter, we can use a syntax such as /* WHERE author_id = [VARIABLE] */.

Passing the parameter value

To test the bookmark, we recall it as usual and enter a value in the Variable dialog.

Passing the parameter value

When we click on Go, we see the expanded query, and the author Smith's books.

Passing the parameter value
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.145.37.126