Re: Passing a value into a WHERE clause

Folks above in thread already mentioned how to go about doing do what you are looking to do - by throwing a variable into the WHERE clause for the dynamic changing of the sql statement to the left of the '=' sign, and then your '?' to the right of the '=' sign would stay the same.

Would add on to this discussion by commenting that the reason that you can use the '?' to the right of the '=' is that you are using the '?' as a placeholder to bind the value if you are going to use the statement multiple times in your script for example. Most newer database products support this and by doing it you don't have to incur the overhead of executing the execution plan each time the statement is run.

Which brings me to a question which interests me which your point gets me wondering about...

In a way it seems as though using a variable in the creation a sql-DBI prepare statement such as in the example in this thread is an oxymoron. Can you get away with not incuring the overhead of re-executing the execution plan each time even though you are changing your actual sql statement via a variable which alters the statement itself?

I looked into the Perldoc DBI module documentation documentation and around a bit and couldn't find the answer to the question.

Does anyone know the answer to this? peppiv's initial question got me wondering (nothing like posting a question back to a question)...

nandeya

Comment on Re: Passing a value into a WHERE clause

Replies are listed 'Best First'.
Re: Re: Passing a value into a WHERE clause by dws (Chancellor) on Jan 26, 2002 at 07:03 UTC
In a way it seems as though using a variable in the creation a sql-DBI prepare statement such as in the example in this thread is an oxymoron. Can you get away with not incuring the overhead of re-executing the execution plan each time even though you are changing your actual sql statement via a variable which alters the statement itself? No. When you change the statement, you need to re-prepare, and hence replace. What you can do is cache statement handles based on what field name you've decided to plug in to the query. In theory, you then bite off the overhead of preparing (and query planning) only once per statement.	[reply]

Replies are listed 'Best First'.

Re: Re: Passing a value into a WHERE clause
by dws (Chancellor) on Jan 26, 2002 at 07:03 UTC

In a way it seems as though using a variable in the creation a sql-DBI prepare statement such as in the example in this thread is an oxymoron. Can you get away with not incuring the overhead of re-executing the execution plan each time even though you are changing your actual sql statement via a variable which alters the statement itself?

No. When you change the statement, you need to re-prepare, and hence replace.

What you can do is cache statement handles based on what field name you've decided to plug in to the query. In theory, you then bite off the overhead of preparing (and query planning) only once per statement.

[reply]