CFQUERYPARAM with tables names

[Don Vaillancourt]

Is there a way to create this sort of query?

select *
from <cfqueryparam value="table_name">


Thank you

[jdeline]

No. Here's what the CF docs say about the field "value": Required. Specifies
the actual value that ColdFusion passes to the right of the comparison operator
in a where clause.

What are you trying to accomplish?

[mxstu]

No. I believe cfqueryparam uses jdbc data type specs and afaik there is not a
"table" data type. If you just need to scrub the "table_name" string, you
could use a simple UDF. There are some over at cflib.org or you could create
your own.

[url]http://livedocs.macromedia.com/coldfusion/7/htmldocs/00000317.htm[/url]

[Don Vaillancourt]

We have an app which needs to support multiple databases. And so
because not all database engines support auto-increament and
auto-increament through ColdFusion is not necessarily a good idea on
clustered environments we opted to stored the unique ids in a special
table that has two columns, table_name and id. There is a third column
we use for clustering, but isn't important for this example.

So anyway, to keep the recompliation of queries to a minimum I was
hoping to make the table_name a variable.

The functionality is already written and so all is working. I just
wanted to optimize the query as much as possible.

Where the table_name comes into play as a table is if the entry in the
unique_id table for the specified table_name is non-existant, I would
need to get the largest ID from the table_name table.

And because our system has almost 50 tables that require a unique PK, I
was hoping to keep the number of compiled queries down to one.

jdeline wrote:

> No. Here's what the CF docs say about the field "value": Required. Specifies
> the actual value that ColdFusion passes to the right of the comparison operator
> in a where clause.
>
> What are you trying to accomplish?
>

[jdeline]

You can make the table name a variable.

SELECT * FROM #tableName# WHERE ...

Will that do the job for you?

[Don Vaillancourt]

Well like I said, it's all done and we've done like you describe.

But my goal in regards to optimization is I was hoping to make as much
into parameters as possible. But that's alright. I'll use cfqueryparam
where I can and simple variables when I cannot.

Thanks


jdeline wrote:

> You can make the table name a variable.
>
> SELECT * FROM #tableName# WHERE ...
>
> Will that do the job for you?